From: Sergey Glukhov
Date: June 9 2011 10:11am
Subject: Re: bzr commit into mysql-5.5 branch (jorgen.loland:3409) Bug#12561818
List-Archive: http://lists.mysql.com/commits/138956
Message-Id: <4DF09C4B.204@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Hi,
On 05/27/2011 04:04 PM, Jorgen Loland wrote:
> #At file:///export/home/jl208045/mysql/mysql-5.5-12561818/ based on revid:dmitry.shulga@stripped
>
> 3409 Jorgen Loland 2011-05-27
> BUG#12561818 - RERUN OF STORED FUNCTION GIVES ERROR 1172:
> RESULT CONSISTED OF MORE THAN ONE ROW
>
> MySQL converts incorrect DATEs and DATETIMEs to '0000-00-00' on
> insertion by default. This means that this sequence is possible:
>
> CREATE TABLE t1(date_notnull DATE NOT NULL);
> INSERT INTO t1 values (NULL);
> SELECT * FROM t1;
> 0000-00-00
>
> At the same time, ODBC drivers do not (or at least did not in the
> 90's) understand the DATE and DATETIME value '0000-00-00'. Thus,
> to be able to query for the value 0000-00-00 it was decided in
> MySQL 4.x (or maybe even before that) that for the special case
> of DATE/DATETIME NOT NULL columns, the query "SELECT ... WHERE
> date_notnull IS NULL" should return rows with date_notnull ==
> '0000-00-00'. This is documented misbehavior that we do not want
> to change.
>
> The hack used to make MySQL return these rows is to convert
> "date_notnull IS NULL" to "date_notnull = 0". This is, however,
> only done if the table date_notnull belongs to is not an inner
> table of an outer join. The rationale for this seems to be that
> if there is no join match for the row in the outer table,
> null-complemented rows would otherwise not be returned because
> the null-complemented DATE value is actually NULL. On the other
> hand, this means that the "return rows with 0000-00-00 when the
> query asks for IS NULL"-hack is not in effect for outer joins.
>
> In this bug, we have a LEFT JOIN that does not misbehave like
> the documentation says it should. The fix is to rewrite
>
> "date_notnull IS NULL" to "date_notnull IS NULL OR
> date_notnull = 0"
> instead of
> "date_notnull IS NULL" to "date_notnull = 0"
> and also do this rewrite for outer joins.
> @ mysql-test/r/ps.result
> Added test for BUG#12561818
> @ mysql-test/r/type_datetime.result
> " IS NULL" for DATE NOT NULL column is now rewritten to
> " IS NULL OR = 0" and also done for outer joins.
> @ mysql-test/t/ps.test
> Added test for BUG#12561818
> @ sql/sql_select.cc
> Special handling of NULL for DATE/DATETIME NOT NULL columns:
> Instead of rewriting
> "date_notnull IS NULL" => "date_notnull = 0"
> it is now rewritten to
> "date_notnull IS NULL" => "date_notnull IS NULL OR date_notnull = 0"
>
> modified:
> mysql-test/r/ps.result
> mysql-test/r/type_datetime.result
> mysql-test/t/ps.test
> sql/sql_select.cc
>
> === modified file 'mysql-test/t/ps.test'
> --- a/mysql-test/t/ps.test 2011-03-22 11:48:56 +0000
> +++ b/mysql-test/t/ps.test 2011-05-27 12:04:16 +0000
> @@ -3357,6 +3357,36 @@ disconnect con1;
> SELECT *
> FROM (SELECT 1 UNION SELECT 2) t;
>
> +--echo #
> +--echo # BUG#12561818: RERUN OF STORED FUNCTION GIVES ERROR 1172:
> +--echo # RESULT CONSISTED OF MORE THAN ONE ROW
> +--echo #
> +
> +CREATE TABLE t1 (a DATE NOT NULL, b INT);
> +INSERT INTO t1 VALUES ('0000-00-00',1), ('1999-05-10',2);
> +
> +--echo
> +SELECT * FROM t1 WHERE a IS NULL;
> +--echo
^^^
I would prefer to remove 'echo' line,
result line could be treated as empty result row
which looks a bit confusing for me, but I don't insist :)
> +SELECT * FROM t1 LEFT JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
> +--echo
^^^
same here
> +SELECT * FROM t1 JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
> +
> +--echo
> +DELIMITER $;
> +CREATE PROCEDURE p1()
> +BEGIN
> + SELECT *
> + FROM t1 LEFT JOIN t1 AS t1_2 ON 1
> + WHERE t1_2.a IS NULL AND t1_2.b< 2;
> +END $
> +DELIMITER ;$
> +--echo
> +CALL p1();
> +CALL p1();
> +
> +DROP PROCEDURE p1;
> +DROP TABLE t1;
>
> --echo #
> --echo # End of 5.5 tests.
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2011-05-06 08:27:04 +0000
> +++ b/sql/sql_select.cc 2011-05-27 12:04:16 +0000
> @@ -9519,24 +9519,29 @@ internal_remove_eq_conds(THD *thd, COND
> Field *field=((Item_field*) args[0])->field;
> /* fix to replace 'NULL' dates with '0' (shreeve@stripped) */
> /*
> - datetime_field IS NULL has to be modified to
> - datetime_field == 0
> + See BUG#12594011
> + Documentation says that
> + SELECT datetime_notnull d FROM t1 WHERE d IS NULL
> + shall return rows where d=='0000-00-00'
> +
> + Thus, for DATE and DATETIME columns defined as NOT NULL,
> + "date_notnull IS NULL" has to be modified to
> + "date_notnull IS NULL OR date_notnull == 0"
> */
> if (((field->type() == MYSQL_TYPE_DATE) ||
> (field->type() == MYSQL_TYPE_DATETIME))&&
> - (field->flags& NOT_NULL_FLAG)&& !field->table->maybe_null)
> + (field->flags& NOT_NULL_FLAG))
> {
> - COND *new_cond;
> - if ((new_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2))))
> - {
> - cond=new_cond;
> - /*
> - Item_func_eq can't be fixed after creation so we do not check
> - cond->fixed, also it do not need tables so we use 0 as second
> - argument.
> - */
> - cond->fix_fields(thd,&cond);
> - }
> + COND *eq_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2));
> + if (!eq_cond)
> + return cond;
> +
^^^
Please put the code below under
if (field->table->pos_in_table_list->outer_join) condition.
We don't need additional 'IS NULL' condition if table does not
belong to outer join.
> + COND *or_cond= new Item_cond_or(eq_cond, cond);
> + if (!or_cond)
> + return cond;
> + cond= or_cond;
> +
> + cond->fix_fields(thd,&cond);
> }
> }
> if (cond->const_item())
>
Regards,
Gluh