At file:///home/psergey/dev/maria-5.3-subqueries-r7/ ------------------------------------------------------------ revno: 2761 revision-id: psergey@askmonty.org-20100217215941-5uxponk64p55xg4a parent: psergey@askmonty.org-20100217104755-3psvc5fmo3pqsnpy committer: Sergey Petrunya <psergey@askmonty.org> branch nick: maria-5.3-subqueries-r7 timestamp: Thu 2010-02-18 00:59:41 +0300 message: Subquery optimizations backport: fix test failures, update test results. === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2010-02-17 10:05:27 +0000 +++ b/mysql-test/r/subselect.result 2010-02-17 21:59:41 +0000 @@ -1293,31 +1293,31 @@ 4 explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1336,31 +1336,31 @@ 4 explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using index -2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t3 index a a 5 NULL 3 100.00 Using index +1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.61 Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` = `test`.`t3`.`a`)) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a @@ -1373,10 +1373,10 @@ 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) drop table t0, t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -2827,10 +2827,10 @@ 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 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; FirstMatch(t1) Warnings: -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`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = 'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) +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')) 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 @@ -3416,7 +3416,7 @@ SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort ALTER TABLE t1 ADD INDEX(a); SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); a b @@ -3427,7 +3427,7 @@ SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort DROP TABLE t1; create table t1( f1 int,f2 int); insert into t1 values (1,1),(2,2); @@ -4200,8 +4200,8 @@ 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 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where +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 SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); a b CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); @@ -4210,15 +4210,15 @@ 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 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 2 Using index; Using where +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 SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); a b 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 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where +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 SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); a b DROP TABLE t1,t2; @@ -4320,16 +4320,16 @@ INSERT INTO t1 VALUES (1),(2); EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having 1)) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`))))) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having 1)) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`))))) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. === modified file 'mysql-test/r/subselect2.result' --- a/mysql-test/r/subselect2.result 2007-01-12 20:22:41 +0000 +++ b/mysql-test/r/subselect2.result 2010-02-17 21:59:41 +0000 @@ -126,11 +126,11 @@ 1 PRIMARY t2 ALL DDOCTYPEID_IDX NULL NULL NULL 9 Using where 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 -2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where -3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where -4 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where -5 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where -6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 Using where +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 Using where +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where drop table t1, t2, t3, t4; CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB; INSERT INTO t1 VALUES (1),(2); === modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2010-01-17 20:52:20 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2010-02-17 21:59:41 +0000 @@ -54,7 +54,7 @@ Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 1 AS `1` from dual having ((select '1' AS `a`) = 1) +Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -207,7 +207,7 @@ 3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort Warnings: -Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from dual +Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt` select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); a 2 @@ -318,7 +318,7 @@ Warnings: Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select (select '2' AS `a` from dual where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2` +Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2` select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; ERROR 21000: Subquery returns more than 1 row create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); @@ -739,7 +739,7 @@ Warnings: Note 1249 Select 3 was reduced during optimization Note 1249 Select 2 was reduced during optimization -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1))) +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1)) EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index @@ -1340,7 +1340,7 @@ 4 explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index 1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`) @@ -1350,7 +1350,7 @@ 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index 1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) @@ -1377,7 +1377,7 @@ 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index 1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) @@ -1437,7 +1437,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 (select 'tttt' AS `s1` from dual) +Note 1003 (select 'tttt' AS `s1` from `test`.`t1`) (select * from t1); s1 tttt @@ -1625,7 +1625,7 @@ 3 UNION t1 system NULL NULL NULL NULL 1 100.00 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select 'e' AS `s1` from dual where 1 +Note 1003 select 'e' AS `s1` from `test`.`t1` where 1 drop table t1; CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); @@ -4204,7 +4204,7 @@ 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 where; Using index; LooseScan +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 SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); a b @@ -4214,7 +4214,7 @@ 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 where; Using index; LooseScan +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 SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); a b @@ -4327,13 +4327,13 @@ 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1)))) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having 1)) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1)))) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having 1)) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. @@ -4377,7 +4377,8 @@ WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY st1 index NULL a 5 NULL 2 Using index +2 DEPENDENT SUBQUERY st2 index b b 5 NULL 2 Using where; Using index; Using join buffer SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -4686,7 +4687,7 @@ explain SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using where; Using index +1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using index 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 Using index 2 DEPENDENT SUBQUERY t2 index b,b_2,c d 5 NULL 1 Using where SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a; === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2010-01-17 20:52:20 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2010-02-17 21:59:41 +0000 @@ -54,7 +54,7 @@ Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 1 AS `1` from dual having ((select '1' AS `a`) = 1) +Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -207,7 +207,7 @@ 3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort Warnings: -Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from dual +Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt` select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); a 2 @@ -318,7 +318,7 @@ Warnings: Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select (select '2' AS `a` from dual where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2` +Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2` select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; ERROR 21000: Subquery returns more than 1 row create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); @@ -739,7 +739,7 @@ Warnings: Note 1249 Select 3 was reduced during optimization Note 1249 Select 2 was reduced during optimization -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1))) +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1)) EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index @@ -1318,7 +1318,7 @@ explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where +2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) @@ -1437,7 +1437,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 (select 'tttt' AS `s1` from dual) +Note 1003 (select 'tttt' AS `s1` from `test`.`t1`) (select * from t1); s1 tttt @@ -1625,7 +1625,7 @@ 3 UNION t1 system NULL NULL NULL NULL 1 100.00 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select 'e' AS `s1` from dual where 1 +Note 1003 select 'e' AS `s1` from `test`.`t1` where 1 drop table t1; CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); @@ -4327,13 +4327,13 @@ 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1)))) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having 1)) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1)))) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having 1)) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. @@ -4377,7 +4377,8 @@ WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY st1 index NULL a 5 NULL 2 Using index +2 DEPENDENT SUBQUERY st2 index b b 5 NULL 2 Using where; Using index; Using join buffer SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -4686,7 +4687,7 @@ explain SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using where; Using index +1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using index 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 Using index 2 DEPENDENT SUBQUERY t2 index b,b_2,c d 5 NULL 1 Using where SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a; === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2010-02-17 21:59:41 +0000 @@ -4377,7 +4377,8 @@ WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY st1 index NULL a 5 NULL 2 Using index +2 DEPENDENT SUBQUERY st2 index b b 5 NULL 2 Using where; Using index; Using join buffer SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-02-17 10:05:27 +0000 +++ b/sql/item_subselect.cc 2010-02-17 21:59:41 +0000 @@ -3267,7 +3267,7 @@ if (tmp_result_sink->create_result_table( thd, tmp_columns, TRUE, thd->options | TMP_TABLE_ALL_COLUMNS, - "materialized subselect")) + "materialized subselect", TRUE)) DBUG_RETURN(TRUE); tmp_table= tmp_result_sink->table; === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2010-02-17 10:05:27 +0000 +++ b/sql/opt_subselect.cc 2010-02-17 21:59:41 +0000 @@ -2199,7 +2199,7 @@ */ sjm->sjm_table_param.init(); sjm->sjm_table_param.field_count= item_list.elements; - // psergey-merge: the following is not in 5.x: sjm->sjm_table_param.bit_fields_as_long= TRUE; + sjm->sjm_table_param.bit_fields_as_long= TRUE; List_iterator<Item> it(item_list); Item *right_expr; while((right_expr= it++)) === modified file 'sql/sql_class.cc' --- a/sql/sql_class.cc 2009-11-12 04:31:28 +0000 +++ b/sql/sql_class.cc 2010-02-17 21:59:41 +0000 @@ -2890,6 +2890,7 @@ quick_group= 1; table_charset= 0; precomputed_group_by= 0; + bit_fields_as_long= 0; DBUG_VOID_RETURN; } === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2010-01-17 14:55:08 +0000 +++ b/sql/sql_class.h 2010-02-17 21:59:41 +0000 @@ -2710,11 +2710,18 @@ */ bool precomputed_group_by; bool force_copy_fields; + /* + If TRUE, create_tmp_field called from create_tmp_table will convert + all BIT fields to 64-bit longs. This is a workaround the limitation + that MEMORY tables cannot index BIT columns. + */ + bool bit_fields_as_long; TMP_TABLE_PARAM() :copy_field(0), group_parts(0), group_length(0), group_null_parts(0), convert_blob_length(0), - schema_table(0), precomputed_group_by(0), force_copy_fields(0) + schema_table(0), precomputed_group_by(0), force_copy_fields(0), + bit_fields_as_long(0) {} ~TMP_TABLE_PARAM() { @@ -2745,7 +2752,7 @@ bool create_result_table(THD *thd, List<Item> *column_types, bool is_distinct, ulonglong options, - const char *alias); + const char *alias, bool bit_fields_as_long); }; /* Base subselect interface class */ === modified file 'sql/sql_cursor.cc' --- a/sql/sql_cursor.cc 2009-10-19 17:14:48 +0000 +++ b/sql/sql_cursor.cc 2010-02-17 21:59:41 +0000 @@ -714,7 +714,8 @@ { DBUG_ASSERT(table == 0); if (create_result_table(unit->thd, unit->get_unit_column_types(), - FALSE, thd->options | TMP_TABLE_ALL_COLUMNS, "")) + FALSE, thd->options | TMP_TABLE_ALL_COLUMNS, "", + FALSE)) return TRUE; materialized_cursor= new (&table->mem_root) === modified file 'sql/sql_derived.cc' --- a/sql/sql_derived.cc 2009-05-27 15:19:44 +0000 +++ b/sql/sql_derived.cc 2010-02-17 21:59:41 +0000 @@ -168,7 +168,8 @@ */ if ((res= derived_result->create_result_table(thd, &unit->types, FALSE, create_options, - orig_table_list->alias))) + orig_table_list->alias, + FALSE))) goto exit; table= derived_result->table; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-02-15 21:53:06 +0000 +++ b/sql/sql_select.cc 2010-02-17 21:59:41 +0000 @@ -10963,7 +10963,15 @@ group != 0, !force_copy_fields && (not_all_columns || group !=0), - item->marker == 4, force_copy_fields, + /* + If item->marker == 4 then we force create_tmp_field + to create a 64-bit longs for BIT fields because HEAP + tables can't index BIT fields directly. We do the same + for distinct, as we want the distinct index to be + usable in this case too. + */ + item->marker == 4 || param->bit_fields_as_long, // psergey-feb17 + force_copy_fields, param->convert_blob_length); if (!new_field) @@ -17838,6 +17846,17 @@ /* Add "filtered" field to item_list. */ if (join->thd->lex->describe & DESCRIBE_EXTENDED) { + /* + psergey-todo: + in the code above, we cast to integer when asssigning to + examined_rows. + In the code below, we may divide original value but result of + conversion of the same value to integer, which may produce a + value that's greater than 100%, which looks very odd. + I'm not fixing this right away because that might trigger a wave + of small EXPLAIN EXTENDED output changes, which I don't have time + to deal with right now. + */ float f= 0.0; if (examined_rows) f= (float) (100.0 * join->best_positions[i].records_read / === modified file 'sql/sql_union.cc' --- a/sql/sql_union.cc 2010-01-28 13:48:33 +0000 +++ b/sql/sql_union.cc 2010-02-17 21:59:41 +0000 @@ -103,6 +103,8 @@ is_union_distinct if set, the temporary table will eliminate duplicates on insert options create options + table_alias name of the temporary table + bit_fields_as_long convert bit fields to ulonglong DESCRIPTION Create a temporary table that is used to store the result of a UNION, @@ -116,11 +118,13 @@ bool select_union::create_result_table(THD *thd_arg, List<Item> *column_types, bool is_union_distinct, ulonglong options, - const char *alias) + const char *alias, + bool bit_fields_as_long) { DBUG_ASSERT(table == 0); tmp_table_param.init(); tmp_table_param.field_count= column_types->elements; + tmp_table_param.bit_fields_as_long= bit_fields_as_long; if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types, (ORDER*) 0, is_union_distinct, 1, @@ -350,7 +354,7 @@ create_options= create_options | TMP_TABLE_FORCE_MYISAM; if (union_result->create_result_table(thd, &types, test(union_distinct), - create_options, "")) + create_options, "", FALSE)) goto err; bzero((char*) &result_table_list, sizeof(result_table_list)); result_table_list.db= (char*) "";