MySQL Bugs: #43646: Default values for bit(1) fields displayed incorrectly

Bug #43646	Default values for bit(1) fields displayed incorrectly
Submitted:	14 Mar 2009 9:47	Modified:	16 Apr 2009 18:36
Reporter:	Arcadiy Fedushin	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	1.1.2 Alpha	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Visual Studio plugin bit binary default values

Description:
Default values for bit(1) fields are displayed as pseudo-graphics, smth like ((char)1).
Subsequent queries related to those fields fail until user sets correct default value (0 or 1) manually.
In bottom Tool panel \ Column details \ Default value incorrect default values are not displayed at all.

Very likely the problem relates to binary defaults of any bit(N) field.

How to repeat:
1. Open Microsoft Visual Studio 2005 with 'MySQL Visual Studio Plugin' installed,
2. In 'Alter table' mode set default value for bit(1) field to 0 or 1,
3. Alter table structure by Ctrl+S,

Pseudo-graphics appears. Default value in 'Column details' disappears.

I think this is espected behaviour!

CREATE TABLE `bittest` (                 
           `id` INT(11) NOT NULL AUTO_INCREMENT,  
           `txt` VARCHAR(10) DEFAULT NULL,        
           `bin1` BIT(1) DEFAULT NULL,            
           `bin2` BIT(8) DEFAULT NULL,            
           PRIMARY KEY (`id`)                     
         ) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `bittest`(`id`,`txt`,`bin1`,`bin2`) VALUES ( NULL,'a',b'1',b'01111000');

SELECT * FROM bittest;

/* returns

    id  txt     bin1    bin2  
------  ------  ------  ------
     1  a              x     
     

or 

    id  txt     bin1    bin2  
------  ------  ------  ------
     1  a       <symbol>  x      
     
.. all depending on how the client environment treats a '1' binary string. */    

.. AND note that b'01111000' is 'x' ASCII character and that is what is displayed! And the ASCII-character corresponding b'1' is a non-printable character.

http://dev.mysql.com/doc/refman/5.0/en/bit-field-values.html
"Bit values are returned as binary values. To display them in printable form, add 0 or use a conversion function such as BIN()." And also you will need to enter them a binary values (the string " '1' " is not the same as the binary number " b'1' ".

That said using the MySQL BIT type is an annoyance with any graphical environment (at least). A TINYINT is most often a better option.

A good summary of problems here:
http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/

Peter
(not a MySQL person)

The summary of problem you gave is VERY interesting, thank you!
I met similar problems - to be read (by PHP) a bit field needs conversion to UNSIGNED inside MySQL query...

Let's hope BIT bugs & problems in MySQL will be solved.

For this it's better to improve working with BIT than just to say 'BIT type is an annoyance'. Why not to support correct operating with BIT in MSVS GUI? And start with bit(1), as the most required.

Hi Arcadiy and thanks for your report.

You do not file feature request against plug-in, it's integral part of connector now. So please change the Version field so I know what to test against.

Probably duplicate of your Bug#43653. See my comments there.

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".