Bug #111439 TINYINT(1) is returned as Boolean when fetched with mysql community jdbc driver

• Submitted: 15 Jun 2023 14:56
• Modified: 16 Jun 2023 11:52
• Reporter: Rahul Dhuvad
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[15 Jun 2023 14:56] Rahul Dhuvad

Description:
TINYINT(1) is returned as Boolean when fetched with mysql community jdbc driver using resultSet.getObject("tinyint_columnname) even though allowed value in that column is -128 to 127. It gives proper value when we fetch with resultSet.getInt("tinyint_columnname")

How to repeat:
TINYINT(1) is returned as Boolean when fetched with mysql community jdbc driver using resultSet.getObject("tinyint_columnname) even though allowed value in that column is -128 to 127. It gives proper value when we fetch with resultSet.getInt("tinyint_columnname")

Suggested fix:
It should return same value in resultSet.getObject as the value stored while insertion

[16 Jun 2023 11:52] MySQL Verification Team

Hello Rahul,

Thank you for the report and feedback.
IMHO this is an expected and documented behavior in MySQL 8.0.19 under WL#13127, "Deprecate integer display width and ZEROFILL option". We had to follow in c/J with Bug#97413. Please see the explanation from Alex in Bug #100309.

--
public class Bug111439 {

   public static void main(String[] args) throws ClassNotFoundException, SQLException {

            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
            } catch (ClassNotFoundException e) {
            }
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=mysql123");
            Runtime.Version version = Runtime.version();
            System.out.println("JDK version: " + version);
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println("MySQL Server: " + meta.getDatabaseProductVersion());
            System.out.println("Driver Name & Ver: " + meta.getDriverName() + meta.getDriverVersion());            

            Statement pst = conn.createStatement();
            pst.addBatch("DROP TABLE if exists tempTable");
            pst.addBatch("CREATE table tempTable (tiny TINYINT NOT NULL DEFAULT 0) engine=InnoDB");
            pst.addBatch("insert into tempTable values(127);");
            pst.executeBatch();
            pst.close();

            PreparedStatement pst2 = conn.prepareStatement("SELECT tiny FROM tempTable");
            pst2.execute();
            ResultSet rs = pst2.getResultSet();
            rs.next();
            System.out.println("Boolean? " + (rs.getObject(1) instanceof Boolean));
            System.out.println("Integer? " + (rs.getObject(1) instanceof Integer));
        }       
}

-- when column is defined as TINYINT then it returns 127 (integer)
-- when column is defined as TINYINT(1) returns true(boolean)

regards,
Umesh

[16 Jun 2023 12:31] MySQL Verification Team

Also, please see https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html