Expression index on a computed column is not usable in other attachments and gets corrupted ("missing entries") when the table has a computed column calling a procedure that reads the same table

[tomaszdubiel18]

This report was created with the support of AI. I hope it will help with the analysis of the root cause of the problem.
Related to #7945 — same root symptom, but it is not only a plan/optimizer problem: the index gets physically corrupted.

Environment

• Firebird 5.0.4.1812, Linux x64, SuperServer, Forced Writes ON, default firebird.conf
• The same symptoms are observed in production on Firebird 3.0.x and 5.0.x SuperServer (see Problem with using a computed index on a computed column #7945, reported on 3.0.11)

Description

Table T has:

• a computed column C_PROC that calls a stored procedure, and that procedure reads table T itself,
• a computed column C_KEY that depends on another computed column C_BIT,
• an expression index on C_KEY.

Observed behaviour:

1. Only the attachment that created (or rebuilt) the index can use it. In any new attachment the optimizer does not match the index (PLAN (T NATURAL)), and forcing it fails with index T_C_KEY cannot be used in the specified plan (this is Problem with using a computed index on a computed column #7945).
2. When a new attachment updates a row so that the value of C_KEY changes, and the old record version is then garbage collected, the index loses all entries for that row. gfix -v -full reports:

Error: Index 2 is corrupt {missing entries for record 0} in table T (128)

ALTER INDEX ... INACTIVE / ACTIVE fixes it, but the corruption returns as soon as rows are modified again from normal attachments.

Steps to reproduce

Adjust the database path (2 places) and run:

isql -user SYSDBA -password masterkey -i repro.sql
gfix -v -full -user SYSDBA -password masterkey localhost:/tmp/repro.fdb

repro.sql:

CREATE DATABASE 'localhost:/tmp/repro.fdb';

SET TERM ^ ;
CREATE PROCEDURE P_READ_T (ID INTEGER) RETURNS (RES INTEGER) AS BEGIN SUSPEND; END^
SET TERM ; ^

CREATE TABLE T (
    ID      INTEGER NOT NULL PRIMARY KEY,
    FLAGS   INTEGER DEFAULT 0 NOT NULL,
    C_PROC  COMPUTED BY ((SELECT RES FROM P_READ_T(T.ID))),
    C_BIT   COMPUTED BY (CAST(SIGN(BIN_AND(FLAGS, 64)) AS SMALLINT)),
    C_KEY   COMPUTED BY (CASE WHEN (C_BIT = 1) THEN ID ELSE -ID END)
);

SET TERM ^ ;
ALTER PROCEDURE P_READ_T (ID INTEGER) RETURNS (RES INTEGER) AS
BEGIN
  SELECT T.FLAGS FROM T WHERE T.ID = :ID INTO :RES;
  SUSPEND;
END^

EXECUTE BLOCK AS
  DECLARE I INTEGER = 1;
BEGIN
  WHILE (I <= 1000) DO
  BEGIN
    INSERT INTO T (ID, FLAGS) VALUES (:I, 0);
    I = I + 1;
  END
END^
SET TERM ; ^
COMMIT;

CREATE INDEX T_C_KEY ON T COMPUTED BY (C_KEY);
COMMIT;

SET PLAN ON;
-- Attachment that created the index: index is used
SELECT COUNT(*) FROM T WHERE C_KEY > 0;

-- New attachment
CONNECT 'localhost:/tmp/repro.fdb';
SET PLAN ON;
-- Expected: PLAN (T INDEX (T_C_KEY)); actual: PLAN (T NATURAL)
SELECT COUNT(*) FROM T WHERE C_KEY > 0;

UPDATE T SET FLAGS = BIN_OR(FLAGS, 64) WHERE ID <= 10;
COMMIT;
-- Garbage collection of the old record versions
SELECT COUNT(*) FROM T;
COMMIT;
EXIT;

Actual result

PLAN (T INDEX (T_C_KEY))      <- attachment that created the index
PLAN (T NATURAL)              <- new attachment

gfix -v -full:

Summary of validation errors
        Number of index page errors     : 1

firebird.log:

Error: Index 2 is corrupt {missing entries for record 0} in table T (128)

Expected result

• The index is usable in every attachment.
• Validation reports no errors.

Additional observations

All results below were checked with gfix -v -full. Each combination was run with the first statement in the new attachment being either the UPDATE or a SELECT ... WHERE C_KEY < 0, and with garbage collection done either by the same attachment or by gfix -sweep. All variants gave the same result.

Change made in a new attachment	P_READ_T reads T	P_READ_T does not read T (RES = :ID;)
bit 64: 0 → 1 (C_KEY changes)	corrupted	OK
bit 64: 1 → 0 (C_KEY changes)	corrupted	OK
other bits only (C_KEY unchanged)	OK	OK

• The problem does not depend on the procedure reading computed columns. Reading only ordinary columns of T is enough.
• Every row whose C_KEY changes in a normal attachment loses its index entries (100 of 100 in one test). Validation reports only the first one.
• Online validation (fbsvcmgr ... action_validate) did not report the corruption in some runs where gfix -v -full did.
• The real-world case is an ERP table with about 20 computed columns calling selectable procedures that read the same table. There, a flag bit is set or cleared by ordinary UPDATEs from the application, and the index has to be rebuilt periodically.

[tomaszdubiel18]

Hello. Can someone look at this bug?
Best regards.

[AlexPeshkoff]

On 9/24/26 20:30, Tomasz Dubiel wrote: Table |T| has: * a computed column |C_PROC| that calls a stored procedure, and that procedure reads table |T| itself, * a computed column |C_KEY| that depends on another computed column |C_BIT|, * an expression index on |C_KEY|. Observed behaviour: 1. Only the attachment that created (or rebuilt) the index can use it. In any new attachment the optimizer does not match the index (|PLAN (T NATURAL)|), and forcing it fails with |index T_C_KEY cannot be used in the specified plan| (this is #7945 <#7945>). 2. When a new attachment updates a row so that the value of |C_KEY| changes, and the old record version is then garbage collected, the index loses *all* entries for that row.

Is presence of computed column C_PROC that calls a stored procedure, and that procedure reads table T itself, essential for a bug to take place?

[tomaszdubiel18]

"Yes, it is essential — but so is its position in the table. I ran additional variants on
5.0.4.1812 (same script, checked with gfix -v -full). "Plan" is the plan in the new
attachment:

#	Variant	Plan	Validation
1	Original script	NATURAL	corrupted
2	No C_PROC column at all	INDEX	OK
3	C_PROC present, procedure does not read T (RES = :ID)	INDEX	OK
4	C_PROC is a subquery on T instead of a procedure call	INDEX	OK
5	C_KEY computed from base columns only (no C_BIT inside)	NATURAL	corrupted
6	Index expression on base columns only (no computed column)	INDEX	OK
7	Index on C_BIT (C_PROC defined before C_BIT)	NATURAL	corrupted
8	C_PROC defined as the last column	INDEX	OK
9	Column order C_BIT, C_PROC, C_KEY; index on C_KEY	NATURAL	corrupted
10	Column order C_BIT, C_PROC, C_KEY; index on C_BIT	INDEX	OK

So the bug needs all of the following:

• a computed column calling a stored procedure that reads the same table,
• that column defined BEFORE the computed column used by the index,
• an expression index referencing a computed column (one level is enough; nesting
  is not required).

A subquery on the same table instead of a procedure call does not trigger it, and neither
does an index expression built from base columns only."

[sim1984]

What if you create the T_C_KEY index before ALTER PROCEDURE P_READ_T?

P.S. It is better to avoid creating computed columns that perform a SELECT against anything.

[tomaszdubiel18]

"The order does not matter — it still reproduces. Results on 5.0.4.1812 (same statements,
only reordered; "Plan" is the plan in the new attachment, result checked with gfix -v -full):

Order of statements in the first session	Plan	Validation
ALTER PROCEDURE -> insert data -> CREATE INDEX (original script)	NATURAL	corrupted
CREATE INDEX (empty table) -> ALTER PROCEDURE -> insert data	NATURAL	corrupted
insert data -> CREATE INDEX -> ALTER PROCEDURE	NATURAL	corrupted
insert data -> CREATE INDEX -> ALTER PROCEDURE -> ALTER INDEX INACTIVE/ACTIVE	NATURAL	corrupted
CREATE INDEX -> ALTER PROCEDURE -> insert data, then the second part	NATURAL	corrupted
of the script run in a separate isql process instead of CONNECT

So what matters is the state of the metadata when a new attachment loads the table:
the procedure called from C_PROC already reads T. When the procedure does not read T
at that moment (RES = :ID), the index is used and validation is clean (variant 3 from
my previous comment)."