[MDEV-25007] Clarify the use of unqualified function names in view definitions Created: 2021-02-27  Updated: 2021-03-01

Status: Open
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Both MySQL manual and MariaDB KB describe (in identical words) how unqualified table and view names are resolved within CREATE VIEW statement:

Unqualified table or view names in the SELECT statement are interpreted with respect to the default database. A view can refer to tables or views in other databases by qualifying the table or view name with the proper database name.

However, neither explains what happens with unqualified function names, and it is not obvious, as it apparently works differently from table and view names:

10.5 16388f39

 
MariaDB [test]> create function db1.f() returns int return 1;
 
Query OK, 0 rows affected (0.019 sec)
 
 
 
MariaDB [test]> create function db2.f() returns int return 2;
 
Query OK, 0 rows affected (0.058 sec)
 
 
 
MariaDB [test]> use db1;
 
Database changed
 
MariaDB [db1]> create view db2.v as select f();
 
Query OK, 0 rows affected (0.053 sec)
 
 
 
MariaDB [db1]> select * from db2.v;
 
+------+
 
| f()  |
 
+------+
 
|    2 |
 
+------+
 
1 row in set (0.001 sec)

So, it actually uses the function from the same schema where the view resides, not from the default schema. It works the same way in all MariaDB and MySQL versions, so I hope it's intentional and just needs to be documented.

 Comments   
Comment by Sergei Golubchik [ 2021-03-01 ]

I don't think it's intentional. Compare with columns

create database db1;
 
create database db2;
 
create function db1.f() returns int return 1;
 
create function db2.f() returns int return 2;
 
create table db1.t as select 1 as a;
 
create table db2.t as select 2 as a;
 
use db1;
 
create view db2.v as select f(), a from t;
 
select * from db2.v;
 
show create view db2.v;
 
drop database db1;
 
drop database db2;

This, as you wrote, returns

select * from db2.v;
 
f()     a
 
2       1

And the view definition is

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2`.`v` AS select `f`() AS `f()`,`db1`.`t`.`a` AS `a` from `db1`.`t`

I think it's an oversight that tables stored are db-qualified, while functions aren't. It's inconsistent.

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