[MDEV-15176] Storing DATETIME-alike VARCHAR data into TIME produces wrong results - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.3.5
Component/s: Temporal Types
Labels:
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

MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave

Closed

is duplicated by

MDEV-4900 Bad value inserted into a TIME field on truncation

Closed

relates to

MDEV-5718 Inconsistent between implicit and explicit conversion from DATETIME to TIME

Open

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2018-02-02 11:24

Alexander Barkov made changes - 2018-02-02 11:24

Field	Original Value	New Value
Link		This issue blocks ~~MDEV-8894~~ [ ~~MDEV-8894~~ ]

Alexander Barkov made changes - 2018-02-02 11:24

Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.1 [ 16100 ]

Alexander Barkov made changes - 2018-02-02 11:25

Summary

Storing {{DATETIME}}-alike {{VARCHAR}} data into TIME produces wrong results

Storing DATETIME-alike VARCHAR data into TIME produces wrong results

Alexander Barkov made changes - 2018-02-02 11:25

Affects Version/s

10.0 [ 16000 ]

Alexander Barkov added a comment - 2018-02-02 11:28

Also repeatable with MariaDB-10.0, with only exception that it does not have a system variable mysql56_temporal_format yet:

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');

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 |

+----------------------+----------+-------------+

Alexander Barkov added a comment - 2018-02-02 11:28 Also repeatable with MariaDB-10.0, with only exception that it does not have a system variable mysql56_temporal_format yet: 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' ); 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 | +----------------------+----------+-------------+

Alexander Barkov added a comment - 2018-02-02 11:32

Also, repeatable with MariaDB-5.5 (after removing the OR REPLACE syntax):

SET sql_mode='';

DROP TABLE IF EXISTS t0;

CREATE 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');

DROP TABLE IF EXISTS t1;

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

+----------------------+----------+-------------+

Alexander Barkov added a comment - 2018-02-02 11:32 Also, repeatable with MariaDB-5.5 (after removing the OR REPLACE syntax): SET sql_mode= '' ; DROP TABLE IF EXISTS t0; CREATE 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' ); DROP TABLE IF EXISTS t1; CREATE 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 | +----------------------+----------+-------------+

Alexander Barkov made changes - 2018-02-02 11:32

Affects Version/s

5.5 [ 15800 ]

Alexander Barkov made changes - 2018-02-02 11:35

Description

I create a table with a {{VARCHAR}} column and populate it with a {{DATETIME}}-alike data:
{code:sql}
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');
{code}

Now I create a new table {{t1}} with three fields at populate it from {{t0}}:
{code:sql}
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;
{code}
It returns the following result:
{noformat}
+---------------------+----------+------------+
| 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 |
+---------------------+----------+------------+
{noformat}
This result looks OK:
- The first record has a zero {{YYYY-MM}} part, so 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, to 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:

{code:sql}
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;
{code}
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:
{code:sql}
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;
{code}

It correctly produces 14 warnings (7 records * 2 TIME columns), and returns this result:
{noformat}
+----------------------+----------+------------+
| 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 |
+----------------------+----------+------------+
{noformat}
Notice, the {{TIME}} values in the record {{'0001-00-01 10:20:30x'}} have changed from {{10:20:30}} to {{34:20:30.0}}.
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:
{code:sql}
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;
{code}
{noformat}
+----------------------+----------+-------------+
| 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 |
+----------------------+----------+-------------+
{noformat}
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.

I create a table with a {{VARCHAR}} column and populate it with a {{DATETIME}}-alike data:
{code:sql}
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');
{code}

Now I create a new table {{t1}} with three fields at populate it from {{t0}}:
{code:sql}
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;
{code}
It returns the following result:
{noformat}
+---------------------+----------+------------+
| 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 |
+---------------------+----------+------------+
{noformat}
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, to 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:

{code:sql}
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;
{code}
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:
{code:sql}
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;
{code}

It correctly produces 14 warnings (7 records * 2 TIME columns), and returns this result:
{noformat}
+----------------------+----------+------------+
| 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 |
+----------------------+----------+------------+
{noformat}
Notice, the {{TIME}} values in the record {{'0001-00-01 10:20:30x'}} have changed from {{10:20:30}} to {{34:20:30.0}}.
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:
{code:sql}
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;
{code}
{noformat}
+----------------------+----------+-------------+
| 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 |
+----------------------+----------+-------------+
{noformat}
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.

Alexander Barkov made changes - 2018-02-02 11:36

Description

I create a table with a {{VARCHAR}} column and populate it with a {{DATETIME}}-alike data:
{code:sql}
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');
{code}

Now I create a new table {{t1}} with three fields at populate it from {{t0}}:
{code:sql}
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;
{code}
It returns the following result:
{noformat}
+---------------------+----------+------------+
| 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 |
+---------------------+----------+------------+
{noformat}
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, to 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:

{code:sql}
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;
{code}
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:
{code:sql}
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;
{code}

It correctly produces 14 warnings (7 records * 2 TIME columns), and returns this result:
{noformat}
+----------------------+----------+------------+
| 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 |
+----------------------+----------+------------+
{noformat}
Notice, the {{TIME}} values in the record {{'0001-00-01 10:20:30x'}} have changed from {{10:20:30}} to {{34:20:30.0}}.
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:
{code:sql}
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;
{code}
{noformat}
+----------------------+----------+-------------+
| 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 |
+----------------------+----------+-------------+
{noformat}
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.

I create a table with a {{VARCHAR}} column and populate it with a {{DATETIME}}-alike data:
{code:sql}
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');
{code}

Now I create a new table {{t1}} with three fields at populate it from {{t0}}:
{code:sql}
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;
{code}
It returns the following result:
{noformat}
+---------------------+----------+------------+
| 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 |
+---------------------+----------+------------+
{noformat}
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:

{code:sql}
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;
{code}
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:
{code:sql}
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;
{code}

It correctly produces 14 warnings (7 records * 2 TIME columns), and returns this result:
{noformat}
+----------------------+----------+------------+
| 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 |
+----------------------+----------+------------+
{noformat}
Notice, the {{TIME}} values in the record {{'0001-00-01 10:20:30x'}} have changed from {{10:20:30}} to {{34:20:30.0}}.
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:
{code:sql}
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;
{code}
{noformat}
+----------------------+----------+-------------+
| 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 |
+----------------------+----------+-------------+
{noformat}
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.

Alexander Barkov made changes - 2018-02-02 11:36

Description

I create a table with a {{VARCHAR}} column and populate it with a {{DATETIME}}-alike data:
{code:sql}
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');
{code}

Now I create a new table {{t1}} with three fields at populate it from {{t0}}:
{code:sql}
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;
{code}
It returns the following result:
{noformat}
+---------------------+----------+------------+
| 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 |
+---------------------+----------+------------+
{noformat}
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:

{code:sql}
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;
{code}
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:
{code:sql}
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;
{code}

It correctly produces 14 warnings (7 records * 2 TIME columns), and returns this result:
{noformat}
+----------------------+----------+------------+
| 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 |
+----------------------+----------+------------+
{noformat}
Notice, the {{TIME}} values in the record {{'0001-00-01 10:20:30x'}} have changed from {{10:20:30}} to {{34:20:30.0}}.
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:
{code:sql}
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;
{code}
{noformat}
+----------------------+----------+-------------+
| 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 |
+----------------------+----------+-------------+
{noformat}
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.

I create a table with a {{VARCHAR}} column and populate it with a {{DATETIME}}-alike data:
{code:sql}
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');
{code}

Now I create a new table {{t1}} with three fields at populate it from {{t0}}:
{code:sql}
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;
{code}
It returns the following result:
{noformat}
+---------------------+----------+------------+
| 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 |
+---------------------+----------+------------+
{noformat}
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:

{code:sql}
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;
{code}
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:
{code:sql}
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;
{code}

It correctly produces 14 warnings (7 records * 2 TIME columns), and returns this result:
{noformat}
+----------------------+----------+------------+
| 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 |
+----------------------+----------+------------+
{noformat}
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:
{code:sql}
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;
{code}
{noformat}
+----------------------+----------+-------------+
| 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 |
+----------------------+----------+-------------+
{noformat}
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.

Alexander Barkov made changes - 2018-02-02 20:16

Link

This issue is duplicated by ~~MDEV-4900~~ [ ~~MDEV-4900~~ ]

Alexander Barkov made changes - 2018-02-04 12:45

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2018-02-04 12:45

Component/s		Temporal Types [ 11000 ]
Fix Version/s		10.3.5 [ 22905 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Alexander Barkov added a comment - 2018-02-04 12:45

Pushed to bb-10.2-ext

Alexander Barkov added a comment - 2018-02-04 12:45 Pushed to bb-10.2-ext

Alexander Barkov added a comment - 2018-02-04 14:42

Merged to 10.3.

Alexander Barkov added a comment - 2018-02-04 14:42 Merged to 10.3.

Alexander Barkov made changes - 2018-02-07 04:42

Link

This issue relates to MDEV-5718 [ MDEV-5718 ]

Sergei Golubchik made changes - 2021-12-06 21:46

Workflow

MariaDB v3 [ 85380 ]

MariaDB v4 [ 153718 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2018-02-02 11:24

Updated:: 2018-02-07 04:42

Resolved:: 2018-02-04 12:45

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration