#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);
