List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:November 24 2010 10:20pm
Subject:bzr commit into mysql-trunk-bugfixing branch (olav.sandstaa:3288) Bug#58243
View as plain text  
#At file:///export/home/tmp/olav/opt-bug58243/ based on revid:tor.didriksen@stripped

 3288 Olav Sandstaa	2010-11-24
      Fix for Bug#58243 RQG test optimizer_subquery causes server 
      crash when running with ICP
      
      The crash was due to hitting an assert in InnoDB that checked that the
      same transaction could only have one active data access to InnoDB. In
      these cases there were two ongoing data accesses by the same
      transaction. This happened because the server was pushing down an
      index condition that contained a subquery. When InnoDB executed the
      index condition call back function this would result in a second call
      to InnoDB due to executing the subquery from within the index conditon
      function.
      
      To avoid this problem we should not push down index conditions that
      contains an subquery. The existing code for determining which part of
      a table's where condition that can be pushed down already had code for
      handling this but due to inconsistent data in the condition's item
      tree (or wrong use of methods on it) this code did not work as
      expected. Two cases where this happened (see the code in
      uses_index_fields_only()):
      
      1. Test for constant items:
      
          if (item->const_item())
             return TRUE;
      
         If the item tree contains a subquery the call to const_item() 
         could in some cases return true even when the subquery has to
         be executed later. In this case we would include the subquery
         in the pushed index condition.
      
         Fix for this problem: Check the item's with_subselect field.
         If this is true then do not include it, other let the item
         tree be included.
      
      2. Test for which tables the item tree contains:
      
          if (!(item->used_tables() & tbl->map))
             return other_tbls_ok;
      
         This code would accept to include an item tree if it did not
         use the table we are using for push down. If the item tree
         contained a subquery then the used_tables() method can return
         the wrong set of tables and this could result in that the item
         was wrongly included in the condition that was pushed down.
      
         Fix for this problem: Remove this test. This will result in that we
         will potentially recursively traverse more of the item tree.  The
         evaluation will be done by the main switch statement and the
         decision about whether to accept accesses to data from other tables
         will be done on the field item.
      
      Two test cases are included that would trigger the two cases.
     @ mysql-test/include/icp_tests.inc
        Test case for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
        
        Two test cases are added that covers each of the two situations
        where a subquery wrongly could be included in the pushed down
        index condition.
     @ mysql-test/r/innodb_icp.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/innodb_icp_all.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/innodb_icp_none.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/myisam_icp.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/myisam_icp_all.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/myisam_icp_none.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/subquery_all.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ mysql-test/r/subquery_all_jcl6.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ mysql-test/r/subquery_nomat_nosj.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ mysql-test/r/subquery_nomat_nosj_jcl6.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ sql/sql_select.cc
        Changes how uses_index_fields_only() handles conditions containing 
        a subquery:
        
        1. Before accepting to include a query that is const: also check
           that it does not contain a sub query by checking the item's
           with_subselect flag.
        
        2. Do not use the item->used_tables() to determine if this 
           query will only access "other tables" than the current table.
           This method could return wrong (or missing data) about the
           actual tables needed to execute the item tree. Instead we let
           the main switch handle this.

    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
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_jcl6.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_jcl6.result
      sql/sql_select.cc
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	2010-11-16 08:22:56 +0000
+++ b/mysql-test/include/icp_tests.inc	2010-11-24 22:20:27 +0000
@@ -566,3 +566,80 @@ eval EXPLAIN $query;
 eval $query;
 
 DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#58243 "RQG test optimizer_subquery causes server crash
+--echo #            when running with ICP"
+--echo #
+
+# Test case 1: This test case makes item->const_item() return true
+#              in uses_index_fields_only() for an item tree
+#              containing a subquery. This triggered the subquery
+#              to be pushed down to InnoDB.
+
+CREATE TABLE t1 (
+  pk INTEGER NOT NULL,
+  c1 INTEGER NOT NULL,
+  c2 INTEGER NOT NULL,
+
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t1 VALUES (1,6,7);
+
+CREATE TABLE t2 (
+  c1 INTEGER NOT NULL
+);
+
+let query=
+SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+                            FROM t2)
+ORDER BY t1.c2;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2;
+
+# Test case 2: This test case makes item->used_tables() return 0
+#              in uses_index_fields_only() for an item tree
+#              containg a subquery. This triggered the subquery
+#              to be pushed down to InnoDB.
+
+CREATE TABLE t1 (
+  i1 INTEGER NOT NULL,
+  c1 VARCHAR(1) NOT NULL
+);
+
+INSERT INTO t1 VALUES (2,'w');
+
+CREATE TABLE t2 (
+  i1 INTEGER NOT NULL,
+  c1 VARCHAR(1) NOT NULL,
+  c2 VARCHAR(1) NOT NULL,
+  KEY (c1, i1)
+);
+
+INSERT INTO t2 VALUES (8,'d','d');
+INSERT INTO t2 VALUES (4,'v','v');
+
+CREATE TABLE t3 (
+  c1 VARCHAR(1) NOT NULL
+);
+
+INSERT INTO t3 VALUES ('v');
+
+let query=
+SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+              FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+              WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+                                                FROM t3)); 
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1,t2,t3;

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2010-11-16 08:22:56 +0000
+++ b/mysql-test/r/innodb_icp.result	2010-11-24 22:20:27 +0000
@@ -523,5 +523,71 @@ ON table3.col_int_nokey = table1.col_int
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 col_int_nokey
 DROP TABLE t1, t2;
+#
+# Bug#58243 "RQG test optimizer_subquery causes server crash
+#            when running with ICP"
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+c1 INTEGER NOT NULL,
+c2 INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,6,7);
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	
+SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+c1
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t1 VALUES (2,'w');
+CREATE TABLE t2 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL,
+c2 VARCHAR(1) NOT NULL,
+KEY (c1, i1)
+);
+INSERT INTO t2 VALUES (8,'d','d');
+INSERT INTO t2 VALUES (4,'v','v');
+CREATE TABLE t3 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t3 VALUES ('v');
+EXPLAIN SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t2	ref	c1	c1	3	test.t3.c1	1	Using where
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	
+SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+i1
+2
+DROP TABLE t1,t2,t3;
 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-16 08:22:56 +0000
+++ b/mysql-test/r/innodb_icp_all.result	2010-11-24 22:20:27 +0000
@@ -523,5 +523,71 @@ ON table3.col_int_nokey = table1.col_int
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 col_int_nokey
 DROP TABLE t1, t2;
+#
+# Bug#58243 "RQG test optimizer_subquery causes server crash
+#            when running with ICP"
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+c1 INTEGER NOT NULL,
+c2 INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,6,7);
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Using where; Using filesort
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	
+SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+c1
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t1 VALUES (2,'w');
+CREATE TABLE t2 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL,
+c2 VARCHAR(1) NOT NULL,
+KEY (c1, i1)
+);
+INSERT INTO t2 VALUES (8,'d','d');
+INSERT INTO t2 VALUES (4,'v','v');
+CREATE TABLE t3 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t3 VALUES ('v');
+EXPLAIN SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t2	ref	c1	c1	3	test.t3.c1	1	Using where
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	
+SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+i1
+2
+DROP TABLE t1,t2,t3;
 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-16 16:17:25 +0000
+++ b/mysql-test/r/innodb_icp_none.result	2010-11-24 22:20:27 +0000
@@ -522,5 +522,71 @@ ON table3.col_int_nokey = table1.col_int
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 col_int_nokey
 DROP TABLE t1, t2;
+#
+# Bug#58243 "RQG test optimizer_subquery causes server crash
+#            when running with ICP"
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+c1 INTEGER NOT NULL,
+c2 INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,6,7);
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	
+SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+c1
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t1 VALUES (2,'w');
+CREATE TABLE t2 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL,
+c2 VARCHAR(1) NOT NULL,
+KEY (c1, i1)
+);
+INSERT INTO t2 VALUES (8,'d','d');
+INSERT INTO t2 VALUES (4,'v','v');
+CREATE TABLE t3 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t3 VALUES ('v');
+EXPLAIN SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t2	ref	c1	c1	3	test.t3.c1	1	Using where
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	
+SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+i1
+2
+DROP TABLE t1,t2,t3;
 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-16 08:22:56 +0000
+++ b/mysql-test/r/myisam_icp.result	2010-11-24 22:20:27 +0000
@@ -521,4 +521,70 @@ ON table3.col_int_nokey = table1.col_int
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 col_int_nokey
 DROP TABLE t1, t2;
+#
+# Bug#58243 "RQG test optimizer_subquery causes server crash
+#            when running with ICP"
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+c1 INTEGER NOT NULL,
+c2 INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,6,7);
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+c1
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t1 VALUES (2,'w');
+CREATE TABLE t2 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL,
+c2 VARCHAR(1) NOT NULL,
+KEY (c1, i1)
+);
+INSERT INTO t2 VALUES (8,'d','d');
+INSERT INTO t2 VALUES (4,'v','v');
+CREATE TABLE t3 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t3 VALUES ('v');
+EXPLAIN SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t2	ref	c1	c1	3	const	1	Using where
+3	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	1	
+SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+i1
+2
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result	2010-11-16 08:22:56 +0000
+++ b/mysql-test/r/myisam_icp_all.result	2010-11-24 22:20:27 +0000
@@ -521,4 +521,70 @@ ON table3.col_int_nokey = table1.col_int
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 col_int_nokey
 DROP TABLE t1, t2;
+#
+# Bug#58243 "RQG test optimizer_subquery causes server crash
+#            when running with ICP"
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+c1 INTEGER NOT NULL,
+c2 INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,6,7);
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	PRIMARY	NULL	NULL	NULL	1	
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+c1
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t1 VALUES (2,'w');
+CREATE TABLE t2 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL,
+c2 VARCHAR(1) NOT NULL,
+KEY (c1, i1)
+);
+INSERT INTO t2 VALUES (8,'d','d');
+INSERT INTO t2 VALUES (4,'v','v');
+CREATE TABLE t3 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t3 VALUES ('v');
+EXPLAIN SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t2	ref	c1	c1	3	const	1	Using where
+3	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	1	
+SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+i1
+2
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result	2010-11-16 08:22:56 +0000
+++ b/mysql-test/r/myisam_icp_none.result	2010-11-24 22:20:27 +0000
@@ -520,4 +520,70 @@ ON table3.col_int_nokey = table1.col_int
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 col_int_nokey
 DROP TABLE t1, t2;
+#
+# Bug#58243 "RQG test optimizer_subquery causes server crash
+#            when running with ICP"
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+c1 INTEGER NOT NULL,
+c2 INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,6,7);
+CREATE TABLE t2 (
+c1 INTEGER NOT NULL
+);
+EXPLAIN SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT t1.c1
+FROM t1
+WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
+FROM t2)
+ORDER BY t1.c2;
+c1
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t1 VALUES (2,'w');
+CREATE TABLE t2 (
+i1 INTEGER NOT NULL,
+c1 VARCHAR(1) NOT NULL,
+c2 VARCHAR(1) NOT NULL,
+KEY (c1, i1)
+);
+INSERT INTO t2 VALUES (8,'d','d');
+INSERT INTO t2 VALUES (4,'v','v');
+CREATE TABLE t3 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t3 VALUES ('v');
+EXPLAIN SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t2	ref	c1	c1	3	const	1	Using where
+3	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	1	
+SELECT i1
+FROM t1
+WHERE EXISTS (SELECT t2.c1
+FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
+WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
+FROM t3));
+i1
+2
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result	2010-11-16 16:02:57 +0000
+++ b/mysql-test/r/subquery_all.result	2010-11-24 22:20:27 +0000
@@ -1232,7 +1232,7 @@ create table t1 (id int not null auto_in
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using index condition
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))

=== modified file 'mysql-test/r/subquery_all_jcl6.result'
--- a/mysql-test/r/subquery_all_jcl6.result	2010-11-16 16:02:57 +0000
+++ b/mysql-test/r/subquery_all_jcl6.result	2010-11-24 22:20:27 +0000
@@ -1236,7 +1236,7 @@ create table t1 (id int not null auto_in
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using index condition
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2010-11-16 16:02:57 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2010-11-24 22:20:27 +0000
@@ -1232,7 +1232,7 @@ create table t1 (id int not null auto_in
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using index condition
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))

=== modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result'
--- a/mysql-test/r/subquery_nomat_nosj_jcl6.result	2010-11-16 16:02:57 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result	2010-11-24 22:20:27 +0000
@@ -1236,7 +1236,7 @@ create table t1 (id int not null auto_in
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using index condition
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-11-24 16:57:12 +0000
+++ b/sql/sql_select.cc	2010-11-24 22:20:27 +0000
@@ -9820,11 +9820,18 @@ static bool make_join_select(JOIN *join,
     FALSE  No
 */
 
-bool uses_index_fields_only(Item *item, TABLE *tbl, uint keyno, 
-                            bool other_tbls_ok)
+static bool uses_index_fields_only(Item *item, TABLE *tbl, uint keyno, 
+                                   bool other_tbls_ok)
 {
   if (item->const_item())
-    return TRUE;
+  {
+    /*
+      const_item() might not return correct value if the item tree
+      contains a subquery. If this is the case we do not include this
+      part of the condition.
+    */
+    return !item->with_subselect;
+  }
 
   const Item::Type item_type= item->type();
 
@@ -9841,25 +9848,6 @@ bool uses_index_fields_only(Item *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;
-
   switch (item_type) {
   case Item::FUNC_ITEM:
     {
@@ -9895,7 +9883,7 @@ bool uses_index_fields_only(Item *item, 
     {
       Item_field *item_field= (Item_field*)item;
       if (item_field->field->table != tbl) 
-        return TRUE;
+        return other_tbls_ok;
       /*
         The below is probably a repetition - the first part checks the
         other two, but let's play it safe:


Attachment: [text/bzr-bundle] bzr/olav.sandstaa@oracle.com-20101124222027-11hi9hwgbg6ut2kb.bundle
Thread
bzr commit into mysql-trunk-bugfixing branch (olav.sandstaa:3288) Bug#58243Olav Sandstaa24 Nov