Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL)
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