Repository navigation
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 #9165
Description
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_PROCthat calls a stored procedure, and that procedure reads tableTitself, - a computed column
C_KEYthat depends on another computed columnC_BIT, - an expression index on
C_KEY.
Observed behaviour:
- 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 withindex T_C_KEY cannot be used in the specified plan(this is Problem with using a computed index on a computed column #7945). - When a new attachment updates a row so that the value of
C_KEYchanges, and the old record version is then garbage collected, the index loses all entries for that row.gfix -v -fullreports:
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
Tis enough. - Every row whose
C_KEYchanges 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 wheregfix -v -fulldid. - 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.
Activity
tomaszdubiel18 commentedon Sep 29, 2026
Hello. Can someone look at this bug?
Best regards.
AlexPeshkoff commentedon Sep 29, 2026
tomaszdubiel18 commentedon Sep 29, 2026
"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:
So the bug needs all of the following:
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 commentedon Sep 29, 2026
What if you create the
T_C_KEYindex beforeALTER PROCEDURE P_READ_T?P.S. It is better to avoid creating computed columns that perform a
SELECTagainst anything.tomaszdubiel18 commentedon Sep 29, 2026
"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):
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)."
tomaszdubiel18 commentedon Oct 1, 2026
Hello.
I ordered tests on FB 6.0 for comparison purposes. If it's not helpful for you, you can ignore it, but maybe it will help you with the analysis.
"Checked on the current master snapshot 6.0.0.2195-f9426e5 (Linux x64, SuperServer).
The plan problem (Problem with using a computed index on a computed column #7945) still exists. Exactly the same variants as on 5.0.4 lose the index (original script, variants 5, 7 and 9 from my earlier comment, and all statement orders from the previous comment).
There is NO index corruption on 6.0.
gfix -v -fullis clean in all 15 variants and in a stress run: 40 cycles of "first attachment, flip the bit in ~100 rows, GC", about 4000 key changes in total.The plan problem affects a different set of attachments than on 5.0:
gfix -v -fullSo on 6.0 only the attachment that first loads the table metadata (while it is not cached) fails to match the index. Attachments opened while the metadata is cached match it, and the keys stored by the first attachment are correct.
This looks consistent with the problem being created during the initial, recursive load of the relation (computed column -> procedure -> the same table). With the shared metadata cache in 6.0 it only affects statement compilation in the attachment doing that load. On 5.0, where every attachment loads its own metadata, every attachment gets an index expression that differs from the one in the creating attachment, and uses it to compute keys as well, which leads to the corruption."
tomaszdubiel18 commentedon Oct 7, 2026
Hello. Are there chances to introduce the fix into the next Firebird 5.0 release?
AlexPeshkoff commentedon Oct 7, 2026
Yes, there are chances - but not guarantees.
Also take into an account that releases take place not weekly and even not monthly.
PS. Including normal (not AI) description can make that chances grow :-)
tomaszdubiel18 commentedon Oct 7, 2026
I'm aware of that. What matters to me is to have it in the next official release.
I will do my best, I will follow @dyemanov recommendations concerning AI reports :-)
tomaszdubiel18 commentedon Oct 15, 2026
Below are the results of analysis performed by AI.
Summary
src/jrd/met.epp(v5.0-release, below) fixes both issues: the plan, the wrong results and the index corruption.fb_assert(!field || !field->fld_computation)inFieldNode::execute(); with the patch, no assertions fire;Root cause
The call chain was captured with gdb on 5.0.5.1916 using the script from this ticket, during the first query in a new attachment:
scan_relation()setsREL_scannedbefore it parses the fields, and it parses them in the order of RDB$RUNTIME.FieldNode::pass1()cannot replace the computed column with its expression. It leaves a plain field reference and setscsb_reload, the mechanism added for Computed field could be wrongly evaluated as NULL [CORE6351] #6592.jrd_prcandFunctioncheckcsb_reloadand recompile the routine before it is executed.MET_lookup_index_expression()andMET_lookup_index_condition()ignore the flag, and store the incomplete tree inIndexBlock::idb_expression/idb_conditionuntil the attachment ends.In that attachment:
PLAN (T NATURAL), andindex T_C_KEY cannot be used in the specified plan(Problem with using a computed index on a computed column #7945).gfix -v -fulldoes not always detect the damage. It reported no errors for partial indexes or in Classic, although the index was wrong.Patch (v5.0-release)
The incomplete expression is still returned to the caller, which is the optimizer working on the routine being compiled during the scan. It can only match an expression that also contains a plain reference to a not yet loaded computed column. Compiling such an expression sets
csb_reloadfor the routine too, so the routine is recompiled before it is executed. The uncached statement is allocated from an attachment pool, so it stays valid while the routine statement exists and is freed at disconnect. This matches the existing path in the same function when the index block lock cannot be taken.Verification (5.0.5.1916)
Flow of each test:
select count(*)(garbage collection).C_KEY + 0.gfix -v -fullis run.ON T (ID) WHERE C_BIT = 1gfix -sweepPLAN JOIN (N NATURAL, NK INDEX (FK_NAGLKSEF_NAGL)), 0 vs 10PLAN JOIN (N INDEX (MK_DO_KSEFX), NK INDEX (FK_NAGLKSEF_NAGL)), 10 vs 10In the same database, forcing each of the 4 expression indexes in a new attachment fails for 1 of them (MK_DO_KSEFX) without the patch, and for none with the patch.
Regression checks
--runslow, SuperServer Release, both builds side by side: 2886 tests, results identical exceptgh_7141andgh_8421. Both tests also pass and fail at random without the patch (gh_8421measures timing).fbguard -forever:firebird.logfor both builds, all unrelated to the patch (InternalDS.cpp:231,protocol.cpp:2277,dfw.cpp:10799);Not covered
IndexPermanent::refreshIndexCode()also ignorescsb_reload. According to the comments in 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 #9165, though, on 6.0 only the attachment that loads the metadata first is affected. A port would need separate analysis and was not tested.