List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:June 9 2011 2:47pm
Subject:Re: bzr commit into mysql-5.5 branch (jorgen.loland:3434) Bug#12561818
View as plain text  
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())
>
>
>
>


Thread
bzr commit into mysql-5.5 branch (jorgen.loland:3434) Bug#12561818Jorgen Loland9 Jun
  • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3434) Bug#12561818Roy Lyseng9 Jun