Issue Details (XML | Word | Printable)

Key: CORE-3052
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Adriano dos Santos Fernandes
Reporter: Attila Molnár
Votes: 2
Watchers: 4
Operations

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

Wrong resultset

Created: 17/Jun/10 05:35 AM   Updated: 23/Sep/15 12:58 PM
Component/s: Engine
Affects Version/s: 2.0.5
Fix Version/s: 3.0 Alpha 1

Environment: Tested in different hardwares (x32, x64), different OS (Win, Linux), different type (Classic, SuperServer)
Issue Links:
Depend
 
Relate

QA Status: Done successfully


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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 17/Jun/10 09:19 AM - edited
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.

Attila Molnár added a comment - 17/Jun/10 09:50 AM - edited
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.

Vlad Khorsun added a comment - 17/Jun/10 10:20 AM
This is bad workaround as it is equal to te.m2 LIKE '%' , see number of indexed reads ...

Adriano dos Santos Fernandes added a comment - 17/Jun/10 10:52 AM
> 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 CORE-1188, as LIKE injects a STARTING WITH.

Attila Molnár added a comment - 17/Jun/10 12:10 PM - edited
The related CORE-1188 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).

Attila Molnár added a comment - 10/Nov/10 10:37 AM
Hi!

Any news on this issue?

Attila Molnár added a comment - 29/Aug/12 06:42 AM
Finally! Thank You!
Any chance backport it to 2.5?

Adriano dos Santos Fernandes added a comment - 29/Aug/12 10:41 AM
The fix changes how some index keys are generated, so I don't think it can be backported without side effects.

Pavel Cisar added a comment - 23/Sep/15 12:58 PM
Test created.