From: | Guyren Howe <guyren(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SELECT DISTINCT ON removes results |
Date: | 2016-10-28 21:25:23 |
Message-ID: | 1413574A-19A4-4A0B-A420-9151FFC3C448@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On Oct 28, 2016, at 13:50 , David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe <guyren(at)gmail(dot)com <mailto:guyren(at)gmail(dot)com>> wrote:
> Using 9.5, this query:
Unless I'm missing something, this ought to be impossible.
Two queries differing only in having a DISTINCT clause produce the same result, demonstrated by EXCEPT:
=> SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, ','::text)))
-> string_agg(air_way_bills.number::text, ','::text) AS number,
-> air_way_bills.order_id
-> FROM pt.air_way_bills
-> where air_way_bills.order_id = 2792
-> GROUP BY air_way_bills.order_id
-> except
-> select string_agg(air_way_bills.number::text, ','::text) AS number,
-> air_way_bills.order_id
-> FROM pt.air_way_bills
-> where air_way_bills.order_id = 2792
-> GROUP BY air_way_bills.order_id;
number | order_id
--------+----------
(0 rows)
but joining with them produces different results:
=> SELECT o.id,
-> a.number AS awb
-> FROM pt.orders o
-> LEFT JOIN (
(> SELECT
(> string_agg(air_way_bills.number::text, ','::text) AS number,
(> air_way_bills.order_id
(> FROM pt.air_way_bills
(> GROUP BY air_way_bills.order_id) a ON a.order_id = o.id
-> where o.id = 2792;
id | awb
------+----------
2792 | 91484540
(1 row)
=> SELECT o.id,
-> a.number AS awb
-> FROM pt.orders o
-> LEFT JOIN (
(> SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, ','::text)))
(> string_agg(air_way_bills.number::text, ','::text) AS number,
(> air_way_bills.order_id
(> FROM pt.air_way_bills
(> GROUP BY air_way_bills.order_id) a ON a.order_id = o.id
-> where o.id = 2792
id | awb
------+-----
2792 |
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | Venkatesh Gudi (vgudi) | 2016-10-28 22:11:35 | postgres installer fails with comspec error even though it is correct |
Previous Message | Guyren Howe | 2016-10-28 21:17:34 | Re: SELECT DISTINCT ON removes results |