Re: [HACKERS] ORDER/GROUP BY expression not found in targetlist

[Tatsuro Yamada]

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  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'

Re: [HACKERS] ORDER/GROUP BY expression not found in targetlist

[Tom Lane]

Andres Freund  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


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

Re: [HACKERS] ORDER/GROUP BY expression not found in targetlist

[Tom Lane]

Andreas Seltenreich  writes:
> Tom Lane writes:
>> It's looking for an operator that is known to be semantically equality,
>> by virtue of being the equality member of a btree or hash opclass.
>> Type path has no such opclass unfortunately.

> As do lots of data types in the regression db while still having an
> operator providing semantic equivalence.  I was hoping for someone to
> question that status quo.  Naively I'd say an equivalence flag is
> missing in the catalog that is independent of opclasses.

[ shrug... ]  I see little wrong with that status quo.  For this
particular use-case, there are two ways we could implement DISTINCT: one
of them requires sorting, and the other requires hashing.  So you would
need to provide that opclass infrastructure even if there were some other
way of identifying the operator that means equality.

Type path and the other geometric types lack any natural sort order so
it's hard to imagine making a default btree opclass for them.  But a
default hash opclass might not be out of reach, given an exact equality
operator.

Another problem with the geometric types is that long ago somebody
invented "=" operators for most of them that have little to do with what
anyone would consider equality.  The "path = path" operator just compares
whether the paths have the same number of points.  A lot of the other ones
compare areas.  It'd be hard to justify marking any of them as default
equality for the type.

regards, tom lane


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

Re: [HACKERS] ORDER/GROUP BY expression not found in targetlist

[Andreas Seltenreich]

Tom Lane writes:

> Andreas Seltenreich  writes:
>> Peter Geoghegan writes:
>>> It's surprising that SQL Smith didn't catch something with such simple
>>> steps to reproduce.
>
>> I removed distinct relatively early because it causes a large part of
>> queries to fail due to it not finding an equality operator it likes.  It
>> seems to be more picky about the equality operator than, say, joins.
>> I'm sure it has a good reason to do so?
>
> It's looking for an operator that is known to be semantically equality,
> by virtue of being the equality member of a btree or hash opclass.
> Type path has no such opclass unfortunately.

As do lots of data types in the regression db while still having an
operator providing semantic equivalence.  I was hoping for someone to
question that status quo.  Naively I'd say an equivalence flag is
missing in the catalog that is independent of opclasses.

regards
Andreas


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

Re: [HACKERS] ORDER/GROUP BY expression not found in targetlist

[Tom Lane]

Andreas Seltenreich  writes:
> Peter Geoghegan writes:
>> It's surprising that SQL Smith didn't catch something with such simple
>> steps to reproduce.

> I removed distinct relatively early because it causes a large part of
> queries to fail due to it not finding an equality operator it likes.  It
> seems to be more picky about the equality operator than, say, joins.
> I'm sure it has a good reason to do so?

It's looking for an operator that is known to be semantically equality,
by virtue of being the equality member of a btree or hash opclass.
Type path has no such opclass unfortunately.  But when you write "a = b"
that just looks for an operator named "=".

regards, tom lane


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

Re: [HACKERS] ORDER/GROUP BY expression not found in targetlist

[Andreas Seltenreich]

Peter Geoghegan writes:

> On Wed, May 25, 2016 at 7:12 PM, Andres Freund  wrote:
>>
>> =# 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.
>
> It's surprising that SQL Smith didn't catch something with such simple
> steps to reproduce.

I removed distinct relatively early because it causes a large part of
queries to fail due to it not finding an equality operator it likes.  It
seems to be more picky about the equality operator than, say, joins.
I'm sure it has a good reason to do so?

regression=> select distinct f1 from path_tbl;
ERROR:  could not identify an equality operator for type path
LINE 1: select distinct f1 from path_tbl;

regression=> \do =
-[ RECORD 38 ]-+
Schema | pg_catalog
Name   | =
Left arg type  | path
Right arg type | path
Result type| boolean
Description| equal



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

Re: [HACKERS] ORDER/GROUP BY expression not found in targetlist

[Peter Geoghegan]

On Wed, May 25, 2016 at 7:12 PM, Andres Freund  wrote:
>
> =# 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.

It's surprising that SQL Smith didn't catch something with such simple
steps to reproduce.


-- 
Peter Geoghegan


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

[HACKERS] ORDER/GROUP BY expression not found in targetlist

[Andres Freund]

Hi,

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.

Greetings,

Andres Freund


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