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

Virtual columns depending on system function work incorrectly with query cache

    XMLWordPrintable

    Details

      Description

      MariaDB [test]> SET GLOBAL query_cache_type= ON;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> SET query_cache_type= ON;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> CREATE TABLE t1 (d DATE, dv DATE AS (CURDATE()));
      Query OK, 0 rows affected (0.18 sec)
       
      MariaDB [test]> INSERT INTO t1 (d) VALUES (CURDATE());
      Query OK, 1 row affected (0.04 sec)
      

      Insert into cache

      MariaDB [test]> SELECT d, dv FROM t1;
      +------------+------------+
      | d          | dv         |
      +------------+------------+
      | 2017-10-11 | 2017-10-11 |
      +------------+------------+
      1 row in set (0.00 sec)
      

      MariaDB [test]> SET timestamp = @@timestamp + 3600*24;
      Query OK, 0 rows affected (0.00 sec)
      

      Wrong

      MariaDB [test]> SELECT d, dv FROM t1;
      +------------+------------+
      | d          | dv         |
      +------------+------------+
      | 2017-10-11 | 2017-10-11 |
      +------------+------------+
      1 row in set (0.00 sec)
      

      Re-cache

      MariaDB [test]> ANALYZE TABLE t1;
      +---------+---------+----------+----------+
      | Table   | Op      | Msg_type | Msg_text |
      +---------+---------+----------+----------+
      | test.t1 | analyze | status   | OK       |
      +---------+---------+----------+----------+
      1 row in set (0.08 sec)
       
      MariaDB [test]> SELECT d, dv FROM t1;
      +------------+------------+
      | d          | dv         |
      +------------+------------+
      | 2017-10-11 | 2017-10-12 |
      +------------+------------+
      1 row in set (0.00 sec)
      

      It gets worse when two connections have different values of the function.

      Test case

      SET GLOBAL query_cache_type= ON;
      SET query_cache_type= ON;
       
      CREATE TABLE t1 (d DATE, dv DATE AS (CURDATE()));
      INSERT INTO t1 (d) VALUES (CURDATE());
      SELECT d, dv FROM t1;
      SET timestamp = @@timestamp + 3600*24;
      SELECT d, dv FROM t1;
      ANALYZE TABLE t1;
      SELECT d, dv FROM t1;
       
      DROP TABLE t1;
      SET GLOBAL query_cache_type= DEFAULT;
      

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            elenst Elena Stepanova
            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.