[HIVE-28254] CBO (Calcite Return Path): Multiple DISTINCT leads to wrong results

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0.0
• Fix Version/s: 4.1.0, 4.0.1
• Component/s: CBO
• Labels: hive-4.0.1-merged hive-4.0.1-must pull-request-available
• Assignee: Shohei Okumiya
• Reporter: Shohei Okumiya
• Created: 11/May/24 17:02
• Updated: 24/Sep/24 02:15
• Resolved: 04/Jun/24 07:30

Description

CBO return path can build incorrect GroupByOperator when multiple aggregations with DISTINCT are involved.

This is an example.

CREATE TABLE test (col1 INT, col2 INT);
INSERT INTO test VALUES (1, 100), (2, 200), (2, 200), (3, 300);

set hive.cbo.returnpath.hiveop=true;
set hive.map.aggr=false;

SELECT
  SUM(DISTINCT col1),
  COUNT(DISTINCT col1),
  SUM(DISTINCT col2),
  SUM(col2)
FROM test;

The last column should be 800. But the SUM refers to col1 and the actual result is 8.

+------+------+------+------+
| _c0  | _c1  | _c2  | _c3  |
+------+------+------+------+
| 6    | 3    | 600  | 8    |
+------+------+------+------+ 

 

Issue Links

• links to: GitHub Pull Request #5245