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

[29 Sep 2005 10:44] Thoralf Rickert
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)
[29 Sep 2005 11:57] Thoralf Rickert
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.
[30 Sep 2005 8:02] Vasily Kishkin
Thanks for the bug report. I was able to reproduce the bug. My test case is attached.
[30 Sep 2005 8:03] Vasily Kishkin
Test case

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

[17 Oct 2005 19:53] Mark Matthews
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).
[17 Oct 2005 20:10] Mark Matthews
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.
[17 Oct 2005 23:07] Heikki Tuuri
I think InnoDB does not support the BIT type internally. Does MySQL somehow emulate it?

Regards,

Heikki
[17 Oct 2005 23:10] Heikki Tuuri
Putting the category as 'MySQL Server', because internally there is no BIT type in InnoDB.
[15 Feb 2006 14:58] Simon Greenaway
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)
[9 Mar 2006 2:40] 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/3610
[5 Apr 2006 0:46] 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/4467
[11 Apr 2006 7:39] Jim Winstead
Fixed in 5.0.21 and 5.1.10.
[14 Apr 2006 14:32] Paul DuBois
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)