The command
UPDATE dok SET krdokumnr=NULL WHERE krdokumnr NOT in (select dokumnr from dok);
That's a rather dangerous query; I'm pretty sure it updates a lot more than you think.
There is a long standing "bug" in the SQL standard where NOT IN (..., ..., NULL) always evaluates to NULL, causing the WHERE clause to evaluate to FALSE...
AFAIK, the reason this hasn't been fixed is that many people use it as a feature, depending on the buggy behaviour of WHERE.
Some quick examples to show what happens:
select 1 NOT IN (2,3,4,5,NULL); ?column? ----------
(1 row)
select 1 NOT IN (1,2,3,4,5,NULL); ?column? ---------- f (1 row)
select 1 NOT IN (2,3,4,5); ?column? ---------- t (1 row)
I don't think PostgreSQL will end up in an infinite loop because of this, as to my knowledge the subquery is evaluated only once, but the experts here will doubtlesly correct me if I'm wrong.
runs forever. Postgres.exe process takes 90% of CPU time, disk LED is flashing. Platform: Win XP Prof SP2, Postgres 8 dok table has only 7651 rows Killing client application does not help. Only killing postgres.exe process stops computer activity.
CREATE TABLE ou1.dok ( doktyyp char(1), dokumnr numeric(12) NOT NULL DEFAULT nextval('"ou1".dok_dokumnr_seq'::text), krdokumnr numeric(12), ... a lot of other fields CONSTRAINT dok_pkey PRIMARY KEY (dokumnr) ) WITHOUT OIDS;
any idea ?
Andrus.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
-- Alban Hertroys MAG Productions
T: +31(0)53 4346874 F: +31(0)53 4346876 E: alban@xxxxxxxxxxxxxxxxx W: http://www.magproductions.nl
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
