Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
I create a table with a VARCHAR column and populate it with a DATETIME-alike data:
SET sql_mode=''; |
CREATE OR REPLACE TABLE t0 (d VARCHAR(64)); |
INSERT INTO t0 VALUES ('0000-00-01 10:20:30'); |
INSERT INTO t0 VALUES ('0000-01-00 10:20:30'); |
INSERT INTO t0 VALUES ('0000-01-01 10:20:30'); |
INSERT INTO t0 VALUES ('0001-00-00 10:20:30'); |
INSERT INTO t0 VALUES ('0001-00-01 10:20:30'); |
INSERT INTO t0 VALUES ('0001-01-00 10:20:30'); |
INSERT INTO t0 VALUES ('0001-01-01 10:20:30'); |
Now I create a new table t1 with three fields at populate it from t0:
SET @@global.mysql56_temporal_format=1; |
CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1)); |
INSERT INTO t1 SELECT d,d,d FROM t0; |
SELECT * FROM t1; |
It returns the following result:
+---------------------+----------+------------+
|
| d | t0 | t1 |
|
+---------------------+----------+------------+
|
| 0000-00-01 10:20:30 | 34:20:30 | 34:20:30.0 |
|
| 0000-01-00 10:20:30 | 10:20:30 | 10:20:30.0 |
|
| 0000-01-01 10:20:30 | 10:20:30 | 10:20:30.0 |
|
| 0001-00-00 10:20:30 | 10:20:30 | 10:20:30.0 |
|
| 0001-00-01 10:20:30 | 10:20:30 | 10:20:30.0 |
|
| 0001-01-00 10:20:30 | 10:20:30 | 10:20:30.0 |
|
| 0001-01-01 10:20:30 | 10:20:30 | 10:20:30.0 |
|
+---------------------+----------+------------+
|
This result looks OK:
- The first record has a zero YYYY-MM part, the value is considered as a TIME interval '1 10:20:30', so days are added to hours on conversion to TIME.
- The other records have a non-zero YYYY-MM part, to the value is considered as a fuzzy DATE, the entire YYYY-MM-DD part is thrown away on conversion to TIME.
Now I run the same with the obsolete MariaDB-5.3 temporal format:
SET @@global.mysql56_temporal_format=0; |
CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1)); |
INSERT INTO t1 SELECT d,d,d FROM t0; |
SELECT * FROM t1; |
It returns exactly the same result. So far so good. Both formats produce the same result.
Now I add 'x' to the end of the values, to force warnings, and run with the modern format:
SET @@global.mysql56_temporal_format=1; |
CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1)); |
INSERT INTO t1 SELECT CONCAT(d,'x'),CONCAT(d,'x'),CONCAT(d,'x') FROM t0; |
SELECT * FROM t1; |
It correctly produces 14 warnings (7 records * 2 TIME columns), and returns this result:
+----------------------+----------+------------+
|
| d | t0 | t1 |
|
+----------------------+----------+------------+
|
| 0000-00-01 10:20:30x | 34:20:30 | 34:20:30.0 |
|
| 0000-01-00 10:20:30x | 10:20:30 | 10:20:30.0 |
|
| 0000-01-01 10:20:30x | 10:20:30 | 10:20:30.0 |
|
| 0001-00-00 10:20:30x | 10:20:30 | 10:20:30.0 |
|
| 0001-00-01 10:20:30x | 34:20:30 | 34:20:30.0 |
|
| 0001-01-00 10:20:30x | 10:20:30 | 10:20:30.0 |
|
| 0001-01-01 10:20:30x | 10:20:30 | 10:20:30.0 |
|
+----------------------+----------+------------+
|
Notice, the TIME values in the record '0001-00-01 10:20:30x' have changed from 10:20:30 to 34:20:30.
Looks wrong! I expect exactly the same results with the first script, just with warnings about trailing 'x' truncation.
Now I run the same script, using the obsolete MariaDB-5.3 format:
SET @@global.mysql56_temporal_format=0; |
CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1)); |
INSERT INTO t1 SELECT CONCAT(d,'x'),CONCAT(d,'x'),CONCAT(d,'x') FROM t0; |
SELECT * FROM t1; |
+----------------------+----------+-------------+
|
| d | t0 | t1 |
|
+----------------------+----------+-------------+
|
| 0000-00-01 10:20:30x | 34:20:30 | 34:20:30.0 |
|
| 0000-01-00 10:20:30x | 10:20:30 | 778:20:30.0 |
|
| 0000-01-01 10:20:30x | 34:20:30 | 802:20:30.0 |
|
| 0001-00-00 10:20:30x | 10:20:30 | 10:20:30.0 |
|
| 0001-00-01 10:20:30x | 34:20:30 | 34:20:30.0 |
|
| 0001-01-00 10:20:30x | 10:20:30 | 778:20:30.0 |
|
| 0001-01-01 10:20:30x | 34:20:30 | 802:20:30.0 |
|
+----------------------+----------+-------------+
|
Notice:
- the TIME(0) column produced even more unexpected records with 34:20:30.
- the TIME(1) column produced something really unexpected.
It should be fixed to make all scripts produce results as in the very first script.
Attachments
Issue Links
- blocks
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Open
-