Description:
BIT(0) is not handled properly.
http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html says:
BIT[(M)]
A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.
http://dev.mysql.com/doc/refman/5.5/en/bit-type.html says:
The BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.
Values of M out of range on the high end produce an error. So do negative values of M. This occurs even without strict mode:
mysql> SET sql_mode='';
Query OK, 0 rows affected (0.03 sec)
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t (b BIT(65));
ERROR 1439 (42000): Display width out of range for column 'b' (max = 64)
mysql> CREATE TABLE t (b BIT(-1));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1))' at line 1
But BIT(0), although illegal, produces neither an error nor a warning. The 0 is coerced, silently, to 1. This occurs even in strict SQL mode:
mysql> SET sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> CREATE TABLE t (b BIT(0));
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`b` bit(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
How to repeat:
Run this script with mysql -f
SET sql_mode='';
DROP TABLE IF EXISTS t;
CREATE TABLE t (b BIT(65));
SHOW CREATE TABLE t\G
DROP TABLE IF EXISTS t;
CREATE TABLE t (b BIT(-1));
SHOW CREATE TABLE t\G
SET sql_mode='TRADITIONAL';
DROP TABLE IF EXISTS t;
CREATE TABLE t (b BIT(0));
SHOW CREATE TABLE t\G
Suggested fix:
BIT(0) should be illegal like BIT(64).
