commit f721e94b5f360391fc3ffe183bf697a0441e9184


[postgres@bluey queries]$ psql
psql (9.6beta1)
Type "help" for help.

postgres=# \i 1.explain.sql
ERROR:  ORDER/GROUP BY expression not found in targetlist
STATEMENT:  explain analyze select
                l_returnflag,
                l_linestatus,
                sum(l_quantity) as sum_qty,
                sum(l_extendedprice) as sum_base_price,
                sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
                sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
                avg(l_quantity) as avg_qty,
                avg(l_extendedprice) as avg_price,
                avg(l_discount) as avg_disc,
                count(*) as count_order
        from
                lineitem
        where
                l_shipdate <= date '1998-12-01' - interval '70' day
        group by
                l_returnflag,
                l_linestatus
        order by
                l_returnflag,
                l_linestatus
        LIMIT 1;


postgres=# \i 4.explain.sql
ERROR:  ORDER/GROUP BY expression not found in targetlist
STATEMENT:  explain analyze select
                o_orderpriority,
                count(*) as order_count
        from
                orders
        where
                o_orderdate >= date '1993-10-01'
                and o_orderdate < date '1993-10-01' + interval '3' month
                and exists (
                        select
                                *
                        from
                                lineitem
                        where
                                l_orderkey = o_orderkey
                                and l_commitdate < l_receiptdate
                )
        group by
                o_orderpriority
        order by
                o_orderpriority
        LIMIT 1;


postgres=# \i 8.explain.sql
ERROR:  ORDER/GROUP BY expression not found in targetlist
STATEMENT:  explain analyze select
                o_year,
                sum(case
                        when nation = 'EGYPT' then volume
                        else 0
                end) / sum(volume) as mkt_share
        from
                (
                        select
                                extract(year from o_orderdate) as o_year,
                                l_extendedprice * (1 - l_discount) as volume,
                                n2.n_name as nation
                        from
                                part,
                                supplier,
                                lineitem,
                                orders,
                                customer,
                                nation n1,
                                nation n2,
                                region
                        where
                                p_partkey = l_partkey
                                and s_suppkey = l_suppkey
                                and l_orderkey = o_orderkey
                                and o_custkey = c_custkey
                                and c_nationkey = n1.n_nationkey
                                and n1.n_regionkey = r_regionkey
                                and r_name = 'MIDDLE EAST'
                                and s_nationkey = n2.n_nationkey
                                and o_orderdate between date '1995-01-01' and date '1996-12-31'
                                and p_type = 'STANDARD ANODIZED TIN'
                ) as all_nations
        group by
                o_year
        order by
                o_year
        LIMIT 1;


postgres=# \i 12.explain.sql
ERROR:  ORDER/GROUP BY expression not found in targetlist
STATEMENT:  explain analyze select
                l_shipmode,
                sum(case
                        when o_orderpriority = '1-URGENT'
                                or o_orderpriority = '2-HIGH'
                                then 1
                        else 0
                end) as high_line_count,
                sum(case
                        when o_orderpriority <> '1-URGENT'
                                and o_orderpriority <> '2-HIGH'
                                then 1
                        else 0
                end) as low_line_count
        from
                orders,
                lineitem
        where
                o_orderkey = l_orderkey
                and l_shipmode in ('REG AIR', 'AIR')
                and l_commitdate < l_receiptdate
                and l_shipdate < l_commitdate
                and l_receiptdate >= date '1996-01-01'
                and l_receiptdate < date '1996-01-01' + interval '1' year
        group by
                l_shipmode
        order by
                l_shipmode
        LIMIT 1;

postgres=# \i 17.explain.sql
ERROR:  ORDER/GROUP BY expression not found in targetlist
STATEMENT:  explain analyze select
                sum(l_extendedprice) / 7.0 as avg_yearly
        from
                lineitem,
                part,
                (SELECT l_partkey AS agg_partkey, 0.2 * avg(l_quantity) AS avg_quantity FROM lineitem GROUP BY l_partkey) part_agg
        where
                p_partkey = l_partkey
                and agg_partkey = l_partkey
                and p_brand = 'Brand#44'
                and p_container = 'MED PACK'
                and l_quantity < avg_quantity
        LIMIT 1;




---------------------------------------------
BTW, Andres's query is OK.

postgres=# CREATE TABLE twocol(col01 int, col02 int);
CREATE TABLE
postgres=# SELECT DISTINCT col01, col02, col01 FROM twocol ;
 col01 | col02 | col01
-------+-------+-------
(0 rows)