Re: weird results from trivial SELECT statement

Greetings,
I've got a Postgresql-8.4.x instance with a bunch of tables taht have
a text column (called 'active') that can contain any one of the
following values:
NULL
'disabled'
<some other text string>

When I run the following query, it seems to ignore NULL values:
SELECT * FROM mytbl WHERE active!='disabled'

and only returns rows where active!='disabled' AND active IS NOT NULL.
Is postgresql implicitly assuming that I want non-NULL values?

I can provide additional information, if requested.

thanks!

From my knowledge, I think that NULL is not a value, it's rather the absense
of a value.
It doesn't matter if you use = or !=, testing 'disabled' against a NULL
column will always fail.

Why dont you try a WHERE clause like
WHERE active != 'disabled
OR active IS NULL
if you want the NULL records as well.

HTH,

Best,
Oliveiros

----- Original Message -----
From: "Lonni J Friedman" <netllama(at)gmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Sent: Wednesday, April 27, 2011 5:25 PM
Subject: [NOVICE] weird results from trivial SELECT statement

> Greetings,
> I've got a Postgresql-8.4.x instance with a bunch of tables taht have
> a text column (called 'active') that can contain any one of the
> following values:
> NULL
> 'disabled'
> <some other text string>
>
> When I run the following query, it seems to ignore NULL values:
> SELECT * FROM mytbl WHERE active!='disabled'
>
> and only returns rows where active!='disabled' AND active IS NOT NULL.
> Is postgresql implicitly assuming that I want non-NULL values?
>
> I can provide additional information, if requested.
>
> thanks!
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

Thanks for the reply. This corresponds with the behavior that I'm seeing.

On Wed, Apr 27, 2011 at 9:47 AM, Oliveiros d'Azevedo Cristina
<oliveiros(dot)cristina(at)marktest(dot)pt> wrote:
> From my knowledge, I think that NULL is not a value, it's rather the absense
> of a value.
> It doesn't matter if you use = or !=,  testing 'disabled' against a NULL
> column will always fail.
>
> Why dont you try a WHERE clause like
> WHERE active != 'disabled
> OR active IS NULL
> if you want the NULL records as well.
>
> HTH,
>
>
> Best,
> Oliveiros
>
>
> ----- Original Message ----- From: "Lonni J Friedman" <netllama(at)gmail(dot)com>
> To: <pgsql-novice(at)postgresql(dot)org>
> Sent: Wednesday, April 27, 2011 5:25 PM
> Subject: [NOVICE] weird results from trivial SELECT statement
>
>
>> Greetings,
>> I've got a Postgresql-8.4.x instance with a bunch of tables taht have
>> a text column (called 'active') that can contain any one of the
>> following values:
>> NULL
>> 'disabled'
>> <some other text string>
>>
>> When I run the following query, it seems to ignore NULL values:
>> SELECT * FROM mytbl WHERE active!='disabled'
>>
>> and only returns rows where active!='disabled' AND active IS NOT NULL.
>> Is postgresql implicitly assuming that I want non-NULL values?
>>
>> I can provide additional information, if requested.
>>
>> thanks!

On Apr 27, 2011, at 9:25 AM, Lonni J Friedman wrote:

> Greetings,
> I've got a Postgresql-8.4.x instance with a bunch of tables taht have
> a text column (called 'active') that can contain any one of the
> following values:
> NULL
> 'disabled'
> <some other text string>
>
> When I run the following query, it seems to ignore NULL values:
> SELECT * FROM mytbl WHERE active!='disabled'
>
> and only returns rows where active!='disabled' AND active IS NOT NULL.
> Is postgresql implicitly assuming that I want non-NULL values?
>
> I can provide additional information, if requested.
>
> thanks!

Unfortunately, there is only one NULL and it can take on several meanings. Because of this, most databases will not perform comparison operations on NULL. NULL is not comparable to anything, including itself. NULL == NULL will return FALSE on most systems.

Some of the interpretations of NULL include, it is unknown if there should be data here, there is no data here, and there should be data here but we don't know what it is. This generates enough controversy to merit a few PhD theses. Yes, it is the database world it is possible to write a PhD thesis on nothing.

Brent D.

(Please do not top-post.)

Lonni J Friedman wrote:
>>> Greetings,
>>> I've got a Postgresql-8.4.x instance with a bunch of tables taht have
>>> a text column (called 'active') that can contain any one of the
>>> following values:
>>> NULL
>>> 'disabled'
>>> <some other text string>
>>>
>>> When I run the following query, it seems to ignore NULL values:
>>> SELECT * FROM mytbl WHERE active!='disabled'
>>>
>>> and only returns rows where active!='disabled' AND active IS NOT NULL.
>>> Is postgresql [sic] implicitly assuming that I want non-NULL values?

This is just one of the very basic rules of SQL, which are important to know
if you're going to use Postgres successfully. It's kind of hard to program
successfully with a computer language if you don't know the language.

The answer is in the manual, should you have an interest in RTFM.
<http://www.postgresql.org/docs/9.0/interactive/functions-comparison.html>
"Do not write expression = NULL because NULL is not "equal to" NULL. (The null
value represents an unknown value, and it is not known whether two unknown
values are equal.) This behavior conforms to the SQL standard.

"... Ordinary comparison operators yield null (signifying "unknown"), not true
or false, when either input is null. For example, 7 = NULL yields null. ..."

For the ordinary comparison operators in SQL, { FALSE, TRUE, NULL } works like
{ FALSE, TRUE, UNKNOWN } in a 3-valued logic.

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

Brent Dombrowski wrote:
> Lonni J Friedman wrote:
>
>> Greetings,
>> I've got a Postgresql-8.4.x instance with a bunch of tables taht have
>> a text column (called 'active') that can contain any one of the
>> following values:
>> NULL
>> 'disabled'
>> <some other text string>
>>
>> When I run the following query, it seems to ignore NULL values:
>> SELECT * FROM mytbl WHERE active!='disabled'
>>
>> and only returns rows where active!='disabled' AND active IS NOT NULL.
>> Is postgresql implicitly assuming that I want non-NULL values?
>>
>> I can provide additional information, if requested.

> Unfortunately, there is only one NULL and it can take on several meanings.
> Because of this, most databases will not perform comparison operations on NULL.
> NULL is not comparable to anything, including itself.
> NULL == NULL will return FALSE on most systems.

"Most" databases? "Most" systems?

*ALL* SQL-compliant products.

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg