nextdreamblue commented on issue #14726:
URL: https://github.com/apache/doris/issues/14726#issuecomment-1336368053
复现流程：
建表sql：
```sql
CREATE TABLE `lineitem` (
`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"
);
```
导入数据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 "的多行数据。
按AGGREGATE表的逻辑，最终查询主键相同的行时，应该指返回一行，其他相同主键的行会聚合
但是导入数据后执行如下sql：
```
MySQL [test]> select l_orderkey,l_partkey,l_suppkey,l_linenumber,l_comment
from lineitem10 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 |
| 5970000001 | 75814607 | 5814608 | 1 | 00_63 |
+------------+-----------+-----------+--------------+-----------+
2 rows in set (0.01 sec)
MySQL [test]>
MySQL [test]>
MySQL [test]> select l_orderkey,l_partkey,l_suppkey,l_linenumber,l_comment
from lineitem10 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 |
| 5970000001 | 75814607 | 5814608 | 1 | 00_63 |
| 5979996005 | 197922326 | 5422384 | 4 | 00_100 |
| 5979996005 | 197922326 | 5422384 | 4 | 01_63 |
+------------+-----------+-----------+--------------+-----------+
4 rows in set (0.01 sec)
```
有主键完全相同的行
导入数据2：
数据文件：
https://doris-agg-table-bug-1301087413.cos.ap-beijing.myqcloud.com/agg_table_bug/test_agg_table_data_2
与1类似，有100条主键为 " 5970999942|130195832|5195859|1 " 和"
5979996005|197922326|5422384|4 " 的数据
然后继续执行上边的查询sql
```
MySQL [test]> select l_orderkey,l_partkey,l_suppkey,l_linenumber,l_comment
from lineitem10 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_200 |
| 5970000001 | 75814607 | 5814608 | 1 | 00_163 |
| 5970000001 | 75814607 | 5814608 | 1 | 00_63 |
+------------+-----------+-----------+--------------+-----------+
3 rows in set (0.02 sec)
MySQL [test]> select l_orderkey,l_partkey,l_suppkey,l_linenumber,l_comment
from lineitem10 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_200 |
| 5970000001 | 75814607 | 5814608 | 1 | 00_163 |
| 5970000001 | 75814607 | 5814608 | 1 | 00_63 |
| 5979996005 | 197922326 | 5422384 | 4 | 01_100 |
| 5979996005 | 197922326 | 5422384 | 4 | 01_63 |
| 5979996005 | 197922326 | 5422384 | 4 | 01_63 |
+------------+-----------+-----------+--------------+-----------+
6 rows in set (0.02 sec)
```
同样结果不对
--
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
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
