Thread: not null - trivial, unexpected behavior
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/
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
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
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
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
--- 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/
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
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
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
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