From 597e41e45eec8038b8c2c1153d1d050bfafeacbf Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 2 Mar 2016 23:31:39 -0500 Subject: [PATCH] Fix json_to_record() bug with nested objects. A thinko concerning nesting depth caused json_to_record() to produce bogus output if a field of its input object contained a sub-object with a field name matching one of the requested output column names. Per bug #13996 from Johann Visagie. I added a regression test case based on his example, plus parallel tests for json_to_recordset, jsonb_to_record, jsonb_to_recordset. The latter three do not exhibit the same bug (which suggests that we may be missing some opportunities to share code...) but testing seems like a good idea in any case. Back-patch to 9.4 where these functions were introduced. --- src/backend/utils/adt/jsonfuncs.c | 2 +- src/test/regress/expected/json.out | 16 ++++++++++++++++ src/test/regress/expected/jsonb.out | 16 ++++++++++++++++ src/test/regress/sql/json.sql | 9 ++++++++- src/test/regress/sql/jsonb.sql | 8 ++++++++ 5 files changed, 49 insertions(+), 2 deletions(-) diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index b49a5dd61e4..18e7b9c8798 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -2350,7 +2350,7 @@ hash_object_field_end(void *state, char *fname, bool isnull) /* * Ignore nested fields. */ - if (_state->lex->lex_level > 2) + if (_state->lex->lex_level > 1) return; /* diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out index 65c43c9b65c..8573b58e7c4 100644 --- a/src/test/regress/expected/json.out +++ b/src/test/regress/expected/json.out @@ -1552,3 +1552,19 @@ select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":fa 2 | {"d":"bar"} | f (2 rows) +select *, c is null as c_is_null +from json_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8}'::json) + as t(a int, b json, c text, x int); + a | b | c | x | c_is_null +---+-----------------+---+---+----------- + 1 | {"c":16, "d":2} | | 8 | t +(1 row) + +select *, c is null as c_is_null +from json_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::json) + as t(a int, b json, c text, x int); + a | b | c | x | c_is_null +---+-----------------+---+---+----------- + 1 | {"c":16, "d":2} | | 8 | t +(1 row) + diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index e2cb57097d2..0ef36329ba9 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -1760,6 +1760,22 @@ select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar", 2 | bar | t (2 rows) +select *, c is null as c_is_null +from jsonb_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8}'::jsonb) + as t(a int, b jsonb, c text, x int); + a | b | c | x | c_is_null +---+-------------------+---+---+----------- + 1 | {"c": 16, "d": 2} | | 8 | t +(1 row) + +select *, c is null as c_is_null +from jsonb_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::jsonb) + as t(a int, b jsonb, c text, x int); + a | b | c | x | c_is_null +---+-------------------+---+---+----------- + 1 | {"c": 16, "d": 2} | | 8 | t +(1 row) + -- indexing SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}'; count diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql index bf540c06a5f..346e5b8363f 100644 --- a/src/test/regress/sql/json.sql +++ b/src/test/regress/sql/json.sql @@ -487,7 +487,6 @@ select json_object('{a,b,NULL,"d e f"}','{1,2,3,"a b c"}'); select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}'); - -- json_to_record and json_to_recordset select * from json_to_record('{"a":1,"b":"foo","c":"bar"}') @@ -498,3 +497,11 @@ select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar"," select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]') as x(a int, b json, c boolean); + +select *, c is null as c_is_null +from json_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8}'::json) + as t(a int, b json, c text, x int); + +select *, c is null as c_is_null +from json_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::json) + as t(a int, b json, c text, x int); diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 74d34dcab4f..c84ad54ba35 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -372,6 +372,14 @@ select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}') select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') as x(a int, b text, c boolean); +select *, c is null as c_is_null +from jsonb_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8}'::jsonb) + as t(a int, b jsonb, c text, x int); + +select *, c is null as c_is_null +from jsonb_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::jsonb) + as t(a int, b jsonb, c text, x int); + -- indexing SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}'; SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC"}'; -- 2.39.5