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
