Regression: "no alias or table named "<T>" at this scope level" on attempt to specify execution plan for query to a view that contains <T> as an alias to another data source

[pavel-zotov]

Please consider following script (i've encounter with problem during re-implementing test for core-0203):

set bail on;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
set echo on;

recreate table test(x int, y int);
commit;
 
create index test_x_asc on test(x);
commit;
 
recreate view v_test3 as select * from test t where x = 0 plan (t index(test_x_asc));
recreate view v_test4 as select * from v_test3;
commit;
 
set bail off;
set planonly;
select * from v_test4 v4
;
 
select * from v_test4 v4
PLAN (V4 V_TEST3 T INDEX (TEST_X_ASC)) 
;

On 3.x ... 4.x and up to 6.0.0.800-1f226fc (last snapshot before SQL schemas were introduced) its output will be:

...
select * from v_test4 v4
;

PLAN (V4 V_TEST3 T INDEX (TEST_X_ASC))

select * from v_test4 v4
PLAN (V4 V_TEST3 T INDEX (TEST_X_ASC))
;

PLAN (V4 V_TEST3 T INDEX (TEST_X_ASC))

(i.e. no errors).

Since 6.0.0.834 message about "unknown alias or table" appeared:

select * from v_test4 v4
;

PLAN ("V4" "PUBLIC"."V_TEST3" "T" INDEX ("PUBLIC"."TEST_X_ASC"))

select * from v_test4 v4
PLAN (V4 V_TEST3 T INDEX (TEST_X_ASC))
;
Statement failed, SQLSTATE = 42S02
Dynamic SQL Error
-SQL error code = -104
-Invalid command
-there is no alias or table named "T" at this scope level

Attempt to fix it by enclosing each object name in double quotes and adding schema prefix ("PUBLIC".) in the execution plan does not help:

set planonly;
select * from v_test4 v4;
 
select * from v_test4 v4
PLAN ("V4" "PUBLIC"."V_TEST3" "T" INDEX ("PUBLIC"."TEST_X_ASC")) 
;
 
select * from v_test4 v4
PLAN ("V4" "PUBLIC"."V_TEST3" "PUBLIC"."T" INDEX ("PUBLIC"."TEST_X_ASC")) 
;

Output:

set bail off;
set planonly;
select * from v_test4 v4;
 
PLAN ("V4" "PUBLIC"."V_TEST3" "T" INDEX ("PUBLIC"."TEST_X_ASC"))
 
select * from v_test4 v4
PLAN ("V4" "PUBLIC"."V_TEST3" "T" INDEX ("PUBLIC"."TEST_X_ASC"))
;
Statement failed, SQLSTATE = 42S02
Dynamic SQL Error
-SQL error code = -104
-Invalid command
-there is no alias or table named "T" at this scope level
 
select * from v_test4 v4
PLAN ("V4" "PUBLIC"."V_TEST3" "PUBLIC"."T" INDEX ("PUBLIC"."TEST_X_ASC"))
;
Statement failed, SQLSTATE = 42S02
Dynamic SQL Error
-SQL error code = -104
-Invalid command
-there is no alias or table named "PUBLIC"."T" at this scope level

[mrotteveel]

Know that prefixing T with PUBLIC doesn't make any sense, as it's an alias within the view, not a table or view in a schema.

[pavel-zotov]

Know that prefixing T with PUBLIC doesn't make any sense

I did guess about it :-) But tried both cases.

[pavel-zotov]

... prefixing T with PUBLIC doesn't make any sense, as it's an alias within the view, not a table or view in a schema.

C:\temp>isql /:employee
Database: /:employee, User: SYSDBA
SQL> recreate table foo(id int primary key);
SQL> set planonly;
SQL> select 1 from rdb$database;

PLAN ("SYSTEM"."RDB$DATABASE" NATURAL)
SQL>
SQL> select 1 from foo where id = 1;

PLAN ("PUBLIC"."FOO" INDEX ("PUBLIC"."RDB$PRIMARY27"))
SQL>
SQL> select 1 from rdb$database as r;

PLAN ("R" NATURAL) ----------- [ 1 ]
SQL>
SQL> select 1 from foo as f where id = 1;

PLAN ("F" INDEX ("PUBLIC"."RDB$PRIMARY27")) --------------- [ 2 ]
SQL>

I can't understand this... Both [1] and [2] refer to aliases of REAL TABLE.

What is the rule that used in FB ? What SQL standard states for this ?

[pavel-zotov]

Both [1] and [2] refer to aliases of REAL TABLE.

I mean: why there are no prefixes with schema name ("PUBLIC".) near "R" and "F" ?

[mrotteveel]

There are no prefixes with PUBLIC for those, because those are aliases, not tables. The alias replaces (or "hides") the real name of the table, that is R replaces "SYSTEM"."RDB$DATABASE", and F replaces "PUBLIC"."FOO".

This has nothing to do with the SQL standard, as the plan syntax is a non-standard extension that is specific to Firebird. Though even in the standard, aliases are not bound to a schema, only objects can be bound to a schema, and an alias is not an object, it is a temporary name (label).

[pavel-zotov]

There are no prefixes with PUBLIC for those, because those are aliases, not tables. The alias replaces (or "hides") the real name of the table

Good news for those who didn't need the schemas: it is enough always to use aliases and "PUBLIC". will never appear (the plan will not be cluttered with unnecessary prefix).

Bad for those who will deal with objects from different schemas and use queries (with aliases, of course). How such developer would recognize to what schema belong a table that "hides" under some alias ?

[asfernandes]

Bad for those who will deal with objects from different schemas and use queries (with aliases, of course). How such developer would recognize to what schema belong a table that "hides" under some alias ?

As the alias always hidden the table, why one would need to know the schema?

[pavel-zotov]

As the alias always hidden the table

From whom one need to hide name of tables involved in a query ? I can't imagine how someone who sees the plan can not see the appropriate query. Table aliases are used (mostly) for brevity. Also, there is good rule of thumb always to specify alias for <some_table> even in a trivial query like select t.* from <some_table> because later this query can be "extended" with other data sources and their column names may conflict.

[pavel-zotov]

PS. Wasn't it better to show data source affiliations to schemas in separate lines after an execution plan (when it is shown in legacy form) ?

[mrotteveel]

Bad for those who will deal with objects from different schemas and use queries (with aliases, of course). How such developer would recognize to what schema belong a table that "hides" under some alias ?

They should be aware of the structure of their database, and know which tables they are using in their queries. If they really want to to know the origin of their columns, they should use set sqlda_display on; or set explain;

[mrotteveel]

PS. Wasn't it better to show data source affiliations to schemas in separate lines after an execution plan (when it is shown in legacy form) ?

The legacy plan syntax is as it is, because it is also the syntax of the PLAN clause of DML statements.