Re: ERROR: ORDER/GROUP BY expression not found in targetlist - Mailing list pgsql-hackers

I wrote:
> Amit Kapila <amit.kapila@enterprisedb.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
optimizedout>,    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=<valueoptimized 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