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

Question

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)");

DownloadPizza · Accepted Answer · 2022-09-06T06:52:49.387

0

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.

edited Sep 06 '22 at 06:52

answered Sep 05 '22 at 09:19

DownloadPizza

3,307
1
12
27

use case-when instead can execute successfully,thanks so much.And search key world:"IF(condition, value1, value2)" can found if function https://calcite.apache.org/docs/reference.html ，seems calcite support if function,but I still don't know how to use it rightly. – yanling Sep 05 '22 at 09:24
@yanling you are right, there is a compatibility column next to it. "The following operators are not in the SQL standard, and are not enabled in Calcite’s default operator table. They are only available for use in queries if your session has enabled an extra operator table." im guessing you dont have it enabled or are in the wrong dialect. According to the table IF is avilable in BigQuery, Hive and Spark – DownloadPizza Sep 05 '22 at 09:36
But very good spot, ill add it to the answer for future reference. – DownloadPizza Sep 05 '22 at 09:38
1

Good.And by the way, I've tried to add "fun"="hive" to connection config,I can execute `if` correctly. – yanling Sep 05 '22 at 09:43
Oh yeah mind marking this as the accepted answer? – DownloadPizza Sep 06 '22 at 06:52

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

1 Answers1