| Bug #30377 | EXPLAIN loses last_query_cost when used with UNION | ||
|---|---|---|---|
| Submitted: | 12 Aug 2007 16:20 | Modified: | 23 Oct 2007 18:15 |
| Reporter: | Tobias Asplund | ||
| Status: | Closed | ||
| Category: | Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.45 | OS: | Linux |
| Assigned to: | Georgi Kodinov | Target Version: | |
[13 Aug 2007 9:31]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[22 Aug 2007 15:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/32874 ChangeSet@1.2501, 2007-08-22 16:41:11+03:00, gkodinov@magare.gmz +4 -0 Bug #30377: EXPLAIN loses last_query_cost when used with UNION Currently the Last_query_cost session status variable shows only the cost of a single flat subselect. For complex queries (with subselects or unions etc) Last_query_cost is not valid as it was showing the cost for the last optimized subselect. Fixed by reseting to zero Last_query_cost when the complete cost of the query cannot be determined. Last_query_cost will be non-zero only for single flat queries.
[28 Aug 2007 17:54]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/33234 ChangeSet@1.2501, 2007-08-28 18:51:03+03:00, gkodinov@magare.gmz +4 -0 Bug #30377: EXPLAIN loses last_query_cost when used with UNION Currently the Last_query_cost session status variable shows only the cost of a single flat subselect. For complex queries (with subselects or unions etc) Last_query_cost is not valid as it was showing the cost for the last optimized subselect. Fixed by reseting to zero Last_query_cost when the complete cost of the query cannot be determined. Last_query_cost will be non-zero only for single flat queries.
[14 Sep 2007 9:44]
Bugs System
Pushed into 5.1.23-beta
[14 Sep 2007 9:46]
Bugs System
Pushed into 5.0.50
[23 Oct 2007 18:15]
Paul DuBois
Noted in 5.0.50, 5.1.23 changelogs.
Description: Somehow EXPLAIN will not record the last_query_cost when a UNION query is being used in the EXPLAIN. mysql> CREATE TABLE bug1; ERROR 1113 (42000): A table must have at least 1 column mysql> CREATE TABLE bug1 ( a INT ); Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE bug2 ( a INT ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO bug1 VALUES (1), (2); Query OK, 2 rows affected (0.07 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO bug2 VALUES (3), (4); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM bug1 UNION SELECT * FROM bug2; +------+ | a | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.35 sec) mysql> SHOW SESSION STATUS LIKE '%cost%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | Last_query_cost | 10.999000 | +-----------------+-----------+ 1 row in set (1.26 sec) mysql> EXPLAIN SELECT * FROM bug1 UNION SELECT * FROM bug2; +----+--------------+------------+------+---------------+------+---------+------+------+-- -----+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+------+---------------+------+---------+------+------+-- -----+ | 1 | PRIMARY | bug1 | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | UNION | bug2 | ALL | NULL | NULL | NULL | NULL | 2 | | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+------------+------+---------------+------+---------+------+------+-- -----+ 3 rows in set (0.03 sec) mysql> SHOW SESSION STATUS LIKE '%cost%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | Last_query_cost | 0.000000 | +-----------------+----------+ 1 row in set (0.00 sec) I would have expected the same cost as in the previous example here. How to repeat: CREATE TABLE bug1 ( a INT ); CREATE TABLE bug2 ( a INT ); INSERT INTO bug1 VALUES (1), (2); INSERT INTO bug2 VALUES (3), (4); SELECT * FROM bug1 UNION SELECT * FROM bug2; SHOW SESSION STATUS LIKE '%cost%'; EXPLAIN SELECT * FROM bug1 UNION SELECT * FROM bug2; SHOW SESSION STATUS LIKE '%cost%'; DROP TABLE bug1; DROP TABLE bug2;