| Bug #31499 | Alias lost when selecting from LEFT JOIN involving Derived Table | ||
|---|---|---|---|
| Submitted: | 10 Oct 2007 11:13 | Modified: | 31 Oct 2007 15:58 |
| Reporter: | Mick Francis (Candidate Quality Contributor) | ||
| Status: | Closed | ||
| Category: | Connector/J | Severity: | S2 (Serious) |
| Version: | 5.1.5 | OS: | Any (Also in previous versions) |
| Assigned to: | MC Brown | Target Version: | |
[10 Oct 2007 15:33]
Mark Matthews
The behavior you ask for isn't actually JDBC-compliant (which is why there was a change in behavior in 5.1). If you want the old, non-compliant behavior, you should add "useOldAliasMetadataBehavior=true" as a configuration parameter in your JDBC URL. I've alerted the documentation team so that we can get the "Upgrading" section to mention this change.
[10 Oct 2007 15:35]
Mark Matthews
Note: The JDBC-compliant way of getting the information you're asking for, i.e. the "alias" for the column is by calling ResultSetMetaData.getColumnLabel(), not getColumnName(). The reason that getColumnName() is _not_ supposed to return the alias, is because it is supposed to be possible for a consumer of this API to generate a DML statement based on the metadata present in the ResultSet and ResultSetMetaData, and thus getColumnName() should return the _physical_ column name, not the alias.
[31 Oct 2007 15:58]
MC Brown
I've added a note to the Upgrade section of Connector/J to highlight this change.
Description: The alias for a SELECT expression should be returned as the name for that column in the ResultSet. In the example given, the alias is lost. How to repeat: -- Create tables as follows create table BugDimensionDesc ( cDimensionId varchar(255) not null, cDimensionTypeId varchar(16) not null, cDimValSql text null, bLocked tinyint not null) engine=innodb; alter table BugDimensionDesc add constraint PK_BugDimensionDesc primary key (cDimensionId); create table BugDimensionValues ( cDimensionId varchar(255) not null, cDataTable varchar(128) not null, cDimValSql text not null) engine=innodb; alter table BugDimensionValues add constraint PK_BugDimensionValues primary key (cDimensionId, cDataTable); create table BugDataTables ( cDataTable varchar(128) not null, cPkColumns varchar(255) null, dUpdateStart datetime not null, dUpdateEnd datetime null, bEnableQueryCaching tinyint not null, bEnableAutoCaching tinyint not null) engine=innodb; alter table BugDataTables add constraint BugDataTables_PK primary key (cDataTable); -- Now run the following SELECT using JDBC: -- SELECT DT.cDimensionId, -- DT.cDataTable, -- DT.cDimValSql AS cGenericDvs, -- DV.cDimValSql -- FROM (SELECT * FROM BugDimensionDesc,BugDataTables)DT -- LEFT JOIN BugDimensionValues DV ON DV.cDimensionId = DT.cDimensionId -- AND DV.cDataTable = DT.cDataTable -- WHERE DT.cDimValSql IS NOT NULL -- OR DV.cDimValSql IS NOT NULL; -- -- The name of the 3rd column is returned as cDimValSql rather than cGenericDvs