Bug #43684 JDBC Metadata ColumnName Name is incorrect if using field alias

• Submitted: 17 Mar 2009 0:16
• Modified: 18 Mar 2009 19:00
• Reporter: Thomas Feldmann
• Status: Closed
• Category: Connector / J
• Severity: S2 (Serious)
• Version: mySql 5.1.31 / JDBC 5.1.7
• OS: Any
• CPU Architecture: Any
• Tags: alias, as, field, jdbc, metadata

[17 Mar 2009 0:16] Thomas Feldmann

Description:
If you define a alias (as) for a select field, it is not possible to access the field over the alias. On the mysql command line it works, so i think it's a problem of the jdbc driver.

-> Example 
select COL1 as xx, COL2 as xy from TEST_ALIAS;

If you use a simple function over a field the alias is working

-> Example
select  if(COL1=1,COL1,COL1) as xx, COL2 as xy from TEST_ALIAS;

How to repeat:
CREATE TABLE TEST_ALIAS
    (COL1 VARCHAR(128),
     COL2 VARCHAR(12864)
);
insert into TEST_ALIAS VALUES ('VAL10','VAL20');
insert into TEST_ALIAS VALUES ('VAL11','VAL21');

select COL1, COL2 from TEST_ALIAS;
-> Result
COL1	COL2
------------
VAL10	VAL20
VAL11	VAL21

select COL1 as xx, COL2 as xy from TEST_ALIAS;
-> Result
COL1	COL2
------------
VAL10	VAL20
VAL11	VAL21

select  if(COL1=1,COL1,COL1) as xx, COL2 as xy from TEST_ALIAS;
-> Result
xx	COL2
------------
VAL10	VAL20
VAL11	VAL21

[17 Mar 2009 7:43] Tonci Grgin

Hi Thomas and thanks for your report.

Please attach your small but complete test case (see my samples in numerous reports). Do note JDK and connection string you're using.

[17 Mar 2009 22:15] Thomas Feldmann

Sorry for wrong information, the access with alias field name is working.
But the metadata are incorrect.
So other application like BIRT not working anymore.

Using Java:
SUN 1.5.0_15 and
IBM 1.5.0_9

Example Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class MySqlBugReport {

	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://serv1003:50101/appdb01";
			Connection connection = DriverManager.getConnection(url, "root", "xxxxxx");
			Statement statement = connection.createStatement();
			System.out.println("Query=select COL1 as xx, COL2 as xy from TEST_ALIAS");
			ResultSet resultSet = statement.executeQuery("select COL1 as xx, COL2 as xy from TEST_ALIAS");
			ResultSetMetaData resultSetMetaData =  resultSet.getMetaData();

			for (int j = 1; j <= resultSetMetaData.getColumnCount(); j++) {
				System.out.println("MetaData ColumnName=" + resultSetMetaData.getColumnName(j));
			}
		  if (resultSet.next()) {
		  	String xx = resultSet.getString("xx");
		  	System.out.println("Get xx=" + xx);
		  }

			resultSet.close();
			
			System.out.println("Query=select  if(COL1=1,COL1,COL1) as xx, COL2 as xy from TEST_ALIAS");
			resultSet = statement.executeQuery("select  if(COL1=1,COL1,COL1) as xx, COL2 as xy from TEST_ALIAS");
			resultSetMetaData =  resultSet.getMetaData();

			for (int j = 1; j <= resultSetMetaData.getColumnCount(); j++) {
				System.out.println("MetaData ColumnName=" + resultSetMetaData.getColumnName(j));
			}
		  if (resultSet.next()) {
		  	String xx = resultSet.getString("xx");
		  	System.out.println("Get xx=" + xx);
		  }

			resultSet.close();
			statement.close();
			connection.close();
			
			

		}
		catch (Exception e) {
			e.printStackTrace();
		}

	}

}

Output:
Query=select COL1 as xx, COL2 as xy from TEST_ALIAS
MetaData ColumnName=COL1
MetaData ColumnName=COL2
Get xx=VAL10
Query=select  if(COL1=1,COL1,COL1) as xx, COL2 as xy from TEST_ALIAS
MetaData ColumnName=xx
MetaData ColumnName=COL2
Get xx=VAL10

[18 Mar 2009 7:45] Tonci Grgin

Thanks Thomas.

Now, this appears to me like duplicate of Bug#31499, see what Mark said there:
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.

The documentation team changed 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.

Please test with "useOldAliasMetadataBehavior=true" or with rsmd.getColumnLabel() and report back to me with result.

[18 Mar 2009 19:00] Thomas Feldmann

Thanks a lot for your support.
You are right.
Sorry for not finding the other bug report.
We are opening a issue on BIRT, to change the metadata handling.