MySQL Bugs: #30377: EXPLAIN loses last_query

Bug #30377	EXPLAIN loses last_query_cost when used with UNION
Submitted:	12 Aug 2007 14:20	Modified:	23 Oct 2007 16:15
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

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;

Thank you for a bug report. Verified just as described.

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.

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.

Pushed into 5.1.23-beta

Pushed into 5.0.50

Noted in 5.0.50, 5.1.23 changelogs.