MySQL Bugs: #13212: boolean execute methods return false for empty-set results from SELECT queries

Bug #13212	boolean execute methods return false for empty-set results from SELECT queries
Submitted:	15 Sep 2005 4:06	Modified:	15 Sep 2005 13:33
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.10	OS:	JDK 1.4
Assigned to:		CPU Architecture:	Any

Description:
The 'boolean execute' methods of java.sql.Statement and java.sql.Prepared.Statement objects return 'true' for empty result sets. It should be 'false'.

How to repeat:
String sqlquery = "SELECT * FROM customer WHERE customer_id=17;";
// Suppose this query returns an empty set.

Connection con = DAO.getConn();
// some connection pool

Statement stmt = con.createStatement();

boolean rec_exists = stmt.execute(sqlquery);

if (rec_exists) {
   System.err.println("Bug!")
   } else {
      System.out.println("No Bug!")
      }

I've tried to repeat this with the following testcase, and the JDBC driver behaves as expected:

                assertTrue(this.stmt.execute("SELECT 1 LIMIT 0"));
		createTable("testBug13212", "(field1 int)");
		assertTrue(this.stmt.execute("SELECT field1 FROM testBug13212"));

Are you running in an environment where these connections might be wrapped by some other implementation?

If your code runs without errors you already have repeated the bug:

    assertTrue(this.stmt.execute("SELECT field1 FROM testBug13212"));

The result of this statement is an empty set. It does return 'true' but should return 'false'.

Try:

    assertFalse(this.stmt.execute("SELECT field1 FROM testBug13212"));

I think you're confused about what Statement.execute() is supposed to return.

Statement.execute() returns _true_ if a result set, regardless of size has been returned. It returns false for DML and non-result-set-producing statements. From the javadocs:

"true if the first result is a ResultSet object; false if it is an update count or there are no results"

While confusing, I have been in discussions w/ Sun where it has been made clear that "no results" doesn't mean _no_ rows returned, it's for statements that don't return a result set of any amount of rows, or an update count.

You check for empty result sets using the standard navigational methods on ResultSet (i.e. next()).

Hello Mark,

thank you for your clarification. Sorry for congesting the bug database with stuff that doesn't belong there.

Keep up the good work!

Regards,

Christian