EmmyMiao87 opened a new issue #6726: URL: https://github.com/apache/incubator-doris/issues/6726
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues. ### Version 0.14.13 ### What's Wrong? The table when has colocated group but return duplicate results with group by clause. ``` MySQL [test]> SELECT content_id, content_type, channel_first_level, ifnull(SUM(page_show),0) AS `PageShow` FROM dws_content_stats WHERE p_date >= '2021-09-22' AND content_id IN (1) AND content_type IN (1) GROUP BY channel_first_level, channel_second_level, content_id, content_type; +------------+--------------+---------------------+----------+ | content_id | content_type | channel_first_level | PageShow | +------------+--------------+---------------------+----------+ | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | +------------+--------------+---------------------+----------+ 2 rows in set (0.059 sec) ``` ### What You Expected? ``` MySQL [test]> SELECT content_id, content_type, channel_first_level, ifnull(SUM(page_show),0) AS `PageShow` FROM dws_content_stats WHERE p_date >= '2021-09-22' AND content_id IN (1) AND content_type IN (1) GROUP BY channel_first_level, channel_second_level, content_id, content_type; +------------+--------------+---------------------+----------+ | content_id | content_type | channel_first_level | PageShow | +------------+--------------+---------------------+----------+ | 1 | 1 | 1 | 2 | +------------+--------------+---------------------+----------+ 1 row in set (0.041 sec) ``` ### How to Reproduce? 1. create a colocate table with more than 1 replication number and more than 1 partitions 2. ``` CREATE TABLE `dws_content_stats` ( `p_date` date NULL COMMENT "入库时间", `content_type` int(11) NULL COMMENT "内容类型", `content_id` bigint(20) NULL COMMENT "内容 ID", `channel_first_level` int(11) NULL COMMENT "一级渠道", `channel_second_level` int(11) NULL COMMENT "一级渠道", `page_show` bigint(20) SUM NULL COMMENT "PageShow" ) ENGINE=OLAP AGGREGATE KEY(`p_date`, `content_type`, `content_id`, `channel_first_level`, `channel_second_level`) COMMENT "轻度内容聚合" PARTITION BY RANGE(`p_date`) (PARTITION p20210922 VALUES [('2021-09-22'), ('2021-09-23')), PARTITION p20210923 VALUES [('2021-09-23'), ('2021-09-24'))) DISTRIBUTED BY HASH(`content_type`, `content_id`) BUCKETS 32 PROPERTIES ( "replication_allocation" = "tag.location.default: 2", "colocate_with" = "content_group_32", "dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "DAY", "dynamic_partition.time_zone" = "Asia/Shanghai", "dynamic_partition.start" = "-2147483648", "dynamic_partition.end" = "4", "dynamic_partition.prefix" = "p", "dynamic_partition.replication_allocation" = "tag.location.default: 3", "dynamic_partition.buckets" = "32", "dynamic_partition.create_history_partition" = "false", "dynamic_partition.history_partition_num" = "-1", "dynamic_partition.hot_partition_num" = "0", "in_memory" = "false", "storage_format" = "V2" ); ``` 2. group by colocated column ``` MySQL [test]> SELECT content_id, content_type, channel_first_level, ifnull(SUM(page_show),0) AS `PageShow` FROM dws_content_stats WHERE p_date >= '2021-09-22' AND content_id IN (1) AND content_type IN (1) GROUP BY channel_first_level, channel_second_level, content_id, content_type; +------------+--------------+---------------------+----------+ | content_id | content_type | channel_first_level | PageShow | +------------+--------------+---------------------+----------+ | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | +------------+--------------+---------------------+----------+ 2 rows in set (0.059 sec) ``` 3. duplicate result ### 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: 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
