From: Sergey Petrunia Date: November 17 2008 5:55pm Subject: Re: bzr commit into mysql-6.0-opt branch (roy.lyseng:2700) Bug#40667 List-Archive: http://lists.mysql.com/commits/58986 Message-Id: <20081117175517.GM13327@pslp2.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7BIT 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`,((`test`.`t1`.`one`,`test`.`t1`.`two`),(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond((((`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond((((`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond((`test`.`t2`.`one`)) and trigcond((`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 ((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), (`test`.`t1`.`one` in 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