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
Operations

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')
);
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


 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
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.

Vlad Khorsun made changes - 04/Aug/20 10:38 PM
Field Original Value New Value
Assignee Vlad Khorsun [ hvlad ]
Vlad Khorsun made changes - 19/Aug/20 11:48 AM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 4.0 RC 1 [ 10930 ]
Fix Version/s 3.0.7 [ 10940 ]
Resolution Fixed [ 1 ]
Pavel Zotov made changes - 20/Aug/20 09:16 PM
Status Resolved [ 5 ] Resolved [ 5 ]
QA Status No test Done successfully
Pavel Zotov made changes - 20/Aug/20 09:16 PM
Status Resolved [ 5 ] Closed [ 6 ]