From: Date: October 18 2007 2:19pm Subject: bk commit into 5.0 tree (gkodinov:1.2544) BUG#31221 List-Archive: http://lists.mysql.com/commits/35835 X-Bug: 31221 Message-Id: <200710181219.l9ICJ7oO016519@magare.gmz> Below is the list of changes that have just been committed into a local 5.0 repository of kgeorge. When kgeorge does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-10-18 15:19:04+03:00, gkodinov@stripped +13 -0 Bug #31221: Optimizer incorrectly identifies impossible WHERE clause No warning was generated when a TIMESTAMP with a non-zero time part was converted to a DATE value. This caused index lookup to assume that this is a valid conversion and was returning rows that match a comparison between a TIMESTAMP value and a DATE keypart. Fixed by generating a warning on such a truncation. mysql-test/r/derived.result@stripped, 2007-10-18 15:19:02+03:00, gkodinov@stripped +2 -1 Bug #31221: fixed an existing not-precise test case mysql-test/r/ps_2myisam.result@stripped, 2007-10-18 15:19:02+03:00, gkodinov@stripped +2 -1 Bug #31221: Warnings cased by existing tests mysql-test/r/ps_3innodb.result@stripped, 2007-10-18 15:19:02+03:00, gkodinov@stripped +2 -1 Bug #31221: Warnings cased by existing tests mysql-test/r/ps_4heap.result@stripped, 2007-10-18 15:19:02+03:00, gkodinov@stripped +2 -1 Bug #31221: Warnings cased by existing tests mysql-test/r/ps_5merge.result@stripped, 2007-10-18 15:19:02+03:00, gkodinov@stripped +4 -2 Bug #31221: Warnings cased by existing tests mysql-test/r/ps_6bdb.result@stripped, 2007-10-18 15:19:02+03:00, gkodinov@stripped +2 -1 Bug #31221: Warnings cased by existing tests mysql-test/r/ps_7ndb.result@stripped, 2007-10-18 15:19:02+03:00, gkodinov@stripped +2 -1 Bug #31221: Warnings cased by existing tests mysql-test/r/type_date.result@stripped, 2007-10-18 15:19:02+03:00, gkodinov@stripped +30 -0 Bug #31221: Warnings cased by existing tests mysql-test/r/type_datetime.result@stripped, 2007-10-18 15:19:02+03:00, gkodinov@stripped +2 -0 Bug #31221: test case mysql-test/t/derived.test@stripped, 2007-10-18 15:19:02+03:00, gkodinov@stripped +2 -1 Bug #31221: fixed an existing not-precise test case mysql-test/t/type_date.test@stripped, 2007-10-18 15:19:02+03:00, gkodinov@stripped +22 -0 Bug #31221: test case sql/field.cc@stripped, 2007-10-18 15:19:03+03:00, gkodinov@stripped +21 -4 Bug #31221: - Upgraded fix for bug 29729 - issue a warning only if the hh:mm:ss.msec is not zero consistently for all the Field_newdate::store function sql/item_timefunc.cc@stripped, 2007-10-18 15:19:03+03:00, gkodinov@stripped +1 -2 Bug #31221: don't ignore the errors when storing data diff -Nrup a/mysql-test/r/derived.result b/mysql-test/r/derived.result --- a/mysql-test/r/derived.result 2007-02-26 12:49:23 +02:00 +++ b/mysql-test/r/derived.result 2007-10-18 15:19:02 +03:00 @@ -326,7 +326,8 @@ id select_type table type possible_keys 2 DERIVED t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index drop table t2; CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`)); -insert into t1 values (128, 'rozn', 2, now(), 10),(128, 'rozn', 1, now(), 10); +insert into t1 values (128, 'rozn', 2, curdate(), 10), +(128, 'rozn', 1, curdate(), 10); SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices; min max avg 10.00 10.00 10 diff -Nrup a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result --- a/mysql-test/r/ps_2myisam.result 2007-06-29 16:37:15 +03:00 +++ b/mysql-test/r/ps_2myisam.result 2007-10-18 15:19:02 +03:00 @@ -2973,11 +2973,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -3011,7 +3013,6 @@ Warning 1265 Data truncated for column ' Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 diff -Nrup a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result --- a/mysql-test/r/ps_3innodb.result 2007-06-29 16:37:15 +03:00 +++ b/mysql-test/r/ps_3innodb.result 2007-10-18 15:19:02 +03:00 @@ -2956,11 +2956,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -2994,7 +2996,6 @@ Warning 1265 Data truncated for column ' Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 diff -Nrup a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result --- a/mysql-test/r/ps_4heap.result 2007-06-29 16:37:15 +03:00 +++ b/mysql-test/r/ps_4heap.result 2007-10-18 15:19:02 +03:00 @@ -2957,11 +2957,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -2995,7 +2997,6 @@ Warning 1265 Data truncated for column ' Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 diff -Nrup a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result --- a/mysql-test/r/ps_5merge.result 2007-06-29 16:37:15 +03:00 +++ b/mysql-test/r/ps_5merge.result 2007-10-18 15:19:02 +03:00 @@ -2893,11 +2893,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -2931,7 +2933,6 @@ Warning 1265 Data truncated for column ' Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 @@ -5914,11 +5915,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -5952,7 +5955,6 @@ Warning 1265 Data truncated for column ' Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 diff -Nrup a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result --- a/mysql-test/r/ps_6bdb.result 2007-07-02 00:28:07 +03:00 +++ b/mysql-test/r/ps_6bdb.result 2007-10-18 15:19:02 +03:00 @@ -2956,11 +2956,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -2994,7 +2996,6 @@ Warning 1265 Data truncated for column ' Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 diff -Nrup a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result --- a/mysql-test/r/ps_7ndb.result 2007-06-29 21:05:41 +03:00 +++ b/mysql-test/r/ps_7ndb.result 2007-10-18 15:19:02 +03:00 @@ -2956,11 +2956,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -2994,7 +2996,6 @@ Warning 1265 Data truncated for column ' Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 diff -Nrup a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result --- a/mysql-test/r/type_date.result 2007-10-04 10:15:24 +03:00 +++ b/mysql-test/r/type_date.result 2007-10-18 15:19:02 +03:00 @@ -146,3 +146,33 @@ str_to_date( '', a ) 0000-00-00 00:00:00 NULL DROP TABLE t1; +CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (DATE(NOW()), 1); +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +COUNT(*) +0 +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +INSERT INTO t1 VALUES (DATE(NOW()), 2); +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +COUNT(*) +0 +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; +COUNT(*) +0 +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +ALTER TABLE t1 DROP PRIMARY KEY; +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +COUNT(*) +0 +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +DROP TABLE t1; +End of 5.0 tests diff -Nrup a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result --- a/mysql-test/r/type_datetime.result 2007-09-22 10:48:39 +03:00 +++ b/mysql-test/r/type_datetime.result 2007-10-18 15:19:02 +03:00 @@ -59,6 +59,8 @@ t drop table t1; CREATE TABLE t1 (a timestamp, b date, c time, d datetime); insert into t1 (b,c,d) values(now(),curtime(),now()); +Warnings: +Note 1265 Data truncated for column 'b' at row 1 select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1; date_format(a,"%Y-%m-%d")=b right(a+0,6)=c+0 a=d+0 1 1 1 diff -Nrup a/mysql-test/t/derived.test b/mysql-test/t/derived.test --- a/mysql-test/t/derived.test 2007-02-26 12:49:23 +02:00 +++ b/mysql-test/t/derived.test 2007-10-18 15:19:02 +03:00 @@ -211,7 +211,8 @@ drop table t2; # select list counter # CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`)); -insert into t1 values (128, 'rozn', 2, now(), 10),(128, 'rozn', 1, now(), 10); +insert into t1 values (128, 'rozn', 2, curdate(), 10), + (128, 'rozn', 1, curdate(), 10); SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices; DROP TABLE t1; diff -Nrup a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test --- a/mysql-test/t/type_date.test 2007-10-04 10:15:25 +03:00 +++ b/mysql-test/t/type_date.test 2007-10-18 15:19:02 +03:00 @@ -149,3 +149,25 @@ INSERT INTO t1 VALUES (NULL); SELECT str_to_date( '', a ) FROM t1; DROP TABLE t1; + + +# +# Bug #31221: Optimizer incorrectly identifies impossible WHERE clause +# + +CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (DATE(NOW()), 1); +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +INSERT INTO t1 VALUES (DATE(NOW()), 2); +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; +ALTER TABLE t1 DROP PRIMARY KEY; +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); + +DROP TABLE t1; + +--echo End of 5.0 tests diff -Nrup a/sql/field.cc b/sql/field.cc --- a/sql/field.cc 2007-09-29 02:27:18 +03:00 +++ b/sql/field.cc 2007-10-18 15:19:03 +03:00 @@ -5282,7 +5282,7 @@ int Field_newdate::store(const char *fro { tmp= l_time.day + l_time.month*32 + l_time.year*16*32; if (!error && (ret != MYSQL_TIMESTAMP_DATE) && - thd->count_cuted_fields != CHECK_FIELD_IGNORE) + (l_time.hour || l_time.minute || l_time.second || l_time.second_part)) error= 3; // Datetime was cut (note) } @@ -5329,10 +5329,16 @@ int Field_newdate::store(longlong nr, bo else tmp= l_time.day + l_time.month*32 + l_time.year*16*32; + if (!error && l_time.time_type != MYSQL_TIMESTAMP_DATE && + (l_time.hour || l_time.minute || l_time.second || l_time.second_part)) + error= 3; + if (error) - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - error == 2 ? ER_WARN_DATA_OUT_OF_RANGE : - WARN_DATA_TRUNCATED,nr,MYSQL_TIMESTAMP_DATE, 1); + set_datetime_warning(error == 3 ? MYSQL_ERROR::WARN_LEVEL_NOTE : + MYSQL_ERROR::WARN_LEVEL_WARN, + error == 2 ? + ER_WARN_DATA_OUT_OF_RANGE : WARN_DATA_TRUNCATED, + nr,MYSQL_TIMESTAMP_DATE, 1); int3store(ptr,tmp); return error; @@ -5358,6 +5364,17 @@ int Field_newdate::store_time(MYSQL_TIME make_date((DATE_TIME_FORMAT *) 0, ltime, &str); set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, str.ptr(), str.length(), MYSQL_TIMESTAMP_DATE, 1); + } + if (!error && ltime->time_type != MYSQL_TIMESTAMP_DATE && + (ltime->hour || ltime->minute || ltime->second || ltime->second_part)) + { + char buff[MAX_DATE_STRING_REP_LENGTH]; + String str(buff, sizeof(buff), &my_charset_latin1); + make_datetime((DATE_TIME_FORMAT *) 0, ltime, &str); + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_NOTE, + WARN_DATA_TRUNCATED, + str.ptr(), str.length(), MYSQL_TIMESTAMP_DATE, 1); + error= 3; } } else diff -Nrup a/sql/item_timefunc.cc b/sql/item_timefunc.cc --- a/sql/item_timefunc.cc 2007-09-22 10:48:34 +03:00 +++ b/sql/item_timefunc.cc 2007-10-18 15:19:03 +03:00 @@ -1603,8 +1603,7 @@ bool Item_func_now::get_date(MYSQL_TIME int Item_func_now::save_in_field(Field *to, bool no_conversions) { to->set_notnull(); - to->store_time(<ime, MYSQL_TIMESTAMP_DATETIME); - return 0; + return to->store_time(<ime, MYSQL_TIMESTAMP_DATETIME); }