Hi Roy,
The difference is not just EXPLAIN output, there are different query
results (grep down for result-difference!) . We need to investigate those
before we can push this.
On Mon, Nov 17, 2008 at 02:46:44PM +0100, Roy Lyseng wrote:
> #At file:///home/rl136806/mysql/repo/mysql-6.0-opt/
>
> 2700 Roy Lyseng 2008-11-17
> Bug#40667: subselect.test switches connection at line 1464, forgets
> optimization settings
>
> One test case requires a "new" connection. When that test case is finished,
> close the new connection
> and switch back to the default one.
> modified:
> mysql-test/r/subselect_no_mat.result
> mysql-test/r/subselect_no_opts.result
> mysql-test/r/subselect_no_semijoin.result
> mysql-test/t/subselect.test
>
> per-file messages:
> mysql-test/t/subselect.test
> Make sure that all test cases use the default connection, unless explicitly asked
> for another.
>
>
> === modified file 'mysql-test/r/subselect_no_semijoin.result'
> --- a/mysql-test/r/subselect_no_semijoin.result 2008-08-27 16:48:24 +0000
> +++ b/mysql-test/r/subselect_no_semijoin.result 2008-11-17 13:46:35 +0000
> @@ -2822,10 +2822,10 @@ Warnings:
> Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS
> `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select
> `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where
> ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) =
> `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and
> trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or
> isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`))
> and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
> explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM
> t2 WHERE flag = 'N');
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join
> buffer
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
> Warnings:
> -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from
> `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and
> (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))
> +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from
> `test`.`t1` where
> <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`)
> in ( <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two`
> from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ),
> <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on
> distinct_key)))
> explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag =
> '0' group by one,two) as 'test' from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
> @@ -4165,14 +4165,26 @@ CREATE TABLE t1 (s1 BINARY(5), s2 VARBIN
> INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
> SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
> s1 s2
> +A+B+C SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
> s1 s2
> +A+B+C CREATE INDEX I1 ON t1 (s1);
> CREATE INDEX I2 ON t1 (s2);
> SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
> s1 s2
> +A+B+C SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
> s1 s2
> +A+B+C TRUNCATE t1;
> INSERT INTO t1 VALUES (0x41,0x41);
> SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
> @@ -4209,37 +4221,47 @@ LEFT(t1.a1,1)
> 3
> SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
> a2
> +2
> +3
result-difference!
> DROP TABLE t1, t2, t3;
> CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
> INSERT INTO t1 VALUES ('a', 'aa');
> INSERT INTO t1 VALUES ('a', 'aaa');
> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
> a b
> +a aa
> +a aaa
result-difference!
> CREATE INDEX I1 ON t1 (a);
> CREATE INDEX I2 ON t1 (b);
> EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan
> -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY t1 index NULL I1 2 NULL 2 Using index
> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
> a b
> +a aa
> +a aaa
result-difference!
> CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
> INSERT INTO t2 SELECT * FROM t1;
> CREATE INDEX I1 ON t2 (a);
> CREATE INDEX I2 ON t2 (b);
> EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan
> -1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY t2 index NULL I1 4 NULL 2 Using index
> SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
> a b
> +a aa
> +a aaa
result-difference!
> EXPLAIN
> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
> -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY t1 index NULL I1 2 NULL 2 Using where; Using index
> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
> a b
> +a aa
> +a aaa
result-difference!
> DROP TABLE t1,t2;
> CREATE TABLE t1(a INT, b INT);
> INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
>
> === modified file 'mysql-test/t/subselect.test'
> --- a/mysql-test/t/subselect.test 2008-10-20 09:16:47 +0000
> +++ b/mysql-test/t/subselect.test 2008-11-17 13:46:35 +0000
> @@ -1460,10 +1460,13 @@ drop table t1;
>
> #
> # Subselect in non-select command just after connection
> +# Disconnect new connection and switch back when test is finished
> #
> connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
> connection root;
> set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
> +disconnect root;
> +connection default;
>
> #
> # primary query with temporary table and subquery with groupping
BR
Sergey
--
Sergey Petrunia, Lead Software Engineer
MySQL AB, www.mysql.com
Office: N/A
Blog: http://s.petrunia.net/blog
