Hi! While working on the JSON_TABLE PLAN clause [1], I found that our implementation of JSON_TABLE has a standard divergence. In our implementation, the table-level ON ERROR clause and the column-level ON ERROR clause are completely independent. But according to ISO/IEC 9075-2 2023, the table-level ON ERROR clause is propagated to the column level if the column doesn't have its own explicit ON ERROR clause.
I can't share my copy of the standard, but I verified that Chapter 7.11 Syntax Rules 1) e) iv) and 1) f) xi) specify that if a column element lacks an ON ERROR clause and the table-level clause is ERROR ON ERROR, then ERROR ON ERROR applies to the column. Otherwise (no table-level clause or table-level clause is NULL ON ERROR), NULL ON ERROR applies to the column. For instance, this query returns NULL on the current master, but must error out according to the standard. SELECT * FROM JSON_TABLE(jsonb '"err"', '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt; I've drafted a patch that implements the behavior specified in the standard. That's not a very complicated patch, but I see a problem: JSON_TABLE was released in PostgreSQL 17, and this patch changes user-visible behavior. I see some options we can go ahead with: 1) Backpatch this to PostgreSQL 17, and issue release notes that users should check and revise their queries. 2) Patch only master, and issue the same notes for the PostgreSQL 20 release. 3) Add the GUC controlling this behavior (I don't really like that). Any thoughts? Links. 1. https://www.postgresql.org/message-id/CAPpHfdt%3DLncQH9PAq9O8qO7KZcTT9rOsxLLanscRF7xDFvK8mA%40mail.gmail.com ------ Regards, Alexander Korotkov Supabase
v1-0001-JSON_TABLE-propagate-table-level-ON-ERROR-to-colu.patch
Description: Binary data
