|
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.
|