[ https://issues.apache.org/jira/browse/CALCITE-2053?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16253170#comment-16253170 ]
yuqi edited comment on CALCITE-2053 at 11/15/17 9:29 AM: --------------------------------------------------------- The problem is that in function filterRoutinesByParameterType, calcite do think decimal(bigdecimal in java) can transfer to double and vice versa(see SqlTypeAssignmentRules.java), however in function filterRoutinesByTypePrecedence,according to number type precedence list in SqlTypeExplicitPrecedentList.java, decimal value can to cast to decimal and double and double value can only be cast to double, this is why this problem exist. My points to solve the problem are as follows: 1. we can change function containsType in class SqlTypeExplicitPrecedentList, When containsType return false, containsType directly return a positive values which means the second parameter does't precedent to the first one, thus best match parameter is still the former. 2. we can also change the precedent list in SqlTypeExplicitPrecedentList, what puzzle me is that how to choose the sequence of Decimal and Double ? now decimal is in front of double, double value can't cast to decimal, if put decimal after double then decimal, decimal value can't cast to double. What's your opinion about this problem, suggestions are welcome [~julianhyde], thanks was (Author: yuqi): The problem is that in function filterRoutinesByParameterType, calcite do think decimal(bigdecimal in java) can transfer to double and vice versa(see SqlTypeAssignmentRules.java), however in function filterRoutinesByTypePrecedence , according to number type precedence list in SqlTypeExplicitPrecedentList.java , decimal value can to cast to decimal and double and double value can only be cast to double, this is why this problem exist. My points to solve the problem are as follows: 1. we can change function containsType in class SqlTypeExplicitPrecedentList , When containsType return false, containsType directly return a positive values which means the second parameter does't precedent to the first one, thus best match parameter is still the former. 2. we can also change the precedent list in SqlTypeExplicitPrecedentList , what puzzle me is that how to choose the sequence of Decimal and Double ? now decimal is in front of double, double value can't cast to decimal, if put decimal after double then decimal, decimal value can't cast to double. What's your opinion about this problem, suggestions are welcome [~julianhyde], thanks > Overloaded user-defined functions that have Double and BigDecimal arguments > will goes wrong > ------------------------------------------------------------------------------------------- > > Key: CALCITE-2053 > URL: https://issues.apache.org/jira/browse/CALCITE-2053 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.13.0 > Reporter: yuqi > Assignee: Julian Hyde > Fix For: 1.14.0 > > > We define a udf function class have two function as follows > {code:java} > public double toDouble(Double var) { > return var; > } > > public double toDouble(BigDecimal var) { > return var.doubleValue(); > } > {code} > when use it in sql like: > {code:sql} > select sum(price) from tb; > {code} > where price is a double value in table tb, exception occurs: > {code:java} > java.lang.AssertionError: DECIMAL(19, 0) > at > org.apache.calcite.sql.type.SqlTypeExplicitPrecedenceList.compareTypePrecedence(SqlTypeExplicitPrecedenceList.java:154) > at org.apache.calcite.sql.SqlUtil.bestMatch(SqlUtil.java:626) > at > org.apache.calcite.sql.SqlUtil.filterRoutinesByTypePrecedence(SqlUtil.java:592) > at > org.apache.calcite.sql.SqlUtil.lookupSubjectRoutines(SqlUtil.java:446) > at org.apache.calcite.sql.SqlUtil.lookupRoutine(SqlUtil.java:371) > at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:245) > at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:223) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5371) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5358) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1592) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1577) > at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:236) > at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:407) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5081) > at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:115) > at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:235) > at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:407) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5081) > at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:115) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:901) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:611) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:551) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:263) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:229) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:786) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:640) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:610) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:221) > at > org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:603) > at > org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:638) > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:149) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:218) > at > org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:564) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1337) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1320) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1284) > at > org.apache.calcite.test.UdfTest.testDoubleAndDecimal(UdfTest.java:896) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:497) > {code} -- This message was sent by Atlassian JIRA (v6.4.14#64029)