pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedno cond returns diff res than cond or !cond
3+ messages / 3 participants
[nested] [flat]
* no cond returns diff res than cond or !cond
@ 2001-11-12 14:26 Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 2 replies; 3+ messages in thread
From: Markus Bertheau @ 2001-11-12 14:26 UTC (permalink / raw)
To: pgsql-sql
Good day,
feline=> select version();
version
---------------------------------------------------------------
PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2
(1 row)
feline=> select count(*) from produkte;
count
-------
36405
(1 row)
feline=> select count(*) from produkte where artnummer != NULL or
artnummer = NULL;
count
-------
36347
(1 row)
cenes_test=> select version();
version
-------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
cenes_test=> select count(*) from produkte;
count
-------
36384
(1 row)
cenes_test=> select count(*) from produkte where artnummer = NULL or
artnummer != NULL;
count
-------
36326
(1 row)
2nd query 58 less than first.
bug?
Markus Bertheau
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: no cond returns diff res than cond or !cond
@ 2001-11-12 14:39 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2001-11-12 14:39 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
On 12 Nov 2001, Markus Bertheau wrote:
> cenes_test=> select version();
> version
> -------------------------------------------------------------
> PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
> (1 row)
>
> cenes_test=> select count(*) from produkte;
> count
> -------
> 36384
> (1 row)
>
> cenes_test=> select count(*) from produkte where artnummer = NULL or
> artnummer != NULL;
> count
> -------
> 36326
> (1 row)
>
> 2nd query 58 less than first.
>
> bug?
Probably not. You don't want to compare to NULL (although there is a
hack which makes an explicit = NULL into the correct comparison), you
want to use IS NULL and IS NOT NULL. Comparing to NULL returns an
unknown (neither true nor false) and negating that comparison still
returns unknown. The hack is in there for compatibility with an
MS Access feature, and I think is optional in 7.2, and turns = NULL
into IS NULL, there's no equivalent hack for != NULL AFAIK.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: no cond returns diff res than cond or !cond
@ 2001-11-12 15:46 Haller Christoph <ch@rodos.fzk.de>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 0 replies; 3+ messages in thread
From: Haller Christoph @ 2001-11-12 15:46 UTC (permalink / raw)
To: twanger@bluetwanger.de; +Cc: pgsql-sql
Hi Markus,
AFAIK the SQL standard says for querying columns to be NULL
resp. NOT NULL
select count(*) from produkte where artnummer is NULL or
artnummer is NOT NULL ;
Please try and let me know about your results.
Regards, Christoph
>
> Good day,
>
> feline=> select version();
> version
> ---------------------------------------------------------------
> PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2
> (1 row)
>
> feline=> select count(*) from produkte;
> count
> -------
> 36405
> (1 row)
>
> feline=> select count(*) from produkte where artnummer != NULL or
> artnummer = NULL;
> count
> -------
> 36347
> (1 row)
>
> cenes_test=> select version();
> version
> -------------------------------------------------------------
> PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
> (1 row)
>
> cenes_test=> select count(*) from produkte;
> count
> -------
> 36384
> (1 row)
>
> cenes_test=> select count(*) from produkte where artnummer = NULL or
> artnummer != NULL;
> count
> -------
> 36326
> (1 row)
>
> 2nd query 58 less than first.
>
> bug?
>
> Markus Bertheau
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-11-12 15:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-12 14:26 no cond returns diff res than cond or !cond Markus Bertheau <twanger@bluetwanger.de>
2001-11-12 14:39 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-12 15:46 ` Haller Christoph <ch@rodos.fzk.de>
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