[
https://issues.apache.org/jira/browse/CALCITE-5813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750926#comment-17750926
]
Jiajun Xie commented on CALCITE-5813:
-------------------------------------
Hi, [~mbudiu].
The PR looks good, you also fixed SPACE, XML_TRANSFORM, and XML_EXTRACT
functions.
So Jira title(Type inference for REPEAT sql function is incorrect) is not
enough, please change it.
> Type inference for REPEAT sql function is incorrect
> ---------------------------------------------------
>
> Key: CALCITE-5813
> URL: https://issues.apache.org/jira/browse/CALCITE-5813
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.34.0, 1.35.0
> Reporter: Mihai Budiu
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> The result of the following SQL query (enabling the proper dialects for the
> REPEAT function):
> {code:sql}
> SELECT REPEAT('abc', 2)
> {code}
> is incorrectly computed by Calcite as 'abc' (no repetitions) if the constant
> folding optimization PROJECT_REDUCE_EXPRESSIONS is enabled.
> (I am not sure exactly how to modify the operator tables of the
> RelOptFixture, so I had to jump through some hoops to create a simple
> reproduction.)
> The plans before and after are as following:
> {code:java}
> LogicalProject(EXPR$0=[REPEAT('abc', 2)])
> LogicalValues(tuples=[[{ 0 }]])
> ---------------
> LogicalProject(EXPR$0=['abc':VARCHAR(3)])
> LogicalValues(tuples=[[{ 0 }]])
> {code}
> The root cause is the following:
> In the definition of the REPEAT SqlFunction:
> {code:java}
> @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL})
> public static final SqlFunction REPEAT =
> SqlBasicFunction.create("REPEAT",
> ReturnTypes.ARG0_NULLABLE_VARYING, /// <<< WRONG
> OperandTypes.STRING_INTEGER,
> SqlFunctionCategory.STRING);
> {code}
> the output type is the same as the first argument type. If the first argument
> type is VARCHAR(N), the output type is also VARCHAR(N). This causes the
> optimizer to first correctly compute the repeated string and then truncate
> result to the original length.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
