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

Unreasonable SQL Error (1356) on select from view

    XMLWordPrintable

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

            varun Varun Gupta (Inactive)
            balta Tadas Balaišis
            Votes:
            0 Vote for this issue
            Watchers:
            6 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.