List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:November 15 2010 12:42pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (olav.sandstaa:3281) Bug#56529
View as plain text  
#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
Thread
bzr commit into mysql-next-mr-bugfixing branch (olav.sandstaa:3281) Bug#56529Olav Sandstaa15 Nov
  • Re: bzr commit into mysql-next-mr-bugfixing branch (olav.sandstaa:3281)Bug#56529Jorgen Loland15 Nov
    • Re: bzr commit into mysql-next-mr-bugfixing branch (olav.sandstaa:3281)Bug#56529Olav Sandstaa15 Nov