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 1kJGon-0000c5-QL for pgsql-bugs@arkaria.postgresql.org; Fri, 18 Sep 2020 13:52:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kJGol-0005xP-Eh for pgsql-bugs@arkaria.postgresql.org; Fri, 18 Sep 2020 13:52:51 +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 ) id 1kJGol-0005xI-97 for pgsql-bugs@lists.postgresql.org; Fri, 18 Sep 2020 13:52:51 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kJGoj-0005z7-1A for pgsql-bugs@lists.postgresql.org; Fri, 18 Sep 2020 13:52:50 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 08IDqkUh445214; Fri, 18 Sep 2020 09:52:46 -0400 From: Tom Lane To: krishnamoorthi72@gmail.com cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16623: JSON select query result is getting differed when we change DB version In-reply-to: <16623-95eafdd48a3196e1@postgresql.org> References: <16623-95eafdd48a3196e1@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Fri, 18 Sep 2020 05:55:34 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <445212.1600437166.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 18 Sep 2020 09:52:46 -0400 Message-ID: <445213.1600437166@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > Could you please give your inputs on below issue that how we can proceed > further. We are moving our application which is currently using Postgres= 10.4 > into Postgres9.6.2. Why would you be interested in going to an older major version? If for some reason you absolutely had to, why in the world would you choose a minor version that is seventeen releases out of date? Go read the release notes for the 9.6.x series, and ask yourself if you don't want any of the post-9.6.2 bug fixes. > Issue: When we read JSON array element values, Postgres10.4 is giving pr= oper > response based on array elements index but Postgres9.6.2 is returning th= e > results like 'CROSS JOIN' outputs even retrieved from single column. It looks to me like the issue here is the multiple, nested set-returning functions. In v10 we rewrote the way those work and fixed some weird behaviors that were left over from the original Berkeley implementation. See https://www.postgresql.org/docs/10/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETU= RNING-SET particularly the NOTE that says Before PostgreSQL 10, putting more than one set-returning function in the same select list did not behave very sensibly unless they always produced equal numbers of rows. Otherwise, what you got was a number of output rows equal to the least common multiple of the numbers of rows produced by the set-returning functions. Also, nested set-returning functions did not work as described above; instead, a set-returning function could have at most one set-returning argument, and each nest of set-returning functions was run independently. Also, conditional execution (set-returning functions inside CASE etc) was previously allowed, complicating things even more. Use of the LATERAL syntax is recommended when writing queries that need to work in older PostgreSQL versions, because that will give consistent results across different versions. So yeah, 9.6.x is buggy here, but it won't be fixed. If you really need to make the query work the same in both versions, use the LATERAL equivalent explained in the v10 documentation. regards, tom lane