From: Jorgen Loland
Date: June 10 2011 8:22am
Subject: bzr commit into mysql-5.5 branch (jorgen.loland:3440) Bug#12561818
List-Archive: http://lists.mysql.com/commits/139037
X-Bug: 12561818
Message-Id: <20110610082252.D768F8FC@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============2385555475388943162=="

--===============2385555475388943162==
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:dmitry.shulga@stripped

 3440 Jorgen Loland	2011-06-10
      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-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
+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
+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())


--===============2385555475388943162==
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\
#   whqn4twrpcy7envl
# target_branch: file:///export/home/jl208045/mysql/mysql-5.5-\
#   12561818/
# testament_sha1: c5eddf5eb6828a6cca6b82a1ff2467dc0194d33a
# timestamp: 2011-06-10 10:22:52 +0200
# base_revision_id: dmitry.shulga@stripped\
#   ie1fg12b4adfcjc3
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWf1hzzAAB13/gEV4AoB59///
f/f/yr////pgDn6+++nc+vNs833m4630ffH3Yu+8kOi+vpt5z7bxPp9t3sfZgX1Zoe77uGSIxTBN
DTCBQ8yCnpqb0o9BBoGg0ADIAJKamRpkAE1NKfpT09FPU8phMhoZPU0MQ0AAaAGgEEymkxTaI2oa
AeoA9QAAA0AAACREIkzQVP1HppqPVG9NJBk8Keo9TINPUNGJoD1HqBoEUhGpknqp+aGVT2inqeSe
nqnqAB6gyMh6T1NAANPUyAkSEAmQEyniaamSeJom1T1PUGyE2oPEgPUANNM1Xt0qPGG9IIoI+QfI
Lnl/3C+GufCZzfVGE5R6U2ByIk8jfpe+Q+vz16nNslgwdaRid5XiogeDv8LAsPDINZURXjjIvs3y
9P5IoTcZ9qK2rxXsKt9zx2T3stdcA2LHtp+7NDrZuZkltusyBbTIg8l+vDIduzo0UpkGxbVjCd8K
7mgz2M6tRQABm8/jh2U1W6QQIAVAlqKQKQ3+zX2jC9h/WWJu7D1BULazYVwnN7PWksF3rS12LjTG
xsbXIl39HbVC/n1jDdlpmqBpttLz90K84ftWriR9gTQFFiRVqNlin9g695MQjBygaxpG3iq/zFJ2
85TJLydavPUeYYUaXxXStAYopcYgsIBepUL23obZ0kgkU5/JavUu9h79YrzogFAvRAL2tVqrhDly
q7P4i09HiGV2SZfiORSBM1GZhz4jAmGzxfR4dU8KinSbdHQFZY6Xan0u2UpUsu2F1Y6znE672Q14
eM8TkYcGhheBb9GNoGX4ZHG1lrDZDwl3USW11VqdhMf2wuHQ9FkXrmNddMjBVktGkHwixZicRc90
Hep7P6tJ0HmnnOMLrJ7ErZXxdepXaQk4NYvsvhWFb2WTMyriCHeVXU3iOwX1C+Y05KO4UC7hlAMA
4MOAwEZrCoLBqJrMQ1yvNC2zdj2zOGDs6ND1ITZUAZU5orqu58jDObTsm5Z21QHMcAUDdMHzdPRP
Nn68h5fj8kYy/jWumFBV5E38N30RIxfY5htqUS0DITb/ITLAtJWYKxAdBE1gRmaP87+77qK1n37/
BdXXzNp9uIV6sQ56lXLTEvYbmPOIHhjqwsyC+yoUsO72ZT+v49fxAYmrkHBV8qrzidSneeMeIA4n
WpXoho0DtFJQ8G9rFH6F1kjWb++/wp44URHNvkuFVp28uGDdppvtOIKlFiAtmJMjFORl8Z2E16yl
dcK3YD1vFIF1A1jG68U0pDGQCg9BAS1KUNs2beSUkqtJLaHSmDGMaYxJjGNAEJJcYeoBgwewsMWA
tysLIhYkKyFWiotcgEEicyj4BXFAiO8TBkERLRq485QmJ2dZYkxHSNXZ3XV75BW3QbVlrZCYNLPH
CNZbSkEgM6giDOEMN7NCFjWM17pLCYlwNHsogPnyMyiRSqBbSz9qxatKGHhbP0rKKAdBDgvCjkHN
diJGYStgN4ybmbZwWLFCHn01B8UmsXmCsni/N0pbkxLq3bVQ5QgmTOs2K/yizxtN2Aj/ghhI6wkC
jWLJgDVhmvkcoLxGkV5Vb5JloiuLGLe5FNnZbfaLhBIsMy3wLER1wuRgXD9rPIOFmjFQtJEwkL78
uPlO5borKBNnJhQMhnWwzv5iGMxylt0sqpiqttJq3HkfabnIocYGVpQxIZPEcCZKBbYM1xCEaX3U
ckzcFatq0LyECv/Fh8nfQmqxGvxdc4RwxVIjXaop0MsYlirHDiDX4dWUIZwxG4aucE86snss2T9p
6Wz4XTk41yusyFgezPEmNhjLhzmbDAq6tR11F966K4YTpZ0rw5FNLNmuOG5YrKmuKoVI/IvuOvZP
K1tSMnabFFELaulbQWxdLHuwRPIkYSqXPA0IWeEQx3FcpTimbVKwYXEqaCQxnPYgOgMGDjKJyzG3
BsYVF5CYixd7F9J7MadLyWKWhZFlqkccREhGmTgzVj4uFQ6SwQpZQZEQzBzriTJvEtDg8WBIAyyk
5CCRTQCHZ6ApTPL2OeruYUTmYa5SEVG1IxaTug+hjPdgxLO9/cbnc0WbozNDS7reHVAFxw6GqngX
HrPOHmcD5ggT7HtLUkj1HHkEMRET8y0qlvD7SbVKIHjTEMkDUMVah5/R6DSmMvTRKTJhQxrSiBMY
TKykozVQH+hKqpS9KAkJO4AYKiUfQGbJtX0gji1D47hDYsNw5Sav7AELUG5KISmyGcHPAJg+VoZH
bYAb9vohYhiBX8uVcUC1cUExItIZBKesQ86ycCJo2trYDJkTaIUSTCEhT84EN1abUuLFfghbRBAi
ia6lIhpm5VrkgkTTMDbnDAmTaBQgiGE1BAl6TegLXudwh0Glo6BDUK3ie8YI+py6wNHidDJubOXE
AzcE1LsAppC5dQO8IdzIkBN4ltbUta67YhcOIOQaxCK1mWZXBcWhkoUAGAfV4+oXrBjGDGJMXT6k
CBOPYCPL0rWbeBUY3nLBB4333zv8BDxLsgPUDY/APgHQxsGFgH3H1mGK+2R8V+OgnkXNfC8zQE4T
86stVB5ykKhaa+op3Oaz63/8FbLC8JzZbPtmkNXlRB8/fqQeTOX0GxVX8SdxMqakwgntSStUsxh9
/yrLFpr0n08hamByMgl8T6NBw69x8jmuCs7Vw8nDXnqF2wxrHIbBJBB3ZmITSO4p+AjSnUt43zWA
e3c1ZOz2+5cr8Hm+ZfgcESIXaG/sv6qSU5oN63J7kBrVW1lQGr0l51nZaPLED+2BuDAe1XCMhtUL
xiOdXS1cQoGs/GRPhHBxQHiarCfEn4Twb99+zx7TrMuY8UO4dWRmwRV9AwKktMAs5HV+Dgb8YRY1
L9/ERGw+wZoJGDOOZrTonwB8bdqsOtb1UB4p6b0ztjfl01AzMdT3rzhCLfPlDDrZdiupIZq9I35z
tOiQlLhqLTkvtbC1qWZPPWUBQRELmNxy21J+5rrJ2lXfpc+UjtH3G/HL2LQupcZxHlEVCwE6CCrB
bc4nR4KZHUNwMIbUGiRxFq9pkzIjpljHAoNweTbat1WNLmiKtMIwlBLJkaZo6Eb2KmMZD1uEuQjE
VBuWzKwLJFkpDiGFREgITb/e9R2Xo45whMNNaw+c8rmgNTQtTDRHp3S1BcIb0iYJpZeiBZJhI9NE
qrFaDGzsQcxnnPXwzrzk/VBxORVYG0K1uFgFHRbo4HT3Pu5MObi3yekwN6TUocxIqX2BzQRre/tb
mzIOLEwVemeVvbXOvua5Lv+hwsVBpAt0xsgn5yK0D0EIPaxjQ00bOyG0pDbKxO0r5qpamI6FBRLE
EVORhTztm2G8RSwbgr7PG6znzaQrOGakh6HSrk3boIghJkGvfVsN4ywxwwL4qX236ujfSwXQJ6Mx
d2tg1U4Gu04KoWMzIffRVKY6lFpCXc2I5ITfQSIeA74CG/g3UG5jSEoiV1zUniaZQc02ovEtZqSN
rTGGRschwkJkJ5QuFTwSu28sjfAKi7yoe2OjMs0BGxANQxPfRGUtRtLlVeXa3NIG1wcF67kjs68d
460tZ0WhrVWyfa5Xcy4KwBnlUQLTuKEN58GkNODzrIrbrChQ1rqz3zLXGFAHFsIxAGi5SLSEEzlQ
1oGRgiIpIPCbP4qTStKk0TYQkUnKtQjQrFBNvMms9+OJICoxobgTAhIcwQpxRqLlqVUqWZlclRHE
kD227LB1NweVNZpME2jYkFm981RYFQjv12HeNDBoNL1kQTx1mer4OcoyyD1L84jzI/V5EiEkU1No
1tDGSQFq76ldMvRkLsucWsN/sp9P4oDYKo/hpSKpIjBd5UmbgOK+Fiyl10SDj52VAefXScJcd4KS
3qe9WdzTAkIrD3CLPbjiHuwVZ5tWfN2UgxlzK9LDouyBMR3YbFVf5WQtADSZ2xAnu6YUptJHNgXU
KNwa1j8VeBBU5UdX/T1NcOdgI2Aq+lUYv5Mw7lCOR4md5ctIg1gpKQiKbujIeAyGJt3p65pXzuDB
8Mwc+KJ4HvalmJw1LRK254TdREMvszIVnX2LmollhbtxDVTN2nFdGmNI0KKKyH5EPjnWiAmvbQ8q
xtFRX/l0rczBTrOa8zzREREYY9KlOSpM0BtuOovmLO0Sd4F4oE6YOuqVk98DmRkRI33Xbgp1ukxW
61VSioGms3ooZcANg0k20MEvuP8kAe3pwvVvgJ1dr8MplRXoGaLYEE9TKtP6VYNbzqAbzHIOBWRe
CmKVikpIPEGLQNBkxpi99JZcaNI4kjg8axj9y3ltWoCEguEYMbRIWtUkRkryEQp5kvsEc9a5RCUw
y+ajPQU3zIAveJgNRvAkcMJFKTFxerotDGyXSYSgpsQTXetTwj4lUb4J2S1oL10LLxhGItlUIleU
cFJQfO8gnMze8xsahkJoTp2a75bWrrhfcc1b4RAxi2iP5Kar0rxMhEjch5guwuWzati8Q/4u5Ipw
oSH6w55g


--===============2385555475388943162==--