MySQL Bugs: #3334: Different behavior of complex query when execute by different method

Bug #3334	Different behavior of complex query when execute by different method
Submitted:	30 Mar 2004 5:04	Modified:	28 Mar 2014 13:52
Reporter:	Konstantin Kirenko	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.1-alpha	OS:	Windows (WinXP)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
Environment:
MySQL 4.1.1, InnoDB engine
Connector/J 3.1.1-alpha
Sun JDK 1.4.2
Windows XP Pro

I have for tables, named 'a', 'b', 'c' and 'd', connect one to another by foreign keys. All tables have primary key named id, type varchar(50). Reference diagram looks like

a <- b <- c <- d

I try to execute this query

select a.id
from a join b on a.id = b.id
     join c on c.id = b.id
     join d on c.id = d.id
     and d.id='b90b39-f773079549-9c78b0b89a412814b469497b3f3fd9ba'

I have one row with this id in d, c, b, and a. I have many other rows in c, b, a. When I execute this query from MySQL console, I get expected result - 1 row. When I execute this query from Java in through PreparedStatement, I get many rows as a result.

String sql = ...; // query described above
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next())
{
    System.out.println("One more row");
}
rs.close();
pstmt.close();

I think problem is in using ServerPreparedStatement by MySQL driver. I can't turn off using ServerPreparedStatement, because need streaming database access in some circumstances (some table are quite large).

How to repeat:
Create four tables as described above, run specified query from console and through JDBC driver.

There is not enough information in your bug report to repeat this bug.

You should also consider trying the latest MySQL-4.1 from BitKeeper, as prepared statements in 4.1.1 had quite a few issues (this looking similar to others which caused various bits of the query to be improperly optimized away).

I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.