Description:
When using parameters with a prepared statement using an inner join, 0 rows are returned when performing a select. Simply by changing the parameters set to statement values, the result size is 1. (on a side note, simply changing the inner to join to outer join returns the correct number of rows) This is using the connectorJ-3.1.8.
How to repeat:
1. Create example database
---------------------------------------------------------
Create table if not exists cat (
id Bigint NOT NULL AUTO_INCREMENT,
color Varchar(25) ,
type Varchar(50) NOT NULL ,
Index AI_id (id),
Primary Key (id)) TYPE = InnoDB;
Create table if not exists domestic_cat (
name Varchar(20) NOT NULL ,
id Bigint NOT NULL ,
Primary Key (id)) TYPE = InnoDB;
Alter table domestic_cat add Index IX_Relationship1 (id);
Alter table domestic_cat add Foreign Key (id) references cat (id);
2. Execute the following Java code:
---------------------------------------
Connection con = getConnection();
con.createStatement().executeUpdate("insert into cat (color, type, id) values ('blue', 'fat', 1)");
con.createStatement().executeUpdate("insert into domestic_cat (name, id) values ('bob', 1)");
PreparedStatement ps = con.prepareStatement(
"select domesticca0_.id as id0_, domesticca0_.name as name1_0_, domesticca0__1_.color as color0_0_, domesticca0__1_.type as type0_0_ " +
"from domestic_cat domesticca0_ " +
"inner join cat domesticca0__1_ on domesticca0_.id=domesticca0__1_.id " +
"where domesticca0_.id=?");
ps.setLong(1, 1);
ResultSet rs = ps.executeQuery();
System.out.println("rs.next() with parameters: " + rs.next());
rs.close();
ps.close();
ps = con.prepareStatement(
"select domesticca0_.id as id0_, domesticca0_.name as name1_0_, domesticca0__1_.color as color0_0_, domesticca0__1_.type as type0_0_ " +
"from domestic_cat domesticca0_ " +
"inner join cat domesticca0__1_ on domesticca0_.id=domesticca0__1_.id " +
"where domesticca0_.id=1");
rs = ps.executeQuery();
System.out.println("rs.next() without parameters: " + rs.next());
rs.close();
ps.close();
3. Examine console output
-------------------------------------
rs.next() with parameters: false
rs.next() without parameters: true
As you can see, both of the queries should have results however, the one that takes parameters does not have any results.
Suggested fix:
I don't know how to fix this.
Description: When using parameters with a prepared statement using an inner join, 0 rows are returned when performing a select. Simply by changing the parameters set to statement values, the result size is 1. (on a side note, simply changing the inner to join to outer join returns the correct number of rows) This is using the connectorJ-3.1.8. How to repeat: 1. Create example database --------------------------------------------------------- Create table if not exists cat ( id Bigint NOT NULL AUTO_INCREMENT, color Varchar(25) , type Varchar(50) NOT NULL , Index AI_id (id), Primary Key (id)) TYPE = InnoDB; Create table if not exists domestic_cat ( name Varchar(20) NOT NULL , id Bigint NOT NULL , Primary Key (id)) TYPE = InnoDB; Alter table domestic_cat add Index IX_Relationship1 (id); Alter table domestic_cat add Foreign Key (id) references cat (id); 2. Execute the following Java code: --------------------------------------- Connection con = getConnection(); con.createStatement().executeUpdate("insert into cat (color, type, id) values ('blue', 'fat', 1)"); con.createStatement().executeUpdate("insert into domestic_cat (name, id) values ('bob', 1)"); PreparedStatement ps = con.prepareStatement( "select domesticca0_.id as id0_, domesticca0_.name as name1_0_, domesticca0__1_.color as color0_0_, domesticca0__1_.type as type0_0_ " + "from domestic_cat domesticca0_ " + "inner join cat domesticca0__1_ on domesticca0_.id=domesticca0__1_.id " + "where domesticca0_.id=?"); ps.setLong(1, 1); ResultSet rs = ps.executeQuery(); System.out.println("rs.next() with parameters: " + rs.next()); rs.close(); ps.close(); ps = con.prepareStatement( "select domesticca0_.id as id0_, domesticca0_.name as name1_0_, domesticca0__1_.color as color0_0_, domesticca0__1_.type as type0_0_ " + "from domestic_cat domesticca0_ " + "inner join cat domesticca0__1_ on domesticca0_.id=domesticca0__1_.id " + "where domesticca0_.id=1"); rs = ps.executeQuery(); System.out.println("rs.next() without parameters: " + rs.next()); rs.close(); ps.close(); 3. Examine console output ------------------------------------- rs.next() with parameters: false rs.next() without parameters: true As you can see, both of the queries should have results however, the one that takes parameters does not have any results. Suggested fix: I don't know how to fix this.