DRILL-6768: Improve to_date, to_time and to_timestamp and corresponding cast functions to handle empty string when option is enabled

closes #1494

2 files changed, 99 insertions, 7 deletions

diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/FunctionImplementationRegistry.java b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/FunctionImplementationRegistry.java
index 5621c44ca..137969a34 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/FunctionImplementationRegistry.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/FunctionImplementationRegistry.java
@@ -33,6 +33,7 @@ import java.util.Set;
 import java.util.concurrent.TimeUnit;
 import java.util.concurrent.atomic.AtomicInteger;
 
+import org.apache.drill.common.expression.fn.FunctionReplacementUtils;
 import org.apache.drill.shaded.guava.com.google.common.base.Preconditions;
 import org.apache.drill.shaded.guava.com.google.common.collect.Sets;
 import org.apache.drill.shaded.guava.com.google.common.io.Files;
@@ -42,7 +43,6 @@ import org.apache.drill.common.config.CommonConstants;
 import org.apache.drill.common.config.DrillConfig;
 import org.apache.drill.common.exceptions.DrillRuntimeException;
 import org.apache.drill.common.expression.FunctionCall;
-import org.apache.drill.common.expression.fn.CastFunctions;
 import org.apache.drill.common.scanner.ClassPathScanner;
 import org.apache.drill.common.scanner.RunTimeScan;
 import org.apache.drill.common.scanner.persistence.ScanResult;
@@ -204,16 +204,16 @@ public class FunctionImplementationRegistry implements FunctionLookupContext, Au
     if (functionCall.args.size() == 0) {
       return funcName;
     }
-    boolean castToNullableNumeric = optionManager != null &&
-                  optionManager.getOption(ExecConstants.CAST_TO_NULLABLE_NUMERIC_OPTION);
-    if (! castToNullableNumeric) {
+    boolean castEmptyStringToNull = optionManager != null &&
+                  optionManager.getOption(ExecConstants.CAST_EMPTY_STRING_TO_NULL_OPTION);
+    if (!castEmptyStringToNull) {
       return funcName;
     }
     MajorType majorType =  functionCall.args.get(0).getMajorType();
     DataMode dataMode = majorType.getMode();
     MinorType minorType = majorType.getMinorType();
-    if (CastFunctions.isReplacementNeeded(funcName, minorType)) {
-        funcName = CastFunctions.getReplacingCastFunction(funcName, dataMode, minorType);
+    if (FunctionReplacementUtils.isReplacementNeeded(funcName, minorType)) {
+      funcName = FunctionReplacementUtils.getReplacingFunction(funcName, dataMode, minorType);
     }
 
     return funcName;
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/MathFunctions.java b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/MathFunctions.java
index 59236ce27..8470e1fb0 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/MathFunctions.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/MathFunctions.java
@@ -27,6 +27,8 @@ import org.apache.drill.exec.expr.annotations.Output;
 import org.apache.drill.exec.expr.annotations.Param;
 import org.apache.drill.exec.expr.annotations.Workspace;
 import org.apache.drill.exec.expr.holders.Float8Holder;
+import org.apache.drill.exec.expr.holders.NullableFloat8Holder;
+import org.apache.drill.exec.expr.holders.NullableVarCharHolder;
 import org.apache.drill.exec.expr.holders.VarCharHolder;
 
 public class MathFunctions{
@@ -70,6 +72,7 @@ public class MathFunctions{
     @Workspace int decimalDigits;
     @Output Float8Holder out;
 
+    @Override
     public void setup() {
       byte[] buf = new byte[right.end - right.start];
       right.buffer.getBytes(right.start, buf, 0, right.end - right.start);
@@ -77,13 +80,14 @@ public class MathFunctions{
       decimalDigits = inputFormat.getMaximumFractionDigits();
     }
 
+    @Override
     public void eval() {
       byte[] buf1 = new byte[left.end - left.start];
       left.buffer.getBytes(left.start, buf1, 0, left.end - left.start);
       String input = new String(buf1);
       try {
         out.value = inputFormat.parse(input).doubleValue();
-      }  catch(java.text.ParseException e) {
+      }  catch (java.text.ParseException e) {
          throw new UnsupportedOperationException("Cannot parse input: " + input + " with pattern : " + inputFormat.toPattern());
       }
 
@@ -93,6 +97,94 @@ public class MathFunctions{
     }
   }
 
+  @FunctionTemplate(name = "convertVarCharToNumber", scope = FunctionScope.SIMPLE, isInternal = true)
+  public static class ToNullableNumber implements DrillSimpleFunc {
+    @Param
+    VarCharHolder left;
+    @Param
+    VarCharHolder right;
+    @Workspace
+    java.text.DecimalFormat inputFormat;
+    @Workspace
+    int decimalDigits;
+    @Output
+    NullableFloat8Holder out;
+
+    @Override
+    public void setup() {
+      byte[] buf = new byte[right.end - right.start];
+      right.buffer.getBytes(right.start, buf, 0, right.end - right.start);
+      inputFormat = new DecimalFormat(new String(buf));
+      decimalDigits = inputFormat.getMaximumFractionDigits();
+    }
+
+    @Override
+    public void eval() {
+      if (left.start == left.end) {
+        out.isSet = 0;
+        return;
+      }
+      out.isSet = 1;
+
+      byte[] buf1 = new byte[left.end - left.start];
+      left.buffer.getBytes(left.start, buf1, 0, left.end - left.start);
+      String input = new String(buf1);
+      try {
+        out.value = inputFormat.parse(input).doubleValue();
+      } catch (java.text.ParseException e) {
+        throw new UnsupportedOperationException("Cannot parse input: " + input + " with pattern : " + inputFormat.toPattern());
+      }
+
+      // Round the value
+      java.math.BigDecimal roundedValue = new java.math.BigDecimal(out.value);
+      out.value = (roundedValue.setScale(decimalDigits, java.math.BigDecimal.ROUND_HALF_UP)).doubleValue();
+    }
+  }
+
+  @FunctionTemplate(name = "convertNullableVarCharToNumber", scope = FunctionScope.SIMPLE, isInternal = true)
+  public static class ToNullableNumberNullableInput implements DrillSimpleFunc {
+    @Param
+    NullableVarCharHolder left;
+    @Param
+    VarCharHolder right;
+    @Workspace
+    java.text.DecimalFormat inputFormat;
+    @Workspace
+    int decimalDigits;
+    @Output
+    NullableFloat8Holder out;
+
+    @Override
+    public void setup() {
+      byte[] buf = new byte[right.end - right.start];
+      right.buffer.getBytes(right.start, buf, 0, right.end - right.start);
+      inputFormat = new DecimalFormat(new String(buf));
+      decimalDigits = inputFormat.getMaximumFractionDigits();
+    }
+
+    @Override
+    public void eval() {
+      if (left.isSet == 0 || left.start == left.end) {
+        out.isSet = 0;
+        return;
+      }
+      out.isSet = 1;
+
+      byte[] buf1 = new byte[left.end - left.start];
+      left.buffer.getBytes(left.start, buf1, 0, left.end - left.start);
+      String input = new String(buf1);
+      try {
+        out.value = inputFormat.parse(input).doubleValue();
+      } catch (java.text.ParseException e) {
+        throw new UnsupportedOperationException("Cannot parse input: " + input + " with pattern : " + inputFormat.toPattern());
+      }
+
+      // Round the value
+      java.math.BigDecimal roundedValue = new java.math.BigDecimal(out.value);
+      out.value = (roundedValue.setScale(decimalDigits, java.math.BigDecimal.ROUND_HALF_UP)).doubleValue();
+    }
+  }
+
   @FunctionTemplate(name = "pi", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
   public static class Pi implements DrillSimpleFunc {