Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Computed field could be wrongly evaluated as NULL [CORE6351] #6592

Closed
firebird-automations opened this issue Jul 2, 2020 · 7 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @hvlad

Relate to CORE2032

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

Commits: 3861b68 61b89e3 c14a42f 5f336a1

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

I think a computed column that uses a stored procedure that references the table itself is a bit of an anti-pattern.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Mark,

sure, it is very bad practice. But it is not forbidden and should work as expected or throw an error at DDL execution time.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

Fix Version: 3.0.7 [ 10940 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE2032 [ CORE2032 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants