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

Unreasonable SQL Error (1356) on select from view

    Details

      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.

        Attachments

          Activity

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              balta Tadas BalaiĊĦis
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: