Search code examples
apache-calcite

How To Troubleshot This Exception :No match found for function signature IF(<BOOLEAN>, <NUMERIC>, <NUMERIC>)

I have a table defined 2 filed:integer id,varchar name

I can execute common query like this right:

ResultSet resultSet = statement.executeQuery("select \"id\",\"name\" from test.test01 where \"id\" in (1,2)");

Now，I want use "if" condition in select statement，execute this query：

ResultSet resultSet = statement.executeQuery("select \"id\",IF(\"id\">=1, 100, 200) as myid,\"name\" from test.test01 where \"id\" in (1,2)");

Caught Exception：

    java.sql.SQLException: Error while executing SQL "select "id",IF("id">=1, 100, 200) as myid,"name" from test.test01 where "id" in (1,2)": From line 1, column 13 to line 1, column 33: No match found for function signature IF(<BOOLEAN>, <NUMERIC>, <NUMERIC>)
    at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
    at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
    at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
    at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227)
    at org.example.Client.main(Client.java:40)
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 13 to line 1, column 33: No match found for function signature IF(<BOOLEAN>, <NUMERIC>, <NUMERIC>)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:932)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5266)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1948)
    at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:326)
    at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:231)
    at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6277)
    at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6264)
    at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1862)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1847)
    at org.apache.calcite.sql.SqlAsOperator.deriveType(SqlAsOperator.java:133)
    at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6277)
    at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6264)
    at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1862)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1847)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:463)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4409)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3652)
    at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:64)
    at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1100)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1071)
    at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:247)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1046)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:752)
    at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:586)
    at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
    at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
    at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:648)
    at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:514)
    at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:484)
    at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:234)
    at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:623)
    at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:674)
    at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
    ... 2 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature IF(<BOOLEAN>, <NUMERIC>, <NUMERIC>)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
    at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599)
    ... 39 more

How To Troubleshot this problem, is there any thing wrong?

————————

Solution1：add connection config like below

URL url = Client.class.getResource("/model.json");
String str = URLDecoder.decode(url.toString(), "UTF-8");
Properties info = new Properties();
info.put("model", str.replace("file:", ""));
info.put("fun","hive");
Connection connection = DriverManager.getConnection("jdbc:calcite:", info);

Solution2：use case-when instead：

ResultSet resultSet = statement.executeQuery("select \"id\",\"id\"*10 as myid1,case when \"id\">1 then 100 else 200 end as myid2,\"name\" from test.test01 where \"id\" in (1,2,3)");
Solution

  • I was not able to find the IF function in the calcite docs. I think it is MySQL specific. Instead you have CASE I think the correct way to use it here would be

    CASE WHEN 
"id">=1 THEN 100
ELSE 200
END

    Check out the docs here: https://calcite.apache.org/docs/reference.html

    EDIT: good spot by OP, IF exists, but can only be used with the right databases and connection strings.