summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/type_date.result46
-rw-r--r--mysql-test/r/type_datetime.result40
-rw-r--r--mysql-test/suite/funcs_1/r/innodb_func_view.result12
-rw-r--r--mysql-test/suite/funcs_1/r/memory_func_view.result12
-rw-r--r--mysql-test/suite/funcs_1/r/myisam_func_view.result12
-rw-r--r--mysql-test/t/type_date.test28
-rw-r--r--mysql-test/t/type_datetime.test29
-rw-r--r--sql/field.cc8
-rw-r--r--sql/item_timefunc.cc31
-rw-r--r--sql/sql_time.cc51
-rw-r--r--sql/sql_time.h3
11 files changed, 235 insertions, 37 deletions
diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 03b942be3f6..4e299ecf0b1 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -349,6 +349,52 @@ Warning 1292 Incorrect datetime value: '0'
Warning 1292 Incorrect datetime value: '0'
SET @@timestamp=DEFAULT;
#
+# MDEV-5041 Inserting a TIME with hour>24 into a DATETIME column produces a wrong value
+#
+SELECT CAST(TIME('-800:20:30') AS DATE);
+CAST(TIME('-800:20:30') AS DATE)
+NULL
+Warnings:
+Warning 1292 Truncated incorrect date value: '-800:20:30'
+SELECT CAST(TIME('800:20:30') AS DATE);
+CAST(TIME('800:20:30') AS DATE)
+0000-01-02
+SELECT CAST(TIME('33 08:20:30') AS DATE);
+CAST(TIME('33 08:20:30') AS DATE)
+0000-01-02
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES (TIME('800:20:30'));
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES (TIME('33 08:20:30'));
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+SET SQL_MODE=NO_ZERO_IN_DATE;
+INSERT INTO t1 VALUES (TIME('48:20:30'));
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+SET SQL_MODE=DEFAULT;
+SELECT * FROM t1;
+a
+0000-01-02
+0000-01-02
+0000-00-00
+DROP TABLE t1;
+CREATE PROCEDURE test5041()
+BEGIN
+DECLARE t TIME;
+DECLARE d DATE;
+SET t= TIME('800:00:00');
+SET d= t;
+SELECT d;
+END;|
+call test5041();
+d
+0000-01-02
+Warnings:
+Note 1265 Data truncated for column 'd' at row 1
+drop procedure test5041;
+#
# End of 5.3 tests
#
#
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 51966147bdd..091ed216252 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -684,6 +684,46 @@ CONVERT_TZ(GREATEST(TIMESTAMP('2021-00-00'),TIMESTAMP('2022-00-00')),'+00:00','+
NULL
Warnings:
Warning 1292 Incorrect datetime value: '2022-00-00 00:00:00'
+#
+# MDEV-5041 Inserting a TIME with hour>24 into a DATETIME column produces a wrong value
+#
+SELECT CAST(TIME('-800:20:30') AS DATETIME);
+CAST(TIME('-800:20:30') AS DATETIME)
+NULL
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '-800:20:30'
+SELECT CAST(TIME('800:20:30') AS DATETIME);
+CAST(TIME('800:20:30') AS DATETIME)
+0000-01-02 08:20:30
+SELECT CAST(TIME('33 08:20:30') AS DATETIME);
+CAST(TIME('33 08:20:30') AS DATETIME)
+0000-01-02 08:20:30
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES (TIME('800:20:30'));
+INSERT INTO t1 VALUES (TIME('33 08:20:30'));
+SET SQL_MODE=NO_ZERO_IN_DATE;
+INSERT INTO t1 VALUES (TIME('48:20:30'));
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 1
+SET SQL_MODE=DEFAULT;
+SELECT * FROM t1;
+a
+0000-01-02 08:20:30
+0000-01-02 08:20:30
+0000-00-00 00:00:00
+DROP TABLE t1;
+CREATE PROCEDURE test5041()
+BEGIN
+DECLARE t TIME;
+DECLARE dt DATETIME;
+SET t= TIME('800:20:30');
+SET dt= t;
+SELECT dt;
+END;|
+call test5041();
+dt
+0000-01-02 08:20:30
+drop procedure test5041;
End of 5.3 tests
#
# Start of 5.5 tests
diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result
index b8958d0e88c..feb8c0a983f 100644
--- a/mysql-test/suite/funcs_1/r/innodb_func_view.result
+++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result
@@ -4302,10 +4302,12 @@ my_time, id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
+NULL -838:59:59 2
+0000-01-03 838:59:59 3
0000-00-00 13:00:00 4
0000-00-00 10:00:00 5
+Warnings:
+Warning 1292 Truncated incorrect date value: '-838:59:59'
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as date) AS `CAST(my_time AS DATE)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -4314,10 +4316,12 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL) order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
+NULL -838:59:59 2
+0000-01-03 838:59:59 3
0000-00-00 13:00:00 4
0000-00-00 10:00:00 5
+Warnings:
+Warning 1292 Truncated incorrect date value: '-838:59:59'
DROP VIEW v1;
diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result
index 9fa90797497..43703aa7ead 100644
--- a/mysql-test/suite/funcs_1/r/memory_func_view.result
+++ b/mysql-test/suite/funcs_1/r/memory_func_view.result
@@ -4303,10 +4303,12 @@ my_time, id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
+NULL -838:59:59 2
+0000-01-03 838:59:59 3
0000-00-00 13:00:00 4
0000-00-00 10:00:00 5
+Warnings:
+Warning 1292 Truncated incorrect date value: '-838:59:59'
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as date) AS `CAST(my_time AS DATE)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -4315,10 +4317,12 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL) order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
+NULL -838:59:59 2
+0000-01-03 838:59:59 3
0000-00-00 13:00:00 4
0000-00-00 10:00:00 5
+Warnings:
+Warning 1292 Truncated incorrect date value: '-838:59:59'
DROP VIEW v1;
diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result
index 9fa90797497..43703aa7ead 100644
--- a/mysql-test/suite/funcs_1/r/myisam_func_view.result
+++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result
@@ -4303,10 +4303,12 @@ my_time, id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
+NULL -838:59:59 2
+0000-01-03 838:59:59 3
0000-00-00 13:00:00 4
0000-00-00 10:00:00 5
+Warnings:
+Warning 1292 Truncated incorrect date value: '-838:59:59'
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as date) AS `CAST(my_time AS DATE)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -4315,10 +4317,12 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 35 OR select_id IS NULL) order by id;
CAST(my_time AS DATE) my_time id
NULL NULL 1
-0000-00-00 -838:59:59 2
-0000-00-00 838:59:59 3
+NULL -838:59:59 2
+0000-01-03 838:59:59 3
0000-00-00 13:00:00 4
0000-00-00 10:00:00 5
+Warnings:
+Warning 1292 Truncated incorrect date value: '-838:59:59'
DROP VIEW v1;
diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test
index 6cfd9711c3e..0b2ce16b7cc 100644
--- a/mysql-test/t/type_date.test
+++ b/mysql-test/t/type_date.test
@@ -322,6 +322,34 @@ SELECT
SET @@timestamp=DEFAULT;
--echo #
+--echo # MDEV-5041 Inserting a TIME with hour>24 into a DATETIME column produces a wrong value
+--echo #
+SELECT CAST(TIME('-800:20:30') AS DATE);
+SELECT CAST(TIME('800:20:30') AS DATE);
+SELECT CAST(TIME('33 08:20:30') AS DATE);
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES (TIME('800:20:30'));
+INSERT INTO t1 VALUES (TIME('33 08:20:30'));
+SET SQL_MODE=NO_ZERO_IN_DATE;
+INSERT INTO t1 VALUES (TIME('48:20:30'));
+SET SQL_MODE=DEFAULT;
+SELECT * FROM t1;
+DROP TABLE t1;
+DELIMITER |;
+CREATE PROCEDURE test5041()
+BEGIN
+ DECLARE t TIME;
+ DECLARE d DATE;
+ SET t= TIME('800:00:00');
+ SET d= t;
+ SELECT d;
+END;|
+DELIMITER ;|
+call test5041();
+drop procedure test5041;
+
+
+--echo #
--echo # End of 5.3 tests
--echo #
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index a1c0509666a..45aca47bd03 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -491,6 +491,35 @@ drop table t1,t2;
--echo #
SELECT CONVERT_TZ(GREATEST(TIMESTAMP('2021-00-00'),TIMESTAMP('2022-00-00')),'+00:00','+7:5');
+
+--echo #
+--echo # MDEV-5041 Inserting a TIME with hour>24 into a DATETIME column produces a wrong value
+--echo #
+SELECT CAST(TIME('-800:20:30') AS DATETIME);
+SELECT CAST(TIME('800:20:30') AS DATETIME);
+SELECT CAST(TIME('33 08:20:30') AS DATETIME);
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES (TIME('800:20:30'));
+INSERT INTO t1 VALUES (TIME('33 08:20:30'));
+SET SQL_MODE=NO_ZERO_IN_DATE;
+INSERT INTO t1 VALUES (TIME('48:20:30'));
+SET SQL_MODE=DEFAULT;
+SELECT * FROM t1;
+DROP TABLE t1;
+DELIMITER |;
+CREATE PROCEDURE test5041()
+BEGIN
+ DECLARE t TIME;
+ DECLARE dt DATETIME;
+ SET t= TIME('800:20:30');
+ SET dt= t;
+ SELECT dt;
+END;|
+DELIMITER ;|
+call test5041();
+drop procedure test5041;
+
+
--echo End of 5.3 tests
--echo #
diff --git a/sql/field.cc b/sql/field.cc
index e9cd153ad75..998110fbed4 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -5058,6 +5058,13 @@ int Field_temporal::store_time_dec(MYSQL_TIME *ltime, uint dec)
int error = 0, have_smth_to_conv= 1;
MYSQL_TIME l_time= *ltime;
ErrConvTime str(ltime);
+
+ if (l_time.time_type == MYSQL_TIMESTAMP_TIME && time_to_datetime(&l_time))
+ {
+ have_smth_to_conv= 0;
+ error= 1;
+ goto store;
+ }
/*
We don't perform range checking here since values stored in TIME
structure always fit into DATETIME range.
@@ -5066,6 +5073,7 @@ int Field_temporal::store_time_dec(MYSQL_TIME *ltime, uint dec)
(current_thd->variables.sql_mode &
(MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
MODE_INVALID_DATES)), &error);
+store:
return store_TIME_with_warning(&l_time, &str, error, have_smth_to_conv);
}
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 9758d86213d..cb0e638046d 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2443,10 +2443,10 @@ bool Item_date_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
if (get_arg0_date(ltime, fuzzy_date & ~TIME_TIME_ONLY))
return 1;
- ltime->hour= ltime->minute= ltime->second= ltime->second_part= 0;
- ltime->time_type= MYSQL_TIMESTAMP_DATE;
- return (null_value= check_date_with_warn(ltime, fuzzy_date,
- MYSQL_TIMESTAMP_DATE));
+ if (make_date_with_warn(ltime, fuzzy_date, MYSQL_TIMESTAMP_DATE))
+ return (null_value= 1);
+
+ return 0;
}
@@ -2458,28 +2458,9 @@ bool Item_datetime_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
if (decimals < TIME_SECOND_PART_DIGITS)
ltime->second_part= sec_part_truncate(ltime->second_part, decimals);
+ if (make_date_with_warn(ltime, fuzzy_date, MYSQL_TIMESTAMP_DATETIME))
+ return (null_value= 1);
- /*
- ltime is valid MYSQL_TYPE_TIME (according to fuzzy_date).
- But not every valid TIME value is a valid DATETIME value!
- */
- if (ltime->time_type == MYSQL_TIMESTAMP_TIME)
- {
- if (ltime->neg)
- {
- ErrConvTime str(ltime);
- make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
- &str, MYSQL_TIMESTAMP_DATETIME, 0);
- return (null_value= 1);
- }
-
- uint day= ltime->hour/24;
- ltime->hour %= 24;
- ltime->month= day / 31;
- ltime->day= day % 31;
- }
-
- ltime->time_type= MYSQL_TIMESTAMP_DATETIME;
return 0;
}
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index efcde017671..c696fc1344a 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -1100,6 +1100,57 @@ int my_time_compare(MYSQL_TIME *a, MYSQL_TIME *b)
}
+/**
+ Convert TIME to DATETIME.
+ @param ltime The value to convert.
+ @return false on success, true of error (negative time).
+*/
+bool time_to_datetime(MYSQL_TIME *ltime)
+{
+ DBUG_ASSERT(ltime->time_type == MYSQL_TIMESTAMP_TIME);
+ DBUG_ASSERT(ltime->year == 0);
+ DBUG_ASSERT(ltime->month == 0);
+ DBUG_ASSERT(ltime->day == 0);
+ if (ltime->neg)
+ return true;
+ uint day= ltime->hour / 24;
+ ltime->hour%= 24;
+ ltime->month= day / 31;
+ ltime->day= day % 31;
+ return false;
+}
+
+
+/**
+ Return a valid DATE or DATETIME value from an arbitrary MYSQL_TIME.
+ If ltime is TIME, it's first converted to DATETIME.
+ If ts_type is DATE, hhmmss is set to zero.
+ The date part of the result is checked against fuzzy_date.
+
+ @param ltime The value to convert.
+ @param fuzzy_date Flags to check date.
+ @param ts_type The type to convert to.
+ @return false on success, true of error (negative time).*/
+bool
+make_date_with_warn(MYSQL_TIME *ltime, ulonglong fuzzy_date,
+ timestamp_type ts_type)
+{
+ DBUG_ASSERT(ts_type == MYSQL_TIMESTAMP_DATE ||
+ ts_type == MYSQL_TIMESTAMP_DATETIME);
+ if (ltime->time_type == MYSQL_TIMESTAMP_TIME && time_to_datetime(ltime))
+ {
+ /* e.g. negative time */
+ ErrConvTime str(ltime);
+ make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ &str, ts_type, 0);
+ return true;
+ }
+ if ((ltime->time_type= ts_type) == MYSQL_TIMESTAMP_DATE)
+ ltime->hour= ltime->minute= ltime->second= ltime->second_part= 0;
+ return check_date_with_warn(ltime, fuzzy_date, ts_type);
+}
+
+
/*
Convert a TIME value to DAY-TIME interval, e.g. for extraction:
EXTRACT(DAY FROM x), EXTRACT(HOUR FROM x), etc.
diff --git a/sql/sql_time.h b/sql/sql_time.h
index f9092769749..aa81edbc105 100644
--- a/sql/sql_time.h
+++ b/sql/sql_time.h
@@ -33,6 +33,7 @@ typedef struct st_known_date_time_format KNOWN_DATE_TIME_FORMAT;
ulong convert_period_to_month(ulong period);
ulong convert_month_to_period(ulong month);
+bool time_to_datetime(MYSQL_TIME *ltime);
void time_to_daytime_interval(MYSQL_TIME *l_time);
bool get_date_from_daynr(long daynr,uint *year, uint *month, uint *day);
my_time_t TIME_to_timestamp(THD *thd, const MYSQL_TIME *t, uint *error_code);
@@ -124,6 +125,8 @@ check_date(const MYSQL_TIME *ltime, ulonglong flags, int *was_cut)
}
bool check_date_with_warn(const MYSQL_TIME *ltime, ulonglong fuzzy_date,
timestamp_type ts_type);
+bool make_date_with_warn(MYSQL_TIME *ltime,
+ ulonglong fuzzy_date, timestamp_type ts_type);
bool adjust_time_range_with_warn(MYSQL_TIME *ltime, uint dec);
#endif /* SQL_TIME_INCLUDED */
View Lorry Controller Status