From: Roy Lyseng Date: June 9 2011 2:47pm Subject: Re: bzr commit into mysql-5.5 branch (jorgen.loland:3434) Bug#12561818 List-Archive: http://lists.mysql.com/commits/138971 Message-Id: <4DF0DCF7.2020206@oracle.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------020704080604020105070208" --------------020704080604020105070208 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Hi Jørgen, this bugfix is approved. Thanks, Roy On 09.06.11 13.57, Jorgen Loland wrote: > #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()) > > > > --------------020704080604020105070208--