List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:January 24 2011 11:17am
Subject:Re: bzr commit into mysql-trunk branch (roy.lyseng:3327) Bug#57431
View as plain text  
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?


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


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

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