You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by Yash Sharma <ya...@impetus.co.in> on 2013/09/08 13:42:12 UTC
Exception while implementing drill trigonometric functions (For
DRILL-219). Need tips.
Hi All,
I have added few trigonometric for drill's math functions but am ending with the below exception when trying to use it in query. I have implemented these new functions exactly same as the floor/ceil functions (which i implemented previously). Floor and ceil are working correctly as expected.
Can anyone throw some tips on what i am messing up here?
Also adding the diff file for reference.
##################### SQLLINE EXCEPTION #################
0: jdbc:drill:schema=parquet-local> SELECT sin(2.75) FROM "sample-data/region.parquet";
Sep 08, 2013 4:57:40 PM org.eigenbase.sql.validate.SqlValidatorException <init>
SEVERE: org.eigenbase.sql.validate.SqlValidatorException: No match found for function signature SIN(<NUMERIC>)
Sep 08, 2013 4:57:40 PM org.eigenbase.util.EigenbaseException <init>
SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1, column 8 to line 1, column 16
org.eigenbase.util.EigenbaseContextException: From line 1, column 8 to line 1, column 16
at org.eigenbase.resource.EigenbaseResource$_Def12.ex(EigenbaseResource.java:1026)
at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:739)
at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:726)
at org.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3830)
at org.eigenbase.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1594)
at org.eigenbase.sql.SqlFunction.deriveType(SqlFunction.java:312)
at org.eigenbase.sql.SqlFunction.deriveType(SqlFunction.java:222)
at org.eigenbase.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4238)
at org.eigenbase.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4220)
at org.eigenbase.sql.SqlCall.accept(SqlCall.java:187)
at org.eigenbase.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1502)
at org.eigenbase.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1484)
at org.eigenbase.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:435)
at org.eigenbase.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:3290)
at org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3026)
at org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:69)
at org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:90)
at org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:802)
at org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:790)
at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:154)
at org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:753)
at org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:444)
at org.eigenbase.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:445)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:160)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:129)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare2_(OptiqPrepareImpl.java:255)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:195)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:168)
at net.hydromatic.optiq.jdbc.OptiqStatement.parseQuery(OptiqStatement.java:402)
at net.hydromatic.optiq.jdbc.OptiqStatement.execute(OptiqStatement.java:192)
at sqlline.SqlLine$Commands.execute(SqlLine.java:3825)
at sqlline.SqlLine$Commands.sql(SqlLine.java:3738)
at sqlline.SqlLine.dispatch(SqlLine.java:882)
at sqlline.SqlLine.begin(SqlLine.java:717)
at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:460)
at sqlline.SqlLine.main(SqlLine.java:443)
Caused by: org.eigenbase.sql.validate.SqlValidatorException: No match found for function signature SIN(<NUMERIC>)
at org.eigenbase.resource.EigenbaseResource$_Def9.ex(EigenbaseResource.java:963)
... 32 more
##################### DIFF #################
diff --git a/common/src/main/java/org/apache/drill/common/expression/fn/MathFunctions.java b/common/src/main/java/org/apache/drill/common/expression/fn/MathFunctions.java
index b1e1b67..34a1a4a 100644
--- a/common/src/main/java/org/apache/drill/common/expression/fn/MathFunctions.java
+++ b/common/src/main/java/org/apache/drill/common/expression/fn/MathFunctions.java
@@ -17,10 +17,14 @@
******************************************************************************/
package org.apache.drill.common.expression.fn;
+import org.apache.drill.common.expression.Arg;
import org.apache.drill.common.expression.ArgumentValidators;
+import org.apache.drill.common.expression.BasicArgumentValidator;
import org.apache.drill.common.expression.FunctionDefinition;
import org.apache.drill.common.expression.CallProvider;
import org.apache.drill.common.expression.OutputTypeDeterminer;
+import org.apache.drill.common.types.TypeProtos;
+import org.apache.drill.common.types.Types;
@@ -35,7 +39,19 @@ public class MathFunctions implements CallProvider{
FunctionDefinition.operator("divide", new ArgumentValidators.NumericTypeAllowed(1, Integer.MAX_VALUE, true), new OutputTypeDeterminer.SameAsAnySoft(), "/"),
FunctionDefinition.operator("multiply", new ArgumentValidators.NumericTypeAllowed(1, Integer.MAX_VALUE, true), new OutputTypeDeterminer.SameAsAnySoft(), "*"),
FunctionDefinition.operator("modulo", new ArgumentValidators.NumericTypeAllowed(1, Integer.MAX_VALUE, true), new OutputTypeDeterminer.SameAsAnySoft(), "%"),
- XOR
+ XOR,
+ FunctionDefinition.simple("floatfloor", new BasicArgumentValidator(new Arg(Types.required(TypeProtos.MinorType.FLOAT8))), new OutputTypeDeterminer.SameAsFirstInput(), "floor"),
+ FunctionDefinition.simple("floatceil", new BasicArgumentValidator(new Arg(Types.required(TypeProtos.MinorType.FLOAT8))), new OutputTypeDeterminer.SameAsFirstInput(), "ceil"),
+ FunctionDefinition.simple("floatcos", new BasicArgumentValidator(new Arg(Types.required(TypeProtos.MinorType.FLOAT8))), new OutputTypeDeterminer.SameAsAnySoft(), "cos"),
+ FunctionDefinition.simple("floatsin", new BasicArgumentValidator(new Arg(Types.required(TypeProtos.MinorType.FLOAT8))), new OutputTypeDeterminer.SameAsAnySoft(), "sin"),
+ FunctionDefinition.simple("floattan", new BasicArgumentValidator(new Arg(Types.required(TypeProtos.MinorType.FLOAT8))), new OutputTypeDeterminer.SameAsAnySoft(), "tan"),
+ FunctionDefinition.simple("floatacos", new BasicArgumentValidator(new Arg(Types.required(TypeProtos.MinorType.FLOAT8))), new OutputTypeDeterminer.SameAsFirstInput(), "acos"),
+ FunctionDefinition.simple("floatasin", new BasicArgumentValidator(new Arg(Types.required(TypeProtos.MinorType.FLOAT8))), new OutputTypeDeterminer.SameAsFirstInput(), "asin"),
+ FunctionDefinition.simple("floatatan", new BasicArgumentValidator(new Arg(Types.required(TypeProtos.MinorType.FLOAT8))), new OutputTypeDeterminer.SameAsFirstInput(), "atan"),
+ FunctionDefinition.simple("floatcosh", new BasicArgumentValidator(new Arg(Types.required(TypeProtos.MinorType.FLOAT8))), new OutputTypeDeterminer.SameAsFirstInput(), "cosh"),
+ FunctionDefinition.simple("floatsinh", new BasicArgumentValidator(new Arg(Types.required(TypeProtos.MinorType.FLOAT8))), new OutputTypeDeterminer.SameAsFirstInput(), "sinh"),
+ FunctionDefinition.simple("floattanh", new BasicArgumentValidator(new Arg(Types.required(TypeProtos.MinorType.FLOAT8))), new OutputTypeDeterminer.SameAsFirstInput(), "tanh")
+
};
}
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/MathFunctions.java b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/MathFunctions.java
index 3f4b976..8d143cf 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/MathFunctions.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/MathFunctions.java
@@ -1,5 +1,12 @@
package org.apache.drill.exec.expr.fn.impl;
+import org.apache.drill.common.expression.Arg;
+import org.apache.drill.common.expression.BasicArgumentValidator;
+import org.apache.drill.common.expression.CallProvider;
+import org.apache.drill.common.expression.FunctionDefinition;
+import org.apache.drill.common.expression.OutputTypeDeterminer;
+import org.apache.drill.common.types.TypeProtos;
+import org.apache.drill.common.types.Types;
import org.apache.drill.exec.expr.DrillSimpleFunc;
import org.apache.drill.exec.expr.annotations.FunctionTemplate;
import org.apache.drill.exec.expr.annotations.FunctionTemplate.FunctionScope;
@@ -7,6 +14,7 @@ import org.apache.drill.exec.expr.annotations.FunctionTemplate.NullHandling;
import org.apache.drill.exec.expr.annotations.Output;
import org.apache.drill.exec.expr.annotations.Param;
import org.apache.drill.exec.expr.holders.BigIntHolder;
+import org.apache.drill.exec.expr.holders.Float8Holder;
import org.apache.drill.exec.expr.holders.IntHolder;
import org.apache.drill.exec.record.RecordBatch;
@@ -59,4 +67,172 @@ public class MathFunctions{
}
-}
+
+
+ @FunctionTemplate(name = "floatceil", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class Float8Ceil implements DrillSimpleFunc{
+
+ @Param Float8Holder input;
+ @Output Float8Holder out;
+
+ public void setup(RecordBatch b){}
+
+ public void eval(){
+ out.value = Math.ceil(input.value);
+ }
+
+ }
+
+
+ @FunctionTemplate(name = "floatfloor", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class Float8Floor implements DrillSimpleFunc{
+
+ @Param Float8Holder input;
+ @Output Float8Holder out;
+
+ public void setup(RecordBatch b){}
+
+ public void eval(){
+ out.value = Math.floor(input.value);
+ }
+
+ }
+
+
+
+ @FunctionTemplate(name = "floatcos", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class Float8Cos implements DrillSimpleFunc{
+
+ @Param Float8Holder input;
+ @Output Float8Holder out;
+
+ public void setup(RecordBatch b){}
+
+ public void eval(){
+ out.value = Math.cos(input.value);
+ }
+
+ }
+
+
+ @FunctionTemplate(name = "floatsin", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class Float8Sin implements DrillSimpleFunc{
+
+ @Param Float8Holder input;
+ @Output Float8Holder out;
+
+ public void setup(RecordBatch b){}
+
+ public void eval(){
+ out.value = Math.sin(input.value);
+ }
+
+ }
+
+
+ @FunctionTemplate(name = "floattan", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class Float8Tan implements DrillSimpleFunc{
+
+ @Param Float8Holder input;
+ @Output Float8Holder out;
+
+ public void setup(RecordBatch b){}
+
+ public void eval(){
+ out.value = Math.tan(input.value);
+ }
+
+ }
+
+
+ @FunctionTemplate(name = "floatacos", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class Float8ACos implements DrillSimpleFunc{
+
+ @Param Float8Holder input;
+ @Output Float8Holder out;
+
+ public void setup(RecordBatch b){}
+
+ public void eval(){
+ out.value = Math.acos(input.value);
+ }
+
+ }
+
+
+
+ @FunctionTemplate(name = "floatasin", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class Float8ASin implements DrillSimpleFunc{
+
+ @Param Float8Holder input;
+ @Output Float8Holder out;
+
+ public void setup(RecordBatch b){}
+
+ public void eval(){
+ out.value = Math.asin(input.value);
+ }
+
+ }
+
+
+ @FunctionTemplate(name = "floatatan", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class Float8ATan implements DrillSimpleFunc{
+
+ @Param Float8Holder input;
+ @Output Float8Holder out;
+
+ public void setup(RecordBatch b){}
+
+ public void eval(){
+ out.value = Math.atan(input.value);
+ }
+
+ }
+
+
+ @FunctionTemplate(name = "floatcosh", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class Float8Cosh implements DrillSimpleFunc{
+
+ @Param Float8Holder input;
+ @Output Float8Holder out;
+
+ public void setup(RecordBatch b){}
+
+ public void eval(){
+ out.value = Math.cosh(input.value);
+ }
+
+ }
+
+
+ @FunctionTemplate(name = "floatsinh", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class Float8Sinh implements DrillSimpleFunc{
+
+ @Param Float8Holder input;
+ @Output Float8Holder out;
+
+ public void setup(RecordBatch b){}
+
+ public void eval(){
+ out.value = Math.sinh(input.value);
+ }
+
+ }
+
+
+ @FunctionTemplate(name = "floattanh", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class Float8Tanh implements DrillSimpleFunc{
+
+ @Param Float8Holder input;
+ @Output Float8Holder out;
+
+ public void setup(RecordBatch b){}
+
+ public void eval(){
+ out.value = Math.tanh(input.value);
+ }
+
+ }
+
+}
\ No newline at end of file
##################### END #################
________________________________
NOTE: This message may contain information that is confidential, proprietary, privileged or otherwise protected by law. The message is intended solely for the named addressee. If received in error, please destroy and notify the sender. Any use of this email is prohibited when received in error. Impetus does not represent, warrant and/or guarantee, that the integrity of this communication has been maintained nor that the communication is free of errors, virus, interception or interference.
Re: Exception while implementing drill trigonometric functions (For DRILL-219). Need tips.
Posted by Julian Hyde <ju...@gmail.com>.
On Sep 9, 2013, at 1:09 PM, Ted Dunning <te...@gmail.com> wrote:
> I encountered the same problem adding things like AVG. Are aggregators
> handled the same way (and in the same place)?
I suspect it's a little bit different. First, AVG is already a built-in (as were some of the other functions that have recently been implemented in Drill, such as Abs and IS NOT NULL). So you probably saw a "cannot implement" exception rather than "unknown function".
Second, Although aggregate functions are defined in Optiq fairly similarly to regular functions (in terms of validating the number and type of parameters, overload resolution, deducing the return type), they are implemented differently because they need different code-generation.
But AVG isn't treated like a "typical" aggregate function. The built-in implementation of AVG treats it more like a macro, decomposing it into SUM / COUNT (taking special care when COUNT is 0), so code-generation is not required.
There are very few "typical" aggregate functions. I'm sure Drill & Optiq will want to allow user-defined aggregate functions at some point, but they are likely to be a compromise in terms of type-deduction and performance.
Julian
Re: Exception while implementing drill trigonometric functions (For
DRILL-219). Need tips.
Posted by Ted Dunning <te...@gmail.com>.
Julian,
I encountered the same problem adding things like AVG. Are aggregators
handled the same way (and in the same place)?
On Mon, Sep 9, 2013 at 12:30 PM, Julian Hyde <ju...@gmail.com> wrote:
> That error stack says that the SIN function is not known to Optiq's SQL
> validator. Optiq's validator is driven by a table of functions and
> operators, but Drill doesn't currently have a way to add to that table.
>
> Since SIN is a generally useful function, it should be built-in in Optiq.
> You could do that by modifying SqlStdOperatorTable and adding a field
> similar to the one that defines the SQRT operator:
>
> public static final SqlFunction sqrtFunc =
> new SqlFunction(
> "SQRT",
> SqlKind.OTHER_FUNCTION,
> SqlTypeStrategies.rtiNullableDouble,
> null,
> SqlTypeStrategies.otcNumeric,
> SqlFunctionCategory.Numeric);
>
> I believe the validation rules are identical for SQRT and SIN.
>
> However we should also add a mechanism to Drill to allow it to add its own
> functions (including user-defined functions) to Optiq's SQL validator on
> the fly.
>
> Julian
>
>
Re: Exception while implementing drill trigonometric functions (For DRILL-219). Need tips.
Posted by Julian Hyde <ju...@gmail.com>.
On Sep 9, 2013, at 11:02 PM, Yash Sharma <ya...@impetus.co.in> wrote:
> We can probably have a discussion in hangout today on how to add the mechanism to drill itself. That would make future implementations of newer UDF's easy for developers, rather than having to modify SqlStdOperatorTable in Optiq every time.
It's a good topic for discussion at the hangout. But unfortunately I'll miss this hangout -- I'm at the XLDB conference tomorrow.
Julian
RE: Exception while implementing drill trigonometric functions (For
DRILL-219). Need tips.
Posted by Yash Sharma <ya...@impetus.co.in>.
We can probably have a discussion in hangout today on how to add the mechanism to drill itself. That would make future implementations of newer UDF's easy for developers, rather than having to modify SqlStdOperatorTable in Optiq every time.
Cheers,
Yash
-----Original Message-----
From: Julian Hyde [mailto:julianhyde@gmail.com]
Sent: Tuesday, September 10, 2013 1:00 AM
To: drill-dev@incubator.apache.org
Subject: Re: Exception while implementing drill trigonometric functions (For DRILL-219). Need tips.
That error stack says that the SIN function is not known to Optiq's SQL validator. Optiq's validator is driven by a table of functions and operators, but Drill doesn't currently have a way to add to that table.
Since SIN is a generally useful function, it should be built-in in Optiq. You could do that by modifying SqlStdOperatorTable and adding a field similar to the one that defines the SQRT operator:
public static final SqlFunction sqrtFunc =
new SqlFunction(
"SQRT",
SqlKind.OTHER_FUNCTION,
SqlTypeStrategies.rtiNullableDouble,
null,
SqlTypeStrategies.otcNumeric,
SqlFunctionCategory.Numeric);
I believe the validation rules are identical for SQRT and SIN.
However we should also add a mechanism to Drill to allow it to add its own functions (including user-defined functions) to Optiq's SQL validator on the fly.
Julian
________________________________
NOTE: This message may contain information that is confidential, proprietary, privileged or otherwise protected by law. The message is intended solely for the named addressee. If received in error, please destroy and notify the sender. Any use of this email is prohibited when received in error. Impetus does not represent, warrant and/or guarantee, that the integrity of this communication has been maintained nor that the communication is free of errors, virus, interception or interference.
Re: Exception while implementing drill trigonometric functions (For DRILL-219). Need tips.
Posted by Julian Hyde <ju...@gmail.com>.
That error stack says that the SIN function is not known to Optiq's SQL validator. Optiq's validator is driven by a table of functions and operators, but Drill doesn't currently have a way to add to that table.
Since SIN is a generally useful function, it should be built-in in Optiq. You could do that by modifying SqlStdOperatorTable and adding a field similar to the one that defines the SQRT operator:
public static final SqlFunction sqrtFunc =
new SqlFunction(
"SQRT",
SqlKind.OTHER_FUNCTION,
SqlTypeStrategies.rtiNullableDouble,
null,
SqlTypeStrategies.otcNumeric,
SqlFunctionCategory.Numeric);
I believe the validation rules are identical for SQRT and SIN.
However we should also add a mechanism to Drill to allow it to add its own functions (including user-defined functions) to Optiq's SQL validator on the fly.
Julian