From: Jorgen Loland
Date: May 27 2011 12:04pm
Subject: bzr commit into mysql-5.5 branch (jorgen.loland:3409) Bug#12561818
List-Archive: http://lists.mysql.com/commits/138323
X-Bug: 12561818
Message-Id: <20110527120433.A86D3EEE@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============6922360502674550796=="
--===============6922360502674550796==
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
3409 Jorgen Loland 2011-05-27
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"
instead of
"date_notnull IS NULL" to "date_notnull = 0"
and also do this rewrite for outer joins.
@ mysql-test/r/ps.result
Added test for BUG#12561818
@ mysql-test/r/type_datetime.result
" IS NULL" for DATE NOT NULL column is now rewritten to
" IS NULL OR = 0" and also done for outer joins.
@ mysql-test/t/ps.test
Added test for BUG#12561818
@ sql/sql_select.cc
Special handling of NULL for DATE/DATETIME NOT NULL columns:
Instead of rewriting
"date_notnull IS NULL" => "date_notnull = 0"
it is now rewritten to
"date_notnull IS NULL" => "date_notnull IS NULL OR date_notnull = 0"
modified:
mysql-test/r/ps.result
mysql-test/r/type_datetime.result
mysql-test/t/ps.test
sql/sql_select.cc
=== modified file 'mysql-test/r/ps.result'
--- a/mysql-test/r/ps.result 2011-03-22 11:48:56 +0000
+++ b/mysql-test/r/ps.result 2011-05-27 12:04:16 +0000
@@ -3746,4 +3746,42 @@ FROM (SELECT 1 UNION SELECT 2) t;
1
2
#
+# 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);
+
+SELECT * FROM t1 WHERE a IS NULL;
+a b
+0000-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 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
+
+CREATE PROCEDURE p1()
+BEGIN
+SELECT *
+FROM t1 LEFT JOIN t1 AS t1_2 ON 1
+WHERE t1_2.a IS NULL AND t1_2.b < 2;
+END $
+
+CALL p1();
+a b a b
+0000-00-00 1 0000-00-00 1
+1999-05-10 2 0000-00-00 1
+CALL p1();
+a b a b
+0000-00-00 1 0000-00-00 1
+1999-05-10 2 0000-00-00 1
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
# End of 5.5 tests.
=== 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-05-27 12:04:16 +0000
@@ -543,7 +543,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where (`test`.`t1`.`id`,(select 1 from `test`.`t1` `x1` where ((`test`.`t1`.`cur_date` = 0) and ((`test`.`t1`.`id`) = `test`.`x1`.`id`))))
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where (`test`.`t1`.`id`,(select 1 from `test`.`t1` `x1` where (((`test`.`t1`.`cur_date` = 0) or (isnull(`test`.`t1`.`cur_date`))) and ((`test`.`t1`.`id`) = `test`.`x1`.`id`))))
select * from t1
where id in (select id from t1 as x1 where (t1.cur_date is null));
id cur_date
@@ -555,7 +555,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where (`test`.`t2`.`id`,(select 1 from `test`.`t2` `x1` where ((`test`.`t2`.`cur_date` = 0) and ((`test`.`t2`.`id`) = `test`.`x1`.`id`))))
+Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where (`test`.`t2`.`id`,(select 1 from `test`.`t2` `x1` where (((`test`.`t2`.`cur_date` = 0) or (isnull(`test`.`t2`.`cur_date`))) and ((`test`.`t2`.`id`) = `test`.`x1`.`id`))))
select * from t2
where id in (select id from t2 as x1 where (t2.cur_date is null));
id cur_date
=== modified file 'mysql-test/t/ps.test'
--- a/mysql-test/t/ps.test 2011-03-22 11:48:56 +0000
+++ b/mysql-test/t/ps.test 2011-05-27 12:04:16 +0000
@@ -3357,6 +3357,36 @@ disconnect con1;
SELECT *
FROM (SELECT 1 UNION SELECT 2) t;
+--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);
+
+--echo
+SELECT * FROM t1 WHERE a IS NULL;
+--echo
+SELECT * FROM t1 LEFT JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
+--echo
+SELECT * FROM t1 JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
+
+--echo
+DELIMITER $;
+CREATE PROCEDURE p1()
+BEGIN
+ SELECT *
+ FROM t1 LEFT JOIN t1 AS t1_2 ON 1
+ WHERE t1_2.a IS NULL AND t1_2.b < 2;
+END $
+DELIMITER ;$
+--echo
+CALL p1();
+CALL p1();
+
+DROP PROCEDURE p1;
+DROP TABLE t1;
--echo #
--echo # End of 5.5 tests.
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-05-06 08:27:04 +0000
+++ b/sql/sql_select.cc 2011-05-27 12:04:16 +0000
@@ -9519,24 +9519,29 @@ 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 (((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);
- }
+ COND *eq_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2));
+ if (!eq_cond)
+ return cond;
+
+ COND *or_cond= new Item_cond_or(eq_cond, cond);
+ if (!or_cond)
+ return cond;
+ cond= or_cond;
+
+ cond->fix_fields(thd, &cond);
}
}
if (cond->const_item())
--===============6922360502674550796==
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\
# nt7qovmx52h2m4bq
# target_branch: file:///export/home/jl208045/mysql/mysql-5.5-\
# 12561818/
# testament_sha1: fe71dc31925259ea9a6ffbb7bb4cfbca64cfd772
# timestamp: 2011-05-27 14:04:33 +0200
# base_revision_id: dmitry.shulga@stripped\
# vx1bqttx302xs2dk
#
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWWfvKogABzF/gEU4AIB99///
f/f/iv////BgDr9Xt9uZ3feXdz02m+tB2s6XHAKQ4Ja0e7LrILKx7t2S1VXhkhqIyj1PUw1PU2KM
TTTQ0NANNAAA0AGgZQTSbCp5TwKNAGQaAGQAAAAAAap6m0QjQTBNomEYAhhMCaYjJhMTAAEiJGin
pkmU9pTNJpkaamniI0aNGmZTQMjQNDQBFIFGgMk9KeEaVN6U209ITKeoN6kejKaPKbUNGmnpN6kE
kiYITCaTDRTyaaTEm01PU9SaaPUDPUh6gA0eoLzqEj1PwsSDcbgYghVDB6h6oOB27GixHXTXcWjm
UNbp2nMvFaVYNE9dfSr7DzreWrefuyWal3F831VIzibUHMtZmIx3vn9PGMRo3Sz2IjaHJkT4O1eW
DmJYSi9xW6ZRZYGxqeX94sNt0tt2ukI45SR5tpz6f47sc1my6mAa5k9eOWnFBRKiaGY+10DSSK/T
jxq4Q0nwQDCARMEycO2CLSD2Gn5a6V1H9ihM9Jbc4eHxQ0HGXQH4B5QhbbbBtdCDHuZe7n8QuVkc
2/hqoEllvbXLqrFw8Z6zpSI5iT/hOEK2ix3FHT9rlfR1NHidw7I4fiubH+TyHHA1FQYirO4r4p8/
sHVW9r3Lf2ryE9ui5LUWqFhblARCZ5j4Ni+IggJ+P4OHqDnB7di3OMkJB55D24uL+sYeId+c+8+3
1k3BhoQ1HEiF0B+RygTYFkPiQSUB6S2Hgtt7OolGAQIs+jTYx0sKCkoc1ViubLju72TxdN5xM09m
i0Vhv5ZX0sNEXz544M26asIwesvOZt6lcn7w1GmwQpsRAFKQwagl0psBwJZIySSCCEWl1/rMlVVZ
Yn5mwGH2hZC1sqTfFEI78iBYpucQJYVWnQX9Bf1GTPTusThd5HG0rAsqryeRq0GibjdCpsCIwfga
WGFB2s0sEGu6cemUdpZtyOA0BlCALNJn5jBJDf3sEcRwuVgff5gG4dyRS8iR6QDEOLbg43IR0KEQ
tAEsQ4bG37RPsYXAWReWxBSDUxSZihR0fSOc4eWw7PWqAwxw5rI9lhxKi4uk80sEA43DTauDP9DY
OLkh3ZjbKZ0vjIhFN5eQXh+Nu1BWJH4CT2j2r43ch3qHKrGog9iExr0eeWP83nc9/MH1Ch9sHrQm
XYvYSN9nCd2/DB3xM91e23gAHMCiLgRG1JXj3smTNkmRqtDzJQiEprvDW596QHaDfDgApGidkEhI
zdKgSGxI4gSGJHIHvIgYobxxftbNxNd02btooSmx1PzGtoUTIvkVwrgVhfkKKCaSqbr7U1ndVFBl
s1VP5Zxl1xyGnG6pkztJROskGJjUPRtIUkICBNL5id+QyCGxxQkp+bk4vGAMzT6QruHJBenB6zWD
FHasFEgBewx9JmQag6JlT79tdEVuIFHnUUEIK23ZZC0LEFFW1STzv1d1wnI+znJLWuYczfnA5LI2
hCS7BiY9ZCoHdsIBZcgZ3nvE8+8SBqQXMkEELXsqMTDsOscWqanlSZo2ka6DknZnZZTSySrJkzi8
VB8OOC3x0RSuLwNBzGLjQT8F8zCN4LIFmsIakYX5Wv/PZD2LKaqYxKF1ZMoNdYpkjSTcdt5NS2dr
9F85Uue+43EIFpKrPzcpmlmK1jo2uBUDNjQJngmRcaTZhZjGd2a+Kqqmb042GktxkTNsgpFo3aTy
dKYuJ6gbigY6JH5szz83c/Qw8ni2OcXhAtNsYjF26qEk+UE4eWwUYLpZXYtZrHI2d8bjd1nAg0OX
E0TbzQciDIfo8Xcc3scpnet5rmtOGeyRHYCis3JthtkQOolk8TEOI9NaRpYZnt2jymFU78AkThas
KOZEaS6adxjG2Kgada2LDLIrClFDnJJKb1AgbqBTPSISiAqsgM4qmiWJALMRzWTMFc869FZrOvcZ
grF1UxMWtxc1j2E/Y8XLdWb7KbZFDaZlxoGzmXTWtjbP0IERi42Gl1eVt91GadUBaAVnUUG2R3Y2
wElptJNZkFbAytpa0LBuCaVgLUVJRowptNu69XFYxgOAxhtTfOSpFgTV2uygoRSuuwsFaEXOEWRW
22iVbngfL8ywcsiRx7YKkhQY815hrgPiNwSN+gYjBcmZobbOoKSMBltpOC78AocG3piIOB5/oM73
dbpR/imPwDwPk7Af6h7EhT0G/AgIiAicHySlktzqH3B7yrNKqfBYGCaHEgLVGoW/vh8SEuYZpBOS
bEgZTZJropJm7Ammk+KmBoLGwTW1bnwMWTYdOA+3KYZ5DsSHMO2ZNuf+CQmB9xnW5slWBTW6SzUo
Juy+EIRCRCJ/vamlSyQa1NYQgwWpqE/NZOciRNCzZmsnIUKENQKsmEkid0CdGUwcQovgshJDkeBJ
hmENNdpWriAoqYAwzUZNNptNaQYr0SrH8n8QxE6gzNXKLqFOgTjsA7pXj/ls5XK143ib3Q6kwWmc
uTUL0OgkM3QlmzZyV1wmnQcIqoAqsVitQXGSwUBhhAeHzeISy4PX1Nj6oA50fV99JDxeQICWkKTq
oY+P40SgtCwy8skKJJGKZbbbbadBB1hrGIYwBnR5XDGOHDGEm0PxB0JCjGdZiI9R6l6IKas+w1Hr
XyqMS1oDw+WgzBFctZ9433dZ95FEkt/8Fx+Ry8x13yvP/BZINVRaxay4tVSpC6DjSN+PrrSOemuE
dr5vIMPYHG5AEwdaXceJXNfJap6j07Q1MjtMB/hYcjI/kuaeR6L3GzVE0lC37WXafm6PNQ3EewdK
AtCqgcwDKZNykO73H8cTxPbfUfMLdMTNFDrKaT3bPDH389SuPaDIE1LBGlUDIEbfYfYe+yPLENkO
DDjWhIwPIvcBuLVY1UZCNNTvkqjM4Dpapyg+I6+uXIrORaFYmtrYoKqoYTINDhJwjPloOZ/yMj6p
DdDPcjHY7QynuNhLWW1WJGs2R2HQQYnNj9KN9qIKfWogeS+sdnfTwx7ly4s2Du/zGYi+rRqhb9Ua
SEasJmym9XO+/Rdewly1FvyWUcPxsEQhhnwnkQ6YuyGuKhxvlxhLvfQiSJHwKjA5VuLbuAYBiG7T
uYHckSAvSgkEmEScVeRMs8UFDGJsYiyK95DB4445vWsx/T9Hk+mzg1HOLJHY7LITF+6a87dbQ/TK
oiIEFU1gti1MoaQbdgich4sBvZL+SKGTKIpym1OCo6TJa0dk5VqHPGd6ZhWIvEgYXq5yXUwjqPrI
oJV1jAxyBYBtPabsV7XHAie/kufgYwPAnKFCo+hdz0fDxdfQ0objyH8+mYU8gYOQUbu5JsGSYNEa
wWgIum1PHz7He1a+hKELAvLb5qVrkumF4DcNdwE8VJ4RG9rjOKdUVAe+QLzjBtFQ285wkBQiIaIX
GTc2cSjkOzQDFznlmL8nUO1Qw2hcMg9BFKpqz6zG6sW7FSQHWppGhTXuYZ+BxKtqU7bpWxooGPKv
bVNdSTYU97MDCZMveZ0XMd5dY2FiMjBGRlgZwvqdxRyp/o5pAX4eja9fCOpiPneDPpcXy15Ozpee
d5LcdrvhjM71DPEQ7yCA1BVZ6Sb+N7ZOEhavMtG6Xi0Boky2gkBKAONUNhY3XNc1JgkQHKJZXtRu
8jvqWUweiYYBXYUyeuqpzB6mWdv2/MSiNF4wESNZZmiioqNK9jy+uYe6VdEQLLI5VrsBSDWnzaQ6
ZN+MRKoTZ1xTsoWUIQqTpTRQbHkGm2+LIQWIbhJihCbJcE1jZU6+VdtVC6ulhNQO8CGMjbbpoNJM
1ie7FzsLQTAnMLcCOst4OUFedBnVdCGmDFaPN8Qx8i1V4GqfW6bI06WuxGa5l7ulGQZQhUtihhBN
HMeXJc56lx1GY1j6CHPAU769/6AniW3+OhGTGjdV5suC+8znLzUBPN1lUoDjUpJNzMqPVB0gFnpZ
84CBKp9aW+vS8C+0xjgOG64tpfg0DgSkpYICUz7JN5AFKTiqzzOMIJePxBZZqSpq72cXoGBg8XOE
3cMZDLxTFS1AQo3ZaDPruU9IngW4Q8dUhL3MZhbvnZ39hsQ7Vw6hQ+bdzhdoxFXAMWQwZVPgmnT7
HGO2eC+MW5MijCdIpTnCxphl/vUVUDER2q1eC7fU7iWkua/Pojari46tWNT3Q8j9pvQ/le2lgqD9
EDorrUoBo+rWtzVhoXSXeAPC8oiIiIiMe+t/uBSyVbRZot3tMpJd+SL0sSBEWFLLZ40QYyyYkuiI
j7XMxpLbpenpllU6aNEFgJB+oD7mA4r4L48rAWhgodAIUM3miRQrgYGSajUq3yj6nidiRmOsOls7
BB9YoAVWjweBzYLxgL2ZhdsH51cBcDoVVxkfY8bkS5c8EQmK7XqjM5qETGb62updr2eJ4yW31j02
qzIZO88l6LABfTkBsYyUwq+jX5xkSAvXMTtnTyaGYX/sbG+rG2Rqb+1LWXxOgG7e8mrUtAMwvRcH
FF1p2KkiSOzHoHetayBZGBGgaNzJDi35I6ZSX73JtdulPO0NjrX3ODSheuBpvyPcZhMc8/4u5Ipw
oSDP3lUQ
--===============6922360502674550796==--