MySQL Bugs: #17450: ResultSet.getBoolean() doesn't always set the wasNullFlag

Bug #17450	ResultSet.getBoolean() doesn't always set the wasNullFlag
Submitted:	16 Feb 2006 11:29	Modified:	5 Apr 2006 20:13
Reporter:	Karel Vervaeke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.11, 3.1.12	OS:	Windows (Windows 2k)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
given this table:
CREATE TABLE  `test`.`booleanbug` (
  `FOO` varchar(100) default NULL,
  `BAR` char(1) NOT NULL
)

given the prepared statement pst:
select * from booleanbug where foo is null

given the resultset rs:
rs = pst.executeQuery();

Perform the following steps
rs.getString(1) // returns null, sets the wasNullFlag to true
rs.getBoolean(2) // returns true, does NOT reset the wasNullFlag.

How to repeat:

Compile and run the following test; it requires mysql-connector-j and junit in the classpath.

------------------------------------------------- MysqlTest.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import junit.framework.TestCase;

public class MysqlTest extends TestCase {

	Connection con;

	protected void setUp() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		con=DriverManager.getConnection("jdbc:mysql:///test","test","test");
		Statement st = con.createStatement();
		st.execute("drop table if exists booleanbug");
		st.execute("create table booleanbug (FOO VARCHAR(100), BAR CHAR NOT NULL)");
		st.execute("delete from booleanbug where 1=1");
		st.execute("insert into booleanbug (foo,bar) values ('foo',true)");
		st.execute("insert into booleanbug (foo,bar) values (null,true)");
	}
	
	protected void tearDown() throws Exception {
		con.close();
	}

	
	public void testPreparedStatement1() throws Exception {
		PreparedStatement st = con.prepareStatement("select * from booleanbug where foo=?");
		st.setString(1,"foo");
		testResult(st.executeQuery());
	}
	
	public void testPreparedStatement2() throws Exception {
		PreparedStatement st = con.prepareStatement("select * from booleanbug where foo is null");
		testResult(st.executeQuery());
	}
	
	public void testStatement1() throws Exception {
		Statement st = con.createStatement();
		testResult(st.executeQuery("select * from booleanbug where foo='foo'"));
	}
	
	public void testStatement2() throws Exception {
		Statement st = con.createStatement();
		testResult(st.executeQuery("select * from booleanbug where foo is null"));
	}
	
	private void testResult(ResultSet rs) throws Exception {
		rs.next();
		rs.getString(1);
		boolean bar = rs.getBoolean(2);
		System.out.println(bar + "/" + rs.wasNull());
		assertEquals("field 2 should be true", true, bar);
		assertFalse("wasNull should return false", rs.wasNull());
	}
}

Suggested fix:
none yet.  workarounds are easy to come up with but depend on your situation;
The best solution imho is to use a more appropriate datatype for the 'boolean' column, anything unrelated to strings, such as BOOLEAN, BIT or TINYINT(1))

The section "Perform the following steps" in the description isn't very clear. Here's a better version:

Perform the following steps:
first do "insert into booleanbug (FOO,BAR) values (null,true)"
then:
ResultSet rs = pst.executeQuery()
rs.getString(1); System.out.println(rs.wasNull()); // prints true (as expected)
rs.getBoolean(2); System.out.println(rs.wasNull()); // prints true (which is wrong)

Duplicate of 4689, works with 3.1.12

Oops! does *not* work with 3.1.12 (sorry!)

Thanks for your bug report. Verified on XP SP2 as described by reporter.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;

public class Main
{
    Connection con;

    public void setUp() throws Exception
    {
        Class.forName("com.mysql.jdbc.Driver");
        con=DriverManager.getConnection("jdbc:mysql://munja/test","root","");
        Statement st = con.createStatement();
        st.execute("drop table if exists booleanbug");
	st.execute("create table booleanbug (FOO VARCHAR(100), BAR CHAR NOT NULL)");
	st.execute("delete from booleanbug where 1=1");
	st.execute("insert into booleanbug (foo,bar) values ('foo',true)");
	st.execute("insert into booleanbug (foo,bar) values (null,true)");
    }

    public void tearDown() throws Exception
    {
        con.close();
    }

   public void testPreparedStatement1() throws Exception { 
       PreparedStatement st = con.prepareStatement("select * from booleanbug where foo=?"); 
       st.setString(1,"foo"); 
       testResult(st.executeQuery()); 
   } 
   
   public void testPreparedStatement2() throws Exception { 
       PreparedStatement st = con.prepareStatement("select * from booleanbug where foo is null"); 
       testResult(st.executeQuery()); 
   } 
   
   public void testStatement1() throws Exception { 
       Statement st = con.createStatement(); 
       testResult(st.executeQuery("select * from booleanbug where foo='foo'")); 
   } 
   
   public void testStatement2() throws Exception { 
       Statement st = con.createStatement(); 
       testResult(st.executeQuery("select * from booleanbug where foo is null")); 
   } 
   
   private void testResult(ResultSet rs) throws Exception { 
       rs.next(); 
       rs.getString(1); 
       boolean bar = rs.getBoolean(2); 
       System.out.println(bar + "/" + rs.wasNull()); 
   }    
   
   //constructor for class
    public Main()
    {
        try
        {
            setUp();
            testPreparedStatement1();
            testPreparedStatement2();
            testStatement1();
            testStatement2();
        }
        catch(Exception e1)
        {
            e1.printStackTrace();
        }
        finally
        {
            try
            {
                tearDown();
            }
            catch(Throwable t){}
        }
    }
    
    //main function 
    public static void main(String args[])
    {
        new Main();
    }
}

Console output:
run:
true/false
true/true
true/false
true/false

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4427