List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:December 14 2010 2:49pm
Subject:Re: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58422
View as plain text  
Hi Ole John,

thank you for fixing this bug.

The fix is approved.

Please see one suggestion for simplification of testing.
On 07.12.10 12.50, Ole John Aske wrote:
> #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on
> revid:georgi.kodinov@stripped
>
>   3477 Ole John Aske	2010-12-07
>        Fix for bug#58422: Incorrect result when OUTER JOIN'ing with an empty table
>
>        Fixed incorrect checks in join_read_const_table() for when to
>        accept a non-existing, or empty const-row as a part of the const'ified
>        set of tables.
>
>        Intention of this test is to only accept NULL-rows if this table is outer
> joined
>        into the resultset. (In case of an inner-join we can conclude at this point
> that
>        resultset will be empty, end we want to return 'error' to signal this.)
>
>        Initially 'maybe_null' is set to the same value as 'outer_join' in
>        setup_table_map(), mysql_priv.h ~line 2424. Later simplify_joins() will
>        attemp to replace outer joins by inner join whenever possible. This
>        will cause 'outer_join' to be updated. However, 'maybe_null' is *not* updated
>        to reflect this rewrite as this field is used to currectly set the
> 'nullability'
>        property for the columns in the resultset.
>
>        We should therefore change join_read_const_table() to check the 'outer_join'
>        property instead of 'maybe_null', as this correctly reflect the nullability
> of
>        the *execution plan* (not *resultset*).
>
>      modified:
>        mysql-test/r/select.result
>        mysql-test/t/select.test
>        sql/sql_select.cc
> === modified file 'mysql-test/r/select.result'
> --- a/mysql-test/r/select.result	2010-06-24 08:00:48 +0000
> +++ b/mysql-test/r/select.result	2010-12-07 11:50:00 +0000
> @@ -4797,4 +4797,68 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati
>   1
>   1
>   DROP TABLE t1;
> +#
> +# Bug #58422: Incorrect result when OUTER JOIN'ing
> +# with an empty table
> +#
> +CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
> +CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
> +INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
> +CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
> +INSERT INTO t2 VALUES (1,1), (2,2), (3,3);

You may write the below queries as follows for a shorter test specification:

let $query=
SELECT * FROM ...;

eval explain $query;
eval $query;
> +EXPLAIN
> +SELECT *
> +FROM
> +t1
> +LEFT OUTER JOIN
> +(t2 INNER JOIN t_empty ON TRUE)
> +ON t1.pk=t2.pk
> +WHERE t2.pk<>  2;
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading
> const tables
> +SELECT *
> +FROM
> +t1
> +LEFT OUTER JOIN
> +(t2 INNER JOIN t_empty ON TRUE)
> +ON t1.pk=t2.pk
> +WHERE t2.pk<>  2;
> +pk	i	pk	i	pk	i
> +EXPLAIN
> +SELECT *
> +FROM
> +t1
> +LEFT OUTER JOIN
> +(t2 CROSS JOIN t_empty)
> +ON t1.pk=t2.pk
> +WHERE t2.pk<>  2;
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading
> const tables
> +SELECT *
> +FROM
> +t1
> +LEFT OUTER JOIN
> +(t2 CROSS JOIN t_empty)
> +ON t1.pk=t2.pk
> +WHERE t2.pk<>  2;
> +pk	i	pk	i	pk	i
> +EXPLAIN
> +SELECT *
> +FROM
> +t1
> +LEFT OUTER JOIN
> +(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
> +ON t1.pk=t2.pk
> +WHERE t2.pk<>  2;
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading
> const tables
> +SELECT *
> +FROM
> +t1
> +LEFT OUTER JOIN
> +(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
> +ON t1.pk=t2.pk
> +WHERE t2.pk<>  2;
> +pk	i	pk	i	pk	i
> +DROP TABLE t1,t2,t_empty;
>   End of 5.1 tests
>
> === modified file 'mysql-test/t/select.test'
> --- a/mysql-test/t/select.test	2010-06-24 17:13:08 +0000
> +++ b/mysql-test/t/select.test	2010-12-07 11:50:00 +0000
> @@ -4088,4 +4088,74 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati
>   DROP TABLE t1;
>
>
> +
> +--echo #
> +--echo # Bug #58422: Incorrect result when OUTER JOIN'ing
> +--echo # with an empty table
> +--echo #
> +
> +CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
> +CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
> +INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
> +CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
> +INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
> +
> +EXPLAIN
> +SELECT *
> +  FROM
> +    t1
> +  LEFT OUTER JOIN
> +    (t2 INNER JOIN t_empty ON TRUE)
> +  ON t1.pk=t2.pk
> +  WHERE t2.pk<>  2;
> +
> +SELECT *
> +  FROM
> +    t1
> +  LEFT OUTER JOIN
> +    (t2 INNER JOIN t_empty ON TRUE)
> +  ON t1.pk=t2.pk
> +  WHERE t2.pk<>  2;
> +
> +
> +EXPLAIN
> +SELECT *
> +  FROM
> +    t1
> +  LEFT OUTER JOIN
> +    (t2 CROSS JOIN t_empty)
> +  ON t1.pk=t2.pk
> +  WHERE t2.pk<>  2;
> +
> +SELECT *
> +  FROM
> +    t1
> +  LEFT OUTER JOIN
> +    (t2 CROSS JOIN t_empty)
> +  ON t1.pk=t2.pk
> +  WHERE t2.pk<>  2;
> +
> +
> +EXPLAIN
> +SELECT *
> +  FROM
> +    t1
> +  LEFT OUTER JOIN
> +    (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
> +  ON t1.pk=t2.pk
> +  WHERE t2.pk<>  2;
> +
> +SELECT *
> +  FROM
> +    t1
> +  LEFT OUTER JOIN
> +    (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
> +  ON t1.pk=t2.pk
> +  WHERE t2.pk<>  2;
> +
> +
> +
> +DROP TABLE t1,t2,t_empty;
> +
> +
>   --echo End of 5.1 tests
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc	2010-10-29 08:23:06 +0000
> +++ b/sql/sql_select.cc	2010-12-07 11:50:00 +0000
> @@ -11787,7 +11787,7 @@ join_read_const_table(JOIN_TAB *tab, POS
>         /* Mark for EXPLAIN that the row was not found */
>         pos->records_read=0.0;
>         pos->ref_depend_map= 0;
> -      if (!table->maybe_null || error>  0)
> +      if (!table->pos_in_table_list->outer_join || error>  0)
>   	DBUG_RETURN(error);
>       }
>     }
> @@ -11808,7 +11808,7 @@ join_read_const_table(JOIN_TAB *tab, POS
>         /* Mark for EXPLAIN that the row was not found */
>         pos->records_read=0.0;
>         pos->ref_depend_map= 0;
> -      if (!table->maybe_null || error>  0)
> +      if (!table->pos_in_table_list->outer_join || error>  0)
>   	DBUG_RETURN(error);
>       }
>     }

Thanks,
Roy

Thread
bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58422Ole John Aske7 Dec
  • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58422Roy Lyseng14 Dec