Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4837

FLOOR and CEIL of DATE/TIMESTAMP return wrong results for DECADE, CENTURY and MILLENNIUM

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • None
    • None
    • avatica, core
    • None

    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      |
      +--------------+-------------+--------------+--------------+------------------+-----------------+
      
      

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Unassigned Unassigned
            Sergey Nuyanzin Sergey Nuyanzin

            Dates

              Created:
              Updated:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0h
                0h
                Logged:
                Time Spent - 40m
                40m

                Slack

                  Issue deployment