Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
If the sort_intersection optimization is in effect, Last_query_cost takes a huge value, such as 3689348814741910528 . Timour reported that Last_query_cost is not an obsolete variable.
To reproduce:
1. Download the dbt3 scale 0.001 mysqldump from
and load with --default-storage-engine=InnoDB
2. Run query:
SELECT COUNT( l_receiptdate ) FROM lineitem WHERE ( l_receiptDATE IN ( '1992-03-19' , '1993-04-01' ) OR l_receiptDATE IN ( '1998-06-27' , '1992-09-13' , '1993-10-13' ) AND l_shipdate BETWEEN '1994-08-01' AND '1994-08-29' AND l_linenumber BETWEEN 9 AND 5 + 8 ) AND ( ( l_receiptDATE >= '1994-02-13' ) AND ( l_orderkey = 580 OR l_quantity BETWEEN 46 AND 6 + 4 OR l_partkey = 227 OR l_shipdate IN ( '1994-05-26' , '1998-09-03' , '1993-11-15' , '1994-03-11' , '1992-12-10' , '1998-04-14' ) ) );
explain is:
| 1 | SIMPLE | lineitem | index_merge | PRIMARY,i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity | i_l_receiptdate,i_l_shipdate | 4,4 | NULL | 1 | Using sort_intersect(i_l_receiptdate,i_l_shipdate); Using where |
last_query_cost is:
MariaDB [test]> show status like '%last_query_cost%';
-------------------------------------------+
| Variable_name | Value |
-------------------------------------------+
| Last_query_cost | 3689348814741910528.000000 |
-------------------------------------------+
1 row in set (0.01 sec)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Bogus Last_query_cost reported when sort_intersect is used
[This a continuation of the previous comment presenting a bad optimizer choice with
optimizer_switch='index_merge_sort_intersection=on';]
MariaDB [test]> SET SESSION optimizer_switch='index_merge_sort_intersection=on';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> EXPLAIN SELECT * FROM City, Country WHERE City.Name LIKE 'C%' AND City.Population > 1000000 and Country.Code=City.Country;
-----------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------+
| 1 | SIMPLE | Country | ALL | PRIMARY | NULL | NULL | NULL | 239 | |
| 1 | SIMPLE | City | ref | Population,Country,Name | Country | 3 | test.Country.Code | 18 | Using where |
-----------------------------------------------------------------------------------------------+
Launchpad bug id: 754521
Re: Bogus Last_query_cost reported when sort_intersect is used
Actually this bug can result in a bad choice of the query execution plan:
MariaDB [test]> EXPLAIN SELECT * FROM City, Country WHERE City.Name LIKE 'C%' AND City.Population > 1000000 and Country.Code=City.Country;
---
-----------------------------------------------------------------------------------------------------------------------------------------+---
-----------------------------------------------------------------------------------------------------------------------------------------+---
-----------------------------------------------------------------------------------------------------------------------------------------+2