You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by me...@apache.org on 2015/02/12 03:09:33 UTC

[1/3] drill git commit: DRILL-2199: Fix major type returned for convert to nullable type functions

Repository: drill
Updated Branches:
  refs/heads/merge_15_02_11_2 [created] b79f76619


DRILL-2199: Fix major type returned for convert to nullable type functions


Project: http://git-wip-us.apache.org/repos/asf/drill/repo
Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/9f9fb6c9
Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/9f9fb6c9
Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/9f9fb6c9

Branch: refs/heads/merge_15_02_11_2
Commit: 9f9fb6c95b85c8c3c63d3aa8b1fc1daf90bae6f3
Parents: 21432de
Author: Mehant Baid <me...@gmail.com>
Authored: Mon Feb 9 18:01:23 2015 -0800
Committer: Mehant Baid <me...@gmail.com>
Committed: Wed Feb 11 17:00:29 2015 -0800

----------------------------------------------------------------------
 .../drill/common/util/CoreDecimalUtility.java   | 12 ++++
 .../templates/ConvertToNullableHolder.java      |  4 ++
 .../exec/expr/ExpressionTreeMaterializer.java   | 64 +++++++-------------
 .../expr/fn/DrillDecimalMaxScaleFuncHolder.java | 19 +++---
 .../org/apache/drill/TestFunctionsQuery.java    | 14 +++++
 5 files changed, 62 insertions(+), 51 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/drill/blob/9f9fb6c9/common/src/main/java/org/apache/drill/common/util/CoreDecimalUtility.java
----------------------------------------------------------------------
diff --git a/common/src/main/java/org/apache/drill/common/util/CoreDecimalUtility.java b/common/src/main/java/org/apache/drill/common/util/CoreDecimalUtility.java
index c5cd8c0..302652e 100644
--- a/common/src/main/java/org/apache/drill/common/util/CoreDecimalUtility.java
+++ b/common/src/main/java/org/apache/drill/common/util/CoreDecimalUtility.java
@@ -73,4 +73,16 @@ public class CoreDecimalUtility {
 
     return (input.unscaledValue().intValue());
   }
+
+  /*
+   * Helper function to detect if the given data type is Decimal
+   */
+  public static boolean isDecimalType(TypeProtos.MajorType type) {
+    TypeProtos.MinorType minorType = type.getMinorType();
+    if (minorType == TypeProtos.MinorType.DECIMAL9 || minorType == TypeProtos.MinorType.DECIMAL18 ||
+        minorType == TypeProtos.MinorType.DECIMAL28SPARSE || minorType == TypeProtos.MinorType.DECIMAL38SPARSE) {
+      return true;
+    }
+    return false;
+  }
 }

http://git-wip-us.apache.org/repos/asf/drill/blob/9f9fb6c9/exec/java-exec/src/main/codegen/templates/ConvertToNullableHolder.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/main/codegen/templates/ConvertToNullableHolder.java b/exec/java-exec/src/main/codegen/templates/ConvertToNullableHolder.java
index 89a400d..57efd5e 100644
--- a/exec/java-exec/src/main/codegen/templates/ConvertToNullableHolder.java
+++ b/exec/java-exec/src/main/codegen/templates/ConvertToNullableHolder.java
@@ -32,7 +32,11 @@ import org.apache.drill.exec.expr.annotations.*;
 import org.apache.drill.exec.expr.holders.*;
 import org.apache.drill.exec.record.RecordBatch;
 
+<#if minor.class.startsWith("Decimal")>
+@FunctionTemplate(name = "convertToNullable${minor.class?upper_case}", scope = FunctionTemplate.FunctionScope.DECIMAL_MAX_SCALE, nulls = FunctionTemplate.NullHandling.INTERNAL)
+<#else>
 @FunctionTemplate(name = "convertToNullable${minor.class?upper_case}", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.INTERNAL)
+</#if>
 public class ${className} implements DrillSimpleFunc {
 
   @Param ${minor.class}Holder input;

http://git-wip-us.apache.org/repos/asf/drill/blob/9f9fb6c9/exec/java-exec/src/main/java/org/apache/drill/exec/expr/ExpressionTreeMaterializer.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/ExpressionTreeMaterializer.java b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/ExpressionTreeMaterializer.java
index 8557765..e1ad854 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/ExpressionTreeMaterializer.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/ExpressionTreeMaterializer.java
@@ -58,6 +58,7 @@ import org.apache.drill.common.types.TypeProtos.DataMode;
 import org.apache.drill.common.types.TypeProtos.MajorType;
 import org.apache.drill.common.types.TypeProtos.MinorType;
 import org.apache.drill.common.types.Types;
+import org.apache.drill.common.util.CoreDecimalUtility;
 import org.apache.drill.exec.expr.annotations.FunctionTemplate;
 import org.apache.drill.exec.expr.fn.AbstractFuncHolder;
 import org.apache.drill.exec.expr.fn.DrillComplexWriterFuncHolder;
@@ -108,20 +109,19 @@ public class ExpressionTreeMaterializer {
 
     if (!Types.isFixedWidthType(toType)) {
 
-        /* We are implicitly casting to VARCHAR so we don't have a max length,
-         * using an arbitrary value. We trim down the size of the stored bytes
-         * to the actual size so this size doesn't really matter.
-         */
+      /* We are implicitly casting to VARCHAR so we don't have a max length,
+       * using an arbitrary value. We trim down the size of the stored bytes
+       * to the actual size so this size doesn't really matter.
+       */
       castArgs.add(new ValueExpressions.LongExpression(65536, null));
     }
-    else if (toType.getMinorType().name().startsWith("DECIMAL")) {
+    else if (CoreDecimalUtility.isDecimalType(toType)) {
       // Add the scale and precision to the arguments of the implicit cast
-      castArgs.add(new ValueExpressions.LongExpression(fromExpr.getMajorType().getPrecision(), null));
-      castArgs.add(new ValueExpressions.LongExpression(fromExpr.getMajorType().getScale(), null));
+      castArgs.add(new ValueExpressions.LongExpression(toType.getPrecision(), null));
+      castArgs.add(new ValueExpressions.LongExpression(toType.getScale(), null));
     }
-
     FunctionCall castCall = new FunctionCall(castFuncName, castArgs, ExpressionPosition.UNKNOWN);
-    FunctionResolver resolver = FunctionResolverFactory.getResolver(castCall);
+    FunctionResolver resolver = FunctionResolverFactory.getExactResolver(castCall);
     DrillFuncHolder matchedCastFuncHolder = registry.findDrillFunction(resolver, castCall);
 
     if (matchedCastFuncHolder == null) {
@@ -241,7 +241,12 @@ public class ExpressionTreeMaterializer {
             argsWithCast.add(currentArg);
           } else {
             //Case 3: insert cast if param type is different from arg type.
-            argsWithCast.add(addCastExpression(call.args.get(i), parmType, registry, errorCollector));
+            if (CoreDecimalUtility.isDecimalType(parmType)) {
+              // We are implicitly promoting a decimal type, set the required scale and precision
+              parmType = MajorType.newBuilder().setMinorType(parmType.getMinorType()).setMode(parmType.getMode()).
+                  setScale(currentArg.getMajorType().getScale()).setPrecision(currentArg.getMajorType().getPrecision()).build();
+            }
+            argsWithCast.add(addCastExpression(currentArg, parmType, registry, errorCollector));
           }
         }
 
@@ -262,6 +267,11 @@ public class ExpressionTreeMaterializer {
             extArgsWithCast.add(currentArg);
           } else {
             // Insert cast if param type is different from arg type.
+            if (CoreDecimalUtility.isDecimalType(parmType)) {
+              // We are implicitly promoting a decimal type, set the required scale and precision
+              parmType = MajorType.newBuilder().setMinorType(parmType.getMinorType()).setMode(parmType.getMode()).
+                  setScale(currentArg.getMajorType().getScale()).setPrecision(currentArg.getMajorType().getPrecision()).build();
+            }
             extArgsWithCast.add(addCastExpression(call.args.get(i), parmType, registry, errorCollector));
           }
         }
@@ -273,38 +283,6 @@ public class ExpressionTreeMaterializer {
       return NullExpression.INSTANCE;
     }
 
-    public static LogicalExpression addCastExpression(LogicalExpression fromExpr, MajorType toType, FunctionImplementationRegistry registry, ErrorCollector errorCollector) {
-      String castFuncName = CastFunctions.getCastFunc(toType.getMinorType());
-      List<LogicalExpression> castArgs = Lists.newArrayList();
-      castArgs.add(fromExpr);  //input_expr
-
-      if (!Types.isFixedWidthType(toType)) {
-
-        /* We are implicitly casting to VARCHAR so we don't have a max length,
-         * using an arbitrary value. We trim down the size of the stored bytes
-         * to the actual size so this size doesn't really matter.
-         */
-        castArgs.add(new ValueExpressions.LongExpression(65536, null));
-      }
-      else if (toType.getMinorType().name().startsWith("DECIMAL")) {
-        // Add the scale and precision to the arguments of the implicit cast
-        castArgs.add(new ValueExpressions.LongExpression(fromExpr.getMajorType().getPrecision(), null));
-        castArgs.add(new ValueExpressions.LongExpression(fromExpr.getMajorType().getScale(), null));
-      }
-
-      FunctionCall castCall = new FunctionCall(castFuncName, castArgs, ExpressionPosition.UNKNOWN);
-      FunctionResolver resolver = FunctionResolverFactory.getExactResolver(castCall);
-      DrillFuncHolder matchedCastFuncHolder = registry.findDrillFunction(resolver, castCall);
-
-      if (matchedCastFuncHolder == null) {
-        logFunctionResolutionError(errorCollector, castCall);
-        return NullExpression.INSTANCE;
-      }
-
-      return matchedCastFuncHolder.getExpr(castFuncName, castArgs, ExpressionPosition.UNKNOWN);
-
-    }
-
     @Override
     public LogicalExpression visitIfExpression(IfExpression ifExpr, FunctionImplementationRegistry registry) {
       IfExpression.IfCondition conditions = ifExpr.ifCondition;
@@ -546,7 +524,7 @@ public class ExpressionTreeMaterializer {
         //VarLen type
         if (!Types.isFixedWidthType(type)) {
           newArgs.add(new ValueExpressions.LongExpression(type.getWidth(), null));
-        } else if (type.getMinorType().name().startsWith("DECIMAL")) {
+        }  if (CoreDecimalUtility.isDecimalType(type)) {
             newArgs.add(new ValueExpressions.LongExpression(type.getPrecision(), null));
             newArgs.add(new ValueExpressions.LongExpression(type.getScale(), null));
         }

http://git-wip-us.apache.org/repos/asf/drill/blob/9f9fb6c9/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/DrillDecimalMaxScaleFuncHolder.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/DrillDecimalMaxScaleFuncHolder.java b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/DrillDecimalMaxScaleFuncHolder.java
index 7e54586..aa8e2b5 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/DrillDecimalMaxScaleFuncHolder.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/DrillDecimalMaxScaleFuncHolder.java
@@ -39,19 +39,22 @@ public class DrillDecimalMaxScaleFuncHolder extends DrillSimpleFuncHolder{
     public MajorType getReturnType(List<LogicalExpression> args) {
 
         TypeProtos.DataMode mode = returnValue.type.getMode();
+        boolean nullInput = false;
         int scale = 0;
         int precision = 0;
 
-        if (nullHandling == NullHandling.NULL_IF_NULL) {
-            // if any one of the input types is nullable, then return nullable return type
-            for (LogicalExpression e : args) {
-                if (e.getMajorType().getMode() == TypeProtos.DataMode.OPTIONAL) {
-                    mode = TypeProtos.DataMode.OPTIONAL;
-                }
-                scale = Math.max(scale, e.getMajorType().getScale());
-                precision = Math.max(precision, e.getMajorType().getPrecision());
+        for (LogicalExpression e : args) {
+            if (e.getMajorType().getMode() == TypeProtos.DataMode.OPTIONAL) {
+                nullInput = true;
             }
+            scale = Math.max(scale, e.getMajorType().getScale());
+            precision = Math.max(precision, e.getMajorType().getPrecision());
         }
+
+        if (nullHandling == NullHandling.NULL_IF_NULL && nullInput) {
+            mode = TypeProtos.DataMode.OPTIONAL;
+        }
+
         return (TypeProtos.MajorType.newBuilder().setMinorType(returnValue.type.getMinorType()).setScale(scale).setPrecision(precision).setMode(mode).build());
     }
 }

http://git-wip-us.apache.org/repos/asf/drill/blob/9f9fb6c9/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java b/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java
index 7356640..f1005ab 100644
--- a/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java
+++ b/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java
@@ -695,4 +695,18 @@ public class TestFunctionsQuery extends BaseTestQuery {
         .baselineValues(time1, time2, ts1, ts2, date1, date2)
         .go();
   }
+
+  @Test
+  public void testCaseWithDecimalExpressions() throws Exception {
+    String query = "select " +
+        "case when true then cast(employee_id as decimal(15, 5)) else cast('0.0' as decimal(2, 1)) end as col1 " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("col1")
+        .baselineValues(new BigDecimal("1.00000"))
+        .go();
+  }
 }


[3/3] drill git commit: DRILL-2107: Fix hash aggregate to return OK_NEW_SCHEMA only once. Existing tests will serve as unit tests once swap join rule is enabled

Posted by me...@apache.org.
DRILL-2107: Fix hash aggregate to return OK_NEW_SCHEMA only once.
Existing tests will serve as unit tests once swap join rule is enabled


Project: http://git-wip-us.apache.org/repos/asf/drill/repo
Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/b79f7661
Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/b79f7661
Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/b79f7661

Branch: refs/heads/merge_15_02_11_2
Commit: b79f76619b5290e465921219fcd09091a3d93607
Parents: 9f9fb6c
Author: Mehant Baid <me...@gmail.com>
Authored: Sat Feb 7 19:14:01 2015 -0800
Committer: Mehant Baid <me...@gmail.com>
Committed: Wed Feb 11 17:00:48 2015 -0800

----------------------------------------------------------------------
 .../physical/impl/aggregate/HashAggBatch.java   | 38 ++------------------
 .../impl/aggregate/HashAggTemplate.java         | 31 ++++++----------
 2 files changed, 13 insertions(+), 56 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/drill/blob/b79f7661/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggBatch.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggBatch.java b/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggBatch.java
index 0e2a017..5946d43 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggBatch.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggBatch.java
@@ -49,6 +49,7 @@ import org.apache.drill.exec.record.TypedFieldId;
 import org.apache.drill.exec.record.VectorWrapper;
 import org.apache.drill.exec.record.selection.SelectionVector2;
 import org.apache.drill.exec.record.selection.SelectionVector4;
+import org.apache.drill.exec.vector.AllocationHelper;
 import org.apache.drill.exec.vector.ValueVector;
 
 import com.sun.codemodel.JExpr;
@@ -101,47 +102,12 @@ public class HashAggBatch extends AbstractRecordBatch<HashAggregate> {
       state = BatchState.DONE;
     }
     for (VectorWrapper w : container) {
-      w.getValueVector().allocateNew();
+      AllocationHelper.allocatePrecomputedChildCount(w.getValueVector(), 0, 0, 0);
     }
   }
 
   @Override
   public IterOutcome innerNext() {
-    // this is only called on the first batch. Beyond this, the aggregator manages batches.
-    if (aggregator == null || state == BatchState.FIRST) {
-      if (aggregator != null) {
-        aggregator.cleanup();
-      }
-      IterOutcome outcome;
-      if (state == BatchState.FIRST) {
-        state = BatchState.NOT_FIRST;
-        outcome = IterOutcome.OK;
-      } else {
-        outcome = next(incoming);
-      }
-      if (outcome == IterOutcome.OK) {
-        outcome = IterOutcome.OK_NEW_SCHEMA;
-      }
-      logger.debug("Next outcome of {}", outcome);
-      switch (outcome) {
-      case NONE:
-        //        throw new UnsupportedOperationException("Received NONE on first batch");
-        return outcome;
-      case NOT_YET:
-      case STOP:
-        return outcome;
-      case OK_NEW_SCHEMA:
-        if (!createAggregator()) {
-          state = BatchState.DONE;
-          return IterOutcome.STOP;
-        }
-        break;
-      case OK:
-        break;
-      default:
-        throw new IllegalStateException(String.format("unknown outcome %s", outcome));
-      }
-    }
 
     if (aggregator.allFlushed()) {
       return IterOutcome.NONE;

http://git-wip-us.apache.org/repos/asf/drill/blob/b79f7661/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggTemplate.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggTemplate.java b/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggTemplate.java
index 73cadb2..87cd4d6 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggTemplate.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggTemplate.java
@@ -65,12 +65,12 @@ public abstract class HashAggTemplate implements HashAggregator {
 
   private static final long ALLOCATOR_INITIAL_RESERVATION = 1 * 1024 * 1024;
   private static final long ALLOCATOR_MAX_RESERVATION = 20L * 1000 * 1000 * 1000;
+  private static final int VARIABLE_WIDTH_VALUE_SIZE = 50;
 
   private static final boolean EXTRA_DEBUG_1 = false;
   private static final boolean EXTRA_DEBUG_2 = false;
   private static final String TOO_BIG_ERROR =
       "Couldn't add value to an empty batch.  This likely means that a single value is too long for a varlen field.";
-  private boolean first = true;
   private boolean newSchema = false;
   private int underlyingIndex = 0;
   private int currentIndex = 0;
@@ -370,9 +370,6 @@ public abstract class HashAggTemplate implements HashAggregator {
         }
       }
     } finally {
-      if (first) {
-        first = !first;
-      }
     }
   }
 
@@ -386,11 +383,13 @@ public abstract class HashAggTemplate implements HashAggregator {
     while (outgoingIter.hasNext()) {
       ValueVector vv = outgoingIter.next().getValueVector();
       MajorType type = vv.getField().getType();
-      if (!Types.isFixedWidthType(type) || Types.isRepeated(type)) {
-        vv.allocateNew();
-      } else {
-        AllocationHelper.allocate(vv, records, 1);
-      }
+
+      /*
+       * In build schema we use the allocation model that specifies exact record count
+       * so we need to stick with that allocation model until DRILL-2211 is resolved. Using
+       * 50 as the average bytes per value as is used in HashTable.
+       */
+      AllocationHelper.allocatePrecomputedChildCount(vv, records, VARIABLE_WIDTH_VALUE_SIZE, 0);
     }
   }
 
@@ -426,11 +425,7 @@ public abstract class HashAggTemplate implements HashAggregator {
   }
 
   private final AggOutcome setOkAndReturn() {
-    if (first) {
-      this.outcome = IterOutcome.OK_NEW_SCHEMA;
-    } else {
-      this.outcome = IterOutcome.OK;
-    }
+    this.outcome = IterOutcome.OK;
     for (VectorWrapper<?> v : outgoing) {
       v.getValueVector().getMutator().setValueCount(outputCount);
     }
@@ -471,7 +466,7 @@ public abstract class HashAggTemplate implements HashAggregator {
     // get the number of records in the batch holder that are pending output
     int numPendingOutput = batchHolders.get(outBatchIndex).getNumPendingOutput();
 
-    if (!first && numPendingOutput == 0) {
+    if (numPendingOutput == 0) {
       this.outcome = IterOutcome.NONE;
       return outcome;
     }
@@ -493,11 +488,7 @@ public abstract class HashAggTemplate implements HashAggregator {
 
     outputCount += numOutputRecords;
 
-    if (first) {
-      this.outcome = IterOutcome.OK_NEW_SCHEMA;
-    } else {
-      this.outcome = IterOutcome.OK;
-    }
+    this.outcome = IterOutcome.OK;
 
     logger.debug("HashAggregate: Output current batch index {} with {} records.", outBatchIndex, numOutputRecords);
 


[2/3] drill git commit: DRILL-2184: Move TestFunctionsQuery under execution tests and use the new test framework to verify results

Posted by me...@apache.org.
DRILL-2184: Move TestFunctionsQuery under execution tests and use the new test framework to verify results


Project: http://git-wip-us.apache.org/repos/asf/drill/repo
Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/21432dec
Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/21432dec
Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/21432dec

Branch: refs/heads/merge_15_02_11_2
Commit: 21432decf74f0130b2e76f8266038774779249b9
Parents: 00c08ef
Author: mehant <me...@gmail.com>
Authored: Sun Feb 8 19:48:05 2015 -0800
Committer: Mehant Baid <me...@gmail.com>
Committed: Wed Feb 11 17:00:29 2015 -0800

----------------------------------------------------------------------
 .../org/apache/drill/TestFunctionsQuery.java    | 698 +++++++++++++++++
 .../drill/jdbc/test/TestFunctionsQuery.java     | 758 -------------------
 2 files changed, 698 insertions(+), 758 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/drill/blob/21432dec/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java b/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java
new file mode 100644
index 0000000..7356640
--- /dev/null
+++ b/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java
@@ -0,0 +1,698 @@
+/**
+ * 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;
+
+import org.apache.drill.exec.expr.fn.impl.DateUtility;
+import org.joda.time.DateTime;
+import org.junit.Ignore;
+import org.junit.Test;
+
+import java.math.BigDecimal;
+
+public class TestFunctionsQuery extends BaseTestQuery {
+
+  @Test
+  public void testAbsDecimalFunction() throws Exception{
+    String query = "SELECT " +
+        "abs(cast('1234.4567' as decimal(9, 5))) as DEC9_ABS_1, " +
+        "abs(cast('-1234.4567' as decimal(9, 5))) DEC9_ABS_2, " +
+        "abs(cast('99999912399.4567' as decimal(18, 5))) DEC18_ABS_1, " +
+        "abs(cast('-99999912399.4567' as decimal(18, 5))) DEC18_ABS_2, " +
+        "abs(cast('12345678912345678912.4567' as decimal(28, 5))) DEC28_ABS_1, " +
+        "abs(cast('-12345678912345678912.4567' as decimal(28, 5))) DEC28_ABS_2, " +
+        "abs(cast('1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_ABS_1, " +
+        "abs(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_ABS_2 " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC9_ABS_1", "DEC9_ABS_2", "DEC18_ABS_1", "DEC18_ABS_2", "DEC28_ABS_1", "DEC28_ABS_2", "DEC38_ABS_1", "DEC38_ABS_2")
+        .baselineValues(new BigDecimal("1234.45670"), new BigDecimal("1234.45670"), new BigDecimal("99999912399.45670"), new BigDecimal("99999912399.45670"),
+            new BigDecimal("12345678912345678912.45670"), new BigDecimal("12345678912345678912.45670"), new BigDecimal("1234567891234567891234567891234567891.4"),
+            new BigDecimal("1234567891234567891234567891234567891.4"))
+        .go();
+  }
+
+
+  @Test
+  public void testCeilDecimalFunction() throws Exception {
+    String query = "SELECT " +
+        "ceil(cast('1234.4567' as decimal(9, 5))) as DEC9_1, " +
+        "ceil(cast('1234.0000' as decimal(9, 5))) as DEC9_2, " +
+        "ceil(cast('-1234.4567' as decimal(9, 5))) as DEC9_3, " +
+        "ceil(cast('-1234.000' as decimal(9, 5))) as DEC9_4, " +
+        "ceil(cast('99999912399.4567' as decimal(18, 5))) DEC18_1, " +
+        "ceil(cast('99999912399.0000' as decimal(18, 5))) DEC18_2, " +
+        "ceil(cast('-99999912399.4567' as decimal(18, 5))) DEC18_3, " +
+        "ceil(cast('-99999912399.0000' as decimal(18, 5))) DEC18_4, " +
+        "ceil(cast('12345678912345678912.4567' as decimal(28, 5))) DEC28_1, " +
+        "ceil(cast('999999999999999999.4567' as decimal(28, 5))) DEC28_2, " +
+        "ceil(cast('12345678912345678912.0000' as decimal(28, 5))) DEC28_3, " +
+        "ceil(cast('-12345678912345678912.4567' as decimal(28, 5))) DEC28_4, " +
+        "ceil(cast('-12345678912345678912.0000' as decimal(28, 5))) DEC28_5, " +
+        "ceil(cast('1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_1, " +
+        "ceil(cast('999999999999999999999999999999999999.4' as decimal(38, 1))) DEC38_2, " +
+        "ceil(cast('1234567891234567891234567891234567891.0' as decimal(38, 1))) DEC38_3, " +
+        "ceil(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_4, " +
+        "ceil(cast('-1234567891234567891234567891234567891.0' as decimal(38, 1))) DEC38_5 " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC9_1", "DEC9_2", "DEC9_3", "DEC9_4", "DEC18_1", "DEC18_2", "DEC18_3", "DEC18_4", "DEC28_1",
+            "DEC28_2", "DEC28_3", "DEC28_4", "DEC28_5", "DEC38_1", "DEC38_2", "DEC38_3", "DEC38_4", "DEC38_5")
+        .baselineValues(new BigDecimal("1235"), new BigDecimal("1234"), new BigDecimal("-1234"), new BigDecimal("-1234"),
+            new BigDecimal("99999912400"), new BigDecimal("99999912399"), new BigDecimal("-99999912399"), new BigDecimal("-99999912399"),
+            new BigDecimal("12345678912345678913"), new BigDecimal("1000000000000000000"), new BigDecimal("12345678912345678912"), new BigDecimal("-12345678912345678912"),
+            new BigDecimal("-12345678912345678912"), new BigDecimal("1234567891234567891234567891234567892"), new BigDecimal("1000000000000000000000000000000000000"),
+            new BigDecimal("1234567891234567891234567891234567891"), new BigDecimal("-1234567891234567891234567891234567891"),
+            new BigDecimal("-1234567891234567891234567891234567891"))
+        .go();
+  }
+
+  @Test
+  public void testFloorDecimalFunction() throws Exception {
+    String query = "SELECT " +
+        "floor(cast('1234.4567' as decimal(9, 5))) as DEC9_1, " +
+        "floor(cast('1234.0000' as decimal(9, 5))) as DEC9_2, " +
+        "floor(cast('-1234.4567' as decimal(9, 5))) as DEC9_3, " +
+        "floor(cast('-1234.000' as decimal(9, 5))) as DEC9_4, " +
+        "floor(cast('99999912399.4567' as decimal(18, 5))) DEC18_1, " +
+        "floor(cast('99999912399.0000' as decimal(18, 5))) DEC18_2, " +
+        "floor(cast('-99999912399.4567' as decimal(18, 5))) DEC18_3, " +
+        "floor(cast('-99999912399.0000' as decimal(18, 5))) DEC18_4, " +
+        "floor(cast('12345678912345678912.4567' as decimal(28, 5))) DEC28_1, " +
+        "floor(cast('999999999999999999.4567' as decimal(28, 5))) DEC28_2, " +
+        "floor(cast('12345678912345678912.0000' as decimal(28, 5))) DEC28_3, " +
+        "floor(cast('-12345678912345678912.4567' as decimal(28, 5))) DEC28_4, " +
+        "floor(cast('-12345678912345678912.0000' as decimal(28, 5))) DEC28_5, " +
+        "floor(cast('1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_1, " +
+        "floor(cast('999999999999999999999999999999999999.4' as decimal(38, 1))) DEC38_2, " +
+        "floor(cast('1234567891234567891234567891234567891.0' as decimal(38, 1))) DEC38_3, " +
+        "floor(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_4, " +
+        "floor(cast('-999999999999999999999999999999999999.4' as decimal(38, 1))) DEC38_5 " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC9_1", "DEC9_2", "DEC9_3", "DEC9_4", "DEC18_1", "DEC18_2", "DEC18_3", "DEC18_4", "DEC28_1",
+            "DEC28_2", "DEC28_3", "DEC28_4", "DEC28_5", "DEC38_1", "DEC38_2", "DEC38_3", "DEC38_4", "DEC38_5")
+        .baselineValues(new BigDecimal("1234"), new BigDecimal("1234"), new BigDecimal("-1235"), new BigDecimal("-1234"),
+            new BigDecimal("99999912399"), new BigDecimal("99999912399"), new BigDecimal("-99999912400"), new BigDecimal("-99999912399"),
+            new BigDecimal("12345678912345678912"), new BigDecimal("999999999999999999"), new BigDecimal("12345678912345678912"),
+            new BigDecimal("-12345678912345678913"), new BigDecimal("-12345678912345678912"), new BigDecimal("1234567891234567891234567891234567891"),
+            new BigDecimal("999999999999999999999999999999999999"), new BigDecimal("1234567891234567891234567891234567891"),
+            new BigDecimal("-1234567891234567891234567891234567892"), new BigDecimal("-1000000000000000000000000000000000000"))
+        .go();
+  }
+
+  @Test
+  public void testTruncateDecimalFunction() throws Exception {
+    String query = "SELECT " +
+        "trunc(cast('1234.4567' as decimal(9, 5))) as DEC9_1, " +
+        "trunc(cast('1234.0000' as decimal(9, 5))) as DEC9_2, " +
+        "trunc(cast('-1234.4567' as decimal(9, 5))) as DEC9_3, " +
+        "trunc(cast('0.111' as decimal(9, 5))) as DEC9_4, " +
+        "trunc(cast('99999912399.4567' as decimal(18, 5))) DEC18_1, " +
+        "trunc(cast('99999912399.0000' as decimal(18, 5))) DEC18_2, " +
+        "trunc(cast('-99999912399.4567' as decimal(18, 5))) DEC18_3, " +
+        "trunc(cast('-99999912399.0000' as decimal(18, 5))) DEC18_4, " +
+        "trunc(cast('12345678912345678912.4567' as decimal(28, 5))) DEC28_1, " +
+        "trunc(cast('999999999999999999.4567' as decimal(28, 5))) DEC28_2, " +
+        "trunc(cast('12345678912345678912.0000' as decimal(28, 5))) DEC28_3, " +
+        "trunc(cast('-12345678912345678912.4567' as decimal(28, 5))) DEC28_4, " +
+        "trunc(cast('-12345678912345678912.0000' as decimal(28, 5))) DEC28_5, " +
+        "trunc(cast('1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_1, " +
+        "trunc(cast('999999999999999999999999999999999999.4' as decimal(38, 1))) DEC38_2, " +
+        "trunc(cast('1234567891234567891234567891234567891.0' as decimal(38, 1))) DEC38_3, " +
+        "trunc(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_4, " +
+        "trunc(cast('-999999999999999999999999999999999999.4' as decimal(38, 1))) DEC38_5 " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC9_1", "DEC9_2", "DEC9_3", "DEC9_4", "DEC18_1", "DEC18_2", "DEC18_3", "DEC18_4", "DEC28_1",
+            "DEC28_2", "DEC28_3", "DEC28_4", "DEC28_5", "DEC38_1", "DEC38_2", "DEC38_3", "DEC38_4", "DEC38_5")
+        .baselineValues(new BigDecimal("1234"), new BigDecimal("1234"), new BigDecimal("-1234"), new BigDecimal("0"),
+            new BigDecimal("99999912399"), new BigDecimal("99999912399"), new BigDecimal("-99999912399"),
+            new BigDecimal("-99999912399"), new BigDecimal("12345678912345678912"), new BigDecimal("999999999999999999"),
+            new BigDecimal("12345678912345678912"), new BigDecimal("-12345678912345678912"), new BigDecimal("-12345678912345678912"),
+            new BigDecimal("1234567891234567891234567891234567891"), new BigDecimal("999999999999999999999999999999999999"),
+            new BigDecimal("1234567891234567891234567891234567891"), new BigDecimal("-1234567891234567891234567891234567891"),
+            new BigDecimal("-999999999999999999999999999999999999"))
+        .go();
+  }
+
+  @Test
+  public void testTruncateWithParamDecimalFunction() throws Exception {
+    String query = "SELECT " +
+        "trunc(cast('1234.4567' as decimal(9, 5)), 2) as DEC9_1, " +
+        "trunc(cast('1234.45' as decimal(9, 2)), 4) as DEC9_2, " +
+        "trunc(cast('-1234.4567' as decimal(9, 5)), 0) as DEC9_3, " +
+        "trunc(cast('0.111' as decimal(9, 5)), 2) as DEC9_4, " +
+        "trunc(cast('99999912399.4567' as decimal(18, 5)), 2) DEC18_1, " +
+        "trunc(cast('99999912399.0000' as decimal(18, 5)), 2) DEC18_2, " +
+        "trunc(cast('-99999912399.45' as decimal(18, 2)), 6) DEC18_3, " +
+        "trunc(cast('-99999912399.0000' as decimal(18, 5)), 4) DEC18_4, " +
+        "trunc(cast('12345678912345678912.4567' as decimal(28, 5)), 1) DEC28_1, " +
+        "trunc(cast('999999999999999999.456' as decimal(28, 3)), 6) DEC28_2, " +
+        "trunc(cast('12345678912345678912.0000' as decimal(28, 5)), 2) DEC28_3, " +
+        "trunc(cast('-12345678912345678912.45' as decimal(28, 2)), 0) DEC28_4, " +
+        "trunc(cast('-12345678912345678912.0000' as decimal(28, 5)), 1) DEC28_5, " +
+        "trunc(cast('999999999.123456789' as decimal(38, 9)), 7) DEC38_1, " +
+        "trunc(cast('999999999.4' as decimal(38, 1)), 8) DEC38_2, " +
+        "trunc(cast('999999999.1234' as decimal(38, 4)), 12) DEC38_3, " +
+        "trunc(cast('-123456789123456789.4' as decimal(38, 1)), 10) DEC38_4, " +
+        "trunc(cast('-999999999999999999999999999999999999.4' as decimal(38, 1)), 1) DEC38_5 " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC9_1", "DEC9_2", "DEC9_3", "DEC9_4", "DEC18_1", "DEC18_2", "DEC18_3", "DEC18_4", "DEC28_1",
+            "DEC28_2", "DEC28_3", "DEC28_4", "DEC28_5", "DEC38_1", "DEC38_2", "DEC38_3", "DEC38_4", "DEC38_5")
+        .baselineValues(new BigDecimal("1234.45"), new BigDecimal("1234.4500"), new BigDecimal("-1234"), new BigDecimal("0.11"),
+            new BigDecimal("99999912399.45"), new BigDecimal("99999912399.00"), new BigDecimal("-99999912399.450000"),
+            new BigDecimal("-99999912399.0000"), new BigDecimal("12345678912345678912.4"), new BigDecimal("999999999999999999.456000"),
+            new BigDecimal("12345678912345678912.00"), new BigDecimal("-12345678912345678912"), new BigDecimal("-12345678912345678912.0"),
+            new BigDecimal("999999999.1234567"), new BigDecimal("999999999.40000000"), new BigDecimal("999999999.123400000000"),
+            new BigDecimal("-123456789123456789.4000000000"), new BigDecimal("-999999999999999999999999999999999999.4"))
+        .go();
+  }
+
+  @Test
+  public void testRoundDecimalFunction() throws Exception {
+    String query = "SELECT " +
+        "round(cast('1234.5567' as decimal(9, 5))) as DEC9_1, " +
+        "round(cast('1234.1000' as decimal(9, 5))) as DEC9_2, " +
+        "round(cast('-1234.5567' as decimal(9, 5))) as DEC9_3, " +
+        "round(cast('-1234.1234' as decimal(9, 5))) as DEC9_4, " +
+        "round(cast('99999912399.9567' as decimal(18, 5))) DEC18_1, " +
+        "round(cast('99999912399.0000' as decimal(18, 5))) DEC18_2, " +
+        "round(cast('-99999912399.5567' as decimal(18, 5))) DEC18_3, " +
+        "round(cast('-99999912399.0000' as decimal(18, 5))) DEC18_4, " +
+        "round(cast('12345678912345678912.5567' as decimal(28, 5))) DEC28_1, " +
+        "round(cast('999999999999999999.5567' as decimal(28, 5))) DEC28_2, " +
+        "round(cast('12345678912345678912.0000' as decimal(28, 5))) DEC28_3, " +
+        "round(cast('-12345678912345678912.5567' as decimal(28, 5))) DEC28_4, " +
+        "round(cast('-12345678912345678912.0000' as decimal(28, 5))) DEC28_5, " +
+        "round(cast('999999999999999999999999999.5' as decimal(38, 1))) DEC38_1, " +
+        "round(cast('99999999.512345678123456789' as decimal(38, 18))) DEC38_2, " +
+        "round(cast('999999999999999999999999999999999999.5' as decimal(38, 1))) DEC38_3, " +
+        "round(cast('1234567891234567891234567891234567891.2' as decimal(38, 1))) DEC38_4, " +
+        "round(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_5, " +
+        "round(cast('-999999999999999999999999999999999999.9' as decimal(38, 1))) DEC38_6 " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC9_1", "DEC9_2", "DEC9_3", "DEC9_4", "DEC18_1", "DEC18_2", "DEC18_3", "DEC18_4", "DEC28_1",
+            "DEC28_2", "DEC28_3", "DEC28_4", "DEC28_5", "DEC38_1", "DEC38_2", "DEC38_3", "DEC38_4", "DEC38_5", "DEC38_6")
+        .baselineValues(new BigDecimal("1235"), new BigDecimal("1234"), new BigDecimal("-1235"), new BigDecimal("-1234"),
+            new BigDecimal("99999912400"), new BigDecimal("99999912399"), new BigDecimal("-99999912400"), new BigDecimal("-99999912399"),
+            new BigDecimal("12345678912345678913"), new BigDecimal("1000000000000000000"), new BigDecimal("12345678912345678912"),
+            new BigDecimal("-12345678912345678913"), new BigDecimal("-12345678912345678912"), new BigDecimal("1000000000000000000000000000"),
+            new BigDecimal("100000000"), new BigDecimal("1000000000000000000000000000000000000"), new BigDecimal("1234567891234567891234567891234567891"),
+            new BigDecimal("-1234567891234567891234567891234567891"), new BigDecimal("-1000000000000000000000000000000000000"))
+        .go();
+  }
+
+  @Test
+  public void testRoundWithScaleDecimalFunction() throws Exception {
+    String query = "SELECT " +
+        "round(cast('1234.5567' as decimal(9, 5)), 3) as DEC9_1, " +
+        "round(cast('1234.1000' as decimal(9, 5)), 2) as DEC9_2, " +
+        "round(cast('-1234.5567' as decimal(9, 5)), 4) as DEC9_3, " +
+        "round(cast('-1234.1234' as decimal(9, 5)), 3) as DEC9_4, " +
+        "round(cast('-1234.1234' as decimal(9, 2)), 4) as DEC9_5, " +
+        "round(cast('99999912399.9567' as decimal(18, 5)), 3) DEC18_1, " +
+        "round(cast('99999912399.0000' as decimal(18, 5)), 2) DEC18_2, " +
+        "round(cast('-99999912399.5567' as decimal(18, 5)), 2) DEC18_3, " +
+        "round(cast('-99999912399.0000' as decimal(18, 5)), 0) DEC18_4, " +
+        "round(cast('12345678912345678912.5567' as decimal(28, 5)), 2) DEC28_1, " +
+        "round(cast('999999999999999999.5567' as decimal(28, 5)), 1) DEC28_2, " +
+        "round(cast('12345678912345678912.0000' as decimal(28, 5)), 8) DEC28_3, " +
+        "round(cast('-12345678912345678912.5567' as decimal(28, 5)), 3) DEC28_4, " +
+        "round(cast('-12345678912345678912.0000' as decimal(28, 5)), 0) DEC28_5, " +
+        "round(cast('999999999999999999999999999.5' as decimal(38, 1)), 1) DEC38_1, " +
+        "round(cast('99999999.512345678923456789' as decimal(38, 18)), 9) DEC38_2, " +
+        "round(cast('999999999.9999999995678' as decimal(38, 18)), 9) DEC38_3, " +
+        "round(cast('999999999.9999999995678' as decimal(38, 18)), 11) DEC38_4, " +
+        "round(cast('999999999.9999999995678' as decimal(38, 18)), 21) DEC38_5, " +
+        "round(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1)), 1) DEC38_6, " +
+        "round(cast('-999999999999999999999999999999999999.9' as decimal(38, 1)), 0) DEC38_7 " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC9_1", "DEC9_2", "DEC9_3", "DEC9_4", "DEC9_5", "DEC18_1", "DEC18_2", "DEC18_3", "DEC18_4", "DEC28_1",
+            "DEC28_2", "DEC28_3", "DEC28_4", "DEC28_5", "DEC38_1", "DEC38_2", "DEC38_3", "DEC38_4", "DEC38_5", "DEC38_6", "DEC38_7")
+        .baselineValues(new BigDecimal("1234.557"), new BigDecimal("1234.10"), new BigDecimal("-1234.5567"), new BigDecimal("-1234.123"),
+            new BigDecimal("-1234.1200"), new BigDecimal("99999912399.957"), new BigDecimal("99999912399.00"), new BigDecimal("-99999912399.56"),
+            new BigDecimal("-99999912399"), new BigDecimal("12345678912345678912.56"), new BigDecimal("999999999999999999.6"),
+            new BigDecimal("12345678912345678912.00000000"), new BigDecimal("-12345678912345678912.557"), new BigDecimal("-12345678912345678912"),
+            new BigDecimal("999999999999999999999999999.5"), new BigDecimal("99999999.512345679"), new BigDecimal("1000000000.000000000"),
+            new BigDecimal("999999999.99999999957"), new BigDecimal("999999999.999999999567800000000"), new BigDecimal("-1234567891234567891234567891234567891.4"),
+            new BigDecimal("-1000000000000000000000000000000000000"))
+        .go();
+  }
+
+  @Ignore("we don't have decimal division")
+  @Test
+  public void testCastDecimalDivide() throws Exception {
+    String query = "select  (cast('9' as decimal(9, 1)) / cast('2' as decimal(4, 1))) as DEC9_DIV, " +
+        "cast('999999999' as decimal(9,0)) / cast('0.000000000000000000000000001' as decimal(28,28)) as DEC38_DIV, " +
+        "cast('123456789.123456789' as decimal(18, 9)) * cast('123456789.123456789' as decimal(18, 9)) as DEC18_MUL " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC9_DIV", "DEC38_DIV", "DEC18_MUL")
+        .baselineValues(new BigDecimal("4.500000000"), new BigDecimal("999999999000000000000000000000000000.0"),
+            new BigDecimal("15241578780673678.515622620750190521"))
+        .go();
+  }
+
+  @Test
+  public void testDecimalMultiplicationOverflowHandling() throws Exception {
+    String query = "select cast('1' as decimal(9, 5)) * cast ('999999999999999999999999999.999999999' as decimal(38, 9)) as DEC38_1, " +
+        "cast('1000000000000000001.000000000000000000' as decimal(38, 18)) * cast('0.999999999999999999' as decimal(38, 18)) as DEC38_2, " +
+        "cast('3' as decimal(9, 8)) * cast ('333333333.3333333333333333333' as decimal(38, 19)) as DEC38_3 " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC38_1", "DEC38_2", "DEC38_3")
+        .baselineValues(new BigDecimal("1000000000000000000000000000.00000"), new BigDecimal("1000000000000000000"), new BigDecimal("1000000000.000000000000000000"))
+        .go();
+  }
+
+  @Test
+  public void testDecimalRoundUp() throws Exception {
+    String query = "select cast('999999999999999999.9999999999999999995' as decimal(38, 18)) as DEC38_1, " +
+        "cast('999999999999999999.9999999999999999994' as decimal(38, 18)) as DEC38_2, " +
+        "cast('999999999999999999.1234567895' as decimal(38, 9)) as DEC38_3, " +
+        "cast('99999.12345' as decimal(18, 4)) as DEC18_1, " +
+        "cast('99999.99995' as decimal(18, 4)) as DEC18_2 " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC38_1", "DEC38_2", "DEC38_3", "DEC18_1", "DEC18_2")
+        .baselineValues(new BigDecimal("1000000000000000000.000000000000000000"), new BigDecimal("999999999999999999.999999999999999999"),
+            new BigDecimal("999999999999999999.123456790"), new BigDecimal("99999.1235"), new BigDecimal("100000.0000"))
+        .go();
+  }
+
+  @Test
+  public void testDecimalDownwardCast() throws Exception {
+    String query = "select cast((cast('12345.6789' as decimal(18, 4))) as decimal(9, 4)) as DEC18_DEC9_1, " +
+        "cast((cast('12345.6789' as decimal(18, 4))) as decimal(9, 2)) as DEC18_DEC9_2, " +
+        "cast((cast('-12345.6789' as decimal(18, 4))) as decimal(9, 0)) as DEC18_DEC9_3, " +
+        "cast((cast('999999999.6789' as decimal(38, 4))) as decimal(9, 0)) as DEC38_DEC19_1, " +
+        "cast((cast('-999999999999999.6789' as decimal(38, 4))) as decimal(18, 2)) as DEC38_DEC18_1, " +
+        "cast((cast('-999999999999999.6789' as decimal(38, 4))) as decimal(18, 0)) as DEC38_DEC18_2, " +
+        "cast((cast('100000000999999999.6789' as decimal(38, 4))) as decimal(28, 0)) as DEC38_DEC28_1 " +
+        "from cp.`employee.json` where employee_id = 1";
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC18_DEC9_1", "DEC18_DEC9_2", "DEC18_DEC9_3", "DEC38_DEC19_1", "DEC38_DEC18_1", "DEC38_DEC18_2", "DEC38_DEC28_1")
+        .baselineValues(new BigDecimal("12345.6789"), new BigDecimal("12345.68"), new BigDecimal("-12346"), new BigDecimal("1000000000"),
+            new BigDecimal("-999999999999999.68"), new BigDecimal("-1000000000000000"), new BigDecimal("100000001000000000"))
+        .go();
+  }
+
+  @Test
+  public void testTruncateWithParamFunction() throws Exception {
+    String query = "SELECT " +
+        "trunc(1234.4567, 2) as T_1, " +
+        "trunc(-1234.4567, 2) as T_2, " +
+        "trunc(1234.4567, -2) as T_3, " +
+        "trunc(-1234.4567, -2) as T_4, " +
+        "trunc(1234, 4) as T_5, " +
+        "trunc(-1234, 4) as T_6, " +
+        "trunc(1234, -4) as T_7, " +
+        "trunc(-1234, -4) as T_8, " +
+        "trunc(8124674407369523212, 0) as T_9, " +
+        "trunc(81246744073695.395, 1) as T_10 " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("T_1", "T_2", "T_3", "T_4", "T_5", "T_6", "T_7", "T_8", "T_9", "T_10")
+        .baselineValues(new Double("1234.45"), new Double("-1234.45"), new Double("1200.0"), new Double("-1200.0"), new Double("1234.0"),
+            new Double("-1234.0"), new Double("0.0"), new Double("0.0"), new Double("8.1246744073695232E18"), new Double("8.12467440736953E13"))
+        .go();
+  }
+
+  @Test
+  public void testRoundWithParamFunction() throws Exception {
+    String query = "SELECT " +
+        "round(1234.4567, 2) as T_1, " +
+        "round(-1234.4567, 2) as T_2, " +
+        "round(1234.4567, -2) as T_3, " +
+        "round(-1234.4567, -2) as T_4, " +
+        "round(1234, 4) as T_5, " +
+        "round(-1234, 4) as T_6, " +
+        "round(1234, -4) as T_7, " +
+        "round(-1234, -4) as T_8, " +
+        "round(8124674407369523212, -4) as T_9, " +
+        "round(81246744073695.395, 1) as T_10 " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("T_1", "T_2", "T_3", "T_4", "T_5", "T_6", "T_7", "T_8", "T_9", "T_10")
+        .baselineValues(new Double("1234.46"), new Double("-1234.46"), new Double("1200.0"), new Double("-1200.0"), new Double("1234.0"),
+            new Double("-1234.0"), new Double("0.0"), new Double("0.0"), new Double("8.1246744073695201E18"), new Double("8.12467440736954E13"))
+        .go();
+
+  }
+
+  @Test
+  public void testToCharFunction() throws Exception {
+    String query = "SELECT " +
+        "to_char(1234.5567, '#,###.##') as FLOAT8_1, " +
+        "to_char(1234.5, '$#,###.00') as FLOAT8_2, " +
+        "to_char(cast('1234.5567' as decimal(9, 5)), '#,###.##') as DEC9_1, " +
+        "to_char(cast('99999912399.9567' as decimal(18, 5)), '#.#####') DEC18_1, " +
+        "to_char(cast('12345678912345678912.5567' as decimal(28, 5)), '#,###.#####') DEC28_1, " +
+        "to_char(cast('999999999999999999999999999.5' as decimal(38, 1)), '#.#') DEC38_1 " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("FLOAT8_1", "FLOAT8_2", "DEC9_1", "DEC18_1", "DEC28_1", "DEC38_1")
+        .baselineValues("1,234.56", "$1,234.50", "1,234.56", "99999912399.9567", "12,345,678,912,345,678,912.5567", "999999999999999999999999999.5")
+        .go();
+  }
+
+  @Test
+  public void testConcatFunction() throws Exception {
+    String query = "SELECT " +
+        "concat('1234', ' COL_VALUE ', R_REGIONKEY, ' - STRING') as STR_1 " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("STR_1")
+        .baselineValues("1234 COL_VALUE 0 - STRING")
+        .go();
+  }
+
+  @Test
+  public void testTimeStampConstant() throws Exception {
+    String query = "SELECT " +
+        "timestamp '2008-2-23 12:23:23' as TS " +
+        "FROM cp.`tpch/region.parquet` limit 1";
+
+    DateTime date = DateUtility.formatTimeStamp.parseDateTime("2008-02-23 12:23:23.0");
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("TS")
+        .baselineValues(date)
+        .go();
+  }
+
+  @Test
+  public void testNullConstantsTimeTimeStampAndDate() throws Exception {
+    String query = "SELECT " +
+        "CAST(NULL AS TIME) AS t, " +
+        "CAST(NULL AS TIMESTAMP) AS ts, " +
+        "CAST(NULL AS DATE) AS d " +
+        "FROM cp.`region.json` LIMIT 1";
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("t", "ts", "d")
+        .baselineValues(null, null, null)
+        .go();
+  }
+
+  @Test
+  public void testIntMinToDecimal() throws Exception {
+    String query = "select cast((employee_id - employee_id + -2147483648) as decimal(28, 2)) as DEC_28," +
+        "cast((employee_id - employee_id + -2147483648) as decimal(18, 2)) as DEC_18 from " +
+        "cp.`employee.json` limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC_28", "DEC_18")
+        .baselineValues(new BigDecimal("-2147483648.00"), new BigDecimal("-2147483648.00"))
+        .go();
+  }
+
+  @Test
+  public void testHashFunctions() throws Exception {
+    String query = "select " +
+        "hash(cast(hire_date as date)) hash_date, " +
+        "hash(cast(employee_id as decimal(9, 2))) as hash_dec9, " +
+        "hash(cast(employee_id as decimal(38, 11))) as hash_dec38 " +
+        "from cp.`employee.json` where employee_id = 1 limit 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("hash_date", "hash_dec9", "hash_dec38")
+        .baselineValues(818600896, 262164233, 1799649913)
+        .go();
+  }
+
+  @Test
+  public void testDecimalAddConstant() throws Exception {
+    String query = "select (cast('-1' as decimal(37, 3)) + cast (employee_id as decimal(37, 3))) as CNT " +
+        "from cp.`employee.json` where employee_id <= 4";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("CNT")
+        .baselineValues(new BigDecimal("0.000"))
+        .baselineValues(new BigDecimal("1.000"))
+        .baselineValues(new BigDecimal("3.000"))
+        .go();
+  }
+
+  @Test
+  public void testDecimalAddIntConstant() throws Exception {
+    String query = "select 1 + cast(employee_id as decimal(9, 3)) as DEC_9 , 1 + cast(employee_id as decimal(37, 5)) as DEC_38 " +
+        "from cp.`employee.json` where employee_id <= 2";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC_9", "DEC_38")
+        .baselineValues(new BigDecimal("2.000"), new BigDecimal("2.00000"))
+        .baselineValues(new BigDecimal("3.000"), new BigDecimal("3.00000"))
+        .go();
+  }
+
+  @Test
+  public void testSignFunction() throws Exception {
+    String query = "select sign(cast('1.23' as float)) as SIGN_FLOAT, sign(-1234.4567) as SIGN_DOUBLE, sign(23) as SIGN_INT " +
+        "from cp.`employee.json` where employee_id < 2";
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("SIGN_FLOAT", "SIGN_DOUBLE", "SIGN_INT")
+        .baselineValues(1, -1, 1)
+        .go();
+  }
+
+
+  @Test
+  public void testPadFunctions() throws Exception {
+    String query = "select rpad(first_name, 10) as RPAD_DEF, rpad(first_name, 10, '*') as RPAD_STAR, lpad(first_name, 10) as LPAD_DEF, lpad(first_name, 10, '*') as LPAD_STAR, " +
+        "lpad(first_name, 2) as LPAD_TRUNC, rpad(first_name, 2) as RPAD_TRUNC " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("RPAD_DEF", "RPAD_STAR", "LPAD_DEF", "LPAD_STAR", "LPAD_TRUNC", "RPAD_TRUNC")
+        .baselineValues("Sheri     ", "Sheri*****", "     Sheri", "*****Sheri", "Sh", "Sh")
+        .go();
+
+  }
+
+  @Test
+  public void testExtractSecond() throws Exception {
+    String query = "select extract(second from date '2008-2-23') as DATE_EXT, extract(second from timestamp '2008-2-23 10:00:20.123') as TS_EXT, " +
+        "extract(second from time '10:20:30.303') as TM_EXT " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DATE_EXT", "TS_EXT", "TM_EXT")
+        .baselineValues(0.0d, 20.123d, 30.303d)
+        .go();
+  }
+
+  @Test
+  public void testCastDecimalDouble() throws Exception {
+    String query = "select cast((cast('1.0001' as decimal(18, 9))) as double) DECIMAL_DOUBLE_CAST " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DECIMAL_DOUBLE_CAST")
+        .baselineValues(1.0001d)
+        .go();
+  }
+
+  @Test
+  public void testExtractSecondFromInterval() throws Exception {
+    String query = "select extract (second from interval '1 2:30:45.100' day to second) as EXT_INTDAY " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("EXT_INTDAY")
+        .baselineValues(45.1d)
+        .go();
+  }
+
+  @Test
+  public void testFunctionCaseInsensitiveNames() throws Exception {
+    String query = "SELECT to_date('2003/07/09', 'yyyy/MM/dd') as col1, " +
+        "TO_DATE('2003/07/09', 'yyyy/MM/dd') as col2, " +
+        "To_DaTe('2003/07/09', 'yyyy/MM/dd') as col3 " +
+        "from cp.`employee.json` LIMIT 1";
+
+    DateTime date = DateUtility.formatDate.parseDateTime("2003-07-09");
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("col1", "col2", "col3")
+        .baselineValues(date, date, date)
+        .go();
+  }
+
+  @Test
+  public void testDecimal18Decimal38Comparison() throws Exception {
+    String query = "select cast('-999999999.999999999' as decimal(18, 9)) = cast('-999999999.999999999' as decimal(38, 18)) as CMP " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("CMP")
+        .baselineValues(true)
+        .go();
+  }
+
+  @Test
+  public void testOptiqDecimalCapping() throws Exception {
+    String query = "select  cast('12345.678900000' as decimal(18, 9))=cast('12345.678900000' as decimal(38, 9)) as CMP " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("CMP")
+        .baselineValues(true)
+        .go();
+  }
+
+  @Test
+  public void testNegative() throws Exception {
+    String query = "select  negative(2) as NEG " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("NEG")
+        .baselineValues(-2l)
+        .go();
+  }
+
+  @Test
+  public void testOptiqValidationFunctions() throws Exception {
+    String query = "select trim(first_name) as TRIM_STR, substring(first_name, 2) as SUB_STR " +
+        "from cp.`employee.json` where employee_id = 1";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("TRIM_STR", "SUB_STR")
+        .baselineValues("Sheri", "heri")
+        .go();
+  }
+
+  @Test
+  public void testToTimeStamp() throws Exception {
+    String query = "select to_timestamp(cast('800120400.12312' as decimal(38, 5))) as DEC38_TS, to_timestamp(200120400) as INT_TS " +
+        "from cp.`employee.json` where employee_id < 2";
+
+    DateTime result1 = new DateTime(800120400123l);
+    DateTime result2 = new DateTime(200120400000l);
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("DEC38_TS", "INT_TS")
+        .baselineValues(result1, result2)
+        .go();
+  }
+
+  @Test
+  public void testDateTrunc() throws Exception {
+    String query = "select "
+        + "date_trunc('MINUTE', time '2:30:21.5') as TIME1, "
+        + "date_trunc('SECOND', time '2:30:21.5') as TIME2, "
+        + "date_trunc('HOUR', timestamp '1991-05-05 10:11:12.100') as TS1, "
+        + "date_trunc('SECOND', timestamp '1991-05-05 10:11:12.100') as TS2, "
+        + "date_trunc('MONTH', date '2011-2-2') as DATE1, "
+        + "date_trunc('YEAR', date '2011-2-2') as DATE2 "
+        + "from cp.`employee.json` where employee_id < 2";
+
+    DateTime time1 = DateUtility.formatTime.parseDateTime("2:30:00.0");
+    DateTime time2 = DateUtility.formatTime.parseDateTime("2:30:21.0");
+    DateTime ts1 = DateUtility.formatTimeStamp.parseDateTime("1991-05-05 10:00:00.0");
+    DateTime ts2 = DateUtility.formatTimeStamp.parseDateTime("1991-05-05 10:11:12.0");
+    DateTime date1 = DateUtility.formatDate.parseDateTime("2011-02-01");
+    DateTime date2 = DateUtility.formatDate.parseDateTime("2011-01-01");
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("TIME1", "TIME2", "TS1", "TS2", "DATE1", "DATE2")
+        .baselineValues(time1, time2, ts1, ts2, date1, date2)
+        .go();
+  }
+}

http://git-wip-us.apache.org/repos/asf/drill/blob/21432dec/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestFunctionsQuery.java
----------------------------------------------------------------------
diff --git a/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestFunctionsQuery.java b/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestFunctionsQuery.java
deleted file mode 100644
index 881834b..0000000
--- a/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestFunctionsQuery.java
+++ /dev/null
@@ -1,758 +0,0 @@
-/**
- * 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.jdbc.test;
-
-import java.nio.file.Paths;
-
-import org.apache.drill.jdbc.Driver;
-import org.joda.time.DateTime;
-import org.joda.time.format.DateTimeFormat;
-import org.joda.time.format.DateTimeFormatter;
-import org.junit.Ignore;
-import org.junit.Test;
-
-public class TestFunctionsQuery {
-
-  public static final String WORKING_PATH;
-  static{
-    Driver.load();
-    WORKING_PATH = Paths.get("").toAbsolutePath().toString();
-
-  }
-  @Test
-  public void testAbsDecimalFunction() throws Exception{
-    String query = String.format("SELECT abs(cast('1234.4567' as decimal(9, 5))) as DEC9_ABS_1, " +
-        "abs(cast('-1234.4567' as decimal(9, 5))) DEC9_ABS_2, " +
-        "abs(cast('99999912399.4567' as decimal(18, 5))) DEC18_ABS_1, " +
-        "abs(cast('-99999912399.4567' as decimal(18, 5))) DEC18_ABS_2, " +
-        "abs(cast('12345678912345678912.4567' as decimal(28, 5))) DEC28_ABS_1, " +
-        "abs(cast('-12345678912345678912.4567' as decimal(28, 5))) DEC28_ABS_2, " +
-        "abs(cast('1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_ABS_1, " +
-        "abs(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_ABS_2 " +
-        "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC9_ABS_1=1234.45670; " +
-                "DEC9_ABS_2=1234.45670; " +
-                "DEC18_ABS_1=99999912399.45670; " +
-                "DEC18_ABS_2=99999912399.45670; " +
-                "DEC28_ABS_1=12345678912345678912.45670; " +
-                "DEC28_ABS_2=12345678912345678912.45670; " +
-                "DEC38_ABS_1=1234567891234567891234567891234567891.4; " +
-                "DEC38_ABS_2=1234567891234567891234567891234567891.4\n"
-        );
-
-  }
-
-  @Test
-  public void testCeilDecimalFunction() throws Exception {
-    String query = String.format("SELECT " +
-        "ceil(cast('1234.4567' as decimal(9, 5))) as DEC9_1, " +
-        "ceil(cast('1234.0000' as decimal(9, 5))) as DEC9_2, " +
-        "ceil(cast('-1234.4567' as decimal(9, 5))) as DEC9_3, " +
-        "ceil(cast('-1234.000' as decimal(9, 5))) as DEC9_4, " +
-        "ceil(cast('99999912399.4567' as decimal(18, 5))) DEC18_1, " +
-        "ceil(cast('99999912399.0000' as decimal(18, 5))) DEC18_2, " +
-        "ceil(cast('-99999912399.4567' as decimal(18, 5))) DEC18_3, " +
-        "ceil(cast('-99999912399.0000' as decimal(18, 5))) DEC18_4, " +
-        "ceil(cast('12345678912345678912.4567' as decimal(28, 5))) DEC28_1, " +
-        "ceil(cast('999999999999999999.4567' as decimal(28, 5))) DEC28_2, " +
-        "ceil(cast('12345678912345678912.0000' as decimal(28, 5))) DEC28_3, " +
-        "ceil(cast('-12345678912345678912.4567' as decimal(28, 5))) DEC28_4, " +
-        "ceil(cast('-12345678912345678912.0000' as decimal(28, 5))) DEC28_5, " +
-        "ceil(cast('1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_1, " +
-        "ceil(cast('999999999999999999999999999999999999.4' as decimal(38, 1))) DEC38_2, " +
-        "ceil(cast('1234567891234567891234567891234567891.0' as decimal(38, 1))) DEC38_3, " +
-        "ceil(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_4, " +
-        "ceil(cast('-1234567891234567891234567891234567891.0' as decimal(38, 1))) DEC38_5 " +
-        "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC9_1=1235; " +
-                "DEC9_2=1234; " +
-                "DEC9_3=-1234; " +
-                "DEC9_4=-1234; " +
-                "DEC18_1=99999912400; " +
-                "DEC18_2=99999912399; " +
-                "DEC18_3=-99999912399; " +
-                "DEC18_4=-99999912399; " +
-                "DEC28_1=12345678912345678913; " +
-                "DEC28_2=1000000000000000000; " +
-                "DEC28_3=12345678912345678912; " +
-                "DEC28_4=-12345678912345678912; " +
-                "DEC28_5=-12345678912345678912; " +
-                "DEC38_1=1234567891234567891234567891234567892; " +
-                "DEC38_2=1000000000000000000000000000000000000; " +
-                "DEC38_3=1234567891234567891234567891234567891; " +
-                "DEC38_4=-1234567891234567891234567891234567891; " +
-                "DEC38_5=-1234567891234567891234567891234567891\n"
-        );
-
-  }
-
-  @Test
-  public void testFloorDecimalFunction() throws Exception {
-    String query = String.format("SELECT " +
-        "floor(cast('1234.4567' as decimal(9, 5))) as DEC9_1, " +
-        "floor(cast('1234.0000' as decimal(9, 5))) as DEC9_2, " +
-        "floor(cast('-1234.4567' as decimal(9, 5))) as DEC9_3, " +
-        "floor(cast('-1234.000' as decimal(9, 5))) as DEC9_4, " +
-        "floor(cast('99999912399.4567' as decimal(18, 5))) DEC18_1, " +
-        "floor(cast('99999912399.0000' as decimal(18, 5))) DEC18_2, " +
-        "floor(cast('-99999912399.4567' as decimal(18, 5))) DEC18_3, " +
-        "floor(cast('-99999912399.0000' as decimal(18, 5))) DEC18_4, " +
-        "floor(cast('12345678912345678912.4567' as decimal(28, 5))) DEC28_1, " +
-        "floor(cast('999999999999999999.4567' as decimal(28, 5))) DEC28_2, " +
-        "floor(cast('12345678912345678912.0000' as decimal(28, 5))) DEC28_3, " +
-        "floor(cast('-12345678912345678912.4567' as decimal(28, 5))) DEC28_4, " +
-        "floor(cast('-12345678912345678912.0000' as decimal(28, 5))) DEC28_5, " +
-        "floor(cast('1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_1, " +
-        "floor(cast('999999999999999999999999999999999999.4' as decimal(38, 1))) DEC38_2, " +
-        "floor(cast('1234567891234567891234567891234567891.0' as decimal(38, 1))) DEC38_3, " +
-        "floor(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_4, " +
-        "floor(cast('-999999999999999999999999999999999999.4' as decimal(38, 1))) DEC38_5 " +
-        "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC9_1=1234; " +
-                "DEC9_2=1234; " +
-                "DEC9_3=-1235; " +
-                "DEC9_4=-1234; " +
-                "DEC18_1=99999912399; " +
-                "DEC18_2=99999912399; " +
-                "DEC18_3=-99999912400; " +
-                "DEC18_4=-99999912399; " +
-                "DEC28_1=12345678912345678912; " +
-                "DEC28_2=999999999999999999; " +
-                "DEC28_3=12345678912345678912; " +
-                "DEC28_4=-12345678912345678913; " +
-                "DEC28_5=-12345678912345678912; " +
-                "DEC38_1=1234567891234567891234567891234567891; " +
-                "DEC38_2=999999999999999999999999999999999999; " +
-                "DEC38_3=1234567891234567891234567891234567891; " +
-                "DEC38_4=-1234567891234567891234567891234567892; " +
-                "DEC38_5=-1000000000000000000000000000000000000\n"
-        );
-  }
-
-  @Test
-  public void testTruncateDecimalFunction() throws Exception {
-    String query = String.format("SELECT " +
-        "trunc(cast('1234.4567' as decimal(9, 5))) as DEC9_1, " +
-        "trunc(cast('1234.0000' as decimal(9, 5))) as DEC9_2, " +
-        "trunc(cast('-1234.4567' as decimal(9, 5))) as DEC9_3, " +
-        "trunc(cast('0.111' as decimal(9, 5))) as DEC9_4, " +
-        "trunc(cast('99999912399.4567' as decimal(18, 5))) DEC18_1, " +
-        "trunc(cast('99999912399.0000' as decimal(18, 5))) DEC18_2, " +
-        "trunc(cast('-99999912399.4567' as decimal(18, 5))) DEC18_3, " +
-        "trunc(cast('-99999912399.0000' as decimal(18, 5))) DEC18_4, " +
-        "trunc(cast('12345678912345678912.4567' as decimal(28, 5))) DEC28_1, " +
-        "trunc(cast('999999999999999999.4567' as decimal(28, 5))) DEC28_2, " +
-        "trunc(cast('12345678912345678912.0000' as decimal(28, 5))) DEC28_3, " +
-        "trunc(cast('-12345678912345678912.4567' as decimal(28, 5))) DEC28_4, " +
-        "trunc(cast('-12345678912345678912.0000' as decimal(28, 5))) DEC28_5, " +
-        "trunc(cast('1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_1, " +
-        "trunc(cast('999999999999999999999999999999999999.4' as decimal(38, 1))) DEC38_2, " +
-        "trunc(cast('1234567891234567891234567891234567891.0' as decimal(38, 1))) DEC38_3, " +
-        "trunc(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_4, " +
-        "trunc(cast('-999999999999999999999999999999999999.4' as decimal(38, 1))) DEC38_5 " +
-        "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC9_1=1234; " +
-                "DEC9_2=1234; " +
-                "DEC9_3=-1234; " +
-                "DEC9_4=0; " +
-                "DEC18_1=99999912399; " +
-                "DEC18_2=99999912399; " +
-                "DEC18_3=-99999912399; " +
-                "DEC18_4=-99999912399; " +
-                "DEC28_1=12345678912345678912; " +
-                "DEC28_2=999999999999999999; " +
-                "DEC28_3=12345678912345678912; " +
-                "DEC28_4=-12345678912345678912; " +
-                "DEC28_5=-12345678912345678912; " +
-                "DEC38_1=1234567891234567891234567891234567891; " +
-                "DEC38_2=999999999999999999999999999999999999; " +
-                "DEC38_3=1234567891234567891234567891234567891; " +
-                "DEC38_4=-1234567891234567891234567891234567891; " +
-                "DEC38_5=-999999999999999999999999999999999999\n"
-        );
-  }
-
-  @Test
-  public void testTruncateWithParamDecimalFunction() throws Exception {
-    String query = String.format("SELECT " +
-        "trunc(cast('1234.4567' as decimal(9, 5)), 2) as DEC9_1, " +
-        "trunc(cast('1234.45' as decimal(9, 2)), 4) as DEC9_2, " +
-        "trunc(cast('-1234.4567' as decimal(9, 5)), 0) as DEC9_3, " +
-        "trunc(cast('0.111' as decimal(9, 5)), 2) as DEC9_4, " +
-        "trunc(cast('99999912399.4567' as decimal(18, 5)), 2) DEC18_1, " +
-        "trunc(cast('99999912399.0000' as decimal(18, 5)), 2) DEC18_2, " +
-        "trunc(cast('-99999912399.45' as decimal(18, 2)), 6) DEC18_3, " +
-        "trunc(cast('-99999912399.0000' as decimal(18, 5)), 4) DEC18_4, " +
-        "trunc(cast('12345678912345678912.4567' as decimal(28, 5)), 1) DEC28_1, " +
-        "trunc(cast('999999999999999999.456' as decimal(28, 3)), 6) DEC28_2, " +
-        "trunc(cast('12345678912345678912.0000' as decimal(28, 5)), 2) DEC28_3, " +
-        "trunc(cast('-12345678912345678912.45' as decimal(28, 2)), 0) DEC28_4, " +
-        "trunc(cast('-12345678912345678912.0000' as decimal(28, 5)), 1) DEC28_5, " +
-        "trunc(cast('999999999.123456789' as decimal(38, 9)), 7) DEC38_1, " +
-        "trunc(cast('999999999.4' as decimal(38, 1)), 8) DEC38_2, " +
-        "trunc(cast('999999999.1234' as decimal(38, 4)), 12) DEC38_3, " +
-        "trunc(cast('-123456789123456789.4' as decimal(38, 1)), 10) DEC38_4, " +
-        "trunc(cast('-999999999999999999999999999999999999.4' as decimal(38, 1)), 1) DEC38_5 " +
-        "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC9_1=1234.45; " +
-                "DEC9_2=1234.4500; " +
-                "DEC9_3=-1234; " +
-                "DEC9_4=0.11; " +
-                "DEC18_1=99999912399.45; " +
-                "DEC18_2=99999912399.00; " +
-                "DEC18_3=-99999912399.450000; " +
-                "DEC18_4=-99999912399.0000; " +
-                "DEC28_1=12345678912345678912.4; " +
-                "DEC28_2=999999999999999999.456000; " +
-                "DEC28_3=12345678912345678912.00; " +
-                "DEC28_4=-12345678912345678912; " +
-                "DEC28_5=-12345678912345678912.0; " +
-                "DEC38_1=999999999.1234567; " +
-                "DEC38_2=999999999.40000000; " +
-                "DEC38_3=999999999.123400000000; " +
-                "DEC38_4=-123456789123456789.4000000000; " +
-                "DEC38_5=-999999999999999999999999999999999999.4\n"
-        );
-  }
-
-  @Test
-  public void testTruncateWithParamFunction() throws Exception {
-    String query = String.format("SELECT " +
-      "trunc(1234.4567, 2) as T_1, " +
-      "trunc(-1234.4567, 2) as T_2, " +
-      "trunc(1234.4567, -2) as T_3, " +
-      "trunc(-1234.4567, -2) as T_4, " +
-      "trunc(1234, 4) as T_5, " +
-      "trunc(-1234, 4) as T_6, " +
-      "trunc(1234, -4) as T_7, " +
-      "trunc(-1234, -4) as T_8, " +
-      "trunc(8124674407369523212, 0) as T_9, " +
-      "trunc(81246744073695.395, 1) as T_10 " +
-      "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-      .sql(query)
-      .returns(
-          "T_1=1234.45; " +
-              "T_2=-1234.45; " +
-              "T_3=1200.0; " +
-              "T_4=-1200.0; " +
-              "T_5=1234.0; " +
-              "T_6=-1234.0; " +
-              "T_7=0.0; " +
-              "T_8=0.0; " +
-              "T_9=8.1246744073695232E18; " +
-              "T_10=8.12467440736953E13\n"
-      );
-  }
-
-  @Test
-  public void testRoundDecimalFunction() throws Exception {
-    String query = String.format("SELECT " +
-        "round(cast('1234.5567' as decimal(9, 5))) as DEC9_1, " +
-        "round(cast('1234.1000' as decimal(9, 5))) as DEC9_2, " +
-        "round(cast('-1234.5567' as decimal(9, 5))) as DEC9_3, " +
-        "round(cast('-1234.1234' as decimal(9, 5))) as DEC9_4, " +
-        "round(cast('99999912399.9567' as decimal(18, 5))) DEC18_1, " +
-        "round(cast('99999912399.0000' as decimal(18, 5))) DEC18_2, " +
-        "round(cast('-99999912399.5567' as decimal(18, 5))) DEC18_3, " +
-        "round(cast('-99999912399.0000' as decimal(18, 5))) DEC18_4, " +
-        "round(cast('12345678912345678912.5567' as decimal(28, 5))) DEC28_1, " +
-        "round(cast('999999999999999999.5567' as decimal(28, 5))) DEC28_2, " +
-        "round(cast('12345678912345678912.0000' as decimal(28, 5))) DEC28_3, " +
-        "round(cast('-12345678912345678912.5567' as decimal(28, 5))) DEC28_4, " +
-        "round(cast('-12345678912345678912.0000' as decimal(28, 5))) DEC28_5, " +
-        "round(cast('999999999999999999999999999.5' as decimal(38, 1))) DEC38_1, " +
-        "round(cast('99999999.512345678123456789' as decimal(38, 18))) DEC38_2, " +
-        "round(cast('999999999999999999999999999999999999.5' as decimal(38, 1))) DEC38_3, " +
-        "round(cast('1234567891234567891234567891234567891.2' as decimal(38, 1))) DEC38_4, " +
-        "round(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1))) DEC38_5, " +
-        "round(cast('-999999999999999999999999999999999999.9' as decimal(38, 1))) DEC38_6 " +
-        "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC9_1=1235; " +
-                "DEC9_2=1234; " +
-                "DEC9_3=-1235; " +
-                "DEC9_4=-1234; " +
-                "DEC18_1=99999912400; " +
-                "DEC18_2=99999912399; " +
-                "DEC18_3=-99999912400; " +
-                "DEC18_4=-99999912399; " +
-                "DEC28_1=12345678912345678913; " +
-                "DEC28_2=1000000000000000000; " +
-                "DEC28_3=12345678912345678912; " +
-                "DEC28_4=-12345678912345678913; " +
-                "DEC28_5=-12345678912345678912; " +
-                "DEC38_1=1000000000000000000000000000; " +
-                "DEC38_2=100000000; " +
-                "DEC38_3=1000000000000000000000000000000000000; " +
-                "DEC38_4=1234567891234567891234567891234567891; " +
-                "DEC38_5=-1234567891234567891234567891234567891; " +
-                "DEC38_6=-1000000000000000000000000000000000000\n"
-        );
-  }
-
-  @Test
-  public void testRoundWithScaleDecimalFunction() throws Exception {
-    String query = String.format("SELECT " +
-        "round(cast('1234.5567' as decimal(9, 5)), 3) as DEC9_1, " +
-        "round(cast('1234.1000' as decimal(9, 5)), 2) as DEC9_2, " +
-        "round(cast('-1234.5567' as decimal(9, 5)), 4) as DEC9_3, " +
-        "round(cast('-1234.1234' as decimal(9, 5)), 3) as DEC9_4, " +
-        "round(cast('-1234.1234' as decimal(9, 2)), 4) as DEC9_5, " +
-        "round(cast('99999912399.9567' as decimal(18, 5)), 3) DEC18_1, " +
-        "round(cast('99999912399.0000' as decimal(18, 5)), 2) DEC18_2, " +
-        "round(cast('-99999912399.5567' as decimal(18, 5)), 2) DEC18_3, " +
-        "round(cast('-99999912399.0000' as decimal(18, 5)), 0) DEC18_4, " +
-        "round(cast('12345678912345678912.5567' as decimal(28, 5)), 2) DEC28_1, " +
-        "round(cast('999999999999999999.5567' as decimal(28, 5)), 1) DEC28_2, " +
-        "round(cast('12345678912345678912.0000' as decimal(28, 5)), 8) DEC28_3, " +
-        "round(cast('-12345678912345678912.5567' as decimal(28, 5)), 3) DEC28_4, " +
-        "round(cast('-12345678912345678912.0000' as decimal(28, 5)), 0) DEC28_5, " +
-        "round(cast('999999999999999999999999999.5' as decimal(38, 1)), 1) DEC38_1, " +
-        "round(cast('99999999.512345678923456789' as decimal(38, 18)), 9) DEC38_2, " +
-        "round(cast('999999999.9999999995678' as decimal(38, 18)), 9) DEC38_3, " +
-        "round(cast('999999999.9999999995678' as decimal(38, 18)), 11) DEC38_4, " +
-        "round(cast('999999999.9999999995678' as decimal(38, 18)), 21) DEC38_5, " +
-        "round(cast('-1234567891234567891234567891234567891.4' as decimal(38, 1)), 1) DEC38_6, " +
-        "round(cast('-999999999999999999999999999999999999.9' as decimal(38, 1)), 0) DEC38_7 " +
-        "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC9_1=1234.557; " +
-                "DEC9_2=1234.10; " +
-                "DEC9_3=-1234.5567; " +
-                "DEC9_4=-1234.123; " +
-                "DEC9_5=-1234.1200; " +
-                "DEC18_1=99999912399.957; " +
-                "DEC18_2=99999912399.00; " +
-                "DEC18_3=-99999912399.56; " +
-                "DEC18_4=-99999912399; " +
-                "DEC28_1=12345678912345678912.56; " +
-                "DEC28_2=999999999999999999.6; " +
-                "DEC28_3=12345678912345678912.00000000; " +
-                "DEC28_4=-12345678912345678912.557; " +
-                "DEC28_5=-12345678912345678912; " +
-                "DEC38_1=999999999999999999999999999.5; " +
-                "DEC38_2=99999999.512345679; " +
-                "DEC38_3=1000000000.000000000; " +
-                "DEC38_4=999999999.99999999957; " +
-                "DEC38_5=999999999.999999999567800000000; " +
-                "DEC38_6=-1234567891234567891234567891234567891.4; " +
-                "DEC38_7=-1000000000000000000000000000000000000\n"
-        );
-  }
-
-  @Test
-  public void testRoundWithParamFunction() throws Exception {
-    String query = String.format("SELECT " +
-      "round(1234.4567, 2) as T_1, " +
-      "round(-1234.4567, 2) as T_2, " +
-      "round(1234.4567, -2) as T_3, " +
-      "round(-1234.4567, -2) as T_4, " +
-      "round(1234, 4) as T_5, " +
-      "round(-1234, 4) as T_6, " +
-      "round(1234, -4) as T_7, " +
-      "round(-1234, -4) as T_8, " +
-      "round(8124674407369523212, -4) as T_9, " +
-      "round(81246744073695.395, 1) as T_10 " +
-      "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-      .sql(query)
-      .returns(
-          "T_1=1234.46; " +
-              "T_2=-1234.46; " +
-              "T_3=1200.0; " +
-              "T_4=-1200.0; " +
-              "T_5=1234.0; " +
-              "T_6=-1234.0; " +
-              "T_7=0.0; " +
-              "T_8=0.0; " +
-              "T_9=8.1246744073695201E18; " +
-              "T_10=8.12467440736954E13\n"
-      );
-  }
-
-  @Test
-  public void testToCharFunction() throws Exception {
-    String query = String.format("SELECT " +
-        "to_char(1234.5567, '#,###.##') as FLOAT8_1, " +
-        "to_char(1234.5, '$#,###.00') as FLOAT8_2, " +
-        "to_char(cast('1234.5567' as decimal(9, 5)), '#,###.##') as DEC9_1, " +
-        "to_char(cast('99999912399.9567' as decimal(18, 5)), '#.#####') DEC18_1, " +
-        "to_char(cast('12345678912345678912.5567' as decimal(28, 5)), '#,###.#####') DEC28_1, " +
-        "to_char(cast('999999999999999999999999999.5' as decimal(38, 1)), '#.#') DEC38_1 " +
-        "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "FLOAT8_1=1,234.56; " +
-                "FLOAT8_2=$1,234.50; " +
-                "DEC9_1=1,234.56; " +
-                "DEC18_1=99999912399.9567; " +
-                "DEC28_1=12,345,678,912,345,678,912.5567; " +
-                "DEC38_1=999999999999999999999999999.5\n"
-        );
-  }
-
-
-  @Test
-  public void testConcatFunction() throws Exception {
-    String query = String.format("SELECT " +
-        "concat('1234', ' COL_VALUE ', R_REGIONKEY, ' - STRING') as STR_1 " +
-        "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "STR_1=1234 COL_VALUE 0 - STRING\n"
-        );
-  }
-
-  @Test
-  @Ignore
-  public void testTimeStampConstant() throws Exception {
-    String query = String.format("SELECT " +
-        "timestamp '2008-2-23 12:23:23' as TS " +
-        "FROM dfs_test.`%s/../../sample-data/region.parquet` limit 1", WORKING_PATH);
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "TS=2008-02-23 12:23:23.0\n"
-        );
-  }
-
-  @Test
-  public void testNullConstantsTimeTimeStampAndDate() throws Exception {
-    JdbcAssert.withNoDefaultSchema()
-        .sql("SELECT CAST(NULL AS TIME) AS t, " +
-            "CAST(NULL AS TIMESTAMP) AS ts, " +
-            "CAST(NULL AS DATE) AS d " +
-            "FROM cp.`region.json` LIMIT 1")
-        .returns("t=null; ts=null; d=null");
-  }
-
-  @Test
-  public void testIntMinToDecimal() throws Exception {
-    String query = "select cast((employee_id - employee_id + -2147483648) as decimal(28, 2)) as DEC_28," +
-                   "cast((employee_id - employee_id + -2147483648) as decimal(18, 2)) as DEC_18 from " +
-                   "cp.`employee.json` limit 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC_28=-2147483648.00; " +
-            "DEC_18=-2147483648.00\n"
-        );
-  }
-
-  @Test
-  public void testHashFunctions() throws Exception {
-    String query = "select hash(cast(hire_date as date)), hash(cast(employee_id as decimal(9, 2))), hash(cast(employee_id as decimal(38, 11)))" +
-                   "from cp.`employee.json` limit 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query);
-  }
-
-  @Test
-  public void testDecimalAddConstant() throws Exception {
-    String query = "select (cast('-1' as decimal(37, 3)) + cast (employee_id as decimal(37, 3))) as CNT " +
-        "from cp.`employee.json` where employee_id <= 4";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "CNT=0.000\n" +
-            "CNT=1.000\n" +
-            "CNT=3.000\n");
-  }
-
-  @Test
-  public void testDecimalAddIntConstant() throws Exception {
-    String query = "select 1 + cast(employee_id as decimal(9, 3)) as DEC_9 , 1 + cast(employee_id as decimal(37, 5)) as DEC_38 " +
-        "from cp.`employee.json` where employee_id <= 2";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC_9=2.000; " +
-            "DEC_38=2.00000\n" +
-            "DEC_9=3.000; " +
-            "DEC_38=3.00000\n");
-  }
-
-  @Test
-  public void testSignFunction() throws Exception {
-    String query = "select sign(cast('1.23' as float)) as SIGN_FLOAT, sign(-1234.4567) as SIGN_DOUBLE, sign(23) as SIGN_INT " +
-        "from cp.`employee.json` where employee_id < 2";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "SIGN_FLOAT=1; " +
-            "SIGN_DOUBLE=-1; " +
-            "SIGN_INT=1\n");
-  }
-
-  @Ignore
-  @Test
-  public void testDateTrunc() throws Exception {
-    String query = "select "
-        + "date_trunc('MINUTE', time '2:30:21.5') as TIME1, "
-        + "date_trunc('SECOND', time '2:30:21.5') as TIME2, "
-        + "date_trunc('HOUR', timestamp '1991-05-05 10:11:12.100') as TS1, "
-        + "date_trunc('SECOND', timestamp '1991-05-05 10:11:12.100') as TS2, "
-        + "date_trunc('MONTH', date '2011-2-2') as DATE1, "
-        + "date_trunc('YEAR', date '2011-2-2') as DATE2 "
-        + "from cp.`employee.json` where employee_id < 2";
-    JdbcAssert.withNoDefaultSchema()
-    .sql(query)
-    .returns(
-        "TIME1=02:30:00; " +
-        "TIME2=02:30:21; " +
-        "TS1=1991-05-05 10:00:00.0; " +
-        "TS2=1991-05-05 10:11:12.0; " +
-        "DATE1=2011-02-01; " +
-        "DATE2=2011-01-01\n");
-  }
-
-  @Test
-  @Ignore
-  public void testToTimeStamp() throws Exception {
-    String query = "select to_timestamp(cast('800120400.12312' as decimal(38, 5))) as DEC38_TS, to_timestamp(200120400) as INT_TS " +
-        "from cp.`employee.json` where employee_id < 2";
-
-    DateTime result1 = new DateTime(800120400123l);
-    DateTime result2 = new DateTime(200120400000l);
-    DateTimeFormatter f = DateTimeFormat.forPattern("yyyy-MM-dd'T'HH:mm:ss.SSSZZ");
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC38_TS=" + f.print(result1)+ "; " +
-            "INT_TS=" + f.print(result2) + "\n");
-  }
-
-  @Test
-  public void testPadFunctions() throws Exception {
-    String query = "select rpad(first_name, 10) as RPAD_DEF, rpad(first_name, 10, '*') as RPAD_STAR, lpad(first_name, 10) as LPAD_DEF, lpad(first_name, 10, '*') as LPAD_STAR, " +
-        "lpad(first_name, 2) as LPAD_TRUNC, rpad(first_name, 2) as RPAD_TRUNC " +
-        "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "RPAD_DEF=Sheri     ; " +
-            "RPAD_STAR=Sheri*****; " +
-            "LPAD_DEF=     Sheri; " +
-            "LPAD_STAR=*****Sheri; " +
-            "LPAD_TRUNC=Sh; " +
-            "RPAD_TRUNC=Sh\n");
-  }
-
-  @Test
-  public void testExtractSecond() throws Exception {
-    String query = "select extract(second from date '2008-2-23') as DATE_EXT, extract(second from timestamp '2008-2-23 10:00:20.123') as TS_EXT, " +
-        "extract(second from time '10:20:30.303') as TM_EXT " +
-        "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DATE_EXT=0.0; " +
-            "TS_EXT=20.123; " +
-            "TM_EXT=30.303\n");
-  }
-
-  @Test
-  public void testCastDecimalDouble() throws Exception {
-    String query = "select cast((cast('1.0001' as decimal(18, 9))) as double) DECIMAL_DOUBLE_CAST " +
-        "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DECIMAL_DOUBLE_CAST=1.0001\n");
-  }
-
-  @Ignore("we don't have decimal division")
-  @Test
-  public void testCastDecimalDivide() throws Exception {
-    String query = "select  (cast('9' as decimal(9, 1)) / cast('2' as decimal(4, 1))) as DEC9_DIV, " +
-        "cast('999999999' as decimal(9,0)) / cast('0.000000000000000000000000001' as decimal(28,28)) as DEC38_DIV, " +
-        "cast('123456789.123456789' as decimal(18, 9)) * cast('123456789.123456789' as decimal(18, 9)) as DEC18_MUL " +
-        "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC9_DIV=4.500000000; " +
-            "DEC38_DIV=999999999000000000000000000000000000.0; " +
-            "DEC18_MUL=15241578780673678.515622620750190521\n");
-  }
-
-  @Test
-  public void testExtractSecondFromInterval() throws Exception {
-    String query = "select extract (second from interval '1 2:30:45.100' day to second) as EXT_INTDAY " +
-        "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "EXT_INTDAY=45.1\n");
-  }
-
-  @Test
-  public void testFunctionCaseInsensitiveNames() throws Exception {
-    String query = "SELECT to_date('2003/07/09', 'yyyy/MM/dd') as col1, " +
-        "TO_DATE('2003/07/09', 'yyyy/MM/dd') as col2, " +
-        "To_DaTe('2003/07/09', 'yyyy/MM/dd') as col3 " +
-        "from cp.`employee.json` LIMIT 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns("col1=2003-07-09; col2=2003-07-09; col3=2003-07-09");
-  }
-
-  @Test
-  public void testDecimal18Decimal38Comparison() throws Exception {
-    String query = "select cast('-999999999.999999999' as decimal(18, 9)) = cast('-999999999.999999999' as decimal(38, 18)) as CMP " +
-        "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "CMP=true\n");
-  }
-
-  @Test
-  public void testDecimalMultiplicationOverflowHandling() throws Exception {
-    String query = "select cast('1' as decimal(9, 5)) * cast ('999999999999999999999999999.999999999' as decimal(38, 9)) as DEC38_1, " +
-                   "cast('1000000000000000001.000000000000000000' as decimal(38, 18)) * cast('0.999999999999999999' as decimal(38, 18)) as DEC38_2, " +
-                   "cast('3' as decimal(9, 8)) * cast ('333333333.3333333333333333333' as decimal(38, 19)) as DEC38_3 " +
-                   "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC38_1=1000000000000000000000000000.00000; " +
-            "DEC38_2=1000000000000000000; " +
-            "DEC38_3=1000000000.000000000000000000\n");
-  }
-
-  @Test
-  public void testOptiqDecimalCapping() throws Exception {
-    String query = "select  cast('12345.678900000' as decimal(18, 9))=cast('12345.678900000' as decimal(38, 9)) as CMP " +
-                   "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "CMP=true\n");
-  }
-
-  @Test
-  public void testDecimalRoundUp() throws Exception {
-    String query = "select cast('999999999999999999.9999999999999999995' as decimal(38, 18)) as DEC38_1, " +
-                   "cast('999999999999999999.9999999999999999994' as decimal(38, 18)) as DEC38_2, " +
-                   "cast('999999999999999999.1234567895' as decimal(38, 9)) as DEC38_3, " +
-                   "cast('99999.12345' as decimal(18, 4)) as DEC18_1, " +
-                   "cast('99999.99995' as decimal(18, 4)) as DEC18_2 " +
-                   "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "DEC38_1=1000000000000000000.000000000000000000; " +
-            "DEC38_2=999999999999999999.999999999999999999; " +
-            "DEC38_3=999999999999999999.123456790; " +
-            "DEC18_1=99999.1235; " +
-            "DEC18_2=100000.0000\n");
-  }
-
-  @Test
-  public void testNegative() throws Exception {
-    String query = "select  negative(2) as NEG " +
-                   "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns(
-            "NEG=-2\n");
-  }
-
-  @Test
-  public void testOptiqValidationFunctions() throws Exception {
-    String query = "select trim(first_name) as TRIM_STR, substring(first_name, 2) as SUB_STR " +
-                   "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns("TRIM_STR=Sheri; "+
-                 "SUB_STR=heri\n");
-  }
-  @Test
-  public void testDecimalDownwardCast() throws Exception {
-    String query = "select cast((cast('12345.6789' as decimal(18, 4))) as decimal(9, 4)) as DEC18_DEC9_1, " +
-                   "cast((cast('12345.6789' as decimal(18, 4))) as decimal(9, 2)) as DEC18_DEC9_2, " +
-                   "cast((cast('-12345.6789' as decimal(18, 4))) as decimal(9, 0)) as DEC18_DEC9_3, " +
-                   "cast((cast('999999999.6789' as decimal(38, 4))) as decimal(9, 0)) as DEC38_DEC19_1, " +
-                   "cast((cast('-999999999999999.6789' as decimal(38, 4))) as decimal(18, 2)) as DEC38_DEC18_1, " +
-                   "cast((cast('-999999999999999.6789' as decimal(38, 4))) as decimal(18, 0)) as DEC38_DEC18_2, " +
-                   "cast((cast('100000000999999999.6789' as decimal(38, 4))) as decimal(28, 0)) as DEC38_DEC28_1 " +
-                   "from cp.`employee.json` where employee_id = 1";
-
-    JdbcAssert.withNoDefaultSchema()
-        .sql(query)
-        .returns("DEC18_DEC9_1=12345.6789; "+
-                 "DEC18_DEC9_2=12345.68; " +
-                 "DEC18_DEC9_3=-12346; " +
-                 "DEC38_DEC19_1=1000000000; " +
-                 "DEC38_DEC18_1=-999999999999999.68; " +
-                 "DEC38_DEC18_2=-1000000000000000; " +
-                 "DEC38_DEC28_1=100000001000000000\n");
-  }
-}