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.04rows=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.00rows=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.332rows=595224 loops=1)                     Filter: (l_shipdate <= '1998-09-22 00:00:00'::timestamp
withouttime 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
>