Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9727

Window functions: datetime arithmetic for RANGE-type frames

Details

    Description

      The standard specifies that RANGE-type frame bounds support date[time] arithmetics. That is, one can write queries like this:

      SUM(number) OVER (PARTITION BY col1 
                        ORDER BY date_col
      		 
                        RANGE BETWEEN INTERVAL 1 MONTH PRECEDING 
                        AND INTERVAL 1 MONTH FOLLOWING)

      MariaDB only supports numeric types there. The reason for the limitation is that interval is not a first-class datatype in MySQL codebase.

      • Interval is not parsed to an Item object
      • One cannot use addition/subtraction to add an interval to a date.

      • MDEV-9676 uses Item_func_plus/Item_func_minus, which allow range bounds to use any numeric datatype. This doesn't include datetime, though.

      The building blocks for date+interval arithmetics are already there:

      SELECT DATE_ADD(date_col, INTERVAL 10 DAY) 
      SELECT DATE_SUB(date_col, INTERVAL 5 YEAR)

      That is,

      • the parser is able to parse interval values.
      • there is code to do addition/subtraction.

      This task is to support interval arithmetics in window functions.

      Attachments

        Issue Links

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MCOL-644 MariaDB 10.2 WF Add RANGE BETWEEN with date values

          • Major - Major loss of function.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-6115 window functions as in the SQL standard

          • Major - Major loss of function.
          • Closed

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-12987 complete window function support for columnstore parity

          • Major - Major loss of function.
          • Open
          relates to

          Task - A task that needs to be done. MDEV-9676 RANGE-type frames for window functions

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24937 window functions over unbounded RANGE fails on not numeric type

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            israelwebdev Zvi Landsman added a comment -

            resolving time to unix timestamp/seconds works:

            SUM(number) OVER (PARTITION BY col1 
                                               order by unix_timestamp(date_col) 
                                               range between 2592000 preceding 
                                               and 2592000 following)

            israelwebdev Zvi Landsman added a comment - resolving time to unix timestamp/seconds works: SUM(number) OVER (PARTITION BY col1 order by unix_timestamp(date_col) range between 2592000 preceding and 2592000 following)

            People

              cvicentiu Vicențiu Ciorbaru
              psergei Sergei Petrunia
              Votes:
              5 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.