Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34028

Bad results near DST change with a dynamic SQL TIMESTAMP parameter

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5, 10.6, 10.11, 11.1(EOL), 11.2, 11.4, 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 11.0(EOL), 11.3(EOL), 11.5(EOL)
    • 11.5(EOL)
    • Data types, Temporal Types
    • None

    Description

      SET time_zone='Europe/Moscow';
      CREATE OR REPLACE TABLE t1 (a TIMESTAMP);
      SET timestamp=1288477526;  -- this is summer time
      INSERT INTO t1 VALUES (NOW());
      SET timestamp=1288477526+3600; -- this is winter time
      INSERT INTO t1 VALUES (NOW());
      SELECT a, UNIX_TIMESTAMP(a) FROM t1;
      DELIMITER $$
      BEGIN NOT ATOMIC
        DECLARE ts0 TIMESTAMP DEFAULT (SELECT MIN(a) FROM t1);
        DECLARE ts1 TIMESTAMP DEFAULT (SELECT MAX(a) FROM t1);
        SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a=ts0;
        SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a=ts1;
        EXECUTE IMMEDIATE 'SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a=?' USING ts0;
        EXECUTE IMMEDIATE 'SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a=?' USING ts1;
      END;
      $$
      DELIMITER ;
      

      +---------------------+-------------------+
      | a                   | UNIX_TIMESTAMP(a) |
      +---------------------+-------------------+
      | 2010-10-31 02:25:26 |        1288477526 | <-- correct, this is the minumum value
      +---------------------+-------------------+
      1 row in set (0.001 sec)
       
      +---------------------+-------------------+
      | a                   | UNIX_TIMESTAMP(a) |
      +---------------------+-------------------+
      | 2010-10-31 02:25:26 |        1288481126 | <-- correct, this is the maximum value
      +---------------------+-------------------+
      1 row in set (0.001 sec)
       
      +---------------------+-------------------+
      | a                   | UNIX_TIMESTAMP(a) |
      +---------------------+-------------------+
      | 2010-10-31 02:25:26 |        1288477526 | <-- correct, this is the mininum value
      +---------------------+-------------------+
      1 row in set (0.001 sec)
       
      +---------------------+-------------------+
      | a                   | UNIX_TIMESTAMP(a) |
      +---------------------+-------------------+
      | 2010-10-31 02:25:26 |        1288477526 | <-- wrong, this is NOT the maximum value
      +---------------------+-------------------+
      1 row in set (0.001 sec)
      

      The problem happens because the TIMESTAMP data type stores actually its DATETIME representation into Item_param when binding.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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