Error in selecting rows with compound index
-------------------------------------------
Key: CORE-3971
URL: http://tracker.firebirdsql.org/browse/CORE-3971
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.2, 2.5.3
Environment: Windows 32 and 64 bits
Reporter: Jesus Angel Garcia Zarco
I have observed an strange behaviour in the latest snapshots of Firebird 2.5.2
and in recent snapshots of 2.5.3. I think is a bug related to some changes
introduced in recent builds that solves other bugs related to index corruptions.
I have one table with a compound index smallint+varchar.
CREATE TABLE NEW_TABLE (
FIELD_ID INTEGER NOT NULL,
FIELD_DESC VARCHAR(10) NOT NULL,
FIELD_SEL SMALLINT NOT NULL
);
CREATE INDEX NEW_TABLE_IDX1 ON NEW_TABLE (FIELD_ID, FIELD_DESC);
CREATE INDEX NEW_TABLE_IDX2 ON NEW_TABLE (FIELD_SEL, FIELD_DESC);
INSERT INTO NEW_TABLE (FIELD_ID, FIELD_DESC, FIELD_SEL)
VALUES (1, '', 1);
INSERT INTO NEW_TABLE (FIELD_ID, FIELD_DESC, FIELD_SEL)
VALUES (2, '', 1);
INSERT INTO NEW_TABLE (FIELD_ID, FIELD_DESC, FIELD_SEL)
VALUES (3, 'B', 1);
If I execute the next statement, all runs fine and returns all rows.
select FIELD_ID, FIELD_DESC, FIELD_SEL
from new_table
where FIELD_SEL = 1 and FIELD_DESC starting with ''
1 '' 1
2 '' 1
3 'B' 1
If i execute the same query, but parametrized, i get two rows:
Search = ''
select FIELD_ID, FIELD_DESC, FIELD_SEL
from new_table
where FIELD_SEL = 1 and FIELD_DESC starting with :Search
1 '' 1
2 '' 1
The interesting thing, is that if I now execute the next update
update NEW_TABLE
set FIELD_DESC = 'A'
where FIELD_ID = 2
and then execute again
select FIELD_ID, FIELD_DESC, FIELD_SEL
from new_table
where FIELD_SEL = 1 and FIELD_DESC starting with :Search
I get
1 '' 1
2 'A' 1
and allways if i execute
select FIELD_ID, FIELD_DESC, FIELD_SEL
from new_table
where FIELD_SEL = 1 and FIELD_DESC starting with ''
or
select FIELD_ID, FIELD_DESC, FIELD_SEL
from new_table
where FIELD_DESC starting with :Search
returns all rows.
--
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
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_sfd2d_oct
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
