The present implementation of JSON_TABLE sets the collation of the
output columns to the default collation if the specified data type is
collatable. Why don't we use the collation of the type directly? This
would make domains with attached collations work correctly.
See attached patch for how to change this. I hacked up a regression
test case to demonstrate this.From b9f6de8bff992bf193d5710d175f6421851c869f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 7 Jun 2022 15:11:13 +0200
Subject: [PATCH] Fix collation of JSON_TABLE output columns
The output columns of JSON_TABLE should have the collations of their
data type. The existing implementation sets the default collation if
the type is collatable.
XXX regression test changes only for demonstration
---
src/backend/parser/parse_jsontable.c | 5 +----
src/test/regress/sql/jsonb_sqljson.sql | 8 ++++++--
2 files changed, 7 insertions(+), 6 deletions(-)
diff --git a/src/backend/parser/parse_jsontable.c
b/src/backend/parser/parse_jsontable.c
index dbd3e66205..ae559d9cae 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -514,10 +514,7 @@ appendJsonTableColumns(JsonTableContext *cxt, List
*columns)
tf->coltypes = lappend_oid(tf->coltypes, typid);
tf->coltypmods = lappend_int(tf->coltypmods, typmod);
- tf->colcollations = lappend_oid(tf->colcollations,
-
type_is_collatable(typid)
-
? DEFAULT_COLLATION_OID
-
: InvalidOid);
+ tf->colcollations = lappend_oid(tf->colcollations,
get_typcollation(typid));
tf->colvalexprs = lappend(tf->colvalexprs, colexpr);
}
}
diff --git a/src/test/regress/sql/jsonb_sqljson.sql
b/src/test/regress/sql/jsonb_sqljson.sql
index fff2537480..8127012f2b 100644
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -338,7 +338,9 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1,
$.a ? (@.datetime("H
COLUMNS (item int PATH '$', foo int)) bar;
-- JSON_TABLE: basic functionality
-CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
+--CREATE COLLATION foo (provider = icu, locale = '');
+CREATE COLLATION foo (provider = icu, locale = '@colAlternate=shifted');
+CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo') COLLATE foo;
SELECT *
FROM
@@ -383,7 +385,9 @@ CREATE DOMAIN jsonb_test_domain AS text CHECK (value <>
'foo');
jba jsonb[] PATH '$'
)
) jt
- ON true;
+ ON true
+ORDER BY "domain"
+;
-- JSON_TABLE: Test backward parsing
--
2.36.1
