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-issue-importer opened this issue Jun 17, 2010 · 17 comments
Closed

Wrong resultset [CORE3052] #3432

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

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

Commented by: @hvlad

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

Modified by: @asfernandes

Link: This issue relate to CORE1188 [ CORE1188 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

Modified by: @asfernandes

Link: This issue depends on CORE1188 [ CORE1188 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 10, 2010

Commented by: Attila Molnár (e_pluribus_unum)

Hi!

Any news on this issue?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 28, 2012

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-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 29, 2012

Commented by: Attila Molnár (e_pluribus_unum)

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 29, 2012

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-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 29, 2014

Modified by: @pcisar

Link: This issue is related to QA551 [ QA551 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 28, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 23, 2015

Commented by: @pcisar

Test created.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 23, 2015

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