bzr push into mysql-5.5 branch (jorgen.loland:3441)

[Jorgen Loland]

 3441 Jorgen Loland	2011-06-10 [merge]
      local merge

    modified:
      VERSION
=== 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-10 08:22:45 +0000
@@ -703,5 +703,51 @@ 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
[phone]-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
[phone]: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
[phone]-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
[phone]: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
[phone]-00	1
+EXECUTE stmt1;
+a	b	a	b
+0000-00-00	1	0000-00-00	1
[phone]-00	1
+DEALLOCATE PREPARE stmt1;
+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-10 08:22:45 +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;
+
+DEALLOCATE PREPARE 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-10 08:22:45 +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);
+        Item *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"
+          Item *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())

No bundle (reason: useless for push emails).