- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Created on 03-31-2017 10:57 PM
Issue:
Below is a sample test
CREATE DATABASE IF NOT EXISTS tmp; DROP TABLE IF EXISTS tmp.orders; CREATE TABLE tmp.orders (username STRING, order_creation_date TIMESTAMP, amount DOUBLE); INSERT INTO TABLE tmp.orders VALUES ("jack", '2017-02-26 13:45:12', 88.2), ("jones", '2017-02-28 15:28:14', 92.4);
HDP 2.4.2
SELECT username FROM tmp.orders WHERE order_creation_date > '2017-02-27'; OK jones select * from tmp.orders; OK jack 2017-02-26 13:45:12 88.2 jones 2017-02-28 15:28:14 92.4HDP 2.5.0 & 2.5.3
SELECT username FROM tmp.orders WHERE order_creation_date > '2017-02-27'; OK SELECT username FROM tmp.orders WHERE order_creation_date > TO_DATE('2017-02-27'); OK You can notice the above 2 queries did not display any result
Resolution:
Workaround: Use cast function in hive or include hh:mm:ss as below
SELECT username FROM tmp.orders WHERE order_creation_date > '2017-02-27 00:00:00'; OK jones
This is a known bug in hive
Reference: Apache, Hortonworks
Created on 04-03-2017 12:34 AM
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
May I have apache jira ID?
Created on 09-21-2017 08:05 PM
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
This is interesting. Thank you for posting this resolution to this comparison as I was able to use a similar approach to my issue!
I am on HDP 2.6.1 and I had some similar unexpected behavior. When selecting a timestamp column, I wanted to grab all columns where the timestamp was within a certain date. However, when it returned the rows.... it "zeroed" the timestamp column to "yyyy-mm-dd 00:00:00" where ymd was the actual day. I found it bizarre since I only applied a function to my where clause and not my selected column. Here is an example:
-- Unexpected query behavior SELECT time_stamp_column FROM my_table WHERE date(time_stamp_column) = '2017-09-15' limit 4; --Unexpected results (no timestamps in table with values "00:00:00 0") 2017-09-15 00:00:00 0 2017-09-15 00:00:00 0 2017-09-15 00:00:00 0 2017-09-15 00:00:00 0 --Query using cast SELECT time_stamp_column FROM my_table WHERE time_stamp_column >= '2017-09-15 00:00:00' AND time_stamp_column < '2017-09-16 00:00:00' limit 4; --Expected/desired results (this is the actual data, as no columns have exactly the 00:00:00 timestamp) 2017-09-15 17:25:28.766248 2017-09-15 17:29:05.427199 2017-09-15 17:29:08.219565 2017-09-15 17:33:20.907088
Thanks @knarendran !
Hopefully someone else will find this useful.