[
https://issues.apache.org/jira/browse/CALCITE-5864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17746626#comment-17746626
]
Jonathan A Sternberg commented on CALCITE-5864:
-----------------------------------------------
(I have a PR for this and I'll update it with some tests since I just learned
how/where to put the tests)
Here's an example SQL that returns the wrong value:
{code:java}
select
cast(empno as Integer) * (INTERVAL '1' WEEK)
from emp{code}
The problem is only observed when you convert it to a {{{}RexLiteral{}}}. This
is the resulting plan after converting things with {{{}SqlToRelConverter{}}}.
{code:java}
expected: <
LogicalProject(EXPR$0=[*($0, 604800000:INTERVAL WEEK)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> but was: <
LogicalProject(EXPR$0=[*($0, 3600000:INTERVAL WEEK)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
604800000 is the number of milliseconds in a week. 3600000 is the number of
milliseconds in an hour. If you replace week with quarter, you get this:
{code:java}
expected: <
LogicalProject(EXPR$0=[*($0, 3:INTERVAL QUARTER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> but was: <
LogicalProject(EXPR$0=[*($0, 1:INTERVAL QUARTER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
This one doesn't look wrong, but the result is wrong because the interval
literals output in their base time rather than in the unit that's reported. So
{{INTERVAL WEEK}} will report its time in milliseconds and {{INTERVAL QUARTER}}
will report its time in months. This doesn't get represented in the output
though.
You can see this by looking at similar unit tests. The HOUR TO MINUTE test has
its output in milliseconds and YEAR TO MONTH has its output in months.
> getValueAs reports the wrong number of milliseconds for QUARTER and WEEK
> ------------------------------------------------------------------------
>
> Key: CALCITE-5864
> URL: https://issues.apache.org/jira/browse/CALCITE-5864
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Jonathan A Sternberg
> Priority: Major
> Labels: pull-request-available
>
> The change that introduced WEEK and QUARTER processing here did not correctly
> implement the `evaluateIntervalLiteralAsX` functions for these two time units.
> For both of them, it used the `fillIntervalValueArray` with the week
> parameter being used for months and the quarter parameter being used for
> months with no modifications to the underlying values.
> For weeks, this results in the field being misused as the hour parameter so 2
> weeks becomes 2 hours. For quarters, this results in the field being misused
> as months so 2 quarters becomes 2 months.
> I believe the proper way to implement these is to perform the modification so
> weeks gets translated to 7 days and quarters gets translated to 3 months.
> This seems to only affect the string versions such as `INTERVAL '2' WEEK`.
> The integer variants don't seem to use the literal pathway so they aren't
> affected.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
