[Commits] Rev 3686: Fix MDEV-4408 (part of MDEV-83): in file:///home/tsk/mprog/src/10.0-md83-lp/

timour at askmonty.org timour at askmonty.org
Thu Aug 22 13:46:45 EEST 2013


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

------------------------------------------------------------
revno: 3686
revision-id: timour at askmonty.org-20130520190622-850ovu24uhhfc30x
parent: timour at askmonty.org-20130417090331-ddp0sq0q61ztm3gj
fixes bug: https://mariadb.atlassian.net/browse/MDEV-4408
committer: timour at askmonty.org
branch nick: 10.0-md83
timestamp: Mon 2013-05-20 22:06:22 +0300
message:
  Fix MDEV-4408 (part of MDEV-83):
  SQ pushdown: Assertion `cache != __null' fails in sub_select_cache
    
  Analysis:
  The WHERE clause of outer query is determined incorrectly to be dependent
  on OUTER_REF_TABLE_BIT. This caused the WHERE clause to be attached to
  JOIN::outer_ref_cond, which is evaluated after JOIN cleanup. At the same
  time the call to curr_join->join_free() already deleted the JOIN_CACHE_BNL
  object used to execute the subquery. This late execution resulted in the
  crash.
    
  Solution:
  The fix has 3 parts, one that fixes the original problem, and two fixes for
  problems that resulted from the initial fix.
  1) The original bug is fixed by Item_cache::update_used_tables() which fixes
  the wrong dependency on OUTER_REF_TABLE_BIT.
  2) This fix led to the failed assert in Item_in_subselect::val_bool:
    DBUG_ASSERT((engine->uncacheable() & ~UNCACHEABLE_EXPLAIN) ||
                ! engine->is_executed());
  which is fixed by adding subselect_engine::make_cacheable
  3) This fix led to wrong results in subselect4.test, which are fixed by
  Item_func_trig_cond::update_used_tables()
-------------- next part --------------
=== modified file 'mysql-test/r/derived_view.result'
--- a/mysql-test/r/derived_view.result	2013-04-17 09:03:31 +0000
+++ b/mysql-test/r/derived_view.result	2013-05-20 19:06:22 +0000
@@ -1099,7 +1099,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t       system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       100.00  
-2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 Warnings:
 Note    1003    select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((6 = 7) or isnull(7))) and trigcond(((5 = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
 SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 
@@ -1113,7 +1113,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       100.00  
-3       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
+3       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 Warnings:
 Note    1003    select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((6 = 7) or isnull(7))) and trigcond(((5 = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
 SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 
@@ -1127,7 +1127,7 @@ id	select_type	table	type	possible_keys
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  
 1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       100.00  
-2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
 Warnings:
 Note    1003    select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((6 = 7) or isnull(7))) and trigcond(((5 = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
 DROP VIEW v1;

=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/having.result	2013-05-20 19:06:22 +0000
@@ -630,7 +630,7 @@ WHERE t2.f2 IN ( SELECT f3 FROM t3 )
 HAVING field1 < 's';
 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      t3      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1003    select min(`test`.`t1`.`f10`) AS `field1` from `test`.`t1` where <in_optimizer>(7,<exists>(select `test`.`t3`.`f3` from `test`.`t3` where (7 = `test`.`t3`.`f3`))) having (<cache>(`field1`) < 's')
 set optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/r/mysqld--help.result'
--- a/mysql-test/r/mysqld--help.result	2013-04-17 09:03:31 +0000
+++ b/mysql-test/r/mysqld--help.result	2013-05-20 19:06:22 +0000
@@ -1130,10 +1130,7 @@ skip-slave-start FALSE
 slave-compressed-protocol FALSE
 slave-exec-mode STRICT
 slave-max-allowed-packet 1073741824
-slave-net-timeout Warning:    8 bytes lost, allocated by T at 0 at mysys/my_malloc.c:250, sql/keycaches.cc:39, sql/keycaches.cc:201, sql/keycaches.cc:219, sql/mysqld.cc:8000, mysys/my_getopt.c:1251, mysys/my_getopt.c:182, sql/mysqld.cc:8058
-Warning: 48 bytes lost, allocated by T at 0 at sql/sql_list.h:625, sql/keycaches.cc:201, sql/keycaches.cc:219, sql/mysqld.cc:8000, mysys/my_getopt.c:1251, mysys/my_getopt.c:182, sql/mysqld.cc:8058, sql/mysqld.cc:3733
-Memory lost: 56 bytes in 2 chunks
- 3600
+slave-net-timeout 3600
 slave-skip-errors (No default value)
 slave-sql-verify-checksum TRUE
 slave-transaction-retries 10

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2013-04-17 09:03:31 +0000
+++ b/mysql-test/r/subselect.result	2013-05-20 19:06:22 +0000
@@ -6136,7 +6136,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3));
 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      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
@@ -6158,7 +6158,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-3       DEPENDENT SUBQUERY      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+3       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT * FROM ( SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)

=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2013-04-01 10:36:05 +0000
+++ b/mysql-test/r/subselect4.result	2013-05-20 19:06:22 +0000
@@ -1180,7 +1180,7 @@ FROM t1, t2
 WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
 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      t1      index_subquery  f2      f2      4       func    2       Using index
+2       SUBQUERY        t1      index_subquery  f2      f2      4       func    2       Using index; Using where
 SELECT t1.f3, MAX(t1.f2)
 FROM t1, t2
 WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
@@ -1319,8 +1319,8 @@ SELECT * FROM t2
 WHERE f1 IN (SELECT t1.f2 FROM t1 JOIN t3 ON t3.f4);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
-2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       Using where; Using join buffer (flat, BNL join)
+2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    2       Using where; Using join buffer (flat, BNL join)
 drop table t1, t2, t3;
 #
 # LP BUG#680005 Second assertion `cache != __null' failed in
@@ -1687,7 +1687,7 @@ EXPLAIN
 SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       Using temporary
+2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using temporary
 SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a);
 f1b     f2b     f3b
 10      5       d1d
@@ -1695,7 +1695,7 @@ EXPLAIN
 SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       Using temporary
+2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using temporary
 SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a);
 f1b     f2b     f3b
 10      5       d1d
@@ -2054,7 +2054,7 @@ FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.
 WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
 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      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
+2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       Using where
 SELECT t1.f4
 FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
 WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
@@ -2149,7 +2149,7 @@ EXPLAIN SELECT * FROM t2
 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3.a = 7);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       Using where
+2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    2       Using where
 3       SUBQUERY        t4      ALL     NULL    NULL    NULL    NULL    2       
 SELECT * FROM t2
 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3.a = 7);
@@ -2333,5 +2333,65 @@ SELECT x FROM t1 WHERE id > (SELECT MAX(
 x
 0
 drop table t1;
+#
+# MDEV-4408 SQ pushdown: Assertion `cache != __null' fails in sub_select_cache
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (9),(3),(4),(2),(5),(3),(1),(3),(6),(7),(5),(1),(2),(4),(9),(5);
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (9);
+CREATE TABLE t3 (c INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (4),(6);
+EXPLAIN EXTENDED
+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 )  
+ORDER BY cnt;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
+1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  Using temporary
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    16      100.00  
+4       DEPENDENT SUBQUERY      t3_1    ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+4       DEPENDENT SUBQUERY      t3_2    ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using join buffer (flat, BNL join)
+3       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    1       100.00  
+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)
+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 )  
+ORDER BY cnt;
+cnt
+16
+EXPLAIN EXTENDED
+SELECT COUNT(b)
+FROM t1, (SELECT b FROM t2) AS t2_sq
+WHERE b < ANY (SELECT a FROM t1)
+OR EXISTS (SELECT 1 FROM t3 t3_1, t3 t3_2 WHERE t3_2.c <> b)
+GROUP BY a;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
+1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       100.00  Using temporary; Using filesort
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    16      100.00  
+4       DEPENDENT SUBQUERY      t3_1    ALL     NULL    NULL    NULL    NULL    2       100.00  
+4       DEPENDENT SUBQUERY      t3_2    ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using join buffer (flat, BNL join)
+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`
+SELECT COUNT(b)
+FROM t1, (SELECT b FROM t2) AS t2_sq
+WHERE b < ANY (SELECT a FROM t1)
+OR EXISTS (SELECT 1 FROM t3 t3_1, t3 t3_2 WHERE t3_2.c <> b)
+GROUP BY a;
+COUNT(b)
+2
+2
+3
+2
+3
+1
+1
+2
+drop table t1, t2, t3;
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;

=== modified file 'mysql-test/r/subselect_exists_to_in.result'
--- a/mysql-test/r/subselect_exists_to_in.result	2013-04-17 09:03:31 +0000
+++ b/mysql-test/r/subselect_exists_to_in.result	2013-05-20 19:06:22 +0000
@@ -6144,7 +6144,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3));
 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      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
@@ -6166,7 +6166,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-3       DEPENDENT SUBQUERY      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+3       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT * FROM ( SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)

=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result	2013-04-17 09:03:31 +0000
+++ b/mysql-test/r/subselect_mat.result	2013-05-20 19:06:22 +0000
@@ -2188,7 +2188,7 @@ EXPLAIN
 SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
-3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+3       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
 (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1)
@@ -2205,7 +2205,7 @@ EXPLAIN
 SELECT (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
-3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+3       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
 SELECT (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
 (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1)

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2013-04-17 09:03:31 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2013-05-20 19:06:22 +0000
@@ -6135,7 +6135,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3));
 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      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
@@ -6157,7 +6157,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-3       DEPENDENT SUBQUERY      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+3       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT * FROM ( SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2013-04-17 09:03:31 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2013-05-20 19:06:22 +0000
@@ -6036,7 +6036,7 @@ WHERE col_varchar_nokey IN
 (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
 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      it1     index_subquery  idx_cvk_cik     idx_cvk_cik     9       func,const      2       Using index; Using where
+2       SUBQUERY        it1     index_subquery  idx_cvk_cik     idx_cvk_cik     9       func,const      2       Using index; Using where
 SELECT col_int_nokey FROM ot 
 WHERE col_varchar_nokey IN 
 (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
@@ -6048,7 +6048,7 @@ WHERE (col_varchar_nokey, 'x') IN
 (SELECT col_varchar_key, col_varchar_key2 FROM it2);
 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      it2     index_subquery  idx_cvk_cvk2_cik,idx_cvk_cik    idx_cvk_cvk2_cik        8       func,const      1       Using index; Using where
+2       SUBQUERY        it2     index_subquery  idx_cvk_cvk2_cik,idx_cvk_cik    idx_cvk_cvk2_cik        8       func,const      1       Using index; Using where
 SELECT col_int_nokey FROM ot
 WHERE (col_varchar_nokey, 'x') IN
 (SELECT col_varchar_key, col_varchar_key2 FROM it2);
@@ -6131,7 +6131,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3));
 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      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
@@ -6153,7 +6153,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-3       DEPENDENT SUBQUERY      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+3       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT * FROM ( SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2013-04-17 09:03:31 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2013-05-20 19:06:22 +0000
@@ -6142,7 +6142,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3));
 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      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
@@ -6164,7 +6164,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-3       DEPENDENT SUBQUERY      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+3       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT * FROM ( SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2013-04-17 09:03:31 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2013-05-20 19:06:22 +0000
@@ -6036,7 +6036,7 @@ WHERE col_varchar_nokey IN
 (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY ot      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      it1     index_subquery  idx_cvk_cik     idx_cvk_cik     9       func,const      2       Using index; Using where
+2       SUBQUERY        it1     index_subquery  idx_cvk_cik     idx_cvk_cik     9       func,const      2       Using index; Using where
 SELECT col_int_nokey FROM ot 
 WHERE col_varchar_nokey IN 
 (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
@@ -6048,7 +6048,7 @@ WHERE (col_varchar_nokey, 'x') IN
 (SELECT col_varchar_key, col_varchar_key2 FROM it2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY ot      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      it2     index_subquery  idx_cvk_cvk2_cik,idx_cvk_cik    idx_cvk_cvk2_cik        8       func,const      1       Using index; Using where
+2       SUBQUERY        it2     index_subquery  idx_cvk_cvk2_cik,idx_cvk_cik    idx_cvk_cvk2_cik        8       func,const      1       Using index; Using where
 SELECT col_int_nokey FROM ot
 WHERE (col_varchar_nokey, 'x') IN
 (SELECT col_varchar_key, col_varchar_key2 FROM it2);
@@ -6131,7 +6131,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3));
 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      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
@@ -6153,7 +6153,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @va
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
-3       DEPENDENT SUBQUERY      c_sq1_alias1    system  PRIMARY NULL    NULL    NULL    1       
+3       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 SELECT * FROM ( SELECT sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)

=== modified file 'mysql-test/r/subselect_partial_match.result'
--- a/mysql-test/r/subselect_partial_match.result	2013-04-17 09:03:31 +0000
+++ b/mysql-test/r/subselect_partial_match.result	2013-05-20 19:06:22 +0000
@@ -66,6 +66,7 @@ the other NULLs are in the inner columns
 create table t1 (a1 char(1), a2 char(1), a3 char(1));
 create table t2 (b1 char(1), b2 char(1), b3 char(1));
 insert into t1 values ('c',  NULL, 'a');
+insert into t1 values ('z',  'z', 'z');
 insert into t2 values (NULL,  'x', NULL);
 insert into t2 values (NULL,  'y', NULL);
 insert into t2 values ('o',   'z', 'p');
@@ -74,104 +75,111 @@ set @@optimizer_switch='partial_match_ro
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    3       
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 c       NULL    a       NULL
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    3       
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    3       
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 c       NULL    a       NULL
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    3       
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 set @@optimizer_switch=@in_exists;
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    3       Using where
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 c       NULL    a       NULL
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    3       Using where
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 set @@optimizer_switch = @test_default_opt_switch;
 drop table t1, t2;
 create table t1 (a1 char(1), a2 char(1) not null, a3 char(1));
 create table t2 (b1 char(1), b2 char(1),          b3 char(1));
 insert into t1 values (NULL,  'y', NULL);
+insert into t1 values ('z',  'z', 'z');
 insert into t2 values ('v', 'x', NULL);
 insert into t2 values (NULL, 'y', 'w');
 set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 NULL    y       NULL    NULL
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 NULL    y       NULL    NULL
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 set @@optimizer_switch=@in_exists;
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 NULL    y       NULL    NULL
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 set @@optimizer_switch = @test_default_opt_switch;
 set @@expensive_subquery_limit=default;
@@ -183,6 +191,7 @@ outer reference. It is a NULL column, so
 create table t1 (a1 char(1), a2 char(1), a3 char(1));
 create table t2 (b1 char(1), b2 char(1), b3 char(1));
 insert into t1 values (NULL,  'g', NULL);
+insert into t1 values ('z',  'z', 'z');
 insert into t2 values ('z',  NULL, 'y');
 insert into t2 values (NULL, 'z', 'y');
 set @@expensive_subquery_limit=0;
@@ -190,49 +199,52 @@ set @@optimizer_switch='partial_match_ro
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 NULL    g       NULL    NULL
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 NULL    g       NULL    NULL
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 set @@optimizer_switch=@in_exists;
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 NULL    g       NULL    NULL
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 set @@optimizer_switch = @test_default_opt_switch;
 set @@expensive_subquery_limit=default;
@@ -244,6 +256,7 @@ of NULL bits do not overlap.
 create table t1 (a1 char(1), a2 char(1), a3 char(1));
 create table t2 (b1 char(1), b2 char(1), b3 char(1));
 insert into t1 values ('b',  'g', NULL);
+insert into t1 values ('z',  'z', 'z');
 insert into t2 values ('z',  NULL, 'y');
 insert into t2 values (NULL, 'z', 'y');
 set @@expensive_subquery_limit=0;
@@ -251,51 +264,54 @@ set @@optimizer_switch='partial_match_ro
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 b       g       NULL    0
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 b       g       NULL
 set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 b       g       NULL    0
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    2       
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 b       g       NULL
 set @@optimizer_switch=@in_exists;
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 a1      a2      a3      in_res
 b       g       NULL    0
+z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3
 b       g       NULL
 set @@expensive_subquery_limit=default;
@@ -307,6 +323,7 @@ non-NULL column.
 create table t1 (a1 char(1), a2 char(1), a3 char(1) not null, a4 char(10));
 create table t2 (b1 char(1), b2 char(1), b3 char(1),          b4 char(1));
 insert into t1 values ('a',  'g', 'x', NULL);
+insert into t1 values ('z',  'z', 'z', 'z');
 insert into t2 values ('z',  NULL, 'y', 'x');
 insert into t2 values (NULL, NULL, 'x', 'y');
 insert into t2 values ('x',  'w', 'z', NULL);
@@ -315,50 +332,56 @@ set @@optimizer_switch='partial_match_ro
 EXPLAIN
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    3       Using where
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 a1      a2      a3      a4      in_res
 a       g       x       NULL    NULL
+z       z       z       z       0
 EXPLAIN
 SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    3       Using where
 SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
 a1      a2      a3      a4
+z       z       z       z
 set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    3       Using where
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 a1      a2      a3      a4      in_res
 a       g       x       NULL    NULL
+z       z       z       z       0
 EXPLAIN
 SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    3       Using where
 SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
 a1      a2      a3      a4
+z       z       z       z
 set @@optimizer_switch=@in_exists;
 EXPLAIN
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    3       Using where
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 a1      a2      a3      a4      in_res
 a       g       x       NULL    NULL
+z       z       z       z       0
 EXPLAIN
 SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    3       Using where
 SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
 a1      a2      a3      a4
+z       z       z       z
 set @@expensive_subquery_limit=default;
 drop table t1, t2;
 -------------------------------------------------------------------------
@@ -368,6 +391,7 @@ The result must be false.
 create table t1 (a1 char(2), a2 char(1), a3 char(1) not null, a4 char(10));
 create table t2 (b1 char(2), b2 char(1), b3 char(1),          b4 char(1));
 insert into t1 values ('99',  NULL, 'j', 'f');
+insert into t1 values ('z',  'z', 'z', 'z');
 insert into t2 values ('01',  NULL, 'y', NULL);
 insert into t2 values ('02',  NULL, 'x', 'y');
 insert into t2 values ('03',  'q', 'y', 'x');
@@ -380,51 +404,54 @@ set @@optimizer_switch='partial_match_ro
 EXPLAIN
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    8       Using where
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 a1      a2      a3      a4      in_res
 99      NULL    j       f       0
+z       z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    8       Using where
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3      a4
 99      NULL    j       f
 set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    8       Using where
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 a1      a2      a3      a4      in_res
 99      NULL    j       f       0
+z       z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    8       Using where
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3      a4
 99      NULL    j       f
 set @@optimizer_switch=@in_exists;
 EXPLAIN
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    8       Using where
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 a1      a2      a3      a4      in_res
 99      NULL    j       f       0
+z       z       z       z       0
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    8       Using where
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
 a1      a2      a3      a4
 99      NULL    j       f
 drop table t1, t2;
@@ -846,8 +873,8 @@ SELECT t3.d , t2.c
 FROM t3 LEFT JOIN t2 ON t3.a = t2.a);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      t3      system  NULL    NULL    NULL    NULL    1       
-2       DEPENDENT SUBQUERY      t2      system  NULL    NULL    NULL    NULL    1       
+2       SUBQUERY        t3      system  NULL    NULL    NULL    NULL    1       
+2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    1       
 SELECT *
 FROM t1
 WHERE (t1.d , t1.d) NOT IN (

=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2013-04-17 09:03:31 +0000
+++ b/mysql-test/r/view.result	2013-05-20 19:06:22 +0000
@@ -4507,7 +4507,7 @@ id	select_type	table	type	possible_keys
 2       DEPENDENT SUBQUERY      t4      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
-Note    1003    select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or <cache>(isnull(NULL))))) having trigcond(<is_not_null_test>(NULL)))))))
+Note    1003    select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(<cache>(((<cache>(10) = NULL) or isnull(NULL))))) having trigcond(<is_not_null_test>(NULL)))))))
 SELECT * FROM t1, t2
 WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
 WHERE t4.a >= t1.a);
@@ -4523,7 +4523,7 @@ id	select_type	table	type	possible_keys
 2       DEPENDENT SUBQUERY      t4      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 Warnings:
 Note    1276    Field or reference 'v1.a' of SELECT #2 was resolved in SELECT #1
-Note    1003    select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or <cache>(isnull(NULL))))) having trigcond(<is_not_null_test>(NULL)))))))
+Note    1003    select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(<cache>(((<cache>(10) = NULL) or isnull(NULL))))) having trigcond(<is_not_null_test>(NULL)))))))
 SELECT * FROM v1, t2
 WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
 WHERE t4.a >= v1.a);

=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result	2013-08-20 11:48:29 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result	2013-05-20 19:06:22 +0000
@@ -1436,8 +1436,8 @@ explain
 select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a;
 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      t1      system  NULL    NULL    NULL    NULL    0       const row not found
-2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    1       
+2       SUBQUERY        t1      system  NULL    NULL    NULL    NULL    0       const row not found
+2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       
 set optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1,t2;
 End of 5.0 tests

=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test	2013-03-29 15:53:21 +0000
+++ b/mysql-test/t/subselect4.test	2013-05-20 19:06:22 +0000
@@ -1878,5 +1878,44 @@ SELECT x FROM t1 WHERE id > (SELECT MAX(
 
 drop table t1;
 
+--echo #
+--echo # MDEV-4408 SQ pushdown: Assertion `cache != __null' fails in sub_select_cache
+--echo #
+
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (9),(3),(4),(2),(5),(3),(1),(3),(6),(7),(5),(1),(2),(4),(9),(5);
+
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (9);
+
+CREATE TABLE t3 (c INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (4),(6);
+
+EXPLAIN EXTENDED
+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 )  
+ORDER BY cnt;
+
+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 )  
+ORDER BY cnt;
+
+EXPLAIN EXTENDED
+SELECT COUNT(b)
+FROM t1, (SELECT b FROM t2) AS t2_sq
+WHERE b < ANY (SELECT a FROM t1)
+   OR EXISTS (SELECT 1 FROM t3 t3_1, t3 t3_2 WHERE t3_2.c <> b)
+GROUP BY a;
+
+SELECT COUNT(b)
+FROM t1, (SELECT b FROM t2) AS t2_sq
+WHERE b < ANY (SELECT a FROM t1)
+   OR EXISTS (SELECT 1 FROM t3 t3_1, t3 t3_2 WHERE t3_2.c <> b)
+GROUP BY a;
+
+drop table t1, t2, t3;
+
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;

=== modified file 'mysql-test/t/subselect_partial_match.test'
--- a/mysql-test/t/subselect_partial_match.test	2013-04-01 10:36:05 +0000
+++ b/mysql-test/t/subselect_partial_match.test	2013-05-20 19:06:22 +0000
@@ -76,6 +76,8 @@ create table t1 (a1 char(1), a2 char(1),
 create table t2 (b1 char(1), b2 char(1), b3 char(1));
 
 insert into t1 values ('c',  NULL, 'a');
+insert into t1 values ('z',  'z', 'z');
+
 insert into t2 values (NULL,  'x', NULL);
 insert into t2 values (NULL,  'y', NULL);
 insert into t2 values ('o',   'z', 'p');
@@ -87,24 +89,24 @@ EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch=@in_exists;
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch = @test_default_opt_switch;
 
@@ -114,6 +116,7 @@ create table t1 (a1 char(1), a2 char(1)
 create table t2 (b1 char(1), b2 char(1),          b3 char(1));
 
 insert into t1 values (NULL,  'y', NULL);
+insert into t1 values ('z',  'z', 'z');
 insert into t2 values ('v', 'x', NULL);
 insert into t2 values (NULL, 'y', 'w');
 
@@ -122,24 +125,24 @@ EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch=@in_exists;
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch = @test_default_opt_switch;
 set @@expensive_subquery_limit=default;
@@ -155,6 +158,7 @@ create table t1 (a1 char(1), a2 char(1),
 create table t2 (b1 char(1), b2 char(1), b3 char(1));
 
 insert into t1 values (NULL,  'g', NULL);
+insert into t1 values ('z',  'z', 'z');
 insert into t2 values ('z',  NULL, 'y');
 insert into t2 values (NULL, 'z', 'y');
 
@@ -165,24 +169,24 @@ EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch=@in_exists;
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch = @test_default_opt_switch;
 set @@expensive_subquery_limit=default;
@@ -198,6 +202,7 @@ create table t1 (a1 char(1), a2 char(1),
 create table t2 (b1 char(1), b2 char(1), b3 char(1));
 
 insert into t1 values ('b',  'g', NULL);
+insert into t1 values ('z',  'z', 'z');
 insert into t2 values ('z',  NULL, 'y');
 insert into t2 values (NULL, 'z', 'y');
 
@@ -208,24 +213,24 @@ EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch=@in_exists;
 EXPLAIN
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@expensive_subquery_limit=default;
 
@@ -240,6 +245,7 @@ create table t1 (a1 char(1), a2 char(1),
 create table t2 (b1 char(1), b2 char(1), b3 char(1),          b4 char(1));
 
 insert into t1 values ('a',  'g', 'x', NULL);
+insert into t1 values ('z',  'z', 'z', 'z');
 insert into t2 values ('z',  NULL, 'y', 'x');
 insert into t2 values (NULL, NULL, 'x', 'y');
 insert into t2 values ('x',  'w', 'z', NULL);
@@ -283,6 +289,7 @@ create table t1 (a1 char(2), a2 char(1),
 create table t2 (b1 char(2), b2 char(1), b3 char(1),          b4 char(1));
 
 insert into t1 values ('99',  NULL, 'j', 'f');
+insert into t1 values ('z',  'z', 'z', 'z');
 
 insert into t2 values ('01',  NULL, 'y', NULL);
 insert into t2 values ('02',  NULL, 'x', 'y');
@@ -298,24 +305,24 @@ EXPLAIN
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
 EXPLAIN
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
 
 set @@optimizer_switch=@in_exists;
 EXPLAIN
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
 EXPLAIN
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
-SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2) and a1 <> 'z';
 
 drop table t1, t2;
 

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2013-04-01 10:36:05 +0000
+++ b/sql/item.cc	2013-05-20 19:06:22 +0000
@@ -8733,6 +8733,17 @@ void Item_cache::set_null()
 }
 
 
+void Item_cache::update_used_tables()
+{
+  example->update_used_tables();
+  if (example->const_item())
+  {
+    used_table_map= 0;
+    maybe_null= example->maybe_null;
+  }
+}
+
+
 bool  Item_cache_int::cache_value()
 {
   if (!example)

=== modified file 'sql/item.h'
--- a/sql/item.h	2013-04-01 10:36:05 +0000
+++ b/sql/item.h	2013-05-20 19:06:22 +0000
@@ -4082,6 +4082,7 @@ class Item_cache: public Item_basic_cons
   }
 
   void set_used_tables(table_map map) { used_table_map= map; }
+  void update_used_tables();
 
   virtual bool allocate(uint i) { return 0; }
   virtual bool setup(Item *item)

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2013-04-01 10:36:05 +0000
+++ b/sql/item_cmpfunc.h	2013-05-20 19:06:22 +0000
@@ -494,8 +494,15 @@ class Item_func_trig_cond: public Item_b
   const char *func_name() const { return "trigcond"; };
   bool const_item() const { return FALSE; }
   bool *get_trig_var() { return trig_var; }
+  void update_used_tables()
+  {
+    Item_bool_func::update_used_tables();
+    const_item_cache= false;
+    used_tables_cache|= OUTER_REF_TABLE_BIT;
+  }
 };
 
+
 class Item_func_not_all :public Item_func_not
 {
   /* allow to check presence of values in max/min optimization */

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2013-04-01 10:36:05 +0000
+++ b/sql/item_subselect.cc	2013-05-20 19:06:22 +0000
@@ -3247,8 +3247,13 @@ void Item_in_subselect::update_used_tabl
 {
   Item_subselect::update_used_tables();
   left_expr->update_used_tables();
-  //used_tables_cache |= left_expr->used_tables();
   used_tables_cache= Item_subselect::used_tables() | left_expr->used_tables();
+  /*
+    Constant optimization of the outer query may transform the left IN operand
+    into a constant. The subquery is no longer uncacheable.
+  */
+  if (!used_tables_cache)
+    engine->make_cacheable();
 }
 
 
@@ -4201,12 +4206,24 @@ uint8 subselect_single_select_engine::un
 }
 
 
+void subselect_single_select_engine::make_cacheable()
+{
+  select_lex->uncacheable= 0;
+}
+
+
 uint8 subselect_union_engine::uncacheable()
 {
   return unit->uncacheable;
 }
 
 
+void subselect_union_engine::make_cacheable()
+{
+  unit->uncacheable= 0;
+}
+
+
 void subselect_single_select_engine::exclude()
 {
   select_lex->master_unit()->exclude_level();

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2013-04-01 10:36:05 +0000
+++ b/sql/item_subselect.h	2013-05-20 19:06:22 +0000
@@ -797,6 +797,7 @@ class subselect_engine: public Sql_alloc
   virtual int exec()= 0;
   virtual uint cols()= 0; /* return number of columns in select */
   virtual uint8 uncacheable()= 0; /* query is uncacheable */
+  virtual void make_cacheable() {}
   enum Item_result type() { return res_type; }
   enum Item_result cmptype() { return cmp_type; }
   enum_field_types field_type() { return res_field_type; }
@@ -837,6 +838,7 @@ class subselect_single_select_engine: pu
   int exec();
   uint cols();
   uint8 uncacheable();
+  void make_cacheable();
   void exclude();
   table_map upper_select_const_tables();
   virtual void print (String *str, enum_query_type query_type);
@@ -871,6 +873,7 @@ class subselect_union_engine: public sub
   int exec();
   uint cols();
   uint8 uncacheable();
+  void make_cacheable();
   void exclude();
   table_map upper_select_const_tables();
   virtual void print (String *str, enum_query_type query_type);



More information about the commits mailing list