MySQL Bugs: #3493: Select query in JDBC prepared statement gives empty result

Bug #3493	Select query in JDBC prepared statement gives empty result
Submitted:	18 Apr 2004 12:38	Modified:	27 Apr 2004 10:29
Reporter:	Niklas Lindholm	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.1-alpha	OS:	Linux (Linux (2.4.20))
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
When executing a query with several joins in some cases I run into the problem that I don't get any results from the query. This only happens when using prepared statements in JDBC.

The server version I am running is 4.1.1.

How to repeat:
This code:

      con = getConnection();
      ps = con.prepareStatement
          ("SELECT COUNT(*) " +
           "FROM Questions, QuestionsCategories, Categories " +
           "WHERE Questions.QuestionId = QuestionsCategories.QuestionId " +
           "AND QuestionsCategories.CategoryId = Categories.CategoryId " +
           "AND Categories.SiteId = ? and Questions.Approved = FALSE");
      System.out.println("*** siteId = " + siteId);
      ps.setInt(1, siteId);
      rs = ps.executeQuery();

Gives the result 0, but it should have given 5.

If I change it into using Statement (instead of PreparedStatement) I get the expected result:

      con = getConnection();
      st = con.createStatement();
      rs = st.executeQuery
          ("SELECT COUNT(*) " +
           "FROM Questions, QuestionsCategories, Categories " +
           "WHERE Questions.QuestionId = QuestionsCategories.QuestionId " +
           "AND QuestionsCategories.CategoryId = Categories.CategoryId " +
           "AND Categories.SiteId = " + siteId + " AND Questions.Approved = FALSE");

I also get the correct result when running the query in the mysql terminal client.

If I change the "COUNT(*)" to an actual field I get in the first case en empty result while I get 5 rows in the second case.

There were quite a few optimizer bugs with MySQL-4.1.1 and prepared statements. Please test with a BK build of MySQL-4.1 (or wait for 4.1.2 to be released). You will also need to use a nightly snapshot of Connector/J 3.1.x to connect to newer versions of MySQL-4.1. Please see http://downloads.mysql.com/snapshots.php

Any idea on when 4.1.2 will be available..?

4.1.2 is being built as we speak.

Finally I have tried it with 4.1.2 and it seems to solve the problem. It's a little hard to verify though since I got some other problems. (See bug 3911)

/Niklas