[HIVE-14838] F481: Expanded NULL Predicate

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Carter Shanklin
• Created: 25/Sep/16 01:08
• Updated: 12/Feb/21 14:59

Description

F481 is mandatory in the SQL spec. In the context of comparisons to null and not null "The <row value expression> can be something other than a <column reference>". Essentially a full row / tuple can be compared against null.

An example in Postgres:

vagrant=# create table test (x int, y int, z int);
vagrant=# insert into test values (1, 1, 1);
vagrant=# insert into test values (2, 2, 2);
vagrant=# insert into test values (null, 3, null);
vagrant=# insert into test values (4, null, null);
vagrant=# insert into test values (null, null, null);

vagrant=# select * from test where (x, z) is null;
 x | y | z
---+---+---
   | 3 |
   |   |
(2 rows)

vagrant=# select * from test where (x, z) is not null;
 x | y | z
---+---+---
 1 | 1 | 1
 2 | 2 | 2

Hive currently produces a parse error for these queries.