Strange result with select/not null/OR

Fernando Papa wrote:

and (sp_link IS NOT NULL OR sp_link <> '')

If sp_link <> '' then it is of course NOT NULL so ...

you get:

sp_link IS NOT NULL --> true
sp_link <> '' --> false

true OR false === true ;)

If I understand what you expect correctly, what you need is:

AND sp_link IS NOT NULL
AND sp_link <> ''

HTH,

--

Jean-Christian Imbeault

That is because '' is not equal to null and therefore the first part
(sp_link is not null) satisfies the requirement.

Avi

On Monday, Jun 9, 2003, at 14:48 America/Chicago, Fernando Papa wrote:

I think that is because if sp_link = '' then sp_link is not null.

There was another thread recently about the null vs ''.

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

www.UrbanaSoft.com

----- Original Message -----
From: "Fernando Papa" <fpapa@claxson.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, June 09, 2003 12:48 PM
Subject: [GENERAL] Strange result with select/not null/OR

Hi all!

I get a strange result with this query:

SELECT count(*)
FROM CONT_CAT
WHERE id_instal = 2
and id_cat = 2
and (sp_link IS NOT NULL OR sp_link <> '')

I have one row with an empty value ('') on sp_link, so, when I execute
the query expect 0 on the result.
I don't know why the result is 1.
If I only put the last part ("sp_link <> ''") everithing goes ok.
But I need to check both conditions, IF is null and IF is empty. And I
can't understand why doesn't work.

Any ideas?

Thanks in advance!

--
Fernando O. Papa
DBA

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster