3784 Roy Lyseng 2012-03-15
Bug#13468414: Query shows different results when literals are selected from
separate table.
The problem occurs when cache_const_exprs() adds cache objects to the
query condition. It attempts to build a cache object on top of an
Item_ref object, but ends up building it on top of the above item, which
is an equality check, and should not be const. Hence, the evaluation
of the first equality is used for every row in the subquery, allowing
every IN subquery predicate to evaluate to true instead of false.
This occurs because compile() returns NULL when the analyzer sets
cache_flag=True for an Item_direct_ref object. The analyzer for the
descendent object (an Item_cache) then returns false, which makes
compile return NULL, which eventually makes the above object (an
Item_func_eq) be wrapped in an Item_cache object.
The fix for this is to distinguish error handling and false return
from the analyzer function better: In compile(), when the analyzer
returns false, return "this" (current return value is NULL).
Also let all NULL returns represent an error.
Other problems have also been fixed:
- A cache object might be built on top of another cache, which is
useless.
- cache_const_exprs() was earlier called before make_join_select(),
then was changed (in some unidentified change set) to be called
after it, and thus, at a stage where conditions have been
propagated to individual tables. So we should apply the transformer
to those conditions, instead of to conds and the JOIN conditions.
- cache_const_exprs() did not have proper error handling, it now returns
true when out-of-memory.
mysql-test/include/subquery.inc
Test case for bug#13468414.
Test case for bug#13735704, which was marked as a duplicate.
mysql-test/r/explain_json_all.result
mysql-test/r/explain_json_none.result
Redundant cache entries are now removed.
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_bka.result
mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_bka.result
mysql-test/r/subquery_none_bka_nixbnl.result
Test case results for bug#13468414.
Test case results for bug#13735704.
Few minor changes for explain extended: In one case, a cache object
had been added to an expression in conds, that already had been
optimized away when propagating conditions to individual tables.
Explain extended reports from the WHERE condition, and not from
the propagated conditions. Adding the cache object was obviously
redundant.
sql/item.cc
In Item::cache_const_expr_analyzer(), do not build a cache object
on top of another cache object.
In Item_ref::compile, return "this" even when analyzer reports false.
replaced the bool flag with an Item * pointer, so that we assert
that the Item * is equal to "this" when appropriate.
sql/item.h
In Item::compile, return "this" even when analyzer reports false.
sql/item_cmpfunc.cc
In Item_cond::compile, return "this" even when analyzer reports false.
Return NULL when compile() on sub-item reports error.
sql/item_func.cc
In Item_func::compile, return "this" even when analyzer reports false.
Return NULL when compile() on sub-item reports error.
sql/sql_optimizer.cc
In JOIN::cache_const_exprs(), apply transform to conditions pushed
down to tables, instead of to conds and join conditions.
Let the analyzer/transformer argument be an Item * instead of a bool.
Add error handling.
sql/sql_optimizer.h
Interface change for JOIN::cache_const_exprs().
modified:
mysql-test/include/subquery.inc
mysql-test/r/explain_json_all.result
mysql-test/r/explain_json_none.result
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_bka.result
mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_bka.result
mysql-test/r/subquery_none_bka_nixbnl.result
sql/item.cc
sql/item.h
sql/item_cmpfunc.cc
sql/item_func.cc
sql/sql_optimizer.cc
sql/sql_optimizer.h
3783 Roy Lyseng 2012-03-15
Bug#13773979: Missing rows on second execution of prepared statement
This problem may occur with prepared statements that reference views
and perform semi-join transformation.
A field reference through a view is replaced during resolving for each
execution of the prepared statement. Semi-join transformation occurs
after resolving, so the fix_after_pullout() stage applied to all
Item_field objects will not be carried out. fix_after_pullout() builds
a new resolver context for the fields, but obviously this is not updated
for the replaced field. On second resolving, the select_lex context
refers to the deleted select_lex and it appears that the field comes
from an outer table.
The fix is to update the select_lex pointer in the context object
that is contained in the select_lex object which will be removed,
to point to the "parent" select_lex object.
This context object is used for every field reference that is not
a join condition. I checked with view references in join conditions as
well, but apparently these do not cause any trouble.
mysql-test/include/subquery_sj.inc
Added test case for bug#13773979.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
Added test case results for bug#13773979.
sql/item.cc
Removed an assert because it no longer holds when resolver context
is updated earlier.
sql/sql_optimizer.cc
In convert_subquery_to_semijoin(), update the select_lex pointer in
the context object that is contained in the deleted select_lex object,
to point to the "parent" select_lex object.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
sql/item.cc
sql/sql_optimizer.cc
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc 2012-03-08 09:44:21 +0000
+++ b/mysql-test/include/subquery.inc 2012-03-15 14:41:06 +0000
@@ -5890,3 +5890,101 @@ WHERE d LIKE '_'
ORDER BY d;
DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#13468414 Query shows different results when literals are selected
+--echo # from separate table
+--echo #
+
+CREATE TABLE t1 (
+ col_varchar_key varchar(1) NOT NULL,
+ col_varchar_nokey varchar(1) NOT NULL,
+ KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+ ('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+ ('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+ ('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+ ('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+
+CREATE TABLE t2 (
+ col_int_nokey INT NOT NULL,
+ col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES
+ (4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+ (3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+ (1,'q'), (6,'w'), (2,'d'), (9,'e');
+
+CREATE TABLE t0 (
+ i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+ (SELECT it2.col_int_nokey
+ FROM t2 AS it2 LEFT JOIN t1 AS it1
+ ON it2.col_varchar_nokey = it1.col_varchar_key
+ );
+
+let $query=
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+ (SELECT it2.col_int_nokey
+ FROM t2 AS it2 LEFT JOIN t1 AS it1
+ ON it2.col_varchar_nokey = it1.col_varchar_key
+ );
+
+eval $query;
+eval explain extended $query;
+
+DROP TABLE t0, t1, t2;
+
+--echo #
+--echo # Bug#13735704 Memory engine + NOT IN + subquery produces different
+--echo # result set
+--echo #
+
+CREATE TABLE t1(
+ col_varchar_key varchar(1),
+ KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+ ('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+
+CREATE TABLE t2(
+ col_int_nokey int,
+ col_int_key int,
+ KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+ (4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+ (156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+ (7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+ (6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+ (1,7), (9,2), (0,1), (6,5);
+
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+ (SELECT col_int_key,col_int_nokey FROM t2);
+
+let $query=
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+ (SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+ (SELECT col_int_key,col_int_nokey FROM t2);
+
+eval explain extended $query;
+eval $query;
+
+DROP TABLE t0, t1, t2;
=== modified file 'mysql-test/r/explain_json_all.result'
--- a/mysql-test/r/explain_json_all.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/explain_json_all.result 2012-03-15 14:41:06 +0000
@@ -750,7 +750,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 2,
"filtered": 100,
- "attached_condition": "(`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, (<cache>((<cache>((select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`)) or isnull(`test`.`t3`.`e`)), true))"
+ "attached_condition": "(`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, ((<cache>((select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true))"
} /* table */
} /* query_block */
},
@@ -786,7 +786,7 @@ EXPLAIN
}
Warnings:
Note 1276 Field or reference 'test.t1.b' of SELECT #4 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, (<cache>((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`)) or isnull(`test`.`t3`.`e`)), true)) having trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true)) having trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))))
DROP TABLE t1, t2, t3, t4;
# semi-join materialization (if enabled)
CREATE TABLE t1 (a INT);
=== modified file 'mysql-test/r/explain_json_none.result'
--- a/mysql-test/r/explain_json_none.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/explain_json_none.result 2012-03-15 14:41:06 +0000
@@ -761,7 +761,7 @@ EXPLAIN
"access_type": "ALL",
"rows": 2,
"filtered": 100,
- "attached_condition": "(`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, (<cache>((<cache>((select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`)) or isnull(`test`.`t3`.`e`)), true))"
+ "attached_condition": "(`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, ((<cache>((select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true))"
} /* table */
} /* query_block */
},
@@ -788,7 +788,7 @@ EXPLAIN
}
Warnings:
Note 1276 Field or reference 'test.t1.b' of SELECT #4 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, (<cache>((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`)) or isnull(`test`.`t3`.`e`)), true)) having trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true)) having trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
DROP TABLE t1, t2, t3, t4;
# semi-join materialization (if enabled)
CREATE TABLE t1 (a INT);
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2012-03-08 09:44:21 +0000
+++ b/mysql-test/r/subquery_all.result 2012-03-15 14:41:06 +0000
@@ -736,7 +736,7 @@ id select_type table type possible_keys
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
-Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
@@ -6789,7 +6789,7 @@ id select_type table type possible_keys
1 SIMPLE it ref idx_cvk_cik idx_cvk_cik 4 const 1 100.00 Using index; FirstMatch(ot)
Warnings:
Note 1276 Field or reference 'test.ot.col_int_nokey' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and <cache>(coalesce('1',1)))
+Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and coalesce('1',1))
DROP TABLE it;
CREATE TABLE it (
col_int_key int(11),
@@ -7171,4 +7171,108 @@ WHERE d LIKE '_'
ORDER BY d;
d
DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+# from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 100.00 Using where
+3 SUBQUERY it2 ALL NULL NULL NULL NULL 20 100.00 NULL
+3 SUBQUERY it1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 100.00 Using index
+2 SUBQUERY t0 system PRIMARY NULL NULL NULL 1 100.00 NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),(/* select#2 */ select '7' from dual where 1) in ( <materialize> (/* select#3 */ select `test`.`it2`.`col_int_nokey` from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it2`.`col_varchar_nokey` = `test`.`it1`.`col_varchar_key`)) where 1 ), <primary_index_lookup>(<cache>('7') in <temporary table> on distinct_key where ((<cache>('7') = `materialized subselect`.`col_int_nokey`)))))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+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
+4 DEPENDENT SUBQUERY t2 index_subquery col_int_key col_int_key 5 const 4 100.00 Using where; Full scan on NULL key
+3 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+2 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result 2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_all_bka.result 2012-03-15 14:41:06 +0000
@@ -737,7 +737,7 @@ id select_type table type possible_keys
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
-Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
@@ -6790,7 +6790,7 @@ id select_type table type possible_keys
1 SIMPLE it ref idx_cvk_cik idx_cvk_cik 4 const 1 100.00 Using index; FirstMatch(ot)
Warnings:
Note 1276 Field or reference 'test.ot.col_int_nokey' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and <cache>(coalesce('1',1)))
+Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and coalesce('1',1))
DROP TABLE it;
CREATE TABLE it (
col_int_key int(11),
@@ -7172,5 +7172,109 @@ WHERE d LIKE '_'
ORDER BY d;
d
DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+# from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 100.00 Using where
+3 SUBQUERY it2 ALL NULL NULL NULL NULL 20 100.00 NULL
+3 SUBQUERY it1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 100.00 Using index
+2 SUBQUERY t0 system PRIMARY NULL NULL NULL 1 100.00 NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),(/* select#2 */ select '7' from dual where 1) in ( <materialize> (/* select#3 */ select `test`.`it2`.`col_int_nokey` from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it2`.`col_varchar_nokey` = `test`.`it1`.`col_varchar_key`)) where 1 ), <primary_index_lookup>(<cache>('7') in <temporary table> on distinct_key where ((<cache>('7') = `materialized subselect`.`col_int_nokey`)))))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+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
+4 DEPENDENT SUBQUERY t2 index_subquery col_int_key col_int_key 5 const 4 100.00 Using where; Full scan on NULL key
+3 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+2 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_all_bka_nixbnl.result 2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_all_bka_nixbnl.result 2012-03-15 14:41:06 +0000
@@ -737,7 +737,7 @@ id select_type table type possible_keys
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
-Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
@@ -6790,7 +6790,7 @@ id select_type table type possible_keys
1 SIMPLE it ref idx_cvk_cik idx_cvk_cik 4 const 1 100.00 Using index; FirstMatch(ot)
Warnings:
Note 1276 Field or reference 'test.ot.col_int_nokey' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and <cache>(coalesce('1',1)))
+Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and coalesce('1',1))
DROP TABLE it;
CREATE TABLE it (
col_int_key int(11),
@@ -7172,5 +7172,109 @@ WHERE d LIKE '_'
ORDER BY d;
d
DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+# from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 100.00 Using where
+3 SUBQUERY it2 ALL NULL NULL NULL NULL 20 100.00 NULL
+3 SUBQUERY it1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 100.00 Using index
+2 SUBQUERY t0 system PRIMARY NULL NULL NULL 1 100.00 NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),(/* select#2 */ select '7' from dual where 1) in ( <materialize> (/* select#3 */ select `test`.`it2`.`col_int_nokey` from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it2`.`col_varchar_nokey` = `test`.`it1`.`col_varchar_key`)) where 1 ), <primary_index_lookup>(<cache>('7') in <temporary table> on distinct_key where ((<cache>('7') = `materialized subselect`.`col_int_nokey`)))))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+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
+4 DEPENDENT SUBQUERY t2 index_subquery col_int_key col_int_key 5 const 4 100.00 Using where; Full scan on NULL key
+3 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+2 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result 2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result 2012-03-15 14:41:06 +0000
@@ -736,7 +736,7 @@ id select_type table type possible_keys
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
-Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
@@ -7171,4 +7171,108 @@ WHERE d LIKE '_'
ORDER BY d;
d
DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+# from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+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
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 20 100.00 Using where
+3 DEPENDENT SUBQUERY it1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 100.00 Using index
+2 SUBQUERY t0 system PRIMARY NULL NULL NULL 1 100.00 NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+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
+4 DEPENDENT SUBQUERY t2 index_subquery col_int_key col_int_key 5 const 4 100.00 Using where; Full scan on NULL key
+3 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+2 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj_bka.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka.result 2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka.result 2012-03-15 14:41:06 +0000
@@ -737,7 +737,7 @@ id select_type table type possible_keys
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
-Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
@@ -7172,5 +7172,109 @@ WHERE d LIKE '_'
ORDER BY d;
d
DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+# from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+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
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 20 100.00 Using where
+3 DEPENDENT SUBQUERY it1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 100.00 Using index
+2 SUBQUERY t0 system PRIMARY NULL NULL NULL 1 100.00 NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+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
+4 DEPENDENT SUBQUERY t2 index_subquery col_int_key col_int_key 5 const 4 100.00 Using where; Full scan on NULL key
+3 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+2 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result 2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result 2012-03-15 14:41:06 +0000
@@ -737,7 +737,7 @@ id select_type table type possible_keys
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
-Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
@@ -7172,5 +7172,109 @@ WHERE d LIKE '_'
ORDER BY d;
d
DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+# from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+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
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 20 100.00 Using where
+3 DEPENDENT SUBQUERY it1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 100.00 Using index
+2 SUBQUERY t0 system PRIMARY NULL NULL NULL 1 100.00 NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+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
+4 DEPENDENT SUBQUERY t2 index_subquery col_int_key col_int_key 5 const 4 100.00 Using where; Full scan on NULL key
+3 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+2 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result 2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_none.result 2012-03-15 14:41:06 +0000
@@ -735,7 +735,7 @@ id select_type table type possible_keys
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
-Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
@@ -7170,4 +7170,108 @@ WHERE d LIKE '_'
ORDER BY d;
d
DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+# from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+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
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 20 100.00 Using where
+3 DEPENDENT SUBQUERY it1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 100.00 Using index
+2 SUBQUERY t0 system PRIMARY NULL NULL NULL 1 100.00 NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+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
+4 DEPENDENT SUBQUERY t2 index_subquery col_int_key col_int_key 5 const 4 100.00 Using where; Full scan on NULL key
+3 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+2 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none_bka.result'
--- a/mysql-test/r/subquery_none_bka.result 2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_none_bka.result 2012-03-15 14:41:06 +0000
@@ -736,7 +736,7 @@ id select_type table type possible_keys
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
-Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
@@ -7171,5 +7171,109 @@ WHERE d LIKE '_'
ORDER BY d;
d
DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+# from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+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
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 20 100.00 Using where
+3 DEPENDENT SUBQUERY it1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 100.00 Using index
+2 SUBQUERY t0 system PRIMARY NULL NULL NULL 1 100.00 NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+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
+4 DEPENDENT SUBQUERY t2 index_subquery col_int_key col_int_key 5 const 4 100.00 Using where; Full scan on NULL key
+3 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+2 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_none_bka_nixbnl.result 2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_none_bka_nixbnl.result 2012-03-15 14:41:06 +0000
@@ -736,7 +736,7 @@ id select_type table type possible_keys
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
-Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note 1003 /* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
@@ -7171,5 +7171,109 @@ WHERE d LIKE '_'
ORDER BY d;
d
DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+# from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+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
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 20 100.00 Using where
+3 DEPENDENT SUBQUERY it1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 100.00 Using index
+2 SUBQUERY t0 system PRIMARY NULL NULL NULL 1 100.00 NULL
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+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
+4 DEPENDENT SUBQUERY t2 index_subquery col_int_key col_int_key 5 const 4 100.00 Using where; Full scan on NULL key
+3 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+2 SUBQUERY t0 const PRIMARY PRIMARY 4 const 1 100.00 Using index
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2012-03-15 13:15:19 +0000
+++ b/sql/item.cc 2012-03-15 14:41:06 +0000
@@ -6709,7 +6709,7 @@ bool Item::send(Protocol *protocol, Stri
/**
Check if an item is a constant one and can be cached.
- @param arg [out] TRUE <=> Cache this item.
+ @param arg [out] != NULL <=> Cache this item.
@return TRUE Go deeper in item tree.
@return FALSE Don't go deeper in item tree.
@@ -6717,18 +6717,19 @@ bool Item::send(Protocol *protocol, Stri
bool Item::cache_const_expr_analyzer(uchar **arg)
{
- bool *cache_flag= (bool*)*arg;
- if (!*cache_flag)
+ Item **cache_item= (Item **)*arg;
+ if (!*cache_item)
{
Item *item= real_item();
/*
Cache constant items unless it's a basic constant, constant field or
- a subselect (they use their own cache).
+ a subquery (they use their own cache), or it is already cached.
*/
if (const_item() &&
!(basic_const_item() || item->basic_const_item() ||
item->type() == Item::FIELD_ITEM ||
item->type() == SUBSELECT_ITEM ||
+ item->type() == CACHE_ITEM ||
/*
Do not cache GET_USER_VAR() function as its const_item() may
return TRUE for the current thread but it still may change
@@ -6736,17 +6737,32 @@ bool Item::cache_const_expr_analyzer(uch
*/
(item->type() == Item::FUNC_ITEM &&
((Item_func*)item)->functype() == Item_func::GUSERVAR_FUNC)))
- *cache_flag= TRUE;
- return TRUE;
+ /*
+ Note that we use cache_item as a flag (NULL vs non-NULL), but we
+ are storing the pointer so that we can assert that we cache the
+ correct item in Item::cache_const_expr_transformer().
+ */
+ *cache_item= this;
+ /*
+ If this item will be cached, no need to explore items further down
+ in the tree, but the transformer must be called, so return 'true'.
+ If this item will not be cached, items further doen in the tree
+ must be explored, so return 'true'.
+ */
+ return true;
}
- return FALSE;
+ /*
+ An item above in the tree is to be cached, so need to cache the present
+ item, and no need to go down the tree.
+ */
+ return false;
}
/**
Cache item if needed.
- @param arg TRUE <=> Cache this item.
+ @param arg != NULL <=> Cache this item.
@return cache if cache needed.
@return this otherwise.
@@ -6754,9 +6770,15 @@ bool Item::cache_const_expr_analyzer(uch
Item* Item::cache_const_expr_transformer(uchar *arg)
{
- if (*(bool*)arg)
+ Item **item= (Item **)arg;
+ if (*item) // Item is to be cached, note that it is used as a flag
{
- *((bool*)arg)= FALSE;
+ DBUG_ASSERT(*item == this);
+ /*
+ Flag applies to present item, must reset it so it does not affect
+ the parent item.
+ */
+ *((Item **)arg)= NULL;
Item_cache *cache= Item_cache::get_cache(this);
if (!cache)
return NULL;
@@ -7261,18 +7283,19 @@ Item* Item_ref::transform(Item_transform
nodes of the tree of the object
@param arg_t parameter to be passed to the transformer
- @return Item returned as the result of transformation of the Item_ref object
+ @return Item returned as the result of transformation of the Item_ref object,
+ or NULL if error.
*/
Item* Item_ref::compile(Item_analyzer analyzer, uchar **arg_p,
Item_transformer transformer, uchar *arg_t)
{
if (!(this->*analyzer)(arg_p))
- return NULL;
+ return this;
DBUG_ASSERT((*ref) != NULL);
Item *new_item= (*ref)->compile(analyzer, arg_p, transformer, arg_t);
- if (!new_item)
+ if (new_item == NULL)
return NULL;
/*
=== modified file 'sql/item.h'
--- a/sql/item.h 2012-03-06 14:29:42 +0000
+++ b/sql/item.h 2012-03-15 14:41:06 +0000
@@ -1196,19 +1196,23 @@ public:
if (this->*some_analyzer(...))
{
compile children if any;
- this->*some_transformer(...);
+ return this->*some_transformer(...);
}
+ else
+ return this;
}
i.e. analysis is performed top-down while transformation is done
- bottom-up.
+ bottom-up. If no transformation is applied, the item is returned unchanged.
+ A transformation error is indicated by returning a NULL pointer. Notice
+ that the analyzer function should never cause an error.
*/
virtual Item* compile(Item_analyzer analyzer, uchar **arg_p,
Item_transformer transformer, uchar *arg_t)
{
if ((this->*analyzer) (arg_p))
return ((this->*transformer) (arg_t));
- return 0;
+ return this;
}
virtual void traverse_cond(Cond_traverser traverser,
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc 2012-03-06 14:29:42 +0000
+++ b/sql/item_cmpfunc.cc 2012-03-15 14:41:06 +0000
@@ -4922,15 +4922,16 @@ Item *Item_cond::transform(Item_transfor
nodes of the tree of the object
@param arg_t parameter to be passed to the transformer
- @return
- Item returned as the result of transformation of the root node
+ @return Item returned as result of transformation of the node,
+ the same item if no transformation applied, or NULL if
+ transformation caused an error.
*/
Item *Item_cond::compile(Item_analyzer analyzer, uchar **arg_p,
Item_transformer transformer, uchar *arg_t)
{
if (!(this->*analyzer)(arg_p))
- return 0;
+ return this;
List_iterator<Item> li(list);
Item *item;
@@ -4942,7 +4943,9 @@ Item *Item_cond::compile(Item_analyzer a
*/
uchar *arg_v= *arg_p;
Item *new_item= item->compile(analyzer, &arg_v, transformer, arg_t);
- if (new_item && new_item != item)
+ if (new_item == NULL)
+ return NULL;
+ if (new_item != item)
current_thd->change_item_tree(li.ref(), new_item);
}
return Item_func::transform(transformer, arg_t);
=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc 2012-03-14 09:52:49 +0000
+++ b/sql/item_func.cc 2012-03-15 14:41:06 +0000
@@ -371,15 +371,16 @@ Item *Item_func::transform(Item_transfor
nodes of the tree of the object
@param arg_t parameter to be passed to the transformer
- @return
- Item returned as the result of transformation of the root node
+ @return Item returned as result of transformation of the node,
+ the same item if no transformation applied, or NULL if
+ transformation caused an error.
*/
Item *Item_func::compile(Item_analyzer analyzer, uchar **arg_p,
Item_transformer transformer, uchar *arg_t)
{
if (!(this->*analyzer)(arg_p))
- return 0;
+ return this;
if (arg_count)
{
Item **arg,**arg_end;
@@ -391,7 +392,9 @@ Item *Item_func::compile(Item_analyzer a
*/
uchar *arg_v= *arg_p;
Item *new_item= (*arg)->compile(analyzer, &arg_v, transformer, arg_t);
- if (new_item && *arg != new_item)
+ if (new_item == NULL)
+ return NULL;
+ if (*arg != new_item)
current_thd->change_item_tree(arg, new_item);
}
}
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-03-15 13:15:19 +0000
+++ b/sql/sql_optimizer.cc 2012-03-15 14:41:06 +0000
@@ -763,7 +763,8 @@ JOIN::optimize()
}
/* Cache constant expressions in WHERE, HAVING, ON clauses. */
- cache_const_exprs();
+ if (cache_const_exprs())
+ DBUG_RETURN(1);
/*
is this simple IN subquery?
@@ -6824,36 +6825,48 @@ void JOIN::drop_unused_derived_keys()
/**
Cache constant expressions in WHERE, HAVING, ON conditions.
+
+ @return False if success, True if error
+
+ @note This function is run after conditions have been pushed down to
+ individual tables, so transformation is applied to JOIN_TAB::condition
+ and not to the WHERE condition.
*/
-void JOIN::cache_const_exprs()
+bool JOIN::cache_const_exprs()
{
- bool cache_flag= FALSE;
- bool *analyzer_arg= &cache_flag;
-
/* No need in cache if all tables are constant. */
if (const_tables == tables)
- return;
+ return false;
- if (conds)
- conds->compile(&Item::cache_const_expr_analyzer, (uchar **)&analyzer_arg,
- &Item::cache_const_expr_transformer, (uchar *)&cache_flag);
- cache_flag= FALSE;
+ for (uint i= const_tables; i < tables; i++)
+ {
+ Item *condition= join_tab[i].condition();
+ if (condition == NULL)
+ continue;
+ Item *cache_item= NULL;
+ Item **analyzer_arg= &cache_item;
+ condition=
+ condition->compile(&Item::cache_const_expr_analyzer,
+ (uchar **)&analyzer_arg,
+ &Item::cache_const_expr_transformer,
+ (uchar *)&cache_item);
+ if (condition == NULL)
+ return true;
+ if (condition != join_tab[i].condition())
+ join_tab[i].set_condition(condition, __LINE__);
+ }
if (having)
- having->compile(&Item::cache_const_expr_analyzer, (uchar **)&analyzer_arg,
- &Item::cache_const_expr_transformer, (uchar *)&cache_flag);
-
- for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables ; tab++)
{
- if (*tab->on_expr_ref)
- {
- cache_flag= FALSE;
- (*tab->on_expr_ref)->compile(&Item::cache_const_expr_analyzer,
- (uchar **)&analyzer_arg,
- &Item::cache_const_expr_transformer,
- (uchar *)&cache_flag);
- }
+ Item *cache_item= NULL;
+ Item **analyzer_arg= &cache_item;
+ having=
+ having->compile(&Item::cache_const_expr_analyzer, (uchar **)&analyzer_arg,
+ &Item::cache_const_expr_transformer,(uchar *)&cache_item);
+ if (having == NULL)
+ return true;
}
+ return false;
}
=== modified file 'sql/sql_optimizer.h'
--- a/sql/sql_optimizer.h 2012-02-29 11:17:52 +0000
+++ b/sql/sql_optimizer.h 2012-03-15 14:41:06 +0000
@@ -563,7 +563,7 @@ public:
return (unit == &thd->lex->unit && (unit->fake_select_lex == 0 ||
select_lex == unit->fake_select_lex));
}
- void cache_const_exprs();
+ bool cache_const_exprs();
bool generate_derived_keys();
void drop_unused_derived_keys();
bool get_best_combination();
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3783 to 3784) Bug#13468414 | Roy Lyseng | 15 Mar |
