[MDEV-15424] Unreasonable SQL Error (1356) on select from view Created: 2018-02-27  Updated: 2018-12-19  Resolved: 2018-12-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.3.4, 10.2, 10.3
Fix Version/s: 10.4.1, 10.2.20, 10.3.12

Type: Bug Priority: Major
Reporter: Tadas BalaiĊĦis Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None


 Description   

DB version: 10.3.4-MariaDB
Error
SQL Error (1356): View 'test.v_ar' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

on select from view:

select * from v_ar where point_id = 1 and ml_id = 8871 and da = date'2018-01-01'

I haven't found any rights issues. Then I started to simplify view query and locate the object that causes the error.
At the end I found that error disappears after changing "ifnull" to "coalesce" in

,ifnull(
 
Max(
 
CASE WHEN r.act_id IS NULL AND r.orig=1 AND r.dstatus IS NULL AND NOT pl_t IS null THEN 1 END
 
) over (PARTITION BY r.point_id,r.ml_id,r.da,r.orig,/*r.pl_t*/CASE WHEN r.per_id IS NULL THEN /*r.pl_t*/CASE WHEN r.act_id IS NULL OR (r.act_enabled = 1 and not r.act_type_id=-1) THEN r.pl_t end ELSE r.bt END)
 
,0) has_pl_custom

into 

,coalesce(
 
Max(
 
CASE WHEN r.act_id IS NULL AND r.orig=1 AND r.dstatus IS NULL AND NOT pl_t IS null THEN 1 END
 
) over (PARTITION BY r.point_id,r.ml_id,r.da,r.orig,/*r.pl_t*/CASE WHEN r.per_id IS NULL THEN /*r.pl_t*/CASE WHEN r.act_id IS NULL OR (r.act_enabled = 1 and not r.act_type_id=-1) THEN r.pl_t end ELSE r.bt END)
 
,0) has_pl_custom

It's funny for me, because coalesce and ifnull are almost the same function. And there's no connection with lack of rights.

 Comments   
Comment by Alice Sherepa [ 2018-09-03 ]

Thanks for the report!
Reproducible with 10.2, 10.3, (using window functions)

create table t1 (id int, n1 int, d1 date);
 
insert into t1 values (1,1,'2010-01-01'), (2,1,'2010-01-05'), (3,2,'2010-01-17'), (4,4,'2010-01-01');
 
 
 
create view v1 as SELECT coalesce(max(n1) over (partition by n1),'aaa') FROM t1;
 
select * from v1;
 
 
 
create or replace view v1 as SELECT ifnull(max(n1) over (partition by n1),'aaa') FROM t1;
 
select * from v1;


query 'select * from v1' failed: 1356: 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 Varun Gupta (Inactive) [ 2018-12-13 ]

The view's frm

 
 
query=select ifnull(`max(n1) over (partition by n1)`,\'aaa\') AS `ifnull(max(n1) over (partition by n1),\'aaa\')` from `test`.`t1`
 
 
 
view_body_utf8=select ifnull(`max(n1) over (partition by n1)`,\'aaa\') AS `ifnull(max(n1) over (partition by n1),\'aaa\')` from `test`.`t1`

So the issue here is the max(n1) over (partition by n1) is treated as an identifier. This is not
correct , it should be parsed as a window function.

Comment by Varun Gupta (Inactive) [ 2018-12-14 ]

Patch
http://lists.askmonty.org/pipermail/commits/2018-December/013206.html

Comment by Oleksandr Byelkin [ 2018-12-18 ]

OK to push!

Generated at Thu Feb 08 08:21:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.