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

SQL_CACHE overrides the cache prohibition for RAND() and similar functions

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11, 11.4, 11.8, 12.3
    • None
    • Query Cache
    • None

    Description

      Explicit SQL_CACHE overwrites safe_to_cache_query after parsing RAND(). Item_func_rand::fix_fields() clears the flag again, but caching has already started by that point. Consequently, repeated queries can return a cached random value.

      Test:

      SET GLOBAL query_cache_type=DEMAND;
      		 
      SET query_cache_type=DEMAND;
      		 
      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES (1);
      		 
       
      		 
      SET rand_seed1=1234567890, rand_seed2=20;
      		 
       
      		 
      SHOW STATUS LIKE 'Qcache_hits';
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      SHOW STATUS LIKE 'Qcache_hits';
      		 
       
      		 
      DROP TABLE t1;

      Output:

      SET GLOBAL query_cache_type=DEMAND;
      		 
      SET query_cache_type=DEMAND;
      		 
      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES (1);
      		 
      SET rand_seed1=1234567890, rand_seed2=20;
      		 
      SHOW STATUS LIKE 'Qcache_hits';
      		 
      Variable_name	Value
      		 
      Qcache_hits	0
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      r
      		 
      0.44934285939609897
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      r
      		 
      0.44934285939609897
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      r
      		 
      0.44934285939609897
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      r
      		 
      0.44934285939609897
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      r
      		 
      0.44934285939609897
      		 
      SELECT SQL_CACHE RAND() AS r FROM t1;
      		 
      r
      		 
      0.44934285939609897
      		 
      SHOW STATUS LIKE 'Qcache_hits';
      		 
      Variable_name	Value
      		 
      Qcache_hits	5
      		 
      DROP TABLE t1;

      Note: this must be a query cache bug, not something to be fixed within Item_func_whatever.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29186 Query cache makes virtual column function RAND() non-random

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-36100 Generate vector embeddings automatically on INSERT

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress

          Bug - A problem which impairs or prevents the functions of the product. MDEV-41567 Query cache *still* makes virtual column function RAND() non-random

          • Major - Major loss of function.
          • Open

          Activity

            People

              Unassigned Unassigned
              svoj Sergey Vojtovich
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.