MySQL Bugs: #17683: LOAD DATA INFILE loads wrong value for BIT columns

Bug #17683	LOAD DATA INFILE loads wrong value for BIT columns
Submitted:	24 Feb 2006 0:59	Modified:	6 Apr 2006 19:16
Reporter:	Ruby Muse	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Linux (Linux 2.4.31-ow1)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
I'm attempting to use LOAD DATA INFILE to load data into BIT columns. No matter what format I use, the result is not what I expect:
Contents of text file: 2
Resulting BIT(8) column value: 11010
Contents of text file: 0x02
Resulting BIT(8) column value: 11111111
Contents of text file: b'010'
Resulting BIT(8) column value: 11111111
Contents of text file: b\'010\'
Resulting BIT(8) column value: 11111111

How to repeat:
$ cat bit_test.txt
2
b'010'
b\'010\'
0x2

mysql> create table bit_test (b bit(8));
Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/home/dev/tmp/bit_test.txt' into table bit_test;
Query OK, 4 rows affected, 3 warnings (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 3

mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 110010   |
| 11111111 |
| 11111111 |
| 11111111 |
+----------+
4 rows in set (0.00 sec)

Suggested fix:
Assuming I'm just not using the right format in the text file, please add to the LOAD DATA INFILE documentation.

When I use PHP's pack() function to write binary data in the text file, LOAD DATA INFILE loads the proper values into the BIT field. It would be nice if the values could be written in hex or decimal, and if the LOAD DATA INFILE doc explicitly stated what format is expected.

Thank you for a problem report. Here is one way to solve the problem:

openxs@suse:~/dbs/5.0> cat /tmp/bit_test.txt
2
127
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 5.0.21

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> load data infile '/tmp/bit_test.txt'  into table bit_test (@var1) set b=
cast(@var1 as signed);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select bin(b+0) from bit_test; .txt'  into
+----------+
| bin(b+0) |
+----------+
| 10       |
| 1111111  |
+----------+
2 rows in set (0.00 sec)

But you are right - loading of BIT values should be explicitely documented. So, it is a verified documentation request.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).