pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy do the two queries below return different results?
2+ messages / 2 participants
[nested] [flat]
* Why do the two queries below return different results?
@ 2003-07-16 20:52 Warren Little <wlittle@securitylending.com>
0 siblings, 1 reply; 2+ messages in thread
From: Warren Little @ 2003-07-16 20:52 UTC (permalink / raw)
To: pgsql-sql
Look at the following two queries.
select casepid, origpid
from virbcase
where date_trunc('day', origdt) >= '2003-07-01'
and date_trunc('day', origdt) <= '2003-07-31'
and origpid in (select pid from party where partyid in ('00339',
'00310',
'00320'))
and not exists (select pid from casecombo where casepid = secondpid)
select casepid, origpid
from virbcase
where date_trunc('day', origdt) >= '2003-07-01'
and date_trunc('day', origdt) <= '2003-07-31'
and origpid in (select pid from party where partyid in ('00339',
'00310',
'00320'))
and casepid not in (select secondpid from casecombo)
The second query is broken and I don't understand why.
Correlating the subquery as is done in the first query fixes it.
--
Warren Little
Senior Vice President
Secondary Marketing
Security Lending Wholesale, LC
www.securitylending.com
Tel: 866-369-7763
Fax: 866-849-8082
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Why do the two queries below return different results?
@ 2003-07-20 04:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Warren Little <wlittle@securitylending.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2003-07-20 04:54 UTC (permalink / raw)
To: Warren Little <wlittle@securitylending.com>; +Cc: pgsql-sql
Warren Little <wlittle@securitylending.com> writes:
> Look at the following two queries.
> ...
> and not exists (select pid from casecombo where casepid = secondpid)
> ...
> and casepid not in (select secondpid from casecombo)
> The second query is broken and I don't understand why.
I'll bet there are some NULL values for secondpid in casecombo.
The behavior of NOT IN with NULLs is fairly unintuitive :-(
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-07-20 04:54 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-16 20:52 Why do the two queries below return different results? Warren Little <wlittle@securitylending.com>
2003-07-20 04:54 ` 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