[MDEV-34069] Zero datetime reinterprets as '1970-01-01 00:00:00' on field_datetime=field_timestamp - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4, 11.5(EOL)
Fix Version/s: 11.3.2, 10.5.25, 10.11.8, 10.6.18, 11.0.6, 11.1.5, 11.2.4, 11.4.2, 11.5.1
Component/s: Temporal Types
Labels:
None

Description

Zero datetime reinterprets as '1970-01-01 00:00:00' on field_datetime=field_timestamp

SET sql_mode='';

SET time_zone='+00:00';

CREATE OR REPLACE TABLE t1 (a TIMESTAMP);

INSERT INTO t1 VALUES ('0000-00-00 00:00:00');

SELECT * FROM t1;

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

| a                   |

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

| 0000-00-00 00:00:00 |

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

CREATE OR REPLACE TABLE t2 (a datetime);

INSERT INTO t2 SELECT a FROM t1;

SELECT * FROM t2;

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

| a                   |

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

| 1970-01-01 00:00:00 |

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

Looks wrong. The zero datetime was erroneously re-interpreted as '1970-01-01 00:00:00'.

Note, if I wrap the column t2.a into COALESCE(), it works OK:

INSERT INTO t2 SELECT COALESCE(a) FROM t1;

SELECT * FROM t2;

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

| a                   |

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

| 1970-01-01 00:00:00 |

| 0000-00-00 00:00:00 |

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

The problem resides in this code:

int Field_timestamp::save_in_field(Field *to)

  ulong sec_part;

  my_time_t ts= get_timestamp(&sec_part);

  return to->store_timestamp_dec(Timeval(ts, sec_part), decimals());

It should not call store_timestamp_dec() for zero datetime, as this method knows nothing about zero datetime. It can store only real timestamp values.

A proposed fix:

int Field_timestamp::save_in_field(Field *to)

  ulong sec_part;

  my_time_t ts= get_timestamp(&sec_part);

  if (!ts && ! sec_part)

    return to->store_time_dec(Datetime::zero().get_mysql_time(), decimals());

  return to->store_timestamp_dec(Timeval(ts, sec_part), decimals());

Attachments

Issue Links

relates to

MDEV-34037 DATETIME <-> TIMESTAMP conversion in a virtual column corrups the table on @@time_zone change

In Review

MDEV-34061 unix_timestamp(coalesce(timestamp_column)) returns NULL on '1970-01-01 00:00:00.000001'

Closed

MDEV-34088 The TIMESTAMP value of '1970-01-01 00:00:00' can be indirectly inserted in strict mode

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2024-05-03 11:13

Alexander Barkov made changes - 2024-05-03 11:13

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-34061~~ [ ~~MDEV-34061~~ ]

Alexander Barkov made changes - 2024-05-03 11:13

Link

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

Alexander Barkov made changes - 2024-05-04 18:27

Description

Zero datetime reinterprets as '1970-01-01 00:00:00' on field_datetime=field_timestamp

{code:sql}
SET sql_mode=DEFAULT;
SET time_zone='+00:00';
CREATE OR REPLACE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES (0);
SELECT * FROM t1;
{code}
{noformat}
+---------------------+
| a |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
{noformat}

{code:sql}
CREATE OR REPLACE TABLE t2 (a datetime);
INSERT INTO t2 SELECT a FROM t1;
SELECT * FROM t2;
{code}
{noformat}
+---------------------+
| a |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
{noformat}

Looks wrong. The zero datetime was erroneously re-interpreted as '1970-01-01 00:00:00'.

Note, if I wrap the column t2.a into COALESCE(), it works OK:

{code:sql}
INSERT INTO t2 SELECT COALESCE(a) FROM t1;
SELECT * FROM t2;
{code}
{noformat}
+---------------------+
| a |
+---------------------+
| 1970-01-01 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
{noformat}

The problem resides in this code:
{code:cpp}
int Field_timestamp::save_in_field(Field *to)
{
  ulong sec_part;
  my_time_t ts= get_timestamp(&sec_part);
  return to->store_timestamp_dec(Timeval(ts, sec_part), decimals());
}
{code}

It should not call store_timestamp_dec() for zero datetime, as this method knows nothing about zero datetime. It can store only real timestamp values.

A proposed fix:
{code:cpp}
int Field_timestamp::save_in_field(Field *to)
{
  ulong sec_part;
  my_time_t ts= get_timestamp(&sec_part);
  if (!ts && ! sec_part)
    return to->store_time_dec(Datetime::zero().get_mysql_time(), decimals());
  return to->store_timestamp_dec(Timeval(ts, sec_part), decimals());
}
{code}

Zero datetime reinterprets as '1970-01-01 00:00:00' on field_datetime=field_timestamp

{code:sql}
SET sql_mode='';
SET time_zone='+00:00';
CREATE OR REPLACE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES (0);
SELECT * FROM t1;
{code}
{noformat}
+---------------------+
| a |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
{noformat}

{code:sql}
CREATE OR REPLACE TABLE t2 (a datetime);
INSERT INTO t2 SELECT a FROM t1;
SELECT * FROM t2;
{code}
{noformat}
+---------------------+
| a |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
{noformat}

Looks wrong. The zero datetime was erroneously re-interpreted as '1970-01-01 00:00:00'.

Note, if I wrap the column t2.a into COALESCE(), it works OK:

{code:sql}
INSERT INTO t2 SELECT COALESCE(a) FROM t1;
SELECT * FROM t2;
{code}
{noformat}
+---------------------+
| a |
+---------------------+
| 1970-01-01 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
{noformat}

The problem resides in this code:
{code:cpp}
int Field_timestamp::save_in_field(Field *to)
{
  ulong sec_part;
  my_time_t ts= get_timestamp(&sec_part);
  return to->store_timestamp_dec(Timeval(ts, sec_part), decimals());
}
{code}

It should not call store_timestamp_dec() for zero datetime, as this method knows nothing about zero datetime. It can store only real timestamp values.

A proposed fix:
{code:cpp}
int Field_timestamp::save_in_field(Field *to)
{
  ulong sec_part;
  my_time_t ts= get_timestamp(&sec_part);
  if (!ts && ! sec_part)
    return to->store_time_dec(Datetime::zero().get_mysql_time(), decimals());
  return to->store_timestamp_dec(Timeval(ts, sec_part), decimals());
}
{code}

Alexander Barkov made changes - 2024-05-04 18:33

Description

Zero datetime reinterprets as '1970-01-01 00:00:00' on field_datetime=field_timestamp

{code:sql}
SET sql_mode='';
SET time_zone='+00:00';
CREATE OR REPLACE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
SELECT * FROM t1;
{code}
{noformat}
+---------------------+
| a |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
{noformat}

{code:sql}
CREATE OR REPLACE TABLE t2 (a datetime);
INSERT INTO t2 SELECT a FROM t1;
SELECT * FROM t2;
{code}
{noformat}
+---------------------+
| a |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
{noformat}

Looks wrong. The zero datetime was erroneously re-interpreted as '1970-01-01 00:00:00'.

Note, if I wrap the column t2.a into COALESCE(), it works OK:

{code:sql}
INSERT INTO t2 SELECT COALESCE(a) FROM t1;
SELECT * FROM t2;
{code}
{noformat}
+---------------------+
| a |
+---------------------+
| 1970-01-01 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
{noformat}

The problem resides in this code:
{code:cpp}
int Field_timestamp::save_in_field(Field *to)
{
  ulong sec_part;
  my_time_t ts= get_timestamp(&sec_part);
  return to->store_timestamp_dec(Timeval(ts, sec_part), decimals());
}
{code}

It should not call store_timestamp_dec() for zero datetime, as this method knows nothing about zero datetime. It can store only real timestamp values.

A proposed fix:
{code:cpp}
int Field_timestamp::save_in_field(Field *to)
{
  ulong sec_part;
  my_time_t ts= get_timestamp(&sec_part);
  if (!ts && ! sec_part)
    return to->store_time_dec(Datetime::zero().get_mysql_time(), decimals());
  return to->store_timestamp_dec(Timeval(ts, sec_part), decimals());
}
{code}

Alexander Barkov made changes - 2024-05-04 19:31

issue.field.resolutiondate

2024-05-04 19:31:42.0

2024-05-04 19:31:42.397

Alexander Barkov made changes - 2024-05-04 19:31

Fix Version/s		10.5.25 [ 29626 ]
Fix Version/s		10.6.18 [ 29627 ]
Fix Version/s		10.11.8 [ 29630 ]
Fix Version/s		11.1.5 [ 29629 ]
Fix Version/s		11.2.4 [ 29631 ]
Fix Version/s		11.4.2 [ 29633 ]
Fix Version/s		11.5.1 [ 29634 ]
Fix Version/s		11.3.2 [ 29522 ]
Fix Version/s		11.0.1 [ 28548 ]
Fix Version/s	10.5 [ 23123 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Alexander Barkov made changes - 2024-05-05 10:36

Link

This issue relates to ~~MDEV-34088~~ [ ~~MDEV-34088~~ ]

Julien Fritsch made changes - 2024-05-05 19:53

Fix Version/s		11.0.6 [ 29628 ]
Fix Version/s	11.0.1 [ 28548 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2024-05-03 11:13

Updated:: 2024-05-05 19:53

Resolved:: 2024-05-04 19:31

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