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

Suspicious limitation for RANGE frames

    XMLWordPrintable

Details

    Description

      Note: results below are from 10.2 348ccb6f038a6c1.

      MariaDB [test]> create table t (i int, j int);
      Query OK, 0 rows affected (0.36 sec)
       
      MariaDB [test]> select i, nth_value(i,2) over (partition by i order by i, j range unbounded preceding) from t;
      ERROR 4019 (HY000): RANGE-type frame requires ORDER BY clause with single sort key
      

      I'm not sure whether it's supposed to be so, or it's a leftover from some previous limitations. The KB page is still nothing more than a stub, it's not helpful at all.

      For ROWS, it works

      MariaDB [test]> select i, nth_value(i,2) over (partition by i order by i, j rows unbounded preceding) from t;
      Empty set (0.00 sec)
      

      In PostgreSQL 9.4, it works (of course, PostgreSQL has its own limitations for RANGE):

      postgres=# create table t (i int, j int);
      CREATE TABLE
      postgres=# select i, nth_value(i,2) over (partition by i order by i, j range unbounded preceding) from t;
       i | nth_value 
      ---+-----------
      (0 rows)
      

      Attachments

        Issue Links

          Activity

            People

              cvicentiu Vicențiu Ciorbaru
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.