Bug #30597 Change EXPLAIN output to include extrema of UNION components
Submitted: 23 Aug 2007 14:16 Modified: 6 Jan 2011 2:35
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Jørgen Løland CPU Architecture:Any
Tags: qc explain
Triage: Triaged: D5 (Feature request)

[23 Aug 2007 14:16] Baron Schwartz
Description:
In the EXPLAIN output of a large UNION, the UNION RESULT row in the output may not list all the items in the UNION.  This makes it a little harder to understand the boundaries of the UNION.

How to repeat:
mysql> explain
    -> select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> union all select 1
    -> ;
+----+--------------+---------------------------------------------------------------+------+---------------+------+---------+------+------+----------------+
| id | select_type  | table                                                         | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+--------------+---------------------------------------------------------------+------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY      | NULL                                                          | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used | 
...snip...
| 33 | UNION        | NULL                                                          | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used | 
| NULL | UNION RESULT | <union1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,...> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                | 
+----+--------------+---------------------------------------------------------------+------+---------------+------+---------+------+------+----------------+
34 rows in set (0.00 sec)

Suggested fix:
I would prefer to truncate the middle and list the beginning and end with dots in between.  The code in question is in sql_select.cc, in select_describe():

14210       memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
14211       for (; sl && len + lastop + 5 < NAME_LEN; sl= sl->next_select())
14212       {
14213         len+= lastop;
14214         lastop= my_snprintf(table_name_buffer + len, NAME_LEN - len,
14215                             "%u,", sl->select_number);
14216       }
14217       if (sl || len + lastop >= NAME_LEN)
14218       {
14219         memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1);
14220         len+= 4;
14221       }

I'm not a good C coder, so I don't know what's a good patch, but I'd like to see this output:

<union1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...33>
[27 Aug 2007 11:34] Valeriy Kravchuk
Thank you for a reasonable feature request.
[18 Aug 2010 9:01] 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/116059

3220 Jorgen Loland	2010-08-18
      Bug#30597: Change EXPLAIN output to include extrema of 
                 UNION components
      
      Previously, EXPLAIN of a large union would truncate the 
      UNION RESULT row at the end of the list like this if the 
      string got too large:
      
      <union1,2,3,4,...>
      
      With this patch, truncation happens in the middle of the string
      like this:
      
      <union1,2,3,...,9>
      
      This makes it easier to understand the boundaries of the UNION.
     @ mysql-test/r/explain.result
        Added test for BUG#30597
     @ mysql-test/t/explain.test
        Added test for BUG#30597
     @ sql/sql_lex.h
        Add inline function st_select_lex::last_select()
     @ sql/sql_select.cc
        Truncate UNION RESULT row of large UNIONs in the middle of the string instead of in the end
[25 Aug 2010 9:45] Guilhem Bichot
approved, minor comments sent by mail
[26 Aug 2010 12:03] 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/116857

3248 Jorgen Loland	2010-08-26
      Bug#30597: Change EXPLAIN output to include extrema of 
                 UNION components
            
      Previously, EXPLAIN of a large union would truncate the 
      UNION RESULT row at the end of the list like this if the 
      string got too large:
            
      <union1,2,3,4,...>
            
      With this patch, truncation happens in the middle of the string
      like this:
            
      <union1,2,3,...,9>
            
      This makes it easier to understand the boundaries of the UNION.
     @ mysql-test/r/explain.result
        Added test for BUG#30597
     @ mysql-test/t/explain.test
        Added test for BUG#30597
     @ sql/sql_lex.h
        Add inline function st_select_lex::last_select()
     @ sql/sql_select.cc
        Truncate UNION RESULT row of large UNIONs in the middle of the
        string instead of in the end
[26 Aug 2010 12:03] Jørgen Løland
Pushed to next-mr-bugfixing
[26 Aug 2010 13:55] 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/116896

3250 Jorgen Loland	2010-08-26
      Post-push fix for BUG#30597: Need to explicitly cast int 
      argument to log10() to double on SPARC 
     @ sql/sql_select.cc
        Cast int argument to log10() to double
[30 Aug 2010 8:33] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[13 Nov 2010 16:21] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[6 Jan 2011 2:35] Paul Dubois
Noted in 5.6.1 changelog.

Previously, EXPLAIN output for a large union truncated the UNION
RESULT row at the end of the list as follows if the string became too
large: 

<union1,2,3,4,...>

To make it easier to understand the boundaries of the union,
truncation now occurs in the middle of the string:

<union1,2,3,...,9>