MySQL Bugs: #31499: Alias lost when selecting from LEFT JOIN involving Derived Table

Bug #31499	Alias lost when selecting from LEFT JOIN involving Derived Table
Submitted:	10 Oct 2007 9:13	Modified:	31 Oct 2007 14:58
Reporter:	Mick Francis (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.5	OS:	Any (Also in previous versions)
Assigned to:	MC Brown	CPU Architecture:	Any

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

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.

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.

I've added a note to the Upgrade section of Connector/J to highlight this change.

I was revisiting some tests against a MySQL database I had created awhile back and updated the MySQL drivers and Connector/J.  As a result, it appears I ran across this same issue.  Here's the test case:

---------------------
test.sqlj file:
---------------------
public class test {

   public test() {
      try {
         MyIterator iter;
         #sql iter = {
            select SCHEMA_NAME "getSchemaName" from information_schema.SCHEMATA
         };
      }
      catch (java.sql.SQLException ex) {
      }
   }

   #sql public static iterator MyIterator implements sqlj.runtime.Scrollable (
      String getSchemaName
   );

}
---------------------

sqlj.properties file:
---------------------
sqlj.driver = com.mysql.jdbc.Driver,org.gjt.mm.mysql.Driver
sqlj.url = jdbc:mysql://YOUR.HOST.NAME/DBNAME
#sqlj.url = jdbc:mysql://YOUR.HOST.NAME/DBNAME?useOldAliasMetadataBehavior=true
sqlj.user = USERNAME/PASSWORD
sqlj.codegen = iso
---------------------

Command line:
---------------------
cd %ORACLE_HOME% (C:\OracleHomes\product\11.1\client)
set CLASSPATH=C:\mysql-connector-java-5.1.10\mysql-connector-java-5.1.10-bin.jar;sqlj\lib\translator.jar;sqlj\lib\runtime12.jar;jdbc\lib\ojdbc6.jar
bin\sqlj.exe -props=sqlj.properties -status test.sqlj
---------------------

Output:
---------------------
>bin\sqlj.exe -props=sqlj.properties -status test.sqlj
[Translating]
[Reading file test]
[Translating file test]
test.sqlj:7.10-9.10: Warning: You are using an Oracle JDBC driver, but connecting to an non-Oracle database. SQLJ will perform JDBC-generic SQL checking.
test.sqlj:7.10-9.10: Error: Column java.lang.String getSchemaName not found in SELECT list.
test.sqlj:7.10-9.10: Warning: The result set column "SCHEMA_NAME" VARCHAR was not used by the named cursor.
Total 1 error and 2 warnings.
---------------------

Comments
---------------------

* with the older version of the Connector/J, the test.java file would get created and the "Error: Column java.lang.String getSchemaName not found in SELECT list." and "Warning: The result set column "SCHEMA_NAME" VARCHAR was not used by the named cursor." messages did not appear.

* adding "useOldAliasMetadataBehavior=true" during translation does resolve the problem; and, fortunately, you don't need that parameter at runtime so you get the new behavior you want when the program is run

* the default behavior should be for a program to be translated successfully, therefore either the Connector/J or SQLj should be changed so that it works without having to specify an "old" behavior -- I suspect that SQLj needs to accomodate the "new" behavior, maybe by changing it's use of getColumnName to getColumnLabel, but since you guys now own both (Oracle's implementation of SQLj and MySQL), I'll let you figure out the politics within Oracle Corp. :-)

Thanks.

Robert, thanks for your feedback but we are two separate companies at the moment so there is nothing we can do. Please contact Oracle.