3 ms·

Most of what you say I agree with. But if this outer query is run only once with version A there is a caveat with where it says “where id = X”. This cannot ma

by swid 1y ago

Most of what you say I agree with. But if this outer query is run only once with version A there is a caveat with where it says “where id = X”. This cannot match more than one X at a time. So that forces the inner query to be run once, as we can only have one id, and running it twice may produce 2. I am not sure though to be honest. … We are too deep for me to reply now, but to your next comment I didn't mean only one row, but only one id. It is easy for a small difference in word choice to get things wrong. I think if it did return two rows; ie limit 2- the query with = will fail. Hmmm maybe that will happen even with limit 1 under certain plans. I wouldn’t trust it.

Diggsey 1y ago
Why would it only be run once? The WHERE condition of the outer query is run multiple times: once for each row, so of course it can return TRUE multiple times. For example: DELETE FROM example WHERE id = (SELECT RANDOM(0, 100) FROM other_table LIMIT 1) This could delete multiple rows in principle, since there may be multiple rows where the `=` expression is true.
- swid 1y ago
  After thinking about it more, yes I agree with your take. Maybe it can even delete different ids using an =.
  - Diggsey 1y ago
    FWIW, I tested this locally (using `DELETE FROM example WHERE id = RANDOM(1,100)`) and it did delete multiple rows in some cases, even without a subquery.