MySQL Bugs: #12972: A BOOLEAN column is reported as java.sql.Types.BIT

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:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any

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.

Java VM: build 1.5.0_04-b05
MySql: 5.0.11-beta-nt

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".

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).

> 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).

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.