[MDEV-30314] Wrong result with InnoDB when index merge is forced Created: 2022-12-28  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - InnoDB
Affects Version/s: 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: not-10.5+


 Description   

When using dbt3_s001 (see mysql-test/main/dbt3_s001.inc) with InnoDB engine one gets:

MariaDB [dbt3_s001]> SELECT COUNT(*) FROM lineitem WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;        
+----------+
 
| COUNT(*) |
 
+----------+
 
|     5375 |
 
+----------+
 
1 row in set (0.054 sec)
 
 
 
MariaDB [dbt3_s001]> SELECT COUNT(*) FROM lineitem  FORCE INDEX (i_l_receiptdate, i_l_orderkey) WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;       +----------+
 
| COUNT(*) |
 
+----------+
 
|     2994 |
 
+----------+
 
1 row in set (0.072 sec)

The second result is incorrect.
The following execution plans are used:

MariaDB [dbt3_s001]> EXPLAIN SELECT COUNT(*) FROM lineitem WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;                                            
+------+-------------+----------+------+------------------------------------------------------------------------+------+---------+------+------+-------------+
 
| id   | select_type | table    | type | possible_keys                                                          | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+----------+------+------------------------------------------------------------------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | lineitem | ALL  | PRIMARY,i_l_suppkey,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity | NULL | NULL    | NULL | 5871 | Using where |
 
+------+-------------+----------+------+------------------------------------------------------------------------+------+---------+------+------+-------------+
 
1 row in set (0.006 sec)
 
 
 
MariaDB [dbt3_s001]> EXPLAIN SELECT COUNT(*) FROM lineitem  FORCE INDEX (i_l_receiptdate, i_l_orderkey) WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;
 
+------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+
 
| id   | select_type | table    | type        | possible_keys                | key                          | key_len | ref  | rows | Extra                                                       |
 
+------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+
 
|    1 | SIMPLE      | lineitem | index_merge | i_l_receiptdate,i_l_orderkey | i_l_receiptdate,i_l_orderkey | 4,4     | NULL | 3336 | Using sort_union(i_l_receiptdate,i_l_orderkey); Using where |
 
+------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+

It does not matter what settings are used for 'use_stat_tables' and 'optimizer_use_condition_selectivity'.
The same database with MyISAM returns correct results with the same plans as for InnoDB.
Later versions most probably are affected as well



 Comments   
Comment by Igor Babaev [ 2022-12-28 ]

Checked with the last 5.5: when using InnoDB storage engine for dbt3_s001 correct results are returned and with the same execution plans as for 10.3.

Comment by Elena Stepanova [ 2022-12-28 ]

Bisect points at this commit in 10.2.35:

commit 291be494744abe90f4bdf6b5a35c4c26ee8ddda5
 
Date:   Thu Sep 24 22:02:00 2020 -0700
 
 
 
    MDEV-23811: With large number of indexes optimizer chooses an inefficient plan

Comment by Igor Babaev [ 2022-12-29 ]

With
set optimizer_switch='extended_keys=off';
we have correct result for InnoDB with index merge as well:

MariaDB [dbt3_s001]> SELECT COUNT(*) FROM lineitem  FORCE INDEX (i_l_receiptdate, i_l_orderkey) WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;
 
+----------+
 
| COUNT(*) |
 
+----------+
 
|     5375 |
 
+----------+
 
 
 
MariaDB [dbt3_s001]> EXPLAIN SELECT COUNT(*) FROM lineitem  FORCE INDEX (i_l_receiptdate, i_l_orderkey) WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;
 
+------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+
 
| id   | select_type | table    | type        | possible_keys                | key                          | key_len | ref  | rows | Extra                                                       |
 
+------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+
 
|    1 | SIMPLE      | lineitem | index_merge | i_l_receiptdate,i_l_orderkey | i_l_receiptdate,i_l_orderkey | 4,4     | NULL | 5873 | Using sort_union(i_l_receiptdate,i_l_orderkey); Using where |
 
+------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+

This explains why for this query we have different results in InnoDB and MyISAM: MyISAM does not use extended keys while InnoDB may use them. Note that the primary key for lineitem has 2 components and extended keys for i_l_receiptdate in InnoDB potentially can have 3 components: (l_receipdate,l_orderkey,I_lineitem).
Yet no valid extended keys can be used for this query.

Comment by Alice Sherepa [ 2023-01-18 ]

On 10.5+ (179c2833721292a9182) there are correct results in both cases

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