From: Roy Lyseng
Date: November 24 2010 2:34pm
Subject: Re: bzr commit into mysql-trunk branch (olav.sandstaa:3287) Bug#58243
List-Archive: http://lists.mysql.com/commits/124884
Message-Id: <4CED2283.6070309@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Olav,

bug fix is approved, just see one comment below.

On 23.11.10 16.07, Olav Sandstaa wrote:
> #At file:///export/home/tmp/mysql2/opt-bug58243/ based on revid:tor.didriksen@stripped
>
>   3287 Olav Sandstaa	2010-11-23
>        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'
>
> === 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-23 15:07:37 +0000
> @@ -566,3 +566,80 @@
>   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-23 15:07:37 +0000
> @@ -523,5 +523,71 @@
>   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-23 15:07:37 +0000
> @@ -523,5 +523,71 @@
>   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-23 15:07:37 +0000
> @@ -522,5 +522,71 @@
>   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-23 15:07:37 +0000
> @@ -521,4 +521,70 @@
>   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-23 15:07:37 +0000
> @@ -521,4 +521,70 @@
>   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-23 15:07:37 +0000
> @@ -520,4 +520,70 @@
>   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-23 15:07:37 +0000
> @@ -1232,7 +1232,7 @@
>   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-23 15:07:37 +0000
> @@ -1236,7 +1236,7 @@
>   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-23 15:07:37 +0000
> @@ -1232,7 +1232,7 @@
>   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-23 15:07:37 +0000
> @@ -1236,7 +1236,7 @@
>   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-16 16:17:25 +0000
> +++ b/sql/sql_select.cc	2010-11-23 15:07:37 +0000
> @@ -9818,7 +9818,17 @@
>                               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.
> +    */
> +    if (!item->with_subselect)
> +      return true;
> +    else
> +      return false;
> +  }
Please consider the simpler form:
     return !item->with_subselect;
>
>     const Item::Type item_type= item->type();
>
> @@ -9835,25 +9845,6 @@
>         ((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:
>       {
> @@ -9889,7 +9880,7 @@
>       {
>         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:

Thanks,
Roy