[
https://issues.apache.org/jira/browse/DRILL-8340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17622936#comment-17622936
]
ASF GitHub Bot commented on DRILL-8340:
---------------------------------------
jnturton commented on code in PR #2689:
URL: https://github.com/apache/drill/pull/2689#discussion_r1002866316
##########
contrib/udfs/src/test/java/org/apache/drill/exec/udfs/TestDateUtils.java:
##########
@@ -0,0 +1,77 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.drill.exec.udfs;
+
+import org.junit.Test;
+
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+
+import static org.junit.Assert.assertEquals;
+
+public class TestDateUtils {
+
+ @Test
+ public void testDateFromString() {
+ LocalDate testDate = LocalDate.of(2022, 3,14);
+ LocalDate badDate = LocalDate.of(1970, 1, 1);
+ assertEquals(testDate, DateUtilFunctions.getDateFromString("2022-03-14"));
+ assertEquals(testDate, DateUtilFunctions.getDateFromString("3/14/2022"));
+ assertEquals(testDate, DateUtilFunctions.getDateFromString("14/03/2022",
true));
+ assertEquals(testDate, DateUtilFunctions.getDateFromString("2022/3/14"));
+
+ // Test bad dates
+ assertEquals(badDate, DateUtilFunctions.getDateFromString(null));
+ assertEquals(badDate, DateUtilFunctions.getDateFromString("1975-13-56"));
+ assertEquals(badDate, DateUtilFunctions.getDateFromString("1975-1s"));
Review Comment:
One possibility is to recast the question from a debate to asking what the
industry norm is. In my experience other SQL engines don't usually return
default values for invalid data and I'd bet that the reason is that the
engineers recognised what lies down that road. Remember the proposal for
numerical functions to return float values of NaN, or a special finite number,
when they encountered invalid data and how that breaks null elimination in
aggregates making things like SUM and AVG silently return useless or bogus
answers rather than helpfully skip invalid data.
If we want to provide users with a short hand for
`foo_func(coalesce(int_col, a_default_int))` then I propose that we
- look for a general mechanism rather having just one or two functions work
this way and
- force the user to think about what they're doing and what Drill is doing
so they think about the potential impact on their results
- at the same time make sure we're doing things like null-if-null everywhere
we should be so that secondary functions don't break in the first place, but
sensibly return null themselves.
One way might be a facility to for users to define aliases for SQL
expressions something like
```
create alias foo_with_default(INT int_col) as foo_func(coalesce(int_col,
a_default_int))
```
Maybe we can chat with @vvysotskyi about whether a feature like this would
work (and his thoughts on default-for-invalid logic).
> Add Additional Date Manipulation Functions (Part 1)
> ---------------------------------------------------
>
> Key: DRILL-8340
> URL: https://issues.apache.org/jira/browse/DRILL-8340
> Project: Apache Drill
> Issue Type: Improvement
> Components: Functions - Drill
> Affects Versions: 1.20.2
> Reporter: Charles Givre
> Assignee: Charles Givre
> Priority: Major
> Fix For: 2.0.0
>
>
> This PR adds several utility functions to facilitate working with dates and
> times. These are modeled after the date/time functionality in MySQL.
> Specifically this adds:
> * YEARWEEK(<date>): Returns an int of year week. IE (202002)
> * TIME_STAMP(<date string>): Converts most anything that looks like a date
> string into a timestamp.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
