Bug #12972 A BOOLEAN column is reported as java.sql.Types.BIT
Submitted: 3 Sep 2005 20:33 Modified: 4 Sep 2005 2:51
Reporter: RW Shore Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:3.1.10 OS:Microsoft Windows (WinXP)
Assigned to: CPU Architecture:Any

[3 Sep 2005 20:33] RW Shore
Description:
Create a table with one or more columns of type BOOLEAN. Execute databaseMetaData.getColumns(null, null, tablename, null). The BOOLEAN columns report DATA_TYPE as java.sql.Types.BIT with length 1. The documentation says that BOOLEAN is an alias for TINYINT(1).

How to repeat:
The situation is repeatable. All BOOLEAN columns report java.sql.Types.BIT.

Suggested fix:
It's not clear whether this should be fixed by (a) modifying the documentation to say that BOOLEAN is an alias for BIT(1) or (b) modifying the connector so that the DATA_TYPE returned by getColumns() reports java.sql.Types.TINYINT (or, for that matter, java.sql.Types.BOOLEAN). 

What is the actual intent of this type?? At a minimum, the documentation and the DATA_TYPE should agree.
[3 Sep 2005 20:35] RW Shore
Java VM: build 1.5.0_04-b05
MySql: 5.0.11-beta-nt
[4 Sep 2005 2:51] Mark Matthews
See the documentation for the "transformedBitIsBoolean" configuration property (the default is false), copied here for your convenience:

If the driver converts TINYINT(1) to a different type, should it use BOOLEAN instead of BIT  for future compatibility with MySQL-5.0, as MySQL-5.0 has a BIT type?

The conversion is the way it is, because of forward-compatibility with MySQL-5.0. If you want TINYINT(1) to report itself as java.sql.Types.BOOLEAN, set this value to "true".
[4 Sep 2005 9:58] RW Shore
I understand the TINYINT reporting parameter. My point is the same: given the documentation, I expected a BOOLEAN to have a DATA_TYPE of java.sql.Types.TINYINT, not java.sql.Types.BIT. At a minimum, the documentation that claims BOOLEAN is an alias for TINYINT(1) would seem to be misleading, since under 5.x BOOLEAN appears to be an alias for BIT(1).
[4 Sep 2005 13:56] Mark Matthews
> I understand the TINYINT reporting parameter. My point is the same:
> given the documentation, I expected a BOOLEAN to have a DATA_TYPE of
> java.sql.Types.TINYINT, not java.sql.Types.BIT. At a minimum, the
> documentation that claims BOOLEAN is an alias for TINYINT(1) would seem
> to be misleading, since under 5.x BOOLEAN appears to be an alias for
> BIT(1).

Most folks want BOOLEAN to operate _as_a_boolean_, so if it's an alias for TINYINT(1), then the driver has to "hide" this fact, irregardless of what the server does with the type (i.e. the type has to stay consistent for client -> server _and_ server -> client). 

Many JDBC applications/frameworks depend on this behavior, so the default is to act that way. 

If you or your application _don't_ want that behavior, then use "tinyInt1isBit=false", as is stated in the documentation.

There are quite a few "corner" cases with the boolean/bit types, since 1) They were dropped from the SQL standard, and two, the _server_ hasn't had true support for BIT until version 5.0. The driver tries to make the best of the situation and provide operations that work within the standard and how the majority of frameworks written on top of JDBC depend on things regarding these types to work (including the JDBC compliance testsuite, without the current behavior, the driver would _not_ be JDBC-compliant).
[26 May 2006 18:44] [ name withheld ]
From what I can glean from my research BIT is only valid in JDBC 2.0 and earlier versions.  If Connector/J is truly a JDBC version 3.0 driver, it should behave that way by default.