[MDEV-10868] Syntax Error when selecting from a view with compound window function Created: 2016-09-22  Updated: 2017-01-19  Resolved: 2017-01-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions, Parser
Affects Version/s: 10.2
Fix Version/s: 10.2.3

Type: Bug Priority: Major
Reporter: Vicențiu Ciorbaru Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None


 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



 Comments   
Comment by Vicențiu Ciorbaru [ 2016-09-22 ]

cc: psergey, igor, sanja

Comment by Sergei Petrunia [ 2016-09-28 ]

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

Comment by Sergei Petrunia [ 2016-09-28 ]

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.

Comment by Elena Stepanova [ 2016-12-16 ]

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.

Comment by Sergei Petrunia [ 2017-01-19 ]

> 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.

Comment by Sergei Petrunia [ 2017-01-19 ]

The fix for this bug was pushed into 10.2.3.

Generated at Thu Feb 08 07:45:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.