FB 2.1.5: Missing entries in 2-column index with COLLATE PXW_CYRL in 1st column
-------------------------------------------------------------------------------
Key: CORE-3918
URL: http://tracker.firebirdsql.org/browse/CORE-3918
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.1.5
Environment: Windows7 x64, Firebird 2.1.5 x86
Reporter: Vadym Lebid
Test Environment:
---------------
Windows7 x64
Firebird 2.1.5 x86 (Firebird-2.1.5.18496_0_Win32)
---------------------------------------------
Issue Description:
---------------
Database is just restored from fbk (gbak - r).
SQL statement
select
<SomeFields>
from
<TableWith_76842599_RecordsInIt>
where
<IndexedFieldWithCollatePXW_CYRL>=<WellKnownValue>
returns a wrong result set (less rows than expected).
Drop/create index doesn't help.
---------------------------------------------
Validation results:
---------------
gfix -z -validate -no_update -full
reports:
gfix version WI-V2.1.5.18496 Firebird 2.1
Summary of validation errors
Number of index page errors : 28
In firebird.log:
Index 1 is corrupt on page 299894 level 0. File:
..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
...
<8 such records here>
...
Index 1 is corrupt on page 321352 level 0. File:
..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
Index 1 is corrupt (missing entries) in table <TableName>
------------------------------------------------------------------------------------------
Builds tested and confirmed as being affected by this issue:
------------------------------------------------------------
2.1.5.18474
2.1.5.18480
2.1.5.18496
2.1.6.18505
2.1.6.18506
------------------------------------------------------------------------------------------
Builds tested and confirmed as NOT being affected by this issue:
------------------------------------------------------------
2.5.2.26538
2.1.4.18314
2.1.4.18420
2.1.4.18438
So this issue got introduced somewhen between 2.1.4.18438 and 2.1.5.18474.
I just don't have these builds handy to test.
---------------------------------------------
Test database:
---------------
SQL DIALECT 3
DEFAULT CHARACTER SET WIN1251
PAGE_SIZE 16384
or
PAGE_SIZE 8192
------------------------------
Test table:
---------------
CREATE TABLE TST (
ID INTEGER NOT NULL,
DKEY VARCHAR(20) COLLATE PXW_CYRL
);
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
---------------------------------------------
Test data:
---------------
76842599 records in the table TST.
---------------------------------------------
Test SQL statements:
---------------
select *
from TST
where dkey='12213696'
Plan used: PLAN (TST INDEX (TST_DKEY))
Returns: 4 rows
---------------
select *
from TST
where dkey||''='12213696'
Plan used: PLAN (TST NATURAL)
Returns: 39 rows
--------------------------------------------------------------------------
Possible workarounds to solve the issue:
--------------------------------------------
1.Don't use COLLATE PXW_CYRL:
DROP INDEX TST_DKEY;
ALTER TABLE TST ADD DKEY_TMP VARCHAR(20);
UPDATE TST SET DKEY_TMP=DKEY;
ALTER TABLE TST DROP DKEY;
ALTER TABLE TST ALTER COLUMN DKEY_TMP TO DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
Enjoy.
2.Don't use 2-column index:
keep the field as it is (VARCHAR(20) COLLATE PXW_CYRL);
DROP INDEX TST_DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY);
That's it.
3.Delete some unnecessary records:
keep table's metadata as is;
DROP INDEX TST_DKEY;
DELETE FROM TST WHERE ID<17000000;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
So less records - no issue.
-------------------------------------------------------------------------------
Test database (fbk in 7z archive, 126 MB) will be sent on request.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
