MySQL Bugs: #37799: Columns after a column from type BIT are handled as string

Bug #37799	Columns after a column from type BIT are handled as string
Submitted:	2 Jul 2008 10:08	Modified:	9 Dec 2008 0:08
Reporter:	Andre Schneider	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0, 5.1, 6.0	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
If you have a table with a column from type BIT then all following columns are handled as string.
So the result returning form queries are wrong.
But this happens only, if you have the BIT column somewhere in your where-clause.

How to repeat:
Example:
Following table:
CREATE TABLE `test`.`testtable` (
  `id` BIGINT  NOT NULL AUTO_INCREMENT,
  `boolean` BIT  NOT NULL,
  `integer` INT  NOT NULL,
  `double` DOUBLE  NOT NULL,
  `string` VARCHAR(255)  NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = MyISAM;
With following entries:
INSERT INTO `test`.`testtable` (`id`,`boolean`,`integer`,`double`,`string`) VALUES
 (4,0x01,100,10.8,'test4'),
 (3,0x00,300,13.5,'test3'),
 (2,0x01,200,12.5,'test2'),
 (1,0x00,100,10.5,'test');

The Statement
SELECT * from testtable
where `integer` >= 100 AND `integer` < 201
and `boolean` = true

should deliver 2 lines with id 2 and 4.
But 0 rows were fetched.

If you omit the 'and `boolean` = true', you get 3 lines: id 1, 2 and 4.

Also the statement:
SELECT * from testtable
where `integer` >= 2 AND `integer` < 201
and `boolean` = true
delivers 0 rows.

But the statement
SELECT * from testtable
where `integer` >= 1 AND `integer` < 201
and `boolean` = true
delivers 2 rows: id 1 and 4

Verified as described by using 5.0 bzr tree.

create table t(i bigint not null auto_increment, b bit not null, i2 integer not null, d double not null, s varchar(255) not null, primary key(i));

insert into t values(4,0x01,100,10.8,'test4'),  (3,0x00,300,13.5,'test3'),  (2,0x01,200,12.5,'test2'),  (1,0x00,100,10.5,'test');

 select * from t;
+---+---+-----+------+-------+
| i | b | i2  | d    | s     |
+---+---+-----+------+-------+
| 4 |  | 100 | 10.8 | test4 | 
| 3 |   | 300 | 13.5 | test3 | 
| 2 |  | 200 | 12.5 | test2 | 
| 1 |   | 100 | 10.5 | test  | 
+---+---+-----+------+-------+

mysql> select 0x01=true;
+-----------+
| 0x01=true |
+-----------+
|         1 | 
+-----------+
1 row in set (0.00 sec)

mysql> select 0x01=false;
+------------+
| 0x01=false |
+------------+
|          0 | 
+------------+

select hex(b) from t;
+--------+
| hex(b) |
+--------+
| 1      | 
| 0      | 
| 1      | 
| 0      | 
+--------+

select * from t where b=true;
+---+---+-----+------+-------+
| i | b | i2  | d    | s     |
+---+---+-----+------+-------+
| 4 |  | 100 | 10.8 | test4 | 
| 2 |  | 200 | 12.5 | test2 | 
+---+---+-----+------+-------+

select * from t where (i2>=100 and i2<201) and b=true;
Empty set (0.00 sec)

select * from t where (i2>=100 and i2<201);
+---+---+-----+------+-------+
| i | b | i2  | d    | s     |
+---+---+-----+------+-------+
| 4 |  | 100 | 10.8 | test4 | 
| 2 |  | 200 | 12.5 | test2 | 
| 1 |   | 100 | 10.5 | test  | 
+---+---+-----+------+-------+

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

2661 Gleb Shchepa	2008-08-15
      Bug #37799: SELECT with a BIT column in WHERE clause
                  returns unexpected result
      
      If:
        1. a table has a not nullable BIT column c1 with a length
           shorter than 8 bits and some additional not nullable
           columns c2 etc, and
        2. the WHERE clause is like: (c1 = constant) AND c2 ...,
      the SELECT query returns unexpected result set.
      
      
      The server stores BIT columns in a tricky way to save disk
      space: if column's bit length is not divisible by 8, the
      server places "uneven" bits among the null bits at the start 
      of a record. The rest bytes are stored in the record itself,
      and Field::ptr points to these rest bytes.
      
      However if a bit length of the whole column is lesser than 8,
      there are no rest bytes, and there is nothing to store in 
      the record at a regular place. In this case Field::ptr points
      to bytes actually occupied by the next column in a record.
      If both columns (BIT and the next column) are NOT NULL,
      the Field::eq function guesses that this is the same column,
      so query transformation/equal item elimination code (see
      build_equal_items_for_cond) may mix these columns and damage 
      conditions containing references to them.
      
      
      The Field::eq function has been modified to take types of
      comparing columns into account to distinguish between BIT and not
      BIT columns referencing the same bytes in a record.

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

2661 Gleb Shchepa	2008-08-26
      Bug #37799: SELECT with a BIT column in WHERE clause
                  returns unexpected result
      
      If:
        1. a table has a not nullable BIT column c1 with a length
           shorter than 8 bits and some additional not nullable
           columns c2 etc, and
        2. the WHERE clause is like: (c1 = constant) AND c2 ...,
      the SELECT query returns unexpected result set.
      
      
      The server stores BIT columns in a tricky way to save disk
      space: if column's bit length is not divisible by 8, the
      server places reminder bits among the null bits at the start
      of a record. The rest bytes are stored in the record itself,
      and Field::ptr points to these rest bytes.
      
      However if a bit length of the whole column is less than 8,
      there are no remaining bytes, and there is nothing to store in
      the record at its regular place. In this case Field::ptr points
      to bytes actually occupied by the next column in a record.
      If both columns (BIT and the next column) are NOT NULL,
      the Field::eq function incorrectly deduces that this is the
      same column, so query transformation/equal item elimination
      code (see build_equal_items_for_cond) may mix these columns
      and damage conditions containing references to them.

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

2679 Gleb Shchepa	2008-08-28
      Bug #37799: SELECT with a BIT column in WHERE clause
                  returns unexpected result
      
      If:
        1. a table has a not nullable BIT column c1 with a length
           shorter than 8 bits and some additional not nullable
           columns c2 etc, and
        2. the WHERE clause is like: (c1 = constant) AND c2 ...,
      the SELECT query returns unexpected result set.
      
      
      The server stores BIT columns in a tricky way to save disk
      space: if column's bit length is not divisible by 8, the
      server places reminder bits among the null bits at the start
      of a record. The rest bytes are stored in the record itself,
      and Field::ptr points to these rest bytes.
      
      However if a bit length of the whole column is less than 8,
      there are no remaining bytes, and there is nothing to store in
      the record at its regular place. In this case Field::ptr points
      to bytes actually occupied by the next column in a record.
      If both columns (BIT and the next column) are NOT NULL,
      the Field::eq function incorrectly deduces that this is the
      same column, so query transformation/equal item elimination
      code (see build_equal_items_for_cond) may mix these columns
      and damage conditions containing references to them.

Pushed into 5.1.28  (revid:gshchepa@mysql.com-20080827211037-mwocc58oflzb9uce) (version source revid:kgeorge@mysql.com-20080828095450-xpq0biuyihkqgb05) (pib:3)

Noted in 5.1.28 changelog.

Setting report to NDI pending push into 5.0.x/6.0.x.

Pushed into 6.0.6-alpha  (revid:gshchepa@mysql.com-20080827211037-mwocc58oflzb9uce) (version source revid:hakan@mysql.com-20080716105246-eg0utbybp122n2w9) (pib:3)

Noted in 6.0.6 changelog.

Setting to NDI pending push into 5.0.x.

Noted in 5.0.70 changelog.