Subject Re: [firebird-support] odd issue with firebird
Author Helen Borrie
At 10:13 AM 22/02/2007, you wrote:
>I am hoping you can assist me with a question I have with
>the firebird database v1.5.
>I am using delphi 7 and while building the interface (ibx)
>I noticed a issue with being able to return the entire
>result set (only 15 rows) to the delphi components.
>I noticed that one specific row would not return. It is a
>simple query and does not have any filtering, grouping or
>ordering. If I use IBEasy+ to acces the database and run
>the same query, I can see the record listed in the result
>set. It does look out of order but it is there.
>I tried many things to resolve this, but the final solution
>was to export the database, then drop the table and rebuild
>it with the exported data and structure. Once this was
>done, the same exact query that would not return the one
>record, now returns all of them as expected.
>I have been on the devshed forum for firebird and everyone
>is saying it is not firebird that is at fault. But I
>suspect there was a issue with the metadata and the
>possibility it was corrupted.

Yes, it sounds like some kind of logical corruption. You don't
provide any information about your metadata or where the original
data came from, but...

-- one thing I would have suggested exploring was that the
possibility that you have used a CHAR datatype where you should have
used a VARCHAR...or something related to the difference between CHAR
and VARCHAR content when one is compared with the other using an
equality check. For a simple example, say your statement said

...where MyColumn = 'Smith'

and MyColumn is a CHAR(10). In that case, the value of "Smith" would
be 'Smith#####' (actually, not trailing '#' but trailing
blanks). The query would not return that record.

Similarly, in a join or correlated subquery that is testing a match
between a varchar(10) in one table and a char(10) in the other.

Along the same lines....consider the possibility that the original
data had a NULL in a column that was used for a comparison check in
your statement. Null==Null does not return True.

(Remember, too, that Firebird 1.5 and above will permit NULLs in
unique key segments. There could be some historical reason why a NULL
was there, where you expected all non-null values because a NOT NULL
constraint was added later. Post-fixing a NOT NULL constraint does
not go back and insert some arbitrary value into columns where NULL
has been stored. If you alter the attributes of data, you have to
follow that up with whatever is needed to make existing data conform
with the new definition.)