[HIVE-14923] DATE and TIMESTAMP comparisons do not work

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 2.3.0
• Component/s: Hive
• Assignee: Ashutosh Chauhan
• Reporter: Matt McCline
• Created: 10/Oct/16 22:30
• Updated: 21/Jul/17 18:12
• Resolved: 22/Nov/16 22:53

Description

When comparing a DATE type with a TIMESTAMP type, the planner promotes both sides to string. But since the DATE value does not include the hh:mm:ss[.nnnnnnnnn] the comparison produces wrong results.

Thanks to Jason Dere for observing this.

Here is a portion of an EXPLAIN output:

                      Map Join Operator
                        condition map:
                             Inner Join 0 to 1
                        keys:
                          0 UDFToString(some_timestamp) (type: string)
                          1 UDFToString(some_date) (type: string)

Workaround is to cast the DATE to a TIMESTAMP.

Issue Links

• duplicates: HIVE-15236 timestamp and date comparison should happen in timestamp