Re: XX000: iso-8859-1 type of jsonb container. - Mailing list pgsql-bugs

> On Tue, Apr 13, 2021 at 06:15:00PM +0200, Dmitry Dolgov wrote:
> > On Wed, Apr 07, 2021 at 08:11:05PM +0200, Dmitry Dolgov wrote:
> > > On Wed, Apr 07, 2021 at 10:59:31AM +0000, Poot, Bas (B.J.) wrote:
> > >
> > > Finally! I have a testcase for you guys. This is my query to generate the data.
> > > select *
> > > into tmp_bug2
> > > from (
> > > select 'thing' as  logical_name
> > > , 'thing' as display_name
> > > , 'thing' as operation
> > > , '{"unit": "Our special Unit", "some.place": "placename", "place_of_birth": "Over the rainbow",
"How_secret_is_it":"Level 1", "Does.it.shoot": "YES!"}'::jsonb as filtur
 
> > > from generate_series(1, 302443)
> > > ) t1;
> > >
> > > And this is the query that generates the error.
> > >
> > > select
> > > display_name
> > > ,'' as x
> > > ,filtur
> > > ,jsonb_each_text(filtur) as x
> > > ,to_jsonb(jsonb_each_text(filtur)) as frows
> > > ,array(SELECT jsonb_object_keys(filtur)) as objectkeys
> > > from tmp_bug2
> > > order by logical_name;
> > >
> > > Notice that when you generate not 302443 but 302442 rows, it works perfectly fine.
> >
> > Thanks for posting the test case, I can reproduce it on the master
> > branch as well (also without the second call to jsonb_each_text in line
> > with to_jsonb). Interesting, it looks like for unclear to me reasons the
> > argument, evaluated in ExecMakeFunctionResultSet for jsonb_each_text,
> > contains value from the previous attribute, not jsonb. This makes
> > iteratorFromContainer complain because both array & object flags are set
> > in the header. I'll try to investigate, unless someone else will be
> > faster.
> 
> Here is what I've found so far:
> 
> * It seems that technically the regression was introduced in
>   ea190ed14b, but not directly, via using gather paths in more
>   situations.
> 
> * The direct problem is that JsonbIteratorInit is confused by the
>   argument, because it contains both headers for array and object set.
>   From what I understood this confusion stems from
>   ExecMakeFunctionResultSet taking wrong value from the slot. Hacking it
>   to use different attnum in this case makes it work.
> 
> * The explanation for this could be that target list from where attnum
>   is taken and slot values have different order of elements. This in
>   turn comes out of grouping planner trying to isolate SRF and SRF-free
>   targets and as a result changing the order of elements in
>   final_target. The final_target is then passed into
>   create_ordered_paths and applied via apply_projection_to_path, but
>   somehow goes in disagreement with what is used while creating the slot
>   with values.
> 
> If this analysis is correct, I'm not sure yet what would be the best
> course of action to address the problem, need to think a bit more. But
> probably others have suggestions or comments?

I couldn't find any other feasible explanations, and have come to a
conclusion that this happens when a projection is applied to a
GatherMerge path. As it's a projection capable path, no new projection
is created and target list is changed in place. In the subpath target
list ordering is different because of query ordering, and I don't see
where it all comes together during execution. Funny enough even explain
shows that the final plan passes a wrong values to jsonb_each_text.

If I make GatherMerge non projection capable it fixes this particular
case and changes only one test in select_parallel (seems like a minor
plan changes). But I have not enough experience with this code to say if
it's a good or bad idea.