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

Wrong resultset [CORE3052] #3432

Closed
firebird-automations opened this issue Jun 17, 2010 · 17 comments
Closed

Wrong resultset [CORE3052] #3432

firebird-automations opened this issue Jun 17, 2010 · 17 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Attila Molnár (e_pluribus_unum)

Relate to CORE1188
Depends on CORE1188
Is related to QA551

Votes: 2

CREATE TABLE tmp_test (
m1 xvar10n /* XVAR10N = VARCHAR(10) NOT NULL */,
m2 xvar10n /* XVAR10N = VARCHAR(10) NOT NULL */,
val xint /* XINT = INTEGER */
);

ALTER TABLE tmp_test ADD CONSTRAINT tmp_test_uk1 UNIQUE (m1, m2);

INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'C1', 1);
INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'C2', 2);
INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'D2', 3);
INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'M3', 3);

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE 'C%'
--No line returned! (wrong), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE 'D%'
--No line returned! (wrong), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE 'M%'
--One line returned. (OK), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE '%C%'
--Two lines returned. (OK), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m2 LIKE 'C%'
-- Two lines returned. (OK), PLAN (TE NATURAL)

On FB 1.5 (ODS 10.1) All five works fine.
On FB 2.0 (ODS 10.1) All five works fine.
On FB 2.0 (ODS 11.0) The first two gives bad result.
The char set is WIN1250 and the collate is PXW_HUNDC.

Commits: c1c5d2b 57ddc9e FirebirdSQL/fbt-repository@a290acc

@firebird-automations
Copy link
Collaborator Author

Modified by: Attila Molnár (e_pluribus_unum)

environment: Tested in differend hardwares and different Win => Tested in different hardwares (x32, x64), different OS (Win, Linux), different type (Classic, SuperServer)

@firebird-automations
Copy link
Collaborator Author

Modified by: Attila Molnár (e_pluribus_unum)

description: CREATE TABLE tmp_test (
m1 xvar10n /* XVAR10N = VARCHAR(10) NOT NULL */,
m2 xvar10n /* XVAR10N = VARCHAR(10) NOT NULL */,
val xint /* XINT = INTEGER */
);

ALTER TABLE tmp_test ADD CONSTRAINT tmp_test_uk1 UNIQUE (m1, m2);

INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'C1', 1);
INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'C2', 2);
INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'D2', 3);
INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'M3', 3);

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE 'C%'
--No lines returned! (wrong), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE 'D%'
--No lines returned! (wrong), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE 'M%'
--One lines returned. (OK), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE '%C%'
--Two lines returned. (OK), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m2 LIKE 'C%'
-- Two lines returned. (OK), PLAN (TE NATURAL)

All five works fine on 1.5, but on FB 2.0 the first two gives bad result.
The char set is WIN1250 and the collate is PXW_HUNDC.

=>

CREATE TABLE tmp_test (
m1 xvar10n /* XVAR10N = VARCHAR(10) NOT NULL */,
m2 xvar10n /* XVAR10N = VARCHAR(10) NOT NULL */,
val xint /* XINT = INTEGER */
);

ALTER TABLE tmp_test ADD CONSTRAINT tmp_test_uk1 UNIQUE (m1, m2);

INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'C1', 1);
INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'C2', 2);
INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'D2', 3);
INSERT INTO tmp_test (m1, m2, val) VALUES ('A', 'M3', 3);

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE 'C%'
--No line returned! (wrong), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE 'D%'
--No line returned! (wrong), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE 'M%'
--One line returned. (OK), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE '%C%'
--Two lines returned. (OK), PLAN (TE INDEX (TMP_TEST_UK1))

SELECT *
FROM tmp_test te
WHERE te.m2 LIKE 'C%'
-- Two lines returned. (OK), PLAN (TE NATURAL)

On FB 1.5 (ODS 10.1) All five works fine.
On FB 2.0 (ODS 10.1) All five works fine.
On FB 2.0 (ODS 11.0) The first two gives bad result.
The char set is WIN1250 and the collate is PXW_HUNDC.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Confirmed.

Key, used for search in the b-tree, is different than key, used when value was inserted into b-tree. This is true for PXW_HUNDC and two-segmens index and false for default collation or for single segment index (on M2 only).
Note, for PXW_HUNDC and single-segment index full undex scan is performed (4 indexed reads, instead of just 2).

The search key is (i put bytes from every segment into separate line) :
2, 78, 3, 0, 0
1, 0

while the first key in index is :
2, 78, 3, 0, 0
1, 80, 62, 3

The code i can't completely understand is in LC_NARROW_string_to_key, /intl/lc_narrow.cpp line, 294 (per v2.5 sources) near :

// ASF: If key_type == INTL_KEY_PARTIAL and the last CompressPair
// isn't complete, don't put the byte in the sortkey. If we put,
// incorrect results occur when using index.

This function produced key of 0 bytes length for string 'C'

Call stack :

> fbintl.dll!LC_NARROW_string_to_key(texttype * obj=0x02be2dd4, unsigned short iInLen=1, const unsigned char * pInChar=0x02c17480, unsigned short iOutLen=4096, unsigned char * pOutChar=0x01df99a2, unsigned short key_type=1) Line 297 C++
fb_inet_server.exe!Jrd::TextType::string_to_key(unsigned short srcLen=1, const unsigned char * src=0x02c17480, unsigned short dstLen=4096, unsigned char * dst=0x01df99a2, unsigned short key_type=1) Line 201 + 0x2b bytes C++
fb_inet_server.exe!INTL_string_to_key(Jrd::thread_db * tdbb=0x01dff50c, unsigned short idxType=33394, const dsc * pString=0x02c17470, dsc * pByte=0x01df9978, unsigned short key_type=1) Line 1216 + 0x2e bytes C++
fb_inet_server.exe!compress(Jrd::thread_db * tdbb=0x01dff50c, const dsc * desc=0x02c17470, Jrd::temporary_key * key=0x01dfaa58, unsigned short itype=33394, bool isNull=false, bool descending=false, unsigned short key_type=1) Line 2607 + 0x1e bytes C++
fb_inet_server.exe!BTR_make_key(Jrd::thread_db * tdbb=0x01dff50c, unsigned short count=2, Jrd::jrd_nod * * exprs=0x02c14080, Jrd::index_desc * idx=0x02c13fac, Jrd::temporary_key * key=0x01dfcc00, bool fuzzy=true) Line 1655 + 0xa1 bytes C++
fb_inet_server.exe!BTR_find_page(Jrd::thread_db * tdbb=0x01dff50c, Jrd::IndexRetrieval * retrieval=0x02c13fa8, Jrd::win * window=0x01dfec20, Jrd::index_desc * idx=0x01dfec44, Jrd::temporary_key * lower=0x01dfdc10, Jrd::temporary_key * upper=0x01dfcc00) Line 867 + 0x42 bytes C++
fb_inet_server.exe!BTR_evaluate(Jrd::thread_db * tdbb=0x01dff50c, Jrd::IndexRetrieval * retrieval=0x02c13fa8, Firebird::SparseBitmap<unsigned __int64,Firebird::BitmapTypes_64> * * bitmap=0x02c16f28, Firebird::SparseBitmap<unsigned __int64,Firebird::BitmapTypes_64> * bitmap_and=0x00000000) Line 660 + 0x29 bytes C++

Probably Adriano could explain it.

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Hi!

SELECT *
FROM tmp_test te
WHERE te.m1 = 'A' AND te.m2 LIKE 'C%' COLLATE PXW_HUNDC
--Two lines returned. (OK), PLAN (TE INDEX (TMP_TEST_UK1))

So there is a workaround, BUT it is unacceptable that we rewrite every single sql conditions in our projects.
Without definig the COLLATE firebird engine shoud choose the collate based on the tested field.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

This is bad workaround as it is equal to te.m2 LIKE '%' , see number of indexed reads ...

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

> This function produced key of 0 bytes length for string 'C'

Vlad, this is correct, believe me. It's related to compressions (like "ch" that expands to different characters, but still starts with "c").

The bug here is CORE1188, as LIKE injects a STARTING WITH.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue relate to CORE1188 [ CORE1188 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue depends on CORE1188 [ CORE1188 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

The related CORE1188 is open for 3 years, and not backported to 2.x.
I hope this issue will be corrigated much-much sooner, and will be in 2.x.

BTW, I can confim Adrianos state. After reading his "ch" example I tested special hungarian letters. It's related to special hungarian letters, which contains more than one character (cs, dz, dzs, gy, ly, ty, sz, zs).

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Hi!

Any news on this issue?

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Finally! Thank You!
Any chance backport it to 2.5?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

The fix changes how some index keys are generated, so I don't think it can be backported without side effects.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA551 [ QA551 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

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

2 participants