--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

>
> At 10:13 AM 22/02/2007, you wrote:
> >Hello,
> >
> >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.)
>
> ./heLen
>

Thank You Helen for your time with me.

If I may bother you once more; if it was a issue of logical
corruption what steps would you suggest to handle this type of issue
gracefully in firebird. It seams a difficult thing to detect as no
error message was thrown.