Hi!
I was playing around with JSON path quite a bit and might have found one case where the current implementation doesn’t
followthe standard.
The functionality in question are the comparison operators except ==. They use the database default collation rather
thenthe standard-mandated "Unicode codepoint collation” (SQL-2:2016 9.39 General Rule 12 c iii 2 D, last sentence in
firstparagraph).
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.
Excellentwork. Down to the SQLSTATEs. For sure the most complete and correct SQL/JSON path implementation I've seen.
