Bug #92538 when I use CachedRowSet. error:Table doesn't exist
Submitted: 24 Sep 2018 3:42 Modified: 1 Oct 2018 8:41
Reporter: Yanhui Chi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.23, 5.7.17 OS:Windows (windows10)
Assigned to: CPU Architecture:x86
Tags: cachedrowset, jdbc, table alias, Table doesn't exist

[24 Sep 2018 3:42] Yanhui Chi 
Description:
JAVA code 1:
public class TestMysql {
	public static void main(String[] args) {
		Connection conn = DBUtil.getConnection();
		try {
			Statement st = conn.createStatement();
			String str = "select * from " +
					"(SELECT a.sheetid,deptname AS qymc," +
					"countyname AS ssqy,dicname AS sshy FROM" +
					" tb_whpcoun_commsg a LEFT JOIN tb_whpcoun_dic b" +
					" ON a.industrytype1=b.sheetid" +
					") t1 ORDER BY qymc";
			System.out.println(str);
			ResultSet rs = st.executeQuery(str);
			CachedRowSet rowSet = new CachedRowSetImpl();
	        rowSet.populate(rs);
			DBUtil.closeAll(conn,st,null);
	        while (rowSet.next()) {
	        	System.out.println(rowSet.getString("sheetid"));
	        }
			System.out.println("ok");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

output 1:
select * from (SELECT a.sheetid,deptname AS qymc,countyname AS ssqy,dicname AS sshy FROM tb_whpcoun_commsg a LEFT JOIN tb_whpcoun_dic b ON a.industrytype1=b.sheetid) t1 ORDER BY qymc
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'aqsc_yjxy.a' doesn't exist
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
	at com.mysql.jdbc.Field.getCollation(Field.java:446)
	at com.mysql.jdbc.ResultSetMetaData.isCaseSensitive(ResultSetMetaData.java:552)
	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:723)
	at com.sun.rowset.CachedRowSetImpl.populate(CachedRowSetImpl.java:640)
	at test.TestMysql.main(TestMysql.java:28)

JAVA code 2:
public class TestMysql {
	public static void main(String[] args) {
		Connection conn = DBUtil.getConnection();
		try {
			Statement st = conn.createStatement();
			String str = "select * from " +
					"(SELECT tb_whpcoun_commsg.sheetid,deptname AS qymc," +
					"countyname AS ssqy,dicname AS sshy FROM" +
					" tb_whpcoun_commsg LEFT JOIN tb_whpcoun_dic" +
					" ON tb_whpcoun_commsg.industrytype1=tb_whpcoun_dic.sheetid" +
					") t1 ORDER BY qymc";
			System.out.println(str);
			ResultSet rs = st.executeQuery(str);
			CachedRowSet rowSet = new CachedRowSetImpl();
	        rowSet.populate(rs);
			DBUtil.closeAll(conn,st,null);
	        while (rowSet.next()) {
	        	System.out.println(rowSet.getString("sheetid"));
	        }
			System.out.println("ok");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

output 2:
select * from (SELECT tb_whpcoun_commsg.sheetid,deptname AS qymc,countyname AS ssqy,dicname AS sshy FROM tb_whpcoun_commsg LEFT JOIN tb_whpcoun_dic ON tb_whpcoun_commsg.industrytype1=tb_whpcoun_dic.sheetid) t1 ORDER BY qymc
6E958840-2100-E937-21ED-11D9FFB94CAA
9DD5E5F5-3D3C-9CE8-3DF4-75A493ED698A
270067BC-22BD-BCB0-82D1-B85FF3F45873
EC4DCB69-91A0-9226-4D8A-5436071CC832
6E95A0F4-3558-959E-CEC4-69CB35D0BF03
AC5E5CA6-3ED9-66ED-5C3D-A6AF5860CA19
CC5CBD42-A385-DDC6-51C2-E740BCABFDFD
4EAD1F4E-3E74-5923-D489-630D1E038F91
DF3E0884-6BF3-D645-27E4-68AEF0DB07D9
0CF29AC1-FB87-502C-B77C-D11B0626F5DD
8DAD7F40-A858-3441-85DA-4CE53CC4ED73
1DF7A254-F729-6176-0054-D1D961777723
314ADF25-0EDE-E399-611D-D5B1183955D9
164B4EFD-DADF-7EEB-57E3-D8F42A11E87E
3DB88A7F-29BF-C373-7729-971775BFD38D
A01E20E5-2217-6EB1-882F-6D582CCC692E
4030D721-A52D-D621-D1F3-362B36D10426
F71EB691-0C7D-7191-A5C6-CDEF2DD3F4AB
E5BA5892-06BE-A6F6-E71E-0761382B8311
56158CF4-B725-0BCC-ECB6-BAAD3C3C0695
8D3231FB-9DA1-0D0E-7561-BACAFCAD901D
0B6C8208-B0E5-EE7A-0525-DD8C9B7ABC94
ABDE203D-B665-6E77-3C1B-A7F484BF2A50
B5AAA5D1-4B36-42A7-DC48-E97566D03269
C61E31F9-EDA0-0F9C-49BF-08C03BC0A2D2
CAFBD44F-7507-0C5C-FA1E-D9B222C5B443
3D48C393-D42B-6FA6-6219-F41120C2A6D8
C30F78FA-3408-C50D-29F2-5B0D8817CFC7
7EF67FC7-E469-B9B0-0F71-AEAEB15E3B68
BE86E906-A6EB-DFA6-D53E-DE60E1D00D7D
3577BF73-7631-0967-8A9C-D04346A14021
AE4E7C0F-B1C7-653D-B51D-12CA4DBA2816
338C7C8A-73C4-D121-F798-1A74B7CB5DC8
D743DE1C-E205-469D-D87A-B7A925A3F733
E0434BEC-8A9C-C7A4-1140-466274187D30
AFCC411C-FC9E-2B11-86B0-CAC7D385D19B
ok

How to repeat:
JAVA code:
public class TestMysql2 {
	public static void main(String[] args) {
		Connection conn = DBUtil.getConnection();
		try {
			Statement st = conn.createStatement();
			String str = "SELECT * FROM (SELECT * FROM city a) t";
			System.out.println(str);
			ResultSet rs = st.executeQuery(str);
			CachedRowSet rowSet = new CachedRowSetImpl();
	        rowSet.populate(rs);
			DBUtil.closeAll(conn,st,null);
	        while (rowSet.next()) {
	        	System.out.println(rowSet.getString("sheetid"));
	        }
			System.out.println("ok");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

output:
SELECT * FROM (SELECT * FROM city a) t
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'world.a' doesn't exist
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
	at com.mysql.jdbc.Field.getCollation(Field.java:446)
	at com.mysql.jdbc.ResultSetMetaData.isCaseSensitive(ResultSetMetaData.java:552)
	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:723)
	at com.sun.rowset.CachedRowSetImpl.populate(CachedRowSetImpl.java:640)
	at test.TestMysql2.main(TestMysql2.java:23)

Suggested fix:
when i use mysql 5.7.3, there is no such problem.
And it happen on 5.7.20 and 5.7.23
[28 Sep 2018 8:28] Alexander Soklakov 
Hi Yanhui Chi,

It looks like the table alias is used instead of the table name when SHOW FULL COLUMNS query is issued by driver. What c/J version do you use?

As a workaround you could set connection property useDynamicCharsetInfo=false.
[28 Sep 2018 11:17] Yanhui Chi 
MySQL Connector/J : 5.1.47
setting connection property useDynamicCharsetInfo=false fix it.
[1 Oct 2018 8:41] MySQL Verification Team 
Thank you Yanhui Chi for the report, Alex for guiding me in verifying this.

regards,
Umesh