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

I wrote:
> Amit Kapila <amit(dot)kapila(at)enterprisedb(dot)com> writes:
>> It is slightly tricky to write a reproducible parallel-query test, but
>> point taken and I think we should try to have a test unless such a test is
>> really time consuming.

> BTW, decent regression tests could be written without the need to create
> enormous tables if the minimum rel size in create_plain_partial_paths()
> could be configured to something less than 1000 blocks. I think it's
> fairly crazy that that arbitrary constant is hard-wired anyway. Should
> we make it a GUC?

Just as an experiment to see what would happen, I did

- int parallel_threshold = 1000;
+ int parallel_threshold = 1;

and ran the regression tests. I got a core dump in the window.sql test:

Program terminated with signal 11, Segmentation fault.
#0 0x0000000000664dbc in make_partialgroup_input_target (root=0x1795018,
input_rel=0x17957a8, target=0x17bf228, rollup_lists=0x0,
rollup_groupclauses=0x0) at planner.c:4307
4307 Index sgref = final_target->sortgrouprefs[i];
(gdb) bt
#0 0x0000000000664dbc in make_partialgroup_input_target (root=0x1795018,
input_rel=0x17957a8, target=0x17bf228, rollup_lists=0x0,
rollup_groupclauses=0x0) at planner.c:4307
#1 create_grouping_paths (root=0x1795018, input_rel=0x17957a8,
target=0x17bf228, rollup_lists=0x0, rollup_groupclauses=0x0)
at planner.c:3420
#2 0x0000000000667405 in grouping_planner (root=0x1795018,
inheritance_update=0 '\000', tuple_fraction=0) at planner.c:1794
#3 0x0000000000668c80 in subquery_planner (glob=<value optimized out>,
parse=0x1703580, parent_root=<value optimized out>,
hasRecursion=<value optimized out>, tuple_fraction=0) at planner.c:769
#4 0x0000000000668ea5 in standard_planner (parse=0x1703580,
cursorOptions=256, boundParams=0x0) at planner.c:308
#5 0x00000000006691b6 in planner (parse=<value optimized out>,
cursorOptions=<value optimized out>, boundParams=<value optimized out>)
at planner.c:178
#6 0x00000000006fb069 in pg_plan_query (querytree=0x1703580,
cursorOptions=256, boundParams=0x0) at postgres.c:798
(gdb) p debug_query_string
$1 = 0x1702078 "SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42;"

which I think may be another manifestation of the failure-to-apply-proper-
pathtarget issue we're looking at in this thread. Or maybe it's just
an unjustified assumption in make_partialgroup_input_target that the
input path must always have some sortgrouprefs assigned.

Before getting to that point, there was also an unexplainable plan change:

*** /home/postgres/pgsql/src/test/regress/expected/aggregates.out Thu Apr 7 21:13:14 2016
--- /home/postgres/pgsql/src/test/regress/results/aggregates.out Mon Jun 13 11:54:01 2016
***************
*** 577,590 ****

explain (costs off)
select max(unique1) from tenk1 where unique1 > 42000;
! QUERY PLAN
! ---------------------------------------------------------------------------
! Result
! InitPlan 1 (returns $0)
! -> Limit
! -> Index Only Scan Backward using tenk1_unique1 on tenk1
! Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000))
! (5 rows)

select max(unique1) from tenk1 where unique1 > 42000;
max
--- 577,588 ----

explain (costs off)
select max(unique1) from tenk1 where unique1 > 42000;
! QUERY PLAN
! ----------------------------------------------------
! Aggregate
! -> Index Only Scan using tenk1_unique1 on tenk1
! Index Cond: (unique1 > 42000)
! (3 rows)

select max(unique1) from tenk1 where unique1 > 42000;
max

I would not be surprised at a change to a parallel-query plan, but there's
no parallelism here, so what happened? This looks like a bug to me.
(Also, doing this query without COSTS OFF shows that the newly selected
plan actually has a greater estimated cost than the expected plan, which
makes it definitely a bug.)

At this point I'm pretty firmly convinced that we should have a way to
run the regression tests with parallel scans considered for even very
small tables. If someone doesn't want that way to be a GUC, you'd better
propose another solution.

regards, tom lane