Error 1292: Truncated incorrect BINARY value

[jingshanglu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Create Table: CREATE TABLE `PK_S_MULTI_29` (
  `COL1` bit(45) NOT NULL,
  `COL2` bit(45) NOT NULL,
  `COL3` bit(45) DEFAULT NULL,
  PRIMARY KEY (`COL1`,`COL2`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

insert into PK_S_MULTI_29 select * from PK_S_MULTI_29 where col1 = 0x3335313834333732303838383331 on duplicate key update col1 = 0x3335313834333732303;

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

mysql 8.0

insert successful

3. What did you see instead (Required)

ERROR 1292 (22007): Truncated incorrect BINARY value: '0x3335313834333732303838383331'

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v5.1.0-alpha-14-gd1b3e4711-dirty
Edition: Community
Git Commit Hash: d1b3e47111a34a2d3e9ca9aec94292f822c2f5a4
Git Branch: master
UTC Build Time: 2021-05-25 02:44:30
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[wzru]

Could you please provide some more complete SQL? I cannot repeat now

tidb> CREATE TABLE `PK_S_MULTI_29` (   `COL1` bit(45) NOT NULL,   `COL2` bit(45) NOT NULL,   `COL3` bit(45) DEFAULT NULL,   PRIMARY KEY (`COL1`,`COL2`) /*T![clustered_index] NONCLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)
tidb> insert into PK_S_MULTI_29_tmp select * from PK_S_MULTI_29_tmp where col1 = 0x3335313834333732303838383331 on duplicate key update col1 = 0x3335313834333732303;
ERROR 1146 (42S02): Table 'test.PK_S_MULTI_29_tmp' doesn't exist

[zanmato1984]

I tried to reproduce in my local MySQL/TiDB. This case doesn't seem to make sense:

MySQL 8.0:

mysql> insert into PK_S_MULTI_29 select * from PK_S_MULTI_29 where col1 = 0x3335313834333732303838383331 on duplicate key update col1 = 0x3335313834333732303;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

TiDB master:

mysql> insert into PK_S_MULTI_29 select * from PK_S_MULTI_29 where col1 = 0x3335313834333732303838383331 on duplicate key update col1 = 0x3335313834333732303;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

1. The insert won't take effect as the table is empty and the select clause returns empty set so there is nothing to insert (unless you missed supplying the statements that create some inital-filled in data).
2. On TiDB master, the result is the same as MySQL.

I'm closing it for now. Feel free to reopen if you find something new.