List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:November 16 2010 8:27am
Subject:bzr push into mysql-next-mr-bugfixing branch (olav.sandstaa:3280 to 3281)
Bug#56529
View as plain text  
 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#56529Olav Sandstaa16 Nov