Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.18, 10.6.11, 10.7.7, 10.8.6, 10.9.4, 10.10.2, 10.11.1
-
None
Description
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(*) |
5658
|
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(*) |
5643
|