[
https://issues.apache.org/jira/browse/HIVE-25306?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17384936#comment-17384936
]
Stamatis Zampetakis commented on HIVE-25306:
--------------------------------------------
Thanks for the comparison [~ashish-kumar-sharma].
I am adding below some observations from a few quick tests in MySQL. It seems
that MySQL in some cases also returns an ERROR for invalid dates in the absence
of explicit casts.
{code:sql}
CREATE TABLE person (id integer, birth date);
SELECT * FROM person where birth > '1970-02-29';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '1970-02-29'
Warning (Code 1292): Incorrect date value: '1970-02-29' for column 'birth' at
row 1
Error (Code 1525): Incorrect DATE value: '1970-02-29'
{noformat}
Without the explicit cast the query fails with an error due to the incorrect
date.
{code:sql}
SELECT * FROM person where birth > CAST('1970-02-29' as DATE);
{code}
{noformat}
Empty set, 1 warning (0.00 sec)
Warning (Code 1292): Incorrect datetime value: '1970-02-29'
{noformat}
With the explicit cast the query does not fail but prints a warning and returns
an empty result as expected.
> Move Date and Timestamp parsing from ResolverStyle.LENIENT to
> ResolverStyle.STRICT
> ----------------------------------------------------------------------------------
>
> Key: HIVE-25306
> URL: https://issues.apache.org/jira/browse/HIVE-25306
> Project: Hive
> Issue Type: Bug
> Components: Query Planning, UDF
> Reporter: Ashish Sharma
> Assignee: Ashish Sharma
> Priority: Major
> Labels: pull-request-available
> Attachments: DB_compare.JPG
>
> Time Spent: 2h
> Remaining Estimate: 0h
>
> Description -
> Currently Date.java and Timestamp.java use DateTimeFormatter for parsing to
> convert the date/timpstamp from int,string,char etc to Date or Timestamp.
> Default DateTimeFormatter which use ResolverStyle.LENIENT which mean date
> like "1992-13-12" is converted to "2000-01-12",
> Moving DateTimeFormatter which use ResolverStyle.STRICT which mean date like
> "1992-13-12" is not be converted instead NULL is return.
> https://docs.google.com/document/d/1YTTPlNq3qyzlKfYVkSl3EFhVQ6-wa9WFRdkdIeCoc1Y/edit?usp=sharing
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
