Thread: BUG #4021: UUID NULL and not equal query failed

BUG #4021: UUID NULL and not equal query failed

From
"Yann ROBIN"
Date:
The following bug has been logged online:

Bug reference:      4021
Logged by:          Yann ROBIN
Email address:      yann.robin@withart.fr
PostgreSQL version: 8.3.0 build 140
Operating system:   Windows XP SP2
Description:        UUID NULL and not equal query failed
Details:

Hi,

I've tried to do a not like query on a UUID field and the row that i'd like
to get has a NULL value for the UUID field, it gives me no result. The
request :

SELECT *
FROM t_account
WHERE Update_Timestamp > '2008-03-09 12:35:10.309000' AND Update_Timestamp
<= '2008-03-09 12:35:37.861000' AND Update_Id !=
'48012851-3aa4-9940-9ab4-b8333da6b45b'

0 result.

The same request without the Update_Id condition :

SELECT *
FROM t_account
WHERE Update_Timestamp > '2008-03-09 12:35:10.309000' AND Update_Timestamp
<= '2008-03-09 12:35:37.861000'

1 result. The Update_Id field is null.

The same request but with a OR IS NULL condition :

SELECT *
FROM t_account
WHERE Update_Timestamp > '2008-03-09 12:35:10.309000' AND Update_Timestamp
<= '2008-03-09 12:35:37.861000'
AND (Update_Id != '48012851-3aa4-9940-9ab4-b8333da6b45b' OR Update_Id IS
NULL)

1 result.

So my conclusion is that the NOT EQUAL operator doesn't match how it should
the NULL value field.
Is it a bug or a wanted behavior ?


--
Yann

Re: BUG #4021: UUID NULL and not equal query failed

From
"Heikki Linnakangas"
Date:
Yann ROBIN wrote:
> So my conclusion is that the NOT EQUAL operator doesn't match how it should
> the NULL value field.
> Is it a bug or a wanted behavior ?

It's normal behavior, required by SQL standard, and not specific to
UUIDs. See also
http://www.postgresql.org/docs/8.3/interactive/functions-comparison.html.


--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: BUG #4021: UUID NULL and not equal query failed

From
"Yann ROBIN"
Date:
Thank you, you're right and I should have none !

-----Message d'origine-----
De=A0: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org] De la part de Heikki Linnakangas
Envoy=E9=A0: dimanche 9 mars 2008 15:24
=C0=A0: Yann ROBIN
Cc=A0: pgsql-bugs@postgresql.org
Objet=A0: Re: [BUGS] BUG #4021: UUID NULL and not equal query failed

Yann ROBIN wrote:
> So my conclusion is that the NOT EQUAL operator doesn't match how it
should
> the NULL value field.
> Is it a bug or a wanted behavior ?

It's normal behavior, required by SQL standard, and not specific to=20
UUIDs. See also=20
http://www.postgresql.org/docs/8.3/interactive/functions-comparison.html.


--=20
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

--=20
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs