[
https://issues.apache.org/jira/browse/CALCITE-5887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750592#comment-17750592
]
Guoliang Sun edited comment on CALCITE-5887 at 8/3/23 6:42 AM:
---------------------------------------------------------------
The problem was introduced by this issue:
https://issues.apache.org/jira/browse/CALCITE-2571
was (Author: JIRAUSER298470):
The problem was introduced by this issue
> 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
> Priority: Major
>
> 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)
