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

            Here is a testcase with dbt3 database and MyISAM. The query however is remarkably similar

            create database dbt3;
            use dbt3;
            --disable_query_log
            --source include/dbt3_s001.inc
            --enable_query_log
             
            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
            EXPLAIN EXTENDED
            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
             
            # Cleanup
            drop database dbt3;
            

            bb-11.0

            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
            COUNT(*)
            168
            EXPLAIN EXTENDED
            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
            id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
            1	SIMPLE	lineitem	range	PRIMARY,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	NULL	177	100.00	Using where; Using index
            Warnings:
            Note	1003	select count(0) AS `COUNT(*)` from `dbt3`.`lineitem` where `dbt3`.`lineitem`.`l_orderkey` between 111 and 262 or `dbt3`.`lineitem`.`l_orderkey` between 152 and 672 and `dbt3`.`lineitem`.`l_linenumber` between 4 and 9
            

            baseline

            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
            COUNT(*)
            293
            EXPLAIN EXTENDED
            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
            id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
            1	SIMPLE	lineitem	range	PRIMARY,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	8	NULL	570	92.98	Using where; Using index
            Warnings:
            Note	1003	select count(0) AS `COUNT(*)` from `dbt3`.`lineitem` where `dbt3`.`lineitem`.`l_orderkey` between 111 and 262 or `dbt3`.`lineitem`.`l_orderkey` between 152 and 672 and `dbt3`.`lineitem`.`l_linenumber` between 4 and 9
            

            elenst Elena Stepanova added a comment - Here is a testcase with dbt3 database and MyISAM. The query however is remarkably similar create database dbt3; use dbt3; --disable_query_log --source include/dbt3_s001.inc --enable_query_log   SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); EXPLAIN EXTENDED SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 );   # Cleanup drop database dbt3; bb-11.0 SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); COUNT (*) 168 EXPLAIN EXTENDED SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE lineitem range PRIMARY ,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 NULL 177 100.00 Using where ; Using index Warnings: Note 1003 select count (0) AS ` COUNT (*)` from `dbt3`.`lineitem` where `dbt3`.`lineitem`.`l_orderkey` between 111 and 262 or `dbt3`.`lineitem`.`l_orderkey` between 152 and 672 and `dbt3`.`lineitem`.`l_linenumber` between 4 and 9 baseline SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); COUNT (*) 293 EXPLAIN EXTENDED SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE lineitem range PRIMARY ,i_l_orderkey,i_l_orderkey_quantity PRIMARY 8 NULL 570 92.98 Using where ; Using index Warnings: Note 1003 select count (0) AS ` COUNT (*)` from `dbt3`.`lineitem` where `dbt3`.`lineitem`.`l_orderkey` between 111 and 262 or `dbt3`.`lineitem`.`l_orderkey` between 152 and 672 and `dbt3`.`lineitem`.`l_linenumber` between 4 and 9

            And here is apparently the same problem, but it doesn't show explicitly range access anymore (so that it's even more difficult to recognize in tests)

            create database dbt3;
            use dbt3;
            --disable_query_log
            --source include/dbt3_s001.inc
            --enable_query_log
             
            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
            EXPLAIN EXTENDED
            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
             
            # Cleanup
            drop database dbt3;
            

            bb-11.0

            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
            COUNT(*)
            229
            EXPLAIN EXTENDED
            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
            id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
            1	SIMPLE	lineitem	index_merge	PRIMARY,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate	PRIMARY,i_l_commitdate	4,4	NULL	256	100.00	Using sort_union(PRIMARY,i_l_commitdate); Using where
            Warnings:
            Note	1003	select count(0) AS `COUNT(*)` from `dbt3`.`lineitem` where `dbt3`.`lineitem`.`l_orderkey` between 111 and 262 or `dbt3`.`lineitem`.`l_commitDATE` between '1994-07-01' and '1994-07-29' or `dbt3`.`lineitem`.`l_orderkey` between 152 and 672 and `dbt3`.`lineitem`.`l_linenumber` between 4 and 9
            

            baseline

            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
            COUNT(*)
            354
            EXPLAIN EXTENDED
            SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
            id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
            1	SIMPLE	lineitem	index_merge	PRIMARY,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate	i_l_orderkey,i_l_commitdate	4,4	NULL	619	100.00	Using sort_union(i_l_orderkey,i_l_commitdate); Using where
            Warnings:
            Note	1003	select count(0) AS `COUNT(*)` from `dbt3`.`lineitem` where `dbt3`.`lineitem`.`l_orderkey` between 111 and 262 or `dbt3`.`lineitem`.`l_commitDATE` between '1994-07-01' and '1994-07-29' or `dbt3`.`lineitem`.`l_orderkey` between 152 and 672 and `dbt3`.`lineitem`.`l_linenumber` between 4 and 9
            

            elenst Elena Stepanova added a comment - And here is apparently the same problem, but it doesn't show explicitly range access anymore (so that it's even more difficult to recognize in tests) create database dbt3; use dbt3; --disable_query_log --source include/dbt3_s001.inc --enable_query_log   SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); EXPLAIN EXTENDED SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 );   # Cleanup drop database dbt3; bb-11.0 SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); COUNT (*) 229 EXPLAIN EXTENDED SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE lineitem index_merge PRIMARY ,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate PRIMARY ,i_l_commitdate 4,4 NULL 256 100.00 Using sort_union( PRIMARY ,i_l_commitdate); Using where Warnings: Note 1003 select count (0) AS ` COUNT (*)` from `dbt3`.`lineitem` where `dbt3`.`lineitem`.`l_orderkey` between 111 and 262 or `dbt3`.`lineitem`.`l_commitDATE` between '1994-07-01' and '1994-07-29' or `dbt3`.`lineitem`.`l_orderkey` between 152 and 672 and `dbt3`.`lineitem`.`l_linenumber` between 4 and 9 baseline SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); COUNT (*) 354 EXPLAIN EXTENDED SELECT COUNT (*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_commitDATE BETWEEN '1994-07-01' AND '1994-07-29' OR l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE lineitem index_merge PRIMARY ,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate i_l_orderkey,i_l_commitdate 4,4 NULL 619 100.00 Using sort_union(i_l_orderkey,i_l_commitdate); Using where Warnings: Note 1003 select count (0) AS ` COUNT (*)` from `dbt3`.`lineitem` where `dbt3`.`lineitem`.`l_orderkey` between 111 and 262 or `dbt3`.`lineitem`.`l_commitDATE` between '1994-07-01' and '1994-07-29' or `dbt3`.`lineitem`.`l_orderkey` between 152 and 672 and `dbt3`.`lineitem`.`l_linenumber` between 4 and 9

            This was a bug in the fix for MDEV-30325. Will push a fix shortly

            monty Michael Widenius added a comment - This was a bug in the fix for MDEV-30325 . Will push a fix shortly

            This issue was caused by the bug fix for MDEV-30325 Wrong result upon range query using index condition

            The bug could happen in the case of several overlapping key ranges with OR

            Pushed to 10.5 and bb-11.0

            monty Michael Widenius added a comment - This issue was caused by the bug fix for MDEV-30325 Wrong result upon range query using index condition The bug could happen in the case of several overlapping key ranges with OR Pushed to 10.5 and bb-11.0

            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.