Re: Same data, different results in Postgres vs. FrontBase

On Sat, 18 Feb 2006, Brendan Duddridge wrote:

> Hi,
>
> I have a query that returns 569 rows in FrontBase, but only 30 rows
> in Postgres. The data is the same as I just finished copying my
> entire database over from FrontBase to Postgres.
>
> I've reduced my problem to the following statement and have
> discovered that FrontBase returns null rows along with the rows that
> match the query and PostgreSQL only returns the not null rows.
>
> CON.IS_SUBSCRIBED NOT IN ('X', 'P')
>
> Is that normal?

Short form from the spec as we read it:
RVC NOT IN (IPV) => NOT (RVC IN (IPV)) => NOT (RVC =ANY IPV)

The result of RVC =ANY IPV can be described with:
If the implied comparison predicate [ RVC = IPVi] is true for at least
one row IPVi in IPV then true
If the implied comparison predicate is false for every row IPVi in IPV
then false
Otherwise unknown.

NULL = 'X' returns unknown, as does NULL = 'P', so the last case is the
one that should apply. NOT (unknown) is unknown, so the result of
CON.IS_SUBSCRIBED NOT IN ('X', 'P') is unknown for NULL IS_SUBSCRIBED.
Where clauses pass rows where the result of the clause is true, so those
rows are not part of the result.