[ 
https://issues.apache.org/jira/browse/CALCITE-2053?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16253170#comment-16253170
 ] 

yuqi commented on CALCITE-2053:
-------------------------------

The problem is that  in function 

{code:java}
filterRoutinesByParameterType
{code}
 , calcite do think decimal(bigdecimal in java)  can transfer to double and 
vice versa(see 
{code:java}
SqlTypeAssignmentRules.java
{code}
) 
however in function 
{code:java}
filterRoutinesByTypePrecedence
{code}
, according to number type precedence list in 
{code:java}
SqlTypeExplicitPrecedentList.java
{code}
,  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  
{code:java}
containsType 
{code}
in class 
{code:java}
SqlTypeExplicitPrecedentList
{code}
, 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 
{code:java}
SqlTypeExplicitPrecedentList
{code}
, 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)

Reply via email to