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

sql_mode=ORACLE: Package function in VIEW

    XMLWordPrintable

    Details

      Description

      There is an error when executing query with view, which uses package function.

      delimiter $$
      set sql_mode=ORACLE;
      $$
      CREATE or replace PACKAGE test1
      AS
      	function f_test return number;
      END test1;
      $$
      CREATE or replace PACKAGE body test1
      AS
        function f_test return number
        is
        begin
      	return 1;  
        end;
      END test1;
      $$
      create or replace view v_test
      as
      select 1 where 1=test1.f_test();
      $$
      select * from v_test;
      $$
      

      Produces error:

      SQL Error [1356] [HY000]: (conn:20) View 'test.v_test' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
      

      And there is no error when using standalone function, not package function:

      delimiter $$
      create or replace function f_test return number
      is
      begin
        return 1;  
      end;
      $$
      create or replace view v_test
      as
      select 1 where 1=f_test();
      $$
      select * from v_test;
      $$
      

      produces no error.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              ward3r Yuriy Kuleshov
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.