#At file:///export/home/tmp/mysql/opt-bug56529-v2/ based on revid:olav.sandstaa@stripped
3281 Olav Sandstaa 2010-11-15
Fix for Bug#56529 Crash due to long semaphore wait in InnoDB with ICP and subqueries
This crash was caused by the server pushing down an index condition to
InnoDB that contained a subselect. When InnoDB executed this index
condition the subselect would open a new handler object and access
InnoDB a second time. This caused the same transaction to have two
active data accesses to InnoDB which triggered the assert in InnoDB.
The fix for this is to not push down index conditions to the handler
that contain a subselect.
@ mysql-test/include/icp_tests.inc
Test case for Bug#56529 Crash due to long semaphore wait in InnoDB
with ICP and subqueries.
@ mysql-test/r/innodb_icp.result
Result file for Test case for Bug#56529 Crash due to long semaphore
wait in InnoDB with ICP and subqueries.
@ mysql-test/r/innodb_icp_all.result
Result file for Test case for Bug#56529 Crash due to long semaphore
wait in InnoDB with ICP and subqueries.
@ mysql-test/r/innodb_icp_none.result
Result file for Test case for Bug#56529 Crash due to long semaphore
wait in InnoDB with ICP and subqueries.
@ mysql-test/r/myisam_icp.result
Result file for Test case for Bug#56529 Crash due to long semaphore
wait in InnoDB with ICP and subqueries.
@ mysql-test/r/myisam_icp_all.result
Result file for Test case for Bug#56529 Crash due to long semaphore
wait in InnoDB with ICP and subqueries.
@ mysql-test/r/myisam_icp_none.result
Result file for Test case for Bug#56529 Crash due to long semaphore
wait in InnoDB with ICP and subqueries.
@ sql/sql_select.cc
We should not push down index conditions to the handler that contains
a subselect. This was already handled by the default case of the
switch in uses_index_fields_only() but due to an earlier check in
uses_index_fields() the code for checking that the condition only
used "other tables" could return ok early.
Fix this by adding an explicite check for if the condition
contains a subselect and if so return false.
modified:
mysql-test/include/icp_tests.inc
mysql-test/r/innodb_icp.result
mysql-test/r/innodb_icp_all.result
mysql-test/r/innodb_icp_none.result
mysql-test/r/myisam_icp.result
mysql-test/r/myisam_icp_all.result
mysql-test/r/myisam_icp_none.result
sql/sql_select.cc
=== modified file 'mysql-test/include/icp_tests.inc'
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc 2010-11-08 08:49:16 +0000
+++ b/mysql-test/include/icp_tests.inc 2010-11-15 12:42:32 +0000
@@ -515,3 +515,61 @@
EXPLAIN SELECT * FROM t1 WHERE (c1 <= '6566-06-15' AND c2 <> 3);
DROP TABLE t1;
+
+--echo #
+--echo # Bug#56529 - "Crash due to long semaphore wait in InnoDB
+--echo # with ICP and subqueries"
+--echo #
+
+CREATE TABLE t1 (
+ col_int_nokey INTEGER,
+ col_int_key INTEGER,
+ col_varchar_key VARCHAR(1),
+
+ KEY (col_int_key),
+ KEY (col_varchar_key, col_int_key)
+);
+
+INSERT INTO t1 VALUES (NULL,2,'w');
+INSERT INTO t1 VALUES (7,9,'m');
+INSERT INTO t1 VALUES (9,3,'m');
+INSERT INTO t1 VALUES (7,9,'k');
+INSERT INTO t1 VALUES (4,NULL,'r');
+INSERT INTO t1 VALUES (2,9,'t');
+INSERT INTO t1 VALUES (6,3,'j');
+INSERT INTO t1 VALUES (8,8,'u');
+INSERT INTO t1 VALUES (NULL,8,'h');
+INSERT INTO t1 VALUES (5,53,'o');
+INSERT INTO t1 VALUES (NULL,0,NULL);
+INSERT INTO t1 VALUES (6,5,'k');
+INSERT INTO t1 VALUES (188,166,'e');
+INSERT INTO t1 VALUES (2,3,'n');
+INSERT INTO t1 VALUES (1,0,'t');
+INSERT INTO t1 VALUES (1,1,'c');
+INSERT INTO t1 VALUES (0,9,'m');
+INSERT INTO t1 VALUES (9,5,'y');
+INSERT INTO t1 VALUES (NULL,6,'f');
+
+CREATE TABLE t2 (
+ c1 INTEGER NOT NULL
+);
+
+let query=
+SELECT table1.col_int_nokey
+FROM
+ (
+ t1 AS table1 STRAIGHT_JOIN
+ (
+ (
+ t1 AS table2 INNER JOIN t1 AS table3
+ ON (table3.col_varchar_key = table2.col_varchar_key)
+ )
+ )
+ ON (table3.col_int_nokey = table1.col_int_key)
+ )
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2;
=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result 2010-11-08 08:49:16 +0000
+++ b/mysql-test/r/innodb_icp.result 2010-11-15 12:42:32 +0000
@@ -471,5 +471,71 @@
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,id1 id1 6 NULL 1 Using index condition; Using where
DROP TABLE t1;
+#
+# Bug#56529 - "Crash due to long semaphore wait in InnoDB
+# with ICP and subqueries"
+#
+CREATE TABLE t1 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key VARCHAR(1),
+KEY (col_int_key),
+KEY (col_varchar_key, col_int_key)
+);
+INSERT INTO t1 VALUES (NULL,2,'w');
+INSERT INTO t1 VALUES (7,9,'m');
+INSERT INTO t1 VALUES (9,3,'m');
+INSERT INTO t1 VALUES (7,9,'k');
+INSERT INTO t1 VALUES (4,NULL,'r');
+INSERT INTO t1 VALUES (2,9,'t');
+INSERT INTO t1 VALUES (6,3,'j');
+INSERT INTO t1 VALUES (8,8,'u');
+INSERT INTO t1 VALUES (NULL,8,'h');
+INSERT INTO t1 VALUES (5,53,'o');
+INSERT INTO t1 VALUES (NULL,0,NULL);
+INSERT INTO t1 VALUES (6,5,'k');
+INSERT INTO t1 VALUES (188,166,'e');
+INSERT INTO t1 VALUES (2,3,'n');
+INSERT INTO t1 VALUES (1,0,'t');
+INSERT INTO t1 VALUES (1,1,'c');
+INSERT INTO t1 VALUES (0,9,'m');
+INSERT INTO t1 VALUES (9,5,'y');
+INSERT INTO t1 VALUES (NULL,6,'f');
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table2 index col_varchar_key col_varchar_key 9 NULL 19 Using where; Using index
+1 PRIMARY table3 ref col_varchar_key col_varchar_key 4 test.table2.col_varchar_key 1 Using where
+1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 1
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
+SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+col_int_nokey
+DROP TABLE t1, t2;
set default_storage_engine= @save_storage_engine;
set optimizer_switch=default;
=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result 2010-11-08 08:49:16 +0000
+++ b/mysql-test/r/innodb_icp_all.result 2010-11-15 12:42:32 +0000
@@ -471,5 +471,71 @@
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,id1 id1 6 NULL 1 Using index condition; Using where
DROP TABLE t1;
+#
+# Bug#56529 - "Crash due to long semaphore wait in InnoDB
+# with ICP and subqueries"
+#
+CREATE TABLE t1 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key VARCHAR(1),
+KEY (col_int_key),
+KEY (col_varchar_key, col_int_key)
+);
+INSERT INTO t1 VALUES (NULL,2,'w');
+INSERT INTO t1 VALUES (7,9,'m');
+INSERT INTO t1 VALUES (9,3,'m');
+INSERT INTO t1 VALUES (7,9,'k');
+INSERT INTO t1 VALUES (4,NULL,'r');
+INSERT INTO t1 VALUES (2,9,'t');
+INSERT INTO t1 VALUES (6,3,'j');
+INSERT INTO t1 VALUES (8,8,'u');
+INSERT INTO t1 VALUES (NULL,8,'h');
+INSERT INTO t1 VALUES (5,53,'o');
+INSERT INTO t1 VALUES (NULL,0,NULL);
+INSERT INTO t1 VALUES (6,5,'k');
+INSERT INTO t1 VALUES (188,166,'e');
+INSERT INTO t1 VALUES (2,3,'n');
+INSERT INTO t1 VALUES (1,0,'t');
+INSERT INTO t1 VALUES (1,1,'c');
+INSERT INTO t1 VALUES (0,9,'m');
+INSERT INTO t1 VALUES (9,5,'y');
+INSERT INTO t1 VALUES (NULL,6,'f');
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table2 index col_varchar_key col_varchar_key 9 NULL 19 Using where; Using index
+1 PRIMARY table3 ref col_varchar_key col_varchar_key 4 test.table2.col_varchar_key 1 Using where
+1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 1
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
+SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+col_int_nokey
+DROP TABLE t1, t2;
set default_storage_engine= @save_storage_engine;
set optimizer_switch=default;
=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result 2010-11-08 08:49:16 +0000
+++ b/mysql-test/r/innodb_icp_none.result 2010-11-15 12:42:32 +0000
@@ -470,5 +470,71 @@
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,id1 id1 6 NULL 1 Using where
DROP TABLE t1;
+#
+# Bug#56529 - "Crash due to long semaphore wait in InnoDB
+# with ICP and subqueries"
+#
+CREATE TABLE t1 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key VARCHAR(1),
+KEY (col_int_key),
+KEY (col_varchar_key, col_int_key)
+);
+INSERT INTO t1 VALUES (NULL,2,'w');
+INSERT INTO t1 VALUES (7,9,'m');
+INSERT INTO t1 VALUES (9,3,'m');
+INSERT INTO t1 VALUES (7,9,'k');
+INSERT INTO t1 VALUES (4,NULL,'r');
+INSERT INTO t1 VALUES (2,9,'t');
+INSERT INTO t1 VALUES (6,3,'j');
+INSERT INTO t1 VALUES (8,8,'u');
+INSERT INTO t1 VALUES (NULL,8,'h');
+INSERT INTO t1 VALUES (5,53,'o');
+INSERT INTO t1 VALUES (NULL,0,NULL);
+INSERT INTO t1 VALUES (6,5,'k');
+INSERT INTO t1 VALUES (188,166,'e');
+INSERT INTO t1 VALUES (2,3,'n');
+INSERT INTO t1 VALUES (1,0,'t');
+INSERT INTO t1 VALUES (1,1,'c');
+INSERT INTO t1 VALUES (0,9,'m');
+INSERT INTO t1 VALUES (9,5,'y');
+INSERT INTO t1 VALUES (NULL,6,'f');
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table2 index col_varchar_key col_varchar_key 9 NULL 19 Using where; Using index
+1 PRIMARY table3 ref col_varchar_key col_varchar_key 4 test.table2.col_varchar_key 1 Using where
+1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 1
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
+SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+col_int_nokey
+DROP TABLE t1, t2;
set default_storage_engine= @save_storage_engine;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result 2010-11-08 08:49:16 +0000
+++ b/mysql-test/r/myisam_icp.result 2010-11-15 12:42:32 +0000
@@ -469,4 +469,70 @@
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,id1 id1 6 NULL 1 Using where
DROP TABLE t1;
+#
+# Bug#56529 - "Crash due to long semaphore wait in InnoDB
+# with ICP and subqueries"
+#
+CREATE TABLE t1 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key VARCHAR(1),
+KEY (col_int_key),
+KEY (col_varchar_key, col_int_key)
+);
+INSERT INTO t1 VALUES (NULL,2,'w');
+INSERT INTO t1 VALUES (7,9,'m');
+INSERT INTO t1 VALUES (9,3,'m');
+INSERT INTO t1 VALUES (7,9,'k');
+INSERT INTO t1 VALUES (4,NULL,'r');
+INSERT INTO t1 VALUES (2,9,'t');
+INSERT INTO t1 VALUES (6,3,'j');
+INSERT INTO t1 VALUES (8,8,'u');
+INSERT INTO t1 VALUES (NULL,8,'h');
+INSERT INTO t1 VALUES (5,53,'o');
+INSERT INTO t1 VALUES (NULL,0,NULL);
+INSERT INTO t1 VALUES (6,5,'k');
+INSERT INTO t1 VALUES (188,166,'e');
+INSERT INTO t1 VALUES (2,3,'n');
+INSERT INTO t1 VALUES (1,0,'t');
+INSERT INTO t1 VALUES (1,1,'c');
+INSERT INTO t1 VALUES (0,9,'m');
+INSERT INTO t1 VALUES (9,5,'y');
+INSERT INTO t1 VALUES (NULL,6,'f');
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where
+1 PRIMARY table2 ref col_varchar_key col_varchar_key 4 test.table3.col_varchar_key 2 Using index
+1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 2
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+col_int_nokey
+DROP TABLE t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result 2010-11-08 08:49:16 +0000
+++ b/mysql-test/r/myisam_icp_all.result 2010-11-15 12:42:32 +0000
@@ -469,4 +469,70 @@
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,id1 id1 6 NULL 1 Using where
DROP TABLE t1;
+#
+# Bug#56529 - "Crash due to long semaphore wait in InnoDB
+# with ICP and subqueries"
+#
+CREATE TABLE t1 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key VARCHAR(1),
+KEY (col_int_key),
+KEY (col_varchar_key, col_int_key)
+);
+INSERT INTO t1 VALUES (NULL,2,'w');
+INSERT INTO t1 VALUES (7,9,'m');
+INSERT INTO t1 VALUES (9,3,'m');
+INSERT INTO t1 VALUES (7,9,'k');
+INSERT INTO t1 VALUES (4,NULL,'r');
+INSERT INTO t1 VALUES (2,9,'t');
+INSERT INTO t1 VALUES (6,3,'j');
+INSERT INTO t1 VALUES (8,8,'u');
+INSERT INTO t1 VALUES (NULL,8,'h');
+INSERT INTO t1 VALUES (5,53,'o');
+INSERT INTO t1 VALUES (NULL,0,NULL);
+INSERT INTO t1 VALUES (6,5,'k');
+INSERT INTO t1 VALUES (188,166,'e');
+INSERT INTO t1 VALUES (2,3,'n');
+INSERT INTO t1 VALUES (1,0,'t');
+INSERT INTO t1 VALUES (1,1,'c');
+INSERT INTO t1 VALUES (0,9,'m');
+INSERT INTO t1 VALUES (9,5,'y');
+INSERT INTO t1 VALUES (NULL,6,'f');
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where
+1 PRIMARY table2 ref col_varchar_key col_varchar_key 4 test.table3.col_varchar_key 2 Using index
+1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 2
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+col_int_nokey
+DROP TABLE t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result 2010-11-08 08:49:16 +0000
+++ b/mysql-test/r/myisam_icp_none.result 2010-11-15 12:42:32 +0000
@@ -468,4 +468,70 @@
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,id1 id1 6 NULL 1 Using where
DROP TABLE t1;
+#
+# Bug#56529 - "Crash due to long semaphore wait in InnoDB
+# with ICP and subqueries"
+#
+CREATE TABLE t1 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key VARCHAR(1),
+KEY (col_int_key),
+KEY (col_varchar_key, col_int_key)
+);
+INSERT INTO t1 VALUES (NULL,2,'w');
+INSERT INTO t1 VALUES (7,9,'m');
+INSERT INTO t1 VALUES (9,3,'m');
+INSERT INTO t1 VALUES (7,9,'k');
+INSERT INTO t1 VALUES (4,NULL,'r');
+INSERT INTO t1 VALUES (2,9,'t');
+INSERT INTO t1 VALUES (6,3,'j');
+INSERT INTO t1 VALUES (8,8,'u');
+INSERT INTO t1 VALUES (NULL,8,'h');
+INSERT INTO t1 VALUES (5,53,'o');
+INSERT INTO t1 VALUES (NULL,0,NULL);
+INSERT INTO t1 VALUES (6,5,'k');
+INSERT INTO t1 VALUES (188,166,'e');
+INSERT INTO t1 VALUES (2,3,'n');
+INSERT INTO t1 VALUES (1,0,'t');
+INSERT INTO t1 VALUES (1,1,'c');
+INSERT INTO t1 VALUES (0,9,'m');
+INSERT INTO t1 VALUES (9,5,'y');
+INSERT INTO t1 VALUES (NULL,6,'f');
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where
+1 PRIMARY table2 ref col_varchar_key col_varchar_key 4 test.table3.col_varchar_key 2 Using index
+1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 2
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT table1.col_int_nokey
+FROM
+(
+t1 AS table1 STRAIGHT_JOIN
+(
+(
+t1 AS table2 INNER JOIN t1 AS table3
+ON (table3.col_varchar_key = table2.col_varchar_key)
+)
+)
+ON (table3.col_int_nokey = table1.col_int_key)
+)
+WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+col_int_nokey
+DROP TABLE t1, t2;
set optimizer_switch=default;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-11-05 22:19:41 +0000
+++ b/sql/sql_select.cc 2010-11-15 12:42:32 +0000
@@ -9820,6 +9820,8 @@
if (item->const_item())
return TRUE;
+ const Item::Type item_type= item->type();
+
/*
Don't push down the triggered conditions. Nested outer joins execution
code may need to evaluate a condition several times (both triggered and
@@ -9829,14 +9831,29 @@
possible
2. Put the second copy into tab->select_cond.
*/
- if (item->type() == Item::FUNC_ITEM &&
+ if (item_type == Item::FUNC_ITEM &&
((Item_func*)item)->functype() == Item_func::TRIG_COND_FUNC)
return FALSE;
+ /*
+ Do not push down subselects for execution by the handler. This
+ case would also be handled by the default label of the second
+ switch statement in this function. But since a subselect might
+ only refer to other tables the check below (if this item only
+ contains "other" tables) can return true and thus we need to do
+ this check here.
+ */
+ if (item_type == Item::SUBSELECT_ITEM)
+ return false;
+
+ /*
+ If this item will be evaluated using only "other tables" we let
+ the value of the other_tbls_ok determine if this item can be
+ pushed down or not.
+ */
if (!(item->used_tables() & tbl->map))
return other_tbls_ok;
- Item::Type item_type= item->type();
switch (item_type) {
case Item::FUNC_ITEM:
{
Attachment: [text/bzr-bundle] bzr/olav.sandstaa@oracle.com-20101115124232-o7lt2sx2rf5z4hds.bundle
