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

Same select give different resultset on 1.5.5 and 2.0.5 [CORE2649] #3056

Closed
firebird-automations opened this issue Sep 28, 2009 · 12 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

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?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I cannot reproduce the problem.

@firebird-automations
Copy link
Collaborator Author

Modified by: Attila Molnár (e_pluribus_unum)

Attachment: testcase2.zip [ 11503 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Attached my testcase DB, and sql.

@firebird-automations
Copy link
Collaborator Author

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 :

fbintl\.dll\!LC\_NARROW\_key\_length\(texttype \* obj=0x03dcbfe4, unsigned short inLen=30\)  Line 95	C
fbserver\.exe\!TextType\_BC<TextTypeNC\>::key\_length\(unsigned short a=30\)  Line 1970 \+ 0x17	C\+\+
fbserver\.exe\!INTL\_key\_length\(tdbb \* tdbb=0x02fffa98, unsigned short idxType=33394, unsigned short iLength=30\)  Line 937 \+ 0x11	C\+\+
fbserver\.exe\!compress\(tdbb \* tdbb=0x02fffa98, dsc \* desc=0x02ffec18, key \* key=0x02ffec2c, unsigned short itype=33394, unsigned short missing=1, unsigned short descending=0, unsigned short fuzzy=0\)  Line 2044 \+ 0x13	C\+\+
fbserver\.exe\!BTR\_key\(tdbb \* tdbb=0x02fffa98, jrd\_rel \* relation=0x02d136d0, rec \* record=0x03dd7b20, idx \* idx=0x03dd6a18, key \* key=0x02ffee90, idx\_null\_state \* null\_state=0x00000000\)  Line 1032 \+ 0x3c	C\+\+
fbserver\.exe\!get\_record\(Rsb \* rsb=0x03dd6c18, irsb\_nav \* impure=0x03dd68ec, rpb \* rpb=0x03dd673c, key \* key=0x02fff50c, unsigned char inhibit\_cleanup=0\)  Line 1661 \+ 0x2a	C\+\+

> 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)
{
USHORT len;
/* assert (inLen <= LANGFAM2_MAX_KEY); *//* almost certainly an error */
len = 3 * MAX(inLen, 2);
return (MIN(len, LANGFAM2_MAX_KEY));
}

inLen == 30, len == 90

While in HEAD we have

USHORT LC_NARROW_key_length(texttype* obj, USHORT inLen)
{
// fb_assert (inLen <= LANGFAM2_MAX_KEY); // almost certainly an error

TextTypeImpl\* impl = static\_cast<TextTypeImpl\*\>\(obj\-\>texttype\_impl\);

...
USHORT len = impl->texttype_bytes_per_key * MAX(inLen, 2);

// here we have len == 60

if \(impl\-\>texttype\_expand\_table && \(\(const ExpandChar\*\) impl\-\>texttype\_expand\_table\)\[0\]\.Ch\)
\{
	len \+= \(USHORT\) log10\(inLen \+ 1\.0\) \* 4 \* impl\-\>texttype\_bytes\_per\_key;
\}

// here we have len == 68

return \(MIN\(len, LANGFAM2\_MAX\_KEY\)\);

}

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Choice of index is another question in this case. I also think that index with less segments must be used here.
But this is not a solution to the original issue - different ways of encoding of string keys for ODS10.

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.
Decide what you need to use.

I, personally, see no reason to make any hard changes in FB2 to fix this incompatibility.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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?

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Adriano, its for NULL values before ODS11 only.
Before ODS11 NULL values was padded up to the full length of corresponding field.
In ODS11 NULL values stored as 0 (or 1) byte for asc (or desc) index.
See BTR\compress for details.

@firebird-automations
Copy link
Collaborator Author

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?

@firebird-automations
Copy link
Collaborator Author

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

@EPluribusUnum
Copy link

You can close this issue. Thank you.

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

No branches or pull requests

3 participants