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 <dam6...@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 > <sankar.hariap...@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 <dam6...@gmail.com> >> Sent: 10 July 2021 07:35 >> To: dev <d...@hive.apache.org> >> Cc: sank...@apache.org; Stamatis Zampetakis <zabe...@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 <ashishkumarsharm...@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 >> > >> >
