MySQL Bugs: #2359: Null numeric types in binary result sets not returned correctly.

Bug #2359	Null numeric types in binary result sets not returned correctly.
Submitted:	12 Jan 2004 10:59	Modified:	13 Jan 2004 15:16
Reporter:	Mark Matthews	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	3.1.x	OS:	Java
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
I'm getting the following NullPointerException (I wasn't getting it with
older versions of connectorJ)
 
java.lang.NullPointerException
        at com.mysql.jdbc.ResultSet.getNativeInt(ResultSet.java:4041)
        at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:1313)

 
Code tries to get an integer from the resultset
rs.getInt( 1 );
 
 
I'm using the win2k platform.
 
Thanks for your help,
 
Kate

How to repeat:
(test case generated by Mark Matthews, and committed into regression test suite):

public void testBugNNN() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBugNNN");
			this.stmt.executeUpdate("CREATE TABLE testBugNNN (field1 INT)");
			this.stmt.executeUpdate("INSERT INTO testBugNNN VALUES (null)");
			
			this.pstmt = this.conn.prepareStatement("SELECT field1 FROM testBugNNN");
			this.rs = this.pstmt.executeQuery();
			
			assertTrue(this.rs.next());
			
			assertTrue(this.rs.getByte(1) == 0);
			assertTrue(this.rs.wasNull());
			
			assertTrue(this.rs.getShort(1) == 0);
			assertTrue(this.rs.wasNull());
						
			assertTrue(this.rs.getInt(1) == 0);
			assertTrue(this.rs.wasNull());
	
			assertTrue(this.rs.getLong(1) == 0);
			assertTrue(this.rs.wasNull());
			
			assertTrue(this.rs.getFloat(1) == 0);
			assertTrue(this.rs.wasNull());
			
			assertTrue(this.rs.getDouble(1) == 0);
			assertTrue(this.rs.wasNull());
			
			assertTrue(this.rs.getBigDecimal(1) == null);
			assertTrue(this.rs.wasNull());

		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBugNNN");
			
			this.rs.close();
			this.pstmt.close();
		}

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Please see a nightly snapshot of 3.1.x after 00:00 GMT on Jan. 13th to get the fix.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Please use the test case below.  The one you sent me did not reproduce the problem.  field1 in the table has a value of 1.  When I select the max of field1 I expect the value to be non-zero, but the value returned is zero.

try {
	stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359");
	stmt.executeUpdate("CREATE TABLE testBug2359 (field1 INT, field2 INT)");
	stmt.executeUpdate("INSERT INTO testBug2359 VALUES (1, NULL)");

	pstmt = conn.prepareStatement("SELECT max(field1) FROM testBug2359");
	rs = pstmt.executeQuery();

	assert(rs.next());

	assert(rs.getInt(1) != 0);
	assert(rs.wasNull());

	rs.close();

} finally {
	stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359");
	stmt.close();
	rs.close();
	pstmt.close();
}

version of mysql ConnectorJ is 3.1-nightly-20040113
version of mysql is 4.1.1a-alpha

Using the following test case against both 4.1.1 and 4.1.2, I can not reproduce your issue:

this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359_1");
			this.stmt.executeUpdate("CREATE TABLE testBug2359_1 (field1 INT, field2 INT)");
			this.stmt.executeUpdate("INSERT INTO testBug2359_1 VALUES (1, NULL)");

			this.pstmt = conn.prepareStatement("SELECT max(field1) FROM testBug2359_1");
			this.rs = this.pstmt.executeQuery();

			assertTrue(this.rs.next());

			assertTrue(this.rs.getInt(1) == 1);
			assertTrue(!this.rs.wasNull());

			this.rs.close();

Notice that the asserts were changed, the first equality one to be more concise (and correctly test for the value you're looking for), and the second assert, because the one you submitted couldn't logically have been correct, e.g. you can't have a non-null result (1) and have rs.wasNull() return true as well.

I was missing a variable, the table is of type innodb.  this test case reproduces the problem.  sorry about all the confusion.

try {
	stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359");
	stmt.executeUpdate("CREATE TABLE testBug2359 (id INT) TYPE = InnoDB");
	stmt.executeUpdate("INSERT INTO testBug2359 VALUES (1)");

	pstmt = conn.prepareStatement("SELECT max(id) FROM testBug2359");
	rs = pstmt.executeQuery();
	if(rs.next())
		assert(rs.getInt(1) != 0);
	rs.close();
} finally {
	stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359");
	stmt.close();
	rs.close();
	pstmt.close();
}

same problem here with getLong("colname") and getTimestamp("colname"): Throws nullpointer exception if the value is null.
I confirm it works with mysql-connector-java 3.0.17-ga but not with mysql-connector-java 3.1.11 or 3.1.6.
The problem seems to be limited to InnoDB tables and only if the value is null (works if 0)
     Nullpointerexception at: com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:908) (version 3.1.11)
Table layout:
mysql> show create table job\G
*************************** 1. row ***************************
       Table: job
Create Table: CREATE TABLE `job` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `lastmod` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `jobsource_id` bigint(20) unsigned default NULL,
  `assignedperson_id` bigint(20) unsigned default NULL,
  `description` longtext,
  `status` int(1) default '1',
  `deadline` datetime NOT NULL default '0000-00-00 00:00:00',
  `js_creatorperson_id` bigint(20) unsigned NOT NULL default '0',
  `js_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `js_type` int(5) default '0',
  `company_id` bigint(20) unsigned default NULL,
  `clientperson_id` bigint(20) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `jobsource_id` (`jobsource_id`),
  KEY `assignedperson_id` (`assignedperson_id`),
  KEY `js_creatorperson_id` (`js_creatorperson_id`),
  KEY `company_id` (`company_id`),
  KEY `clientperson_id` (`clientperson_id`),
  KEY `clientperson_id_2` (`clientperson_id`),
  CONSTRAINT `job_ibfk_1` FOREIGN KEY (`clientperson_id`) REFERENCES `person` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

company_id is null (yes, it shouldn't but that's a different problem altogether ;) ) and 
rs.getLong("job.company_id") fails with nullpointerexception.