[CALCITE-4837] FLOOR and CEIL of DATE/TIMESTAMP return wrong results for DECADE, CENTURY and MILLENNIUM

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: avatica, core
• Assignee: Unassigned
• Reporter: Sergey Nuyanzin
• Created: 07/Oct/21 09:01
• Updated: 07/Nov/22 03:08

Description

The query to reproduce

select floor(t to decade) as floor_decade,      
        ceil(t to decade) as ceil_decade,
        floor(t to century) as floor_century,
        ceil(t to century) as ceil_century,
        floor(t to millennium) as floor_millennium,
        ceil(t to millennium) as ceil_millennium
 from (values(date '2021-10-07')) t;

it produces output

+--------------+-------------+--------------+--------------+------------------+-----------------+
| FLOOR_DECADE | CEIL_DECADE | FLOOR_CENTURY | CEIL_CENTURY | FLOOR_MILLENNIUM | CEIL_MILLENNIUM |
+--------------+-------------+--------------+--------------+------------------+-----------------+
| 2021-08-01   | 2021-11-29  | 2019-04-14   | 2022-07-27   | 2002-11-09       | 2035-09-17      |
+--------------+-------------+--------------+--------------+------------------+-----------------+

expected

+--------------+-------------+--------------+--------------+------------------+-----------------+
| FLOOR_DECADE | CEIL_DECADE | FLOOR_CENTURY | CEIL_CENTURY | FLOOR_MILLENNIUM | CEIL_MILLENNIUM |
+--------------+-------------+--------------+--------------+------------------+-----------------+
| 2020-01-01   | 2030-01-01  | 2001-01-01   | 2101-01-01   | 2001-01-01       | 3001-01-01      |
+--------------+-------------+--------------+--------------+------------------+-----------------+

Issue Links

• is related to: CALCITE-1613 Implement EXTRACT for time unit DOW, DOY; and fix CENTURY
• relates to: CALCITE-5369 In Avatica DateTimeUtils, add support for FLOOR and CEIL to ISOYEAR