Tor Didriksen wrote:
> On Tue, Nov 23, 2010 at 4:07 PM, Olav Sandstaa
> <olav.sandstaa@stripped>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
>>
>>
>
> Did you consider fixing the "real" problem here?
>
Yes, I considered it. Here are some of my findings:
* Item::used_tables(): The existing code expected this to return
reliable information about which tables that would be used during
execution of an item tree. I found the following comment in the header
file for item.h:
/* bit map of tables used by item */
virtual table_map used_tables() const { return (table_map) 0L; }
/*
Return table map of tables that can't be NULL tables (tables that are
used in a context where if they would contain a NULL row generated
by a LEFT or RIGHT join, the item would not be true).
This expression is used on WHERE item to determinate if a LEFT JOIN
can be
converted to a normal join.
Generally this function should return used_tables() if the function
would return null if any of the arguments are null
As this is only used in the beginning of optimization, the value don't
have to be updated in update_used_tables()
*/
virtual table_map not_null_tables() const { return used_tables(); }
This comment is for the method not_nulll_tables() but has a reference to
used_tables(). If I read it correctly it says that information about
used_tables does not necessarily have to be correct since they might not
be updated after the beginning of optimization. So it is documented that
this method probably should not be used at this stage.
I also discussed this with Roy and he said that it would take some work
to ensure that this information was correctly updated and propagated at
all times. There is also an issue (according to Roy) that this
information is stored in a table_map variable. In order to be able to
store information about that a subquery in an item tree references an
outer table (or was it an inner table?) it need an extra bit for
propagating this information properly. Currently we do not have any free
bits but Roy thinks we could probably eliminate the use of one of the
tree "special bits" and use this for propagate this information (I have
to admit that I did not quite understand everything Roy told me so some
of this might be wrong).
So to fix Item::used_tables() to in all cases provide correct
information seems like a larger job than just fixing this problem.
I also consider to eliminate the use of Item::used_tables() to have
benefits that might justify this change:
* Using Item::used_tables() has the benefit of being able to stop the
recursive traversal of the item tree earlier. In most cases it will stop
the recursive calls only one level from the bottom of the tree (since it
will require that the sub tree only contains references to "other
tables" than this). So each time this evaluates to true we will in most
times just save to function calls.
* Eliminating the use of Item::used_tables() gives the following benefits:
-on each (recursive) call to uses_index_fields_only() we will save
one call to a virtual function.
-the logic of uses_index_fields_only() becomes simpler by
eliminating this "optimization"
-uses_index_fields_only() already has code for handling this
situation with "other_tables" (see the switch statement for the FIELD item)
* Item::const_item(): The existing code in uses_index_fields_only()
expect that a if this returns true it would be safe to push this part of
the item tree down to the handler/InnoDB. If it contains a subquery it
is not safe to push it down. Still, it might be situations where an item
tree is correctly considered to be const even when there is a subquery
in it. The optimizer might have concluded that it will return a constant
value even if it yet has not been executed. So it might be correct that
Item::const_item() in some situations correctly returns true even when
it contains a subquery (again Roy can probably explain this much better
and more correctly).
So adding the extra check for item->with_subselect to the check for
item->const_item() seems to me to be correct.
> Did you consider renaming uses_index_fields_only() to something like
> can_safely_be_pushed_as_index_condition()
>
No, but that seems like a good idea. I guess to original author did not
want to have it "ICP" specific but it has gradually gotten some ICP
specific code and comments.
Thanks for looking at the patch.
Olav
> -- didrik
>
>
>
>> 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;
>> + }
>>
>> 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:
>>
>>
>>
>> --
>> MySQL Code Commits Mailing List
>> For list archives: http://lists.mysql.com/commits
>> To unsubscribe:
>> http://lists.mysql.com/commits?unsub=1
>>
>>
>
>
