bzr commit into mysql-5.0-bugteam branch (gshchepa:2661) Bug#37799

[Gleb Shchepa]

#At file:///work/bzr/5.0-bugteam-37799/

 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.
modified:
  mysql-test/r/type_bit.result
  mysql-test/t/type_bit.test
  sql/field.h

per-file messages:
  mysql-test/r/type_bit.result
    Added test case for bug #37799.
  mysql-test/t/type_bit.test
    Added test case for bug #37799.
  sql/field.h
    1. 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.
    
    2. Unnecessary type comparison has been removed from the
    Field_bit::eq function (moved to Field::eq).
=== modified file 'mysql-test/r/type_bit.result'
--- a/mysql-test/r/type_bit.result	2007-12-02 00:48:43 +0000
+++ b/mysql-test/r/type_bit.result	2008-08-26 16:28:46 +0000
@@ -684,4 +684,28 @@ SELECT 1 FROM t1 GROUP BY a;
 1
 1
 DROP TABLE t1;
+CREATE TABLE t1 (b BIT NOT NULL, i2 INTEGER NOT NULL, s VARCHAR(255) NOT NULL);
+INSERT INTO t1 VALUES(0x01,100,''), (0x00,300,''), (0x01,200,''), (0x00,100,'');
+SELECT HEX(b), i2 FROM t1 WHERE (i2>=100 AND i2<201) AND b=TRUE;
+HEX(b)	i2
+1	100
+1	200
+CREATE TABLE t2 (b1 BIT NOT NULL, b2 BIT NOT NULL, i2 INTEGER NOT NULL,
+s VARCHAR(255) NOT NULL);
+INSERT INTO t2 VALUES (0x01,0x00,100,''), (0x00,0x01,300,''),
+(0x01,0x00,200,''), (0x00,0x01,100,'');
+SELECT HEX(b1), i2 FROM t2 WHERE (i2>=100 AND i2<201) AND b1=TRUE;
+HEX(b1)	i2
+1	100
+1	200
+SELECT HEX(b2), i2 FROM t2 WHERE (i2>=100 AND i2<201) AND b2=FALSE;
+HEX(b2)	i2
+0	100
+0	200
+SELECT HEX(b1), HEX(b2), i2 FROM t2
+WHERE (i2>=100 AND i2<201) AND b1=TRUE AND b2=FALSE;
+HEX(b1)	HEX(b2)	i2
+1	0	100
+1	0	200
+DROP TABLE t1, t2;
 End of 5.0 tests

=== modified file 'mysql-test/t/type_bit.test'
--- a/mysql-test/t/type_bit.test	2007-11-21 18:56:42 +0000
+++ b/mysql-test/t/type_bit.test	2008-08-26 16:28:46 +0000
@@ -333,4 +333,23 @@ SELECT 1 FROM t1 GROUP BY a;
 
 DROP TABLE t1;
 
+#
+# Bug#37799 SELECT with a BIT column in WHERE clause returns unexpected result
+#
+
+CREATE TABLE t1 (b BIT NOT NULL, i2 INTEGER NOT NULL, s VARCHAR(255) NOT NULL);
+INSERT INTO t1 VALUES(0x01,100,''), (0x00,300,''), (0x01,200,''), (0x00,100,'');
+SELECT HEX(b), i2 FROM t1 WHERE (i2>=100 AND i2<201) AND b=TRUE;
+
+CREATE TABLE t2 (b1 BIT NOT NULL, b2 BIT NOT NULL, i2 INTEGER NOT NULL,
+                 s VARCHAR(255) NOT NULL);
+INSERT INTO t2 VALUES (0x01,0x00,100,''), (0x00,0x01,300,''),
+                      (0x01,0x00,200,''), (0x00,0x01,100,'');
+SELECT HEX(b1), i2 FROM t2 WHERE (i2>=100 AND i2<201) AND b1=TRUE;
+SELECT HEX(b2), i2 FROM t2 WHERE (i2>=100 AND i2<201) AND b2=FALSE;
+SELECT HEX(b1), HEX(b2), i2 FROM t2
+       WHERE (i2>=100 AND i2<201) AND b1=TRUE AND b2=FALSE;
+
+DROP TABLE t1, t2;
+
 --echo End of 5.0 tests

=== modified file 'sql/field.h'
--- a/sql/field.h	2008-08-11 16:10:00 +0000
+++ b/sql/field.h	2008-08-26 16:28:46 +0000
@@ -138,7 +138,7 @@ public:
   virtual bool eq(Field *field)
   {
     return (ptr == field->ptr && null_ptr == field->null_ptr &&
-            null_bit == field->null_bit);
+            null_bit == field->null_bit && field->type() == type());
   }
   virtual bool eq_def(Field *field);
   
@@ -1489,7 +1489,6 @@ public:
   bool eq(Field *field)
   {
     return (Field::eq(field) &&
-            field->type() == type() &&
             bit_ptr == ((Field_bit *)field)->bit_ptr &&
             bit_ofs == ((Field_bit *)field)->bit_ofs);
   }