List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:June 9 2011 11:57am
Subject:bzr commit into mysql-5.5 branch (jorgen.loland:3434) Bug#12561818
View as plain text  
#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())


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110609115749-4dauk9cm8bbvbvcs.bundle
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