pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16623: JSON select query result is getting differed when we change DB version
2+ messages / 2 participants
[nested] [flat]

* BUG #16623: JSON select query result is getting differed when we change DB version
@ 2020-09-18 05:55 PG Bug reporting form <noreply@postgresql.org>
  2020-09-18 13:52 ` Re: BUG #16623: JSON select query result is getting differed when we change DB version Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2020-09-18 05:55 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: krishnamoorthi72@gmail.com

The following bug has been logged on the website:

Bug reference:      16623
Logged by:          Krishna R
Email address:      krishnamoorthi72@gmail.com
PostgreSQL version: 9.6.2
Operating system:   Linux - Red Hat 4.8.5
Description:        

Hi,
Could you please give your inputs on below issue that how we can proceed
further. We are moving our application which is currently using Postgres10.4
into Postgres9.6.2. Because of the application environment changes. All are
working fine but got struck with below issue.

Issue: When we read JSON array element values, Postgres10.4 is giving proper
response based on array elements index but Postgres9.6.2 is returning the
results like 'CROSS JOIN' outputs even retrieved from single column. Please
find below sample.

1.	Create Table Script:
          a.	CREATE TABLE device_data_test (command_output json);

2. 	Insert Script:
         INSERT INTO device_data_test
    (command_output)
     VALUES
     ('[
	{
		"name": "sample1",
		"fvAp": [
			{
              "name": "fvAp1"
			},
			{
              "name": "fvAp1.1"
			}
		]
	},
	{
		"name": "sample2",
		"fvAp": [
			{
              "name": "fvAp2"
			}
     ]
	}
]');

3. Select query results from Postgres10.4:

select json_array_elements(command_output)->>'name' as name,
json_array_elements(json_array_elements(command_output)->'fvAp')->>'name' as
appname from device_data_test;

 name   | appname
---------+---------
 sample1 | fvAp1
 sample1 | fvAp1.1
 sample2 | fvAp2
(3 rows)


4. Select query results from Postgres9.6.2:

select json_array_elements(command_output)->>'name' as name,
json_array_elements(json_array_elements(command_output)->'fvAp')->>'name' as
appname from device_data_test;

  name   | appname
---------+---------
 sample1 | fvAp1
 sample2 | fvAp1.1
 sample1 | fvAp2
 sample2 | fvAp1
 sample1 | fvAp1.1
 sample2 | fvAp2
(6 rows)



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #16623: JSON select query result is getting differed when we change DB version
  2020-09-18 05:55 BUG #16623: JSON select query result is getting differed when we change DB version PG Bug reporting form <noreply@postgresql.org>
@ 2020-09-18 13:52 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2020-09-18 13:52 UTC (permalink / raw)
  To: krishnamoorthi72@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> 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 Postgres10.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 proper
> response based on array elements index but Postgres9.6.2 is returning the
> 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-RETURNING-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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2020-09-18 13:52 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-18 05:55 BUG #16623: JSON select query result is getting differed when we change DB version PG Bug reporting form <noreply@postgresql.org>
2020-09-18 13:52 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox