From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Etienne Dube <etdube(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Strange result with "SELECT ... ORDER BY random() LIMIT 1" and JOINs |
Date: | 2013-11-08 16:40:43 |
Message-ID: | 8464.1383928843@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Etienne Dube <etdube(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2013-11-08 16:47:13 | Re: psql variable interpolation with subsequent underscore |
Previous Message | zach cruise | 2013-11-08 16:33:33 | Re: upgrading to 9.3 |