MySQL Bugs: #30749: Table name not included in result set metadata for union queries

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

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.

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));
  ?>

Sorry for not being clear. I meant MySQL 4.0.26 when I stated MySQL 4.

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.

This is a duplicate of Bug#28577

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.

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.

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).

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...

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.