[
https://issues.apache.org/jira/browse/CALCITE-6645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17893655#comment-17893655
]
xiong duan commented on CALCITE-6645:
-------------------------------------
I noticed you use `allowNiladicParentheses` to control the function's
SqlSyntax. I think it is strange. Maybe we can pass the SqlSyntax.FUNCTION_ID
to `SqlUserDefinedFunction`. Then use `allowNiladicParentheses` to control
whether to use parentheses.
> Support user-defined function without parentheses when db dialect's
> allowNiladicParentheses property is false
> -------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6645
> URL: https://issues.apache.org/jira/browse/CALCITE-6645
> Project: Calcite
> Issue Type: Improvement
> Affects Versions: 1.38.0
> Reporter: Zhengqiang Duan
> Assignee: Zhengqiang Duan
> Priority: Major
> Labels: pull-request-available
>
> Hi community, When I implement a user defined function without parameters for
> Oracle database, I get a `Column 'SYS_DATE' not found in any table` exception
> when I execute the `SELECT SYS_DATE FROM DUAL` statement. I did some research
> and found that the default SqlSyntax used by the SqlUserDefinedFunction is
> SqlSyntax.FUNCTION, and its corresponding function syntax is SYS_DATE()
> instead of SYS_DATE. SqlSyntax.FUNCTION will cause an exception during SQL
> validation. The implementation logic of makeNullaryCall in SqlValidatorImpl
> is as follows, since makeNullaryCall checks if operator.getSyntax() ==
> SqlSyntax.FUNCTION_ID, user defined function cannot be converted to
> SqlBasicCall.
> {code:java}
> @Override
> public @Nullable SqlCall makeNullaryCall(SqlIdentifier id) {
> if (id.names.size() == 1 && !id.isComponentQuoted(0)) {
> final List<SqlOperator> list = new ArrayList<>();
> opTab.lookupOperatorOverloads(id, null, SqlSyntax.FUNCTION, list,
> catalogReader.nameMatcher());
> for (SqlOperator operator : list) {
> if (operator.getSyntax() == SqlSyntax.FUNCTION_ID) {
> // Even though this looks like an identifier, it is a
> // actually a call to a function. Construct a fake
> // call to this function, so we can use the regular
> // operator validation.
> return new SqlBasicCall(operator, ImmutableList.of(),
> id.getParserPosition(), null).withExpanded(true);
> }
> }
> }
> return null;
> }{code}
> In order to support user defined functions without parameters, we need to
> modify the logic of the CalciteCatalogReader#toOp method. If the current user
> defined function has no parameters and allowNiladicParentheses is set to
> false, SqlSyntax.FUNCTION_ID should be passed in for SqlUserDefinedFunction.
> {code:java}
> SqlSyntax syntax = function.getParameters().isEmpty()
> && !config.conformance().allowNiladicParentheses()
> ? SqlSyntax.FUNCTION_ID
> : SqlSyntax.FUNCTION;
> return new SqlUserDefinedFunction(name, kind, returnTypeInference,
> operandTypeInference, operandMetadata, function, syntax);{code}
> I will submit a PR to support this usage. if anyone can provide some
> suggestions I would appreciate it.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
