MySQL Bugs: #20587: Left join and order

Bug #20587	Left join and order
Submitted:	20 Jun 2006 21:33	Modified:	11 Dec 2007 17:43
Reporter:	Luis Llana	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I have succesfully been using mysql 4.1 with the Java connector  with 
jdk 1.4 downloaded from sun site. 

Last week I tried to migrate to mysql 5, in fact I do not know if the 
bug is mysl 5 or in Java connector. The problem is the following, if I make
a query as follows

 	sql="select vales.fecha,obras.nombre from vales left join obras on vales.obra=obras.id ";
	pstmt = con.prepareStatement(sql);
	rs=pstmt.executeQuery();
	rsmd = rs.getMetaData();
	System.out.println(rsmd.getColumnName(2)+":"+rsmd.getTableName(2)+":");
	rs.next();
	System.out.println(rs.getString("obras.nombre"));

every thing is perfect, but if I try order the query by obras.nombre

  	sql="select vales.fecha,obras.nombre from vales left join obras on vales.obra=obras.id order by obras.nombre";
	pstmt = con.prepareStatement(sql);
	rs=pstmt.executeQuery();
	rsmd = rs.getMetaData();
	System.out.println(rsmd.getColumnName(2)+":"+rsmd.getTableName(2)+":");
	rs.next();
	System.out.println(rs.getString("obras.nombre"));  <--- line 961

I get an exception at the last line:

Exception in thread "main" java.sql.SQLException: Column 'obras.nombre' not found.
	at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:929)
	at com.mysql.jdbc.ResultSet.getString(ResultSet.java:4977)
	at valesRV.Vales.main(Vales.java:961)
	
in fact, if I print 'rsmd.getTableName(2)', that should be 'obras', it is the empty string.

As I said previously, I don't if the error is in the Java Connector
or in mysql server since that query in the mysql 5 console works perfectly 
and with mysql-server-4.1 I do not have any problems. 

How to repeat:
In the description I provide the example that fails.

Hi Luis and thanks for your problem report.
Can you please do the following:
  Try to repeat the problem with latest version of JDBC driver from http://dev.mysql.com/downloads/connector/j/3.1.html
  If problem persists, add dump of your data and table structurs and complete java project to this bug report so I can test it.
In any case post your progress here.

Sample of failing Java source code

Attachment: PrJDBC.java (text/x-java), 2.00 KiB.

Failing database. Dump made with mysqlhotcopy.

Attachment: vazmacon.tgz (application/x-tgz, text), 109.12 KiB.

I have installed the mysql-connector-java-3.1.13-bin.jar in JAVA_HOME/jre/lib/ext and the problem remains. I hope that there is no other older version of the connector interfering. 

Also I have just submitted a dump of the failing database and a program that shows the problem.

Luis, thanks for info provided! I was able o verify this behavior with data and test case provided.
MySQL connector/J 3.1.12 and 3.1.13
JDK 1.6
Win XP Pro SP2 

Synopsis: When prepared statement contais "ORDER BY" clause and *returning column is part of multi-field index* resultset and resultset metadata do not return second table name, just field name:
rs.fields[1].tableName = ""
rsmd.fields[1].tableName = ""
which causes an error to be thrown:
Exception in thread "main" java.sql.SQLException: Column 'obras.nombre' not found.
        at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:970)
        at com.mysql.jdbc.ResultSet.getString(ResultSet.java:4755)
If you drop multi-field index containing column from result(UNIQUE KEY `idxNombreCliente` (`nombre`,`cliente`)): alter table obras drop index idxNombreCliente; your example works:
run:
nombre:obras:
Naclares de Oca, 14. Pol. Las Mercedes
nombre:obras:
null

Mysqldump of data

Attachment: test.zip (application/x-zip-compressed, text), 55.40 KiB.

Changing category, as it's the server that's returning erroneus metadata.

Hello,
  After several months without trying the testing program, 
I have just tried it today a standard debian and it works. 

$ dpkg -l mysql-server-5.0
Desired=Unknown/Install/Remove/Purge/Hold
| Estado=No/Instalado/Config-files/Unpacked/Failed-config/Half-installed
|/ Err?=(none)/Hold/Reinst-required/X=both-problems (Status,Err: mayúsc.=malo)
||/ Nombre                 Versión                Descripción
+++-======================-======================-============================================================
ii  mysql-server-5.0       5.0.32-7etch1          mysql database server binaries

Luis, thanks for your feedback and your interest in MySQL. Glad things are fixed in 5.0.