MySQL Bugs: #94533: getObject for boxed primitive types doesn't return null for NULL columns

Bug #94533	getObject for boxed primitive types doesn't return null for NULL columns
Submitted:	3 Mar 2019 12:58	Modified:	11 Jan 2020 17:08
Reporter:	Michal Rehacek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.15	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	getObject

Description:
ResultSetImpl.getObject( int/String, Class ) supports boxed primitive types (Long, Integer, Double, Float, Boolean ). If getObject() is used on database values that are NULL, JDBC getObject() does not return null, but a non-NULL object representing 0 or false. getObject() should return null in that case.

How to repeat:
First, create a table with a NULLable column and insert a row with a NULL value. Example shows this for BIGINT, but the same problem happens with other types - all that can be converted to Float, Double, Integer, Long, Boolean in Java.

----
CREATE TABLE test ( volume BIGINT );
INSERT INTO test VALUES( NULL );
----

Java code:
----
String sql = "SELECT volume FROM test WHERE volume IS NULL LIMIT 1";
Statement s = connection.connection.createStatement();
ResultSet r = s.executeQuery( sql );
r.next();
Long l = r.getObject( "volume", Long.class );
----

Note that l will be a non-null instance of Long with a value equal to 0. It should be null.

The same problem happens for getObject() calls with Integer.class, Float.class, Double.class, Boolean.class. The same problem happens for getObject() taking an integer column index and String column name.

Suggested fix:
ResultSetImpl contains code similar to this (example taken for Long again), line cca 1390:

----
            } else if (type.equals(Long.class) || type.equals(Long.TYPE)) {
                return (T) Long.valueOf(getLong(columnIndex));
----

Something like this would work:

----
            } else if (type.equals(Long.class) || type.equals(Long.TYPE)) {
                long value = getLong(columnIndex);
                if ( wasNull() ) return null;
                else return (T) Long.valueOf( value );
----

and similarly for the other boxed types

Hi Michal,
Thanks for the report. Verified as described.

Michal,

I'd say it's not a bug. JDBC API requires this for getLong(x): "if the value is SQL NULL, the value returned is 0". And I can't find any note in JDBC specification that getObject(x, Long.class) should have a different behaviour, in case of NULL value, than getLong(x).

Hello Alexander,

let me explain why I think getObject( xxx, Long/Integer/etc.class ) should be returning null. It's a combination of two paragraphs in the JDBC standard that make me believe so:

---
15.2.3.3 - Retrieving NULL values

Quote: ... Column values that map to Java Object types are returned as a Java null; those that map to numeric types are returned as 0 ...

---

Table B-3 JDBC Types Mapped to java Object Types

Quote: ResultSet.getObject and CallableStatement.getObject use the mapping
shown in TABLE B-3 for standard mappings

and the actual table shows that BIGINT maps to Long (with capital L, i.e. the Object long type)
---

The first reference says that NULL column values mapped to Java Object should be returned as Java null. And the second table says that Long (capital L) is considered an object type. Similarly for the other object types that box the primitives.

Hi, Michal!

You're right. Also, while getObject(int columnIndex, Class<T> type) behaviour is not described in API, other getObject() methods documentation says "If the value is an SQL NULL, the driver returns a Java null".

So, yes, we need to do the requested fix.