[HIVE-25209] SELECT query with SUM function producing unexpected result

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Labels: pull-request-available
• Assignee: Soumyakanti Das
• Reporter: Soumyakanti Das
• Created: 07/Jun/21 14:34
• Updated: 17/Nov/22 08:48
• Resolved: 19/Jul/21 08:58

Description

Hive: SELECT query with SUM function producing unexpected result

Problem Statement:

SELECT SUM(1) FROM t1;
---- result: 0
SELECT SUM(agg0) FROM (
    SELECT SUM(1) as agg0 FROM t1 WHERE t1.c0 UNION ALL 
    SELECT SUM(1) as agg0 FROM t1 WHERE NOT (t1.c0) UNION ALL 
    SELECT SUM(1) as agg0 FROM t1 WHERE (t1.c0) IS NULL
    ) as asdf;
---- result: null 

Steps to reproduce:

DROP DATABASE IF EXISTS db5 CASCADE;
CREATE DATABASE db5;
use db5;
CREATE TABLE IF NOT EXISTS t1(c0 boolean, c1 boolean);
SELECT SUM(1) FROM t1;
-- result: 0
SELECT SUM(agg0) FROM (
    SELECT SUM(1) as agg0 FROM t1 WHERE t1.c0 UNION ALL 
    SELECT SUM(1) as agg0 FROM t1 WHERE NOT (t1.c0) UNION ALL 
    SELECT SUM(1) as agg0 FROM t1 WHERE (t1.c0) IS NULL
    ) as asdf;
-- result: null 

Observations:

SELECT SUM(1) as agg0 FROM t1 WHERE t1.c0 = t1.c1; – will result in null

Similarity with postgres,
both the queries result in null

Similarity with Impala,
both the queries result in null

Issue Links

• links to: GitHub Pull Request #2360