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

Wrong result with rowid_filter=on and indexed virtual column

    XMLWordPrintable

Details

    Description

      --source include/have_innodb.inc
       
      CREATE TABLE t (
        a INT,
        b INT,
        vb INT GENERATED ALWAYS AS (b) VIRTUAL,
        pk INT AUTO_INCREMENT,
        PRIMARY KEY (pk),
        KEY (a),
        KEY (vb)
      ) ENGINE=InnoDB;
       
      INSERT INTO t (a, b) VALUES
      (9,1),(7,1),(1,1),(3,1),(5,2),(1,2),(5,2),(9,1),(8,2),(2,1),
      (2,9),(2,0),(8,2),(1,8),(8,3),(2,5),(9,3);
       
      INSERT INTO t (a,b) SELECT a,b FROM t;
       
      ANALYZE TABLE t PERSISTENT FOR ALL; # Optional, fails either way
       
      SET optimizer_switch='rowid_filter=on';
      SELECT * FROM t WHERE a in (1,2) AND vb = 1;
       
      SET optimizer_switch='rowid_filter=off';
      SELECT * FROM t WHERE a in (1,2) AND vb = 1;
       
      DROP TABLE t;
      

      11.0 5fb2c031

      SET optimizer_switch='rowid_filter=on';
      SELECT * FROM t WHERE a in (1,2) AND vb = 1;
      a	b	vb	pk
      SET optimizer_switch='rowid_filter=off';
      SELECT * FROM t WHERE a in (1,2) AND vb = 1;
      a	b	vb	pk
      1	1	1	3
      2	1	1	10
      1	1	1	20
      2	1	1	27
      

      The result with rowid_filter=off is the correct one.

      Plan for the wrong result:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t	ref|filter	a,vb	vb|a	5|5	const	12 (41%)	41.18	Using where; Using rowid filter
      Warnings:
      Note	1003	select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`vb` AS `vb`,`test`.`t`.`pk` AS `pk` from `test`.`t` where `test`.`t`.`vb` = 1 and `test`.`t`.`a` in (1,2)
      SET optimizer_switch='rowid_filter=off';
      

      The failure used to be happening on earlier versions, too, but after the commit below in 10.4.27 the provided test case no longer triggers a plan with rowid filter and thus the problem does not occur.

      commit 58cd0bd59ef011be54f162237f2ff017c3148e7b
      Author: Igor Babaev
      Date:   Mon Oct 17 16:44:10 2022 -0700
       
          MDEV-28846 Poor performance when rowid filter contains no elements
      

      However, on 11.x it still happens.

      Attachments

        Activity

          People

            monty Michael Widenius
            elenst Elena Stepanova
            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.