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

[12 Aug 2007 14:20] Tobias Asplund
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;
[13 Aug 2007 7:31] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[22 Aug 2007 13: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 15: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 7:44] Bugs System
Pushed into 5.1.23-beta
[14 Sep 2007 7:46] Bugs System
Pushed into 5.0.50
[23 Oct 2007 16:15] Paul Dubois
Noted in 5.0.50, 5.1.23 changelogs.