DML: insert with value of bit type. Not compatible with mysql #24900
Closed
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
Create Table: CREATE TABLE `IDT_MULTI22974_tmp` (
`COL1` char(20) DEFAULT NULL,
`COL2` bit(16) DEFAULT NULL,
`COL3` year(4) DEFAULT NULL,
`COL4` int DEFAULT NULL,
KEY `U_M_COL` (`COL1`(10)),
KEY `U_M_COL2` (`COL2`),
KEY `U_M_COL3` (`COL3`),
KEY `U_M_COL4` (`COL1`(10),`COL2`),
KEY `U_M_COL5` (`COL3`,`COL2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert ignore into IDT_MULTI22974_tmp(col2, col3) values(0xA155, 2020), (0x8FCA, 2076);
insert ignore into IDT_MULTI22974_tmp(col2, col3) values(30036, "2064"), (14991, "1929");
insert ignore into IDT_MULTI22974_tmp(col2, col3) values(NULL, NULL), (NULL, NULL);
insert ignore into IDT_MULTI22974_tmp(col2, col3) values(0x8500, 2152), (31160, 1901);
insert ignore into IDT_MULTI22974_tmp(col2, col3) values(0x3635313836, 2152), (0x3635313836, 2152);
insert ignore into IDT_MULTI22974_tmp(col2, col3) values(0x333830, 1901), (0x333830, 1901);
select * from IDT_MULTI22974_tmp;
2. What did you expect to see? (Required)
mysql
+--------------------------------------------------------------+------------+------+-------------+
| COL1 | COL2 | COL3 | COL4 |
+--------------------------------------------------------------+------------+------+-------------+
| NULL | 0xA155 | 2020 | NULL |
| NULL | 0x8FCA | 2076 | NULL |
| NULL | 0x7554 | 2064 | NULL |
| NULL | 0x3A8F | 1929 | NULL |
| NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL |
| NULL | 0x8500 | 2152 | NULL |
| NULL | 0x79B8 | 1901 | NULL |
| NULL | 0xFFFF | 2152 | NULL |
| NULL | 0xFFFF | 2152 | NULL |
| NULL | 0xFFFF | 1901 | NULL |
| NULL | 0xFFFF | 1901 | NULL |
+--------------------------------------------------------------+------------+------+-------------+
3. What did you see instead (Required)
tidb
+--------------------------------------------------------------+------------+------+-------------+
| COL1 | COL2 | COL3 | COL4 |
+--------------------------------------------------------------+------------+------+-------------+
| NULL | 0xA155 | 2020 | NULL |
| NULL | 0x8FCA | 2076 | NULL |
| NULL | 0x7554 | 2064 | NULL |
| NULL | 0x3A8F | 1929 | NULL |
| NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL |
| NULL | 0x8500 | 2152 | NULL |
| NULL | 0x79B8 | 1901 | NULL |
| NULL | 0x3836 | 2152 | NULL |
| NULL | 0x3836 | 2152 | NULL |
| NULL | 0x3830 | 1901 | NULL |
| NULL | 0x3830 | 1901 | NULL |
+--------------------------------------------------------------+------------+------+-------------+
mysql
+--------------------------------------------------------------+------------+------+-------------+
| COL1 | COL2 | COL3 | COL4 |
+--------------------------------------------------------------+------------+------+-------------+
| NULL | 0xA155 | 2020 | NULL |
| NULL | 0x8FCA | 2076 | NULL |
| NULL | 0x7554 | 2064 | NULL |
| NULL | 0x3A8F | 1929 | NULL |
| NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL |
| NULL | 0x8500 | 2152 | NULL |
| NULL | 0x79B8 | 1901 | NULL |
| NULL | 0xFFFF | 2152 | NULL |
| NULL | 0xFFFF | 2152 | NULL |
| NULL | 0xFFFF | 1901 | NULL |
| NULL | 0xFFFF | 1901 | NULL |
+--------------------------------------------------------------+------------+------+-------------+
4. What is your TiDB version? (Required)
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-beta.2-2904-ge92df204b
Edition: Community
Git Commit Hash: e92df204b53310029b7c32c5143ab6cebbf7ff18
Git Branch: master
UTC Build Time: 2021-05-14 11:16:04
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Activity