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

Clarify the use of unqualified function names in view definitions

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2, 10.3, 10.4, 10.5, 10.6
    • 10.6
    • Views
    • 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.

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.