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

Indices on computed fields are broken after restore (all keys are NULL) [CORE5118] #5402

Closed
firebird-automations opened this issue Feb 26, 2016 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: CHENAVIER Gérald (gchenavier)

Is related to CORE4673
Duplicates CORE2440

Votes: 1

No problem in 2.5.2, it is regression :

The Select with order by computed col return 0 line.

Reproduct :

CREATE TABLE T1 (IDT1 INTEGER, NOMT1 VARCHAR(10), PRENOMT1 VARCHAR(10) , NOMPRENOMT1 COMPUTED BY (NOMT1 || ' ' || PRENOMT1));
CREATE TABLE T2 (IDT2 INTEGER);
INSERT INTO T1 (IDT1, NOMT1, PRENOMT1)VALUES (1, 'NOM1', 'PRENOM1');
INSERT INTO T1 (IDT1, NOMT1, PRENOMT1)VALUES (2, 'NOM2', 'PRENOM2');
INSERT INTO T1 (IDT1, NOMT1, PRENOMT1)VALUES (3, 'NOM3', 'PRENOM3');
INSERT INTO T2 (IDT2)VALUES (0);
CREATE INDEX YYT1 ON T1 COMPUTED BY (NOMPRENOMT1);

make Backup/restore in 2.5.5

select a.IDT1, a.NOMT1, a.NOMPRENOMT1 from T1 a order by 3
-> return 0 line

select a.IDT1, a.NOMT1, a.NOMPRENOMT1 from T1 a order by 2
-> its ok

Commits: dfc0259 fea7c61 a741300

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I've updated the ticket subject for the real problem. Computed fields are initially restored as regular ones (with missing values) and then altered to become computed. Index creation misses the new definition and deals with the older one, causing NULL keys to be stored in the index.

v2.5.2 is also affected, you just don't see it because the engine does not use the index: plan SORT is used instead of ORDER. It was a regression but it was fixed (in v2.5.4, I think). Now the restore issue becomes visible.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5.4 [ 10585 ]

Version: 2.5.3 Update 1 [ 10650 ]

Version: 2.5.3 [ 10461 ]

Version: 2.5.2 [ 10450 ]

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

assignee: Dmitry Yemanov [ dimitr ]

summary: regression : Bug INDEX on COMPUTED VALUE => Indices on computed fields are broken after restore (all keys are NULL)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE4673 [ CORE4673 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE2440 [ CORE2440 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0.0 [ 10740 ]

Version: 4.0 Initial [ 10621 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

Fix Version: 2.5.9 [ 10862 ]

Fix Version: 3.0.5 [ 10885 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@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