3281 Olav Sandstaa 2010-11-16
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
3280 Olav Sandstaa 2010-11-08
Patch for WL#5639 "Enable Index Condition Pushdown optimizer feature"
With this change the MySQL server will be built with support for the
Index Condition Pushdown (ICP) feature. The default will be that ICP
will be enabled.
The following changes to MySQL are implemented:
1. Enable index condition pushdown
With this feature enabled the optimizer will consider to push down to the
handler/storage engine parts (or all) of the where condition that can be
evaluated by the handler/storage engine using the content of the index that is
used for execution of the query.
The main change by enabling index condition pushdown should be improved
performance for certain queries. The user should be able to see if index
condition pushdown has been used by looking in the "Extra" column of the EXPLAIN
output. If this field contains the string "Using index condition" parts or all
of the where statement has been pushed down and will be evaluated by the
handler/storage engine.
2. Enable the user to control the use of index condition pushdown
When this has been enabled the user will be able to control the use
of index condition pushdown by using the optimizer_switch configuration variable.
The new optimizer_switch flag that will become available is named
"index_condition_pushdown" and can take the values "on", "off" and "default".
The default value for this optimizer switch flag will be "on".
@ mysql-test/r/index_merge_myisam.result
Change in result file caused by changing the order for optimizer switch
flags due to enabling Index Condition Pushdown.
@ mysql-test/r/mysqld--help-notwin.result
Change in result file caused by changing the order for optimizer switch
flags due to enabling Index Condition Pushdown.
@ mysql-test/r/mysqld--help-win.result
Change in result file caused by changing the order for optimizer switch
flags due to enabling Index Condition Pushdown.
@ mysql-test/r/optimizer_switch.result
Change in result file caused by changing the order for optimizer switch
flags due to enabling Index Condition Pushdown.
@ mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
Change in result file caused by changing the order for optimizer switch
flags due to enabling Index Condition Pushdown.
@ sql/sql_priv.h
Enable Index Condition Pushdown by default. The
OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN flag will be included in the
OPTIMIZER_SWITCH_DEFAULT macro also for the case where
OPTIMIZER_SWITCH_ALL is undefined.
@ sql/sys_vars.cc
Enable Index Condition Pushdown by changing *optimizer_switch_names variable
so that "index_condition_pushdown" is included also when OPTIMIZER_SWITCH_ALL
is not defined.
modified:
mysql-test/r/index_merge_myisam.result
mysql-test/r/mysqld--help-notwin.result
mysql-test/r/mysqld--help-win.result
mysql-test/r/optimizer_switch.result
mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
sql/sql_priv.h
sql/sys_vars.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-16 08:22:56 +0000
@@ -515,3 +515,54 @@
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-16 08:22:56 +0000
@@ -471,5 +471,57 @@
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-16 08:22:56 +0000
@@ -471,5 +471,57 @@
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-16 08:22:56 +0000
@@ -470,5 +470,57 @@
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-16 08:22:56 +0000
@@ -469,4 +469,56 @@
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-16 08:22:56 +0000
@@ -469,4 +469,56 @@
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-16 08:22:56 +0000
@@ -468,4 +468,56 @@
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-16 08:22:56 +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:
{
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-next-mr-bugfixing branch (olav.sandstaa:3280 to 3281)Bug#56529 | Olav Sandstaa | 16 Nov |
