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

Dynamic SQL: TIMESTAMP parameter value is not preserved on timezone change

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2, 10.3
    • Fix Version/s: 10.4
    • Component/s: Temporal Types
    • Labels:
      None

      Description

      I create this stored function which changes the session time zone:

      SET sql_mode=DEFAULT;
      DELIMITER $$
      CREATE OR REPLACE FUNCTION f1(tz TEXT) RETURNS TEXT
      BEGIN
        SET time_zone=tz;
        RETURN @@time_zone;
      END;
      $$
      DELIMITER ;
      SELECT f1('+00:00');
      

      Now I execute this anonymous code block
      (notice, it changes the session time_zone in the middle of the CREATE..SELECT query by using f1 in the select list):

      SET sql_mode=DEFAULT;
      DELIMITER $$
      BEGIN NOT ATOMIC
        DECLARE va TIMESTAMP;
        SET time_zone='+00:00';
        SET va= FROM_UNIXTIME(1288477526);
        SELECT @@time_zone, UNIX_TIMESTAMP(va), va, CONVERT_TZ(va,'+00:00','Europe/Moscow');
        EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT ? AS a11,f1(''Europe/Moscow'') AS tz12, ? AS a12' USING va, va;
        SELECT @@time_zone, UNIX_TIMESTAMP(a11), a11, UNIX_TIMESTAMP(a12), a12 FROM t1;
      END;
      $$
      DELIMITER ;
      

      It returns these two result sets:

      +-------------+--------------------+---------------------+-----------------------------------------+
      | @@time_zone | UNIX_TIMESTAMP(va) | va                  | CONVERT_TZ(va,'+00:00','Europe/Moscow') |
      +-------------+--------------------+---------------------+-----------------------------------------+
      | +00:00      |         1288477526 | 2010-10-30 22:25:26 | 2010-10-31 02:25:26                     |
      +-------------+--------------------+---------------------+-----------------------------------------+
      1 row in set (0.00 sec)
      

      +---------------+---------------------+---------------------+---------------------+---------------------+
      | @@time_zone   | UNIX_TIMESTAMP(a11) | a11                 | UNIX_TIMESTAMP(a12) | a12                 |
      +---------------+---------------------+---------------------+---------------------+---------------------+
      | Europe/Moscow |          1288477526 | 2010-10-31 02:25:26 |          1288463126 | 2010-10-30 22:25:26 |
      +---------------+---------------------+---------------------+---------------------+---------------------+
      1 row in set (0.04 sec)
      

      Notice, in the second result set the values in UNIX_TIMESTAMP(a11) and a11 are correct:

      • The timestamp is equal to the original timestamp value in va
      • The datetime representation is equal to CONVERT_TZ() in the first result set

      The values in UNIX_TIMESTAMP(a12) and a12 are wrong:

      • The timestamp value 1288463126 is not equal to the original value 1288477526. This is wrong. It should preserve the original value. This is the TIMESTAMP data type!
      • The value in a12 should be 2010-10-31 02:25:26, similar to a11. Instead, it preserved the DATETIME representation of va at bind time.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated: