ERROR: ORDER/GROUP BY expression not found in targetlist

Started by Rajkumar Raghuwanshialmost 8 years ago4 messageshackers
Jump to latest
#1Rajkumar Raghuwanshi
rajkumar.raghuwanshi@enterprisedb.com

Hi,

Below test case is failing with ERROR: ORDER/GROUP BY expression not found
in targetlist. this issue is reproducible from PGv10.

postgres=# CREATE TABLE test(c1 int, c2 text, c3 text);
CREATE TABLE
postgres=# INSERT INTO test SELECT i % 10, i % 250, to_char(i % 4,
'FM0000000') FROM GENERATE_SERIES(1,100000,1)i;
INSERT 0 100000
postgres=# ANALYZE test;
ANALYZE
postgres=# EXPLAIN (verbose) SELECT c1, generate_series(1,1), count(*) FROM
test GROUP BY 1 HAVING count(*) > 1;
QUERY
PLAN
-------------------------------------------------------------------------------
ProjectSet (cost=2291.00..2306.15 rows=3000 width=16)
Output: c1, generate_series(1, 1), (count(*))
-> HashAggregate (cost=2291.00..2291.12 rows=3 width=12)
Output: c1, count(*)
Group Key: test.c1
Filter: (count(*) > 1)
-> Seq Scan on public.test (cost=0.00..1541.00 rows=100000
width=4)
Output: c1, c2, c3
(8 rows)

postgres=# SET min_parallel_table_scan_size=0;
SET
postgres=# EXPLAIN (verbose) SELECT c1, generate_series(1,1), count(*) FROM
test GROUP BY 1 HAVING count(*) > 1;
ERROR: ORDER/GROUP BY expression not found in targetlist

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Rajkumar Raghuwanshi (#1)
Re: ERROR: ORDER/GROUP BY expression not found in targetlist

Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> writes:

Below test case is failing with ERROR: ORDER/GROUP BY expression not found
in targetlist. this issue is reproducible from PGv10.

Thanks for the report. I traced through this, and the problem seems to
be that split_pathtarget_at_srfs() is neglecting to attach sortgroupref
labeling to the extra PathTargets it constructs. I don't remember
whether that code is my fault or Andres', but I'll take a look at
fixing it.

regards, tom lane

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Tom Lane (#2)
Re: ERROR: ORDER/GROUP BY expression not found in targetlist

I wrote:

Thanks for the report. I traced through this, and the problem seems to
be that split_pathtarget_at_srfs() is neglecting to attach sortgroupref
labeling to the extra PathTargets it constructs. I don't remember
whether that code is my fault or Andres', but I'll take a look at
fixing it.

Here's a proposed patch for that.

regards, tom lane

Attachments:

fix-sortgroupref-handling-while-splitting-SRFs.patchtext/x-diff; charset=us-ascii; name=fix-sortgroupref-handling-while-splitting-SRFs.patchDownload+215-39
#4Rajkumar Raghuwanshi
rajkumar.raghuwanshi@enterprisedb.com
In reply to: Tom Lane (#3)
Re: ERROR: ORDER/GROUP BY expression not found in targetlist

Thanks for commit. I have verified reported case. it is fixed now.

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

On Thu, Jun 21, 2018 at 1:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Show quoted text

I wrote:

Thanks for the report. I traced through this, and the problem seems to
be that split_pathtarget_at_srfs() is neglecting to attach sortgroupref
labeling to the extra PathTargets it constructs. I don't remember
whether that code is my fault or Andres', but I'll take a look at
fixing it.

Here's a proposed patch for that.

regards, tom lane