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

Range-type window frame supports only numeric datatype

Details

    Description

      create table t1 (a int, b varchar(5));
      		 
      insert into t1 values (1,'a'), (2, 'b'), (3, 'c');
      		 
       
      		 
      select sum(a) over (order by b range between unbounded preceding and current row) from t1;


      MariaDB [test]> select sum(a) over (order by b range between unbounded preceding and current row) from t1;
      		 
      ERROR 4021 (HY000): Numeric datatype is required for RANGE-type frame

      Mysql/Postgres return:

      mysql> select sum(a) over (order by b range between unbounded preceding and current row) from t1;
      		 
      +----------------------------------------------------------------------------+
      		 
      | sum(a) over (order by b range between unbounded preceding and current row) |
      		 
      +----------------------------------------------------------------------------+
      		 
      |                                                                          1 |
      		 
      |                                                                          3 |
      		 
      |                                                                          6 |
      		 
      +----------------------------------------------------------------------------+
      		 
      3 rows in set (0.00 sec)

      Also range-type frame always expects ORDER BY clause:

      MariaDB [test]> select sum(a) over (range between unbounded preceding and current row) from t1;
      		 
      ERROR 4019 (HY000): RANGE-type frame requires ORDER BY clause with single sort key

      Mysql 8.0.15: 

      mysql> select sum(a) over (range between unbounded preceding and current row) from t1;
      		 
      +-----------------------------------------------------------------+
      		 
      | sum(a) over (range between unbounded preceding and current row) |
      		 
      +-----------------------------------------------------------------+
      		 
      |                                                               6 |
      		 
      |                                                               6 |
      		 
      |                                                               6 |
      		 
      +-----------------------------------------------------------------+
      		 
      3 rows in set (0.00 sec)

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-9740 Window functions: catch invalid window frame specs

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment - 

            create table t1 (a int, b int);
            		 
            insert into t1 values (1,1), (1, 2), (1, 3), (2,1), (2,2);
            		 
             
            		 
            select sum(a) over (order by a,b range between unbounded preceding and current row) from t1;

            RANGE-type frame requires ORDER BY clause with single sort key, while in Mysql/Postgres this works:

            MariaDB [test]> select sum(a) over (order by a,b range between unbounded preceding and current row) from t1;
            		 
            ERROR 4019 (HY000): RANGE-type frame requires ORDER BY clause with single sort key
            		 
            ------------------------------------------------------------------------------------
            		 
             
            		 
            mysql> select sum(a) over (order by a,b range between unbounded preceding and current row) from t1;
            		 
            +------------------------------------------------------------------------------+
            		 
            | sum(a) over (order by a,b range between unbounded preceding and current row) |
            		 
            +------------------------------------------------------------------------------+
            		 
            |                                                                            1 |
            		 
            |                                                                            2 |
            		 
            |                                                                            3 |
            		 
            |                                                                            5 |
            		 
            |                                                                            7 |
            		 
            +------------------------------------------------------------------------------+
            		 
            5 rows in set (0.00 sec)

            alice Alice Sherepa added a comment - create table t1 (a int , b int ); insert into t1 values (1,1), (1, 2), (1, 3), (2,1), (2,2);   select sum (a) over ( order by a,b range between unbounded preceding and current row) from t1; RANGE-type frame requires ORDER BY clause with single sort key, while in Mysql/Postgres this works: MariaDB [test]> select sum(a) over (order by a,b range between unbounded preceding and current row) from t1; ERROR 4019 (HY000): RANGE-type frame requires ORDER BY clause with single sort key ------------------------------------------------------------------------------------   mysql> select sum(a) over (order by a,b range between unbounded preceding and current row) from t1; +------------------------------------------------------------------------------+ | sum(a) over (order by a,b range between unbounded preceding and current row) | +------------------------------------------------------------------------------+ | 1 | | 2 | | 3 | | 5 | | 7 | +------------------------------------------------------------------------------+ 5 rows in set (0.00 sec)
            john-medeiros John Medeiros added a comment -

            Same behavior founded. Version: 10.3.21-MariaDB.

            john-medeiros John Medeiros added a comment - Same behavior founded. Version: 10.3.21-MariaDB.
            danblack Daniel Black added a comment -

            psergei, can I please get a review on https://github.com/MariaDB/server/pull/3334

            danblack Daniel Black added a comment - psergei , can I please get a review on https://github.com/MariaDB/server/pull/3334
            danblack Daniel Black added a comment -

            Not sure if it's compliant with standard or not but this change adds the following like alice put on the first comment:

            "select a, b, sum(b) over (order by a desc, b range between unbounded preceding and current row) from t1" as a valid syntax.

            It seems to work properly, and MySQL 8.0 and Postgres support that syntax as well (results of the query are equal as well).

            danblack Daniel Black added a comment - Not sure if it's compliant with standard or not but this change adds the following like alice put on the first comment: "select a, b, sum(b) over (order by a desc, b range between unbounded preceding and current row) from t1" as a valid syntax. It seems to work properly, and MySQL 8.0 and Postgres support that syntax as well (results of the query are equal as well).
            psergei Sergei Petrunia added a comment -

            Added my input with question for danblack

            psergei Sergei Petrunia added a comment - Added my input with question for danblack
            danblack Daniel Black added a comment -

            Thanks psergei

            danblack Daniel Black added a comment - Thanks psergei

            People

              danblack Daniel Black
              alice Alice Sherepa
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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