From: Roy Lyseng
Date: December 14 2010 2:49pm
Subject: Re: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58422
List-Archive: http://lists.mysql.com/commits/126781
Message-Id: <4D078407.8080300@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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