[
https://issues.apache.org/jira/browse/CALCITE-4837?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sergey Nuyanzin updated CALCITE-4837:
-------------------------------------
Description:
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')) 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}
was:
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}
> 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
> Priority: Major
>
> 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')) 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)
