Re: BUG #17564: Planner bug in combination of generate_series(), unnest() and ORDER BY

Thanks for the report! I can reproduce it on HEAD. The part of the plan
that causes problem looks like:

->  Gather Merge
      Output: results.group_id
      Workers Planned: 1
      ->  Sort
            Output: results.group_id, ((unnest('{}'::jsonb[]) ->> 'timestamp'::text))
            Sort Key: ((unnest('{}'::jsonb[]) ->> 'timestamp'::text))
            ->  Parallel Seq Scan on public.results
                  Output: results.group_id, (unnest('{}'::jsonb[]) ->> 'timestamp'::text)
                  Filter: (results.search_id = 3336)

Please note that the expression below appears in the targetlist of
seqscan:

    unnest('{}'::jsonb[]) ->> 'timestamp'::text

The func for operator '->>' itself is not proretset, but one of its args
(the unnest func) is proretset. And that causes problem when we set up
projection info for the seqscan node.

So why does this expression appear in the targetlist of seqscan node? I
did some debug on that. Since this expression is the desired pathkey of
the query, relation_can_be_sorted_early() checks on it and finds that it
can be computed from the reltarget of rel 'results', which is true since
this expression can be computed all by itself. So it is considered as a
useful ordering for rel 'results' and generate_useful_gather_paths()
creates the Sort and then Gather Merge nodes for 'results' based on this
pathkey.

Still need more time to investigate for the fix.

Thanks
Richard