pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInterest IN problem on 7.4
3+ messages / 2 participants
[nested] [flat]
* Interest IN problem on 7.4
@ 2003-12-13 07:10 pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 3+ messages in thread
From: pginfo @ 2003-12-13 07:10 UTC (permalink / raw)
To: pgsql-sql
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.
In my case A_CARS.IDS is name and also OC.IDS_CAR_REAL.
It is interest that the last update is working well and the first one
not.
Pls, comment this problem.
regards,
ivan.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Interest IN problem on 7.4
@ 2003-12-13 19:00 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2003-12-13 19:00 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Interest IN problem on 7.4
@ 2003-12-15 15:53 pginfo <pginfo@t1.unisoftbg.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: pginfo @ 2003-12-15 15:53 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-12-15 15:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-13 07:10 Interest IN problem on 7.4 pginfo <pginfo@t1.unisoftbg.com>
2003-12-13 19:00 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-12-15 15:53 ` pginfo <pginfo@t1.unisoftbg.com>
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