[
https://issues.apache.org/jira/browse/CALCITE-7762?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Darpan Lunagariya (e6data) updated CALCITE-7762:
------------------------------------------------
Summary: DateRangeRules may produce incorrect ranges, hang, or excessively
expand sub-day predicates (was: DateRangeRules produces incorrect FLOOR and
CEIL ranges and may hang for EXTRACT(HOUR))
> DateRangeRules may produce incorrect ranges, hang, or excessively expand
> sub-day predicates
> -------------------------------------------------------------------------------------------
>
> Key: CALCITE-7762
> URL: https://issues.apache.org/jira/browse/CALCITE-7762
> Project: Calcite
> Issue Type: Bug
> Reporter: Darpan Lunagariya (e6data)
> Assignee: Darpan Lunagariya (e6data)
> Priority: Major
>
> {{DateRangeRules}} maps {{TimeUnitRange.HOUR}} to {{Calendar.HOUR}}, which
> uses a 12-hour clock. SQL timestamp operations use 24-hour semantics, so
> afternoon timestamp values can produce incorrect range boundaries. Rewriting
> a bounded {{EXTRACT(HOUR)}} predicate can also enter an infinite loop.
> h2. Incorrect FLOOR rewrite
> h3. Query
> {code:sql}
> SELECT FLOOR(hiredate TO DAY) AS d
> FROM sales.emp_b
> WHERE FLOOR(hiredate TO DAY)
> < TIMESTAMP '2010-02-04 13:00:00';
> {code}
> h3. Plan before DateRangeRules
> {code}
> LogicalProject(D=[FLOOR($4, FLAG(DAY))])
> LogicalFilter(condition=[<(FLOOR($4, FLAG(DAY)), 2010-02-04 13:00:00)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
> {code}
> h3. Current plan after DateRangeRules
> {code}
> LogicalProject(D=[FLOOR($4, FLAG(DAY))])
> LogicalFilter(condition=[<($4, 2010-02-05 12:00:00)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
> {code}
> The generated boundary is incorrectly shifted by 12 hours.
> h3. Expected plan
> {code}
> LogicalProject(D=[FLOOR($4, FLAG(DAY))])
> LogicalFilter(condition=[<($4, 2010-02-05 00:00:00)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
> {code}
> The same problem affects {{CEIL}} because it uses the same calendar-unit
> mapping.
> h2. EXTRACT(HOUR) planner hang
> h3. Query
> {code:sql}
> SELECT EXTRACT(HOUR FROM hiredate) AS h
> FROM sales.emp_b
> WHERE EXTRACT(YEAR FROM hiredate) = 2010
> AND EXTRACT(HOUR FROM hiredate) = 13;
> {code}
> h3. Plan before DateRangeRules
> {code}
> LogicalProject(H=[EXTRACT(FLAG(HOUR), $4)])
> LogicalFilter(condition=[AND(=(EXTRACT(FLAG(YEAR), $4), 2010),
> =(EXTRACT(FLAG(HOUR), $4), 13))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
> {code}
> h3. Plan after DateRangeRules
> No plan is produced because optimization does not terminate.
> When {{Calendar.HOUR}} is set to {{13}}, the calendar normalizes the
> timestamp to 13:00, but reading {{Calendar.HOUR}} returns {{1}}. The
> range-generation loop observes that {{1 < 13}} and retries indefinitely.
> {{TimeUnitRange.HOUR}} should use {{Calendar.HOUR_OF_DAY}}, and valid hour
> values should be restricted to {{0}} through {{23}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
