| Bug #88949 | jdbc returns incorrect column name when using subselect | ||
|---|---|---|---|
| Submitted: | 17 Dec 2017 18:41 | Modified: | 18 Jan 2018 17:01 |
| Reporter: | Dave Pullin (Basic Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | connector version 5.1. mysql 5.7.18 ++ | OS: | CentOS (6 & 7) |
| Assigned to: | CPU Architecture: | Any | |
[18 Dec 2017 13:31]
Chiranjeevi Battula
Hello Dave Pullin, Thank you for the bug report. Verified this behavior on MySQL Connector / J 5.1.44. Thanks, Chiranjeevi.
[18 Dec 2017 13:32]
Chiranjeevi Battula
run: Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. Driver: com.mysql.jdbc.Driver version 6.0 Driver from:jar:file:/C:/Program%20Files%20(x86)/MySQL/Connector.J%205.1/mysql-connector-java-5.1.44-bin.jar!/com/mysql/jdbc/Driver.class Mon Dec 18 14:30:19 IST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. SQL:select version() as mysql_version, columnname as finalname from (select 'datavalue' as columnname ) as subtable getColumnName: mysql_version = 5.7.20-log getColumnName: columnname = datavalue BUILD SUCCESSFUL (total time: 0 seconds)
[17 Jan 2018 18:40]
Filipe Silva
Hi, Thanks for taking the time to report this Dave. This is not a bug, though. Prior to JDBC 4.0 it wasn't clear what could be used to designate a column name, if the real column name or its alias given by the SQL AS clause. This is why you are getting "finalname" in those very old drivers. JDBC 4.0 clarified this so since then there is a clear distinction of what is a column name and its display name. So, in your code, to have it working as you expect, you should use the ResultSetMetaData method getColumnLabel() instead of getColumnName(). This way getColumnLabel() returns the column alias, as per defined in the SQL AS clause, and getColumnName() returns the real column name or expression or alias in case there is no column backing up the result. (See also: https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSetMetaData.html#getColumnLabel-i...) Finally, you still have to option to get back the old way of handling column names by setting the connection property useOldAliasMetadataBehavior=true if you really need to. I wouldn't recommend it, but it is up to you to decide what is best. IHTH
[18 Jan 2018 17:01]
Dave Pullin
Thank you. Sorry I didn't spot the change.
Description: select version() as mysql_version, columnname as finalname from (select 'datavalue' as columnname ) as subtable should return 'finalname' and the name of the second column it does so in a mysql console, and it does so with earlier versions of Connector/J With version 5.1 it returns 'columnname' How to repeat: import java.sql.Connection; import java.sql.Driver; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class TestJdbcError { public static void main(String[] args) throws Exception { Driver d = (Driver) Class.forName("com.mysql.jdbc.Driver").newInstance(); System.out.println("Driver: " + d.getClass().getName() + " version " + d.getMajorVersion() + "." + d.getMinorVersion()); Class c = d.getClass(); System.out.println("Driver from:" + (c.getClassLoader().getResource(c.getCanonicalName().replace(".", "/") + ".class")).toExternalForm()); String url = "jdbc:mysql://127.0.0.1/XXXXXXX" + "?enableEscapeProcessing=false&noDatetimeStringSync=true&autoReconnect=true&zeroDateTimeBehavior=convertToNull"; Connection connection = java.sql.DriverManager.getConnection(url, XXXXXXX, XXXXXXX); Statement statement = connection.createStatement(); String sql = "select version() as mysql_version, columnname as finalname from (select 'datavalue' as columnname ) as subtable"; System.out.println("SQL:" + sql); ResultSet rs = statement.executeQuery(sql); ResultSetMetaData metadata = rs.getMetaData(); rs.next(); for (int i = 1; i <= metadata.getColumnCount(); i++) { System.out.println("getColumnName: " + metadata.getColumnName(i) + " = " + rs.getString(i)); } } } ran 3 times with different versions of Connector: Driver: com.mysql.jdbc.Driver version 5.1 Driver from:jar:file:/s/libx/mysql-connector-java-5.1.44-bin.jar!/com/mysql/jdbc/Driver.class Sun Dec 17 13:22:48 EST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. SQL:select version() as mysql_version, columnname as finalname from (select 'datavalue' as columnname ) as subtable getColumnName: mysql_version = 5.7.18 getColumnName: columnname = datavalue <<<<<<<<<<<<<<<<<<<< wrong <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<, BUILD SUCCESSFUL (total time: 0 seconds) Driver: com.mysql.jdbc.Driver version 5.0 Driver from:jar:file:/s/libx/mysql-connector-java-5.0.8-bin.jar!/com/mysql/jdbc/Driver.class SQL:select version() as mysql_version, columnname as finalname from (select 'datavalue' as columnname ) as subtable getColumnName: mysql_version = 5.7.18 getColumnName: finalname = datavalue <<<<<<<<<<<<<<<<<<<< correct <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< BUILD SUCCESSFUL (total time: 0 seconds) Driver: com.mysql.jdbc.Driver version 0.0 Driver from:jar:file:/s/libx/jdbc5.0.7fixed.jar!/com/mysql/jdbc/Driver.class SQL:select version() as mysql_version, columnname as finalname from (select 'datavalue' as columnname ) as subtable getColumnName: mysql_version = 5.7.18 getColumnName: finalname = datavalue <<<<<<<<<<<<<<<<<<<< correct <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< BUILD SUCCESSFUL (total time: 0 seconds) Suggested fix: return the correct column name!