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

Wrong result with range access

    XMLWordPrintable

Details

    Description

      --source include/have_innodb.inc
       
      CREATE TABLE t (pk int, a int, PRIMARY KEY (pk), KEY(a)) ENGINE=InnoDB;
       
      INSERT INTO t VALUES (1,3),(2,6),(3,9);
      SELECT * FROM t WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 );
       
      # Cleanup
      DROP TABLE t;
      

      bb-11.0 0ff27057415

      SELECT * FROM t WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 );
      pk	a
      1	3
      2	6
      

      EXPLAIN EXTENDED SELECT * FROM t WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t	range	PRIMARY,a	a	5	NULL	2	100.00	Using where; Using index
      Warnings:
      Note	1003	select `test`.`t`.`pk` AS `pk`,`test`.`t`.`a` AS `a` from `test`.`t` where `test`.`t`.`a` < 8 or `test`.`t`.`pk` between 1 and 5 and `test`.`t`.`a` between 7 and 10
      

      The baseline returns 3 rows, which is the expected result:

      11.0 936436ef43

      pk	a
      1	3
      2	6
      3	9
      

      EXPLAIN EXTENDED SELECT * FROM t WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t	range	PRIMARY,a	a	9	NULL	3	100.00	Using where; Using index
      Warnings:
      Note	1003	select `test`.`t`.`pk` AS `pk`,`test`.`t`.`a` AS `a` from `test`.`t` where `test`.`t`.`a` < 8 or `test`.`t`.`pk` between 1 and 5 and `test`.`t`.`a` between 7 and 10
      

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.