List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:January 24 2011 11:26am
Subject:Re: bzr commit into mysql-trunk branch (roy.lyseng:3327) Bug#57431
View as plain text  
Hi Tor,

thank you for the review.

On 24.01.11 12.17, Tor Didriksen wrote:
> 2011/1/21 Roy Lyseng<roy.lyseng@stripped>
>
>> #At file:///home/rl136806/mysql/repo/mysql-review/ based on
>> revid:jorgen.loland@stripped
>>
>>   3327 Roy Lyseng        2011-01-21
>>       Bug#57431: subquery returns wrong result (semijoin=on) with pred AND
>>
>>       The test case contains an outer query with a single table and an
>>       IN subquery with two outerjoined tables. The MaterializeScan
>>       semijoin strategy is selected, meaning that an outerjoin operation
>>       is first performed over join_tabs 0 and 1. After the outerjoin,
>>       sub_select_sjm() is called to perform a semijoin between the
>>       result of the outerjoin in join_tab 1 and the outer table in
>>       join_tab 2. However, join_tab 1 still contains some reminiscent data
>>       from the outerjoin operation, hence this operation also behaves like
>>       an outerjoin.
>>
>>       Fixed by temporarily deleting the outerjoin information from the
>>       join_tab representing the result of the outerjoin operation.
>>
>
> This comment is not quite correct anymore?

Right... I validated the file commit message, but forgot about this :(
>
>
>>
>>       mysql-test/include/subquery_sj_innodb.inc
>>         Test case for Bug#57431. The problem can only be reproduced with
>>         1 row in the inner table, hence Innodb is needed to prevent
>>         'const table' optimization.
>>
>>       mysql-test/r/subquery_sj_innodb_all.result
>> mysql-test/r/subquery_sj_innodb_all_jcl6.result
>>       mysql-test/r/subquery_sj_innodb_all_jcl7.result
>>       mysql-test/r/subquery_sj_innodb_none.result
>>       mysql-test/r/subquery_sj_innodb_none_jcl6.result
>>       mysql-test/r/subquery_sj_innodb_none_jcl7.result
>>         Updated test results for Bug#57431.
>>
>>       sql/sql_select.cc
>>         In sub_select_sjm(), clear the last_inner field of the first
>>         join_tab to take part in the semijoin operation.
>>
>>     modified:
>>       mysql-test/include/subquery_sj_innodb.inc
>>       mysql-test/r/subquery_sj_innodb_all.result
>>       mysql-test/r/subquery_sj_innodb_all_jcl6.result
>>       mysql-test/r/subquery_sj_innodb_all_jcl7.result
>>       mysql-test/r/subquery_sj_innodb_none.result
>>       mysql-test/r/subquery_sj_innodb_none_jcl6.result
>>       mysql-test/r/subquery_sj_innodb_none_jcl7.result
>>       sql/sql_select.cc
>> === modified file 'mysql-test/include/subquery_sj_innodb.inc'
>> --- a/mysql-test/include/subquery_sj_innodb.inc 2010-07-13 08:14:01 +0000
>> +++ b/mysql-test/include/subquery_sj_innodb.inc 2011-01-21 16:02:32 +0000
>> @@ -114,3 +114,24 @@ explain select 1 from t2 where
>>    c2 in (select 1 from t3, t2) and
>>    c1 in (select convert(c6,char(1)) from t2);
>>   drop table t2, t3;
>> +--echo #
>> +--echo # BUG#57431: subquery returns wrong result (semijoin=on) with pred
>> AND
>> +--echo #
>> +CREATE TABLE t1 (
>> +   i INT
>> +) ENGINE=InnoDB;
>> +INSERT INTO t1 VALUES (2),(4);
>> +
>> +CREATE TABLE t2 (
>> +   i INT,
>> +   vc VARCHAR(1)
>> +) ENGINE=InnoDB;
>> +INSERT INTO t2 VALUES (8,NULL);
>> +
>> +SELECT i
>> +FROM t1
>> +WHERE i IN (SELECT innr.i
>> +             FROM t2 LEFT JOIN t2 innr ON innr.vc)
>> +   AND i = 2;
>> +
>> +DROP TABLE t1, t2;
>>
>> === modified file 'mysql-test/r/subquery_sj_innodb_all.result'
>> --- a/mysql-test/r/subquery_sj_innodb_all.result        2010-11-30 13:55:22
>> +0000
>> +++ b/mysql-test/r/subquery_sj_innodb_all.result        2011-01-21 16:02:32
>> +0000
>> @@ -141,4 +141,23 @@ id select_type     table   type    possible_keys
>>   1      PRIMARY t2      ALL     NULL    NULL    NULL    NULL    1
>> Using where
>>   1      PRIMARY t3      ALL     NULL    NULL    NULL    NULL    2
>> FirstMatch(t2)
>>   drop table t2, t3;
>> +#
>> +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
>> +#
>> +CREATE TABLE t1 (
>> +i INT
>> +) ENGINE=InnoDB;
>> +INSERT INTO t1 VALUES (2),(4);
>> +CREATE TABLE t2 (
>> +i INT,
>> +vc VARCHAR(1)
>> +) ENGINE=InnoDB;
>> +INSERT INTO t2 VALUES (8,NULL);
>> +SELECT i
>> +FROM t1
>> +WHERE i IN (SELECT innr.i
>> +FROM t2 LEFT JOIN t2 innr ON innr.vc)
>> +AND i = 2;
>> +i
>> +DROP TABLE t1, t2;
>>   set optimizer_switch=default;
>>
>> === modified file 'mysql-test/r/subquery_sj_innodb_all_jcl6.result'
>> --- a/mysql-test/r/subquery_sj_innodb_all_jcl6.result   2010-11-30 13:55:22
>> +0000
>> +++ b/mysql-test/r/subquery_sj_innodb_all_jcl6.result   2011-01-21 16:02:32
>> +0000
>> @@ -145,5 +145,24 @@ id select_type     table   type    possible_keys
>>   1      PRIMARY t2      ALL     NULL    NULL    NULL    NULL    1
>> Using where
>>   1      PRIMARY t3      ALL     NULL    NULL    NULL    NULL    2
>> FirstMatch(t2)
>>   drop table t2, t3;
>> +#
>> +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
>> +#
>> +CREATE TABLE t1 (
>> +i INT
>> +) ENGINE=InnoDB;
>> +INSERT INTO t1 VALUES (2),(4);
>> +CREATE TABLE t2 (
>> +i INT,
>> +vc VARCHAR(1)
>> +) ENGINE=InnoDB;
>> +INSERT INTO t2 VALUES (8,NULL);
>> +SELECT i
>> +FROM t1
>> +WHERE i IN (SELECT innr.i
>> +FROM t2 LEFT JOIN t2 innr ON innr.vc)
>> +AND i = 2;
>> +i
>> +DROP TABLE t1, t2;
>>   set optimizer_switch=default;
>>   set optimizer_join_cache_level=default;
>>
>> === modified file 'mysql-test/r/subquery_sj_innodb_all_jcl7.result'
>> --- a/mysql-test/r/subquery_sj_innodb_all_jcl7.result   2010-11-30 13:55:22
>> +0000
>> +++ b/mysql-test/r/subquery_sj_innodb_all_jcl7.result   2011-01-21 16:02:32
>> +0000
>> @@ -145,5 +145,24 @@ id select_type     table   type    possible_keys
>>   1      PRIMARY t2      ALL     NULL    NULL    NULL    NULL    1
>> Using where
>>   1      PRIMARY t3      ALL     NULL    NULL    NULL    NULL    2
>> FirstMatch(t2)
>>   drop table t2, t3;
>> +#
>> +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
>> +#
>> +CREATE TABLE t1 (
>> +i INT
>> +) ENGINE=InnoDB;
>> +INSERT INTO t1 VALUES (2),(4);
>> +CREATE TABLE t2 (
>> +i INT,
>> +vc VARCHAR(1)
>> +) ENGINE=InnoDB;
>> +INSERT INTO t2 VALUES (8,NULL);
>> +SELECT i
>> +FROM t1
>> +WHERE i IN (SELECT innr.i
>> +FROM t2 LEFT JOIN t2 innr ON innr.vc)
>> +AND i = 2;
>> +i
>> +DROP TABLE t1, t2;
>>   set optimizer_switch=default;
>>   set optimizer_join_cache_level=default;
>>
>> === modified file 'mysql-test/r/subquery_sj_innodb_none.result'
>> --- a/mysql-test/r/subquery_sj_innodb_none.result       2010-11-29 13:30:18
>> +0000
>> +++ b/mysql-test/r/subquery_sj_innodb_none.result       2011-01-21 16:02:32
>> +0000
>> @@ -141,4 +141,23 @@ id select_type     table   type    possible_keys
>>   2      DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL
>>   NULL    1
>>   2      DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL
>>   NULL    2       Using where; Using join buffer (BNL, incremental buffers)
>>   drop table t2, t3;
>> +#
>> +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
>> +#
>> +CREATE TABLE t1 (
>> +i INT
>> +) ENGINE=InnoDB;
>> +INSERT INTO t1 VALUES (2),(4);
>> +CREATE TABLE t2 (
>> +i INT,
>> +vc VARCHAR(1)
>> +) ENGINE=InnoDB;
>> +INSERT INTO t2 VALUES (8,NULL);
>> +SELECT i
>> +FROM t1
>> +WHERE i IN (SELECT innr.i
>> +FROM t2 LEFT JOIN t2 innr ON innr.vc)
>> +AND i = 2;
>> +i
>> +DROP TABLE t1, t2;
>>   set optimizer_switch=default;
>>
>> === modified file 'mysql-test/r/subquery_sj_innodb_none_jcl6.result'
>> --- a/mysql-test/r/subquery_sj_innodb_none_jcl6.result  2010-11-29 13:04:34
>> +0000
>> +++ b/mysql-test/r/subquery_sj_innodb_none_jcl6.result  2011-01-21 16:02:32
>> +0000
>> @@ -145,5 +145,24 @@ id select_type     table   type    possible_keys
>>   2      DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL
>>   NULL    1
>>   2      DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL
>>   NULL    2       Using where; Using join buffer (BNL, incremental buffers)
>>   drop table t2, t3;
>> +#
>> +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
>> +#
>> +CREATE TABLE t1 (
>> +i INT
>> +) ENGINE=InnoDB;
>> +INSERT INTO t1 VALUES (2),(4);
>> +CREATE TABLE t2 (
>> +i INT,
>> +vc VARCHAR(1)
>> +) ENGINE=InnoDB;
>> +INSERT INTO t2 VALUES (8,NULL);
>> +SELECT i
>> +FROM t1
>> +WHERE i IN (SELECT innr.i
>> +FROM t2 LEFT JOIN t2 innr ON innr.vc)
>> +AND i = 2;
>> +i
>> +DROP TABLE t1, t2;
>>   set optimizer_switch=default;
>>   set optimizer_join_cache_level=default;
>>
>> === modified file 'mysql-test/r/subquery_sj_innodb_none_jcl7.result'
>> --- a/mysql-test/r/subquery_sj_innodb_none_jcl7.result  2010-11-29 13:04:34
>> +0000
>> +++ b/mysql-test/r/subquery_sj_innodb_none_jcl7.result  2011-01-21 16:02:32
>> +0000
>> @@ -145,5 +145,24 @@ id select_type     table   type    possible_keys
>>   2      DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL
>>   NULL    1
>>   2      DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL
>>   NULL    2       Using where; Using join buffer (BNL, regular buffers)
>>   drop table t2, t3;
>> +#
>> +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
>> +#
>> +CREATE TABLE t1 (
>> +i INT
>> +) ENGINE=InnoDB;
>> +INSERT INTO t1 VALUES (2),(4);
>> +CREATE TABLE t2 (
>> +i INT,
>> +vc VARCHAR(1)
>> +) ENGINE=InnoDB;
>> +INSERT INTO t2 VALUES (8,NULL);
>> +SELECT i
>> +FROM t1
>> +WHERE i IN (SELECT innr.i
>> +FROM t2 LEFT JOIN t2 innr ON innr.vc)
>> +AND i = 2;
>> +i
>> +DROP TABLE t1, t2;
>>   set optimizer_switch=default;
>>   set optimizer_join_cache_level=default;
>>
>> === modified file 'sql/sql_select.cc'
>> --- a/sql/sql_select.cc 2011-01-13 14:47:29 +0000
>> +++ b/sql/sql_select.cc 2011-01-21 16:02:32 +0000
>> @@ -17078,6 +17078,10 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
>>        last_tab->read_record.copy_field_end= sjm->copy_field +
>>                                              sjm->table_cols.elements;
>>        last_tab->read_record.read_record= rr_sequential_and_unpack;
>> +      /*
>> +        Clear possible outer join information from earlier use of this
>> join tab.
>> +      */
>>
>
> Consider using a // comment instead.

Ok.
>
>
>> +      last_tab->last_inner= NULL;
>>      }
>>    }
>>    else
>>
>>
>>
>> --
>> MySQL Code Commits Mailing List
>> For list archives: http://lists.mysql.com/commits
>> To unsubscribe:
>> http://lists.mysql.com/commits?unsub=1
>>
>


Roy
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3327) Bug#57431Roy Lyseng21 Jan
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3327) Bug#57431Tor Didriksen24 Jan
    • Re: bzr commit into mysql-trunk branch (roy.lyseng:3327) Bug#57431Roy Lyseng24 Jan