Bug #247 alias not recognized when selecting only one table's fields from joined tables
Submitted: 8 Apr 2003 3:15 Modified: 8 Apr 2003 3:18
Reporter: [ name withheld ] Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.0.6 OS:Microsoft Windows (windows 2000 professional)
Assigned to: CPU Architecture:Any

[8 Apr 2003 3:15] [ name withheld ]
Description:
I used mySQL Connector/J 3.0.6 as JDBC Driver in the environment of jboss 3.0.6 with jetty as servlet and jsp container. When the following jsp code executed, the error occured which said, "java.sql.SQLException: General error,  message from server: "Table 'logistics.po' doesn't exist" ". The jsp file is following:

conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
try {
	stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
	rs=stmt.executeQuery("select po.id pid,po.company pc from tb_purchase_orders po inner join tb_order_status b on po.status=b.id where b.name='validated' and po.id='03020002'");
	while(rs.next()){
		out.println(rs.getInt(1)+","+rs.getInt(2));
	}
	conn.commit();
} catch(Exception e){
	e.printStackTrace();
	conn.rollback();
}finally{......

I tried to change the condition. The result is as following:
1. When I created a non-updatable Statement, the error disappeared.
2. When selecting fields from both joined tables, the error disappeared. 
   e.g. select po.id,b.name from ......
3. The error is not related to conn.setAutoCommit(false);

The error didn't occur under the version of MySQL Connector/J 2.0.14.
Infact, the error is not related to JBoss because I repeated the error in a simple java local program. 

How to repeat:
Write any program using jdbc which include codes above.
[8 Apr 2003 3:18] Indrek Siitan
Duplicate of #246.