MySQL Bugs: #107534: Aliases are not honored in Select statements when using Views + useCursorFetch

Bug #107534	Aliases are not honored in Select statements when using Views + useCursorFetch
Submitted:	9 Jun 2022 21:00	Modified:	13 Jun 2022 13:53
Reporter:	Mariano Gaston	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.29	OS:	Ubuntu (18.04.6 LTS)
Assigned to:		CPU Architecture:	x86
Tags:	alias, useCursorFetch, VIEW

Description:
If the useCursorFetch property is specified during DB Connection, then executing a query using an alias for a View does not allow to gather columns data using that alias, ie:

String url = "jdbc:mysql://127.0.0.1:3306/mysqlbugreport?useCursorFetch=true";
..

String query = "select * from viewTest as someAlias";
..

long idMain = rs.getLong("someAlias.idMain");

Throws the following exception:

Exception in thread "main" java.sql.SQLException: Column 'someAlias.idMain' not found.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.result.ResultSetImpl.findColumn(ResultSetImpl.java:584)
	at com.mysql.cj.jdbc.result.ResultSetImpl.getLong(ResultSetImpl.java:862)

Some side notes:

1. This aliases works perfectly you if you are just using tables (instead of views)
2. This behaviour only ocurrs when "useCursorFetch" is set to true
3. I've also tried setting up "useColumnNamesInFindColumn=true" and "useOldAliasMetadataBehavior=true" but it didn't work

How to repeat:
Create this simple schema:

CREATE TABLE `mainTable` (
  `idMain` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`idMain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `otherTable` (
  `idMain` int(10) unsigned NOT NULL,
  `value` int(11) NOT NULL,
  KEY `idMain` (`idMain`),
  CONSTRAINT `otherTable_ibfk_1` FOREIGN KEY (`idMain`) REFERENCES `mainTable` (`idMain`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `mainTable` VALUES (1);
INSERT INTO `otherTable` VALUES (1,10);

-- Create the View
create view viewTest as select * from otherTable;

Now, the following Java code that uses the Connector/J  will popup the error:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestReported {

	public static void main(String[] args) throws ClassNotFoundException, Exception {
		
		
		String url = "jdbc:mysql://127.0.0.1:3306/mysqlbugreport?useCursorFetch=true";
		
		Connection conn = DriverManager.getConnection(url, "bugreport", "bugreport");
		
		String query = "select * from viewTest as someAlias";

		PreparedStatement pstmt = conn.prepareStatement(query);

		ResultSet rs = pstmt.executeQuery();

		while( rs.next() ) {
			
			long idMain = rs.getLong("someAlias.idMain");
			long value  = rs.getLong("someAlias.value");

			System.out.println("idMain=" + idMain + ", value=" + value);
			
		}
		
		rs.close();
		pstmt.close();
		conn.close();
				
	}
	
}

Hello Mariano,

Thank you for the report and test case.
I quickly tried using your test case but not seeing any exception that you have reported. Am I missing anything here? Please let us know. Thank you.

-- MySQL Server 8.0.29(hosted on EL7)

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Bug107534 {

    public static void main(String[] args) throws ClassNotFoundException, Exception {

        String url = "jdbc:mysql://support-cluster03.regionaliad02.mysql2iad.oraclevcn.com:3306/test?useCursorFetch=true";
        Connection conn = DriverManager.getConnection(url, "ushastry", "mysql123");
        Runtime.Version version = Runtime.version();
        System.out.println("JDK version: " + version);
        DatabaseMetaData meta = conn.getMetaData();
        System.out.println("MySQL Server: " + meta.getDatabaseProductVersion());
        System.out.println("Driver Name & Ver: " + meta.getDriverName() + meta.getDriverVersion());            
        
        String query = "select * from viewTest as someAlias";
        PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {
            long idMain = rs.getLong("someAlias.idMain");
            long value = rs.getLong("someAlias.value");
            System.out.println("idMain=" + idMain + ", value=" + value);
        }
        rs.close();
        pstmt.close();
        conn.close();

    }
}

-
ant -f C:\\Work\\MySQLSrc\\Bug107542 -Dnb.internal.action.name=run run
init:
Deleting: C:\Work\MySQLSrc\Bug107542\build\built-jar.properties
deps-jar:
Updating property file: C:\Work\MySQLSrc\Bug107542\build\built-jar.properties
Compiling 1 source file to C:\Work\MySQLSrc\Bug107542\build\classes
compile:
run:
JDK version: 11.0.3+12-LTS
MySQL Server: 8.0.29
Driver Name & Ver: MySQL Connector/Jmysql-connector-java-8.0.29 (Revision: dd61577595edad45c398af508cf91ad26fc4144f)
idMain=1, value=10
BUILD SUCCESSFUL (total time: 27 seconds)

regards,
Umesh

Hi Umesh,

Thanks a lot for taking your time analyzing this issue.

Our production servers arec currently using MySQL 5.7.38 (full version is MySQL Server: 5.7.38-0ubuntu0.18.04.1). 

I've just tested on MySQL 8.0.29 and it's working fine, but fails on MySQL 5.7.38.

Here's a new run using your code (just to provide Java and MySQL versions):

JDK version: 11.0.15
MySQL Server: 5.7.38-0ubuntu0.18.04.1
Driver Name & Ver: MySQL Connector/Jmysql-connector-java-8.0.29 (Revision: dd61577595edad45c398af508cf91ad26fc4144f)
Exception in thread "main" java.sql.SQLException: Column 'someAlias.idMain' not found.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.result.ResultSetImpl.findColumn(ResultSetImpl.java:584)
	at com.mysql.cj.jdbc.result.ResultSetImpl.getLong(ResultSetImpl.java:862)
	at TestReportedMysql8.main(TestReportedMysql8.java:31)

Let me know if I'm missing something.

Regards,
Mariano

Hello Mariano,

Thank you for the feedback.
Verified as described.

regards,
Umesh

Not a Connector/J bug. It seems to be an issue in the MySQL server which was filed in Bug#109117 (not visible until fix published).