Re: Strange result with "SELECT ... ORDER BY random() LIMIT 1" and JOINs

Etienne Dube <etdube@gmail.com> writes:
> This query yields unexpected results (tested under 9.2.4):

> SELECT
>      s.car_id,
>      s.color_id AS subquery_color_id,
>      co.color_id AS join_color_id,
>      co.color_name
> FROM
>      (
>          SELECT
>              ca.car_id,
>              (
>                  SELECT color_id
>                  FROM color
>                  WHERE ca.car_id = ca.car_id  -- dependency added to
> avoid getting the same value for every row in the output
>                  ORDER BY random()
>                  LIMIT 1
>              ) AS color_id
>          FROM
>              car ca
>      ) s
>      LEFT JOIN color co ON co.color_id = s.color_id;

> We can see the equality defined in the LEFT JOIN does not hold true for
> the subquery_color_id and join_color_id column aliases in the output.
> EXPLAIN also shows that the subplan for the inner subquery used to pick
> a random row from the color table appears twice.

I've committed patches to prevent duplication of subplans containing
volatile functions.  Thanks for the test case!

            regards, tom lane