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

XML

Word

Printable

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 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: 10.5.25, 10.6.18, 10.11.8, 11.0.6, 11.1.5, 11.2.4, 11.4.2, 11.5.1
Component/s: Temporal Types
Labels:
None

SET time_zone='+00:00';

CREATE OR REPLACE TABLE t1 (a TIMESTAMP(6) NULL);

INSERT INTO t1 VALUES ('1970-01-01 00:00:00.000001');

SELECT unix_timestamp(a), unix_timestamp(coalesce(a)) FROM t1;

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

| unix_timestamp(a) | unix_timestamp(coalesce(a)) |

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

|          0.000001 |                        NULL |

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

The problem resides in this code in Field_timestampf::val_native():

  // Check if it's '0000-00-00 00:00:00' rather than a real timestamp

  if (ptr[0] == 0 && ptr[1] == 0 && ptr[2] == 0 && ptr[3] == 0)

    to->length(0);

    return false;

It considers all values with zero seconds as zero datetime. It's not enough. It should also check microseconds for zero.

relates to

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

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

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

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