[HIVE-25533] Incorrect results when filtering data from UNION ALL sub-query

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: Database/Schema

Environment

Azure HDInsight 4.1.7.5

Hive 3.1.0

• Assignee: Unassigned
• Reporter: Needn Yu
• Due: 31/Oct/21
• Created: 17/Sep/21 03:20
• Updated: 17/Sep/21 12:43

Description

With CBO enabled querying from a view or CTE with a UNION ALL clause produces wrong results, such as the following script shows.

CREATE TABLE n1 (c1 STRING);

INSERT OVERWRITE TABLE n1 VALUES('needn');

CREATE VIEW v1 
AS
SELECT 'maggie'  AS c1 FROM n1
UNION ALL
SELECT c1 FROM n1;

Return the incorrect result when using "=" or "IN" with single element.

For example, the following 2 querys return nothing.

SELECT * FROM v1 WHERE c1 = 'maggie';
SELECT * FROM v1 WHERE c1 IN ('maggie');

 

However, I can get correct result when using "LIKE" or "IN" with multiple element.

For example, the following 2 querys return expected result.

SELECT * FROM v1 WHERE c1 IN ('maggie','This is a bug');
SELECT * FROM v1 WHERE c1 LIKE 'maggie%';