Thread: Interest IN problem on 7.4
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.
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.
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.