3

I'm working with an older schema, and don't understand why my delete operation (to clear out orphan records) doesn't work as expected in some cases. In others both of the queries return identical results :-/

For example, I have 3 models: house, office, and address. house and office both have a nullable reference to an address.id, so an address record can belong to either of them but the address itself doesn't indicate which.

-- query 1
DELETE FROM address adr
WHERE
  NOT EXISTS(SELECT * FROM house  H WHERE H.address_id = adr.id) AND
  NOT EXISTS(SELECT * FROM office O WHERE O.address_id = adr.id);

-- query 2 
DELETE FROM address adr
WHERE
  NOT adr.id IN (select address_id from house) AND
  NOT adr.id IN (select address_id from office);

query 1: deletes 3000 records (correct)

query 2: deletes 0 records

What is the obvious problem with query #2? When I check the records deleted by #1 they really do not exist in either house or office.

  • Its painful to come back and look at this, and remember how many times its bit me. Also, how many times I've done the right thing. Somehow this never sticks, especially after a few months in some other domain. Thanks again @Michal Politowski – Andrew Backer Oct 26 '17 at 9:35
12

Your address_id columns contain nulls, and thus you got caught by the three-valued logic of SQL.

https://www.postgresql.org/docs/current/static/functions-subquery.html#functions-subquery-in describes the behaviour of IN:

Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the IN construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values.

And of course NOT applied to null is null, not true, see https://www.postgresql.org/docs/current/static/functions-logical.html

  • You nailed it. I don't do enough day to day work to keep that in mind, sadly. – Andrew Backer Oct 12 '17 at 13:56
1

Well, I hate having to do this, but I figured it out. Its all about the NULLs.

In query 2 I needed to add where address_id is not NULL to each subquery. Those nulls screw up the matching when the value does not exist, though I've never been clear exactly why.

Corrected query:

-- query 2 
DELETE FROM address adr
WHERE
  NOT adr.id IN (select address_id from house where address_id is not NULL) AND
  NOT adr.id IN (select address_id from office where address_id is not NULL);
  • This is a very common "gotcha" to be caught by with NULL values and NOT IN filtering clauses. – David Spillett Oct 12 '17 at 14:00
  • 1
    And you don't have to use this. You can still use NOT EXISTS which doesn't fall in the null trap ;) – ypercubeᵀᴹ Oct 12 '17 at 15:00
  • @ypercubeᵀᴹ true. Sadly an ORM generates that 2nd query so I can't change the form, but I can filter out the nulls. – Andrew Backer Oct 12 '17 at 17:12
  • And there's no possibility for the ORM to use NOT EXISTS? That's bad. Anyway, I feel your pain. I have been using SQLAlchemy and although it's 2 levels above almost any other ORM out there, I often find it hard to make it produce the SQL code I want. – ypercubeᵀᴹ Oct 12 '17 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.