nextdreamblue opened a new issue, #14811: URL: https://github.com/apache/doris/issues/14811
### 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? 相同的数据，使用agg table replace和unique table进行查询返回的结果不同 ``` MySQL [test]> select l_orderkey,l_partkey,l_suppkey,l_linenumber,l_comment from lineitem_agg 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_63 | +------------+-----------+-----------+--------------+-----------+ 1 row in set (0.02 sec) 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.02 sec) ``` ### What You Expected? 返回相同值 ### How to Reproduce? 创建两个表 ``` CREATE TABLE `lineitem_agg` ( `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) REPLACE NULL, `l_extendedprice` decimal(15, 2) REPLACE NULL, `l_discount` decimal(15, 2) REPLACE NULL, `l_tax` decimal(15, 2) REPLACE NULL, `l_returnflag` char(1) REPLACE NULL, `l_linestatus` char(1) REPLACE NULL, `l_shipdate` date REPLACE NULL, `l_commitdate` date REPLACE NULL, `l_receiptdate` date REPLACE NULL, `l_shipinstruct` char(25) REPLACE NULL, `l_shipmode` char(10) REPLACE NULL, `l_comment` varchar(44) REPLACE NULL ) ENGINE=OLAP AGGREGATE 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", "disable_auto_compaction" = "false" ); CREATE TABLE `lineitem_unique` ( `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", "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_agg 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_63 | +------------+-----------+-----------+--------------+-----------+ 1 row in set (0.02 sec) 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.02 sec) ``` 得到不一样的结果 ### Anything Else? _No response_ ### Are you willing to submit PR? - [ ] 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: commits-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
