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

[30 Mar 2004 5:04] Konstantin Kirenko
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.
[30 Mar 2004 7:14] Mark Matthews
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).
[28 Mar 2014 13:52] Alexander Soklakov
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.