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

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            PeStoe Peter Stöcker
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: