incorrect count distinct value with join

[kaijianding]

Steps to reproduce the behavior (Required)

pt is partition key, type is date
spu_id type is bigint

Correct reuslt is: 5651249

SELECT 
    count(distinct spu_id)
FROM 
    t1 a
WHERE a.pt = '2024-08-08'
	AND a.pt >= '2023-12-16'
+------------------------+
| count(DISTINCT spu_id) |
+------------------------+
|                5651249 |
+------------------------+

SELECT 
    count(distinct spu_id)
FROM 
   t1 a
    cross JOIN (
		SELECT date('2024-08-08') as dim_date1
	) d
where a.pt = d.dim_date1
	AND a.pt >= '2023-12-16'
+------------------------+
| count(DISTINCT spu_id) |
+------------------------+
|                3840110 |
+------------------------+

SELECT 
    count(distinct spu_id)
FROM 
    t1 a
    inner JOIN (
		SELECT date('2024-08-08') as dim_date1
	) d
where a.pt = d.dim_date1
	AND a.pt >= '2023-12-16'
+------------------------+
| count(DISTINCT spu_id) |
+------------------------+
|                3840110 |
+------------------------+

Expected behavior (Required)

the result in consistent

Real behavior (Required)

Result is incorrect with join

StarRocks version (Required)

• You can get the StarRocks version by executing SQL select current_version()
  3.3

[packy92]

please provide the explain info of these sqls

[chenminghua8]

The issue seems to be related to distributed distinct, but it is not easy to reproduce in a single-machine environment.

[chenminghua8]

@kaijianding Execute the following statement and see what the execution plan is：
explain SELECT
count(distinct spu_id)
FROM
t1 a
inner JOIN (
SELECT date('2024-08-08') as dim_date1
) d
where a.pt = d.dim_date1
AND a.pt >= '2023-12-16'

[stdpain]

seems closed by #51150