Hello, please review a joint patch fixing a crashing bug MDEV-5969, together with a non-crashing bug MDEV-5971.
Thanks.
=== modified file 'mysql-test/r/temporal_literal.result' --- mysql-test/r/temporal_literal.result 2014-03-06 20:21:25 +0000 +++ mysql-test/r/temporal_literal.result 2014-03-28 12:55:21 +0000 @@ -472,3 +472,144 @@ SELECT * FROM t1; 10:20:30.123456 10:20:30.123456 DROP TABLE t1; +# +# MDEV-5969 Crash in prepared statement with NO_ZERO_IN_DATE and ROLLUP +# +CREATE TABLE t1 +( +year INT NOT NULL, +product VARCHAR(32) NOT NULL, +profit INT +); +INSERT INTO t1 VALUES ('2001','car',101); +INSERT INTO t1 VALUES ('2001','gas',102); +INSERT INTO t1 VALUES ('2001','toy',103); +INSERT INTO t1 VALUES ('2002','car',201); +INSERT INTO t1 VALUES ('2002','gas',202); +INSERT INTO t1 VALUES ('2002','toy',203); +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT DATE'2001-00-00' AS c,year, SUM(profit) FROM t1 GROUP BY c,year WITH ROLLUP"; +EXECUTE stmt; +c year SUM(profit) +2001-00-00 2001 306 +2001-00-00 2002 606 +2001-00-00 NULL 912 +NULL NULL 912 +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c year SUM(profit) +NULL 2001 306 +NULL 2002 606 +NULL NULL 912 +NULL NULL 912 +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +SET sql_mode=DEFAULT; +DROP TABLE t1; +# +# MDEV-5971 Asymmetry between CAST(DATE'2001-00-00') to INT and TO CHAR in preparet stetements +# +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS SIGNED) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS CHAR) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS DECIMAL(30,0)) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS DOUBLE) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +# +# Zero month or zero day automatically mean NULL flag, no matter SQL_MODE is. +# Only zero year is OK for NOT NULL. +# +SET sql_mode=DEFAULT; +PREPARE stmt FROM "CREATE TABLE t1 AS SELECT CAST(DATE'2001-00-00' AS CHAR) AS c"; +EXECUTE stmt; +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +c varchar(10) YES NULL +SELECT * FROM t1; +c +2001-00-00 +DROP TABLE t1; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +c varchar(10) YES NULL +SELECT * FROM t1; +c +NULL +DROP TABLE t1; +SET sql_mode=DEFAULT; +CREATE TABLE t1 AS SELECT +DATE'2001-01-01', +DATE'0000-01-01', +DATE'2001-00-00', +DATE'2001-00-01', +DATE'2001-01-00'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `DATE'2001-01-01'` date NOT NULL DEFAULT '0000-00-00', + `DATE'0000-01-01'` date NOT NULL DEFAULT '0000-00-00', + `DATE'2001-00-00'` date DEFAULT NULL, + `DATE'2001-00-01'` date DEFAULT NULL, + `DATE'2001-01-00'` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +TIMESTAMP'2001-01-01 00:00:00', +TIMESTAMP'0000-01-01 00:00:00', +TIMESTAMP'2001-00-00 00:00:00', +TIMESTAMP'2001-00-01 00:00:00', +TIMESTAMP'2001-01-00 00:00:00'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `TIMESTAMP'2001-01-01 00:00:00'` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `TIMESTAMP'0000-01-01 00:00:00'` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `TIMESTAMP'2001-00-00 00:00:00'` datetime DEFAULT NULL, + `TIMESTAMP'2001-00-01 00:00:00'` datetime DEFAULT NULL, + `TIMESTAMP'2001-01-00 00:00:00'` datetime DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; === modified file 'mysql-test/t/temporal_literal.test' --- mysql-test/t/temporal_literal.test 2014-03-06 20:21:25 +0000 +++ mysql-test/t/temporal_literal.test 2014-03-28 12:55:14 +0000 @@ -247,3 +247,82 @@ INSERT INTO t1 VALUES (TIME'10:20:30.123 INSERT INTO t1 VALUES (TIME('10:20:30.1234567')); SELECT * FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-5969 Crash in prepared statement with NO_ZERO_IN_DATE and ROLLUP +--echo # +CREATE TABLE t1 +( + year INT NOT NULL, + product VARCHAR(32) NOT NULL, + profit INT +); +INSERT INTO t1 VALUES ('2001','car',101); +INSERT INTO t1 VALUES ('2001','gas',102); +INSERT INTO t1 VALUES ('2001','toy',103); +INSERT INTO t1 VALUES ('2002','car',201); +INSERT INTO t1 VALUES ('2002','gas',202); +INSERT INTO t1 VALUES ('2002','toy',203); +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT DATE'2001-00-00' AS c,year, SUM(profit) FROM t1 GROUP BY c,year WITH ROLLUP"; +EXECUTE stmt; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +SET sql_mode=DEFAULT; +DROP TABLE t1; + +--echo # +--echo # MDEV-5971 Asymmetry between CAST(DATE'2001-00-00') to INT and TO CHAR in preparet stetements +--echo # +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS SIGNED) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS CHAR) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS DECIMAL(30,0)) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS DOUBLE) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; + +--echo # +--echo # Zero month or zero day automatically mean NULL flag, no matter SQL_MODE is. +--echo # Only zero year is OK for NOT NULL. +--echo # + +SET sql_mode=DEFAULT; +PREPARE stmt FROM "CREATE TABLE t1 AS SELECT CAST(DATE'2001-00-00' AS CHAR) AS c"; +EXECUTE stmt; +SHOW COLUMNS FROM t1; +SELECT * FROM t1; +DROP TABLE t1; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +SHOW COLUMNS FROM t1; +SELECT * FROM t1; +DROP TABLE t1; +SET sql_mode=DEFAULT; + +CREATE TABLE t1 AS SELECT + DATE'2001-01-01', + DATE'0000-01-01', + DATE'2001-00-00', + DATE'2001-00-01', + DATE'2001-01-00'; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT + TIMESTAMP'2001-01-01 00:00:00', + TIMESTAMP'0000-01-01 00:00:00', + TIMESTAMP'2001-00-00 00:00:00', + TIMESTAMP'2001-00-01 00:00:00', + TIMESTAMP'2001-01-00 00:00:00'; +SHOW CREATE TABLE t1; +DROP TABLE t1; === modified file 'sql/item.cc' --- sql/item.cc 2014-03-26 21:25:38 +0000 +++ sql/item.cc 2014-03-28 11:18:29 +0000 @@ -341,12 +341,29 @@ String *Item::val_string_from_decimal(St } +/* + All val_xxx_from_date() must call this method, to expose consistent behaviour + regarding SQL_MODE when converting DATE/DATETIME to other data types. +*/ +bool Item::get_temporal_with_sql_mode(MYSQL_TIME *ltime) +{ + return get_date(ltime, field_type() == MYSQL_TYPE_TIME + ? TIME_TIME_ONLY + : sql_mode_for_dates(current_thd)); +} + + +bool Item::is_null_from_temporal() +{ + MYSQL_TIME ltime; + return get_temporal_with_sql_mode(<ime); +} + + String *Item::val_string_from_date(String *str) { MYSQL_TIME ltime; - if (get_date(<ime, field_type() == MYSQL_TYPE_TIME - ? TIME_TIME_ONLY - : sql_mode_for_dates(current_thd)) || + if (get_temporal_with_sql_mode(<ime) || str->alloc(MAX_DATE_STRING_REP_LENGTH)) { null_value= 1; @@ -403,7 +420,7 @@ my_decimal *Item::val_decimal_from_date( { DBUG_ASSERT(fixed == 1); MYSQL_TIME ltime; - if (get_date(<ime, sql_mode_for_dates(current_thd))) + if (get_temporal_with_sql_mode(<ime)) { my_decimal_set_zero(decimal_value); null_value= 1; // set NULL, stop processing @@ -430,7 +447,7 @@ longlong Item::val_int_from_date() { DBUG_ASSERT(fixed == 1); MYSQL_TIME ltime; - if (get_date(<ime, 0)) + if (get_temporal_with_sql_mode(<ime)) return 0; longlong v= TIME_to_ulonglong(<ime); return ltime.neg ? -v : v; @@ -441,7 +458,7 @@ double Item::val_real_from_date() { DBUG_ASSERT(fixed == 1); MYSQL_TIME ltime; - if (get_date(<ime, 0)) + if (get_temporal_with_sql_mode(<ime)) return 0; return TIME_to_double(<ime); } === modified file 'sql/item.h' --- sql/item.h 2014-03-26 21:25:38 +0000 +++ sql/item.h 2014-03-28 12:47:18 +0000 @@ -971,6 +971,11 @@ class Item { double val_real_from_decimal(); double val_real_from_date(); + // Get TIME, DATE or DATETIME using proper sql_mode flags for the field type + bool get_temporal_with_sql_mode(MYSQL_TIME *ltime); + // Check NULL value for a TIME, DATE or DATETIME expression + bool is_null_from_temporal(); + int save_time_in_field(Field *field); int save_date_in_field(Field *field); int save_str_value_in_field(Field *field, String *result); @@ -2933,6 +2938,9 @@ class Item_temporal_literal :public Item bool check_partition_func_processor(uchar *int_arg) {return FALSE;} bool check_vcol_func_processor(uchar *arg) { return FALSE;} + bool is_null() + { return is_null_from_temporal(); } + bool get_date_with_sql_mode(MYSQL_TIME *to); String *val_str(String *str) { return val_string_from_date(str); } longlong val_int() @@ -2959,6 +2967,14 @@ class Item_date_literal: public Item_tem { max_length= MAX_DATE_WIDTH; fixed= 1; + /* + If date has zero month or day, it can return NULL in case of + NO_ZERO_DATE or NO_ZERO_IN_DATE. + We can't just check the current sql_mode here in constructor, + because sql_mode can change in case of prepared statements + between PREPARE and EXECUTE. + */ + maybe_null= !ltime->month || !ltime->day; } enum_field_types field_type() const { return MYSQL_TYPE_DATE; } void print(String *str, enum_query_type query_type); @@ -2995,6 +3011,8 @@ class Item_datetime_literal: public Item { max_length= MAX_DATETIME_WIDTH + (decimals ? decimals + 1 : 0); fixed= 1; + // See the comment on maybe_null in Item_date_literal + maybe_null= !ltime->month || !ltime->day; } enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; } void print(String *str, enum_query_type query_type);
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp