From: Jorgen Loland
Date: June 9 2011 11:57am
Subject: bzr commit into mysql-5.5 branch (jorgen.loland:3434) Bug#12561818
List-Archive: http://lists.mysql.com/commits/138946
X-Bug: 12561818
Message-Id: <20110609115757.54A2D79C@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============5238428870301075206=="

--===============5238428870301075206==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#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())


--===============5238428870301075206==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/jorgen.loland@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: jorgen.loland@stripped\
#   4dauk9cm8bbvbvcs
# target_branch: file:///export/home/jl208045/mysql/mysql-5.5-\
#   12561818/
# testament_sha1: a8fed7fec2c9d1d61a429a467a1b9eccfadd9f8f
# timestamp: 2011-06-09 13:57:57 +0200
# base_revision_id: marko.makela@stripped\
#   ppbvd6s2eyavdk2g
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWVaDCsAAB2N/gEV4AoB59///
f/f/yr////pgDl6+++ve93b31l53vLtVds9HUuHtAVvuGpT7fPY1TXp7H3GH07tYcMkTJpDRPSHo
nqaaaHkm1DTQyANAaAANAAZEho00BqbSmjCn6RNPQjQ0AAAAAAANAKaYjVNTyTeqDQZAAAMmEAGT
QNNGmgEiIpiYpgTIJqfpTyno1GMSZHpMgAwgAaDQIoiEmGmpgRqnlNkm1A9Q2o/VGQG0nqMg00B6
mm1ASKBAmmmmgmEASfoKnppPQnlPNUA2U00HqA0HqbFX78ijxBxpBFRHsHsFrs1/90Y4cWGzoqcv
XGRaqdpJw5ESCm/5xYx4+unRc/LBUq+6QjLYrgjoPB38JQsPDNORQhVnzxLq90cfqhMk4eraipq4
VzCh6deGaW9ljqgbFfyU/hmh1yNzMktt1mQMmqRB228OOB7I3dmZ78R4lrOKX4VnloM7mdWkoAAz
e30w7KarVIIEAMoJb5SBSHP7dvsGFrDo3omz9T0hUMm04SuM5vb61fLfHsXVh/d95giCIIh6F5dH
9WI9p+RAc+euFgDKbZLzKicnKGzo26p98JbCi1IZlG3RUzUGP5JpCEGyB4HI28VV9pOVnXGRFbu1
XHqPQMJtLsXOsQzonaZwWEAupVL23obZyEhJXl9K1dS6WH06xXmyAUD0yD4NbrbxIdzfp8R2dRnb
9DL6SEtAyGsmZG+IwJBr9T2ePTLNQnzm3HYFRW525PndkYxnXbrLbg6ylEp8/E15tpWO4w4NDC7y
4dyMwMv0yOM2lOGxOje6qtu6jHI8Tj+vFQlVMz1Z1kHE7FqAuayiRolpmeby4kbG5/Fkbq6KKN2m
eknxrTUE8J2kdinjoeZgacmrZ7AqeuuReqoQIO4pbPeB2i9ovkNOKhyFAXIZMJBMZMJBTTmLRXSs
h4MpK9X1wWxzk9jdJE2dGh5kJMqAMqdcT1cHPWpnNpVTKrtqwHMbwUDmGPsjsnw5vovPKW9txber
vI5FTnQ2d/J54S+pxDbVAloGQm3+ImZAWgrMFYgOsiawIzNH+cO77aGhB669p0a9IyHgrAbCsDVA
tZerOYYoEtBAR9cHz2Ax2XBWw9XuTTy78PjA4TPuHMq+dV8gnWp4HiHKAfXMQ+9W4IpqhM8xVKf0
7oVfrXgWGvnV0/TP6lIYujfauel558NGiHmat+7XkCqRegM+ZC9icRGr2ngZneWW7ZceN2zsjBQe
YIc85BoskEEnDAlD4CA66wqJQ2zVu7spJVaSW4PImDGMaYxJjGNAEEku4PvAYMHwKDFnF2qgq0Kq
F5itFiuOQCCRPwbrYFnCiytm8IgNTQrPRUSMq2hSVwhZhVrL8bSvfYVFZEddL9QtLlCgTBpUjaNY
wkEAM7QiDQEMNTMquNUFZHBNC3nd9tEB8qCLlCJOnfEWRZ+5XteybNj2Mteo3iLBhJeh7dugTg2A
hqGBf0ZuO0GZgrzYsPc4LhYbFzC5goUzvdyIxTErzHfTOSEE7hbI5Nrdoj/YhhA6gopAo6RXsA2e
o0d3CVDsJizGqq3CuhY+lpQKlVTUYkuG0VpZYXT6rDBcKhYCM1x/zOd5W6BTsLrXpTXUJkWfXbWc
sX09QPoUrg8YqMgzkrBBGF5iNwfMSTs3euCpiKlhWsdNuIczmMKxl5oVxpsmYkyYjqnPNLPHhUbA
0GQiGP/UzD3+lQ2yZDPqaR9FuiOJWF7g82gx5TnBZSvFpM9YsxrgIrNWopNhdJzlDz5MQwPfyHhM
2RniZ7o2SsWwEwDJAamczCGkrKEOuJtLMe7mLCm/mpJhKnFdBzmha+qqtU5lqWUdKnEszDRRtYGn
Oh0y4YiIIla4P3N8oGCwWLSaBc+7glIqoEhy02mg1NsCrUTWECg5w1c82Ig29wQNFhQhM1QxGDZl
nMx1CNxtLKi+oRiIzq/x6YqWkStIzl1bWRpSsVua5VwGYCJEZ6vUSauARaRs7jlWnIYpCnTCzkQ2
8uJlWi51ea1VFIVpZbVFiiXAZK0rcKy57fJ1VdjCiczD8BCKjakYt82g+tjO7Bcs7s59ZDetUSlW
8BaqXeNd3cwLj2uRuU+deH1h2uU8ZAn3PjSEek26ghiIinataIp6P2e6SrRNAeaYMYmRA2DF90yr
4/AyTK2UosFJZDSkjBAUoymFUDuCjVovnQKFDUAZWqVe4NjLauYIyXj9fAIcKw8A65bn9gCFuB4E
qhNGRpB3oBMz5mpqqAcWjuhYhiBX0blwQLVwRTALaZxD1rLlIoja2tgMtChDVCqSwhIp6IENN1dC
VXuR2CEiVfSSw0Jhg2tryIUKsGQ2YGQloFCCIYS8gTGlHmLn1OoQ5zI1cQgwElaI+kvQuG/QBX+C
rUFYp8MQDcr0YJaQK5A4FzicYQ6mSQKOVbW1LW7C2IXNlDcKwQixYrFZQylwZlCgAwDy9fULxAxj
BhBTk6MSBAl64COLEtZ2MBUYXnLBBvMYw7N8h4l1gL7wbH4h8A2MbBh7ixHa1772fmNC89RoXmSJ
nz/SZGdr9p8M5zIDClh0Xfcuo6ioKxaOAfEr4uF/xl8wsgtOgLroLnlZJIauKED5ackHRoRdMbFS
7cStJFGosIEupJKxRvM0hG341HrViysyPV2i1MDnM4Tf6MeOJ7vMuJUOI57vWtSqIy4S0s45Oi5G
5+uMzsEbBHFR5LpBcMzWRcazOduaZr5GYx6zeieFPe3uapbdcgzKWgLRHxqravQG/6C0+c/bJHbE
D9MDcGI+JB3E7RG8dxQupWQ1YQoGtHKRPSOBh3tSgnwT6eniXq2DrRnUC0rrR2lEE0r4Anwf64G/
BCLGp9W+IjrEec7CS9mjDoM9O8D4dSu76tNiyAVT38LdNNmHPdwjbOr8J7xkicffzFDsz+KayobV
747UpVleiESmhkkLWURhENBhe86kAGBBEK3TJrgzYdh3GiRuPIjS271q9SW2UIcRFBWicxBTUdQz
QtlCtiOMGkTREwraR72bTXvIk8GDJyhsO/u8rJ80Uo6eVUmxYiJQXWa5y6rWnJ+FlOBmIdyxzoRo
KifNwamhZIliIcQ0qBn/btf4yKVKsEJoZkyww6VRz4D6SrU1TR5bY6QtEN6RME0sPGAsEwieU0vF
VWK0GNnoQfXufiajUOWleXE6CR2Ks9FYomF4Ue58To/FfZsTW1YqyC7jMb0RSJHQQKi2YdDH3VKO
jIHpUFVAM10tcOhPI3hQ6zxcKKayovAFF64KJOMK0gPSQQfnYxoaaNfdBtKI2yoTsKutUWXaqzMC
JzS4W07FgtucETLECji66P1wVwL9+8uIORpJOCs1MbGiIzRuqUzcKF+N95a6i2luHXulWLYJ43lv
JsGqOA1yOCoLPIwH4TUSlFCwuVfs42rFl6G+MACGKrPGYJEslEsFUwImmjkKjrQGqDarmtcKUDzJ
HS0xha2PcNNYBQUdEV/mooiK4RBTWkoPbGzBYQEb6AahifFVEajaUylut3pA2uNwLVySO3sv3jqS
yNdgZKmqX0ONu4tKgBnWtgiMRZYLaioi3jnaQ04HisStuoJkzoxuqXhO2sHFJxpAsKlcWQQSO681
vDIwREUkG6bP6GVnZwrJsISKTvLiVSRNveTWazLEkBUY0NwJgQkOYIU2xsLFsVUqZGYuSojiSB7b
tdo8jcHKmsZYJtGtILeN7lVYHTqsOkaGDQaXrJksbL9Rhd5orSb7w3viE9lPJxqSqWaIhNMIxkUB
YuVFbIuRgLttcLGG/7J+n5oDYKh+GlIpFEMy7yiZuA4L4VrCPhNIOfwZQDw1TlBLjvBRW9S3qvk0
wMFWHvEWe7DAPfeqjus0Zu9d1ImePWV6mG2/OC6cdiyX8zIWkBpM70QJ83ihSm0kdWJZQo3BxK/X
XKgqeFHxf9Pka254AtwBXc6nRTg1By2KiOCoofl067gjAV6sT+AZDyjIYm2dBwzSv2ODF8EwdeMT
ofvNSy5w1MWl0ctb3NoYBq9WsyOG2Y+EodUlMLq9zICJeMcNh38L5gsrGNCpbukLpuKHS6LzVN64
VFf49K4GXqlhyroeERERF+PIppKrQ0Btym4voLO0S7xXigTrg7Mk200wOZGREjet2469cVpNl1tj
WrUNNlMSVL8wHGQrEQwC9h2kAP0cc1ys8hOn0PywkUKsRmNkBBLJlNPtrGt50AN4nfDjLCLwVBTa
pUoOwGLQNBmY0xd044cZtI4kTg89Qx+5byymQoJBaIzMbREdTZQnGQmdrmXvaXlmWbHP9uzn2MdW
mAp1GnWJDgKb9SVjQ3Ne/r8JbdmvX3ShblQyPv5moYr1S58LBxWsaHXQwed07wxYh2uPAo3qTB9D
yMvMxobQtTv4ZuWdDa3wvS5bo1TJBD+jY1bcH4jcJQiQjwHT5zuYnA8oH/F3JFOFCQVoMKwA


--===============5238428870301075206==--