MySQL Bugs: #85941: wasNull not set after getBytes is called

Bug #85941	wasNull not set after getBytes is called
Submitted:	14 Apr 2017 3:31	Modified:	25 Jun 2018 22:11
Reporter:	Ephraim Khantsis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	6.0.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
the method wasNull on a ResultSetImpl calls wasNull on the implementation of AbstractResultsetRow, however AbstractResultsetRow only returns a meaningful response on wasNull if getNull was at some point called, or AbstractResultsetRow#getValueFromBytes is called. 

When the ResultSetImpl method doesn't call getValue or getNull, but only getBytes, 
This contradicts the ResultSet contract from java.sql.ResultSet#wasNull, which demands a meaningful response only any get* is called, not only getNull

How to repeat:
0. compute a query which returns [null, 1]
1. call resultSet.getString(1)  (this will call AbstractResultsetRow#getValueFromBytes and set wasNull to true.
2. Print resultSet.wasNull -> (true)
3. call resultSet.getBytes(2)
4. Print resultSet.wasNull -> true <--- WRONG

Suggested fix:
public byte[] getBytes(int columnIndex) throws SQLException {
   checkRowPos();
   checkColumnBounds(columnIndex);
   if (this.thisRow.getNull(columnIndex - 1)) {
      return null;
   }
   return this.thisRow.getBytes(columnIndex - 1);
}

add the getNull check.

latest affected version is 6.0.6

in fact, 5.1 branch doesn't have this issue so it's regression

Hello Ephraim Khantsis,

Thank you for the bug report.
I tried to reproduce the issue at my end using MySQL Connector / J 6.0.6 and MySQL 5.7.18 but not seeing any issues in getBytes with values of Null, 1.
Could you please provide repeatable test case (exact steps, sample test case, full stack trace  etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

Hello Ephraim Khantsis,

Thank you for the bug report.
Verified this behavior on MySQL Connector / J 6.0.6.

Thanks,
Chiranjeevi.

This bug can cause data loss when using Hibernate. Hibernate calls rs.wasNull() after reading every column, including byte[] type columns, and replaces the value with null if it returns true. 

This bug only affects result sets that use TextBufferRow, not ByteArrayRow. An easy way to make that happen is to add a long string column:

try (PreparedStatement ps = conn.prepareStatement("SELECT NULL, x'1234', REPEAT('foo', 1000)");
     ResultSet rs = ps.executeQuery()) {
    while (rs.next()) {
        System.out.println("Column 1 value: " + rs.getInt(1));
        System.out.println("Column 1 was null: " + rs.wasNull());
        System.out.println("Column 2 value: " + Arrays.toString(rs.getBytes(2)));
        System.out.println("Column 2 was null: " + rs.wasNull());
    }
}

This prints

Column 1 value: 0
Column 1 was null: true
Column 2 value: [18, 52]
Column 2 was null: true

We have seen the same issue with hibernate 5 and mysql-connector 6 and 8

Same problem using Java8, MySQL 8.0.11 and Connector/J 8.0.11
Impossible to upgrade to this version on my system that have uuid columns as BINARY and some other long char fields.
Sometimes field is not retrieved by Hibernate because rs.wasNull() is incorrectly true.

Posted by developer:
 
Added the following entry to the Connector/J 8.0.12 changelog:

"A wasNull() call on a Resulset did not return the proper value unless AbstractResultsetRow.getNull() or AbstractResultsetRow.getValueFromByte() was called before. This cost data loss when Connector/J was used with frameworks like Hibernate, which rely on wasNull() calls to properly retrieve data. With this fix, wasNull() returns a correct value as long as some getter method has been called before on the ResultSet."