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

Syntax Error when selecting from a view with compound window function

Details

    Description

      The following query works:

      select a + min(a) over (partition by a) from t1 where a = 1;
      		 
       
      		 
      a + min(a) over (partition by a)
      		 
      2
      		 
      2
      		 
      2

      However this one fails with a strange parser error:

      create view win_view
      		 
      as (select a, a + min(a) over (partition by a) from t1 where a = 1);
      		 
      select * from win_view;


      query 'select * from win_view' failed: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '???) AS `a + min(a) over (partition by a)` from `test`.`t1` where (`test`.`t1`.`' at line 1

      Note that the following views however work:

      create view some_view
      		 
      as (select a, a + a from t1 where a = 1);
      		 
      select * from some_view;
      		 
      a	a + a
      		 
      1	2
      		 
      1	2
      		 
      1	2
      		 
      create view win_view
      		 
      as (select a, min(a) over (partition by a) from t1 where a = 1);
      		 
      select * from win_view;
      		 
      a	min(a) over (partition by a)
      		 
      1	1
      		 
      1	1
      		 
      1	1

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          cvicentiu Vicențiu Ciorbaru added a comment -

          cc: psergey, igor, sanja

          cvicentiu Vicențiu Ciorbaru added a comment - cc: psergey , igor , sanja
          psergei Sergei Petrunia added a comment -

          Even a basic example of a view with a window function causes a name resolution error:

          create table t1 (a int);
          		 
          insert into t1 values (1),(2),(3);
          		 
          create view v1 as select a, row_number() over (order by a) as row_num from t1;


          MariaDB [test]> select * from v1;
          		 
          ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

          psergei Sergei Petrunia added a comment - Even a basic example of a view with a window function causes a name resolution error: create table t1 (a int); insert into t1 values (1),(2),(3); create view v1 as select a, row_number() over (order by a) as row_num from t1; MariaDB [test]> select * from v1; ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
          psergei Sergei Petrunia added a comment -

          I was trying to check for a possible issue we've discussed on the optimizer call:

          > A view that uses window functions must not be executed with ALGORITHM=MERGE

          This needs to be checked when the name resolution issue is fixed.

          psergei Sergei Petrunia added a comment - I was trying to check for a possible issue we've discussed on the optimizer call: > A view that uses window functions must not be executed with ALGORITHM=MERGE This needs to be checked when the name resolution issue is fixed.
          elenst Elena Stepanova added a comment -

          psergey, cvicentiu, should it still be open? There is a commit in the main tree:

          commit 903f34c7a99d15ca1b861a7dd4848ebed9891c44
          		 
          Author: Igor Babaev <igor@askmonty.org>
          		 
          Date:   Thu Sep 29 01:15:00 2016 -0700
          		 
           
          		 
              Fixed bug mdev-10868.
          		 
              There was no implementation of the virtual method print()
          		 
              for the Item_window_func class. As a result for a view
          		 
              containing window function an invalid view definition could
          		 
              be written in the frm file. When a query that refers to
          		 
              this view was executed a syntax error was reported.

          elenst Elena Stepanova added a comment - psergey , cvicentiu , should it still be open? There is a commit in the main tree: commit 903f34c7a99d15ca1b861a7dd4848ebed9891c44 Author: Igor Babaev <igor@askmonty.org> Date: Thu Sep 29 01:15:00 2016 -0700   Fixed bug mdev-10868. There was no implementation of the virtual method print() for the Item_window_func class. As a result for a view containing window function an invalid view definition could be written in the frm file. When a query that refers to this view was executed a syntax error was reported.
          psergei Sergei Petrunia added a comment -

          > I was trying to check for a possible issue we've discussed on the optimizer call:
          > > A view that uses window functions must not be executed with ALGORITHM=MERGE

          > This needs to be checked when the name resolution issue is fixed.

          Now checked, and it's fine: VIEWs with window functions do not use merge algorithm.

          psergei Sergei Petrunia added a comment - > I was trying to check for a possible issue we've discussed on the optimizer call: > > A view that uses window functions must not be executed with ALGORITHM=MERGE > This needs to be checked when the name resolution issue is fixed. Now checked, and it's fine: VIEWs with window functions do not use merge algorithm.
          psergei Sergei Petrunia added a comment -

          The fix for this bug was pushed into 10.2.3.

          psergei Sergei Petrunia added a comment - The fix for this bug was pushed into 10.2.3.

          People

            psergei Sergei Petrunia
            cvicentiu Vicențiu Ciorbaru
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.