Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.4, 10.2, 10.3
-
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.