
If you were logged in you would be able to see more operations.
|
|
|
QA Status: |
Done successfully
|
The sample is based on much more complex case sent to me by user privately.
1. Prepare metatada
create table t1
(
id int,
f1 computed by ('abcd'),
f2 computed by ('xyz')
);
commit;
set term ^;
create or alter procedure p_t1 (id int)
returns (val varchar(32))
as
begin
val = 'unknown';
select f2 from t1 where id = :id
into :val;
suspend;
end^
set term ;^
commit;
alter table t1
alter f1 computed by ((select val from p_t1(id)));
alter table t1
alter f2 computed by ('id = ' || id);
commit;
insert into t1 values (1);
commit;
exit;
2. Test OK
select val from p_t1(1);
VAL
================================
id = 1
select * from t1;
ID F1 F2
============ ================================ ================
1 id = 1 id = 1
exit;
3. Test FAIL
select * from t1;
ID F1 F2
============ ================================ ================
1 <null> id = 1
select val from p_t1(1);
VAL
================================
<null>
exit;
The issue happens when:
- table contains at least two caclulated fields
- first calc field contains expression with PSQL object which used second calc field
- the table metadata is loaded into metadata cache before metadata of PSQL object mentioned above
|
Description
|
The sample is based on much more complex case sent to me by user privately.
1. Prepare metatada
create table t1
(
id int,
f1 computed by ('abcd'),
f2 computed by ('xyz')
);
commit;
set term ^;
create or alter procedure p_t1 (id int)
returns (val varchar(32))
as
begin
val = 'unknown';
select f2 from t1 where id = :id
into :val;
suspend;
end^
set term ;^
commit;
alter table t1
alter f1 computed by ((select val from p_t1(id)));
alter table t1
alter f2 computed by ('id = ' || id);
commit;
insert into t1 values (1);
commit;
exit;
2. Test OK
select val from p_t1(1);
VAL
================================
id = 1
select * from t1;
ID F1 F2
============ ================================ ================
1 id = 1 id = 1
exit;
3. Test FAIL
select * from t1;
ID F1 F2
============ ================================ ================
1 <null> id = 1
select val from p_t1(1);
VAL
================================
<null>
exit;
The issue happens when:
- table contains at least two caclulated fields
- first calc field contains expression with PSQL object which used second calc field
- the table metadata is loaded into metadata cache before metadata of PSQL object mentioned above
|
Show » |
|