Horton

Re: remaining sql/json patches

amit <amitlangote09@gmail.com>

View thread
From: Amit Langote <amitlangote09@gmail.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Erik Rijkers <er@xs4all.nl>, jian he <jian.universality@gmail.com>, Andres Freund <andres@anarazel.de>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2024-03-19T10:45:43Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. SQL/JSON: Various improvements to SQL/JSON query function docs

  2. SQL/JSON: Fix some obsolete comments.

  3. SQL/JSON: Fix issues with DEFAULT .. ON ERROR / EMPTY

  4. JSON_TABLE: Add support for NESTED paths and columns

  5. Fix JsonExpr deparsing to emit QUOTES and WRAPPER correctly

  6. Fix typo introduced in 6185c9737

  7. Add basic JSON_TABLE() functionality

  8. Avoid splitting errmsg string to span multiple lines

  9. Add SQL/JSON query functions

  10. Implement various jsonpath methods

  11. Add soft error handling to some expression nodes

  12. Adjust populate_record_field() to handle errors softly

  13. Refactor code used by jsonpath executor to fetch variables

  14. Test EXPLAIN (FORMAT JSON) ... XMLTABLE

  15. Simplify productions for FORMAT JSON [ ENCODING name ]

  16. Add trailing commas to enum definitions

  17. doc: add missing <returnvalue> and whitespace

  18. Add more SQL/JSON constructor functions

  19. Rename a nonterminal used in SQL/JSON grammar

  20. Some refactoring to export json(b) conversion functions

  21. Don't include CaseTestExpr in JsonValueExpr.formatted_expr

  22. Code review for commit b6e1157e7d

  23. Pass constructName to transformJsonValueExpr()

  24. Unify JSON categorize type API and export for external use

  25. Make some indentation in gram.y consistent

  26. Allow most keywords to be used as column labels without requiring AS.

  27. Reduce size of backend scanner's tables.

  28. Use perfect hashing, instead of binary search, for keyword lookup.

Attachments

Hi,

On Thu, Mar 7, 2024 at 9:06 PM Amit Langote <amitlangote09@gmail.com> wrote:
> This boils down to the difference in the cast expression chosen to
> convert the source value to int in the two cases.
>
> The case where the source value has no quotes, the chosen cast
> expression is a FuncExpr for function numeric_int4(), which has no way
> to suppress errors.  When the source value has quotes, the cast
> expression is a CoerceViaIO expression, which can suppress the error.
> The default behavior is to suppress the error and return NULL, so the
> correct behavior is when the source value has quotes.
>
> I think we'll need either:
>
> * fix the code in 0001 to avoid getting numeric_int4() in this case,
> and generally cast functions that don't have soft-error handling
> support, in favor of using IO coercion.
> * fix FuncExpr (like CoerceViaIO) to respect SQL/JSON's request to
> suppress errors and fix downstream functions like numeric_int4() to
> comply by handling errors softly.
>
> I'm inclined to go with the 1st option as we already have the
> infrastructure in place -- input functions can all handle errors
> softly.

I've adjusted the coercion-handling code to deal with this and similar
cases to use coercion by calling the target type's input function in
more cases.  The resulting refactoring allowed me to drop a bunch of
code and node structs, notably, the JsonCoercion and JsonItemCoercion
nodes.  Going with input function based coercion as opposed to using
casts means the coercion may fail in more cases than before but I
think that's acceptable.  For example, the following case did not fail
before because they'd use numeric_int() cast function to convert 1.234
to an integer:

select json_value('{"a": 1.234}', '$.a' returning int error on error);
ERROR:  invalid input syntax for type integer: "1.234"

It is same error as this case, where the source numerical value is
specified as a string:

select json_value('{"a": "1.234"}', '$.a' returning int error on error);
ERROR:  invalid input syntax for type integer: "1.234"

I had hoped to get rid of all instances of using casts and standardize
on coercion at runtime using input functions and json_populate_type(),
but there are a few cases where casts produce saner results and also
harmless (error-safe), such as cases where the target types are
domains or are types with typmod.

I've also tried to address most of Jian He's comments and a bunch of
cleanups of my own.  Attaching 0002 as the delta over v42 containing
all of those changes.

I intend to commit 0001+0002 after a bit more polishing.

-- 
Thanks, Amit Langote