MySQL Bugs: #15854: JDBCMetadata for BIT type returns wrong value

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

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.

Which metadata specifically? DatabaseMetaData.getColumns(), or ResultSetMetaData?

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.

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

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

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.

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

Still failing with mysql-connector-java-3.1-nightly-20060116-bin.jar

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

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();
				}
			}	
		}
	}

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.

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 !

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.

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 !