count(1) 和 count(distinct xx)一起用的时候count(1)值不对

💬 StarRocks 用户问答
#1

在tableplus客户端和mysql命令客户端结果不对，在dbeaver和idea客户端没有问题
希望修复

select ymd, count(1), count(distinct deviceId)

from xxx

where ymd >= 20230802 and source = ‘96’

and hour(FROM_UNIXTIME(createdAt/1000)) in (0, 1, 2, 3, 4, 5)

group by ymd;

#2

升级到对应的最新 release

#3

您好，我们也碰到了类似的问题，不过好像和客户端版本没什么关系。
两条SQL，区别在于是否count (distinct) 了分桶key，count distinct了 bucket key之后，组合在一起的count(1)输出结果就不对，请问这可能是什么原因呀？

wecom-temp-99418-2dfc7c55db1ef92e07e17f5584a775b2
wecom-temp-99418-2dfc7c55db1ef92e07e17f5584a775b2550×820 89.7 KB

#5

请发下两个sql的执行计划，可以通过explain + sql 进行获取

#6

带count distinct的explain：
±--------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
±--------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:1: imp_date | 3: biz_id | 28: count | 29: count | 30: sum |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 6:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 1: imp_date, 3: biz_id |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 06 |
| UNPARTITIONED |
| |
| 5:AGGREGATE (merge finalize) |
| | output: count(28: count), sum(29: count), sum(30: sum) |
| | group by: 1: imp_date, 3: biz_id |
| | |
| 4:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| HASH_PARTITIONED: 1: imp_date, 3: biz_id |
| |
| 3:AGGREGATE (update serialize) |
| | STREAMING |
| | output: count(9: qimei36), sum(31: count), sum(32: sum) |
| | group by: 1: imp_date, 3: biz_id |
| | |
| 2:AGGREGATE (update finalize) |
| | output: count(1), sum(1) |
| | group by: 1: imp_date, 3: biz_id, 9: qimei36 |
| | |
| 1:Project |
| | <slot 1> : 1: imp_date |
| | <slot 3> : 3: biz_id |
| | <slot 9> : 9: qimei36 |
| | |
| 0:OlapScanNode |
| TABLE: dws_ug_qimei36_growth_dashboard_data_di |
| PREAGGREGATION: ON |
| PREDICATES: 1: imp_date = 20240225, 7: growth_weapon = ‘信息流’, 3: biz_id = 1001002001, coalesce(9: qimei36, ‘’) != ‘’ |
| partitions=1/34 |
| rollup: dws_ug_qimei36_growth_dashboard_data_di |
| tabletRatio=40/40 |
| tabletList=208449018,208449022,208449026,208449030,208449034,208449038,208449042,208449046,208449050,208449054 … |
| cardinality=19432075 |
| avgRowSize=4.0 |
| numNodes=0 |
±--------------------------------------------------------------------------------------------------------------------------------+

不带count distinct的explain:
---------------------------------------------------------------------------------------+
| Explain String |
±--------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:1: imp_date | 3: biz_id | 28: count | 29: sum |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 5:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 1: imp_date, 3: biz_id |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 05 |
| UNPARTITIONED |
| |
| 4:AGGREGATE (merge finalize) |
| | output: count(28: count), sum(29: sum) |
| | group by: 1: imp_date, 3: biz_id |
| | |
| 3:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 03 |
| HASH_PARTITIONED: 1: imp_date, 3: biz_id |
| |
| 2:AGGREGATE (update serialize) |
| | STREAMING |
| | output: count(1), sum(1) |
| | group by: 1: imp_date, 3: biz_id |
| | |
| 1:Project |
| | <slot 1> : 1: imp_date |
| | <slot 3> : 3: biz_id |
| | |
| 0:OlapScanNode |
| TABLE: dws_ug_qimei36_growth_dashboard_data_di |
| PREAGGREGATION: ON |
| PREDICATES: 1: imp_date = 20240225, 7: growth_weapon = ‘信息流’, 3: biz_id = 1001002001, coalesce(9: qimei36, ‘’) != ‘’ |
| partitions=1/34 |
| rollup: dws_ug_qimei36_growth_dashboard_data_di |
| tabletRatio=40/40 |
| tabletList=208449018,208449022,208449026,208449030,208449034,208449038,208449042,208449046,208449050,208449054 … |
| cardinality=19432075 |
| avgRowSize=4.0 |
| numNodes=0 |
±--------------------------------------------------------------------------------------------------------------------------------+

#7

看了一下相关的讨论，不知道这个pr会不会解决这个问题：https://github.com/StarRocks/starrocks/pull/24222，但是看修复是把count改成 -> count + sum，看起来本身这个错误的case本身就改成了count + sum，可能又不是修复这个的，不知道是不是我理解的有问题。

京ICP备2022026421号-1