Darpan Lunagariya (e6data) created CALCITE-7762:
---------------------------------------------------
Summary: DateRangeRules produces incorrect FLOOR and CEIL ranges
and may hang for EXTRACT(HOUR)
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)
{{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)
