You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Stamatis Zampetakis <za...@gmail.com> on 2021/07/13 16:21:20 UTC
Re: [EXTERNAL] Re: Move Date and Timestamp parsing from
ResolverStyle.LENIENT to ResolverStyle.STRICT
Hi all,
Thanks for pushing this forward Ashish!
Actually I am not in favor of creating a flag for this. Either we decide
consciously to break backward compatibility in the hope that we are
improving the expected results or we keep the current behavior.
Adding another flag means that we maintain and support two variants that
makes the problem of test coverage brought by David even worse.
I second David's idea to run some tests over some well adopted DBMS (MySQL,
Oracle, MSSQL, Postgres) to see what they return.
I think Ashish already did some tests over MySQL and MSSQL but personally I
would like to see some more (dates + engines) in order to express
a preference.
We shouldn't forget that since Hive is implemented in Java, having
functions that are inline with the Java APIs is not such a bad idea.
The last comment is slightly supportive of the current behavior.
I am including user@ list in the discussion since we should definitely
consider the feedback of people that are using Hive for real.
Best,
Stamatis
On Tue, Jul 13, 2021 at 4:31 PM David <da...@gmail.com> wrote:
> Hello,
>
> Is anyone able to try out a few different vendor RDBMS to see how they
> handle invalid dates, or provide links to documentation, both for invalid
> formatting and things like mm-dd-yyy 12-40-2021?
>
> Thanks.
>
> On Tue, Jul 13, 2021 at 5:14 AM Sankar Hariappan
> <Sa...@microsoft.com.invalid> wrote:
>
>> I'm supporting this change to return "NULL" for invalid date/timestamp.
>> In the interest of backward compatibility, can we make all these changes
>> under a flag which can be enabled by default?
>>
>>
>> Thanks,
>> Sankar
>> -----Original Message-----
>> From: David <da...@gmail.com>
>> Sent: 10 July 2021 07:35
>> To: dev <de...@hive.apache.org>
>> Cc: sankarh@apache.org; Stamatis Zampetakis <za...@gmail.com>
>> Subject: [EXTERNAL] Re: Move Date and Timestamp parsing from
>> ResolverStyle.LENIENT to ResolverStyle.STRICT
>>
>> Hello,
>>
>> I too would be in favor of this. It drastically cuts down on the test
>> matrix for Hive if we can clamp down on timestamp formats. With that being
>> said, I've tried this and it's a big effort. I put it down without getting
>> consensus or buy-in or engagement on the effort. Please check out my work
>> here:
>>
>>
>> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fplugins%2Fservlet%2Fmobile%23issue%2FHIVE-24814&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=mvYgaG7liJOwUZmMvgwlo%2B1HvcUsrnzXA3Ltfz5yEYE%3D&reserved=0
>>
>>
>> On Fri, Jul 9, 2021, 9:49 PM Ashish Sharma <ashishkumarsharma20@gmail.com
>> >
>> wrote:
>>
>> > Hi,
>> >
>> > When casting incorrect date or timestamp literals to DATE or TIMESTAMP
>> > data type hive returns wrong values
>> >
>> > hive> select cast('2020-20-20' as date);
>> >
>> > OK
>> >
>> > 2021-08-20
>> >
>> > Time taken: 4.436 seconds, Fetched: 1 row(s)
>> >
>> >
>> > I have created a solution draft. Please review the draft and provide
>> > your valuable feedback on the same.
>> >
>> >
>> >
>> > https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs
>> > .google.com%2Fdocument%2Fd%2F1YTTPlNq3qyzlKfYVkSl3EFhVQ6-wa9WFRdkdIeCo
>> > c1Y%2Fedit%3Fusp%3Dsharing&data=04%7C01%7CSankar.Hariappan%40micro
>> > soft.com%7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7c
>> > d011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoi
>> > MC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&
>> > sdata=iihK9wJC%2B1uPktHSE9BpXADvbal1UT7vZ3rwigkgkIY%3D&reserved=0
>> >
>> >
>> > https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissu
>> > es.apache.org%2Fjira%2Fbrowse%2FHIVE-25306&data=04%7C01%7CSankar.H
>> > ariappan%40microsoft.com%7Cd47432b9d7654d66a46908d943472338%7C72f988bf
>> > 86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFp
>> > bGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn
>> > 0%3D%7C3000&sdata=nW%2Bw%2B0AYn%2BbvOqRLrXghfH0FG%2B1cQW%2BzdrpT%2
>> > B9R%2B6rA%3D&reserved=0
>> >
>> >
>> > Thank you
>> >
>> > Ashish Sharma
>> >
>>
>
Re: [EXTERNAL] Re: Move Date and Timestamp parsing from
ResolverStyle.LENIENT to ResolverStyle.STRICT
Posted by Stamatis Zampetakis <za...@gmail.com>.
I am +1 on returning NULL for seemingly "invalid" dates/timestamps.
Invalid may not be the most appropriate term since the parsing adheres to
JDK APIs but indeed the results may seem surprising.
I guess we can merge this PR when all the comments are addressed if nobody
raises a concern in the meantime.
Best,
Stamatis
On Tue, Jul 20, 2021 at 5:04 PM Sankar Hariappan <
Sankar.Hariappan@microsoft.com> wrote:
> +1
>
> Thanks Ashish for the comparison!
>
> I talked to few Hive users (HDInsight) and they supported returning NULL
> for invalid date/timestamp inputs instead of returning incorrect results or
> exception.
>
> Can others pls share your thoughts?
>
>
>
> Thanks,
>
> Sankar
>
>
>
> *From:* Ashish Sharma <as...@gmail.com>
> *Sent:* 20 July 2021 14:02
> *To:* dev@hive.apache.org
> *Cc:* Sankar.Hariappan@microsoft.com.invalid; sankarh@apache.org;
> user@hive.apache.org; David <da...@gmail.com>
> *Subject:* Re: [EXTERNAL] Re: Move Date and Timestamp parsing from
> ResolverStyle.LENIENT to ResolverStyle.STRICT
>
>
>
> Hi all,
>
>
>
> I also feel that adding more config doesn't make sense in this as we are
> tightening the date and timestamp format. We should decide upon a single
> solution even if it break the compatibility. Below the comparison of HIVE
> 1.2, HIVE 3.2, MYSQL, PostgreSQL, Oracle
>
>
>
>
>
> *Query*
>
> *Hive 1.2*
>
> *Hive 3.2*
>
> *Mysql*
>
> *PostgreSQL*
>
> *ORACLE *
>
> select cast('2020-20-20' as date);
>
> NULL
>
> 2021-08-20
>
> NULL
>
> date/time field value out of range: "2020-20-20"
>
> not a valid month
>
> select cast(null as date);
>
> NULL
>
> NULL
>
> NULL
>
> NULL
>
> NULL
>
> select cast('2020-02-31' as date);
>
> 2020-03-02
>
> 2020-03-02
>
> NULL
>
> date/time field value out of range: "2020-02-31"
>
> date format picture ends before converting entire input string
>
> select cast('2020/02/20' as date);
>
> NULL
>
> NULL
>
> 2020-02-20
>
> 2020-02-20
>
> literal does not match format string
>
> select cast('0000-00-00' as date);
>
> NULL
>
> 0002-11-30
>
> NULL
>
> date/time field value out of range: "0000-00-00"
>
> literal does not match format string
>
>
>
>
>
> From the comparison it is quite clear that date and timestamp formatting
> was much tighter in older versions of HIVE. For most of the wrong date
> input *NULL *was the standard response instead of Exception.
>
>
>
> Also when I went through the code I found that. While doing the Vector
> implementation of some of the date related UDF like datediff etc. MySql was
> taken as the gold standard
> <https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fbrowse%2FHIVE-15338%3FfocusedCommentId%3D15727553%26page%3Dcom.atlassian.jira.plugin.system.issuetabpanels%253Acomment-tabpanel%23comment-15727553&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537409104%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=UjkFmuO8PHHkcojm1f%2FPJipguQ1JYMkbl%2F0XzcrvGBg%3D&reserved=0>.
> So it make more sense that we should comply with MySql as we already refer
> MySql as gold standard and returning NULL as result for wrong dates in cast
> is also documented
> <https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcwiki.apache.org%2Fconfluence%2Fdisplay%2Fhive%2Flanguagemanual%2Btypes%23LanguageManualTypes-CastingDates&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537419060%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=69O6Ct8q%2FK8l31D4yK4eF1fQRFjtl3jAiooP3FWJTJ4%3D&reserved=0>
>
>
>
>
> *So I propose to make NULL as the standard response for all parsing
> errors.*
>
>
>
> Thanks
>
> Ashish Sharma
>
>
>
> On Tue, Jul 13, 2021 at 9:52 PM Stamatis Zampetakis <za...@gmail.com>
> wrote:
>
> Hi all,
>
> Thanks for pushing this forward Ashish!
>
> Actually I am not in favor of creating a flag for this. Either we decide
> consciously to break backward compatibility in the hope that we are
> improving the expected results or we keep the current behavior.
> Adding another flag means that we maintain and support two variants that
> makes the problem of test coverage brought by David even worse.
>
> I second David's idea to run some tests over some well adopted DBMS (MySQL,
> Oracle, MSSQL, Postgres) to see what they return.
> I think Ashish already did some tests over MySQL and MSSQL but personally I
> would like to see some more (dates + engines) in order to express
> a preference.
> We shouldn't forget that since Hive is implemented in Java, having
> functions that are inline with the Java APIs is not such a bad idea.
> The last comment is slightly supportive of the current behavior.
>
> I am including user@ list in the discussion since we should definitely
> consider the feedback of people that are using Hive for real.
>
> Best,
> Stamatis
>
> On Tue, Jul 13, 2021 at 4:31 PM David <da...@gmail.com> wrote:
>
> > Hello,
> >
> > Is anyone able to try out a few different vendor RDBMS to see how they
> > handle invalid dates, or provide links to documentation, both for invalid
> > formatting and things like mm-dd-yyy 12-40-2021?
> >
> > Thanks.
> >
> > On Tue, Jul 13, 2021 at 5:14 AM Sankar Hariappan
> > <Sa...@microsoft.com.invalid> wrote:
> >
> >> I'm supporting this change to return "NULL" for invalid date/timestamp.
> >> In the interest of backward compatibility, can we make all these changes
> >> under a flag which can be enabled by default?
> >>
> >>
> >> Thanks,
> >> Sankar
> >> -----Original Message-----
> >> From: David <da...@gmail.com>
> >> Sent: 10 July 2021 07:35
> >> To: dev <de...@hive.apache.org>
> >> Cc: sankarh@apache.org; Stamatis Zampetakis <za...@gmail.com>
> >> Subject: [EXTERNAL] Re: Move Date and Timestamp parsing from
> >> ResolverStyle.LENIENT to ResolverStyle.STRICT
> >>
> >> Hello,
> >>
> >> I too would be in favor of this. It drastically cuts down on the test
> >> matrix for Hive if we can clamp down on timestamp formats. With that
> being
> >> said, I've tried this and it's a big effort. I put it down without
> getting
> >> consensus or buy-in or engagement on the effort. Please check out my
> work
> >> here:
> >>
> >>
> >>
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fplugins%2Fservlet%2Fmobile%23issue%2FHIVE-24814&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=mvYgaG7liJOwUZmMvgwlo%2B1HvcUsrnzXA3Ltfz5yEYE%3D&reserved=0
> >>
> >>
> >> On Fri, Jul 9, 2021, 9:49 PM Ashish Sharma <
> ashishkumarsharma20@gmail.com
> >> >
> >> wrote:
> >>
> >> > Hi,
> >> >
> >> > When casting incorrect date or timestamp literals to DATE or TIMESTAMP
> >> > data type hive returns wrong values
> >> >
> >> > hive> select cast('2020-20-20' as date);
> >> >
> >> > OK
> >> >
> >> > 2021-08-20
> >> >
> >> > Time taken: 4.436 seconds, Fetched: 1 row(s)
> >> >
> >> >
> >> > I have created a solution draft. Please review the draft and provide
> >> > your valuable feedback on the same.
> >> >
> >> >
> >> >
> >> >
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs
> >> > .google.com
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgoogle.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537419060%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=buVSLhJVTluXbYd0iy491oHq2LU2e%2FfKfKYRks1co30%3D&reserved=0>
> %2Fdocument%2Fd%2F1YTTPlNq3qyzlKfYVkSl3EFhVQ6-wa9WFRdkdIeCo
> >> > c1Y%2Fedit%3Fusp%3Dsharing&data=04%7C01%7CSankar.Hariappan%40micro
> >> > soft.com
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fsoft.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537429016%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=no6XEmDCYcHNdjQduJdynmsEJpLww56lKSEze4LZ8Qo%3D&reserved=0>
> %7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7c
> >> > d011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoi
> >> > MC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&
> >> > sdata=iihK9wJC%2B1uPktHSE9BpXADvbal1UT7vZ3rwigkgkIY%3D&reserved=0
> >> >
> >> >
> >> >
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissu
> >> > es.apache.org
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fes.apache.org%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537429016%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=W4mMKQSceHXb1NFvkYwtBvy02B81WoX9mAyPHd0F77s%3D&reserved=0>
> %2Fjira%2Fbrowse%2FHIVE-25306&data=04%7C01%7CSankar.H
> >> > ariappan%40microsoft.com
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2F40microsoft.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537438972%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=a8wpFdw26oKHEDLE1XqTGVBCIgkHs%2Fj0r9BMnMkszOA%3D&reserved=0>
> %7Cd47432b9d7654d66a46908d943472338%7C72f988bf
> >> > 86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFp
> >> > bGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn
> >> > 0%3D%7C3000&sdata=nW%2Bw%2B0AYn%2BbvOqRLrXghfH0FG%2B1cQW%2BzdrpT%2
> >> > B9R%2B6rA%3D&reserved=0
> >> >
> >> >
> >> > Thank you
> >> >
> >> > Ashish Sharma
> >> >
> >>
> >
>
>
Re: [EXTERNAL] Re: Move Date and Timestamp parsing from
ResolverStyle.LENIENT to ResolverStyle.STRICT
Posted by Stamatis Zampetakis <za...@gmail.com>.
I am +1 on returning NULL for seemingly "invalid" dates/timestamps.
Invalid may not be the most appropriate term since the parsing adheres to
JDK APIs but indeed the results may seem surprising.
I guess we can merge this PR when all the comments are addressed if nobody
raises a concern in the meantime.
Best,
Stamatis
On Tue, Jul 20, 2021 at 5:04 PM Sankar Hariappan <
Sankar.Hariappan@microsoft.com> wrote:
> +1
>
> Thanks Ashish for the comparison!
>
> I talked to few Hive users (HDInsight) and they supported returning NULL
> for invalid date/timestamp inputs instead of returning incorrect results or
> exception.
>
> Can others pls share your thoughts?
>
>
>
> Thanks,
>
> Sankar
>
>
>
> *From:* Ashish Sharma <as...@gmail.com>
> *Sent:* 20 July 2021 14:02
> *To:* dev@hive.apache.org
> *Cc:* Sankar.Hariappan@microsoft.com.invalid; sankarh@apache.org;
> user@hive.apache.org; David <da...@gmail.com>
> *Subject:* Re: [EXTERNAL] Re: Move Date and Timestamp parsing from
> ResolverStyle.LENIENT to ResolverStyle.STRICT
>
>
>
> Hi all,
>
>
>
> I also feel that adding more config doesn't make sense in this as we are
> tightening the date and timestamp format. We should decide upon a single
> solution even if it break the compatibility. Below the comparison of HIVE
> 1.2, HIVE 3.2, MYSQL, PostgreSQL, Oracle
>
>
>
>
>
> *Query*
>
> *Hive 1.2*
>
> *Hive 3.2*
>
> *Mysql*
>
> *PostgreSQL*
>
> *ORACLE *
>
> select cast('2020-20-20' as date);
>
> NULL
>
> 2021-08-20
>
> NULL
>
> date/time field value out of range: "2020-20-20"
>
> not a valid month
>
> select cast(null as date);
>
> NULL
>
> NULL
>
> NULL
>
> NULL
>
> NULL
>
> select cast('2020-02-31' as date);
>
> 2020-03-02
>
> 2020-03-02
>
> NULL
>
> date/time field value out of range: "2020-02-31"
>
> date format picture ends before converting entire input string
>
> select cast('2020/02/20' as date);
>
> NULL
>
> NULL
>
> 2020-02-20
>
> 2020-02-20
>
> literal does not match format string
>
> select cast('0000-00-00' as date);
>
> NULL
>
> 0002-11-30
>
> NULL
>
> date/time field value out of range: "0000-00-00"
>
> literal does not match format string
>
>
>
>
>
> From the comparison it is quite clear that date and timestamp formatting
> was much tighter in older versions of HIVE. For most of the wrong date
> input *NULL *was the standard response instead of Exception.
>
>
>
> Also when I went through the code I found that. While doing the Vector
> implementation of some of the date related UDF like datediff etc. MySql was
> taken as the gold standard
> <https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fbrowse%2FHIVE-15338%3FfocusedCommentId%3D15727553%26page%3Dcom.atlassian.jira.plugin.system.issuetabpanels%253Acomment-tabpanel%23comment-15727553&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537409104%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=UjkFmuO8PHHkcojm1f%2FPJipguQ1JYMkbl%2F0XzcrvGBg%3D&reserved=0>.
> So it make more sense that we should comply with MySql as we already refer
> MySql as gold standard and returning NULL as result for wrong dates in cast
> is also documented
> <https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcwiki.apache.org%2Fconfluence%2Fdisplay%2Fhive%2Flanguagemanual%2Btypes%23LanguageManualTypes-CastingDates&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537419060%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=69O6Ct8q%2FK8l31D4yK4eF1fQRFjtl3jAiooP3FWJTJ4%3D&reserved=0>
>
>
>
>
> *So I propose to make NULL as the standard response for all parsing
> errors.*
>
>
>
> Thanks
>
> Ashish Sharma
>
>
>
> On Tue, Jul 13, 2021 at 9:52 PM Stamatis Zampetakis <za...@gmail.com>
> wrote:
>
> Hi all,
>
> Thanks for pushing this forward Ashish!
>
> Actually I am not in favor of creating a flag for this. Either we decide
> consciously to break backward compatibility in the hope that we are
> improving the expected results or we keep the current behavior.
> Adding another flag means that we maintain and support two variants that
> makes the problem of test coverage brought by David even worse.
>
> I second David's idea to run some tests over some well adopted DBMS (MySQL,
> Oracle, MSSQL, Postgres) to see what they return.
> I think Ashish already did some tests over MySQL and MSSQL but personally I
> would like to see some more (dates + engines) in order to express
> a preference.
> We shouldn't forget that since Hive is implemented in Java, having
> functions that are inline with the Java APIs is not such a bad idea.
> The last comment is slightly supportive of the current behavior.
>
> I am including user@ list in the discussion since we should definitely
> consider the feedback of people that are using Hive for real.
>
> Best,
> Stamatis
>
> On Tue, Jul 13, 2021 at 4:31 PM David <da...@gmail.com> wrote:
>
> > Hello,
> >
> > Is anyone able to try out a few different vendor RDBMS to see how they
> > handle invalid dates, or provide links to documentation, both for invalid
> > formatting and things like mm-dd-yyy 12-40-2021?
> >
> > Thanks.
> >
> > On Tue, Jul 13, 2021 at 5:14 AM Sankar Hariappan
> > <Sa...@microsoft.com.invalid> wrote:
> >
> >> I'm supporting this change to return "NULL" for invalid date/timestamp.
> >> In the interest of backward compatibility, can we make all these changes
> >> under a flag which can be enabled by default?
> >>
> >>
> >> Thanks,
> >> Sankar
> >> -----Original Message-----
> >> From: David <da...@gmail.com>
> >> Sent: 10 July 2021 07:35
> >> To: dev <de...@hive.apache.org>
> >> Cc: sankarh@apache.org; Stamatis Zampetakis <za...@gmail.com>
> >> Subject: [EXTERNAL] Re: Move Date and Timestamp parsing from
> >> ResolverStyle.LENIENT to ResolverStyle.STRICT
> >>
> >> Hello,
> >>
> >> I too would be in favor of this. It drastically cuts down on the test
> >> matrix for Hive if we can clamp down on timestamp formats. With that
> being
> >> said, I've tried this and it's a big effort. I put it down without
> getting
> >> consensus or buy-in or engagement on the effort. Please check out my
> work
> >> here:
> >>
> >>
> >>
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fplugins%2Fservlet%2Fmobile%23issue%2FHIVE-24814&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=mvYgaG7liJOwUZmMvgwlo%2B1HvcUsrnzXA3Ltfz5yEYE%3D&reserved=0
> >>
> >>
> >> On Fri, Jul 9, 2021, 9:49 PM Ashish Sharma <
> ashishkumarsharma20@gmail.com
> >> >
> >> wrote:
> >>
> >> > Hi,
> >> >
> >> > When casting incorrect date or timestamp literals to DATE or TIMESTAMP
> >> > data type hive returns wrong values
> >> >
> >> > hive> select cast('2020-20-20' as date);
> >> >
> >> > OK
> >> >
> >> > 2021-08-20
> >> >
> >> > Time taken: 4.436 seconds, Fetched: 1 row(s)
> >> >
> >> >
> >> > I have created a solution draft. Please review the draft and provide
> >> > your valuable feedback on the same.
> >> >
> >> >
> >> >
> >> >
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs
> >> > .google.com
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgoogle.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537419060%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=buVSLhJVTluXbYd0iy491oHq2LU2e%2FfKfKYRks1co30%3D&reserved=0>
> %2Fdocument%2Fd%2F1YTTPlNq3qyzlKfYVkSl3EFhVQ6-wa9WFRdkdIeCo
> >> > c1Y%2Fedit%3Fusp%3Dsharing&data=04%7C01%7CSankar.Hariappan%40micro
> >> > soft.com
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fsoft.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537429016%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=no6XEmDCYcHNdjQduJdynmsEJpLww56lKSEze4LZ8Qo%3D&reserved=0>
> %7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7c
> >> > d011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoi
> >> > MC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&
> >> > sdata=iihK9wJC%2B1uPktHSE9BpXADvbal1UT7vZ3rwigkgkIY%3D&reserved=0
> >> >
> >> >
> >> >
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissu
> >> > es.apache.org
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fes.apache.org%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537429016%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=W4mMKQSceHXb1NFvkYwtBvy02B81WoX9mAyPHd0F77s%3D&reserved=0>
> %2Fjira%2Fbrowse%2FHIVE-25306&data=04%7C01%7CSankar.H
> >> > ariappan%40microsoft.com
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2F40microsoft.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537438972%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=a8wpFdw26oKHEDLE1XqTGVBCIgkHs%2Fj0r9BMnMkszOA%3D&reserved=0>
> %7Cd47432b9d7654d66a46908d943472338%7C72f988bf
> >> > 86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFp
> >> > bGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn
> >> > 0%3D%7C3000&sdata=nW%2Bw%2B0AYn%2BbvOqRLrXghfH0FG%2B1cQW%2BzdrpT%2
> >> > B9R%2B6rA%3D&reserved=0
> >> >
> >> >
> >> > Thank you
> >> >
> >> > Ashish Sharma
> >> >
> >>
> >
>
>
RE: [EXTERNAL] Re: Move Date and Timestamp parsing from
ResolverStyle.LENIENT to ResolverStyle.STRICT
Posted by Sankar Hariappan <Sa...@microsoft.com.INVALID>.
+1
Thanks Ashish for the comparison!
I talked to few Hive users (HDInsight) and they supported returning NULL for invalid date/timestamp inputs instead of returning incorrect results or exception.
Can others pls share your thoughts?
Thanks,
Sankar
From: Ashish Sharma <as...@gmail.com>
Sent: 20 July 2021 14:02
To: dev@hive.apache.org
Cc: Sankar.Hariappan@microsoft.com.invalid; sankarh@apache.org; user@hive.apache.org; David <da...@gmail.com>
Subject: Re: [EXTERNAL] Re: Move Date and Timestamp parsing from ResolverStyle.LENIENT to ResolverStyle.STRICT
Hi all,
I also feel that adding more config doesn't make sense in this as we are tightening the date and timestamp format. We should decide upon a single solution even if it break the compatibility. Below the comparison of HIVE 1.2, HIVE 3.2, MYSQL, PostgreSQL, Oracle
Query
Hive 1.2
Hive 3.2
Mysql
PostgreSQL
ORACLE
select cast('2020-20-20' as date);
NULL
2021-08-20
NULL
date/time field value out of range: "2020-20-20"
not a valid month
select cast(null as date);
NULL
NULL
NULL
NULL
NULL
select cast('2020-02-31' as date);
2020-03-02
2020-03-02
NULL
date/time field value out of range: "2020-02-31"
date format picture ends before converting entire input string
select cast('2020/02/20' as date);
NULL
NULL
2020-02-20
2020-02-20
literal does not match format string
select cast('0000-00-00' as date);
NULL
0002-11-30
NULL
date/time field value out of range: "0000-00-00"
literal does not match format string
From the comparison it is quite clear that date and timestamp formatting was much tighter in older versions of HIVE. For most of the wrong date input NULL was the standard response instead of Exception.
Also when I went through the code I found that. While doing the Vector implementation of some of the date related UDF like datediff etc. MySql was taken as the gold standard<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fbrowse%2FHIVE-15338%3FfocusedCommentId%3D15727553%26page%3Dcom.atlassian.jira.plugin.system.issuetabpanels%253Acomment-tabpanel%23comment-15727553&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537409104%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=UjkFmuO8PHHkcojm1f%2FPJipguQ1JYMkbl%2F0XzcrvGBg%3D&reserved=0>. So it make more sense that we should comply with MySql as we already refer MySql as gold standard and returning NULL as result for wrong dates in cast is also documented<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcwiki.apache.org%2Fconfluence%2Fdisplay%2Fhive%2Flanguagemanual%2Btypes%23LanguageManualTypes-CastingDates&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537419060%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=69O6Ct8q%2FK8l31D4yK4eF1fQRFjtl3jAiooP3FWJTJ4%3D&reserved=0>
So I propose to make NULL as the standard response for all parsing errors.
Thanks
Ashish Sharma
On Tue, Jul 13, 2021 at 9:52 PM Stamatis Zampetakis <za...@gmail.com>> wrote:
Hi all,
Thanks for pushing this forward Ashish!
Actually I am not in favor of creating a flag for this. Either we decide
consciously to break backward compatibility in the hope that we are
improving the expected results or we keep the current behavior.
Adding another flag means that we maintain and support two variants that
makes the problem of test coverage brought by David even worse.
I second David's idea to run some tests over some well adopted DBMS (MySQL,
Oracle, MSSQL, Postgres) to see what they return.
I think Ashish already did some tests over MySQL and MSSQL but personally I
would like to see some more (dates + engines) in order to express
a preference.
We shouldn't forget that since Hive is implemented in Java, having
functions that are inline with the Java APIs is not such a bad idea.
The last comment is slightly supportive of the current behavior.
I am including user@ list in the discussion since we should definitely
consider the feedback of people that are using Hive for real.
Best,
Stamatis
On Tue, Jul 13, 2021 at 4:31 PM David <da...@gmail.com>> wrote:
> Hello,
>
> Is anyone able to try out a few different vendor RDBMS to see how they
> handle invalid dates, or provide links to documentation, both for invalid
> formatting and things like mm-dd-yyy 12-40-2021?
>
> Thanks.
>
> On Tue, Jul 13, 2021 at 5:14 AM Sankar Hariappan
> <Sa...@microsoft.com.invalid>> wrote:
>
>> I'm supporting this change to return "NULL" for invalid date/timestamp.
>> In the interest of backward compatibility, can we make all these changes
>> under a flag which can be enabled by default?
>>
>>
>> Thanks,
>> Sankar
>> -----Original Message-----
>> From: David <da...@gmail.com>>
>> Sent: 10 July 2021 07:35
>> To: dev <de...@hive.apache.org>>
>> Cc: sankarh@apache.org<ma...@apache.org>; Stamatis Zampetakis <za...@gmail.com>>
>> Subject: [EXTERNAL] Re: Move Date and Timestamp parsing from
>> ResolverStyle.LENIENT to ResolverStyle.STRICT
>>
>> Hello,
>>
>> I too would be in favor of this. It drastically cuts down on the test
>> matrix for Hive if we can clamp down on timestamp formats. With that being
>> said, I've tried this and it's a big effort. I put it down without getting
>> consensus or buy-in or engagement on the effort. Please check out my work
>> here:
>>
>>
>> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fplugins%2Fservlet%2Fmobile%23issue%2FHIVE-24814&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=mvYgaG7liJOwUZmMvgwlo%2B1HvcUsrnzXA3Ltfz5yEYE%3D&reserved=0
>>
>>
>> On Fri, Jul 9, 2021, 9:49 PM Ashish Sharma <as...@gmail.com>
>> >
>> wrote:
>>
>> > Hi,
>> >
>> > When casting incorrect date or timestamp literals to DATE or TIMESTAMP
>> > data type hive returns wrong values
>> >
>> > hive> select cast('2020-20-20' as date);
>> >
>> > OK
>> >
>> > 2021-08-20
>> >
>> > Time taken: 4.436 seconds, Fetched: 1 row(s)
>> >
>> >
>> > I have created a solution draft. Please review the draft and provide
>> > your valuable feedback on the same.
>> >
>> >
>> >
>> > https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs
>> > .google.com<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgoogle.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537419060%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=buVSLhJVTluXbYd0iy491oHq2LU2e%2FfKfKYRks1co30%3D&reserved=0>%2Fdocument%2Fd%2F1YTTPlNq3qyzlKfYVkSl3EFhVQ6-wa9WFRdkdIeCo
>> > c1Y%2Fedit%3Fusp%3Dsharing&data=04%7C01%7CSankar.Hariappan%40micro
>> > soft.com<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fsoft.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537429016%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=no6XEmDCYcHNdjQduJdynmsEJpLww56lKSEze4LZ8Qo%3D&reserved=0>%7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7c
>> > d011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoi
>> > MC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&
>> > sdata=iihK9wJC%2B1uPktHSE9BpXADvbal1UT7vZ3rwigkgkIY%3D&reserved=0
>> >
>> >
>> > https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissu
>> > es.apache.org<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fes.apache.org%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537429016%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=W4mMKQSceHXb1NFvkYwtBvy02B81WoX9mAyPHd0F77s%3D&reserved=0>%2Fjira%2Fbrowse%2FHIVE-25306&data=04%7C01%7CSankar.H
>> > ariappan%40microsoft.com<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2F40microsoft.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537438972%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=a8wpFdw26oKHEDLE1XqTGVBCIgkHs%2Fj0r9BMnMkszOA%3D&reserved=0>%7Cd47432b9d7654d66a46908d943472338%7C72f988bf
>> > 86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFp
>> > bGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn
>> > 0%3D%7C3000&sdata=nW%2Bw%2B0AYn%2BbvOqRLrXghfH0FG%2B1cQW%2BzdrpT%2
>> > B9R%2B6rA%3D&reserved=0
>> >
>> >
>> > Thank you
>> >
>> > Ashish Sharma
>> >
>>
>
RE: [EXTERNAL] Re: Move Date and Timestamp parsing from
ResolverStyle.LENIENT to ResolverStyle.STRICT
Posted by Sankar Hariappan <Sa...@microsoft.com>.
+1
Thanks Ashish for the comparison!
I talked to few Hive users (HDInsight) and they supported returning NULL for invalid date/timestamp inputs instead of returning incorrect results or exception.
Can others pls share your thoughts?
Thanks,
Sankar
From: Ashish Sharma <as...@gmail.com>
Sent: 20 July 2021 14:02
To: dev@hive.apache.org
Cc: Sankar.Hariappan@microsoft.com.invalid; sankarh@apache.org; user@hive.apache.org; David <da...@gmail.com>
Subject: Re: [EXTERNAL] Re: Move Date and Timestamp parsing from ResolverStyle.LENIENT to ResolverStyle.STRICT
Hi all,
I also feel that adding more config doesn't make sense in this as we are tightening the date and timestamp format. We should decide upon a single solution even if it break the compatibility. Below the comparison of HIVE 1.2, HIVE 3.2, MYSQL, PostgreSQL, Oracle
Query
Hive 1.2
Hive 3.2
Mysql
PostgreSQL
ORACLE
select cast('2020-20-20' as date);
NULL
2021-08-20
NULL
date/time field value out of range: "2020-20-20"
not a valid month
select cast(null as date);
NULL
NULL
NULL
NULL
NULL
select cast('2020-02-31' as date);
2020-03-02
2020-03-02
NULL
date/time field value out of range: "2020-02-31"
date format picture ends before converting entire input string
select cast('2020/02/20' as date);
NULL
NULL
2020-02-20
2020-02-20
literal does not match format string
select cast('0000-00-00' as date);
NULL
0002-11-30
NULL
date/time field value out of range: "0000-00-00"
literal does not match format string
From the comparison it is quite clear that date and timestamp formatting was much tighter in older versions of HIVE. For most of the wrong date input NULL was the standard response instead of Exception.
Also when I went through the code I found that. While doing the Vector implementation of some of the date related UDF like datediff etc. MySql was taken as the gold standard<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fbrowse%2FHIVE-15338%3FfocusedCommentId%3D15727553%26page%3Dcom.atlassian.jira.plugin.system.issuetabpanels%253Acomment-tabpanel%23comment-15727553&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537409104%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=UjkFmuO8PHHkcojm1f%2FPJipguQ1JYMkbl%2F0XzcrvGBg%3D&reserved=0>. So it make more sense that we should comply with MySql as we already refer MySql as gold standard and returning NULL as result for wrong dates in cast is also documented<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcwiki.apache.org%2Fconfluence%2Fdisplay%2Fhive%2Flanguagemanual%2Btypes%23LanguageManualTypes-CastingDates&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537419060%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=69O6Ct8q%2FK8l31D4yK4eF1fQRFjtl3jAiooP3FWJTJ4%3D&reserved=0>
So I propose to make NULL as the standard response for all parsing errors.
Thanks
Ashish Sharma
On Tue, Jul 13, 2021 at 9:52 PM Stamatis Zampetakis <za...@gmail.com>> wrote:
Hi all,
Thanks for pushing this forward Ashish!
Actually I am not in favor of creating a flag for this. Either we decide
consciously to break backward compatibility in the hope that we are
improving the expected results or we keep the current behavior.
Adding another flag means that we maintain and support two variants that
makes the problem of test coverage brought by David even worse.
I second David's idea to run some tests over some well adopted DBMS (MySQL,
Oracle, MSSQL, Postgres) to see what they return.
I think Ashish already did some tests over MySQL and MSSQL but personally I
would like to see some more (dates + engines) in order to express
a preference.
We shouldn't forget that since Hive is implemented in Java, having
functions that are inline with the Java APIs is not such a bad idea.
The last comment is slightly supportive of the current behavior.
I am including user@ list in the discussion since we should definitely
consider the feedback of people that are using Hive for real.
Best,
Stamatis
On Tue, Jul 13, 2021 at 4:31 PM David <da...@gmail.com>> wrote:
> Hello,
>
> Is anyone able to try out a few different vendor RDBMS to see how they
> handle invalid dates, or provide links to documentation, both for invalid
> formatting and things like mm-dd-yyy 12-40-2021?
>
> Thanks.
>
> On Tue, Jul 13, 2021 at 5:14 AM Sankar Hariappan
> <Sa...@microsoft.com.invalid>> wrote:
>
>> I'm supporting this change to return "NULL" for invalid date/timestamp.
>> In the interest of backward compatibility, can we make all these changes
>> under a flag which can be enabled by default?
>>
>>
>> Thanks,
>> Sankar
>> -----Original Message-----
>> From: David <da...@gmail.com>>
>> Sent: 10 July 2021 07:35
>> To: dev <de...@hive.apache.org>>
>> Cc: sankarh@apache.org<ma...@apache.org>; Stamatis Zampetakis <za...@gmail.com>>
>> Subject: [EXTERNAL] Re: Move Date and Timestamp parsing from
>> ResolverStyle.LENIENT to ResolverStyle.STRICT
>>
>> Hello,
>>
>> I too would be in favor of this. It drastically cuts down on the test
>> matrix for Hive if we can clamp down on timestamp formats. With that being
>> said, I've tried this and it's a big effort. I put it down without getting
>> consensus or buy-in or engagement on the effort. Please check out my work
>> here:
>>
>>
>> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fplugins%2Fservlet%2Fmobile%23issue%2FHIVE-24814&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=mvYgaG7liJOwUZmMvgwlo%2B1HvcUsrnzXA3Ltfz5yEYE%3D&reserved=0
>>
>>
>> On Fri, Jul 9, 2021, 9:49 PM Ashish Sharma <as...@gmail.com>
>> >
>> wrote:
>>
>> > Hi,
>> >
>> > When casting incorrect date or timestamp literals to DATE or TIMESTAMP
>> > data type hive returns wrong values
>> >
>> > hive> select cast('2020-20-20' as date);
>> >
>> > OK
>> >
>> > 2021-08-20
>> >
>> > Time taken: 4.436 seconds, Fetched: 1 row(s)
>> >
>> >
>> > I have created a solution draft. Please review the draft and provide
>> > your valuable feedback on the same.
>> >
>> >
>> >
>> > https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs
>> > .google.com<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgoogle.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537419060%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=buVSLhJVTluXbYd0iy491oHq2LU2e%2FfKfKYRks1co30%3D&reserved=0>%2Fdocument%2Fd%2F1YTTPlNq3qyzlKfYVkSl3EFhVQ6-wa9WFRdkdIeCo
>> > c1Y%2Fedit%3Fusp%3Dsharing&data=04%7C01%7CSankar.Hariappan%40micro
>> > soft.com<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fsoft.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537429016%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=no6XEmDCYcHNdjQduJdynmsEJpLww56lKSEze4LZ8Qo%3D&reserved=0>%7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7c
>> > d011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoi
>> > MC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&
>> > sdata=iihK9wJC%2B1uPktHSE9BpXADvbal1UT7vZ3rwigkgkIY%3D&reserved=0
>> >
>> >
>> > https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissu
>> > es.apache.org<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fes.apache.org%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537429016%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=W4mMKQSceHXb1NFvkYwtBvy02B81WoX9mAyPHd0F77s%3D&reserved=0>%2Fjira%2Fbrowse%2FHIVE-25306&data=04%7C01%7CSankar.H
>> > ariappan%40microsoft.com<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2F40microsoft.com%2F&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7C5546147ca1b1489c558c08d94b58e75b%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637623667537438972%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=a8wpFdw26oKHEDLE1XqTGVBCIgkHs%2Fj0r9BMnMkszOA%3D&reserved=0>%7Cd47432b9d7654d66a46908d943472338%7C72f988bf
>> > 86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFp
>> > bGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn
>> > 0%3D%7C3000&sdata=nW%2Bw%2B0AYn%2BbvOqRLrXghfH0FG%2B1cQW%2BzdrpT%2
>> > B9R%2B6rA%3D&reserved=0
>> >
>> >
>> > Thank you
>> >
>> > Ashish Sharma
>> >
>>
>
Re: [EXTERNAL] Re: Move Date and Timestamp parsing from
ResolverStyle.LENIENT to ResolverStyle.STRICT
Posted by Ashish Sharma <as...@gmail.com>.
Hi all,
I also feel that adding more config doesn't make sense in this as we are
tightening the date and timestamp format. We should decide upon a single
solution even if it break the compatibility. Below the comparison of HIVE
1.2, HIVE 3.2, MYSQL, PostgreSQL, Oracle
Query
Hive 1.2
Hive 3.2
Mysql
PostgreSQL
ORACLE
select cast('2020-20-20' as date);
NULL
2021-08-20
NULL
date/time field value out of range: "2020-20-20"
not a valid month
select cast(null as date);
NULL
NULL
NULL
NULL
NULL
select cast('2020-02-31' as date);
2020-03-02
2020-03-02
NULL
date/time field value out of range: "2020-02-31"
date format picture ends before converting entire input string
select cast('2020/02/20' as date);
NULL
NULL
2020-02-20
2020-02-20
literal does not match format string
select cast('0000-00-00' as date);
NULL
0002-11-30
NULL
date/time field value out of range: "0000-00-00"
literal does not match format string
From the comparison it is quite clear that date and timestamp formatting
was much tighter in older versions of HIVE. For most of the wrong date
input *NULL *was the standard response instead of Exception.
Also when I went through the code I found that. While doing the Vector
implementation of some of the date related UDF like datediff etc. MySql was
taken as the gold standard
<https://issues.apache.org/jira/browse/HIVE-15338?focusedCommentId=15727553&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-15727553>.
So it make more sense that we should comply with MySql as we already refer
MySql as gold standard and returning NULL as result for wrong dates in cast
is also documented
<https://cwiki.apache.org/confluence/display/hive/languagemanual+types#LanguageManualTypes-CastingDates>
*So I propose to make NULL as the standard response for all parsing errors.*
Thanks
Ashish Sharma
On Tue, Jul 13, 2021 at 9:52 PM Stamatis Zampetakis <za...@gmail.com>
wrote:
> Hi all,
>
> Thanks for pushing this forward Ashish!
>
> Actually I am not in favor of creating a flag for this. Either we decide
> consciously to break backward compatibility in the hope that we are
> improving the expected results or we keep the current behavior.
> Adding another flag means that we maintain and support two variants that
> makes the problem of test coverage brought by David even worse.
>
> I second David's idea to run some tests over some well adopted DBMS (MySQL,
> Oracle, MSSQL, Postgres) to see what they return.
> I think Ashish already did some tests over MySQL and MSSQL but personally I
> would like to see some more (dates + engines) in order to express
> a preference.
> We shouldn't forget that since Hive is implemented in Java, having
> functions that are inline with the Java APIs is not such a bad idea.
> The last comment is slightly supportive of the current behavior.
>
> I am including user@ list in the discussion since we should definitely
> consider the feedback of people that are using Hive for real.
>
> Best,
> Stamatis
>
> On Tue, Jul 13, 2021 at 4:31 PM David <da...@gmail.com> wrote:
>
> > Hello,
> >
> > Is anyone able to try out a few different vendor RDBMS to see how they
> > handle invalid dates, or provide links to documentation, both for invalid
> > formatting and things like mm-dd-yyy 12-40-2021?
> >
> > Thanks.
> >
> > On Tue, Jul 13, 2021 at 5:14 AM Sankar Hariappan
> > <Sa...@microsoft.com.invalid> wrote:
> >
> >> I'm supporting this change to return "NULL" for invalid date/timestamp.
> >> In the interest of backward compatibility, can we make all these changes
> >> under a flag which can be enabled by default?
> >>
> >>
> >> Thanks,
> >> Sankar
> >> -----Original Message-----
> >> From: David <da...@gmail.com>
> >> Sent: 10 July 2021 07:35
> >> To: dev <de...@hive.apache.org>
> >> Cc: sankarh@apache.org; Stamatis Zampetakis <za...@gmail.com>
> >> Subject: [EXTERNAL] Re: Move Date and Timestamp parsing from
> >> ResolverStyle.LENIENT to ResolverStyle.STRICT
> >>
> >> Hello,
> >>
> >> I too would be in favor of this. It drastically cuts down on the test
> >> matrix for Hive if we can clamp down on timestamp formats. With that
> being
> >> said, I've tried this and it's a big effort. I put it down without
> getting
> >> consensus or buy-in or engagement on the effort. Please check out my
> work
> >> here:
> >>
> >>
> >>
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fplugins%2Fservlet%2Fmobile%23issue%2FHIVE-24814&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=mvYgaG7liJOwUZmMvgwlo%2B1HvcUsrnzXA3Ltfz5yEYE%3D&reserved=0
> >>
> >>
> >> On Fri, Jul 9, 2021, 9:49 PM Ashish Sharma <
> ashishkumarsharma20@gmail.com
> >> >
> >> wrote:
> >>
> >> > Hi,
> >> >
> >> > When casting incorrect date or timestamp literals to DATE or TIMESTAMP
> >> > data type hive returns wrong values
> >> >
> >> > hive> select cast('2020-20-20' as date);
> >> >
> >> > OK
> >> >
> >> > 2021-08-20
> >> >
> >> > Time taken: 4.436 seconds, Fetched: 1 row(s)
> >> >
> >> >
> >> > I have created a solution draft. Please review the draft and provide
> >> > your valuable feedback on the same.
> >> >
> >> >
> >> >
> >> >
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs
> >> > .google.com
> %2Fdocument%2Fd%2F1YTTPlNq3qyzlKfYVkSl3EFhVQ6-wa9WFRdkdIeCo
> >> > c1Y%2Fedit%3Fusp%3Dsharing&data=04%7C01%7CSankar.Hariappan%40micro
> >> > soft.com
> %7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7c
> >> > d011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoi
> >> > MC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&
> >> > sdata=iihK9wJC%2B1uPktHSE9BpXADvbal1UT7vZ3rwigkgkIY%3D&reserved=0
> >> >
> >> >
> >> >
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissu
> >> > es.apache.org
> %2Fjira%2Fbrowse%2FHIVE-25306&data=04%7C01%7CSankar.H
> >> > ariappan%40microsoft.com
> %7Cd47432b9d7654d66a46908d943472338%7C72f988bf
> >> > 86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFp
> >> > bGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn
> >> > 0%3D%7C3000&sdata=nW%2Bw%2B0AYn%2BbvOqRLrXghfH0FG%2B1cQW%2BzdrpT%2
> >> > B9R%2B6rA%3D&reserved=0
> >> >
> >> >
> >> > Thank you
> >> >
> >> > Ashish Sharma
> >> >
> >>
> >
>
Re: [EXTERNAL] Re: Move Date and Timestamp parsing from
ResolverStyle.LENIENT to ResolverStyle.STRICT
Posted by Ashish Sharma <as...@gmail.com>.
Hi all,
I also feel that adding more config doesn't make sense in this as we are
tightening the date and timestamp format. We should decide upon a single
solution even if it break the compatibility. Below the comparison of HIVE
1.2, HIVE 3.2, MYSQL, PostgreSQL, Oracle
Query
Hive 1.2
Hive 3.2
Mysql
PostgreSQL
ORACLE
select cast('2020-20-20' as date);
NULL
2021-08-20
NULL
date/time field value out of range: "2020-20-20"
not a valid month
select cast(null as date);
NULL
NULL
NULL
NULL
NULL
select cast('2020-02-31' as date);
2020-03-02
2020-03-02
NULL
date/time field value out of range: "2020-02-31"
date format picture ends before converting entire input string
select cast('2020/02/20' as date);
NULL
NULL
2020-02-20
2020-02-20
literal does not match format string
select cast('0000-00-00' as date);
NULL
0002-11-30
NULL
date/time field value out of range: "0000-00-00"
literal does not match format string
From the comparison it is quite clear that date and timestamp formatting
was much tighter in older versions of HIVE. For most of the wrong date
input *NULL *was the standard response instead of Exception.
Also when I went through the code I found that. While doing the Vector
implementation of some of the date related UDF like datediff etc. MySql was
taken as the gold standard
<https://issues.apache.org/jira/browse/HIVE-15338?focusedCommentId=15727553&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-15727553>.
So it make more sense that we should comply with MySql as we already refer
MySql as gold standard and returning NULL as result for wrong dates in cast
is also documented
<https://cwiki.apache.org/confluence/display/hive/languagemanual+types#LanguageManualTypes-CastingDates>
*So I propose to make NULL as the standard response for all parsing errors.*
Thanks
Ashish Sharma
On Tue, Jul 13, 2021 at 9:52 PM Stamatis Zampetakis <za...@gmail.com>
wrote:
> Hi all,
>
> Thanks for pushing this forward Ashish!
>
> Actually I am not in favor of creating a flag for this. Either we decide
> consciously to break backward compatibility in the hope that we are
> improving the expected results or we keep the current behavior.
> Adding another flag means that we maintain and support two variants that
> makes the problem of test coverage brought by David even worse.
>
> I second David's idea to run some tests over some well adopted DBMS (MySQL,
> Oracle, MSSQL, Postgres) to see what they return.
> I think Ashish already did some tests over MySQL and MSSQL but personally I
> would like to see some more (dates + engines) in order to express
> a preference.
> We shouldn't forget that since Hive is implemented in Java, having
> functions that are inline with the Java APIs is not such a bad idea.
> The last comment is slightly supportive of the current behavior.
>
> I am including user@ list in the discussion since we should definitely
> consider the feedback of people that are using Hive for real.
>
> Best,
> Stamatis
>
> On Tue, Jul 13, 2021 at 4:31 PM David <da...@gmail.com> wrote:
>
> > Hello,
> >
> > Is anyone able to try out a few different vendor RDBMS to see how they
> > handle invalid dates, or provide links to documentation, both for invalid
> > formatting and things like mm-dd-yyy 12-40-2021?
> >
> > Thanks.
> >
> > On Tue, Jul 13, 2021 at 5:14 AM Sankar Hariappan
> > <Sa...@microsoft.com.invalid> wrote:
> >
> >> I'm supporting this change to return "NULL" for invalid date/timestamp.
> >> In the interest of backward compatibility, can we make all these changes
> >> under a flag which can be enabled by default?
> >>
> >>
> >> Thanks,
> >> Sankar
> >> -----Original Message-----
> >> From: David <da...@gmail.com>
> >> Sent: 10 July 2021 07:35
> >> To: dev <de...@hive.apache.org>
> >> Cc: sankarh@apache.org; Stamatis Zampetakis <za...@gmail.com>
> >> Subject: [EXTERNAL] Re: Move Date and Timestamp parsing from
> >> ResolverStyle.LENIENT to ResolverStyle.STRICT
> >>
> >> Hello,
> >>
> >> I too would be in favor of this. It drastically cuts down on the test
> >> matrix for Hive if we can clamp down on timestamp formats. With that
> being
> >> said, I've tried this and it's a big effort. I put it down without
> getting
> >> consensus or buy-in or engagement on the effort. Please check out my
> work
> >> here:
> >>
> >>
> >>
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fplugins%2Fservlet%2Fmobile%23issue%2FHIVE-24814&data=04%7C01%7CSankar.Hariappan%40microsoft.com%7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=mvYgaG7liJOwUZmMvgwlo%2B1HvcUsrnzXA3Ltfz5yEYE%3D&reserved=0
> >>
> >>
> >> On Fri, Jul 9, 2021, 9:49 PM Ashish Sharma <
> ashishkumarsharma20@gmail.com
> >> >
> >> wrote:
> >>
> >> > Hi,
> >> >
> >> > When casting incorrect date or timestamp literals to DATE or TIMESTAMP
> >> > data type hive returns wrong values
> >> >
> >> > hive> select cast('2020-20-20' as date);
> >> >
> >> > OK
> >> >
> >> > 2021-08-20
> >> >
> >> > Time taken: 4.436 seconds, Fetched: 1 row(s)
> >> >
> >> >
> >> > I have created a solution draft. Please review the draft and provide
> >> > your valuable feedback on the same.
> >> >
> >> >
> >> >
> >> >
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs
> >> > .google.com
> %2Fdocument%2Fd%2F1YTTPlNq3qyzlKfYVkSl3EFhVQ6-wa9WFRdkdIeCo
> >> > c1Y%2Fedit%3Fusp%3Dsharing&data=04%7C01%7CSankar.Hariappan%40micro
> >> > soft.com
> %7Cd47432b9d7654d66a46908d943472338%7C72f988bf86f141af91ab2d7c
> >> > d011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFpbGZsb3d8eyJWIjoi
> >> > MC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&
> >> > sdata=iihK9wJC%2B1uPktHSE9BpXADvbal1UT7vZ3rwigkgkIY%3D&reserved=0
> >> >
> >> >
> >> >
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissu
> >> > es.apache.org
> %2Fjira%2Fbrowse%2FHIVE-25306&data=04%7C01%7CSankar.H
> >> > ariappan%40microsoft.com
> %7Cd47432b9d7654d66a46908d943472338%7C72f988bf
> >> > 86f141af91ab2d7cd011db47%7C1%7C0%7C637614795446338436%7CUnknown%7CTWFp
> >> > bGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn
> >> > 0%3D%7C3000&sdata=nW%2Bw%2B0AYn%2BbvOqRLrXghfH0FG%2B1cQW%2BzdrpT%2
> >> > B9R%2B6rA%3D&reserved=0
> >> >
> >> >
> >> > Thank you
> >> >
> >> > Ashish Sharma
> >> >
> >>
> >
>