Fw: select null + 0 question

On Sun, Jul 13, 2003 at 11:14:15PM -0700, Vincent Hikida wrote:

Oops forgot to cc the list.

I just happened to notice another difference recently between Oracle and
Postgresql for the clause

WHERE 1 IN (1,2,NULL)

In Oracle, this clause is false because 1 compared to a NULL is false.
However, Postgresql will return a true. I actually don't know what the

ANSI

standard is for this case. Perhaps someone else on this list will know.
Perhaps the standard body never even thought of this. Yes, I was actually
stung by this particular while using it in Oracle.

I can;t comment on what the correct answer is, but I beleive the reason it
works in Postgres is because the expression is expanded to:

WHERE (1=1) OR (1=0) OR (1=NULL)

which becomes:

WHERE TRUE OR FALSE OR NULL

which is TRUE. (standard tri-value logic)
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Sun, 13 Jul 2003, Vincent Hikida wrote:

Oops forgot to cc the list.

Unfortunately, intra-row functions using nulls return nulls. Inter-row
functions "usually" ignore the nulls. I think there may be a few

exceptions.

Though there is a relational theory which has is rigorously consistent,
nulls are not part of the theory. Nulls are basically what

someone/somewhere

thought of as a convenient tool (which it is) but has no theoretical
underpinning and is dangerous. I use it because I feel that I have enough
experience but perhaps I'll be stung one day.

It has been discussed on the list before that in Oracle that in Oracle an
empty string and null are the same. However Postgres treats an empty

string

'' as an actual value and not as null.

I just happened to notice another difference recently between Oracle and
Postgresql for the clause

WHERE 1 IN (1,2,NULL)

In Oracle, this clause is false because 1 compared to a NULL is false.

If this is really true, then I believe Oracle does not implement this
feature correctly. By my reading of SQL92, RVC IN IPV is equivalent to
RVC =ANY IPV and in 8.7 GR2c, "If the implied <comparison predicate> is
true for at least one row RT in T, then R <comp op> <some> T" is true
which I believe holds for the case above.

This was executed via sql+ on an Oracle 9i installation:

SQL> select 1 from dual where 1 in (1,2,null);

1
----------
1

SQL> select 1 from dual where 1 in (null);

no rows selected

I would say the Oracle implementation is correct and the same as in
Postgres. For your problem I would check the other parts of the query.

Cheers,
Csaba.

Thanks for checkin on this Csaba. I should have tried this again myself. I
have 9i on my machine and it works as you said. Of course I had this problem
on 8i perhaps I was mistaken. If someone else has 8i I would appreciate them
testing this.

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "Csaba Nagy" <nagy@ecircle-ag.com>
To: "Vincent Hikida" <vhikida@inreach.com>
Cc: "Postgres general mailing list" <pgsql-general@postgresql.org>
Sent: Monday, July 14, 2003 1:50 AM
Subject: Re: Fw: [GENERAL] select null + 0 question

This was executed via sql+ on an Oracle 9i installation:

SQL> select 1 from dual where 1 in (1,2,null);

1
----------
1

SQL> select 1 from dual where 1 in (null);

no rows selected

I would say the Oracle implementation is correct and the same as in
Postgres. For your problem I would check the other parts of the query.

Cheers,
Csaba.

On Mon, 2003-07-14 at 08:14, Vincent Hikida wrote:

Oops forgot to cc the list.

Unfortunately, intra-row functions using nulls return nulls. Inter-row
functions "usually" ignore the nulls. I think there may be a few

exceptions.

Though there is a relational theory which has is rigorously

consistent,

nulls are not part of the theory. Nulls are basically what

someone/somewhere

thought of as a convenient tool (which it is) but has no theoretical
underpinning and is dangerous. I use it because I feel that I have

enough

experience but perhaps I'll be stung one day.

It has been discussed on the list before that in Oracle that in Oracle

an

empty string and null are the same. However Postgres treats an empty

string

'' as an actual value and not as null.

I just happened to notice another difference recently between Oracle

and

Postgresql for the clause

WHERE 1 IN (1,2,NULL)

In Oracle, this clause is false because 1 compared to a NULL is false.
However, Postgresql will return a true. I actually don't know what the

ANSI

standard is for this case. Perhaps someone else on this list will

know.

Perhaps the standard body never even thought of this. Yes, I was

actually

stung by this particular while using it in Oracle.

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "Jean-Christian Imbeault" <jc@mega-bucks.co.jp>
To: <pgsql-general@postgresql.org>
Sent: Sunday, July 13, 2003 10:42 PM
Subject: [GENERAL] select null + 0 question

Why is it that "select null + 1" gives null but "select sum(a) from
table" where there are null entries returns an integer?

Shouldn't the sum() and "+" operators behave the same?

TAL=# select null + 0;
?column?
----------

(1 row)

TAL=# select * from a;
a
---

1
(3 rows)

TAL=# select sum(a) from a;
sum
-----
1
(1 row)

Thanks,

Jean-Christian Imbeault

---------------------------(end of

broadcast)---------------------------

OK. This has been bothering me. I finally recreated what I remember as an
anomoly in Oracle involving nulls. It also works the same way in PostgreSQL.
I guess in mind I extrapolated that there was a problem with "WHERE 1 IN
(1,2,NULL)" which there isn't.

The following is my query in 9i:

SQL> select 1 from dual where 2 not in (1,null);
no rows selected

The following is in PostgreSQL:

select 1 where 2 not in (1,null)
?column?
------------
(0 rows)

Of course this is just a simple test. The actual query that was being used
when we found the anomoly was more practical:

select ... from tablex where x.a not in (select y.a from tabley);

We finally did something like this to get what we wanted:

select .. from tablex where not exists (select null from tabley where y.a =
x.a);

Now that I think about it a little, SQL does make sense. We are sure that "1
is in (1,2,NULL)". However we cannot be sure that in 3 is not in (1,2, NULL)
because the NULL maybe 3 (if you interpret null as being unknown). However,
the SQL does not work if you have other interpretations of NULL (e.g. Null
means not applicable, Null means divide by 0).

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "Csaba Nagy" <nagy@ecircle-ag.com>
To: "Vincent Hikida" <vhikida@inreach.com>
Cc: "Postgres general mailing list" <pgsql-general@postgresql.org>
Sent: Monday, July 14, 2003 1:50 AM
Subject: Re: Fw: [GENERAL] select null + 0 question

This was executed via sql+ on an Oracle 9i installation:

SQL> select 1 from dual where 1 in (1,2,null);

1
----------
1

SQL> select 1 from dual where 1 in (null);

no rows selected

I would say the Oracle implementation is correct and the same as in
Postgres. For your problem I would check the other parts of the query.

Cheers,
Csaba.

On Mon, 2003-07-14 at 08:14, Vincent Hikida wrote:

Oops forgot to cc the list.

Unfortunately, intra-row functions using nulls return nulls. Inter-row
functions "usually" ignore the nulls. I think there may be a few

exceptions.

Though there is a relational theory which has is rigorously

consistent,

nulls are not part of the theory. Nulls are basically what

someone/somewhere

thought of as a convenient tool (which it is) but has no theoretical
underpinning and is dangerous. I use it because I feel that I have

enough

experience but perhaps I'll be stung one day.

It has been discussed on the list before that in Oracle that in Oracle

an

empty string and null are the same. However Postgres treats an empty

string

'' as an actual value and not as null.

I just happened to notice another difference recently between Oracle

and

Postgresql for the clause

WHERE 1 IN (1,2,NULL)

In Oracle, this clause is false because 1 compared to a NULL is false.
However, Postgresql will return a true. I actually don't know what the

ANSI

standard is for this case. Perhaps someone else on this list will

know.

Perhaps the standard body never even thought of this. Yes, I was

actually

stung by this particular while using it in Oracle.

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "Jean-Christian Imbeault" <jc@mega-bucks.co.jp>
To: <pgsql-general@postgresql.org>
Sent: Sunday, July 13, 2003 10:42 PM
Subject: [GENERAL] select null + 0 question

Why is it that "select null + 1" gives null but "select sum(a) from
table" where there are null entries returns an integer?

Shouldn't the sum() and "+" operators behave the same?

TAL=# select null + 0;
?column?
----------

(1 row)

TAL=# select * from a;
a
---

1
(3 rows)

TAL=# select sum(a) from a;
sum
-----
1
(1 row)

Thanks,

Jean-Christian Imbeault

---------------------------(end of

broadcast)---------------------------

On Tuesday 15 Jul 2003 7:38 pm, Vincent Hikida wrote:

OK. This has been bothering me. I finally recreated what I remember as an
anomoly in Oracle involving nulls. It also works the same way in
PostgreSQL. I guess in mind I extrapolated that there was a problem with
"WHERE 1 IN (1,2,NULL)" which there isn't.

The following is my query in 9i:

SQL> select 1 from dual where 2 not in (1,null);
no rows selected

The following is in PostgreSQL:

select 1 where 2 not in (1,null)
?column?
------------
(0 rows)

Now that I think about it a little, SQL does make sense. We are sure that
"1 is in (1,2,NULL)". However we cannot be sure that in 3 is not in (1,2,
NULL) because the NULL maybe 3 (if you interpret null as being unknown).
However, the SQL does not work if you have other interpretations of NULL
(e.g. Null means not applicable, Null means divide by 0).

You might like to look over:
http://techdocs.postgresql.org/guides/BriefGuideToNulls

It's my typing, but I got a lot of input from the list, so it's good stuff.
Sorry I didn't post this link earlier but I'm afraid I didn't look into your
thread until tonight.

--
Richard Huxton