Hi Jørgen,
this bugfix is approved.
Thanks,
Roy
On 09.06.11 13.57, Jorgen Loland wrote:
> #At file:///export/home/jl208045/mysql/mysql-5.5-12561818/ based on
> revid:marko.makela@stripped
>
> 3434 Jorgen Loland 2011-06-09
> 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"
> if dealing with an OUTER JOIN, otherwise
> "date_notnull IS NULL" to "date_notnull = 0"
> as was done before.
>
> Note:
> The bug was originally reported as different result on first
> and second execution of SP. The reason was that during first
> execution the query was correctly rewritten to an inner join
> due to a null-rejecting predicate. On second execution the
> "IS NULL" -> "= 0" rewrite was done because there was no outer
> join. The real problem, though, was incorrect date/datetime
> IS NULL handling for OUTER JOINs.
> @ mysql-test/r/type_datetime.result
> Add test for BUG#12561818
> @ mysql-test/t/type_datetime.test
> Add test for BUG#12561818
> @ sql/sql_select.cc
> Special handling of NULL for DATE/DATETIME NOT NULL columns:
> In the case of outer join,
> "date_notnull IS NULL"
> is now rewritten to
> "date_notnull IS NULL OR date_notnull = 0"
>
> modified:
> mysql-test/r/type_datetime.result
> mysql-test/t/type_datetime.test
> sql/sql_select.cc
> === modified file 'mysql-test/r/type_datetime.result'
> --- a/mysql-test/r/type_datetime.result 2011-01-19 14:12:43 +0000
> +++ b/mysql-test/r/type_datetime.result 2011-06-09 11:57:49 +0000
> @@ -703,5 +703,50 @@ b
> DROP TABLE t1;
> #
> #
> +# BUG#12561818: RERUN OF STORED FUNCTION GIVES ERROR 1172:
> +# RESULT CONSISTED OF MORE THAN ONE ROW
> +#
> +CREATE TABLE t1 (a DATE NOT NULL, b INT);
> +INSERT INTO t1 VALUES ('0000-00-00',1), ('1999-05-10',2);
> +CREATE TABLE t2 (a DATETIME NOT NULL, b INT);
> +INSERT INTO t2 VALUES ('0000-00-00 00:00:00',1), ('1999-05-10 00:00:00',2);
> +
> +SELECT * FROM t1 WHERE a IS NULL;
> +a b
> +0000-00-00 1
> +SELECT * FROM t2 WHERE a IS NULL;
> +a b
> +0000-00-00 00:00:00 1
> +SELECT * FROM t1 LEFT JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
> +a b a b
> +0000-00-00 1 0000-00-00 1
> +1999-05-10 2 0000-00-00 1
> +SELECT * FROM t2 LEFT JOIN t2 AS t2_2 ON 1 WHERE t2_2.a IS NULL;
> +a b a b
> +0000-00-00 00:00:00 1 0000-00-00 00:00:00 1
> +1999-05-10 00:00:00 2 0000-00-00 00:00:00 1
> +SELECT * FROM t1 JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
> +a b a b
> +0000-00-00 1 0000-00-00 1
> +1999-05-10 2 0000-00-00 1
> +SELECT * FROM t2 JOIN t2 AS t2_2 ON 1 WHERE t2_2.a IS NULL;
> +a b a b
> +0000-00-00 00:00:00 1 0000-00-00 00:00:00 1
> +1999-05-10 00:00:00 2 0000-00-00 00:00:00 1
> +
> +PREPARE stmt1 FROM
> +'SELECT *
> + FROM t1 LEFT JOIN t1 AS t1_2 ON 1
> + WHERE t1_2.a IS NULL AND t1_2.b< 2';
> +EXECUTE stmt1;
> +a b a b
> +0000-00-00 1 0000-00-00 1
> +1999-05-10 2 0000-00-00 1
> +EXECUTE stmt1;
> +a b a b
> +0000-00-00 1 0000-00-00 1
> +1999-05-10 2 0000-00-00 1
> +DROP TABLE t1,t2;
> +#
> # End of 5.5 tests
> #
>
> === modified file 'mysql-test/t/type_datetime.test'
> --- a/mysql-test/t/type_datetime.test 2011-01-19 14:12:43 +0000
> +++ b/mysql-test/t/type_datetime.test 2011-06-09 11:57:49 +0000
> @@ -506,5 +506,35 @@ DROP TABLE t1;
> --echo #
>
> --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);
> +
> +CREATE TABLE t2 (a DATETIME NOT NULL, b INT);
> +INSERT INTO t2 VALUES ('0000-00-00 00:00:00',1), ('1999-05-10 00:00:00',2);
> +
> +--echo
> +SELECT * FROM t1 WHERE a IS NULL;
> +SELECT * FROM t2 WHERE a IS NULL;
> +SELECT * FROM t1 LEFT JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
> +SELECT * FROM t2 LEFT JOIN t2 AS t2_2 ON 1 WHERE t2_2.a IS NULL;
> +SELECT * FROM t1 JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
> +SELECT * FROM t2 JOIN t2 AS t2_2 ON 1 WHERE t2_2.a IS NULL;
> +
> +--echo
> +PREPARE stmt1 FROM
> + 'SELECT *
> + FROM t1 LEFT JOIN t1 AS t1_2 ON 1
> + WHERE t1_2.a IS NULL AND t1_2.b< 2';
> +EXECUTE stmt1;
> +EXECUTE stmt1;
> +
> +DROP TABLE t1,t2;
> +
> +--echo #
> --echo # End of 5.5 tests
> --echo #
> +
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2011-05-06 08:27:04 +0000
> +++ b/sql/sql_select.cc 2011-06-09 11:57:49 +0000
> @@ -9519,24 +9519,41 @@ 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 outer join)
> + "date_notnull == 0" (otherwise)
> +
> */
> 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);
> - }
> + Item *item0= new(thd->mem_root) Item_int((longlong)0, 1);
> + COND *eq_cond= new(thd->mem_root) Item_func_eq(args[0], item0);
> + if (!eq_cond)
> + return cond;
> +
> + if (field->table->pos_in_table_list->outer_join)
> + {
> + // outer join: transform "col IS NULL" to "col IS NULL or col=0"
> + COND *or_cond= new(thd->mem_root) Item_cond_or(eq_cond, cond);
> + if (!or_cond)
> + return cond;
> + cond= or_cond;
> + }
> + else
> + {
> + // not outer join: transform "col IS NULL" to "col=0"
> + cond= eq_cond;
> + }
> +
> + cond->fix_fields(thd,&cond);
> }
> }
> if (cond->const_item())
>
>
>
>
