[HIVE-15040] LATERAL VIEW + WHERE IN ...= WRONG RESULT

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 2.0.0
• Assignee: Unassigned
• Reporter: Furcy Pin
• Created: 24/Oct/16 10:20
• Updated: 30/Jul/21 11:36

Description

This query:

SELECT 
  COUNT(1)
FROM (
  SELECT 1 as c1 , Array(1, 2, 3) as c2 
  UNION ALL 
  SELECT 2 as c1 , Array(2, 3, 4) as c2 
) T
LATERAL VIEW explode(c2) LV AS c
WHERE c = 42
AND T.c1 NOT IN (SELECT 1 UNION ALL SELECT 3) 
;

returns 3 in Hive 1.1.0 and 2.0.0

But obviously it should return 0, since c = 42 is false.
It seems that the clause is ignored.

Spark-SQL does return 0.

P.S. The UNION ALL is not causing the bug, I just wanted to demonstrate is with a standalone query. Using regular tables instead still causes the same bug.

Issue Links

• is related to: HIVE-24969 Predicates may be removed when decorrelating subqueries with lateral