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

        1. 2.sql
          63 kB
          Elena Stepanova

        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.