MySQL Bugs: #13601: Wrong int type for bit

Bug #13601	Wrong int type for bit
Submitted:	29 Sep 2005 10:44	Modified:	14 Apr 2006 14:32
Reporter:	Thoralf Rickert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Java
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
MySQL 5 supports finally the BIT type.

Now I've a problem with the MySQL JDBC driver.
The metadata in a resultset returns the wrong column type constant for a column with the type "BIT". The type BIT in java.sql.Types has the value -7. The metadata returns  -3 (for VARBINARY). The column type name is correct.

I'm not sure, if this bug is already fixed in a unstable version but the bug affects productive environments.

How to repeat:
Use mysql5.

CREATE TABLE test (
  id BIGINT NOT NULL,
  deleted BIT default 0 NOT NULL
) Type=InnoDB;

In Java:

      String sql = "SELECT * FROM test WHERE 1=-1";
      System.out.println(sql);
      PreparedStatement stmt = connection.prepareStatement(sql);
      ResultSet rs = stmt.executeQuery();
      if (rs != null) {
        ResultSetMetaData metadata = rs.getMetaData();
        System.out.println(metadata.getColumnCount()+" Columns found.");
        for (int column=1; column<=metadata.getColumnCount(); column++) {
          System.out.print(column+": ");
          System.out.print(metadata.getColumnName(column));
          System.out.print("("+metadata.getColumnTypeName(column)+")");
          System.out.println("("+metadata.getColumnType(column)+")");
        }
      }

This code returns the following output:

SELECT * FROM test WHERE 1=-1
2 Columns found.
1: id(BIGINT)(-5)
2: deleted(BIT)(-3)

But the type BIT in java.sql.Types has the value -7 and not -3 (for VARBINARY).

Suggested fix:
Use column type name or change column type to tinyint(1). Then the method returns BIT (-7)

Okay, I think, I found the bug:

File: com.mysql.jdbc.Field line 190

line 190: if (this.mysqlType == MysqlDefs.FIELD_TYPE_BIT) {
line 191:   if (this.length == 0) {
line 192:     this.sqlType = Types.BIT;
line 193:   } else {
line 194:     this.sqlType = Types.VARBINARY;
line 195:     this.colFlag |= 128; // we need to pretend this is a full
line 196:     this.colFlag |= 16; // binary blob
line 197:   }
line 198:}

length == 0? I think, that doesn't work.

Thanks for the bug report. I was able to reproduce the bug. My test case is attached.

Test case

Attachment: Bug13601.java (text/java), 1.46 KiB.

When the type was first introduced to the server "BIT" actually did return a length of zero (basically the length returned was not number of bits, but number of bits / 8).

Appears to be a server issue. MyISAM tables return length of "0" for the DDL in the bug report, InnoDB returns "1", and thus the driver can't reliably distinguish between the semantics of "BIT" and something more like "BIT VARYING", as a > 1 bit BIT type in MyISAM appears to be different than a > 1 bit BIT type in InnoDB.

I think InnoDB does not support the BIT type internally. Does MySQL somehow emulate it?

Regards,

Heikki

Putting the category as 'MySQL Server', because internally there is no BIT type in InnoDB.

Under server 5.0.15 and jdbc 3.1.12 the test case gives:

SELECT * FROM test WHERE 1=-1
2 Columns found.
1: id(BIGINT)(-5)
2: deleted(BIT)(12)

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/3610

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/4467

Fixed in 5.0.21 and 5.1.10.

Noted in 5.0.21, 5.1.10 changelogs.

In result set metadata, the
<literal>MYSQL_FIELD.length</literal> value for
<literal>BIT</literal> columns now is reported in number of
bits. For example, the value for a <literal>BIT(9)</literal>
column is 9. (Formerly, the value was related to number of
bytes.) (Bug #13601)