Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25822

JSON_TABLE: default values should allow non-string literals

Details

    Description

      Filing this for https://www.mail-archive.com/maria-developers@lists.launchpad.net/msg12296.html

      At the moment MariaDB requires that the values in DEFAULT clauses are quoted.
      Example:

      select * 
      from 
        json_table(
      		 
          '{"intval": 1000}',
      		 
          '$' columns( 
                       col1 int path '$.intval_'
      		 
                          default '100' on empty
      		 
                     )
      		 
         ) as T;

      here, "100" must be quoted, otherwise one gets a parse error. However, the quoted value is interpreted as an SQL literal. This looks puzzling.

      MySQL-8 also requires that the default value is quoted, but they have a (very odd) reason for it: they interpret the default value as JSON:

      https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/json-table-functions.html
      says:

      DEFAULT json_string ON EMPTY: the provided json_string is parsed as JSON, as
      long as it is valid, and stored instead of the missing value. Column type
      rules also apply to the default value.

      I am not sure why MySQL chose to do this. Looking into the SQL Standard, one
      can see:

      <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 ]
      		 
       
      		 
      <JSON table column empty behavior> ::=
      		 
          ERROR
      		 
        | NULL
      		 
        | DEFAULT <value expression>

      This doesn't say whether the <value expression> should be interepreted as JSON
      or just as a value. But one can find this passage:

      Without Feature T826, "General value expression in ON ERROR or ON EMPTY 
      clauses", the <value
      		 
      expression> contained in <JSON table column empty behavior> or <JSON table 
      column error behavior>
      		 
      contained in a <JSON table regular column definition> JTRCD shall be a 
      <literal> that can be cast to the
      		 
      data type specified by the <data type> contained in JTRCD without raising an 
      exception condition
      		 
      according to the General Rules of Subclause 6.13, "<cast specification>".

      The important part is:

      ... shall be a <literal> that can be cast to the data type specified ...

      which means it is not JSON. It is just a literal, and literal can be a string
      literal (in quotes, 'string') or an integer literal (without quotes) or other
      kind of literal.

      Btw, Oracle Database allows non-string literals in the default clause:
      https://dbfiddle.uk/?rdbms=oracle_18&fiddle=9af7e43ede77ee285e1a65f1f419d3bd

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          View 5 older comments
          psergei Sergei Petrunia added a comment - - edited

          Merged: 

          commit eaff5ae82168825f036dff00b5c9cc16a004c4ac (HEAD -> bb-10.6-MDEV-25822, origin/bb-10.6-MDEV-25822)
          		 
          Author: Sergei Petrunia <sergey@mariadb.com>
          		 
          Date:   Tue Jul 23 14:33:33 2024 +0300
          		 
           
          		 
              MDEV-25822 JSON_TABLE: default values should allow non-string literals
          		 
              
              (Polished initial patch by Alexey Botchkov)
          		 
              Make the code handle DEFAULT values of any datatype
          		 
              
              - Make Json_table_column::On_response::m_default be Item*, not LEX_STRING.
          		 
              - Change the parser to use string literal non-terminals for producing
          		 
                the DEFAULT value
          		 
              -- Also, stop updating json_table->m_text_literal_cs for the DEFAULT
          		 
                 value literals as it is not used.

          Need to check which version to push this into

          psergei Sergei Petrunia added a comment - - edited Merged: commit eaff5ae82168825f036dff00b5c9cc16a004c4ac (HEAD -> bb-10.6-MDEV-25822, origin/bb-10.6-MDEV-25822) Author: Sergei Petrunia <sergey@mariadb.com> Date: Tue Jul 23 14:33:33 2024 +0300   MDEV-25822 JSON_TABLE: default values should allow non-string literals (Polished initial patch by Alexey Botchkov) Make the code handle DEFAULT values of any datatype - Make Json_table_column::On_response::m_default be Item*, not LEX_STRING. - Change the parser to use string literal non-terminals for producing the DEFAULT value -- Also, stop updating json_table->m_text_literal_cs for the DEFAULT value literals as it is not used. Need to check which version to push this into
          psergei Sergei Petrunia added a comment -

          And also

          commit 7b59d374f3e71c2de0f9e6664b95674686916387 (HEAD -> bb-10.6-MDEV-25822-v2, origin/bb-10.6-MDEV-25822-v2)
          		 
          Author: Sergei Petrunia <sergey@mariadb.com>
          		 
          Date:   Tue Jul 23 14:58:24 2024 +0300
          		 
           
          		 
              Remove unused Table_function_json_table::m_text_literal_cs

          psergei Sergei Petrunia added a comment - And also commit 7b59d374f3e71c2de0f9e6664b95674686916387 (HEAD -> bb-10.6-MDEV-25822-v2, origin/bb-10.6-MDEV-25822-v2) Author: Sergei Petrunia <sergey@mariadb.com> Date: Tue Jul 23 14:58:24 2024 +0300   Remove unused Table_function_json_table::m_text_literal_cs
          psergei Sergei Petrunia added a comment - - edited

          Ok maybe it wasn't explicitly specified but a month ago I took Alexey's patch, reviewed it, applied my own input and committed as 

          commit eaff5ae82168825f036dff00b5c9cc16a004c4ac (HEAD -> bb-10.6-MDEV-25822, origin/bb-10.6-MDEV-25822)
          		 
          Author: Sergei Petrunia <sergey@mariadb.com>
          		 
          Date:   Tue Jul 23 14:33:33 2024 +0300
          		 
           
              MDEV-25822 JSON_TABLE: default values should allow non-string literals
          		 
              
              (Polished initial patch by Alexey Botchkov)

          added a few more commits and passed to holyfoot to look at. Now the ball is on Alexey's turf.
          Now changed issue status to reflect this too.

          psergei Sergei Petrunia added a comment - - edited Ok maybe it wasn't explicitly specified but a month ago I took Alexey's patch, reviewed it, applied my own input and committed as commit eaff5ae82168825f036dff00b5c9cc16a004c4ac (HEAD -> bb-10.6-MDEV-25822, origin/bb-10.6-MDEV-25822) Author: Sergei Petrunia <sergey@mariadb.com> Date: Tue Jul 23 14:33:33 2024 +0300 MDEV-25822 JSON_TABLE: default values should allow non-string literals (Polished initial patch by Alexey Botchkov) added a few more commits and passed to holyfoot to look at. Now the ball is on Alexey's turf. Now changed issue status to reflect this too.
          psergei Sergei Petrunia added a comment -

          Got Ok to push.

          psergei Sergei Petrunia added a comment - Got Ok to push.
          psergei Sergei Petrunia added a comment - - edited

          Notes for the documentation (needs editing):
          JSON_TABLE allows to specify default column values (See https://mariadb.com/kb/en/json_table/)

          Before this patch, one had to specify default values as string constants.

          After this patch, one can specify default values as any literals (string, integer, decimal number, date literal, etc)

          NOTE: https://mariadb.com/kb/en/json_table/ needs to be updated to reflect this change!

          select * 
           from 
          json_table('{"a": "123"}', 
                     '$' columns(col1 INT path '$.a' default 1 on empty)) as T;


          select * 
           from 
          json_table('{"a": "123"}', 
                     '$' columns(col1 date path '$.date' default date '2020-01-01' on empty)) as T;

          psergei Sergei Petrunia added a comment - - edited Notes for the documentation (needs editing): JSON_TABLE allows to specify default column values (See https://mariadb.com/kb/en/json_table/ ) Before this patch, one had to specify default values as string constants. After this patch, one can specify default values as any literals (string, integer, decimal number, date literal, etc) NOTE: https://mariadb.com/kb/en/json_table/ needs to be updated to reflect this change! select * from json_table( '{"a": "123"}' , '$' columns(col1 INT path '$.a' default 1 on empty)) as T; select * from json_table( '{"a": "123"}' , '$' columns(col1 date path '$.date' default date '2020-01-01' on empty)) as T;

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.