Bug #14198 SHOW COLUMNS FROM not correctly reporting BIT default values
Submitted: 21 Oct 2005 2:16 Modified: 18 Nov 2005 11:41
Reporter: John Lucas Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.13 OS:Windows (Win 2K)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[21 Oct 2005 2:16] John Lucas 
Description:
SHOW COLUMNS FROM tablename does not correctly report default values for BIT datatype.

How to repeat:
CREATE TABLE Table1
(
         ID1 Bigint NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID1),
         Column1 Bit NOT NULL DEFAULT 1 ,
         Column2 Bit NOT NULL DEFAULT 0 
)
TYPE=InnoDB

Then run:

SHOW COLUMNS FROM Table1 and the defaukt values are not correctly reflected
[21 Oct 2005 3:01] Paul DuBois 
SHOW CREATE TABLE output for the table has the same problem.
It appears that the BIT column default values are being displayed
in binary.  If you look at the output from these statements with
a hex editor, you can see that the values are 0x01 and 0x00.
[21 Oct 2005 8:15] Hartmut Holzgraefe 
SELECT from a table with BIT fields now also returns binary values instead of numeric:

 select * from Table1;
+-----+---------+---------+
| ID1 | Column1 | Column2 |
+-----+---------+---------+
|   1 |        |         |
|   2 |         |        |
|   3 |        |         |
+-----+---------+---------+

if this change in behavior is really intended (it also affects e.g. PHP where BIT values are now returned as binary instead of numeric strings) then i think we need more documentation on this 
than just what is now in http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

BIT[(M)]

A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

This data type was added in MySQL 5.0.3 for MyISAM, and extended in 5.0.5 to MEMORY, InnoDB, and BDB. Before 5.0.3, BIT is a synonym for TINYINT(1).
[15 Nov 2005 12:21] Sergei Golubchik 
the behaviour of SELECT and SHOW COLUMNS is intentional, but SHOW CREATE TABLE should still return valid SQL syntax.
[18 Nov 2005 11:41] Ramil Kalimullin 
SHOW CREATE TABLE produces valid sql statements, indeed.