CODEWITHSUNDEEP
hivehiveqlwhere-clause
Micah Pearce
Micah Pearce

Reputation: 1935

Hive Where Clause 'And'/'Or' Order of Operations

I'm using Hadoop 2.7.3. When you run a hiveql command and have a where clause with 'and' and 'or' in it how does it distribute the conditions?

For example Say I have the following query:

... where A and B or C and D.

Does it return one of the following:

A or B or C or D
((A and B) or C) and D
(A and B and D) or C
A and (B or C) and D

I know I can use parentheses to specifc exactly which of the above is used, but what does it do by default?

Upvotes: 1

Views: 3291

Answers (2)

pault
pault

Reputation: 43504

The answer by @GordonLinoff is correct. You can verify this by constructing the truth table using the following query:

SELECT *, A and B or C and D AS result
FROM
  (SELECT TRUE AS a
   UNION ALL SELECT FALSE AS a) A
CROSS JOIN
  (SELECT TRUE AS b
   UNION ALL SELECT FALSE AS b) B
CROSS JOIN
  (SELECT TRUE AS c
   UNION ALL SELECT FALSE AS c) C
CROSS JOIN
  (SELECT TRUE AS d
   UNION ALL SELECT FALSE AS d) D

Which ooutputs:

+-----+-----+-----+-----+-------+
|  a.a|  b.b|  c.c|  d.d| result|
+-----+-----+-----+-----+-------+
| true| true| true|false|   true|
| true| true| true| true|   true|
| true| true|false|false|   true|
| true| true|false| true|   true|
|false| true| true|false|  false|
|false| true| true| true|   true|
|false| true|false|false|  false|
|false| true|false| true|  false|
| true|false| true|false|  false|
| true|false| true| true|   true|
| true|false|false|false|  false|
| true|false|false| true|  false|
|false|false| true|false|  false|
|false|false| true| true|   true|
|false|false|false|false|  false|
|false|false|false| true|  false|
+-----+-----+-----+-----+-------+

Thus, we can empirically conclude that this indeed evaluates to (A and B) or (C and D).

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is the precedence of operations. AND binds more closely than OR, so:

A and B or C and D

is interpreted as:

(A and B) or (C and D)

Upvotes: 4

Related Questions