SQL/JSON: Prevent ON EMPTY for EXISTS columns in JSON_TABLE()

amitlan · amitlan · commit e3c1393efc31 · 2024-06-28T14:01:43.000+09:00
Due to an oversight in de36004, the ON EMPTY clause was incorrectly allowed in the EXISTS column. Fix the grammar to prevent this. Discussion: https://postgr.es/m/CA%2BHiwqHh3YDXTpccgAo4CdfV9Mhy%2Bmg%3Doh6t8rfM5uLW1BJN4g%40mail.gmail.com
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
@@ -14265,7 +14265,7 @@ json_table_column_definition:
 				}
 			| ColId Typename
 				EXISTS json_table_column_path_clause_opt
-				json_behavior_clause_opt
+				json_on_error_clause_opt
 				{
 					JsonTableColumn *n = makeNode(JsonTableColumn);
 
@@ -14276,8 +14276,8 @@ json_table_column_definition:
 					n->wrapper = JSW_NONE;
 					n->quotes = JS_QUOTES_UNSPEC;
 					n->pathspec = (JsonTablePathSpec *) $4;
-					n->on_empty = (JsonBehavior *) linitial($5);
-					n->on_error = (JsonBehavior *) lsecond($5);
+					n->on_empty = NULL;
+					n->on_error = (JsonBehavior *) $5;
 					n->location = @1;
 					$$ = (Node *) n;
 				}
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
@@ -1067,3 +1067,8 @@ CREATE OR REPLACE VIEW public.jsonb_table_view7 AS
         ) sub
 DROP VIEW jsonb_table_view7;
 DROP TABLE s;
+-- Prevent ON EMPTY specification on EXISTS columns
+SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on empty));
+ERROR:  syntax error at or near "empty"
+LINE 1: ...sonb '1', '$' COLUMNS (a int exists empty object on empty));
+                                                               ^
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
@@ -518,3 +518,6 @@ SELECT sub.* FROM s,
 \sv jsonb_table_view7
 DROP VIEW jsonb_table_view7;
 DROP TABLE s;
+
+-- Prevent ON EMPTY specification on EXISTS columns
+SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on empty));

Original file line number	Diff line number	Diff line change
`@@ -14265,7 +14265,7 @@ json_table_column_definition:`
`14265`	`14265`	`}`
`14266`	`14266`	`\| ColId Typename`
`14267`	`14267`	`EXISTS json_table_column_path_clause_opt`
`14268`		`- json_behavior_clause_opt`
	`14268`	`+ json_on_error_clause_opt`
`14269`	`14269`	`{`
`14270`	`14270`	`JsonTableColumn *n = makeNode(JsonTableColumn);`
`14271`	`14271`
`@@ -14276,8 +14276,8 @@ json_table_column_definition:`
`14276`	`14276`	`n->wrapper = JSW_NONE;`
`14277`	`14277`	`n->quotes = JS_QUOTES_UNSPEC;`
`14278`	`14278`	`n->pathspec = (JsonTablePathSpec *) $4;`
`14279`		`- n->on_empty = (JsonBehavior *) linitial($5);`
`14280`		`- n->on_error = (JsonBehavior *) lsecond($5);`
	`14279`	`+ n->on_empty = NULL;`
	`14280`	`+ n->on_error = (JsonBehavior *) $5;`
`14281`	`14281`	`n->location = @1;`
`14282`	`14282`	`$$ = (Node *) n;`
`14283`	`14283`	`}`