NAME_RESOLUTION_ORDER

Using this option, you can enable or disable resolving of non-fully qualified names in user queries.

Resolving of non-fully qualified names applies to tables, views, and stored procedures. Hereinafter, these entities are referred to as resources or group symbols.

Enabling Non-Fully Qualified Names Resolution

There are two ways to enable the non-fully qualified names resolution, depending on whether you need to enable it for the current session or globally: by using the SET statement or by calling the SYSADMIN.setDefaultOptionValue() stored procedure.

Using SET Statement

In this case, the option becomes valid only for the current session.

The example below demonstrates enabling the names resolution with the SET statement:

SET $NAME_RESOLUTION_ORDER "test_tables_pg, test_tables";;
SET $NAME_RESOLUTION_ORDER "*";;
SET $NAME_RESOLUTION_ORDER "-";;

Using Stored Procedure

In this case, the option is set globally.

The example below demonstrates enabling the names resolution by calling the stored procedure:

SQL

CALL SYSADMIN.setDefaultOptionValue("opt" => 'NAME_RESOLUTION_ORDER', "val" => '-');;
CALL SYSADMIN.setDefaultOptionValue("opt" => 'NAME_RESOLUTION_ORDER', "val" => '*');;
CALL SYSADMIN.setDefaultOptionValue("opt" => 'NAME_RESOLUTION_ORDER', "val" => 'schema1, schema2');;
CALL SYSADMIN.setDefaultOptionValue("opt" => 'NAME_RESOLUTION_ORDER', "val" => 'schema1, schema2, *');

Options

The table below summarizes the values allowed for this option:

To view the full table, click the expand button in its top right corner

Option value	Description
-	Fully qualified names are always required; default value
*	Look in all schemas as in the previous implementation. The main difference is that now the resolution algorithm takes into account also the user rights on the resource and two different error messages ("Group is ambiguous" or "User has no rights") can be shown depending on the number of discovered resources accessible to the user. Some examples will be shown in the next section.
<comma separated list of schemas>	Look in these schemas exactly in this order. If the resource is defined in more than one schema provided in the list, the name will be resolved using the first schema in the order containing the resource; if the resource is not defined in these schemas, the system will throw a "Table/View/Proc/ not found..." exception. More examples will be shown in the following section.
<comma separated list of schema> + "*" symbol	Look in these schemas as for the previous option. If the resource is defined in none of these schemas but it is defined multiple times in other schemas, the system will throw a "Group specified is ambiguous..." error.

Usage Examples

The following table shows some examples (this table does not cover all possible combinations) of the system behaviour when running a query like SELECT * FROM <non-fully qualified table name>:

To view the full table, click the expand button in its top right corner

NAME_RESOLUTION_ORDER	sch1	Rights on sch1	sch2	Rights on sch2	schX	Rights on schX	schY	Rights on schY	Result
-									The query always fails if the table name is not fully qualified
"sch1, sch2"									sch2
"sch1, sch2"									"No permissions..." error
"sch1, sch2"									sch1
"sch1, sch2"									sch2
"sch1, sch2"									"Table not found" error
"sch1, sch2"									"Table not found" error
"sch1, sch2, *"									sch1
"sch1, sch2, *"									sch1
"sch1, sch2, *"									sch2
"sch1, sch2, *"									sch1
"sch1, sch2, *"									schX
"sch1, sch2, *"									"Group specified is ambiguous..." error
"sch1, sch2, *"									"Table not found" error
*									"Table not found" error
*									"Group specified is ambiguous..." error
*									"No permissions..." error
*									sch1

Legend:

 – the schema has no tables with this name

 – the schema contains a table with this name

 – for the resolution, it does not matter if the schema contains such a table or not

For the rights column:

 – no permission

 – user has permissions on the schema/table

 – does not matter