Um. How is this possible? Am I doing something very, very stupid, here?
mydb=# select * from time_recs where id_time_rec not in (select id_time_rec
from punch_time_recs);
id_time_rec | id_user | record_date | id_code_task | id_code_pay_type |
id_project | time_amount | comment | commit_state |
Scott Whitney swhit...@journyx.com wrote:
Um. How is this possible?
mydb=# select * from time_recs where id_time_rec not in (select
id_time_rec from punch_time_recs);
(0 rows)
Table public.punch_time_recs
Column | Type |
So, you're sayin' I ain't crazy? :)
-Original Message-
From: Hoover, Jeffrey [mailto:jhoo...@jcvi.org]
Sent: Wednesday, January 28, 2009 12:18 PM
To: Kevin Grittner; Scott Whitney; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Strange query problem...
Wow! I would never have expected
Wow. This just boggles my mind, but there it is. Here's Oracle:
SQL select i from a1;
I
one
two
three
four
five
SQL select i from a2;
I
two
four
SQL select i from a1 where i not in (select i from a2);
no rows selected
Or, if you want the exact
On Wed, 2009-01-28 at 12:38 -0600, Scott Whitney wrote:
Wow. This just boggles my mind, but there it is. Here's Oracle:
This looks like a NULL vs '' issue. Am I wrong?
Joshua D. Drake
SQL select i from a1;
I
one
two
three
four
five
SQL select i from a2;
Adding in where id_time_rec is not null does solve the problem. SQL server
appears to be the only one that natively says yeah, he doesn't care about
those.
I'd argue, now that I'm understanding it, that the query is doing what I
asked. Just not what I wanted. :)
-Original Message-
From:
Hoover, Jeffrey jhoo...@jcvi.org wrote:
In Sybase?
Sybase ASE defaults to the same behavior as Microsoft SQL Server, but
they have a configuration option to yield standards compliant behavior
in this regard (SET ANSINULL ON).
-Kevin
--
Sent via pgsql-admin mailing list
Joshua D. Drake j...@commandprompt.com writes:
This looks like a NULL vs '' issue. Am I wrong?
No, it's a NULL vs NOT IN issue. Specifically, if the subquery yields
any NULLs and the comparison operator is strict (which nearly all are)
then it's impossible to get a TRUE result from the NOT IN