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

Timestamp based virtual colum incongruences

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5.3
    • Fix Version/s: 10.2, 10.3, 10.4
    • Component/s: Time zones
    • Labels:
      None
    • Environment:
      Suse 15.1 self compiled from 8c0b988073c6537f53bcda0afbba0f4ce20fae34

      Description

      In short after changing timezone, the select * data differ from the indexed one

      SET time_zone = 'UTC';
       
      CREATE TABLE `l2` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        `dateUTC` date GENERATED ALWAYS AS (cast(`timestamp` as date)) VIRTUAL,
        PRIMARY KEY (`id`),
        KEY `dateUTC` (`dateUTC`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
       
      INSERT INTO `l2` (`id`, `timestamp`) VALUES
      (1,	'2020-04-12 23:10:04'),
      (2,	'2020-04-12 20:10:04');
       
      **************************************************
      SET time_zone = 'Europe/Helsinki';
       
      select * from l2 where timestamp > '2020-04-13';
      +----+---------------------+------------+
      | id | timestamp           | dateUTC    |
      +----+---------------------+------------+
      |  1 | 2020-04-13 02:10:04 | 2020-04-13 |
      +----+---------------------+------------+
      1 row in set (0.001 sec)
       
      select * from l2 where dateUTC = '2020-04-13';
      Empty set (0.002 sec)
       
      select * from l2 where dateUTC = '2020-04-12';
      +----+---------------------+------------+
      | id | timestamp           | dateUTC    |
      +----+---------------------+------------+
      |  1 | 2020-04-13 02:10:04 | 2020-04-13 |
      |  2 | 2020-04-12 23:10:04 | 2020-04-12 |
      +----+---------------------+------------+
      

      This is related to MDEV-21249
      Which fixed only the representational, but the data in the index is still "wrong"

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            RoyBellingan Roy Bellingan
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration