From ba310fcb06b291a3611bcd84a415d60817bfe880 Mon Sep 17 00:00:00 2001 From: jian he Date: Thu, 4 Apr 2024 14:34:58 +0800 Subject: [PATCH v49 1/1] validate parsing back json_table json_table syntax is quite long. human eyes are not easy to found out the parsing back json_table is the correct. we need a way to validate parse back json_table works. we can do it through view. main gottcha is query: select count(*) from ((select * from view except all literal_view_def)) should return zero. --- .../regress/expected/sqljson_jsontable.out | 87 +++++++++++++++---- src/test/regress/sql/sqljson_jsontable.sql | 40 ++++++++- 2 files changed, 109 insertions(+), 18 deletions(-) diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index 77c04d97..ac69e920 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -218,6 +218,37 @@ FROM json_table_test vals (14 rows) -- JSON_TABLE: Test backward parsing +drop function if exists validate_view_def; +NOTICE: function validate_view_def() does not exist, skipping +CREATE OR REPLACE FUNCTION validate_view_def(view_name text) RETURNS bigint AS +$func$ +DECLARE + cnt bigint := 0; + temp text; + view_def text; + query text; + r record; + r1 record; +BEGIN + temp := $sql$ + SELECT pg_catalog.pg_get_viewdef(c.oid, true) + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid + where c.relkind = 'v' and c.relname = $sql$ || quote_literal($1); + --step1, get the pg_get_viewdef. + EXECUTE format('%s ', temp) into r; + ---step2 replace the last semicolon in pg_get_viewdef. + if (right(r.pg_get_viewdef,1) = ';') then + view_def := substr(r.pg_get_viewdef, 1, length(r.pg_get_viewdef) - 1); + end if; + -- step3, execute the viewdef string. + EXECUTE format('%s ', r.pg_get_viewdef) into r1; + --step4, compare viewdef with the view. + query := 'select count(*) from (( ' || view_def || ' ) except all ( select * from ' || quote_ident($1) || '))'; + -- raise notice '%', query; + EXECUTE format('%s ', query) into cnt; + return cnt; +END $func$ LANGUAGE plpgsql; CREATE VIEW jsonb_table_view2 AS SELECT * FROM JSON_TABLE( @@ -234,7 +265,6 @@ SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( - js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc char(4) FORMAT JSON PATH '$', @@ -261,7 +291,6 @@ SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( - js2 json PATH '$', jsb2w jsonb PATH '$' WITH WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia int[] PATH '$', @@ -291,8 +320,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view2 AS ) \sv jsonb_table_view3 CREATE OR REPLACE VIEW public.jsonb_table_view3 AS - SELECT js, - jb, + SELECT jb, jst, jsc, jsv @@ -302,7 +330,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view3 AS 1 + 2 AS a, '"foo"'::json AS "b c" COLUMNS ( - js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', @@ -345,8 +372,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view5 AS ) \sv jsonb_table_view6 CREATE OR REPLACE VIEW public.jsonb_table_view6 AS - SELECT js2, - jsb2w, + SELECT jsb2w, jsb2q, ia, ta, @@ -357,7 +383,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view6 AS 1 + 2 AS a, '"foo"'::json AS "b c" COLUMNS ( - js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', @@ -365,6 +390,36 @@ CREATE OR REPLACE VIEW public.jsonb_table_view6 AS jba jsonb[] PATH '$' ) ) +SELECT validate_view_def('jsonb_table_view2'); + validate_view_def +------------------- + 0 +(1 row) + +SELECT validate_view_def('jsonb_table_view3'); + validate_view_def +------------------- + 0 +(1 row) + +SELECT validate_view_def('jsonb_table_view4'); + validate_view_def +------------------- + 0 +(1 row) + +SELECT validate_view_def('jsonb_table_view5'); + validate_view_def +------------------- + 0 +(1 row) + +SELECT validate_view_def('jsonb_table_view6'); + validate_view_def +------------------- + 0 +(1 row) + EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- @@ -374,11 +429,11 @@ EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2; (3 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Table Function Scan on "json_table" - Output: "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv - Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$')) + Output: "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv + Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$')) (3 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4; @@ -398,11 +453,11 @@ EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view5; (3 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view6; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Table Function Scan on "json_table" - Output: "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba - Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$')) + Output: "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba + Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$')) (3 rows) -- JSON_TABLE() with alias diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index bdce4636..7b80b1ad 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -118,6 +118,38 @@ FROM json_table_test vals -- JSON_TABLE: Test backward parsing +drop function if exists validate_view_def; +CREATE OR REPLACE FUNCTION validate_view_def(view_name text) RETURNS bigint AS +$func$ +DECLARE + cnt bigint := 0; + temp text; + view_def text; + query text; + r record; + r1 record; +BEGIN + temp := $sql$ + SELECT pg_catalog.pg_get_viewdef(c.oid, true) + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid + where c.relkind = 'v' and c.relname = $sql$ || quote_literal($1); + --step1, get the pg_get_viewdef. + EXECUTE format('%s ', temp) into r; + ---step2 replace the last semicolon in pg_get_viewdef. + if (right(r.pg_get_viewdef,1) = ';') then + view_def := substr(r.pg_get_viewdef, 1, length(r.pg_get_viewdef) - 1); + end if; + -- step3, execute the viewdef string. + EXECUTE format('%s ', r.pg_get_viewdef) into r1; + --step4, compare viewdef with the view. + query := 'select count(*) from (( ' || view_def || ' ) except all ( select * from ' || quote_ident($1) || '))'; + -- raise notice '%', query; + EXECUTE format('%s ', query) into cnt; + return cnt; +END $func$ LANGUAGE plpgsql; + + CREATE VIEW jsonb_table_view2 AS SELECT * FROM JSON_TABLE( @@ -135,7 +167,6 @@ SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( - js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc char(4) FORMAT JSON PATH '$', @@ -165,7 +196,6 @@ SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( - js2 json PATH '$', jsb2w jsonb PATH '$' WITH WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia int[] PATH '$', @@ -178,6 +208,12 @@ SELECT * FROM \sv jsonb_table_view5 \sv jsonb_table_view6 +SELECT validate_view_def('jsonb_table_view2'); +SELECT validate_view_def('jsonb_table_view3'); +SELECT validate_view_def('jsonb_table_view4'); +SELECT validate_view_def('jsonb_table_view5'); +SELECT validate_view_def('jsonb_table_view6'); + EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2; EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3; EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4; base-commit: 2a217c371799ae3ecd8d32a137cea874fad7f5dc prerequisite-patch-id: 19eaa6cd330842b7147245e46120a3962de325bc -- 2.34.1