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)
