Bogus Last_query_cost reported when sort_intersect is used

[Philip Stoev]

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

http://bazaar.launchpad.net/~randgen/randgen/rqg2/download/philips%40eve-20101203092302-30zu7xpf7uw0ub3a/dbt3s0.001.dump-20101203092242-l5ozx659nxhsvi0e-3/dbt3-s0.001.dump

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)

[Igor Babaev (igorb-seattle)]

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;
+----+-------------+---------+--------+-------------------------+---------+---------+-------------------+------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+-------------------------+---------+---------+-------------------+------+--------------------------------------------------------+
| 1 | SIMPLE | City | range | Population,Country,Name | Name | 35 | NULL | 161 | Using index condition; Using where; Rowid-ordered scan |
| 1 | SIMPLE | Country | eq_ref | PRIMARY | PRIMARY | 3 | test.City.Country | 1 | |
+----+-------------+---------+--------+-------------------------+---------+---------+-------------------+------+--------------------------------------------------------+
2

[Igor Babaev (igorb-seattle)]

[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 |
+----+-------------+---------+------+-------------------------+---------+---------+-------------------+------+-------------+