Re: ERROR: ORDER/GROUP BY expression not found in targetlist

Hi,

I tried to run tpc-h queries, but some queries failed by the error on last week.

>Subject: Re: [HACKERS] ORDER/GROUP BY expression not found in targetlist
>Date: Thu, 09 Jun 2016 12:08:01 +0900

Today, I try it again by changing max_parallel_workers_per_gather parameter.
The result of Q1 is bellow. Is this bug in the Open items on wiki?

-------------
postgres=# set max_parallel_workers_per_gather = 0;
SET
postgres=# \i queries/1.explain.sql
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=43474.03..43474.03 rows=1 width=236) (actual time=1039.583..1039.583 rows=1 loops=1)
-> Sort (cost=43474.03..43474.04 rows=6 width=236) (actual time=1039.583..1039.583 rows=1 loops=1)
Sort Key: l_returnflag, l_linestatus
Sort Method: top-N heapsort Memory: 25kB
-> HashAggregate (cost=43473.83..43474.00 rows=6 width=236) (actual time=1039.529..1039.534 rows=4 loops=1)
Group Key: l_returnflag, l_linestatus
-> Seq Scan on lineitem (cost=0.00..19668.15 rows=595142 width=25) (actual time=0.048..125.332 rows=595224 loops=1)
Filter: (l_shipdate <= '1998-09-22 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 5348
Planning time: 0.180 ms
Execution time: 1039.758 ms
(11 rows)

postgres=# set max_parallel_workers_per_gather = default;
SET
postgres=# \i queries/1.explain.sql
ERROR: ORDER/GROUP BY expression not found in targetlist

-------------

Regards,
Tatsuro Yamada
NTT OSS Center

On 2016/06/13 12:39, Thomas Munro wrote:
> Hi,
>
> What is going on here?
>
> postgres=# create table logs as select generate_series(1,
> 1000000)::text as data;
> SELECT 1000000
> postgres=# insert into logs select * from logs;
> INSERT 0 1000000
> postgres=# insert into logs select * from logs;
> INSERT 0 2000000
> postgres=# insert into logs select * from logs;
> INSERT 0 4000000
> postgres=# insert into logs select * from logs;
> INSERT 0 8000000
> postgres=# insert into logs select * from logs;
> INSERT 0 16000000
> postgres=# analyze logs;
> ANALYZE
> postgres=# set max_parallel_workers_per_gather = 0;
> SET
> postgres=# explain select length(data) from logs group by length(data);
> ┌────────────────────────────────────────────────────────────────────────────┐
> │ QUERY PLAN │
> ├────────────────────────────────────────────────────────────────────────────┤
> │ Group (cost=5843157.07..6005642.13 rows=993989 width=4) │
> │ Group Key: (length(data)) │
> │ -> Sort (cost=5843157.07..5923157.11 rows=32000018 width=4) │
> │ Sort Key: (length(data)) │
> │ -> Seq Scan on logs (cost=0.00..541593.22 rows=32000018 width=4) │
> └────────────────────────────────────────────────────────────────────────────┘
> (5 rows)
>
> postgres=# set max_parallel_workers_per_gather = 2;
> SET
> postgres=# explain select length(data) from logs group by length(data);
> ERROR: ORDER/GROUP BY expression not found in targetlist
>