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

Query with virtual columns are stored in query cache even when in expression of virtual columns exists CURRENT_TIMESTAMP()

    XMLWordPrintable

Details

    Description

      With the following procedure you can reproduce the bug:

      1. Query cache needs to be ON
      2. Create the Table

      CREATE TABLE `test`.`virtualtest` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
      `aDateTime` DATETIME,
      `isGreater` TINYINT(1) GENERATED ALWAYS AS (IF(CURRENT_TIMESTAMP()>`aDateTime`,TRUE,FALSE)) VIRTUAL,
      PRIMARY KEY (`id`)
      );

      3. Insert data

      INSERT INTO `virtualtest` (`aDateTime`) VALUES (TIMESTAMPADD(SECOND,30,CURRENT_TIMESTAMP()));

      4. Select the data

      SELECT * FROM `virtualtest` a;

      Expected behaviour:
      When current timestamp ist graeater than the timestamp in the table the field isGreater whould become true.
      But when the query is executed before (isGreater=false) this time the result will keep FALSE even when current timestamp is greater.

      Conclusion:
      Obviously the result set is stored in query cache. I guess the check for query cache doesn't check the expressen of virtual columns.

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              PeStoe Peter Stöcker
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.