X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C16EFD1DCEE for ; Mon, 15 Dec 2003 17:04:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 79446-01 for ; Mon, 15 Dec 2003 13:04:24 -0400 (AST) Received: from mail.unisoftbg.com (mail.unisoftbg.com [194.12.229.207]) by svr1.postgresql.org (Postfix) with ESMTP id 05CAED1B4C8 for ; Mon, 15 Dec 2003 13:04:12 -0400 (AST) Received: (qmail 9802 invoked by uid 507); 15 Dec 2003 18:09:28 -0000 Received: from unknown (HELO t1.unisoftbg.com) (pginfo%t1.unisoftbg.com@194.12.229.193) by 0 with SMTP; 15 Dec 2003 18:09:28 -0000 Message-ID: <3FDDD8F9.8EF3E45B@t1.unisoftbg.com> Date: Mon, 15 Dec 2003 16:53:29 +0100 From: pginfo X-Mailer: Mozilla 4.03 [en] (Win95; I) MIME-Version: 1.0 To: Stephan Szabo Cc: "pgsql-sql@postgresql.org" Subject: Re: Interest IN problem on 7.4 References: <3FDABB58.69A785DA@t1.unisoftbg.com> <20031213105027.R10402@megazone.bigpanda.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/106 X-Sequence-Number: 16272 Hi, thanks a lot. All is ok now. regards, ivan. Stephan Szabo wrote: > On Sat, 13 Dec 2003, pginfo wrote: > > > Hi, > > > > I am using pg 7.4. > > > > Pls, see this test: > > > > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > > dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars > > oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) AND > > IDS = 'SOF_9989'; > > UPDATE 0 > > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > > dog_or_free=4 and ids IN ( select oc.ids_car_real from a_oferti > > _cars oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) > > AND IDS = 'SOF_9989'; > > UPDATE 0 > > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > > dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars > > oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964 AND > > OC.IDS_CAR_REAL IS NOT NULL) AND IDS = 'SOF_9989'; > > UPDATE 1 > > > > I think IN is not working correct in this case. > > A NOT IN (subselect) when the subselect contains a NULL cannot ever return > true by specification. > > -------------- > A NOT IN (subselect) -> NOT (A IN (subselect)) > NOT (A IN (subselect)) -> NOT (A = ANY (subselect)) > > The result of A = ANY (subselect) is derived by the application of the > implied comparison predicate, R = RT for every row RT in the subselect. > > If the implied comparison predicate is true for at least one row RT then A > = ANY (subselect) is true. If the subselect is empty or the implied > predicate is false for every row RT then A = ANY (subselect) is false. > Otherwise it is unknown. > > For the one element row RT, A = RT where RT is a NULL returns unknown. > Therefore, we know that it's not an empty subselect (it returns at least > one row containing NULL -- that's our precondition), and that it does not > return false for every row, so A = ANY (subselect) is either true or > unknown depending on whether there's another row which does match, so > NOT(A = ANY(subselect)) is either false or unknown.