[Bug] (merge-on-write) get many rows with same key from merge-on-write table #14994
Description
Search before asking
- I had searched in the issues and found no similar issues.
Version
master
What's Wrong?
导入一批包含相同key的数据，在普通unique table下，只返回一条，但是在打开了merge-on-write的unique table，却返回了两条
例子：
普通的unique table
MySQL [test]> select l_orderkey,l_partkey,l_suppkey,l_linenumber,l_comment from lineitem_unique where l_orderkey=5970000001 and l_partkey=75814607 and l_suppkey=5814608 and l_linenumber=1;+------------+-----------+-----------+--------------+-----------+
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_comment |
+------------+-----------+-----------+--------------+-----------+
| 5970000001 | 75814607 | 5814608 | 1 | 00_100 |
+------------+-----------+-----------+--------------+-----------+
1 row in set (0.04 sec)
MySQL [test]> select l_orderkey,l_partkey,l_suppkey,l_linenumber,l_comment from lineitem_unique where l_orderkey in (5970000001,5979996005) and l_partkey in (75814607,197922326) and l_suppkey in (5814608,5422384) and l_linenumber in (1,4);
+------------+-----------+-----------+--------------+-----------+
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_comment |
+------------+-----------+-----------+--------------+-----------+
| 5970000001 | 75814607 | 5814608 | 1 | 00_100 |
| 5979996005 | 197922326 | 5422384 | 4 | 00_100 |
+------------+-----------+-----------+--------------+-----------+
2 rows in set (0.02 sec)
结果正确
merge-on-write的unique table
MySQL [test]> select l_orderkey,l_partkey,l_suppkey,l_linenumber,l_comment from lineitem_write where l_orderkey=5970000001 and l_partkey=75814607 and l_suppkey=5814608 and l_linenumber=1;
+------------+-----------+-----------+--------------+-----------+
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_comment |
+------------+-----------+-----------+--------------+-----------+
| 5970000001 | 75814607 | 5814608 | 1 | 00_61 |
| 5970000001 | 75814607 | 5814608 | 1 | 00_100 |
+------------+-----------+-----------+--------------+-----------+
2 rows in set (0.01 sec)
MySQL [test]> select l_orderkey,l_partkey,l_suppkey,l_linenumber,l_comment from lineitem_write where l_orderkey in (5970000001,5979996005) and l_partkey in (75814607,197922326) and l_suppkey in (5814608,5422384) and l_linenumber in (1,4);
+------------+-----------+-----------+--------------+-----------+
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_comment |
+------------+-----------+-----------+--------------+-----------+
| 5970000001 | 75814607 | 5814608 | 1 | 00_61 |
| 5979996005 | 197922326 | 5422384 | 4 | 01_61 |
| 5970000001 | 75814607 | 5814608 | 1 | 00_100 |
| 5979996005 | 197922326 | 5422384 | 4 | 00_100 |
+------------+-----------+-----------+--------------+-----------+
4 rows in set (0.00 sec)
结果错误，有主键相同的重复数据
What You Expected?
查询merge-on-write的unique table，主键相同的数据只返回一行
How to Reproduce?
创建merge-on-write的unique table
CREATE TABLE `lineitem_write` (
`l_orderkey` bigint(20) NULL,
`l_partkey` int(11) NULL,
`l_suppkey` int(11) NULL,
`l_linenumber` int(11) NULL,
`l_quantity` decimal(15, 2) NULL,
`l_extendedprice` decimal(15, 2) NULL,
`l_discount` decimal(15, 2) NULL,
`l_tax` decimal(15, 2) NULL,
`l_returnflag` char(1) NULL,
`l_linestatus` char(1) NULL,
`l_shipdate` date NULL,
`l_commitdate` date NULL,
`l_receiptdate` date NULL,
`l_shipinstruct` char(25) NULL,
`l_shipmode` char(10) NULL,
`l_comment` varchar(44) NULL
) ENGINE=OLAP
UNIQUE KEY(`l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"disable_auto_compaction" = "false"
);
导入数据1：
数据文件：
https://doris-agg-table-bug-1301087413.cos.ap-beijing.myqcloud.com/agg_table_bug/test_agg_table_data_1
这个数据中包含100w数据，其中每隔10000条表，有1条主键为 " 5970999942|130195832|5195859|1 " 的数据，并且l_comment值按序号递增的数据，一种有100条主键相同l_comment不同的行，以及另一组主键为" 5979996005|197922326|5422384|4 "的多行数据。
然后执行如下sql
MySQL [test]> select l_orderkey,l_partkey,l_suppkey,l_linenumber,l_comment from lineitem_write where l_orderkey=5970000001 and l_partkey=75814607 and l_suppkey=5814608 and l_linenumber=1;
+------------+-----------+-----------+--------------+-----------+
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_comment |
+------------+-----------+-----------+--------------+-----------+
| 5970000001 | 75814607 | 5814608 | 1 | 00_61 |
| 5970000001 | 75814607 | 5814608 | 1 | 00_100 |
+------------+-----------+-----------+--------------+-----------+
2 rows in set (0.01 sec)
MySQL [test]> select l_orderkey,l_partkey,l_suppkey,l_linenumber,l_comment from lineitem_write where l_orderkey in (5970000001,5979996005) and l_partkey in (75814607,197922326) and l_suppkey in (5814608,5422384) and l_linenumber in (1,4);
+------------+-----------+-----------+--------------+-----------+
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_comment |
+------------+-----------+-----------+--------------+-----------+
| 5970000001 | 75814607 | 5814608 | 1 | 00_61 |
| 5979996005 | 197922326 | 5422384 | 4 | 01_61 |
| 5970000001 | 75814607 | 5814608 | 1 | 00_100 |
| 5979996005 | 197922326 | 5422384 | 4 | 00_100 |
+------------+-----------+-----------+--------------+-----------+
4 rows in set (0.00 sec)
Anything Else?
No response
Are you willing to submit PR?
- Yes I am willing to submit a PR!
Code of Conduct
- I agree to follow this project's Code of Conduct