[swid]

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]

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]

After thinking about it more, yes I agree with your take. Maybe it can even delete different ids using an =.

[Diggsey]

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.