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

AFAIK NULL is not a value according to SQL spec, so it doesn't match in a "not
in" clause (or any other value comparing clause for that matter, i.e. blabla
>= 10 will not match rows where blabla is null). Therefor I'd say the result
of 30 is correct.
If you want to see null results too you should say so, i.e.

CON.IS_SUBSCRIBED NOT IN ('X', 'P') OR CON.IS_SUBSCRIBED IS NULL



On Saturday 18 February 2006 21:51, 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? I guess I have to rewrite my queries to handle this
> situation.
>
> Does anyone have any idea why the two database engines might differ
> in this way?
>
> Thanks,
>
> ____________________________________________________________________
> Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan@clickspace.com
>
> ClickSpace Interactive Inc.
> Suite L100, 239 - 10th Ave. SE
> Calgary, AB  T2G 0V9
>
> http://www.clickspace.com

--
    UC

--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416

Brendan Duddridge <brendan@clickspace.com> writes:
> 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')

You mean FrontBase thinks a null value of IS_SUBSCRIBED satisfies
that condition?  That's completely broken.  Tell them to read the
SQL standard sometime.

            regards, tom lane

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.