[MDEV-2446] LP:754521 - Bogus Last_query_cost reported when sort_intersect is used Created: 2011-04-08  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug754521.xml    

 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

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)

 Comments   
Comment by Igor Babaev [ 2011-04-27 ]

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

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

Comment by Igor Babaev [ 2011-04-27 ]

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

-----------------------------------------------------------------------------------------------+

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 754521

Generated at Thu Feb 08 06:41:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.