Constants and equivalence

[Adriano dos Santos Fernandes]

Hi!

The current design of constants makes some cases problematic.

I do think it should not work as now in this case.

IMO, at compilation constant reference node should be switched by the
constant value.


SQL> recreate package pkg as begin constant c1 integer = 1; end!
SQL> recreate table t (n integer)!
SQL> create index t1 on t computed by (n + 1)!
SQL> set plan on!

SQL> select * from t where n + 1 = 1!
PLAN ("PUBLIC"."T" INDEX ("PUBLIC"."T1"))


-- Cannot use the index
SQL> select * from t where n + pkg.c1 = 1!
PLAN ("PUBLIC"."T" NATURAL)


SQL> drop index t1!
SQL> create index t2 on t computed by (n + pkg.c1)!


-- Cannot use the index
SQL> select * from t where n + 1 = 1!
PLAN ("PUBLIC"."T" NATURAL)

SQL> select * from t where n + pkg.c1 = 1!
PLAN ("PUBLIC"."T" INDEX ("PUBLIC"."T2"))


Adriano

[Artyom Abakumov]

13:22:53 UTC+3, Adriano dos Santos Fernandes:

Hi!

The current design of constants makes some cases problematic.

I do think it should not work as now in this case.

IMO, at compilation constant reference node should be switched by the
constant value.

 

As far as I understand, the only problem is the CMP_post_access checks used in PackageReferenceNode::pass1. I moved them to PackageReferenceNode::parse, and it worked fine with a simple select statement. But it might not work in more complex scenarios.

I'm also concerned about the possible hard mixing of DDL and DML, but it seems the value should remain unchanged.

set autoddl on;
set autoterm on;

recreate function f RETURNS int as begin return 0; end;
commit;
execute block
RETURNS (a int)
as
begin
execute statement 'alter function f RETURNS int as begin return 4; end';
a = f();
suspend;
execute statement 'alter function f RETURNS int as begin return 8; end;';

a = f();
suspend;
end;

           A
============
           0
           0 

recreate package pkg as begin constant c1 integer = 0; end;
commit;
execute block
RETURNS (a int)
as
begin
execute statement 'alter package pkg as begin constant c1 integer = 4; end;';
a = pkg.c1;
suspend;
execute statement 'alter package pkg as begin constant c1 integer = 8; end;';
a = pkg.c1;
suspend;
end;
           A
============
           0
           0 

 

[Alex Peshkoff]

I'm also concerned about the possible hard mixing of DDL and DML, but it seems the value should remain unchanged.

Yes - it should. If you use execute block to run mix of DDL in execute statement and related DML than you execute all DML in same request. Once started request will continue to use versions of all objects that existed at startup moment. Therefore - all zeros.

On contrary if you use new request it will have access to latest object version when started in DDL transaction:

set autoddl off;
--set sqlda_display on;
set list on;
set echo on;

set term ^;

recreate function f RETURNS int as begin return 0; end^
select f() from rdb$database^

F                               0



commit^

alter function f RETURNS int as begin return 4; end^
select f() from rdb$database^

F                               4



alter function f RETURNS char(6) as begin return 'qwerty'; end^
select f() from rdb$database^

F                               qwerty



rollback^

select f() from rdb$database^

F                               0


And last but not least - mixing DDL with DML should not cause problems now.