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 <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
