Thread: not null - trivial, unexpected behavior

not null - trivial, unexpected behavior

From
John Scott
Date:
the following behaviour seems inconsistent to me.
I can qualify on nulls, but not on nonullness.

This was tested under 7.1.2

create table A
(   a    text,   b    text
);

insert into A(a) values('a1');
insert into A(a, b) values('a2', 'b2');

select count(*) from A where b = null;      /* Returns 1, ok */
select count(*) from A where b != null;     /* Returns 0 ... not ok! */

What am i missing?  Shouldn't those two sets be complimentary?

john


=====
John Scott
Senior Partner
August Associates

email: john@august.com web: http://www.august.com/~jmscott

__________________________________________________
Do You Yahoo!?
Spot the hottest trends in music, movies, and more.
http://buzz.yahoo.com/


Re: not null - trivial, unexpected behavior

From
Peter Eisentraut
Date:
John Scott writes:

> insert into A(a) values('a1');
> insert into A(a, b) values('a2', 'b2');
>
> select count(*) from A where b = null;      /* Returns 1, ok */

no

> select count(*) from A where b != null;     /* Returns 0 ... not ok! */

yes

> What am i missing?  Shouldn't those two sets be complimentary?

They should.  There's a thread about the abnormal behaviour of foo = NULL
in the -hackers archive of last/this(?) week.  You should use 'foo is
[not] null' anyway.

See also

http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/functions-comparison.html

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter



Re: not null - trivial, unexpected behavior

From
Tom Lane
Date:
John Scott <jmscott@yahoo.com> writes:
> select count(*) from A where b = null;      /* Returns 1, ok */
> select count(*) from A where b != null;     /* Returns 0 ... not ok! */

Uh ... there have been several threads about this just in the past
couple days.  See for example
http://www.ca.postgresql.org/mhonarc/pgsql-sql/2001-06/msg00102.html
and followups.
        regards, tom lane


Re: not null - trivial, unexpected behavior

From
Peter Eisentraut
Date:
I wrote:

[ x = NULL vs. x != NULL ]
> > What am i missing?  Shouldn't those two sets be complimentary?
>
> They should.

Correction:  Each Boolean expression can have one of *three* values: true,
false, unknown (null).  So those two sets will not be "complimentary" in
the traditional sense because both expressions _should_ (modulo the
information I pointed you to) return "unknown".

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter



Re: not null - trivial, unexpected behavior

From
"Josh Berkus"
Date:
Peter,

> Correction:  Each Boolean expression can have one of *three* values:
> true,
> false, unknown (null).  So those two sets will not be "complimentary"
> in
> the traditional sense because both expressions _should_ (modulo the
> information I pointed you to) return "unknown".

I agree pretty strongly with Peter here ... the " = NULL " functionality
should go.  I've already had to re-examine some of my functions for
unexpected results due to = NULL possibly evaluating to TRUE, an outcome
for which I did not plan.

Plus it leads those new to SQL92 into trouble, as it did with John :-)

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


Re: not null - trivial, unexpected behavior

From
John Scott
Date:
--- Josh Berkus <josh@agliodbs.com> wrote:
> Peter,
> 
> > Correction:  Each Boolean expression can have one of *three* values:
> > true,
> > false, unknown (null).  So those two sets will not be "complimentary"
> > in
> > the traditional sense because both expressions _should_ (modulo the
> > information I pointed you to) return "unknown".
> 
> I agree pretty strongly with Peter here ... the " = NULL " functionality
> should go.  I've already had to re-examine some of my functions for
> unexpected results due to = NULL possibly evaluating to TRUE, an outcome
> for which I did not plan.
> 
> Plus it leads those new to SQL92 into trouble, as it did with John :-)
> 
> -Josh Berkus

not a showstopper for me.

but, as i understand the sql92 standard, both att = null and att != null
are NOT sql92.  perhaps they're just remnants of the Cish nature of quel.

seems to me that if att != null is unknown, then att = null
should be unknown as well.  or at least documented as such.

a practical example of where this becomes a problem
is a query like
   select * from t where a != :aqual

to do this properly for a null 'aqual' in sql92 i need to change '=' to 
'is not null', which is considerably more complex than simple variable
substitution.

i guess a summary question would be : is a predicate like
"att = null" even predicatable and, if not, 
why should any unpredicatble query
be accepted by the database engine?

-j


__________________________________________________
Do You Yahoo!?
Spot the hottest trends in music, movies, and more.
http://buzz.yahoo.com/


Re: not null - trivial, unexpected behavior

From
"Josh Berkus"
Date:
John,

> but, as i understand the sql92 standard, both att = null and att !=
> null
> are NOT sql92. 

You are correct.

> perhaps they're just remnants of the Cish nature of
> quel.

Tom seems to indicate that = Null is a result of (possibly misguided)
desire for compatibility with Microsoft ODBC.

> seems to me that if att != null is unknown, then att = null
> should be unknown as well.  or at least documented as such.

Yup.  Absolutely.  Any expression which includes a NULL should evaluate
to NULL.  If the expression includes an unknown value, its result must
also remain unknown.
> a practical example of where this becomes a problem
> is a query like
> 
>     select * from t where a != :aqual
> 
> to do this properly for a null 'aqual' in sql92 i need to change '='
> to 
> 'is not null', which is considerably more complex than simple
> variable
> substitution.

If this is a concern, then set the column to NOT NULL and force a value
to be inserted ... 0 for numerical columns, '' for VARCHAR, and
'1900-01-01' for dates.

Hmmm ... what is a good "no date" value for dates?  Depends on the
application, I suppose.  

> i guess a summary question would be : is a predicate like
> "att = null" even predicatable and, if not, 
> why should any unpredicatble query
> be accepted by the database engine?

It *is* predictable in that "att = null" evaluates to False, regardless
of the value of att.  att cannot be equal to Null since Null is the
"unknown value".

If you wish to test two values as equivalent because they both equal
NULL, then you can do like the following:

IF (COALESCE(att, 'NULL')) = (COALESCE(pbs, 'NULL')) 

or:

IF (att = pbs) OR (att IS NULL and pbs IS NULL) 

All of the troubles you mention above are the reason why some DB
theorists are opposed to the use of NULLs at all.  However, NULLs are
part of the SQL standard, as is all null tests = False and all null
computations = Null.  We gain little by flaunting the standard.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


Re: not null - trivial, unexpected behavior

From
Peter Eisentraut
Date:
John Scott writes:

> but, as i understand the sql92 standard, both att = null and att != null
> are NOT sql92.

They are.  We just don't implement att = null right because of reasons
that can be found in the archives.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter



Re: not null - trivial, unexpected behavior

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> John Scott writes:
>> but, as i understand the sql92 standard, both att = null and att != null
>> are NOT sql92.

> They are.  We just don't implement att = null right because of reasons
> that can be found in the archives.

In a very narrow sense, they're not SQL92, because SQL92 doesn't
actually allow an unadorned keyword NULL to appear in arbitrary
expression contexts.  You could legally write the expression asatt = CAST (NULL AS type-of-att)
and then the required result would always be NULL, a/k/a UNKNOWN
(nb. this is NOT the same as FALSE).  And indeed that's what Postgres
will produce if you do it that way.

In practice, since Postgres extends the spec to allow the unadorned
keyword NULL to appear in arbitrary expressions (with implicit
resolution of the datatype of the null), you'd expect thatatt = NULL
would behave the same as if the NULL came from a CAST, evaluation of
a data value, etc.  But it doesn't, for reasons that have been
discussed already.
        regards, tom lane


Re: not null - trivial, unexpected behavior

From
Rene Pijlman
Date:
jmscott@yahoo.com (John Scott) schreef:
>select count(*) from A where b = null;      /* Returns 1, ok */
>select count(*) from A where b != null;     /* Returns 0 ... not ok! */
>
>What am i missing?  

The documentation :-)

"To check whether a value is or is not NULL, use the constructs 

expression IS NULL
expression IS NOT NULL

Do not use expression = NULL because NULL is not "equal to" NULL.
(NULL represents an unknown value, so it is not known whether two
unknown values are equal.) Postgres presently converts x = NULL
clauses to x IS NULL to allow some broken client applications (such as
Microsoft Access) to work, but this may be discontinued in a future
release."

http://www.postgresql.org/users-lounge/docs/7.1/postgres/functions-comparison.html

Regards,
René Pijlman
http://www.applinet.nl