Bug #80933 Error updating BIT column value to 0 (false)

• Submitted: 1 Apr 2016 14:26
• Modified: 4 Apr 2016 5:42
• Reporter: Anibal P
• Status: Verified
• Category: MySQL Workbench: SQL Editor
• Severity: S3 (Non-critical)
• Version: 6.3, 6.3.6, 8.0
• OS: Windows
• CPU Architecture: Any
• Tags: update bit column false

[1 Apr 2016 14:26] Anibal P

Description:
When updating a BIT type column in "Result Grid" (showed as an integer (0/1)) and applying update, value is not changed and no error is raised.
It seems to be generating an incorrect update value of '0' (with commas), instead of 0 or false.

How to repeat:
CREATE TABLE `new_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO new_table VALUES (null, true);

When changing from interface to 0 or false, this is the generated sentence generated that does nothing:

UPDATE `home_security_dev`.`new_table` SET `field1`='0' WHERE `id`='1';

This should be 0 or false without commas.

[4 Apr 2016 5:42] MySQL Verification Team

Hello Anibal P,

Thank you for the report.
Observed this with WB 6.3.6 on Win7.

Thanks,
Umesh

[18 Apr 2016 4:19] MySQL Verification Team

Bug #81110 marked as duplicate of this

[20 Apr 2016 17:40] MySQL Verification Team

http://bugs.mysql.com/bug.php?id=81158 marked as duplicate of this one.

[21 Dec 2017 15:21] Lee Butler

I can confirm that this issue is still outstanding in 6.3.9.

To easily replicate, select a row from a table which includes a BIT value.
Attempt to update the value by typing a 1 or a 0, whichever wasnt already in the field.

Press apply to apply the changes. Like mentioned above, this will generate SQL that looks something like [...] SET FieldName='1' [...] which will result in 

"ERROR 1406: 1406: Data too long for column[...]"

[14 May 2018 0:33] Rafael Pereira

One year later and the bug still :)

[19 Jun 2018 3:42] franz phillips

When updating with the result grid to 0 you have to type in b'0'.

[18 Aug 2021 12:43] MySQL Verification Team

Bug #104644 marked as duplicate of this one