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
Same select give different resultset on 1.5.5 and 2.0.5 [CORE2649] #3056
Comments
Commented by: @dyemanov I cannot reproduce the problem. |
Modified by: Attila Molnár (e_pluribus_unum)Attachment: testcase2.zip [ 11503 ] |
Commented by: Attila Molnár (e_pluribus_unum) Attached my testcase DB, and sql. |
Commented by: @hvlad The record is missed because of different NULL key encoding by FB 1.5 and FB 2.x for ODS10 and WIN1250 (probably for other encodings also). The call stack (in FB 1.5) is :
> fbserver.exe!NAV_get_record(Rsb * rsb=0x03dd6c18, irsb_nav * impure=0x03dd68ec, rpb * rpb=0x03dd673c, rse_get_mode direction=RSE_get_forward) Line 753 + 0x1a C++ In FB 1.5 we have USHORT LC_NARROW_key_length(TEXTTYPE obj, USHORT inLen) inLen == 30, len == 90 While in HEAD we have USHORT LC_NARROW_key_length(texttype* obj, USHORT inLen)
... // here we have len == 60
// here we have len == 68
} Probably Adriano can explain more. I doubt we will change something in 2.x in this regards. Migrate your database to ODS 11 and all will be ok. |
Commented by: Attila Molnár (e_pluribus_unum) I think the different ODS should not affect th resultset. 2.x serie support ODS 10.1. Or are you give up the compability? BTW I think a better index choise strategy should solve the problem : choose PK index instead of the UK index. |
Commented by: @hvlad Choice of index is another question in this case. I also think that index with less segments must be used here. As another workaround you can rebuild index by FB 2. And it will not work correctly with FB 1.5 ;) I see no reason to use the same database one day with FB 2 then one day with FB 1.5 then again with FB 2. I, personally, see no reason to make any hard changes in FB2 to fix this incompatibility. |
Commented by: @dyemanov Formally, this is a bug. But I don't see any urgent need to fix it either. Every FB version has a native ODS version which is designed to work with. We try to not break access to prior ODS versions, but this support was never tested by the project, so we cannot claim it being official. |
Commented by: @asfernandes Vlad, it's news for me that a different "max key length" may change index keys encoding. What would happen then when altering field lengths? |
Commented by: @hvlad Adriano, its for NULL values before ODS11 only. |
Commented by: @asfernandes Ok, Vlad... But doesn't that mean that not only using ODS11 in v2 could cause problem? I.e., altering an indexed field in 1.5 would do the same? |
Commented by: @hvlad Adriano, > But doesn't that mean that not only using ODS11 in v2 could cause problem? Sorry, could you explain ? I not understand > I.e., altering an indexed field in 1.5 would do the same? No, as alter of column don't touched records and they left on disk in old format. When you update such record it will change key value in index even if you don't change value of indexed field as NULL representation is different in old and new formats |
You can close this issue. Thank you. |
Submitted by: Attila Molnár (e_pluribus_unum)
Attachments:
testcase2.zip
The database is created on FB1.5.5 (ODS 10.1)
CREATE TABLE TESTTABLE (
ID XIDN /* XIDN = INTEGER NOT NULL */,
FLAG XVAR1N /* XVAR1N = VARCHAR(1) NOT NULL */,
CODE XVAR30N /* XVAR30N = VARCHAR(30) NOT NULL */,
NAME XVAR100N /* XVAR100N = VARCHAR(100) NOT NULL */,
NAME2 XVAR100 /* XVAR100 = VARCHAR(100) */
);
ALTER TABLE TESTTABLE ADD CONSTRAINT TESTTABLE_PK PRIMARY KEY (ID);
ALTER TABLE TESTTABLE ADD CONSTRAINT TESTTABLE_UK1 UNIQUE (FLAG, NAME);
ALTER TABLE TESTTABLE ADD CONSTRAINT TESTTABLE_UK2 UNIQUE (ID, NAME2);
CREATE TABLE TESTTABLE_CHILD (
ID XIDN /* XIDN = INTEGER NOT NULL */,
TESTTABLE_ID XIDN /* XIDN = INTEGER NOT NULL */,
TEXT XVAR30N /* XVAR30N = VARCHAR(30) NOT NULL */,
NAME2 XVAR100 /* XVAR100 = VARCHAR(100) */
);
ALTER TABLE TESTTABLE_CHILD ADD CONSTRAINT TESTTABLE_CHILD_PK PRIMARY KEY (ID);
ALTER TABLE TESTTABLE_CHILD ADD CONSTRAINT TESTTABLE_CHILD_FK1 FOREIGN KEY (TESTTABLE_ID, NAME2) REFERENCES TESTTABLE (ID, NAME2);
INSERT INTO testtable(id,flag,code,name,name2) VALUES(0,'A','001','Name1','Name1-alternative');
INSERT INTO testtable(id,flag,code,name,name2) VALUES(1,'A','002','Name2','Name2-alternative');
INSERT INTO testtable(id,flag,code,name,name2) VALUES(2,'A','003','Name3',NULL);
INSERT INTO testtable(id,flag,code,name,name2) VALUES(3,'B','004','Name4',NULL);
INSERT INTO testtable(id,flag,code,name,name2) VALUES(4,'B','005','Name5',NULL);
INSERT INTO testtable_child(id,testtable_id,text,NAME2) values(5,0,'a text','Name1-alternative');
INSERT INTO testtable_child(id,testtable_id,text,NAME2) values(6,1,'a text','Name2-alternative');
SELECT * FROM testtable ORDER BY id
On FB 1.5.5 all 5 records are in the list (runs on TESTTABLE_UK2 index)
On FB 2.0.5 only the first 2 records are (where the name2 value is not null) in the list (runs on TESTTABLE_UK2 index)
I know TESTTABLE_UK2 is a strange unique, becaue it's first field is the PK, but we need it, because the child table foreign key. The TESTTABLE_CHILD_FK1 protects the name2 field value form updates.
We've got workarounds for the problem (use a different way for protection). BUT because the "data loss" (phisically the data is exists in the DB, and can be reached if do not use the ORDER BY on id) I set it Mayor instead of Minor. Or it sholud be Critical?
The text was updated successfully, but these errors were encountered: