ResultSet `getXXX` returns default value instead of `null` for nullable fields

[o-shevchenko]

ResultSet getXXX returns default value instead of null for nullable fields.

BigQueryResultImpl implementation of getBoolean, getDouble, getLong should return null the same as others methods

Schema

Data (See row 4)

private fun ResultSet.getFieldValue(field: Field): Any? {
    val fieldName = field.name
    return  when (field.type.standardType) {
            StandardSQLTypeName.BOOL -> getBoolean(fieldName)
            StandardSQLTypeName.INT64 -> getLong(fieldName)
            StandardSQLTypeName.FLOAT64 -> getDouble(fieldName)
            StandardSQLTypeName.NUMERIC, StandardSQLTypeName.BIGNUMERIC -> getBigDecimal(fieldName)
            StandardSQLTypeName.STRING -> getString(fieldName)
            StandardSQLTypeName.BYTES -> getBytes(fieldName)
            StandardSQLTypeName.TIMESTAMP -> getTimestamp(fieldName)?.toInstant()
            StandardSQLTypeName.DATE -> getDate(fieldName)?.toLocalDate()?.atStartOfDay()?.toInstant(ZoneOffset.UTC)
            StandardSQLTypeName.GEOGRAPHY -> getString(fieldName)?.toByteArray(StandardCharsets.UTF_8)
            StandardSQLTypeName.JSON -> getString(fieldName)
            StandardSQLTypeName.INTERVAL -> getObject(fieldName)
            StandardSQLTypeName.RANGE -> getObject(fieldName)
            else -> getObject(fieldName)
        }
}

[PhongChuong]

Hi @o-shevchenko ,
We are following the specification of java.sql.ResultSet, which for getDouble states: Returns: the column value; if the value is SQL NULL, the value returned is 0. Ditto for getBoolean and getLong.

[o-shevchenko]

Thanks, @PhongChuong for the answer.
That's an interesting and unexpected behavior.
Not sure how it's possible to figure out if we really have null or 0 for such a field, which is a big difference for most use cases.

[PhongChuong]

The specification of java.sql.ResultSet has a wasNull() method: Returns: true if the last column value read was SQL NULL and false otherwise. So this can be used to check for null after reading the value. E.g.,

long value = rs.getLong("longFieldName");
if (rs.wasNull("longFieldName")) {
  // Do something
}

Unfortunately, it seems like this has not been implemented in BigQueryResultSet. Let me know if you want to change this into a feature request. It should fairly straight forward to add.

[o-shevchenko]

Yes, I tried to use wasNull() but it returns Method threw 'java.lang.RuntimeException' exception: Not implemented.
So, yes, probably we should proceed with your suggestion and change this into a feature request.