Bug #24700 PreparedStatement fails to return result
Submitted: 29 Nov 2006 17:23 Modified: 4 Dec 2006 14:44
Reporter: Sean Liang Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.0.4 OS:
Assigned to: CPU Architecture:Any

[29 Nov 2006 17:23] Sean Liang
Description:
If there is a KEY combined by multiple fields, the PreparedStatement query will fail to get the result set, as described below.

How to repeat:
1. Create a table with a (UNIQUE) KEY combined by multiple fields
CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `value1` int(11) unsigned NOT NULL default '0',
  `value2` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `value1` (`value1`,`value2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Insert a record
INSERT INTO `test` (`id`, `value1`, `value2`) VALUES 
  (1, 1, 3);

3. Compile and execute the Test.java, the result would be "No result" and "Has result"
import java.sql.*;

public class Test {

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection conn = DriverManager.getConnection("jdbc:mysql:///1cm","root","");
        PreparedStatement ps = conn.prepareStatement("SELECT id,value1,value2 FROM test WHERE (( ( value1 = ? ) and ( value2 = ? ) ) or ( ( value1 = ? ) and ( value2 = ?) ));");
        ps.setInt(1, 3);
        ps.setInt(2, 1);
        ps.setInt(3, 1);
        ps.setInt(4, 3);
        ResultSet rset = ps.executeQuery();
        System.out.println(rset.next() ? "Has result" : "No result");
        rset.close();
        Statement stmt = conn.createStatement();
        rset = stmt.executeQuery("SELECT id,value1,value2 FROM test WHERE (( ( value1 = 3) and ( value2 = 1) ) or ( ( value1 = 1) and ( value2 = 3) ));");
        System.out.println(rset.next() ? "Has result" : "No result");
        rset.close();
        ps.close();
        conn.close();
    }

}
[4 Dec 2006 14:44] Tonci Grgin
Hi Sean and thanks for your problem report. I was unable to verify it on latest connector/J sources.

Connected to 5.0.27-log
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

id=1	value1=1	value2=3	

Time: 0,531

OK (1 test)
[4 Dec 2006 14:45] Tonci Grgin
Test case

Attachment: TestBug24700.java (text/x-java), 1.79 KiB.