MySQL Bugs: #36327: table name aliasing does not work

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:	None
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

Description:
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:
Init:
CREATE TABLE test (id INT, data TEXT);
INSERT INTO test VALUES(10, 'hello');

Query:
SELECT user.id, user.data FROM test AS user;

Test case

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

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,
27.4.2.3.2. 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
Flags:      NOT_NULL PRI_KEY NO_DEFAULT_VALUE PART_KEY

Hi,

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.

Thanks,
Shajee

Fixed test case

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

Hi,

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.