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


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