MySQL Bugs: #28630: Edit Mode in MySQL Query Browser doesn't support entry of b'1' to a BIT(1) field

Bug #28630	Edit Mode in MySQL Query Browser doesn't support entry of b'1' to a BIT(1) field
Submitted:	23 May 2007 20:33	Modified:	26 May 2009 12:57
Reporter:	Charlie Frias	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.2.17	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	BINARY, bit, CHECKED, insert, values

Description:
While in the MySQL Query Browser, I am running into trouble using the Edit Mode to update any tables that include an update to a field defined as BIT(1).  If I execute a simple INSERT command or a simple UPDATE statement from the Query Browser to update that row, the update occurs with no issues.  But if I use the Edit Mode to update the field and then Apply Changes, I get an error.

How to repeat:
To repeat the problem:
-- Create a test table:
CREATE TABLE `TestABC` (
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `TestBit` BIT,
  PRIMARY KEY (`ID`)
)
ENGINE = InnoDB;

-- Test the INSERT statement (the row is inserted with no issues)
INSERT INTO TestABC
SET TestBIT = b'1'

-- Test the UPDATE statement (the row is updated with no issues)
UPDATE TestABC
SET TestBit = b'1'
WHERE ID = '1'

-- Test the Edit Mode by 
-- a) clicking on the grid and touching the value, setting it finally to "b'1'".  
-- b) click on Apply Changes

-- Observe the following error message:
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'' WHERE `ID`='1'' at line 1

Suggested fix:
I have to assume that the fix that was done for handling single quotes in text edit fields in the editor may need to be applied to binary edit fields as well.

Thank you for a bug report. Verified just as described with latest 1.2.12.

Is there a work around for this?  Manual SQL insert I suppose . .

I don't believe there's a workaround aside from manual SQL.  The inability to edit is an annoyance but the bigger problem I recall us having was that we couldn't distinguish 0/false vs. 1/true when having to query from the command line where we didn't have access to Query Browser in some customer environments.  (I'll look to see if there's another issue open for that.)  I actually changed all of the boolean columns in our schema from BIT to BOOLEAN to eliminate both issues.  It's not necessarily efficient but the impact on us was negligible.  BOOLEAN is the same as TINYINT(1) and BIT(1) used to also be the same as TINYINT(1) in older versions of MySQL.

To followup to my above comment about the command line I found it at:

http://bugs.mysql.com/bug.php?id=28422

Still in 1.2.17.

We are on the way to implement full functionality of MySQL Query Browser into MySQL Workbench. We won't fix this anymore.

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/

Bug #30005 is a duplicate of this bug here.

This bug is NOT repeatable with WB 5.2.10.