Bug #246 alias not recognized when selecting only one table's fields from joined tables

• Submitted: 8 Apr 2003 3:12
• Modified: 8 Apr 2003 6:05
• Reporter: [ name withheld ]
• Status: Closed
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 3.0.6,3.1.0
• OS: Windows (windows 2000 professional)
• CPU Architecture: Any

[8 Apr 2003 3:12] [ 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. 
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 6:05] Mark Matthews

This is a known issue. You can't create updatable result sets on queries with aliases, because MySQL versions prior to version 4.0 don't return the original table names.

You must use MySQL-4.1 if you want to be able to do this.

[16 Apr 2003 19:51] [ name withheld ]

Thanks for your answer.

But why does it return correct result when I select more than 2 fields from different tables? e.g:

select a.id,b.name from tb_a a inner join tb_b b on a.id=b.id

it will return correct result.

but when I execute query: select a.id,a.name from tb_a a inner join tb_b b on a.id=b.id, it return the exception.

So the emphasis is whether the selected fields are from different table, i.e. whether the different alias appear in the column names. Is this known feature of Mysql4.0.11? And Why doesn't happen with Connect/J 2.0.14?