Re: SELECT DISTINCT ON removes results

[Guyren Howe <guyren@xxxxxxxxx>]

On Oct 28, 2016, at 13:50 , David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe <guyren@xxxxxxxxx> 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)