Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lZxWA-0004JS-UN for pgsql-bugs@arkaria.postgresql.org; Fri, 23 Apr 2021 15:14:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lZxW8-0000nF-MX for pgsql-bugs@arkaria.postgresql.org; Fri, 23 Apr 2021 15:14:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from <9erthalion6@gmail.com>) id 1lZxW8-0000n8-Db for pgsql-bugs@lists.postgresql.org; Fri, 23 Apr 2021 15:14:52 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from <9erthalion6@gmail.com>) id 1lZxW6-00086F-5E for pgsql-bugs@lists.postgresql.org; Fri, 23 Apr 2021 15:14:51 +0000 Received: by mail-ej1-x62d.google.com with SMTP id w3so74457340ejc.4 for ; Fri, 23 Apr 2021 08:14:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=1hyigthATGBirSUokjb/JkA6iQGN2lihu1CGR1W0LuA=; b=pJH0p76q6CGulThb/7CS0wL0eG7aQtk1UwtR0EaLlQN0GraBW+BrpPA27mb3d4oRlp MqQebkKhlW1BSqPetdPjJdQU/7ko95VaPOTAb6Ej/Asr6hmn1hTU/GYuImz4OgUWH0Zz 2DuLaR4ZJTlVRmhl/qE/S7rBclQR7cFuL28IbP3sEMl3/mr3GUoiFR57h6B2SXiYxm5J sdnUJtTHeKzt75RE088wkT8YJPUy/hLxEGfyGxJ8fINv3XqJX5zinCvzpCl8Tbkw4TI/ Zs3iFv0TjjsR8f+suJvJxn39iK7K6n2+MmzQo2r4SYUkmkjmwLrzPKEf1Iupw22Yhuxa wJPw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=1hyigthATGBirSUokjb/JkA6iQGN2lihu1CGR1W0LuA=; b=lsfzHx/vMFBJzV0Z4XSHpdWbwpQZnIC5HOpmXd6ZUIHQA9q+feZqD/OjhsIAT+NTHr plLEswtSrZl3QG0l5t5U2wEnijn6CkKz/7hmyT2jVqcGfAObmUVw0rYghY4CvTjnPHAk RQQ2768cfAZ6da1O28TCai5Ba4oONN4OCVEAvTL/V9J+6WuOhsjbDs0Y5nznyKkdt2mL l69KgdL3Su0iDJ5lZ9kdDyzitOHjz7nRYw3+JVCOBLFeLDB52+3gwOjJ2IxbJlGgdN2A kTMjOg4QdgW4SFe7rKGeup+c2RWDTUIMwb2DmeiMJeKuDaGrWyBsAbGpWbOO389e6XYd 2vww== X-Gm-Message-State: AOAM530kXn30B/KrfyNp9AalPW003SHjJFQ1DhxGyeFmfEjK06UDqDv9 aGsikD/qZrgYdRcXn4HS6kE= X-Google-Smtp-Source: ABdhPJy9P/3HPDZEYfgwXu8g2smR3nCVOJkaexxpuXnwMIsqyw51EfQObN78bVDOZMg0HB3TKu9bkw== X-Received: by 2002:a17:906:b09:: with SMTP id u9mr4858501ejg.244.1619190888087; Fri, 23 Apr 2021 08:14:48 -0700 (PDT) Received: from localhost (dslb-178-012-047-165.178.012.pools.vodafone-ip.de. [178.12.47.165]) by smtp.gmail.com with ESMTPSA id c12sm5585172edx.54.2021.04.23.08.14.47 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 23 Apr 2021 08:14:47 -0700 (PDT) Date: Fri, 23 Apr 2021 17:15:05 +0200 From: Dmitry Dolgov <9erthalion6@gmail.com> To: "Poot, Bas (B.J.)" Cc: "pgsql-bugs@lists.postgresql.org" Subject: Re: XX000: unknown type of jsonb container. Message-ID: <20210423151505.lxx5ti7ivcza3xqw@localhost> References: <534fca83789c4a378c7de379e9067d4f@politie.nl> <20210407181105.lmi7hgifwzs3eeee@localhost> <20210413161500.kufk4iyrmcmhhbrc@localhost> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20210413161500.kufk4iyrmcmhhbrc@localhost> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > 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.