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

JSON_TABLE: CREATE VIEW involving NESTED PATH ends up with invalid frm

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: 10.6.0
    • Component/s: JSON, Views
    • Labels:
      None

      Description

      Note: Patch for MDEV-25142 is already in the tree, and the test case from there doesn't fail.

      CREATE OR REPLACE VIEW v AS SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(NESTED PATH '$**.*' COLUMNS(a FOR ORDINALITY), b VARCHAR(8) PATH '$')) AS jt;
      SELECT * FROM v;
      

      bb-10.6-mdev17399-hf 8611fb30bc

      MariaDB [test]> CREATE OR REPLACE VIEW v AS SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(NESTED PATH '$**.*' COLUMNS(a FOR ORDINALITY), b VARCHAR(8) PATH '$')) AS jt;
      Query OK, 0 rows affected (0.015 sec)
       
      MariaDB [test]> SELECT * FROM v;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '), `b` varchar(8) PATH '$')) `jt`' at line 1
      

      frm

      TYPE=VIEW
      query=select `jt`.`a` AS `a`,`jt`.`b` AS `b` from JSON_TABLE(\'{}\', \'$\' COLUMNS (NESTED PATH \'$**.*\'COLUMNS (`a` FOR ORDINALITY, ), `b` varchar(8) PATH \'$\')) `jt`
      md5=e7bd531bffd0aa8f52eed72da11e08c1
      updatable=1
      algorithm=0
      definer_user=root
      definer_host=localhost
      suid=2
      with_check_option=0
      timestamp=2021-03-17 23:04:36
      create-version=2
      source=SELECT * FROM JSON_TABLE(\'{}\', \'$\' COLUMNS(NESTED PATH \'$**.*\' COLUMNS(a FOR ORDINALITY), b VARCHAR(8) PATH \'$\')) AS jt
      client_cs_name=utf8
      connection_cl_name=utf8_general_ci
      view_body_utf8=select `jt`.`a` AS `a`,`jt`.`b` AS `b` from JSON_TABLE(\'{}\', \'$\' COLUMNS (NESTED PATH \'$**.*\'COLUMNS (`a` FOR ORDINALITY, ), `b` varchar(8) PATH \'$\')) `jt`
      mariadb-version=100600
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: