Re: BUG #15826: BUG: Where-Clause referring to iso-8859-1 column in CTEis ignored in Update-statement

The following bug has been logged on the website:

Bug reference:      15826
Logged by:          Thomas Hantel
Email address:      tom.hantel@googlemail.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.