Postgres Pro
Downloads
Home   >   mailing lists

Thread: IS NULL seems to return false, even when parameter is NULL

IS NULL seems to return false, even when parameter is NULL

From
Lucazeo
Date:
I have a strange problem with the following condition in a SELECT:
AND (("TableData" = "inDate") OR ("inDate" IS NULL))

it works perfectly when the input date in the function (inDate) matchs
a date in the table, but it does not work when the parameter inDate is
NULL.
I recall the function with pgadmin writing NULL as paramater.
It seems that ("inDate" IS NULL) never returns true, even when the
parameter is null...
What's wrong?

Lucazeo

Re: IS NULL seems to return false, even when parameter is NULL

From
Tom Lane
Date:
Lucazeo <lucazeo@katamail.com> writes:
> It seems that ("inDate" IS NULL) never returns true, even when the
> parameter is null...

Exceedingly unlikely.

> What's wrong?

You have not provided enough context for anyone to guess.

            regards, tom lane

Re: IS NULL seems to return false, even when parameter is NULL

From
"Andrus"
Date:
>I have a strange problem with the following condition in a SELECT:
> AND (("TableData" = "inDate") OR ("inDate" IS NULL))
>
> it works perfectly when the input date in the function (inDate) matchs
> a date in the table, but it does not work when the parameter inDate is
> NULL.
> I recall the function with pgadmin writing NULL as paramater.
> It seems that ("inDate" IS NULL) never returns true, even when the
> parameter is null...
> What's wrong?

Order is wrong, change it to

> AND (("inDate" IS NULL) OR ("TableData" = "inDate") )

PostgreSQL OR is not commutative if left operand evaluates to NULL.

Andrus.

Re: IS NULL seems to return false, even when parameter is NULL

From
Richard Huxton
Date:
Andrus wrote:
>> I have a strange problem with the following condition in a SELECT:
>> AND (("TableData" = "inDate") OR ("inDate" IS NULL))
>>
>> it works perfectly when the input date in the function (inDate) matchs
>> a date in the table, but it does not work when the parameter inDate is
>> NULL.
>> I recall the function with pgadmin writing NULL as paramater.
>> It seems that ("inDate" IS NULL) never returns true, even when the
>> parameter is null...
>> What's wrong?
>
> Order is wrong, change it to
>> AND (("inDate" IS NULL) OR ("TableData" = "inDate") )
>
> PostgreSQL OR is not commutative if left operand evaluates to NULL.

Seems to work here:

select (true or null) as a, (null or true) as b, (null or false) as c,
(false or null) as d;
 a | b | c | d
---+---+---+---
 t | t |   |
(1 row)


--
  Richard Huxton
  Archonet Ltd

Re: IS NULL seems to return false, even when parameter is NULL

From
"Andrus"
Date:
Richard,

> Seems to work here:
>
> select (true or null) as a, (null or true) as b, (null or false) as c,
> (false or null) as d;
> a | b | c | d
> ---+---+---+---
> t | t |   |
> (1 row)

I'm sorry for the wrong information.

Andrus.

Re: IS NULL seems to return false, even when parameter is NULL

From
Tom Lane
Date:
"Andrus" <kobruleht2@hot.ee> writes:
> PostgreSQL OR is not commutative if left operand evaluates to NULL.

This is nonsense.

            regards, tom lane