[Bug] inconsistent results between select distinct and count(distinct) on multi-column combinations

[dh-cloud]

Search before asking

• I had searched in the issues and found no similar issues.

Version

4.0

What's Wrong?

We observed inconsistent behavior between SELECT DISTINCT and COUNT(DISTINCT) when operating on multi-column combinations

What You Expected?

SELECT DISTINCT returns x rows (correct).
COUNT(DISTINCT) returns x

How to Reproduce?

Steps to Reproduce
Create a table with nullable columns and duplicate combinations:

CREATE TABLE test_distinct (
col1 VARCHAR(10),
col2 VARCHAR(10)
);
INSERT INTO test_distinct VALUES
('A', 'X'), ('A', 'X'), -- Duplicate
('B', NULL), ('B', NULL), -- NULL duplicates
('C', 'Y'), (NULL, 'Z'), (NULL, NULL);
Execute the following queries:

-- Returns 5 rows (all distinct combinations, including NULLs)
SELECT DISTINCT col1, col2 FROM test_distinct;

-- Returns 2 (incorrect, should match the 5 unique combinations)
SELECT COUNT(DISTINCT col1, col2) FROM test_distinct;
Expected vs Actual Behavior
Expected:
Both queries should agree on the number of distinct combinations (5 in this case)

Actual:

SELECT DISTINCT returns 5 rows (correct).
COUNT(DISTINCT) returns 2 (incorrect, likely due to NULL handling or optimization bugs).

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[yiguolei]

Could you please provide the query profile for these queries？

[dh-cloud]

Could you please provide the query profile for these queries？

mysql> explain SELECT COUNT(DISTINCT col1, col2) FROM test;
+------------------------------------------------------------------------------+
| Explain String(Nereids Planner) |
+------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| COUNT(DISTINCT col1, col2)[#7] |
| PARTITION: UNPARTITIONED |
| |
| HAS_COLO_PLAN_NODE: true |
| |
| VRESULT SINK |
| MYSQL_PROTOCAL |
| |
| 6:VAGGREGATE (merge finalize)(147) |
| | output: count(partial_count(if(col1 IS NULL, NULL, col2))[#6])[#7] |
| | group by: |
| | sortByGroupKey:false |
| | cardinality=1 |
| | distribute expr lists: |
| | |
| 5:VEXCHANGE |
| offset: 0 |
| distribute expr lists: |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: col1[#2], col2[#3] |
| |
| HAS_COLO_PLAN_NODE: true |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 05 |
| UNPARTITIONED |
| |
| 4:VAGGREGATE (update serialize)(139) |
| | output: partial_count(if(col1[#4] IS NULL, NULL, col2[#5]))[#6] |
| | group by: |
| | sortByGroupKey:false |
| | cardinality=1 |
| | distribute expr lists: col1[#4], col2[#5] |
| | |
| 3:VAGGREGATE (merge finalize)(135) |
| | group by: col1[#2], col2[#3] |
| | sortByGroupKey:false |
| | cardinality=5 |
| | distribute expr lists: col1[#2], col2[#3] |
| | |
| 2:VEXCHANGE |
| offset: 0 |
| distribute expr lists: |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: RANDOM |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: col1[#2], col2[#3] |
| |
| 1:VAGGREGATE (update finalize)(127) |
| | STREAMING |
| | group by: col1[#0], col2[#1] |
| | sortByGroupKey:false |
| | cardinality=5 |
| | distribute expr lists: |
| | |
| 0:VOlapScanNode(123) |
| TABLE: cloud.test(test), PREAGGREGATION: ON |
| partitions=1/1 (test) |
| tablets=10/10, tabletList=1761821613823,1761821613827,1761821613831 ... |
| cardinality=7, avgRowSize=399.2857, numNodes=3 |
| pushAggOp=NONE |
| |
| |
| |
| ========== STATISTICS ========== |
+------------------------------------------------------------------------------+
75 rows in set (0.11 sec)

mysql> explain SELECT DISTINCT col1, col2 from test;
+------------------------------------------------------------------------------+
| Explain String(Nereids Planner) |
+------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| col1[#4] |
| col2[#5] |
| PARTITION: HASH_PARTITIONED: col1[#2], col2[#3] |
| |
| HAS_COLO_PLAN_NODE: true |
| |
| VRESULT SINK |
| MYSQL_PROTOCAL |
| |
| 3:VAGGREGATE (merge finalize)(93) |
| | group by: col1[#2], col2[#3] |
| | sortByGroupKey:false |
| | cardinality=5 |
| | distribute expr lists: col1[#2], col2[#3] |
| | |
| 2:VEXCHANGE |
| offset: 0 |
| distribute expr lists: |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: RANDOM |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: col1[#2], col2[#3] |
| |
| 1:VAGGREGATE (update finalize)(85) |
| | STREAMING |
| | group by: col1[#0], col2[#1] |
| | sortByGroupKey:false |
| | cardinality=5 |
| | distribute expr lists: |
| | |
| 0:VOlapScanNode(81) |
| TABLE: cloud.test(test), PREAGGREGATION: ON |
| partitions=1/1 (test) |
| tablets=10/10, tabletList=1761821613823,1761821613827,1761821613831 ... |
| cardinality=7, avgRowSize=399.2857, numNodes=3 |
| pushAggOp=NONE |
| |
| |
| |
| ========== STATISTICS ========== |

[uchenily]

The result COUNT(DISTINCT col1, col2) return 2 is expected, same with mysql (tested on MariaDB 10.11.13)

MariaDB [test]> CREATE TABLE test_distinct (
    ->     col1 VARCHAR(10),
    ->     col2 VARCHAR(10)
    -> );
Query OK, 0 rows affected (0.026 sec)

MariaDB [test]> INSERT INTO test_distinct VALUES
    -> ('A', 'X'),
    -> ('A', 'X'),
    -> ('B', NULL),
    -> ('B', NULL),
    -> ('C', 'Y'),
    -> (NULL, 'Z'),
    -> (NULL, NULL);
Query OK, 7 rows affected (0.003 sec)
Records: 7  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT DISTINCT col1, col2 FROM test_distinct;
+------+------+
| col1 | col2 |
+------+------+
| A    | X    |
| B    | NULL |
| C    | Y    |
| NULL | Z    |
| NULL | NULL |
+------+------+
5 rows in set (0.000 sec)

MariaDB [test]> SELECT COUNT(DISTINCT col1, col2) FROM test_distinct;
+----------------------------+
| COUNT(DISTINCT col1, col2) |
+----------------------------+
|                          2 |
+----------------------------+
1 row in set (0.001 sec)

FYR:
https://github.com/apache/doris/blob/4.0.2-rc02/be/src/vec/aggregate_functions/aggregate_function_count.h#L210-L215