[ 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)