MySQL Bugs: #100309: TINYINT(1) UNSIGNED not treated as Boolean

Bug #100309	TINYINT(1) UNSIGNED not treated as Boolean
Submitted:	23 Jul 2020 18:56	Modified:	29 Jun 2021 8:55
Reporter:	Jean-Marc Marchand	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.19, 8.0.20, 8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	boolean, regression, Tinyint

Description:
ConnectorJ does not convert TINYINT(1) UNSIGNED to Boolean anymore; it returns an Integer. It does convert to Boolean only if UNSIGNED is removed from the column definition.

Version 8.0.18 and prior used to convert this datatype to Boolean correctly.

Documentation https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html#i... states that it should do this if the tinyint is of length=1, but doesn't mention anything about unsigned.

This has a major impact on schemas having such UNSIGNED columns and treating them as Boolean in Java

How to repeat:
// mind the missing error handlingg
Statement pst = con.createStatement();
pst.addBatch("DROP TABLE if exists tempTable");
pst.addBatch("CREATE table tempTable (tinyunsigned TINYINT(1) UNSIGNED NOT NULL DEFAULT 0) engine=InnoDB");
pst.addBatch("insert into tempTable values(1);");
pst.executeBatch();
pst.close();

PreparedStatement pst2 = con.prepareStatement("SELECT tinyunsigned 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));

Suggested fix:
Put back the support for UNSIGNED tinyint(1) for Booleans

Hello Jean-Marc Marchand,

Thank you for the report and feedback.

regards,
Umesh

Hi,

Which server version do you use?

There was a significant change 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.

The fact is that starting from MySQL 8.0.19 only TINYINT(1) (no ZEROFILL, no UNSIGNED) can be treated as Boolean, other variants are not supplied with display width.

We could add the server version check to c/J in order TINYINT(1) UNSIGNED could work with previous server versions but I think it would be better to follow the server changes and rework your DB structure to avoid possible problems with future migration.

Bug#100722 is marked as a duplicate of this one.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Posted by developer:
 
Closing as not a bug. Please reopen the report in case you still need some kind of c/J backward compatibility in TINYINT(1) UNSIGNED vs Boolean handling.