summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/temporal_literal.result141
-rw-r--r--mysql-test/t/temporal_literal.test79
-rw-r--r--sql/item.cc29
-rw-r--r--sql/item.h18
4 files changed, 261 insertions, 6 deletions
diff --git a/mysql-test/r/temporal_literal.result b/mysql-test/r/temporal_literal.result
index 44525aae761..6b7ee2d61c7 100644
--- a/mysql-test/r/temporal_literal.result
+++ b/mysql-test/r/temporal_literal.result
@@ -472,3 +472,144 @@ a
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 prepared statements
+#
+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;
diff --git a/mysql-test/t/temporal_literal.test b/mysql-test/t/temporal_literal.test
index 4bb7eb8d609..177aab0daa8 100644
--- a/mysql-test/t/temporal_literal.test
+++ b/mysql-test/t/temporal_literal.test
@@ -247,3 +247,82 @@ INSERT INTO t1 VALUES (TIME'10:20:30.1234567');
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 prepared statements
+--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;
diff --git a/sql/item.cc b/sql/item.cc
index 7c04dc00880..9b27f730e80 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -341,12 +341,29 @@ String *Item::val_string_from_decimal(String *str)
}
+/*
+ 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(&ltime);
+}
+
+
String *Item::val_string_from_date(String *str)
{
MYSQL_TIME ltime;
- if (get_date(&ltime, field_type() == MYSQL_TYPE_TIME
- ? TIME_TIME_ONLY
- : sql_mode_for_dates(current_thd)) ||
+ if (get_temporal_with_sql_mode(&ltime) ||
str->alloc(MAX_DATE_STRING_REP_LENGTH))
{
null_value= 1;
@@ -403,7 +420,7 @@ my_decimal *Item::val_decimal_from_date(my_decimal *decimal_value)
{
DBUG_ASSERT(fixed == 1);
MYSQL_TIME ltime;
- if (get_date(&ltime, sql_mode_for_dates(current_thd)))
+ if (get_temporal_with_sql_mode(&ltime))
{
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(&ltime, 0))
+ if (get_temporal_with_sql_mode(&ltime))
return 0;
longlong v= TIME_to_ulonglong(&ltime);
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(&ltime, 0))
+ if (get_temporal_with_sql_mode(&ltime))
return 0;
return TIME_to_double(&ltime);
}
diff --git a/sql/item.h b/sql/item.h
index ba7631daeb4..29e727b8d5f 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -971,6 +971,11 @@ public:
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 @@ public:
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 @@ public:
{
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 @@ public:
{
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);
View Lorry Controller Status