[CALCITE-5813] Type inference for sql functions REPEAT, SPACE, XML_TRANSFORM, and XML_EXTRACT is incorrect

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.34.0, 1.35.0
• Fix Version/s: 1.36.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Mihai Budiu
• Created: 01/Jul/23 00:52
• Updated: 10/Nov/23 11:34
• Resolved: 12/Aug/23 08:11

Description

The result of the following SQL query (enabling the proper dialects for the REPEAT function):

SELECT REPEAT('abc', 2)

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:

LogicalProject(EXPR$0=[REPEAT('abc', 2)])
  LogicalValues(tuples=[[{ 0 }]])

---------------

LogicalProject(EXPR$0=['abc':VARCHAR(3)])
  LogicalValues(tuples=[[{ 0 }]])

The root cause is the following:

In the definition of the REPEAT SqlFunction:

  @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL})
  public static final SqlFunction REPEAT =
      SqlBasicFunction.create("REPEAT",
          ReturnTypes.ARG0_NULLABLE_VARYING,  /// <<< WRONG
          OperandTypes.STRING_INTEGER,
          SqlFunctionCategory.STRING);

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.

Issue Links

• relates to: CALCITE-5989 Type inference for RPAD and LPAD functions (BIGQUERY) is incorrect
• links to: GitHub Pull Request #3289