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
          psergei Sergei Petrunia created issue -
          psergei Sergei Petrunia made changes -
          Field Original Value New Value
          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:

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

          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:
          {quote}
            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.
          {quote}

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

          {quote}
          <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>
          {quote}

          This doesn't say whether the <value expression> should be interepreted as JSON
          or just as a value. But one can find this passage:
           
          {quote}
          <quote>
          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>".
          </quote>
          {quote}

          The important part is:

          {quote}
          ... shall be a <literal> that can be cast to the data type specified ...
          {quote}
          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

          		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:

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

          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:
          {quote}
            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.
          {quote}

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

          {code}
          <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>
          {code}

          This doesn't say whether the <value expression> should be interepreted as JSON
          or just as a value. But one can find this passage:
           
          {code}
          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>".
          {code}

          The important part is:

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

          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

          psergei Sergei Petrunia added a comment -

          A patch by Alexey: https://github.com/MariaDB/server/commit/9c518e4cc9b0569cae2daa5a4024e209293eca45

          psergei Sergei Petrunia added a comment - A patch by Alexey: https://github.com/MariaDB/server/commit/9c518e4cc9b0569cae2daa5a4024e209293eca45
          psergei Sergei Petrunia made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          psergei Sergei Petrunia made changes -
          Assignee Alexey Botchkov [ holyfoot ] Sergei Petrunia [ psergey ]
          psergei Sergei Petrunia made changes -
          Status Confirmed [ 10101 ] In Review [ 10002 ]
          psergei Sergei Petrunia added a comment -

          Review input: https://lists.launchpad.net/maria-developers/msg12763.html

          psergei Sergei Petrunia added a comment - Review input: https://lists.launchpad.net/maria-developers/msg12763.html
          psergei Sergei Petrunia made changes -
          Assignee Sergei Petrunia [ psergey ] Alexey Botchkov [ holyfoot ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          holyfoot Alexey Botchkov made changes -
          Status Stalled [ 10000 ] In Progress [ 3 ]
          holyfoot Alexey Botchkov added a comment -

          https://github.com/MariaDB/server/commit/8dae7ee02f98e71e9352d73d1da235fd4128d076

          holyfoot Alexey Botchkov added a comment - https://github.com/MariaDB/server/commit/8dae7ee02f98e71e9352d73d1da235fd4128d076
          holyfoot Alexey Botchkov made changes -
          Assignee Alexey Botchkov [ holyfoot ] Sergei Petrunia [ psergey ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 122334 ] MariaDB v4 [ 143793 ]
          julien.fritsch Julien Fritsch made changes -
          Priority Major [ 3 ] Critical [ 2 ]
          psergei Sergei Petrunia added a comment -

          The patch conflicts with this patch:

          commit 6b6d745b9eab64c6c1a3c13c11afe38c6761df69
          		 
          Author:	Alexey Botchkov <holyfoot@askmonty.org>  Sun May 30 04:42:35 2021
          		 
          Committer:	Rucha Deodhar <rucha.deodhar@mariadb.com>  Tue May 31 09:38:54 2022
          		 
           
          		 
          let numeric in the DEMAULT.

          merging...

          psergei Sergei Petrunia added a comment - The patch conflicts with this patch: commit 6b6d745b9eab64c6c1a3c13c11afe38c6761df69 Author: Alexey Botchkov <holyfoot@askmonty.org> Sun May 30 04:42:35 2021 Committer: Rucha Deodhar <rucha.deodhar@mariadb.com> Tue May 31 09:38:54 2022   let numeric in the DEMAULT. merging...
          psergei Sergei Petrunia added a comment -

          That patch adds assignments to m_text_literal_cs, which apparently is not used anywhere.
          Trying this:

          commit 5c478d644dd2031919f60d85cd9efcc522cf754e (HEAD -> bb-10.6-remove-m_text_literal, origin/bb-10.6-remove-m_text_literal)
          		 
          Author: Sergei Petrunia <sergey@mariadb.com>
          		 
          Date:   Tue Jul 23 11:32:42 2024 +0300
          		 
           
          		 
              Remove Table_function_json_table::m_text_literal_cs - it is not used.

          psergei Sergei Petrunia added a comment - That patch adds assignments to m_text_literal_cs , which apparently is not used anywhere. Trying this: commit 5c478d644dd2031919f60d85cd9efcc522cf754e (HEAD -> bb-10.6-remove-m_text_literal, origin/bb-10.6-remove-m_text_literal) Author: Sergei Petrunia <sergey@mariadb.com> Date: Tue Jul 23 11:32:42 2024 +0300   Remove Table_function_json_table::m_text_literal_cs - it is not used.
          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 made changes -
          Assignee Sergei Petrunia [ psergey ] Alexey Botchkov [ holyfoot ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          holyfoot Alexey Botchkov made changes -
          Status Stalled [ 10000 ] In Progress [ 3 ]
          holyfoot Alexey Botchkov made changes -
          Assignee Alexey Botchkov [ holyfoot ] Sergei Petrunia [ psergey ]
          psergei Sergei Petrunia added a comment -

          Got Ok to push.

          psergei Sergei Petrunia added a comment - Got Ok to push.
          psergei Sergei Petrunia made changes -
          Fix Version/s 10.6.20 [ 29903 ]
          Fix Version/s 10.11.10 [ 29904 ]
          Fix Version/s 11.2.6 [ 29906 ]
          Fix Version/s 11.4.4 [ 29907 ]
          Fix Version/s 11.6.2 [ 29908 ]
          Fix Version/s 11.7.1 [ 29913 ]
          Fix Version/s 10.6 [ 24028 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          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.