PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15826
PG Version11.3
OSFedora Linux
Opened2019-05-31 10:55:01+00
Reported byThomas Hantel
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15826
Logged by:          Thomas Hantel
Email address:      (redacted)
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.

Messages

DateAuthorSubject
2019-05-31 10:55:01+00PG Bug reporting formBUG #15826: BUG: Where-Clause referring to unknown column in CTE is ignored in Update-statement
2019-05-31 11:06:09+00Pantelis TheodosiouRe: BUG #15826: BUG: Where-Clause referring to unknown column in CTE is ignored in Update-statement
2019-05-31 13:45:22+00"David G(dot) Johnston"Re: BUG #15826: BUG: Where-Clause referring to unknown column in CTE is ignored in Update-statement