Thread: BUG #2417: bug for finding string in column

The following bug has been logged online:

Bug reference:      2417
Logged by:          James
Email address:      james@mercstudio.com
PostgreSQL version: 8.1
Operating system:   Microsoft Windows XP Professional
Description:        bug for finding string in column
Details:

hi,

i've encounter bug where when a field is empty / null (type varchar(255) ),
when i use the sql query <> 'myvalue', it seems to be false all the time.


example:
select * from user_profile where acc_lock <> 'Y'

where the acc_lock field for all rows were empty...

2nd, hopefully that postgresql can be flexible to accept empty '' as null
search as well...

On Tue, 2 May 2006, James wrote:

>
> The following bug has been logged online:
>
> Bug reference:      2417
> Logged by:          James
> Email address:      james@mercstudio.com
> PostgreSQL version: 8.1
> Operating system:   Microsoft Windows XP Professional
> Description:        bug for finding string in column
> Details:
>
> hi,
>
> i've encounter bug where when a field is empty / null (type varchar(255) ),
> when i use the sql query <> 'myvalue', it seems to be false all the time.

If it's null, it's actually returning unknown (not true or false) and
as far as we know that's not a bug because that's how SQL defines the
comparison operators.

On Tue, May 02, 2006 at 10:34:12 +0000,
  James <james@mercstudio.com> wrote:
>
> i've encounter bug where when a field is empty / null (type varchar(255) ),
> when i use the sql query <> 'myvalue', it seems to be false all the time.

Empty and NULL are not the same thing. NULL <> to any value evaluates
to NULL which will not satisfy a WHERE clause. This is according to the
SQL (i.e. it's not a bug).