Обсуждение: Re: Strange result with select/not null/OR

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:

>
> 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.

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

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

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