Why does Hive throw me an error while using Order by date?

Question

I am trying to write a query In hive and I am seeing the following error. "Error while compiling statement:

FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: Primitve type DATE not supported in Value Boundary expression.

I used the same query in Oracle sql and it works fine. How can I write a valid order by in Hive?

Select   
Email,
FIRST_VALUE(C.abc_cust_id) Over (Partition By Lower(email) Order By C.regt_date
 Desc)As CUSTOMER_ID
from table X

leftjoin leftjoin · Accepted Answer · 2021-04-23T17:07:11

Because some primitive types support (it was no DATE type before) was added after windowing and windowing was not fixed. See HIVE-13973

As a workaround, try to cast DATE as STRING:

Over (Partition By Lower(email) Order By cast(C.regt_date as string) Desc)

Why does Hive throw me an error while using Order by date?

1 Answers