#At file:///home/tsk/mprog/src/5.3-subqueries/ based on revid:timour@askmonty.org-20100315195258-nhomb3anbb1tv3mi 2780 timour@askmonty.org 2010-03-16 MWL#68: Subquery optimization: Efficient NOT IN execution with NULLs Fix for the PBXT copy of subselect.test. modified: mysql-test/suite/pbxt/r/subselect.result mysql-test/suite/pbxt/t/subselect.test === modified file 'mysql-test/suite/pbxt/r/subselect.result' --- a/mysql-test/suite/pbxt/r/subselect.result 2010-02-23 09:22:02 +0000 +++ b/mysql-test/suite/pbxt/r/subselect.result 2010-03-15 22:41:30 +0000 @@ -876,6 +876,8 @@ select (select a+1) from t1; 4.5 NULL drop table t1; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a)); CREATE TABLE t2 (a int(11) default '0', INDEX (a)); INSERT INTO t1 VALUES (1),(2),(3),(4); @@ -1771,6 +1773,7 @@ id select_type table type possible_keys Warnings: Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b` on(((`test`.`b`.`id` = `test`.`a`.`id`) or isnull(`test`.`b`.`id`))) join `test`.`t1` `c` where (if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) = `test`.`c`.`id`) drop table t1,t2; +set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int); insert into t1 values (1); explain select benchmark(1000, (select a from t1 where a=sha(rand()))); @@ -2750,6 +2753,8 @@ select * from (select max(fld) from t1) max(fld) 1 drop table t1; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; CREATE TABLE t1 (one int, two int, flag char(1)); CREATE TABLE t2 (one int, two int, flag char(1)); INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); @@ -2834,6 +2839,7 @@ id select_type table type possible_keys 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') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (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`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; +set @@optimizer_switch=@save_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb')); @@ -3004,6 +3010,8 @@ field1 field2 1 1 1 3 DROP TABLE t1, t2; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; CREATE TABLE t1(a int, INDEX (a)); INSERT INTO t1 VALUES (1), (3), (5), (7); INSERT INTO t1 VALUES (NULL); @@ -3019,6 +3027,7 @@ a a IN (SELECT a FROM t1) 2 NULL 3 1 DROP TABLE t1,t2; +set @@optimizer_switch=@save_optimizer_switch; CREATE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25'); CREATE TABLE t2 AS SELECT === modified file 'mysql-test/suite/pbxt/t/subselect.test' --- a/mysql-test/suite/pbxt/t/subselect.test 2009-11-06 17:22:32 +0000 +++ b/mysql-test/suite/pbxt/t/subselect.test 2010-03-15 22:41:30 +0000 @@ -477,6 +477,9 @@ drop table t1; # Null with keys # +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; + CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a)); CREATE TABLE t2 (a int(11) default '0', INDEX (a)); INSERT INTO t1 VALUES (1),(2),(3),(4); @@ -1121,6 +1124,8 @@ select * from t1 a left join t2 b on (a. explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id); drop table t1,t2; +set @@optimizer_switch=@save_optimizer_switch; + # # Static tables & rund() in subqueries # @@ -1784,6 +1789,9 @@ drop table t1; # Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...) # +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; + CREATE TABLE t1 (one int, two int, flag char(1)); CREATE TABLE t2 (one int, two int, flag char(1)); INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); @@ -1811,6 +1819,9 @@ explain extended SELECT one,two from t1 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; DROP TABLE t1,t2; +set @@optimizer_switch=@save_optimizer_switch; + + # # Bug #12392: where cond with IN predicate for rows and NULL values in table # @@ -1972,6 +1983,9 @@ DROP TABLE t1, t2; # with possible NULL values by index access from the outer query # +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; + CREATE TABLE t1(a int, INDEX (a)); INSERT INTO t1 VALUES (1), (3), (5), (7); INSERT INTO t1 VALUES (NULL); @@ -1984,6 +1998,8 @@ SELECT a, a IN (SELECT a FROM t1) FROM t DROP TABLE t1,t2; +set @@optimizer_switch=@save_optimizer_switch; + # # Bug #11302: getObject() returns a String for a sub-query of type datetime # @@ -3096,6 +3112,7 @@ SELECT a,b FROM t1 WHERE b IN (SELECT a DROP TABLE t1,t2; + # # Bug #32400: Complex SELECT query returns correct result only on some # occasions