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

["Sergey Nuyanzin (Jira)" <ji...@apache.org>]

Sergey Nuyanzin created CALCITE-4837:
----------------------------------------

             Summary: FLOOR and CEIL of DATE/TIMESTAMP return wrong results for DECADE, CENTURY and MILLENNIUM
                 Key: CALCITE-4837
                 URL: https://issues.apache.org/jira/browse/CALCITE-4837
             Project: Calcite
          Issue Type: Bug
          Components: avatica, core
            Reporter: Sergey Nuyanzin


The query to reproduce
{code:sql}
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'), (timestamp '2021-10-07 10:27:35')) t;
{code}
it produces output
{noformat}
+--------------+-------------+--------------+--------------+------------------+-----------------+
| 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      |
+--------------+-------------+--------------+--------------+------------------+-----------------+

{noformat}

expected
{noformat}
+--------------+-------------+--------------+--------------+------------------+-----------------+
| FLOOR_DECADE | CEIL_DECADE | FLOOR_CENTURY | CEIL_CENTURY | FLOOR_MILLENNIUM | CEIL_MILLENNIUM |
+--------------+-------------+--------------+--------------+------------------+-----------------+
| 2020-01-01   | 2030-01-01  | 2000-01-01   | 2100-01-01   | 2000-01-01       | 3000-01-01      |
+--------------+-------------+--------------+--------------+------------------+-----------------+

{noformat}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)