MySQL Bugs: #83312: insert an empty string to bit field should have failed with strict sql mode

Bug #83312	insert an empty string to bit field should have failed with strict sql mode
Submitted:	9 Oct 2016 7:26	Modified:	2 Jan 2020 23:05
Reporter:	帅 Bang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5/5.6/5.7/8.0	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
mysql> drop table if exists t1,t2;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t1(c1 bit(64) default b'');
ERROR 1067 (42000): Invalid default value for 'c1'

OK, it is quite ok for now. OK, let us move on:

mysql> create table t1(c1 bit(64));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(b'');
Query OK, 1 row affected (0.00 sec)

It succeed. Well, IMHO, it should have failed. Why ?  Let us see what will happen if it were type int.

mysql> create table t2(c1 int default '');
ERROR 1067 (42000): Invalid default value for 'c1'

mysql>drop table t2;
mysql> create table t2(c1 int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t2 values('');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'c1' at row 1

yeah, insertion failed. quite good. 

How to repeat:
set @@sql_mode = "STRICT_ALL_TABLES";
drop table t1;
create table t1(c1 bit(64) default b'');
create table t1(c1 bit(64));
insert into t1 values(b'');

Suggested fix:
insertion should have failed

Results

Attachment: bug83312.txt (text/plain), 4.25 KiB.

Thank you for the bug report. Verified as described.

Posted by developer:
 
This does not look like a bug to me.
A string interpreted as an integer must contain at least one digit.
A bit string may be given a zero length, in which case the remaining bits are filled
with zero values.