Re: BUG #15826: BUG: Where-Clause referring to unknown column in CTE is ignored in Update-statement

On Fri, May 31, 2019 at 11:55 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15826
> Logged by: Thomas Hantel
> Email address: tom(dot)hantel(at)googlemail(dot)com
> PostgreSQL version: 11.3
> Operating system: Fedora Linux
> Description:
>
> We are currently seeing the following unexpected effect:
>
> In one of our services there is a statement that first sets up a CTE to
> filter the data and then an UPDATE-statement that refers to that data. If
> the WHERE-clause in the update refers to a column that is not selected in
> the CTE, the whole clause is ignored so that all of the rows in the table
> get updated.
>
> The effect can be reproduced like this:
>
> DROP TABLE IF EXISTS dummy;
> CREATE TABLE IF NOT EXISTS dummy (id int PRIMARY KEY, value text);
>
> INSERT INTO dummy VALUES
> (1, 'text 1')
> ,(2, 'text 2')
> ,(3, 'text 3')
> ON CONFLICT (id) DO UPDATE
> SET value = EXCLUDEd.value;
>
> WITH cte AS
> (
> SELECT value FROM dummy WHERE id = 1
> )
> UPDATE dummy
> SET value = 'text 1 text 1'
> WHERE id = (SELECT id FROM cte)
> ;
>
> SELECT * FROM dummy;
>
> `
>
> In this example we use a temp table with two columns, "id" and "value". The
> CTE selects just the column "value" from this table and filters the data by
> using "WHERE id = 1". This is where in the real-world case our security
> mechanism would make sure that only rows that may be updated are passed
> on.
>
> Now the update statement itself comes into play and is supposed to set the
> value of the field "value" to "text 1 text 1" in just the one record we
> expect to be contained in the CTE (the one with ID 1).
>
> As it turns out, because the CTE just selects the field "value" and our
> comparison operates on a column that is not selected there, we don't get an
> error pertaining to the missing column ("unknown identifier" or something
> along those lines) but instead the WHERE clause is ignored altogether and
> all of the records get updated.
>
> We would expect an error to be thrown in all cases where undefined columns
> are referenced.
>

This is not a bug. It may seem weird but it is expected behaviour under
scope resolution.
The cte does not have an id column but dummy has, so it is used.

You can get an error if you use tablename.column to reference columns:

WHERE dummy.id = (SELECT cte.id FROM cte)

The SELECT cte.id will produce an error as expected.