[
https://issues.apache.org/jira/browse/CALCITE-7762?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Darpan Lunagariya (e6data) updated CALCITE-7762:
------------------------------------------------
Description:
{{DateRangeRules}} converts predicates containing {{EXTRACT}}, {{FLOOR}}, and
{{CEIL}} into timestamp ranges.
It currently maps {{TimeUnitRange.HOUR}} to {{Calendar.HOUR}}, which uses a
12-hour clock. SQL timestamp operations use 24-hour semantics. Consequently:
* {{FLOOR}} and {{CEIL}} can produce incorrect boundaries for afternoon
timestamp literals.
* Rewriting a bounded {{EXTRACT(HOUR)}} predicate can enter an infinite loop.
* Rewriting lower time units can generate an excessive number of ranges.
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. Wrong 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.
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. Current behavior
No transformed 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.
h2. Excessive range expansion
DateRangeRules enumerates each matching occurrence of a lower time unit within
the previously established timestamp range.
For example:
{code:sql}
SELECT EXTRACT(SECOND FROM hiredate) AS s
FROM sales.emp_b
WHERE EXTRACT(YEAR FROM hiredate) = 2010
AND EXTRACT(SECOND FROM hiredate) = 15;
{code}
The year predicate establishes the finite range from {{2010-01-01}} to
{{2011-01-01}}. Rewriting {{EXTRACT(SECOND) = 15}} then attempts to create one
one-second range for every minute of that year: {{365 * 24 * 60 = 525,600}}
ranges.
Similar expansion occurs for:
* {{EXTRACT(HOUR)}} over one year: 365 or 366 ranges.
* {{EXTRACT(MINUTE)}} over one year: 8,760 or 8,784 ranges.
* {{EXTRACT(SECOND)}} over one year: 525,600 or 527,040 ranges.
Even though the timestamp domain is finite, creating such a large expression
can consume excessive planning time and memory.
h2. Expected behavior
* Map {{TimeUnitRange.HOUR}} to {{Calendar.HOUR_OF_DAY}}.
* Accept only hour values from {{0}} through {{23}}.
* Add a budget for the number of ranges generated by an {{EXTRACT}} rewrite.
* Stop the lower-unit rewrite as soon as the expansion budget would be exceeded.
* Preserve the original lower-unit {{EXTRACT}} predicate when expansion is
abandoned.
* Do not emit a partially generated or semantically incomplete range expression.
* Previously completed safe rewrites, such as the bounding {{EXTRACT(YEAR)}}
predicate, may be retained.
was:
{{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}}.
> 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}} converts predicates containing {{EXTRACT}}, {{FLOOR}}, and
> {{CEIL}} into timestamp ranges.
> It currently maps {{TimeUnitRange.HOUR}} to {{Calendar.HOUR}}, which uses a
> 12-hour clock. SQL timestamp operations use 24-hour semantics. Consequently:
> * {{FLOOR}} and {{CEIL}} can produce incorrect boundaries for afternoon
> timestamp literals.
> * Rewriting a bounded {{EXTRACT(HOUR)}} predicate can enter an infinite loop.
> * Rewriting lower time units can generate an excessive number of ranges.
> 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. Wrong 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.
> 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. Current behavior
> No transformed 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.
> h2. Excessive range expansion
> DateRangeRules enumerates each matching occurrence of a lower time unit
> within the previously established timestamp range.
> For example:
> {code:sql}
> SELECT EXTRACT(SECOND FROM hiredate) AS s
> FROM sales.emp_b
> WHERE EXTRACT(YEAR FROM hiredate) = 2010
> AND EXTRACT(SECOND FROM hiredate) = 15;
> {code}
> The year predicate establishes the finite range from {{2010-01-01}} to
> {{2011-01-01}}. Rewriting {{EXTRACT(SECOND) = 15}} then attempts to create
> one one-second range for every minute of that year: {{365 * 24 * 60 =
> 525,600}} ranges.
> Similar expansion occurs for:
> * {{EXTRACT(HOUR)}} over one year: 365 or 366 ranges.
> * {{EXTRACT(MINUTE)}} over one year: 8,760 or 8,784 ranges.
> * {{EXTRACT(SECOND)}} over one year: 525,600 or 527,040 ranges.
> Even though the timestamp domain is finite, creating such a large expression
> can consume excessive planning time and memory.
> h2. Expected behavior
> * Map {{TimeUnitRange.HOUR}} to {{Calendar.HOUR_OF_DAY}}.
> * Accept only hour values from {{0}} through {{23}}.
> * Add a budget for the number of ranges generated by an {{EXTRACT}} rewrite.
> * Stop the lower-unit rewrite as soon as the expansion budget would be
> exceeded.
> * Preserve the original lower-unit {{EXTRACT}} predicate when expansion is
> abandoned.
> * Do not emit a partially generated or semantically incomplete range
> expression.
> * Previously completed safe rewrites, such as the bounding {{EXTRACT(YEAR)}}
> predicate, may be retained.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
