Hi! I was playing around with JSON path quite a bit and might have found one case where the current implementation doesn’t follow the standard.
The functionality in question are the comparison operators except ==. They use
the database default collation rather then the standard-mandated "Unicode
codepoint collation” (SQL-2:2016 9.39 General Rule 12 c iii 2 D, last sentence
in first paragraph).
I guess this is the relevant part of the code:
src/backend/utils/adt/jsonpath_exec.c (compareItems)
case jbvString:
if (op == jpiEqual)
return jb1->val.string.len !=
jb2->val.string.len ||
memcmp(jb1->val.string.val,
jb2->val.string.val,
jb1->val.string.len) ?
jpbFalse : jpbTrue;
cmp = varstr_cmp(jb1->val.string.val,
jb1->val.string.len,
jb2->val.string.val,
jb2->val.string.len,
DEFAULT_COLLATION_OID);
break;
Testcase:
postgres 12beta3=# select * from jsonb_path_query('"dummy"', '$ ? ("a"
< "A")');
jsonb_path_query
------------------
"dummy"
(1 row)
In code points, lower case ‘a' is not less than upper case ‘A’—the result
should be empty.
To convince myself:
postgres 12beta3=# select datcollate, 'a' < 'A', 'a' <'A' COLLATE
ucs_basic from pg_database where datname=current_database();
datcollate | ?column? | ?column?
-------------+----------+----------
en_US.UTF-8 | t | f
(1 row)
I also found two minor typos in the docs. Patch attached.
-markus
ps.: I’ve created 230 test cases. Besides the WIP topic .datetime(), the
collation issue is the only one I found. Excellent work. Down to the SQLSTATEs.
For sure the most complete and correct SQL/JSON path implementation I've seen.
0001-Doc-Fix-typos-in-json-path-documentation.patch
Description: Binary data
