MySQL Bugs: #36239: Getting error while editing BIT value fields

Bug #36239	Getting error while editing BIT value fields
Submitted:	22 Apr 2008 4:38	Modified:	23 Apr 2014 9:58
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / C++	Severity:	S2 (Serious)
Version:	5.2	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	SUPESC

Description:
Hello,

It's impossible to edit MyISAM table with BIT value fields, while applying changes I'am getting these errors:

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

How to repeat:
You just need to have MyISAM type table, with column what has type: BIT(1)

after selecting this table You have b'0' values in COLUMN with bit type.  if you will press EDIT and will change this column value to b'1' and will press apply changes - you will get this error:

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:
seems like you need do not quote this values.

Thank you for the bug report. Could you please provide the create table
statement and the server version. Thanks in advance.

Sure here is crete statement:

CREATE TABLE  `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `test_field` bit(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Server version: 5.0.45-community-nt

And I also just found that when you are trying to ADD new value you will also get an error: 

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

here is also insert statement:

insert into `test` (`test_field`) VALUES (b'0');

Thank you for the feedback.

                      2 Query       SET NAMES utf8
                      2 Init DB     db3
                      2 Query       SELECT @@SQL_MODE
                      2 Query       SHOW FULL COLUMNS FROM `db3`.`test`
                      2 Query       SHOW CREATE TABLE `db3`.`test`
                      2 Query       SELECT * FROM test t
                      2 Quit
080425 14:01:37       1 Query       UPDATE `db3`.`test` SET `test_field`='b'1'' WHERE `id`='3'

This is a duplicate of bug #43670

This is NOT a duplicate. That other bug is about regression in displaying BIT values, while this is about inability to enter/edit them using grid.

I am also getting this. test case on QB 1.12.17 / 5.0.51a-3ubuntu5.4-log:

1) The table being used:
DROP TABLE IF EXISTS `test`.`my_table`;
CREATE TABLE  `test`.`my_table` (
  `id` int(11) NOT NULL,
  `flag` bit(1) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2) Add a new row via the GUI/Edit button and try to add a 0. The QB gives me a warning:
Out of range value adjusted for column 'flag' at row 1:

3) The server's general log says:
090720 15:19:53      13 Query       INSERT INTO `test`.`my_table` (`id`,`flag`) VALUES ('2','0')
                     13 Query       SHOW WARNINGS

4) If I try to insert b'0' I get a syntax error and this is what the server actually tries to run:
 2090720 15:25:56             13 Query       INSERT INTO `test`.`my_table` (`id`,`flag`) VALUES ('2','b'0'')

For BIT values the QB should actually generate:
 INSERT INTO `test`.`my_table` (`id`,`flag`) VALUES ('2',b'0')

Many thanks for writing a bug report.

We are on the way to implement full functionality of MySQL Query Browser into MySQL Workbench. This problem here is already fixed in Workbench.

More informations about MySQL Workbench you will find here:

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

Args. I made a mistake here.

Editing not already works.

Will be fixed in Workbench.

Verfied again with Workbench 5.2.6.

You just will get an error when you try to insert values into a bit field.

Fixed WB part of the bug (odd quotation). Though connector (mysqlcppconn) still needs a fix for fetching of BIT values.

although the problem in WB is already fixed (fix will be in 5.2.8) we will need to wait for the fetching bit values in connector is fixed properly to really close that bug as "fixed". 
thats why i'm leaving that still open

A patch has been committed to the Connector/C++ repository. You should be able to fetch BIT columns as (unsigned) integers. Please verify if it works for you. 

Thanks, 
Ulf

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Closed-fixed