[MDEV-19328] sql_mode=ORACLE: Package function in VIEW Created: 2019-04-25  Updated: 2022-03-25  Resolved: 2022-03-25

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.4.4, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.9.0, 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3

Type: Bug Priority: Major
Reporter: Yuriy Kuleshov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: Compatibility

Issue Links:
Relates
relates to MDEV-19804 sql_mode=ORACLE: call procedure in pa... Closed
relates to MDEV-28166 sql_mode=ORACLE: fully qualified pack... Closed

 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.

 Comments   
Comment by Alice Sherepa [ 2019-04-25 ]

Thanks for the report! Repeatable with 10.3,10.4

MariaDB [test]> SELECT test1.f_test() FROM DUAL;
 
+----------------+
 
| test1.f_test() |
 
+----------------+
 
|              1 |
 
+----------------+
 
1 row in set (0.005 sec)
 
 
 
MariaDB [test]> create view v1 as SELECT test1.f_test() FROM DUAL;
 
Query OK, 0 rows affected (0.009 sec)
 
 
 
MariaDB [test]> select * from v1;
 
ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
 
Error (Code 1305): FUNCTION test.test1.f_test does not exist
 
Error (Code 1356): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them


query=select `test`.`test1.f_test`() AS `test1.f_test()`

Comment by Alessandro [ 2022-03-22 ]

"bug"
"major"
"confirmed"
"unresolved"

Last update 2021...

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