[#MDEV-30325] Wrong result upon range query using index condition

It's possible that the problem is not caused by the selectivity work in 11.0, and the faulty plan can be somehow triggered on older versions, but since I couldn't so far achieve it, I will assume the relation to the changes.

set default_storage_engine=Aria;

--disable_query_log

--source include/dbt3_s001.inc

--enable_query_log

SELECT COUNT(*) FROM lineitem WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 );

The following test fails in earlier MariaDB releases:

SELECT COUNT(*) FROM lineitem force index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 );

ERROR 2013 (HY000): Lost connection to MySQL

bb-11.0 dd26d4f43
COUNT(*)
1346

Plan:

EXPLAIN EXTENDED SELECT * FROM lineitem WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 );

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_orderkey_quantity	13	NULL	1310	100.00	Using index condition; Using where

Warnings:

Note	1003	select `test`.`lineitem`.`l_orderkey` AS `l_orderkey`,`test`.`lineitem`.`l_partkey` AS `l_partkey`,`test`.`lineitem`.`l_suppkey` AS `l_suppkey`,`test`.`lineitem`.`l_linenumber` AS `l_linenumber`,`test`.`lineitem`.`l_quantity` AS `l_quantity`,`test`.`lineitem`.`l_extendedprice` AS `l_extendedprice`,`test`.`lineitem`.`l_discount` AS `l_discount`,`test`.`lineitem`.`l_tax` AS `l_tax`,`test`.`lineitem`.`l_returnflag` AS `l_returnflag`,`test`.`lineitem`.`l_linestatus` AS `l_linestatus`,`test`.`lineitem`.`l_shipDATE` AS `l_shipDATE`,`test`.`lineitem`.`l_commitDATE` AS `l_commitDATE`,`test`.`lineitem`.`l_receiptDATE` AS `l_receiptDATE`,`test`.`lineitem`.`l_shipinstruct` AS `l_shipinstruct`,`test`.`lineitem`.`l_shipmode` AS `l_shipmode`,`test`.`lineitem`.`l_comment` AS `l_comment` from `test`.`lineitem` where `test`.`lineitem`.`l_shipDATE` < '1994-01-01' and `test`.`lineitem`.`l_orderkey` < 800 or `test`.`lineitem`.`l_quantity` > 3 and `test`.`lineitem`.`l_orderkey` not in (157,1444)

The expected result is apparently 5658.

I didn't count the rows myself, but 5658 is what

the same query returns on previous versions;
the same query returns on bb-11.0 with InnoDB or MyISAM;
the query explicitly ignoring the index returns

SELECT COUNT(*) FROM lineitem IGNORE INDEX (i_l_orderkey_quantity) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 );

COUNT(*)

Also, even the 2nd part of the OR condition alone already returns over 5K rows, so 5658 seems to be plausible.

SELECT COUNT(*) FROM lineitem WHERE l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 );

COUNT(*)

[MDEV-30325] Wrong result upon range query using index condition Created: 2023-01-02 Updated: 2023-01-17 Resolved: 2023-01-05
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer, Storage Engine - Aria
Affects Version/s:	10.5.18, 10.6.11, 10.7.7, 10.8.6, 10.9.4, 10.10.2, 10.11.1
Fix Version/s:	10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

[MDEV-30325] Wrong result upon range query using index condition Created: 2023-01-02 Updated: 2023-01-17 Resolved: 2023-01-05