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.
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: