Skip to content
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

Closed
firebird-automations opened this issue Nov 9, 2020 · 14 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It should be fixed now, please test the next snapshot build.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0.8 [ 10960 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: 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

@firebird-automations
Copy link
Collaborator Author

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?

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.
Thanks for the clarification and the correction of the issue!

@firebird-automations
Copy link
Collaborator Author

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.
Queries issue following plans:

1) OK:
PLAN (TEST INDEX (PK_TEST))

2) OK:
PLAN (TEST INDEX (TEST_IDX4))

3) wrong:
- PLAN (TEST INDEX (TEST_IDX3))
+ PLAN (TEST NATURAL)

4) OK:
PLAN (TEST INDEX (TEST_IDX2))

5) wrong:
- PLAN (TEST INDEX (TEST_IDX1))
+ PLAN (TEST NATURAL)

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Open [ 1 ] => Open [ 1 ]

QA Status: No test => Deferred

Test Details: FB 4.x is also affected but has no fix yet.
Waiting for reply, issue 15.11.2020 18:53

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Yes, it will be frontported soon.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Deferred => Done successfully

Test Details: FB 4.x is also affected but has no fix yet.
Waiting for reply, issue 15.11.2020 18:53

=>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment