List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:November 24 2010 8:31am
Subject:Re: bzr commit into mysql-trunk branch (olav.sandstaa:3287) Bug#58243
View as plain text  
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?

Did you consider renaming uses_index_fields_only() to something like
can_safely_be_pushed_as_index_condition()

-- 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
>

Thread
bzr commit into mysql-trunk branch (olav.sandstaa:3287) Bug#58243Olav Sandstaa23 Nov
  • Re: bzr commit into mysql-trunk branch (olav.sandstaa:3287) Bug#58243Tor Didriksen24 Nov
    • Re: bzr commit into mysql-trunk branch (olav.sandstaa:3287) Bug#58243Olav Sandstaa24 Nov
  • Re: bzr commit into mysql-trunk branch (olav.sandstaa:3287) Bug#58243Roy Lyseng24 Nov