Sergey Petrunia wrote:
> 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.
You are right, and I should have notified you. I sorted out that this
must be a known subquery-related bug, I think it is #36752. However, it
seems that bugs.mysql.com is down now so I cannot verify the bug id.
Roy
>
> 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
