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

Wrong result upon query with GROUP BY, HAVING, index involving virtual column and rowid_filter=on

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
    • 10.5, 10.6
    • Optimizer

    Description

      --source include/have_innodb.inc
       
      # InnoDB stats and ANALYZE are optional, the result is the same either way
      SET @stats= @@innodb_stats_persistent;
      SET GLOBAL innodb_stats_persistent= ON;
       
      CREATE OR REPLACE TABLE t (
        a VARCHAR(8),
        b VARCHAR(8) AS (a),
        c CHAR(8),
        id INT PRIMARY KEY,
        KEY ind(a,b),
        KEY (c)
      ) ENGINE=InnoDB;
       
      INSERT INTO t (id,a,c) VALUES (1,'bow','auto'),(2,'cow','no'),(3,'wow','yes');
       
      ANALYZE TABLE t;
       
      SET optimizer_switch= 'rowid_filter=on';
      SELECT a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
      c > a GROUP BY a HAVING a >= 'low';
       
      SET optimizer_switch= 'rowid_filter=off';
      SELECT a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
      c > a GROUP BY a HAVING a >= 'low';
       
      # Cleanup
      DROP TABLE t;
      SET GLOBAL innodb_stats_persistent= @stats;
      

      With rowid_filter=on the result is empty:

      10.5 2776635c

      SET optimizer_switch= 'rowid_filter=on';
      SELECT a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
      a	COUNT(*)
      

      With rowid_filter=off the query returns a row:

      SET optimizer_switch= 'rowid_filter=off';
      SELECT a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
      a	COUNT(*)
      wow	1
      

      The latter is the expected result.

      Plans for the queries, accordingly:

      rowid_filter=on

      EXPLAIN EXTENDED SELECT SQL_NO_CACHE a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t	range|filter	ind,c	ind|c	11|9	NULL	1 (67%)	66.67	Using where; Using rowid filter
      Warnings:
      Note	1003	select sql_no_cache `test`.`t`.`a` AS `a`,count(0) AS `COUNT(*)` from `test`.`t` where `test`.`t`.`c` > 'good' and `test`.`t`.`c` > `test`.`t`.`a` and `test`.`t`.`a` >= 'low' group by `test`.`t`.`a` having 1
      

      rowid_filter=off

      EXPLAIN EXTENDED SELECT SQL_NO_CACHE a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t	range	ind,c	ind	11	NULL	1	66.67	Using where
      Warnings:
      Note	1003	select sql_no_cache `test`.`t`.`a` AS `a`,count(0) AS `COUNT(*)` from `test`.`t` where `test`.`t`.`c` > 'good' and `test`.`t`.`c` > `test`.`t`.`a` and `test`.`t`.`a` >= 'low' group by `test`.`t`.`a` having 1
      

      Reproducible on 10.5+ with InnoDB.
      Not reproducible on 10.4.
      Not reproducible with MyISAM.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            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.