Bug #36327 table name aliasing does not work
Submitted: 24 Apr 2008 20:57 Modified: 28 Nov 2013 12:55
Reporter: Nam Nguyen Email Updates:
Status: Can't repeat Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.6 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: alias, getTableName, ResultMetaData, table

[24 Apr 2008 20:57] Nam Nguyen
In 5.1.6, ResultSetMetaData.getTableName() returns the actual table name 'test'.  In 3.1, it returned the table alias "user".  There is no way to get the 'user' table alias for 5.1.6, which means the result does not reflect the query.

The 'useOldAliasMetadataBehavior' option is broken in 5.1.6 as noted in another bug report.  But if it is working, then there would be no way to retrieve the actual table name 'test' from the ResultMetaData.  This would mean the result reflects the query (but supposedly that violates the JDBC standard?).

How to repeat:
CREATE TABLE test (id INT, data TEXT);
INSERT INTO test VALUES(10, 'hello');

SELECT user.id, user.data FROM test AS user;
[25 Apr 2008 5:53] Tonci Grgin
Test case

Attachment: TestBug36327.java (text/x-java), 3.82 KiB.

[25 Apr 2008 6:03] Tonci Grgin
Hi Nam and thanks for your report.

Actually, this is a bug regarding useOldAliasMetadataBehavior, which appears broken to me, and a feature request to introduce rs.GetTableAlias() function. Or so I see it.

According to manual, Upgrading to MySQL Connector/J 5.1.x
    * In Connector/J 5.0.x and earlier, the alias for a table in a SELECT statement is returned when accessing the result set metadata using ResultSetMetaData.getColumnName(). This behavior however is not JDBC compliant, and in Connector/J 5.1 this behavior was changed so that the original table name, rather than the alias, is returned.

my test case should pass, but it doesn't due to fact that "useOldAliasMetadataBehavior" value has no influence on metadata returned... Further more, if "useOldAliasMetadataBehavior" should work as documented and is set to TRUE, I'm not sure what getColumnLabel should return...

Mark, please clarify:
 - what should getColumnLabel return in case "useOldAliasMetadataBehavior" is TRUE.
 - what is the proper way to get "Original_table" in both cases.
 - what is the proper way to get "Table" in both cases.

Server version: 5.0.58-pb1083-log MySQL Pushbuild Edition, build 1083
mysql> select a1.fid AS Field0, a1.id AS Field1 FROM bug36327 AS a1;
Field   1:  `Field0`
Catalog:    `def`
Database:   `test`
Table:      `a1`
Org_table:  `bug36327`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     255
Max_length: 0
Decimals:   0
[15 May 2008 4:57] Shajee Shajee

I have also been facing the same problem with MySQL Connector 5.1.6
I have tried setting the useOldAliasMetadataBehavior property to true both as a jdbc property and in the url but nothing works.
The query is returning the column names as is and not returning the Alias names. 

Is this bug being resolved or is there any work-around?
Kindly let me know.

[28 Nov 2013 12:47] Alexander Soklakov
Fixed test case

Attachment: TestBug36327.java (text/x-java), 4.27 KiB.

[28 Nov 2013 12:55] Alexander Soklakov

I don't find that useOldAliasMetadataBehavior is broken with current c/J, I guess it wasn't, we just had a broken testcase here. Please look at the last attached one.

So I close this report as "Can't repeat" taking into account that there is a Bug#34019, a feature request about extending JDBC API with getTableLabel() method.