To me your description sounds like a corrupt index. If so, I would expect 'WHERE FLD4+0' to give the right result without backup/restore (though it may be time consuming since the index will not be used). Recreating the index or turning it inactive/active may also work, though others with more experience in this field (I don't think I've ever experienced such a corruption) will be able to give a better (and safer) answer.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Jacek Borowski
Sent: 23. mai 2008 14:32
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Unknown value after insert

Hi,

From about 2 months ago, from time to time (10-15 times) We have wrong field value after insert.

Server: Firebird CS 1.5.5.4926
Database ~18GB working from 7 years, backed up regullary
Linux RedHat Exterprise, kernel 2.6.18
TABLE ~20k records

First example:

TABLE (fld1 varchar(2),fld2 integer,fld3 integer,fld4 varchar(1),fld5 integer)

insert into TABLE (fld1, fld2, fld3) values ('AA',2,1) ;
Table, has no triggers.

select * from TABLE where fld4 is null - no results
select * from TABLE where fld4 is not null - no results
select strlen(fld4) from TABLE where fld1='AA' and fld2=2 and fld3=1 - result 0
select Ascii_val(fld4) from TABLE where fld1='AA' and fld2=2 and fld3=1 - result 32 (space ???)

after backup/restore

select * from TABLE where fld4 is null - correct result


Second example:

insert into TABLE (fld1, fld2, fld3, fld5) values ('AA',2,1,1) ;
select * from TABLE - I see in grid | AA | 2 | 1 | null | 1 |
select * from TABLE where fld5=1 - no results
select * from TABLE where fld5>1 - no results
select * from TABLE where fld5<1 - no results

backup/restore - selects are OK

After few days we have new records with problem as shown above.

Whats wrong ?

Regards,
Jacek