Problem with using a computed index on a computed column

[tomaszdubiel18]

Hello.
Firebird 3.0.11 Windows 64 and Linux.
In a session I create an index on a computed field.
Table NAGL, we have column DO_KSEF and DO_KSEFX. Both are computed fields, I create an index on DO_KSEFX.
INTEGER
COMPUTED BY (CASE WHEN (DO_KSEF = 1) THEN ID_NAGL ELSE -ID_NAGL END) (ID_NAGL is the primary key)
CREATE INDEX MK_DO_KSEFX ON NAGL COMPUTED BY (DO_KSEFX);
After this, I run query:
select first 1 1
from NAGL N
left outer join NAGLKSEF NK on (N.ID_NAGL = NK.ID_NAGL) and
(NK.SRODOWISKO = 1)
where N.DO_KSEFX > 0 and coalesce(NK.POSTEPX, 0) < 3
I get plan:
PLAN JOIN (N INDEX (MK_DO_KSEFX), NK INDEX (FK_NAGLKSEF_NAGL))
However, I reconnect, rerun this query and get unindexed reads from NAGL:
PLAN JOIN (N NATURAL, NK INDEX (FK_NAGLKSEF_NAGL))
This cause signifant delay.
When I want to force the use of the previous plan, I get an error:
index MK_DO_KSEFX cannot be used in the specified plan.
Why? It looks like a bug.

[tomaszdubiel18]

Apart from fixing this bug, is there any workaround for this? It's quite important in our case due to the new law starting soon.

[tomaszdubiel18]

Do you need more informations to reproduce the problem?

[hvlad]

Reproducible test case, please

[tomaszdubiel18]

So far I'm unable to give you the exact steps to reproduce the problem, but maybe the info below will tell you something.
I try to create a similar table (original table has 173 columns):
CREATE TABLE TEST_INDEKS (
ID_TEST_INDEKS INTEGER NOT NULL,
PRZELACZNIK5 INTEGER,
COL_COMP COMPUTED BY (CAST(SIGN(BIN_AND(PRZELACZNIK5, 64)) AS SMALLINT)),
COL_COMP2 COMPUTED BY (case when (col_comp>0) then id_TEST_INDEKS else -id_TEST_INDEKS end)
);

ALTER TABLE TEST_INDEKS ADD CONSTRAINT PK_TEST_INDEKS PRIMARY KEY (ID_TEST_INDEKS);

I insert random data:
execute block
as
declare variable I integer;
begin
I = 1;
while (I < 1000000) do
begin
insert into TEST_INDEKS
values (:I, rand() * 5000 * power(-1, :I));
I = I + 1;
end
end;
I create a problematic index:
CREATE INDEX MK_DO_KSEFX ON TEST_INDEKS COMPUTED BY (COL_COMP2);
commit, then run query:
select first 1 1
from TEST_INDEKS
where col_comp2 > 0;
I reconnect and I should now reproduce the problem, but I'm unable to do this. Do you have any thoughts? What more table/columns properties can be important in this case?
Best regards.

[hvlad]

Could you try with two tables and LEFT JOIN as in original case ?

[tomaszdubiel18]

This is not needed. Those are minimum steps needed to reproduce. On the original table I reproduce this every time.
I drop index, create it, run query:
select FIRST 1 1
from NAGL N
where DO_KSEFX > 0;
I get indexed reads from NAGL with the use of index.
I reconnect and index is no longer used.

[hvlad]

Then there should be possible to extract this table DDL only and reproduce the issue.

[tomaszdubiel18]

Unfortunately, when I created another table with DDL from the old one (I had to get rid of columns being selects from other tables and all foreign keys), I'm still unable to reproduce the problem.

[hvlad]

Is it reproducible with metadata-only DB ?

[tomaszdubiel18]

Yes, it is. I will prepare it and send it to you.

[tomaszdubiel18]

TEST_INDEX.zip
Here you are. To reproduce the problem, restore the database, drop index MK_DO_KSEFX, create it again, run query:
select FIRST 1 1
from NAGL N
where DO_KSEFX > 0;
This index will be used. Reconnect, repeat: index will no longer be used.
I'm looking forward to hearing from you. If you have any workaround for this, I will be grateful.

[hvlad]

It requires UDF GETVALUE to run the query.

[tomaszdubiel18]

dll_udf.zip

[tomaszdubiel18]

Here are all udf dll files

[hvlad]

Reproduced, investigating

[hvlad]

In short: relation have a lot of computed fields, most of them are trivial, but some depends on stored procedures that in turn depend on the same relation. When relation metadata is loaded for a very first time, this dependency circle makes some computed fields to incorrectly compute its expression, as not all computed fields have its expressions loaded at this moment. When index is creating, engine sees already loaded table's metadata, thus index get correct expression and works (until disconnect).
This is not final conclusion, just after a first look. It can't be fixed too easy and quickly, I'm afraid.
Looks like more complex case of #6592