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

            Activity

              People

              • Assignee:
                cvicentiu Vicențiu Ciorbaru
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                2 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated: