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-issue-importer opened this issue Jul 2, 2020 · 7 comments
Closed

Comments

@firebird-issue-importer
Copy link

@firebird-issue-importer firebird-issue-importer commented Jul 2, 2020

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 2, 2020

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 3, 2020

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 5, 2020

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 19, 2020

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 21, 2020

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 21, 2020

Modified by: @pavel-zotov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 29, 2021

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
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants