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
Triage: D2 (Serious)

[2 Jul 2008 10:08] Andre Schneider
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
[2 Jul 2008 10:51] Susanne Ebrecht
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  | 
+---+---+-----+------+-------+
[14 Aug 2008 23: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/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.
[26 Aug 2008 16:30] 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/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.
[27 Aug 2008 21:18] 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/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.
[28 Aug 2008 11:05] Bugs System
Pushed into 5.1.28  (revid:gshchepa@mysql.com-20080827211037-mwocc58oflzb9uce) (version source revid:kgeorge@mysql.com-20080828095450-xpq0biuyihkqgb05) (pib:3)
[2 Sep 2008 18:52] Paul Dubois
Noted in 5.1.28 changelog.

Setting report to NDI pending push into 5.0.x/6.0.x.
[13 Sep 2008 22:27] Bugs System
Pushed into 6.0.6-alpha  (revid:gshchepa@mysql.com-20080827211037-mwocc58oflzb9uce) (version source revid:hakan@mysql.com-20080716105246-eg0utbybp122n2w9) (pib:3)
[16 Sep 2008 14:37] Paul Dubois
Noted in 6.0.6 changelog.

Setting to NDI pending push into 5.0.x.
[9 Dec 2008 0:08] Paul Dubois
Noted in 5.0.70 changelog.