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.