[Bug]: Fail to select the record after inserting it

[DanielZhangQD]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

1.2-dev

Commit ID

v1.2.3-f01f07a2a-2024-09-10

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

Insert a record and select it but get empty result:

CREATE TABLE `serial_numbers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `biz_type` TINYINT DEFAULT NULL,
  `namespace` VARCHAR(64) DEFAULT NULL,
  `sn` BIGINT DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_biz_ns` (`biz_type`,`namespace`)
)

mysql> INSERT INTO `billing`.`serial_numbers` (`biz_type`, `namespace`, `sn`) VALUES (4, '2024091117', 1) ON DUPLICATE KEY UPDATE `sn` = `sn` + 1;
Query OK, 1 row affected (0.12 sec)

mysql> SELECT * FROM `billing`.`serial_numbers` WHERE `biz_type` = 4 and `namespace` = '2024091117';
Empty set (0.06 sec)

mysql> SELECT * FROM `billing`.`serial_numbers` WHERE `biz_type` = 4 order by namespace desc limit 1;
+--------+----------+------------+------+
| id     | biz_type | namespace  | sn   |
+--------+----------+------------+------+
| 562075 |        4 | 2024091117 |    1 |
+--------+----------+------------+------+
1 row in set (0.04 sec)

Expected Behavior

The record is returned

Steps to Reproduce

See detail in `Actual Behavior`

Additional information

Cloud dev

[DanielZhangQD]

mysql> explain analyze SELECT * FROM `billing`.`serial_numbers` WHERE `biz_type` = 4 and `namespace` = '2024091117';
+--------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                              |
|   Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=0 InputSize=0bytes OutputSize=0bytes MemorySize=0bytes               |
|   ->  Join                                                                                                                           |
|         Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=0 InputSize=8bytes OutputSize=0bytes MemorySize=0bytes         |
|         Join Type: INDEX                                                                                                             |
|         Join Cond: (serial_numbers.id = __mo_index_unique_0191dece-3d3f-7834-ac29-0774883c7273.__mo_index_pri_col)                   |
|         Runtime Filter Build: #[-1,0]                                                                                                |
|         ->  Table Scan on billing.serial_numbers [ForceOneCN]                                                                        |
|               Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=0 InputSize=0bytes OutputSize=0bytes MemorySize=0bytes   |
|               Filter Cond: (serial_numbers.namespace = '2024091117'), (serial_numbers.biz_type = 4)                                  |
|               Block Filter Cond: (serial_numbers.namespace = '2024091117')                                                           |
|               Runtime Filter Probe: serial_numbers.id                                                                                |
|         ->  Table Scan on billing.__mo_index_unique_0191dece-3d3f-7834-ac29-0774883c7273 [ForceOneCN]                                |
|               Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=32bytes OutputSize=8bytes MemorySize=33bytes |
|               Filter Cond: (__mo_index_unique_0191dece-3d3f-7834-ac29-0774883c7273.__mo_index_idx_col = '(F2024091117 ')          |
|               Block Filter Cond: (__mo_index_unique_0191dece-3d3f-7834-ac29-0774883c7273.__mo_index_idx_col = '(F2024091117 ')    |
+--------------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.18 sec)

[ouyuanning]

根本原因是：
对于insert on duplicate key中，如果pk是自增列的话，写入隐藏表（unique key和secondary key隐藏表）的key会是null （因为这个时候 自增列还没填充）
于是隐藏表的数据会不对

[ouyuanning]

先处理DML重构

[ouyuanning]

先处理DML重构

[ouyuanning]

DML重构版本合并后处理

[ouyuanning]

DML重构版本合并后处理

[allengaoo]

just did a short testing in 2.0.0 , looks this issue is not there anymore.
mysql> select version();
+-------------------------+
| version() |
+-------------------------+
| 8.0.30-MatrixOne-v2.0.0 |
+-------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE serial_numbers (
-> id bigint NOT NULL AUTO_INCREMENT,
-> biz_type tinyint DEFAULT NULL,
-> namespace varchar(64) DEFAULT NULL,
-> sn bigint DEFAULT NULL,
-> PRIMARY KEY (id), //用一个自增列做主键
-> UNIQUE KEY unq_biz_ns (biz_type,namespace)) //这两个列是业务层面的唯一键。
->
-> ;
Query OK, 0 rows affected (0.01 sec)

mysql> desc serial_numbers;
+-----------+-------------+------+------+---------+----------------+---------+
| Field | Type | Null | Key | Default | Extra | Comment |
+-----------+-------------+------+------+---------+----------------+---------+
| id | BIGINT(64) | NO | PRI | NULL | auto_increment | |
| biz_type | TINYINT(8) | YES | MUL | NULL | | |
| namespace | VARCHAR(64) | YES | | NULL | | |
| sn | BIGINT(64) | YES | | NULL | | |
+-----------+-------------+------+------+---------+----------------+---------+
4 rows in set (0.01 sec)

mysql> INSERT INTO serial_numbers (biz_type, namespace, sn) VALUES (4, '2024091117', 1) ON DUPLICATE KEY UPDATE sn = sn + 1;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO serial_numbers (biz_type, namespace, sn) VALUES (4, '2024091117', 1) ON DUPLICATE KEY UPDATE sn = sn + 1;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from serial_numbers;
+------+----------+------------+------+
| id | biz_type | namespace | sn |
+------+----------+------------+------+
| 1 | 4 | 2024091117 | 2 |
+------+----------+------------+------+
1 row in set (0.01 sec)

mysql> SELECT * FROM serial_numbers WHERE biz_type = 4;
+------+----------+------------+------+
| id | biz_type | namespace | sn |
+------+----------+------------+------+
| 1 | 4 | 2024091117 | 2 |
+------+----------+------------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM serial_numbers WHERE biz_type = 4 order by namespace desc limit 1;
+------+----------+------------+------+
| id | biz_type | namespace | sn |
+------+----------+------------+------+
| 1 | 4 | 2024091117 | 2 |
+------+----------+------------+------+
1 row in set (0.00 sec)

@aunjgr @DanielZhangQD Please double check.

[ouyuanning]

肯定是存在的，高老师测试的部分，我估计是

SELECT * FROM serial_numbers WHERE biz_type = 4;  这个sql要改一下，因为要用到 隐藏表加速 的时候，才能复现的。

改成类似：

SELECT * FROM `billing`.`serial_numbers` WHERE `biz_type` = 4 and `namespace` = '2024091117';

另外可以通过explain看一下，有没有跟隐藏表的join，只有有跟隐藏表的join，问题才会看出来。