Bug #92574 When converting from VARCHAR to Java Boolean, 'N' is not supported
Submitted: 26 Sep 2018 8:46 Modified: 3 Dec 2018 21:04
Reporter: Xinfeng Zhang Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0, 8.0.12 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[26 Sep 2018 8:46] Xinfeng Zhang
Description:
In MySQL connector/J 5.1 and previous versions. If you store 'N' 'n' '0' or 'false' in a VARCHAR, mysql connector can convert it into False in Java.
But in MySQL connector/J 8, it can't and will throw exception.
Example:

Caused by: org.springframework.dao.DataIntegrityViolationException: Error attempting to get column '   XXXXXXXXXXXXXX    ' from result set.  Cause: java.sql.SQLDataException: Cannot determine value type from string 'N'
; SQL []; Cannot determine value type from string 'N'; nested exception is java.sql.SQLDataException: Cannot determine value type from string 'N'

.....

Caused by: java.sql.SQLDataException: Cannot determine value type from string 'N'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:114)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLExce...

How to repeat:
Create a table with a VARCHAR(1) column. Set it's value to 'N'. With connector/J 5.1, it can parse it into a False. But with connector/J 8.0, the program will throw an exception.

Suggested fix:
Compatible with Connector/J 5.1.  Regard 'n'/'N'/'0'/null/'false' as a Java False.
[26 Sep 2018 9:40] Umesh Shastry
Hello Xinfeng,

Thank you for the report.

regards,
Umesh
[3 Dec 2018 21:04] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 8.0.14 changelog:

"The method ResultSet.getBoolean() now returns a false when the the designated coulmn is of data type CHAR or VARCHAR and contains an “N” or “n”. This makes Connector/J 8.0 behaves like Connector/5.1 when it comes to converting strings to booleans."
[26 Jun 2:11] Pavel Goran
"The method ResultSet.getBoolean() now returns a false when the the designated coulmn is of data type CHAR or VARCHAR and contains an “N” or “n”. This makes Connector/J 8.0 behaves like Connector/5.1 when it comes to converting strings to booleans."

This is actually not entirely correct. As of Connector/J 8.0.16, a value 'f' or 't' is not recognized when trying to read it with getBoolean(), and an exception is thrown.

Version 5.1 recognized all strings starting with "t", "y" or "1" as TRUE values, and never thrown exceptions during string-to-boolean conversion (well, at least the code in 5.1's ResultSetImpl.getBooleanFromString() doesn't throw exceptions). So version 8.0.16 does behave different from 5.1 when it comes to converting strings to booleans.
[27 Jun 9:28] Filipe Silva
Hi Pavel,

You are absolutely right. There are a few inconsistencies between Connector/J 5.1 and 8.0 on this regard.

Actually the JDBC spec makes no assumptions regarding these conversions, the only rule is that CHAR values "0" and "1" are converted to false and true respectively. But since we have these flexible conversion rules in 5.1 we better check this out again.

Can you please file a bug describing the behavior you are observing and what you expected instead?