Hi,

I got same error by TPC-H: Q1,4,8,12 and 17.
I've attached results of the queries.

TPC-H (thanks to Tomas Vondra)
  https://github.com/tvondra/pg_tpch

  Datasize
     Scale Factor: 1

PG96beta1
  commit: f721e94b5f360391fc3ffe183bf697a0441e9184

Regards,
Tatsuro Yamada
NTT OSS Center

On 2016/05/27 2:22, Tom Lane wrote:
> Andres Freund <and...@anarazel.de> writes:
>> trying to reproduce a performance problem I just found:
> 
>> =# CREATE TABLE twocol(col01 int, col02 int);
>> =# SELECT DISTINCT col01, col02, col01 FROM twocol ;
>> ERROR:  XX000: ORDER/GROUP BY expression not found in targetlist
>> LOCATION:  get_sortgroupref_tle, tlist.c:341
> 
>> which appears to be a 9.6 regression, presumable fallout from the path
>> restructuring.
> 
> Huh.  The problem is that createplan.c is trying to apply the
> physical-tlist optimization to the seqscan underneath the aggregate
> node.  That means that the output from the seqscan is just
> "col01, col02", which means that col01 can only be decorated with
> a single ressortgroupref ... but there are two ressortgrouprefs
> for it as far as the groupClause is concerned.  Only one gets applied
> to the seqscan's tlist, and then later we fail because we don't find
> the other one there.  Conclusions:
> 
> * we need to back off the physical-tlist optimization in this case
> 
> * the code that transfers sortgroupref labels onto a tlist probably
> ought to notice and complain if it's asked to put inconsistent labels
> onto the same column.
> 
> I'm a little surprised that it's not discarding the third grouping
> item as redundant ... but that's probably not something to mess with
> right now.  Prior versions don't appear to do that either.
> 
>                       regards, tom lane
> 
> 


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)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to