[CALCITE-5569] Functions with 'SPECIAL' SqlSyntax could not be created in SqlLibraryOperators

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Not A Bug
• Fix Version/s: 1.34.0
• Assignee: Unassigned
• Reporter: Dan Zou
• Created: 09/Mar/23 08:58
• Updated: 19/Mar/23 09:19
• Resolved: 09/Mar/23 09:59

Description

I want to add a function with 'SPECIAL' SqlSyntax in SqlLibraryOperators, but I get a function not found exception

org.apache.calcite.runtime.CalciteContextException: From line 1, column 9 to line 1, column 35: No match found for function signature TRY_CAST(<CHARACTER>, <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:945)
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:930)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5464)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1983)
	at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:329)
	at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:231)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6521)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6508)
	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1897)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1882)
	at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:276)
	at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:474)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:6171)
	at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:138)
	at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:275)
	at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:474)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:6171)
	at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:138)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1081)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:787)
	at org.apache.calcite.sql.test.AbstractSqlTester.parseAndValidate(AbstractSqlTester.java:160)
	at org.apache.calcite.sql.test.AbstractSqlTester.validateAndApply(AbstractSqlTester.java:256)
	at org.apache.calcite.sql.test.AbstractSqlTester.getColumnType(AbstractSqlTester.java:134)
	at org.apache.calcite.sql.test.AbstractSqlTester.check(AbstractSqlTester.java:234)
	at org.apache.calcite.test.SqlOperatorTest$TesterImpl.check(SqlOperatorTest.java:10091)
	at org.apache.calcite.sql.test.SqlTester.check(SqlTester.java:159)
	at org.apache.calcite.test.SqlOperatorFixtureImpl.lambda$checkScalarExact$3(SqlOperatorFixtureImpl.java:232)
	at org.apache.calcite.sql.test.AbstractSqlTester.forEachQuery(AbstractSqlTester.java:444)
	at org.apache.calcite.test.SqlOperatorFixtureImpl.checkScalarExact(SqlOperatorFixtureImpl.java:231)
	at org.apache.calcite.sql.test.SqlOperatorFixture.checkScalarExact(SqlOperatorFixture.java:275)
	at org.apache.calcite.test.CalciteSqlOperatorTest.testTryCastToInt(CalciteSqlOperatorTest.java:39)

I found the direct cause is that the SqlFunction#deriveType only lookup function with SqlSyntax.FUNCTION kind. The reason that functions with SqlSyntax.SPECIAL in SqlStdOperatorTable work well is these functions are normalized by `CaseSensitiveKey#normalize`, which will convert SqlSyntax.SPECIAL to SqlSyntax.FUNCTION. But functions are not normalized in SqlLibraryOperatorTableFactory.