[MDEV-30373] Wrong result with range access Created: 2023-01-10  Updated: 2023-01-11  Resolved: 2023-01-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: N/A
Fix Version/s: 10.6.2, 10.5.19, 10.7.8, 10.8.7, 10.9.5, 10.10.3

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-26974 Improve selectivity and related costs... Closed
Relates

 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



 Comments   
Comment by Elena Stepanova [ 2023-01-10 ]

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

Comment by Elena Stepanova [ 2023-01-10 ]

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

Comment by Michael Widenius [ 2023-01-11 ]

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

Comment by Michael Widenius [ 2023-01-11 ]

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

Generated at Thu Feb 08 10:15:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.