Etienne Dube <etdube@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
