WL#13512: Deprecate ON ERROR before ON EMPTY in JSON_TABLE syntax

Affects: Server-8.0   —   Status: Complete

Columns in a JSON_TABLE expression can be specified with optional ON EMPTY and ON ERROR clauses.

For example:

 SELECT * FROM
   JSON_TABLE('{"a":1}',
              '$' COLUMNS (a INT PATH '$.a'
                           NULL ON EMPTY ERROR ON ERROR)
   ) AS jt

The SQL standard says that the ON EMPTY clause should come before the ON ERROR clause, if both are present:

 <JSON table regular column definition> ::=
   <column name> <data type>
       [ PATH <JSON table column path specification> ]
       [ <JSON table column empty behavior> ON EMPTY ]
       [ <JSON table column error behavior> ON ERROR ]

MySQL currently allows the ON ERROR clause to come before the ON EMPTY clause. This extension to the standard syntax should be deprecated, so that it can be removed later. Removing the extension makes the parser simpler, and encourages use of portable SQL syntax.