Re: Strange result with "SELECT ... ORDER BY random() LIMIT 1" and JOINs
От | Tom Lane |
---|---|
Тема | Re: Strange result with "SELECT ... ORDER BY random() LIMIT 1" and JOINs |
Дата | |
Msg-id | 8464.1383928843@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Strange result with "SELECT ... ORDER BY random() LIMIT 1" and JOINs (Etienne Dube <etdube@gmail.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: