SET PATH statement

Let's implement the SET PATH statement with the following grammar:

<set path statement>       ::=  SET  <SQL-path characteristic>

<SQL-path characteristic>  ::=  PATH  <value specification>

Example:

SET PATH 'test,sys';

The new statement will be available for all sql_mode values.

Function:

Set the SQL-path used to determine the subject routine of <routine invocation>s with unqualified <routine name>s in:

• <preparable statement>s that are prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement>
• in <direct SQL statement>s, that are invoked directly

The SQL-path remains the current SQL-path of the SQL-session until another SQL-path is successfully set.

In MariaDB, the SQL-path will also be used to lookup packages in package routine invocations, so this script:

SET PATH='sys';

SELECT UTL_ENCODE.BASE64_DECODE('data');

invokes SYS.UTL_ENCODE.BASE64_DECODE('data'), i.e. the function BASE64_DECODE() in the packge UTL_ENCODE in the database SYS (if such package and package routine exist).

Syntax and General Rules for <set path statement>:

• The declared type of the <value specification> shall be a character string type.
• Let S be <value specification>
• Let V be the character string that is the value of

  TRIM ( BOTH ' ' FROM S )

• If V does not conform to the Format and Syntax Rules of a <schema name list>, then an exception condition is raised: invalid schema name list specification.
• A <set path statement> that is executed between a <prepare statement> and an <execute statement> has no effect on the prepared statement.

The SQL standard grammar and Syntax rules for <schema name list>:

The value in V should conform the grammar and rules for <schema name list>:

Grammar:

<schema name list> ::= <schema name> [ { <comma> <schema name> }... ]

<schema name> ::= [ <catalog name> <period> ] <unqualified schema name>

<unqualified schema name> ::= <identifier>

<identifier> ::= <actual identifier>

<actual identifier> ::= <regular identifier>

                      | <delimited identifier>

                      | <Unicode delimited identifier>

Syntax rules:

• No two <schema name>s contained in <schema name list> shall be equivalent.

MariaDB implementation notes

• There will be a new global and session system variable @@path.
• The SET PATH statement will actually set the @@session.path variable. It should also be possible to assign a new value to @@path directly: SET @@path="sys";

• The value V should support delimited identifiers:

  SET PATH '`test`,`sys`';

• For easier processing, the value in V should support empty schema names, which should be ignored and removed when setting @@path:

  SET PATH ',,,test,,,sys,,,'; -- > 'test,sys'

• The value in V should support double-quote delimited identifiers when MODE_ANSI_QUOTES is set:

  SET PATH '"test","sys"';

  However, the value in @@path should be translated to use backticks, which works for all sql_mode values.

• It should be possible to list internal schemas MARIADB_SCHEMA and ORACLE SCHEMA:

  SET PATH 'ORACLE_SCHEMA,sys,MARIADB_SCHEMA';

To be decided - the current database alias

We'll possibly need a way to specify the current database in "SET PATH". We can use different notations to mean the current database:

• The operating system notation - the dot character ".":

  SET PATH '.,sys';

  SET PATH 'sys,.';

• The SQL keyword CURRENT_SCHEMA:

  SET PATH 'CURRENT_SCHEMA,sys';

  SET PATH 'sys,CURRENT_SCHEMA';

To be decided - the order of lookup when the current database is not listed in SET PATH

It's not clear yet what to do if the current database is not listed in SET PATH, neither by its explicit name, nor using an alias (see above).

• The current database could be looked up before all databases listed in SET PATH
• The current database could be looked up after all databases listed in SET PATH
• The current database could be not looked up at all.

I, Monty, would prefer the first version as this would eliminate the confusion if someone would set PATH to empty string, in which case no stored without a database qualifier function can be found.
Not looking first into the current database could also cause problems when calling a procedure that calls other procedure if PATH is not set.

To be decided - the default value for PATH

• "" (Emty string)
• "CURRENT_SCHEMA"

Just as a reference, PostgreSQL is using "$user,public" as default value and the variable is called search_path.
and it is applied for all objects, not only routine names.