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 DBB0BD1DDA9 for ; Sat, 13 Dec 2003 19:00:32 +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 23546-07 for ; Sat, 13 Dec 2003 15:00:10 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 17C1DD1DDD0 for ; Sat, 13 Dec 2003 15:00:04 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 07C883548D; Sat, 13 Dec 2003 11:00:05 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 0629F35266; Sat, 13 Dec 2003 11:00:05 -0800 (PST) Date: Sat, 13 Dec 2003 11:00:04 -0800 (PST) From: Stephan Szabo To: pginfo Cc: "pgsql-sql@postgresql.org" Subject: Re: Interest IN problem on 7.4 In-Reply-To: <3FDABB58.69A785DA@t1.unisoftbg.com> Message-ID: <20031213105027.R10402@megazone.bigpanda.com> References: <3FDABB58.69A785DA@t1.unisoftbg.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/100 X-Sequence-Number: 16266 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.