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