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

Window functions: datetime arithmetic for RANGE-type frames

    XMLWordPrintable

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

              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.