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

Timestamp based virtual colum incongruences

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5.3
    • 10.4(EOL)
    • Time zones
    • None
    • 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

            serg Sergei Golubchik
            RoyBellingan Roy Bellingan
            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.