Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 10C9063271F for ; Wed, 28 Jan 2009 14:20:59 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 62377-06 for ; Wed, 28 Jan 2009 14:20:47 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227]) by mail.postgresql.org (Postfix) with ESMTP id E7FEF632713 for ; Wed, 28 Jan 2009 14:20:41 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.int.journyx.com (Postfix) with ESMTP id ED9067E486A; Wed, 28 Jan 2009 12:20:25 -0600 (CST) X-Virus-Scanned: amavisd-new at zimbra.int.journyx.com Received: from mail.int.journyx.com ([127.0.0.1]) by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id AtJJBsbz6N0k; Wed, 28 Jan 2009 12:20:16 -0600 (CST) Received: from whitneys (dhcp-100-188.int.journyx.com [192.168.100.188]) by mail.int.journyx.com (Postfix) with ESMTP id 98C877E4526; Wed, 28 Jan 2009 12:20:16 -0600 (CST) From: "Scott Whitney" To: "'Hoover, Jeffrey'" , "'Kevin Grittner'" , Subject: Re: Strange query problem... Date: Wed, 28 Jan 2009 12:21:35 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 In-Reply-To: <3EC2264E4927524DB3F1C0425831CCA54748B3@EXCHANGE.TIGR.ORG> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350 Thread-Index: AcmBcxseeByGMX0mRt6COSnZQ8x/YwAAWiiAAAAtfLA= Message-Id: <20090128182016.98C877E4526@mail.int.journyx.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/150 X-Sequence-Number: 31197 So, you're sayin' I ain't crazy? :) -----Original Message----- From: Hoover, Jeffrey [mailto:jhoover@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 that behavior, but heres the proof: cameradb_dev=# select * from a1; i ------- one three five two four (5 rows) cameradb_dev=# select * from a2; j | i ---+------ 0 | 2 | two 4 | four (3 rows) cameradb_dev=# select * from a1 where i not in (select i from a2); i --- (0 rows) cameradb_dev=# select * from a1 where i not in (select coalesce(i,'') from a2); i ------- one three five (3 rows) cameradb_dev=# -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Kevin Grittner Sent: Wednesday, January 28, 2009 1:05 PM To: Scott Whitney; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Strange query problem... >>> "Scott Whitney" 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 | Modifiers > -------------------+------------------------+--------------------------- ---- > id_time_rec | character varying(38) | The column in punch_time_recs is null capable. Try using NOT EXISTS. The SQL spec requires the NOT IN to be the equivalent of a "not equals" test for all entries, and you can't say that any given value is not equal to NULL, since NULL can mean that there is a value but you don't know it. The semantics of NOT EXISTS are subtly different here -- it means there aren't any rows known to have the value. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin