Laurent Martin created HIVE-13802:
-------------------------------------
Summary: Built-in aggregate functions may produce incorrect values
when all values being aggregated in a group are NULL as the result of an
expression
Key: HIVE-13802
URL: https://issues.apache.org/jira/browse/HIVE-13802
Project: Hive
Issue Type: Bug
Components: Hive, ORC, Tez
Affects Versions: 0.14.0
Reporter: Laurent Martin
With the Tez engine and Hive tables stored as ORC, built-in aggregate functions
may produce incorrect values when all values being aggregated in a group are
NULL as the result of an expression.
-- Test 1
-- The S column is populated as NaN
SET hive.execution.engine=tez;
CREATE TABLE LM
(
D STRING,
X DOUBLE
)
STORED AS ORC;
INSERT INTO TABLE LM
VALUES
('2016-05-11',NULL),
('2016-05-11',NULL),
('2016-05-11',NULL),
('2016-05-12',NULL),
('2016-05-12',NULL),
('2016-05-12',NULL);
SELECT D, MIN(X + 3) AS S
FROM LM
GROUP BY D;
-- Test 2
-- The S column will be populated as 1 (dangerous case!)
SET hive.execution.engine=tez;
CREATE TABLE LM
(
D STRING,
X INT
)
STORED AS ORC;
INSERT INTO TABLE LM
VALUES
('2016-05-11',NULL),
('2016-05-11',NULL),
('2016-05-11',NULL),
('2016-05-12',NULL),
('2016-05-12',NULL),
('2016-05-12',NULL);
SELECT D, MIN(X + 3) AS S
FROM LM
GROUP BY D;
-- Workaound:
-- According to my tests, a workaround is to surround the nullable expression
with
-- COALESCE. Example:
CREATE TABLE LM
(
D STRING,
X INT
)
STORED AS ORC;
INSERT INTO TABLE LM
VALUES
('2016-05-11',NULL),
('2016-05-11',NULL),
('2016-05-11',NULL),
('2016-05-12',NULL),
('2016-05-12',NULL),
('2016-05-12',NULL);
SELECT D, MIN(COALESCE(X + 3)) AS S
FROM LM
GROUP BY D;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
