> On Oct 28, 2016, at 13:50 , David G. Johnston <david.g.johns...@gmail.com> > wrote: > > On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe <guy...@gmail.com > <mailto:guy...@gmail.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)
