CODEWITHSUNDEEP
postgresql
Graham
Graham

Reputation: 661

Postgresql "Missing From-Clause Error"

I have two versions of PostgreSQL (8.0.4 / 8.1.4) and have noticed that in 8.1.4 I get a "Missing From-Clause Error" for the following statement:

DELETE FROM MDFUSERGROUPREL_DELETED 
WHERE userid = MDFUSERGROUPREL.userid 
AND usergroupid = MDFUSERGROUPREL.usergroupid 
AND userid=?

As you cannot do a DELETE with SELECT statement in 8.0.4 I am using a direct table reference instead, but this seems to be causing a problem in 8.1.4

Is anyone able to confirm this behaviour and if possible provide a work around?

I know the versions i am using are old and not supported this is a environmental constraint so no I can't just upgrade it.

Upvotes: 3

Views: 2690

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324951

You're relying on a behaviour of old versions of PostgreSQL which used to implicitly add FROM clauses when tables were referenced that weren't listed in FROM. You'll be able to see this in an EXPLAIN of your query.

This misfeature was removed from PostgreSQL. It caused accidental cross-products on typos and was generally a very bad idea. You can re-enable it as a temporary workaround by setting the add_missing_from configuration parameter to true either with a SET statement before the problem query or in postgresql.conf. This backwards compatibility workaround was removed in 9.0, so you cannot use it to get your app working with 9.0 or above. I've intentionally linked to the 8.4 version of the documentation because the parameter was removed from later versions.

See this mailing list discussion.

The cause of the problem in this query is that you're doing an implicit join on MDFUSERGROUPREL. You should be naming this table explicitly in your USING clause in the DELETE statement.

Here's a demo showing the problem and workaround: http://sqlfiddle.com/#!11/6c4b3/4 ... and here's a demo showing how to do it correctly two different ways: http://sqlfiddle.com/#!11/6c4b3/7 . The 1st is a simple fix that just adds the missing from clause to the query and fixes the resulting column name conflicts:

DELETE FROM MDFUSERGROUPREL_DELETED
USING MDFUSERGROUPREL
WHERE MDFUSERGROUPREL_DELETED.userid = MDFUSERGROUPREL.userid 
AND MDFUSERGROUPREL_DELETED.usergroupid = MDFUSERGROUPREL.usergroupid 
AND MDFUSERGROUPREL_DELETED.userid=1;

You will find that the latter two examples result in the same query plan at least on modern versions of PostgreSQL, so it doesn't make a difference whether you use EXISTS or a join.

I've followed your query's original style in not using table aliases, but I strongly suggest that you do use aliases for readability. See @wildplasser's example.

Upvotes: 4

wildplasser
wildplasser

Reputation: 44250

Not understanding your exact intentions, it still appears to me that exists might do the trick:

DELETE FROM MDFUSERGROUPREL_DELETED del
WHERE EXISTS (SELECT *
    FROM MDFUSERGROUPRE ex
    WHERE ex.userid = del.userid 
    AND ex.usergroupid = del.usergroupid
    ) 
AND del.userid=?
 ;

Upvotes: 2

Related Questions