insert ignore cast invalid data to bit is not compatible with MySQL

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t03(a int, b bit(1), primary key (a, b));
insert ignore into t03 values (1, 0x30);

2. What did you expect to see? (Required)

mysql> insert ignore into t03 values (1, 0x30);
Query OK, 1 row affected, 1 warning (0.09 sec)

mysql> select * from t03;
+---+------+
| a | b    |
+---+------+
| 1 | 0x01 |
+---+------+
1 row in set (0.14 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.07 sec)

3. What did you see instead (Required)

mysql> insert ignore into t03 values (1, 0x30);
Query OK, 1 row affected, 1 warning (0.26 sec)

mysql> select * from t03;
+---+------+
| a | b    |
+---+------+
| 1 | 0x00 |
+---+------+
1 row in set (0.10 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-beta.2-2707-g6889ab44a-dirty
Edition: Community
Git Commit Hash: 6889ab44a5bf21ec0465cd84a4baf68072f1bd3c
Git Branch: master
UTC Build Time: 2021-04-22 13:12:44
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.25 sec)

[ichn-hu]

@aytrack this incompatibility is caused by invalid parameter, and there is no apparent reason why b should be 0x01, so I would say this should be a minor bug, please help change the label if you agree.

[jebter]

TiDB root@127.0.0.1:test> create table t03(a int, b bit(1), primary key (a, b));
Query OK, 0 rows affected
Time: 0.076s
TiDB root@127.0.0.1:test> insert ignore into t03 values (1, 0x30);
Query OK, 1 row affected
Time: 0.002s
TiDB root@127.0.0.1:test> select * from t03;
+---+------+
| a | b    |
+---+------+
| 1 | 0x01 |
+---+------+

1 row in set
Time: 0.004s
TiDB root@127.0.0.1:test> select tidb_version() \G
***************************[ 1. row ]***************************
tidb_version() | Release Version: v8.1.0
Edition: Community
Git Commit Hash: 945d07c5d5c7a1ae212f6013adfb187f2de24b23
Git Branch: HEAD
UTC Build Time: 2024-05-21 03:52:40
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

1 row in set
Time: 0.001s

Please try the latest version.I will close it, if there are any updates, you can reopen it.