Expression indexes w/ "coalesce" within the expression not working after
migration from firebird 2.5.x to firebird 3.0.x
------------------------------------------------------------------------------------------------------------------------
Key: CORE-6440
URL: http://tracker.firebirdsql.org/browse/CORE-6440
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.7, 3.0.6
Environment: Windows 8.1
Reporter: Everton Miyabukuro
Priority: Minor
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'
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
