MySQL Bugs: #11628: PreparedStatement returns wrong result set

Bug #11628	PreparedStatement returns wrong result set
Submitted:	29 Jun 2005 6:00	Modified:	14 Jul 2005 7:37
Reporter:	Irina Tchernouchina	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.7, 3.1.10	OS:	Windows (Microsoft Windows XP,)
Assigned to:		CPU Architecture:	Any

Description:
PreparedStatement.executeQuery() returns very odd result set: (please look below on how to reproduce) we have a set about 2 thousands records returned when we expect to get 3 records only. The records in the returned record set are duplicated about 5 times. We have no idea of what happens, and in the case of CallableStatement used, everything works OK.

Our own code was checked multiple times and we are sure that there're the same SQL query that is for PreparedStatement and the same parameters are set.

We use Bea Weblogic 8.1 on our server, that, in turn, uses MySql..
So it's ServerPreparedStatement is invoked in the jdbc driver, as far as I can detect. And the error should be somewhere there.
We are now to stop using prepared statements at all....

How to repeat:
We have a Stateless Bean method that is called for our bisuness logic, and it, in turn, calls the code like that: (it's not the real code, just the way)
Note: first time the method is called the right results are returned... And maybe second time... But then(((

Connection conn  = null;
try {
    conn = getDataSource().getConnection();
} catch (SQLException e) {
    throw new DBException(e);
}

PreparedStatement ps = conn.prepareStatement("...(a query)...");

// ps fields are set

ps.executeQuery();     // HERE WE GET our BIG result set

try {
} finally {
// result set, statement, connection is closed
}

The SQL that is executed is like that:

SELECT T0.field1 AS F0, ..., T0.field14 AS F14 

FROM Table1 T0
LEFT OUTER JOIN Table2 T1 ON T0.fk1=T1.id
LEFT OUTER JOIN Table3 T2 ON T1.fk2=T2.id
LEFT OUTER JOIN Table4 T3 ON T1.fk3=T3.id

WHERE ( ( ( ( T0.field1 = ? ) AND (  1=1) ) AND ( T0.field2 = ?) ) AND ( T3.dateTime >= ?) ) AND ( T3.dateTime <= ?)

and parameters are like that: [false, false, 2005-06-20 00:00:00.0, 2005-06-29 00:01:00.0]

We had checked parameters setting several times, and it is OK.

Do you get the results you expect if you configure server-side prepared statements to not be used by setting "useServerPrepStmts=false" in your URL?

(if so, it's a server bug).

Yes, with "useServerPrepStmts=false" everything works fine:)

what mysql version do you use?

4.1.10-nt

(in order to reproduce this bug) could you please attach table definitions and (if possible) some example data.

I've just added the query and involved tables dump.

Sorry I was not able to reproduce the bug on 4.1.13. My result is :

1008 0 0 1.0 null null 2801 102123 5797 1 a 5239 0 0 1 null
1009 0 0 1.0 null null 2801 102124 5796 1 a 5238 0 0 1 null
1501 0 0 1.0 null null 2202 103910 5853 1 1-omr 5303 0 0 1 null

Could you please run my test case on your computer? I attached the test case in "Files".

Test case

Attachment: test.java (text/java), 2.33 KiB.

Hi Vasily.

Sorry for delay. We proceed the test you sent and it works OK.

The problem is only under Weblogic 8.1. When JDBC calls are done from stateless session bean, we get our error. We are not sure whether it's Weblogic or MySQL error and unfortunately we have no time for this problem now. We just refused to use prepared statements at the moment.

Sorry...As far as I can understand it is not bug of JDBC.