Bug #15854 JDBCMetadata for BIT type returns wrong value
Submitted: 19 Dec 2005 10:36 Modified: 10 Mar 2006 20:28
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:Mysql 5.0.16 OS:*nix
Assigned to: CPU Architecture:Any

[19 Dec 2005 10:36] [ name withheld ]
Description:
Hi:

Connector/J returns  1111 (OTHER) for a column declared as BIT.

so 

foo BIT

will return 1111 for foo (instead of -7/BIT as specified in java.sql.Types)

This happens with InnoDB tables (I don't know if this happens with other
table types).

Very bad bug because my O/R mapping tool breaks and crashes because
it does not know how to handle 1111 (which is a type almost never 
encountered in any real database).

How to repeat:
create table (foo BIT);

Get JDBCMetaData on that table.
[19 Dec 2005 15:03] Mark Matthews
Which metadata specifically? DatabaseMetaData.getColumns(), or ResultSetMetaData?
[19 Dec 2005 20:56] [ name withheld ]
Via the ResultSet (in the following code, md is a reference to the meta data object)

	ResultSet rs = md.getColumns(
		catalogname, schemaname, tablename, columnpattern);

...and then..

		String colname 		= rs.getString	("COLUMN_NAME");
		int datatype 		= rs.getInt		("DATA_TYPE");

That datatype is 1111 for BIT but should be -7 instead.
[20 Dec 2005 23:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/307
[20 Dec 2005 23:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/308
[11 Jan 2006 12:21] chochis labs
Still failing on JDBC 3.1.12
Using:
//JDBC generic
System.out.println(metadata.getColumnClassName(result.findColumn("ACCEPT_COOKIE")));
System.out.println(metadata.getColumnDisplaySize(result.findColumn("ACCEPT_COOKIE")));
System.out.println(metadata.getColumnLabel(result.findColumn("ACCEPT_COOKIE")));
System.out.println(metadata.getColumnName(result.findColumn("ACCEPT_COOKIE")));
System.out.println(metadata.getColumnType(result.findColumn("ACCEPT_COOKIE")));
System.out.println(metadata.getColumnTypeName(result.findColumn("ACCEPT_COOKIE")));
//MySql specific
System.out.println(metadata.getColumnCharacterEncoding(result.findColumn("ACCEPT_COOKIE")));
System.out.println(metadata.getColumnCharacterSet(result.findColumn("ACCEPT_COOKIE")));

writes:
java.lang.String
1
ACCEPT_COOKIE
ACCEPT_COOKIE
12
BIT
null
US-ASCII 

The metadata is from the resultset. I dont know about databasemetadata.
[11 Jan 2006 14:27] Mark Matthews
This bug was fixed after 3.1.12 was released, so please test with nightly snapshots from http://downloads.mysql.com/snapshots.php#connector-j or from source at http://svn.mysql.com/svnpublic/connector-j/branches/branch_3_1
[16 Jan 2006 11:41] chochis labs
Still failing with mysql-connector-java-3.1-nightly-20060116-bin.jar
[16 Jan 2006 15:34] Mark Matthews
Can you provide a testcase that's specific to your environment and/or situation? The original bug has been solved. This sounds like something related, but new, perhaps your queries are being resolved via temporary tables and the BIT type is getting clobbered? (an EXPLAIN on the query in question would show this, so please post that information as well).
[16 Jan 2006 16:05] Mark Matthews
As an example, the following testcase passes. What does your schema look like?

	public void testBug15854() throws Exception {
		if (versionMeetsMinimum(5, 0)) {
			createTable("testBug15854", "(field1 BIT)");
			try {
				this.rs = this.conn.getMetaData().getColumns(
						this.conn.getCatalog(), null, "testBug15854", "field1");
				assertTrue(this.rs.next());
				assertEquals(Types.BIT, this.rs.getInt("DATA_TYPE"));
				this.rs.close();
				
				this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug15854");
				ResultSetMetaData rsmd = this.rs.getMetaData();
				assertEquals(Types.BIT, rsmd.getColumnType(1));
				assertEquals("java.lang.Boolean", rsmd.getColumnClassName(1));
				
			} finally {
				if (this.rs != null) {
					ResultSet toClose = this.rs;
					this.rs = null;
					toClose.close();
				}
			}	
		}
	}
[10 Mar 2006 20:28] Mark Matthews
Fix will be available in 5.0.1 and 3.1.13. You can try a nightly snapshot from http://downloads.mysql.com/snapshots.php#connector-j if you want to test the fix before it's officially released. Thanks for the bug report.
[20 Apr 2006 9:19] Chitrapandian Nachiappan
Tested this using,
http://downloads.mysql.com/snapshots/mysql-connector-java-3.1/mysql-connector-java-3.1-nig...

But, seems like still this problem was not solved completely, meaning it's not working while using ResultSetMetadata,

ResultSetMetaData rsmd = this.rs.getMetaData();
assertEquals(Types.BIT, rsmd.getColumnType(1));

Output :

 rssmd.getColumnTypeName(col) > : BIT
 rssmd.getColumnType(col))       > : 12 (it should be -7 here?? BUG ?)

DatabaseMetada with this case returns properly,

 TABLE_CAT - null ;
 TABLE_SCHEM - null ;
 TABLE_NAME - mytable ;
 COLUMN_NAME - testcol ;
 DATA_TYPE - -7 ;
 TYPE_NAME - bit ;
 COLUMN_SIZE - 1 ;
 BUFFER_LENGTH - 65535 ;
 DECIMAL_DIGITS - 0 ;
 NUM_PREC_RADIX - 10 ;
 NULLABLE - 1 ;
 REMARKS -  ;
 COLUMN_DEF - null ;
 SQL_DATA_TYPE - 0 ;
 SQL_DATETIME_SUB - 0 ;
 CHAR_OCTET_LENGTH - 1 ;
 ORDINAL_POSITION - 10 ;
 IS_NULLABLE - YES ;

Pandya !
[20 Apr 2006 12:43] Mark Matthews
What storage engine are you using for the table in question? If it's InnoDB, this will still be broken until MySQL-5.0.21 ships, as there's a related bug that causes MySQL to return incorrect metadata to the driver for the BIT type (returns incorrect length) if the storage engine being used for the table in question is InnoDB.
[20 Apr 2006 13:07] Chitrapandian Nachiappan
Yes, I'm using INNODB storage engine, with MySQL Server version : 5.0.18.

Thanks for the info. So, I have to wait for the release of 5.0.21 to get work properly with BIT datatype !!

Pandya !