[
https://issues.apache.org/jira/browse/CALCITE-5713?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17759137#comment-17759137
]
Julian Hyde commented on CALCITE-5713:
--------------------------------------
I see that your PR does include a test case. I didn't read the test case
because its name - misleadingly - implied that it was a unit test for
{{SqlBasicCall}}.
This bug does what I keep asking people not to do. It starts from the perceived
solution and works backwards.
Let's start from the problem, which is that we can't plan a particular query.
If the summary was "Query with nested call to QUARTER function throws error
'cannot translate call'", then we would have a better chance of finding a
solution.
Let's try to find a minimal test case. I notice that there are 3 levels of
nested function calls; is there a test case with fewer levels of nesting? I
notice that the functions all use JDBC 'fn' syntax; does the problem reproduce
if you use regular function syntax?
My hypothesis about the root cause is this. Function expansion works either
top-down or bottom-up, and it does so in a way that throws away the expansion
that happens on the arguments to a function. The fix might be to switch from
top-down to bottom-up (or vice versa; I haven't read the code), or to make
multiple passes until expansion reaches a fixed point.
> SqlBasicCall's Deep Copy Logic Raises Rule Optimization Exception
> -----------------------------------------------------------------
>
> Key: CALCITE-5713
> URL: https://issues.apache.org/jira/browse/CALCITE-5713
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.35.0
> Reporter: Guoliang Sun
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> h3. Background
> In the fix for
> [CALCITE-4795,|https://issues.apache.org/jira/browse/CALCITE-4795] the
> operands in the SqlBasicCall class were changed from SqlNode[] to
> ImmutableNullableList. Also change the variable name to operandList, and add
> a new set method to modify the elements in the collection.
> h3. Exception
> Take the following SQL as an example
> {code:sql}
> SELECT { fn TRUNCATE(
> { fn QUARTER(
> { fn TIMESTAMPADD(
> SQL_TSI_HOUR,
> 1,
> { ts '1900-01-01 00:00:00' }
> ) }
> ) },
> 0
> ) }
> FROM "TDVT"."CALCS" "CALCS"
> GROUP BY 1.1000000000000001
> {code}
> An exception is thrown when the optimization execution reaches the
> CoreRules.PROJECT_REDUCE_EXPRESSIONS rule: {color:#DE350B}cannot translate
> call QUARTER($t4){color}
> h3. RootCause
> The SqlBasicCall#set method uses a deep copy in order to modify immutable
> collections and returns a new operandList object when modified.
> If the SQL contains an operation that requires a rewrite call, such as
> QUARTER in the above SQL, the deep copy logic will cause the operator and
> operandList to be different and eventually raise an exception.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
