Issue Details (XML | Word | Printable)

Key: CORE-5118
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Blocker Blocker
Assignee: Dmitry Yemanov
Reporter: CHENAVIER Gérald
Votes: 1
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Indices on computed fields are broken after restore (all keys are NULL)

Created: 26/Feb/16 11:35 AM   Updated: 06/Mar/19 07:54 AM
Component/s: None
Affects Version/s: 2.5.0, 2.5.1, 2.5.2, 2.5.3, 2.5.3 Update 1, 2.5.4, 2.5.5, 4.0 Initial, 3.0.0
Fix Version/s: 4.0 Beta 1, 2.5.9, 3.0.5

Issue Links:
Duplicate
 
Relate
 

QA Status: Done successfully


 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 29/Feb/16 01:31 PM
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.