Open
Description
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
Metadata
Metadata
Assignees
Labels
No labels