Guoliang Sun created CALCITE-5887:
-------------------------------------
Summary: Trim function can’t work well with
SqlDelegatingConformance
Key: CALCITE-5887
URL: https://issues.apache.org/jira/browse/CALCITE-5887
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.35.0
Reporter: Guoliang Sun
In Calcite, there are test cases that override this function, e.g.:
SqlFunctionsTest#testTrim.
But the use case ignores the fact that the function is optimized in SQL with
rules, which I'll illustrate with an example below.
Take the following SQL as an example of the expected result: *calcite*
{code:java}
select trim(both 'io' from 'iocalciteio'){code}
I wanted to use Calcite's SQLConformance by way of a custom extension, so I
inherited SqlDelegatingConformance, overrode the allowExtendedTrim method, and
set it when SqlParser was initialized.
The SQL is then optimized by a series of parsing transformations, and at this
point it comes to {*}CoreRules.PROJECT_REDUCE_EXPRESSIONS{*}, which calls
RexExecutorImpl to compute the RexNode expression, reducing the constants by
generating code for optimization purposes. In the
*{color:#172b4d}RexExecutorImpl#compile{color}* method, you can see that the
SqlConformance is written to Default, which will cause the custom
*SqlDelegatingConformance* set earlier to not take effect, see the code below.
{code:java}
private static String compile(RexBuilder rexBuilder, List<RexNode> constExps,
RexToLixTranslator.InputGetter getter, RelDataType rowType) {
final RexProgramBuilder programBuilder =
new RexProgramBuilder(rowType, rexBuilder);
for (RexNode node : constExps) {
programBuilder.addProject(
node, "c" + programBuilder.getProjectList().size());
}
final RelDataTypeFactory typeFactory = rexBuilder.getTypeFactory();
final JavaTypeFactory javaTypeFactory = typeFactory instanceof JavaTypeFactory
? (JavaTypeFactory) typeFactory
: new JavaTypeFactoryImpl(typeFactory.getTypeSystem());
final BlockBuilder blockBuilder = new BlockBuilder();
final ParameterExpression root0_ =
Expressions.parameter(Object.class, "root0");
final ParameterExpression root_ = DataContext.ROOT;
blockBuilder.add(
Expressions.declare(
Modifier.FINAL, root_,
Expressions.convert_(root0_, DataContext.class)));
final SqlConformance conformance = SqlConformanceEnum.DEFAULT;
final RexProgram program = programBuilder.getProgram();
final List<Expression> expressions =
RexToLixTranslator.translateProjects(program, javaTypeFactory,
conformance, blockBuilder, null, null, root_, getter, null);
blockBuilder.add(
Expressions.return_(null,
Expressions.newArrayInit(Object[].class, expressions)));
final MethodDeclaration methodDecl =
Expressions.methodDecl(Modifier.PUBLIC, Object[].class,
BuiltInMethod.FUNCTION1_APPLY.method.getName(),
ImmutableList.of(root0_), blockBuilder.toBlock());
String code = Expressions.toString(methodDecl);
if (CalciteSystemProperty.DEBUG.value()) {
Util.debugCode(System.out, code);
}
return code;
} {code}
When you get {*}SqlConformanceEnum.DEFAULT{*}, you get a MethodCallExpression
in the *TrimImplementor#implementSafe* method that doesn't allow Trim to do
multiple character operations on SQL, so Calcite throws an exception in the
SqlFunctions#trim method.
*{color:#de350b}Trim error: trim character must be exactly 1 character{color}*
Coincidentally, this exception is caught by the try catch in the
{color:#172b4d}*RexExecutable#reduce*{color} method, which returns the initial
unoptimized expression, with no exception output, and after the final execution
is complete, you can only see that the result is not as expected.
By the way, I don't think it's reasonable to output the exception message, if
the optimization fails, at least it should be logged for troubleshooting.
Regarding solution ideas, I can think of two ways at the moment and would like
to know what the community thinks, or if the community thinks this is a problem?
* Change SQLConformance to global
* Change CalciteConnectionConfigImpl to global
This would allow you to get the SQLConformance information that has been set in
the configuration elsewhere, there are many places in Calcite where DEFAULT may
be written because you can't get the configuration information.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
