Issue Details (XML | Word | Printable)

Key: CORE-6351
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Vlad Khorsun
Votes: 0
Watchers: 4

If you were logged in you would be able to see more operations.
Firebird Core

Computed field could be wrongly evaluated as NULL

Created: 02/Jul/20 11:53 AM   Updated: 20/Aug/20 09:16 PM
Component/s: Engine
Affects Version/s: 3.0.6
Fix Version/s: 3.0.7, 4.0 RC 1

QA Status: Done successfully

 Description  « Hide
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')

set term ^;
create or alter procedure p_t1 (id int)
  returns (val varchar(32))
  val = 'unknown';

  select f2 from t1 where id = :id
    into :val;

set term ;^

alter table t1
  alter f1 computed by ((select val from p_t1(id)));

alter table t1
  alter f2 computed by ('id = ' || id);

insert into t1 values (1);

2. Test OK

select val from p_t1(1);

id = 1

select * from t1;

          ID F1 F2
============ ================================ ================
           1 id = 1 id = 1


3. Test FAIL

select * from t1;

          ID F1 F2
============ ================================ ================
           1 <null> id = 1

select val from p_t1(1);



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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 02/Jul/20 01:34 PM
I think a computed column that uses a stored procedure that references the table itself is a bit of an anti-pattern.

Vlad Khorsun added a comment - 02/Jul/20 09:12 PM

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