This works as expected because PostgreSQL evaluates each random() call independently during execution.

Here, both random() calls in the SELECT list return the same value because PostgreSQL optimizes the query by evaluating the random() expressions once. When you add ORDER BY random(), the query executor pre-evaluates the SELECT list expressions. This optimization causes both random() calls to return the same value.

In this case, the subquery (select random()) is treated as a stable expression. PostgreSQL evaluates it once and reuses the result for efficiency. This is called "subquery caching".

This produces different values because the reference to the outer query column g forces PostgreSQL to re-evaluate the subquery for each row. Even though g - g is always 0, the presence of the outer reference prevents optimization