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