description: 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.

=>

I have observed an strange behaviour in 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.

Link: This issue duplicates CORE1188 [ CORE1188 ]

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

resolution: Duplicate [ 3 ]

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

QA Status: No test

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

QA Status: No test => Covered by another test(s)

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

Test Details: See test for core-1188