MySQL Bugs: #5032: ResultSet as result of PreparedStatement doesn't deliver Boolean

Bug #5032	ResultSet as result of PreparedStatement doesn't deliver Boolean
Submitted:	13 Aug 2004 14:01	Modified:	13 Aug 2004 23:51
Reporter:	M Wensink	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	Snapshot of august 13	OS:	Windows (W2K)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
After execution of executeQuery() on a PreparedStatement, the ResultSet does not contain a value of type Boolean. Instead an Integer value is returned.

How to repeat:
Make a table containing a Boolean. For example:

CREATE TABLE `user_tbl` (
  `User_ID` INTEGER,
  `User_locked` BOOLEAN
);

Execute the query "SELECT * FROM User_tbl WHERE User_ID = ?" using a PreparedStatement via executeQuery();
Then execute the following code:
Map m = resultSet.getRows()[0];
Boolean b = ((Boolean) m.get ("User_locked")).booleanValue());

The result is: ClassCastException: Java.lang.Integer

Suggested fix:
None

If your code is calling getObject() on the result set, it won't work, as 'BOOLEAN' is currently an 'alias' for TINYINT(1) in MySQL (i.e. there is no true 'boolean' type in MySQL). If you call getBoolean() on the result set, it will work, as the driver does a type conversion.

You can see why this happens if you look at the columns that _actually_ get created for your table:

mysql> show columns from user_tbl\G
*************************** 1. row ***************************
  Field: User_ID
   Type: int(11)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: User_locked
   Type: tinyint(1)
   Null: YES
    Key:
Default: NULL
  Extra:
2 rows in set (0.00 sec)

mysql>

Why does it work correctly if the PreparedStatement is replaced by an ordinary Statement?

You've hidden the actual JDBC call behind your class structure, so until I see _how_ you're calling ResultSet.getFoo(), I couldn't possibly tell you :(

My code is like this:

Result executeQuery ( ) throws SQLException
{
   PreparedStatement pstmt = null;
   Statement stmt = null;
   Result result = null;
   ResultSet rs = null;
   try
   {  if (values != null && values.size() > 0)
      {  // Use a PreparedStatement and set all values:
         pstmt = conn.prepareStatement (sqlValue);
         setValues (pstmt, values);
         rs = pstmt.executeQuery();
      }
      else
      {  // Use a regular Statement:
          stmt = conn.createStatement();
          rs = stmt.executeQuery (sqlValue);
      }
      result = ResultSupport.toResult (rs);
   }
   finally
   { .......  
   }
   return result;
}

Map [ ] getUsers (Connection conn) throws SQLException
{
   sqlValue = "SELECT * FROM User_tbl";
   values = null;
   Result result = executeQuery();
   if (result == null || result.getRowCount() == 0)
   {  // User not found
      return null;
   }
   return result.getRows();
}

Thank you for pasting your code in the report, but unfortunately it still doesn't show which getter method you're calling on ResultSet that shows the issue.

Does your code call ResultSet.getBoolean() or ResultSet.getObject() ?

Okay, found the issue. The driver _does_ detect the 'special' case of TINYINT(1), and considers it a 'BIT' type, however the test was not done in the case of binary-encoded result sets that are returned from server-side prepared statements.

This fix will be in tonight's nightly snapshot after 00:00 GMT, see http://downloads.mysql.com/snapshots.php

OK, this solves the problem.