pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednot null - trivial, unexpected behavior
10+ messages / 5 participants
[nested] [flat]
* not null - trivial, unexpected behavior
@ 2001-06-16 15:16 John Scott <jmscott@yahoo.com>
2001-06-16 15:28 ` Re: not null - trivial, unexpected behavior Rene Pijlman <reageer.in@de.nieuwsgroep>
2001-06-16 16:11 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
2001-06-16 16:21 ` Re: not null - trivial, unexpected behavior Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 10+ messages in thread
From: John Scott @ 2001-06-16 15:16 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: not null - trivial, unexpected behavior
2001-06-16 15:16 not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
@ 2001-06-16 15:28 ` Rene Pijlman <reageer.in@de.nieuwsgroep>
2 siblings, 0 replies; 10+ messages in thread
From: Rene Pijlman @ 2001-06-16 15:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: not null - trivial, unexpected behavior
2001-06-16 15:16 not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
@ 2001-06-16 16:11 ` Peter Eisentraut <peter_e@gmx.net>
2001-06-16 16:23 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
2 siblings, 1 reply; 10+ messages in thread
From: Peter Eisentraut @ 2001-06-16 16:11 UTC (permalink / raw)
To: john@august.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: not null - trivial, unexpected behavior
2001-06-16 15:16 not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
2001-06-16 16:11 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
@ 2001-06-16 16:23 ` Peter Eisentraut <peter_e@gmx.net>
2001-06-16 22:23 ` Re: not null - trivial, unexpected behavior Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 10+ messages in thread
From: Peter Eisentraut @ 2001-06-16 16:23 UTC (permalink / raw)
To: john@august.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: not null - trivial, unexpected behavior
2001-06-16 15:16 not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
2001-06-16 16:11 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
2001-06-16 16:23 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
@ 2001-06-16 22:23 ` Josh Berkus <josh@agliodbs.com>
2001-06-16 23:26 ` Re: not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Josh Berkus @ 2001-06-16 22:23 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; john@august.com; +Cc: pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: not null - trivial, unexpected behavior
2001-06-16 15:16 not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
2001-06-16 16:11 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
2001-06-16 16:23 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
2001-06-16 22:23 ` Re: not null - trivial, unexpected behavior Josh Berkus <josh@agliodbs.com>
@ 2001-06-16 23:26 ` John Scott <jmscott@yahoo.com>
2001-06-16 23:52 ` Re: not null - trivial, unexpected behavior Josh Berkus <josh@agliodbs.com>
2001-06-17 00:15 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 10+ messages in thread
From: John Scott @ 2001-06-16 23:26 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Peter Eisentraut <peter_e@gmx.net>; john@august.com; +Cc: pgsql-sql
--- 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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: not null - trivial, unexpected behavior
2001-06-16 15:16 not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
2001-06-16 16:11 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
2001-06-16 16:23 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
2001-06-16 22:23 ` Re: not null - trivial, unexpected behavior Josh Berkus <josh@agliodbs.com>
2001-06-16 23:26 ` Re: not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
@ 2001-06-16 23:52 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2001-06-16 23:52 UTC (permalink / raw)
To: john@august.com, Josh Berkus <josh@agliodbs.com>; Peter Eisentraut <peter_e@gmx.net>; john@august.com; +Cc: pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: not null - trivial, unexpected behavior
2001-06-16 15:16 not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
2001-06-16 16:11 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
2001-06-16 16:23 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
2001-06-16 22:23 ` Re: not null - trivial, unexpected behavior Josh Berkus <josh@agliodbs.com>
2001-06-16 23:26 ` Re: not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
@ 2001-06-17 00:15 ` Peter Eisentraut <peter_e@gmx.net>
2001-06-17 01:23 ` Re: not null - trivial, unexpected behavior Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: Peter Eisentraut @ 2001-06-17 00:15 UTC (permalink / raw)
To: john@august.com; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: not null - trivial, unexpected behavior
2001-06-16 15:16 not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
2001-06-16 16:11 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
2001-06-16 16:23 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
2001-06-16 22:23 ` Re: not null - trivial, unexpected behavior Josh Berkus <josh@agliodbs.com>
2001-06-16 23:26 ` Re: not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
2001-06-17 00:15 ` Re: not null - trivial, unexpected behavior Peter Eisentraut <peter_e@gmx.net>
@ 2001-06-17 01:23 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2001-06-17 01:23 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: john@august.com, Josh Berkus <josh@agliodbs.com>; pgsql-sql
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 as
att = 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 that
att = 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: not null - trivial, unexpected behavior
2001-06-16 15:16 not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
@ 2001-06-16 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2001-06-16 16:21 UTC (permalink / raw)
To: john@august.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2001-06-17 01:23 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-16 15:16 not null - trivial, unexpected behavior John Scott <jmscott@yahoo.com>
2001-06-16 15:28 ` Rene Pijlman <reageer.in@de.nieuwsgroep>
2001-06-16 16:11 ` Peter Eisentraut <peter_e@gmx.net>
2001-06-16 16:23 ` Peter Eisentraut <peter_e@gmx.net>
2001-06-16 22:23 ` Josh Berkus <josh@agliodbs.com>
2001-06-16 23:26 ` John Scott <jmscott@yahoo.com>
2001-06-16 23:52 ` Josh Berkus <josh@agliodbs.com>
2001-06-17 00:15 ` Peter Eisentraut <peter_e@gmx.net>
2001-06-17 01:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-16 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox