From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: pginfo <pginfo@t1.unisoftbg.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Interest IN problem on 7.4
Date: Sat, 13 Dec 2003 11:00:04 -0800 (PST)
Message-ID: <20031213105027.R10402@megazone.bigpanda.com> (raw)
In-Reply-To: <3FDABB58.69A785DA@t1.unisoftbg.com>
References: <3FDABB58.69A785DA@t1.unisoftbg.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, pginfo@t1.unisoftbg.com
Subject: Re: Interest IN problem on 7.4
In-Reply-To: <20031213105027.R10402@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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