Submitted by: Everton Miyabukuro (everton.miyabukuro)

After migrating a database from Firebird 2.5.8 to Firebird 3.0.6 (tested with firebird 3.0.7 as well), expression indexes with a "coalesce" within the expression stopped being used in queries. Only after dropping and recreating the affected indexes they where picked up by the optimizer. Recomputing the selectivity for the index had not effect. Expression indexes with expressions other than coalesce (e.g. "upper()") worked properly.

Test case:
Create the following database in firebird 2.5.8:
CREATE DATABASE '127.0.0.1:c:\test.fdb'
USER 'SYSDBA'
PAGE_SIZE 16384
DEFAULT CHARACTER SET WIN1252 COLLATION WIN_PTBR;

CREATE TABLE TEST (
FIELD_1 INTEGER NOT NULL,
FIELD_2 VARCHAR(50) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
FIELD_3 VARCHAR(50) CHARACTER SET WIN1252 COLLATE WIN_PTBR
);

INSERT INTO TEST (FIELD_1, FIELD_2, FIELD_3) VALUES (1, 'TEST1', 'TEST1_1');
INSERT INTO TEST (FIELD_1, FIELD_2, FIELD_3) VALUES (2, 'TEST2', 'TEST2_2');

COMMIT WORK;

ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (FIELD_1);

CREATE INDEX TEST_IDX1 ON TEST COMPUTED BY (UPPER(COALESCE(FIELD_2,''))||UPPER(COALESCE(FIELD_3,'')));
CREATE INDEX TEST_IDX2 ON TEST COMPUTED BY (UPPER(FIELD_2)||UPPER(FIELD_3));
CREATE INDEX TEST_IDX3 ON TEST COMPUTED BY (UPPER(COALESCE(FIELD_2,'')));
CREATE INDEX TEST_IDX4 ON TEST COMPUTED BY (UPPER(FIELD_2));

Backup this database in firebird 2.5.8, restore in firebird 3.0.6 or firebird 3.0.7.
Then execute the following selects:

--Uses a proper index: PLAN (TEST INDEX (PK_TEST))
select * from test where field_1 = 1

--Uses a proper index: PLAN (TEST INDEX (TEST_IDX4))
select * from test where (UPPER(FIELD_2)) = 'TEST1'

--Doesn't uses a proper index: PLAN (TEST NATURAL)
select * from test where (UPPER(COALESCE(FIELD_2,''))) = 'TEST1'

--Uses PLAN (TEST INDEX (TEST_IDX2))
select * from test where (UPPER(FIELD_2)||UPPER(FIELD_3)) = 'TEST1TEST1_1'

--Doesn't uses a proper index: PLAN (TEST NATURAL)
select * from test where (UPPER(COALESCE(FIELD_2,''))||UPPER(COALESCE(FIELD_3,''))) = 'TEST1TEST1_1'

Commits: 350aa6f 3dc8c2d FirebirdSQL/fbt-repository@491d448