bug: LOAD BIT DATA, the results are inconsistent

[davidshiz]

Have you read the Contributing Guidelines on issues?

• [X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Describe the problem

After OUTFILE the BIT data, then LOAD the data, the results are inconsistent

mysql> CREATE TABLE `bit_test` (
    ->   `a` bit(7) DEFAULT NULL,
    ->   `b` bit(9) DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO bit_test VALUES (B'000001',B'1101100');
Query OK, 1 row affected (0.01 sec)

mysql> select a+0,b+0 from bit_test;
+------+------+
| a+0  | b+0  |
+------+------+
|    1 |  108 |
+------+------+
1 row in set (0.00 sec)

mysql> select * into outfile '~/bit_test_tbl' from bit_test;
Query OK, 1 row affected (0.00 sec)

root@ub01:~# cat bit_test_tbl
1;108

mysql> LOAD DATA LOCAL infile '~/bit_test_tbl' into table bit_test fields terminated by ';';
Query OK, 1 row affected, 1 warning (0.10 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql> select a+0,b+0 from bit_test;
+------+---------+
| a+0  | b+0     |
+------+---------+
|    1 |     108 |
|   49 | 3223608 |
+------+---------+
2 rows in set (0.00 sec)

Expected behavior

No response

How To Reproduce

CREATE TABLE `bit_test` (
  `a` bit(7) DEFAULT NULL,
  `b` bit(9) DEFAULT NULL
);
INSERT INTO bit_test VALUES (B'000001',B'1101100');
select * into outfile '~/bit_test_tbl' from bit_test;
LOAD DATA LOCAL infile '~/bit_test_tbl' into table bit_test fields terminated by ';'

Environment

root@ub01:~# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 67b5c2073
        Last commit time: Date:   Fri Jan 6 15:40:19 2023 +0800
        Build time: Date: Mon Jan  9 23:16:55 CST 2023
root@ub01:~# cat /etc/issue
Ubuntu 20.04.5 LTS \n \l

Are you interested in submitting a PR to solve the problem?

• [ ] Yes, I will!

[hustjieke]

It seems we need to convert numeric data to binary derectly.

[TomeC]

assigned me

[hustjieke]

Meet any problems here? @TomeC The method on load bit data is different with other types, ref: https://github.com/stoneatom/stonedb/blob/stonedb-5.7-dev/mysql-test/suite/tianmu/t/bit_type.test#L115

Ref from mysql docs: https://dev.mysql.com/doc/refman/8.0/en/load-data.html

BIT values cannot be loaded directly using binary notation (for example, b'011010'). To work around this, use the SET clause to strip off the leading b' and trailing ' and perform a base-2 to base-10 conversion so that MySQL loads the values into the BIT column properly:

$> cat /tmp/bit_test.txt
b'10'
b'1111111'
$> mysql test
mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
       INTO TABLE bit_test (@var1)
       SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-3), 2, 10) AS UNSIGNED);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT BIN(b+0) FROM bit_test;
+----------+
| BIN(b+0) |
+----------+
| 10       |
| 1111111  |
+----------+
2 rows in set (0.00 sec)

For BIT values in 0b binary notation (for example, 0b011010), use this SET clause instead to strip off the leading 0b:

SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-2), 2, 10) AS UNSIGNED)

[TomeC]

Meet any problems here? @TomeC The method on load bit data is different with other types, ref: https://github.com/stoneatom/stonedb/blob/stonedb-5.7-dev/mysql-test/suite/tianmu/t/bit_type.test#L115

Ref from mysql docs: https://dev.mysql.com/doc/refman/8.0/en/load-data.html

BIT values cannot be loaded directly using binary notation (for example, b'011010'). To work around this, use the SET clause to strip off the leading b' and trailing ' and perform a base-2 to base-10 conversion so that MySQL loads the values into the BIT column properly:

$> cat /tmp/bit_test.txt
b'10'
b'1111111'
$> mysql test
mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
       INTO TABLE bit_test (@var1)
       SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-3), 2, 10) AS UNSIGNED);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT BIN(b+0) FROM bit_test;
+----------+
| BIN(b+0) |
+----------+
| 10       |
| 1111111  |
+----------+
2 rows in set (0.00 sec)

For BIT values in 0b binary notation (for example, 0b011010), use this SET clause instead to strip off the leading 0b:

SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-2), 2, 10) AS UNSIGNED)

Thank you for the document,The current progress:

1. In debug mode, the problem of system crash caused by select ... into outfile has been solved.
2. The problem of importing data errors is still reading the code. It will take some time to resolve.