New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Expression indexes containing COALESCE inside cannot be matched by the optimizer after migration from v2.5 to v3.0 [CORE6440] #6674
Comments
Commented by: @dyemanov FB 2.5 and FB 3.0 generate different byte-code (BLR) for the COALESCE function, so they are not considered equal by the optimizer. But perhaps we could find a workaround. |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: @dyemanov It should be fixed now, please test the next snapshot build. |
Modified by: @dyemanovFix Version: 3.0.8 [ 10960 ] |
Modified by: @dyemanovsummary: Expression indexes w/ "coalesce" within the expression not working after migration from firebird 2.5.x to firebird 3.0.x => Expression indexes containing COALESCE inside cannot be matched by the optimizer after migration from v2.5 to v3.0 |
Commented by: Everton Miyabukuro (everton.miyabukuro) Tested and it's working properly now, both on databases that where migrated to firebird 3.0.6 as well on databases restored using the snapshot build 3.0.8. Out of curiosity, shouldn't the BRL that is used by the index be recreated when the database is restored and indexes recreated? Or it is carried over from the original database (w/ firebird 2.5.x) and only reacreate when a "create index" is run? |
Commented by: @dyemanov Source code for that BLR does not necessarily exist (some people remove them for security reasons), so recompiling objects during restore may be impossible. And the engine is always able to parse and execute older BLR, so generally it's simply not required and "migrated" BLR can be used without problems. Optimizer is probably the only place that could be affected by the difference. |
Commented by: Everton Miyabukuro (everton.miyabukuro) I see, makes sense. I knew that this could happen to stored procedures/views/triggers but didn't know that it could happen also to indexes. |
Commented by: @pavel-zotov Any plans to fix is on 4.0.x ? (because it is also affected) I created DB on 2.5, did backup and then - restore on 4.0.0.2265. 1) OK: 2) OK: 3) wrong: 4) OK: 5) wrong: |
Modified by: @pavel-zotovstatus: Open [ 1 ] => Open [ 1 ] QA Status: No test => Deferred Test Details: FB 4.x is also affected but has no fix yet. |
Commented by: @dyemanov Yes, it will be frontported soon. |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 4.0 RC 1 [ 10930 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: Deferred => Done successfully Test Details: FB 4.x is also affected but has no fix yet. => |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
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
The text was updated successfully, but these errors were encountered: