SQL/JSON path: collation for comparisons, minor typos in docs

Поиск
Список
Период
Сортировка
От Markus Winand
Тема SQL/JSON path: collation for comparisons, minor typos in docs
Дата
Msg-id 8B7FA3B4-328D-43D7-95A8-37B8891B8C78@winand.at
обсуждение исходный текст
Ответы Re: SQL/JSON path: collation for comparisons, minor typos in docs  (Alexander Korotkov <a.korotkov@postgrespro.ru>)
Список pgsql-hackers
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. 


Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: [Proposal] Table-level Transparent Data Encryption (TDE) and KeyManagement Service (KMS)
Следующее
От: Heikki Linnakangas
Дата:
Сообщение: Grouping isolationtester tests in the schedule