Bogus Last_query_cost reported when sort_intersect is used
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Igor Babaev |
Bug 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-
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,
last_query_cost is:
MariaDB [test]> show status like '%last_
+------
| Variable_name | Value |
+------
| Last_query_cost | 368934881474191
+------
1 row in set (0.01 sec)
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → Medium |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
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; ------- -----+- ------- -+----- ---+--- ------- ------- ------- -+----- ----+-- ------- +------ ------- ------+ ------+ ------- ------- ------- ------- ------- ------- ------- ------- + ------- -----+- ------- -+----- ---+--- ------- ------- ------- -+----- ----+-- ------- +------ ------- ------+ ------+ ------- ------- ------- ------- ------- ------- ------- ------- + Country, Name | Name | 35 | NULL | 161 | Using index condition; Using where; Rowid-ordered scan | ------- -----+- ------- -+----- ---+--- ------- ------- ------- -+----- ----+-- ------- +------ ------- ------+ ------+ ------- ------- ------- ------- ------- ------- ------- ------- +
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | SIMPLE | City | range | Population,
| 1 | SIMPLE | Country | eq_ref | PRIMARY | PRIMARY | 3 | test.City.Country | 1 | |
+----+-
2