nextdreamblue opened a new issue, #14994: URL: https://github.com/apache/doris/issues/14994
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) 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? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
