[Commits] Rev 3681: Fix bug MDEV-4648 SQ pushdown: Wrong result (missing rows) with materialization+semijoin, IN and ALL subqueries, UNION in file:///home/tsk/mprog/src/10.0-md83/

timour at askmonty.org timour at askmonty.org
Fri Aug 9 15:54:39 EEST 2013


At file:///home/tsk/mprog/src/10.0-md83/

------------------------------------------------------------
revno: 3681
revision-id: timour at askmonty.org-20130809113013-0iulcwv220lww3sn
parent: timour at askmonty.org-20130611081135-tym2k9fo1f2t9w30
fixes bug: https://mariadb.atlassian.net/browse/MDEV-4648
committer: timour at askmonty.org
branch nick: 10.0-md83
timestamp: Fri 2013-08-09 14:30:13 +0300
message:
  Fix bug MDEV-4648 SQ pushdown: Wrong result (missing rows) with materialization+semijoin, IN and ALL subqueries, UNION
  (part of MDEV-83)
  
  Analysis:
  
  The cause of the wrong result was that the ALL subquery was incorrectly
  attached to the last table in the join plan <t1, sj-mat, t2>. The ALL
  subquery was transformed into a MIN expression, however it still depended
  on OUTER_REF_TABLE_BIT as if it was a subquery. The reason for this
  dependence was that the transformed MIN expression referred to the
  Item_cache object of the left subquery operand created for the initial
  Item_in_optimizer.
  
  Solution:
  
  When transforming an ALL subquery into a MIN/MAX expression, use the
  actual left argument expression, instead of the Item_cache created
  for the subquery.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2013-05-20 19:06:22 +0000
+++ b/mysql-test/r/subselect.result	2013-08-09 11:30:13 +0000
@@ -279,7 +279,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       100.00  
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2);
 a
 7
@@ -1640,7 +1640,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1648,7 +1648,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1659,7 +1659,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1667,7 +1667,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1710,7 +1710,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    4       100.00  Using temporary
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= `test`.`t3`.`a`)))
 drop table t2, t3;
 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY  (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
 INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());

=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2013-06-11 08:11:35 +0000
+++ b/mysql-test/r/subselect4.result	2013-08-09 11:30:13 +0000
@@ -2356,7 +2356,7 @@ id	select_type	table	type	possible_keys
 3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    16      100.00  
 Warnings:
 Note    1276    Field or reference 'b' of SELECT #4 was resolved in SELECT #1
-Note    1003    select count(0) AS `cnt` from `test`.`t1` where (<cache>(<nop>(<in_optimizer>(9,((select max(`test`.`t1`.`a`) from `test`.`t1`) > <cache>(9))))) or <expr_cache><9>(exists(select 1 from `test`.`t3` `t3_1` join `test`.`t3` `t3_2` where ((`test`.`t3_2`.`c` = `test`.`t3_1`.`c`) and (`test`.`t3_1`.`c` <> 9))))) order by count(0)
+Note    1003    select count(0) AS `cnt` from `test`.`t1` where (<cache>(<nop>(<in_optimizer>(9,((select max(`test`.`t1`.`a`) from `test`.`t1`) > 9)))) or <expr_cache><9>(exists(select 1 from `test`.`t3` `t3_1` join `test`.`t3` `t3_2` where ((`test`.`t3_2`.`c` = `test`.`t3_1`.`c`) and (`test`.`t3_1`.`c` <> 9))))) order by count(0)
 SELECT  COUNT(*) AS cnt FROM t1, ( SELECT b FROM t2 ) AS t2_sq
 WHERE b < ANY ( SELECT a FROM t1, t2 ) 
 OR EXISTS ( SELECT 1 FROM t3 t3_1, t3 t3_2 WHERE t3_2.c = t3_1.c AND t3_2.c <> b )  
@@ -2377,7 +2377,7 @@ id	select_type	table	type	possible_keys
 3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    16      100.00  
 Warnings:
 Note    1276    Field or reference 'b' of SELECT #4 was resolved in SELECT #1
-Note    1003    select count(9) AS `COUNT(b)` from `test`.`t1` where (<cache>(<nop>(<in_optimizer>(9,((select max(`test`.`t1`.`a`) from `test`.`t1`) > <cache>(9))))) or <expr_cache><9>(exists(select 1 from `test`.`t3` `t3_1` join `test`.`t3` `t3_2` where (`test`.`t3_2`.`c` <> 9)))) group by `test`.`t1`.`a`
+Note    1003    select count(9) AS `COUNT(b)` from `test`.`t1` where (<cache>(<nop>(<in_optimizer>(9,((select max(`test`.`t1`.`a`) from `test`.`t1`) > 9)))) or <expr_cache><9>(exists(select 1 from `test`.`t3` `t3_1` join `test`.`t3` `t3_2` where (`test`.`t3_2`.`c` <> 9)))) group by `test`.`t1`.`a`
 SELECT COUNT(b)
 FROM t1, (SELECT b FROM t2) AS t2_sq
 WHERE b < ANY (SELECT a FROM t1)
@@ -2463,6 +2463,35 @@ a IN (SELECT c FROM t1 AS inner_t1, t3
 WHERE inner_t1.a = 7 OR 2 IN (SELECT a FROM t1 WHERE 0));
 a       b
 drop table t1,t2,t3;
+#
+# MDEV-4648 SQ pushdown: Wrong result (missing rows) with materialization+semijoin, IN and ALL subqueries, UNION
+#
+CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 INT, c2 CHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'c'),(6,'c');
+CREATE TABLE t3 (i3 INT, c3 CHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1,'c'),(4,'r');
+CREATE TABLE t4 (c4 CHAR(1), KEY(c4)) ENGINE=MyISAM;
+INSERT INTO t4 VALUES ('c'),('c');
+EXPLAIN
+SELECT * FROM t1, t2
+WHERE i1 IN ( SELECT i3 FROM t3, t4 WHERE c4 = c3 AND i3 < ALL ( SELECT 4 UNION SELECT 8 ) );
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
+1       PRIMARY <subquery2>     eq_ref  distinct_key    distinct_key    4       func    1       
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       Using join buffer (flat, BNL join)
+2       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    2       Using where
+2       MATERIALIZED    t4      index   c4      c4      2       NULL    2       Using where; Using index; Using join buffer (flat, BNL join)
+3       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
+4       UNION   NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
+NULL    UNION RESULT    <union3,4>      ALL     NULL    NULL    NULL    NULL    NULL    
+SELECT * FROM t1, t2
+WHERE i1 IN ( SELECT i3 FROM t3, t4 WHERE c4 = c3 AND i3 < ALL ( SELECT 4 UNION SELECT 8 ) );
+i1      i2      c2
+1       8       c
+1       6       c
+drop table t1, t2, t3, t4;
 SET optimizer_switch=@@global.optimizer_switch;
 SET optimizer_use_condition_selectivity=default;
 SET use_stat_tables=default;

=== modified file 'mysql-test/r/subselect_exists_to_in.result'
--- a/mysql-test/r/subselect_exists_to_in.result	2013-05-20 19:06:22 +0000
+++ b/mysql-test/r/subselect_exists_to_in.result	2013-08-09 11:30:13 +0000
@@ -283,7 +283,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       100.00  
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2);
 a
 7
@@ -1644,7 +1644,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1652,7 +1652,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1663,7 +1663,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1671,7 +1671,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1714,7 +1714,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    4       100.00  Using temporary
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= `test`.`t3`.`a`)))
 drop table t2, t3;
 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY  (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
 INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2013-05-20 19:06:22 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2013-08-09 11:30:13 +0000
@@ -286,7 +286,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       100.00  
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2);
 a
 7
@@ -1647,7 +1647,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1655,7 +1655,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1666,7 +1666,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1674,7 +1674,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1717,7 +1717,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    4       100.00  Using temporary
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= `test`.`t3`.`a`)))
 drop table t2, t3;
 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY  (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
 INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2013-05-20 19:06:22 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2013-08-09 11:30:13 +0000
@@ -282,7 +282,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       100.00  
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2);
 a
 7
@@ -1643,7 +1643,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1651,7 +1651,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1662,7 +1662,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1670,7 +1670,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1713,7 +1713,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    4       100.00  Using temporary
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= `test`.`t3`.`a`)))
 drop table t2, t3;
 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY  (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
 INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2013-05-20 19:06:22 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2013-08-09 11:30:13 +0000
@@ -285,7 +285,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       100.00  
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2);
 a
 7
@@ -1646,7 +1646,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1654,7 +1654,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1665,7 +1665,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1673,7 +1673,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1716,7 +1716,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    4       100.00  Using temporary
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= `test`.`t3`.`a`)))
 drop table t2, t3;
 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY  (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
 INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2013-05-20 19:06:22 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2013-08-09 11:30:13 +0000
@@ -282,7 +282,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       100.00  
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2);
 a
 7
@@ -1643,7 +1643,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2);
 a
 explain extended select * from t3 where a >= some (select b from t2);
@@ -1651,7 +1651,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where a >= all (select b from t2 group by 1);
 a
 6
@@ -1662,7 +1662,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
 select * from t3 where a >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1670,7 +1670,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
 select * from t3 where NULL >= any (select b from t2);
 a
 explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1713,7 +1713,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Subqueries: 2
 2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    4       100.00  Using temporary
 Warnings:
-Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= <cache>(`test`.`t3`.`a`))))
+Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= `test`.`t3`.`a`)))
 drop table t2, t3;
 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY  (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
 INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());

=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test	2013-06-11 08:11:35 +0000
+++ b/mysql-test/t/subselect4.test	2013-08-09 11:30:13 +0000
@@ -1973,6 +1973,32 @@ WHERE a = b AND
 
 drop table t1,t2,t3;
 
+--echo #
+--echo # MDEV-4648 SQ pushdown: Wrong result (missing rows) with materialization+semijoin, IN and ALL subqueries, UNION
+--echo #
+
+CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (i2 INT, c2 CHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'c'),(6,'c');
+
+CREATE TABLE t3 (i3 INT, c3 CHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1,'c'),(4,'r');
+
+CREATE TABLE t4 (c4 CHAR(1), KEY(c4)) ENGINE=MyISAM;
+INSERT INTO t4 VALUES ('c'),('c');
+
+EXPLAIN
+SELECT * FROM t1, t2
+WHERE i1 IN ( SELECT i3 FROM t3, t4 WHERE c4 = c3 AND i3 < ALL ( SELECT 4 UNION SELECT 8 ) );
+
+SELECT * FROM t1, t2
+WHERE i1 IN ( SELECT i3 FROM t3, t4 WHERE c4 = c3 AND i3 < ALL ( SELECT 4 UNION SELECT 8 ) );
+
+drop table t1, t2, t3, t4;
+
+
 SET optimizer_switch=@@global.optimizer_switch;
 SET optimizer_use_condition_selectivity=default;
 SET use_stat_tables=default;

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2013-05-20 19:06:22 +0000
+++ b/sql/item_cmpfunc.h	2013-08-09 11:30:13 +0000
@@ -258,6 +258,7 @@ class Item_in_optimizer: public Item_boo
   void cleanup();
   const char *func_name() const { return "<in_optimizer>"; }
   Item_cache **get_cache() { return &cache; }
+  Item *left_expr() { return args[0]; }
   void keep_top_level_cache();
   Item *transform(Item_transformer transformer, uchar *arg);
   virtual Item *expr_cache_insert_transformer(uchar *thd_arg);

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2013-06-11 08:11:35 +0000
+++ b/sql/item_subselect.cc	2013-08-09 11:30:13 +0000
@@ -2008,7 +2008,7 @@ bool Item_allany_subselect::transform_in
     The swap is needed for expressions of type 'f1 < ALL ( SELECT ....)'
     where we want to evaluate the sub query even if f1 would be null.
   */
-  subs= func->create_swap(*(optimizer->get_cache()), subs);
+  subs= func->create_swap(optimizer->left_expr(), subs);
   thd->change_item_tree(place, subs);
   if (subs->fix_fields(thd, &subs))
     DBUG_RETURN(true);



More information about the commits mailing list