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-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.
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
    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.
=== 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-14 23:45:11 +0000
@@ -684,4 +684,11 @@ 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
+DROP TABLE t1;
 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-14 23:45:11 +0000
@@ -333,4 +333,13 @@ 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;
+DROP TABLE t1;
+
 --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-14 23:45:11 +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);