Why does this sub-select work? No FROM clause present

Question

I made a silly mistake while writing a PostgreSQL request. The syntax seems to be completely wrong, however it still half work.

I have this table (subscriber):

id - device_id - invert
1    'abcd'       true
2    'abcd'       true

There are other rows but those are the only one concerned (with the same device_id)

And I made the request :

UPDATE subscriber 
   SET invert = false 
WHERE device_id = (SELECT device_id WHERE id = 1) 
RETURNING *;

I forgot the FROM clause of the sub-select. The sub-select on it's own crash as expected (device_id does not exist). However, the full request compute, and does update and return one of the row (the one with id = 1). The correct request should update and return both rows.

Why? Is it some kind of bug? Or does the sub-select behave differently from a select?

score 1 · Answer 1 · answered Jan 11 '18 at 13:45

I think similar questions have been asked before, but I can't find them right now.

Inside the sub-select everything from the outer query is visible as well. If there is a name-clash "local" identifiers in the sub-select have precedence over the "outer" ones. If an identifier is not valid in the sub-select, but is available in the outer query, the one from the outer query is used.

Postgres allows SELECT statements without a FROM clause, so

select 42;

is valid

So your sub-select actually uses the device_id and id columns from the table that is being updated. That's why the query works .

This is not a bug, these visibility rules are required by the SQL standard (however, the possibility to run a SELECT without FROM is an extension to the SQL standard).

score 1 · Accepted Answer · answered Jan 11 '18 at 13:46

I'm afraid, skipping FROM you made expression in brackets a correlated subquery, not just a subquery, eg:

t=# select oid,(select oid) from pg_database limit 2;
  oid  |  oid
-------+-------
     1 |     1
 12945 | 12945
(2 rows)

this way Postgres tries to guess the alias to be used for the column name, which is the the pg_database in my case... So you in your case your where device_id = (SELECT device_id WHERE id = 1) is logically equal to where id = 1...

another example:

t=# select oid,(select oid::int*2 where oid=1),2 from pg_database limit 2;
  oid  | ?column? | ?column?
-------+----------+----------
     1 |        2 |        2
 12945 |          |        2
(2 rows)

I can't disagree that in your update statement such logic is confusing :) — Vao Tsun, Jan 11 '18 at 13:56

Why does this sub-select work? No FROM clause present

2 Answers2