Bug #30749 Table name not included in result set metadata for union queries
Submitted: 31 Aug 2007 16:09 Modified: 14 Dec 2007 20:04
Reporter: Mark Callaghan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.37 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: column, metadata, name, UNION

[31 Aug 2007 16:09] Mark Callaghan
Description:
In MySQL5, the table name is blank for fields returned by UNION queries. In MySQL4 it was set using the table name from the first SELECT branch.

While this might not be a bug, it is a regression from MySQL4 to MySQL5 and the MySQL5 documentation (http://dev.mysql.com/doc/refman/5.0/en/union.html) is not clear on what should be expected. I read the current docs to imply that the MySQL4 behavior is expected.

How to repeat:
From MySQL5
mysql> select s.Date from Dps s union select t.Date from Dps t;
Field   1:  `Date`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   0
Flags:      NOT_NULL BINARY

From MySQL4:
mysql> select s.Date from Dps s union select t.Date from Dps t;
Field   1:  `Date`
Catalog:    ``
Database:   ``
Table:      `s`
Org_table:  `s`
Type:       DATE
Collation:  ? (0)
Length:     10
Max_length: 10
Decimals:   0
Flags:      NOT_NULL

Suggested fix:
Update the docs. If you don't revert to MySQL4 behavior, then this will be a source of confusion for many people trying to upgrade.
[2 Sep 2007 6:08] Hartmut Holzgraefe
I'm getting the same results for MySQL 4.1, 5.0 and 5.1-beta,
with all of them the Table and Org_Table information is empty.
When you are refering to MySQL 4 would that actually be 4.0?

Tested using PHP 5 / mysqli :

  <?php
    $db = mysqli_connect("localhost", "root", "", "test") or die("can't connect");

    mysqli_query($db, "DROP TABLE IF EXISTS t1") or die(mysql_error($db));

    mysqli_query($db, "CREATE TABLE t1(d DATE)") or die(mysql_error($db));

    $res = mysqli_query($db, "SELECT a.d FROM t1 a UNION SELECT b.d FROM t1 b") or die(mysqli_error($db));

    print_r(mysqli_fetch_fields($res));
  ?>
[2 Sep 2007 6:46] Mark Callaghan
Sorry for not being clear. I meant MySQL 4.0.26 when I stated MySQL 4.
[3 Sep 2007 9:29] Sveta Smirnova
Thank you for the report.

Verified as described. May be should be reclassified as documentation bug, as this change is not reflected in any ChangeLog.
[16 Oct 2007 10:32] Tonci Grgin
This is a duplicate of Bug#28577
[20 Nov 2007 19:19] Martin Hansson
Although related, this is not a duplicate of Bug#28577. This bug is exclusively the database name, table name and org_table fields missing from metadata. Catalog information appears to be present in ChangeSet@1.2570.1.1.
[21 Nov 2007 10:20] Martin Hansson
This is not a bug. In UNION queries the desired behavior is that the following fields should be empty:

- Database
- Table
- Org_table

I set this to 'Documenting' to reflect the fact that the problem is the manual not being totally clear on this topic.
[12 Dec 2007 8:01] Martin Hansson
The bespoke fields are empty since version 4.0.26 and have been a source of controversy ever since. But every time the discussion starts over, afaiu it eventually ends with all parties agreeing that returning blank fields is the correct thing to do.

I don't think that returning named fields was a bug. It is simply a change in behavior that I doubt was documented at the time (the behavior nor the change).
[12 Dec 2007 12:43] Tonci Grgin
Martin, although I agree with your findings (as I see the potential problems) I must say that there are a lot of bug reports regarding this so we might have to reconsider the decision to just document things... Metadata is lost, even on tables just touched by UNION, and that represents grave problem, especially for connectors who have well defined platforms/specifications to follow...
[14 Dec 2007 20:04] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I have documented that the table, org_table, and db values are empty for UNION statements.