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:

              '$' 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.

F-1: If a JSON table column definition contains both an ON EMPTY clause and an ON ERROR clause, and the ON ERROR clause is specified before the ON EMPTY clause, a deprecation warning should be raised.

When a JSON_TABLE expression contains a column definition which has an ON ERROR clause before an ON EMPTY clause, the parser will raise a warning with error code ER_WARN_DEPRECATED_SYNTAX, and with the following message: "Specifying an ON EMPTY clause after the ON ERROR clause in a JSON_TABLE column definition is deprecated syntax and will be removed in a future release. Specify ON EMPTY before ON ERROR instead."

sql_yacc.yy has a separate rule for the case where ON ERROR comes before ON EMPTY:

         | opt_on_error opt_on_empty
             $$.error = $1;
             $$.empty = $2;

This is where the deprecation warning should be raised.