Description:
mysql>
mysql> CREATE TABLE t (id INT PRIMARY KEY, b BIT(8));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t VALUES (1, b'00000001');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT (b'00000001') < '0000-00-00'; -- 1 (literal: byte-string comparison, 0x01 < '0')
+------------------------------+
| (b'00000001') < '0000-00-00' |
+------------------------------+
| 1 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT b < '0000-00-00' FROM t; -- 0 (column: numeric comparison, 1 < 0)
+------------------+
| b < '0000-00-00' |
+------------------+
| 0 |
+------------------+
1 row in set, 1 warning (0.00 sec)
Affected surface: all 5 discriminating strings split ('0000-00-00', '000', '0', '00-00', 'a') — comparing a BIT column against an ordinary non-numeric string also triggers.
Impact: WHERE b < '0000-00-00' returns 0 rows while WHERE (b'00000001') < '0000-00-00' returns 1 row — the same value filters different row counts depending on which side of the comparison it appears on.
How to repeat:
CREATE TABLE t (id INT PRIMARY KEY, b BIT(8));
INSERT INTO t VALUES (1, b'00000001');
SELECT (b'00000001') < '0000-00-00'; -- 1 (literal: byte-string comparison, 0x01 < '0')
SELECT b < '0000-00-00' FROM t; -- 0 (column: numeric comparison, 1 < 0)
Description: mysql> mysql> CREATE TABLE t (id INT PRIMARY KEY, b BIT(8)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t VALUES (1, b'00000001'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT (b'00000001') < '0000-00-00'; -- 1 (literal: byte-string comparison, 0x01 < '0') +------------------------------+ | (b'00000001') < '0000-00-00' | +------------------------------+ | 1 | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT b < '0000-00-00' FROM t; -- 0 (column: numeric comparison, 1 < 0) +------------------+ | b < '0000-00-00' | +------------------+ | 0 | +------------------+ 1 row in set, 1 warning (0.00 sec) Affected surface: all 5 discriminating strings split ('0000-00-00', '000', '0', '00-00', 'a') — comparing a BIT column against an ordinary non-numeric string also triggers. Impact: WHERE b < '0000-00-00' returns 0 rows while WHERE (b'00000001') < '0000-00-00' returns 1 row — the same value filters different row counts depending on which side of the comparison it appears on. How to repeat: CREATE TABLE t (id INT PRIMARY KEY, b BIT(8)); INSERT INTO t VALUES (1, b'00000001'); SELECT (b'00000001') < '0000-00-00'; -- 1 (literal: byte-string comparison, 0x01 < '0') SELECT b < '0000-00-00' FROM t; -- 0 (column: numeric comparison, 1 < 0)