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

JSON_TABLE: Inconsistent name resolution and ER_VIEW_INVALID upon combination of RIGHT and NATURAL JOIN

    XMLWordPrintable

Details

    Description

      I am not sure whether it's the next stage of any of MDEV-25256 / MDEV-25254 / MDEV-25202, or all, or neither, so filing separately.

      CREATE TABLE t1 (a INT, b VARCHAR(8));
      INSERT INTO t1 VALUES (1,'{}'),(2,'[]');
       
      CREATE TABLE t2 (a INT);
      INSERT INTO t2 VALUES (2),(3);
       
      SELECT t1.* FROM t1 NATURAL JOIN t2 RIGHT JOIN JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o) WHERE t1.a = 1;
       
      CREATE OR REPLACE VIEW v AS
      SELECT t1.* FROM t1 NATURAL JOIN t2 RIGHT JOIN JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o) WHERE t1.a = 1;
       
      SELECT * FROM v;
      

      The plain SELECT works, CREATE VIEW works, but SELECT from the view fails with ER_VIEW_INVALID.

      bb-10.6-mdev17399-hf 160bd1691

      MariaDB [test]> SELECT t1.* FROM t1 NATURAL JOIN t2 RIGHT JOIN JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o) WHERE t1.a = 1;
      Empty set (0.001 sec)
       
      MariaDB [test]> 
      MariaDB [test]> CREATE OR REPLACE VIEW v AS
          -> SELECT t1.* FROM t1 NATURAL JOIN t2 RIGHT JOIN JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o) WHERE t1.a = 1;
      Query OK, 0 rows affected (0.011 sec)
       
      MariaDB [test]> 
      MariaDB [test]> SELECT * FROM v;
      ERROR 1356 (HY000): View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
      

      MariaDB [test]> show create view v \G
      *************************** 1. row ***************************
                      View: v
               Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (JSON_TABLE(`test`.`t1`.`b`, '$' COLUMNS (`o` FOR ORDINALITY)) `jt` left join (`test`.`t1` join `test`.`t2` on(`test`.`t1`.`a` = `test`.`t2`.`a`)) on(`test`.`t1`.`a` = `jt`.`o`)) where `test`.`t1`.`a` = 1
      character_set_client: utf8
      collation_connection: utf8_general_ci
      1 row in set, 1 warning (0.001 sec)
      

      MariaDB [test]> ANALYZE FORMAT=JSON SELECT t1.* FROM t1 NATURAL JOIN t2 RIGHT JOIN JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o) WHERE t1.a = 1\G
      *************************** 1. row ***************************
      ANALYZE: {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.411648062,
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "r_loops": 1,
            "rows": 2,
            "r_rows": 2,
            "r_table_time_ms": 0.204035477,
            "r_other_time_ms": 0.028717069,
            "filtered": 100,
            "r_filtered": 50,
            "attached_condition": "t1.a = 1"
          },
          "block-nl-join": {
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "r_loops": 1,
              "rows": 2,
              "r_rows": 2,
              "r_table_time_ms": 0.093966739,
              "r_other_time_ms": 0.055881052,
              "filtered": 100,
              "r_filtered": 0,
              "attached_condition": "t2.a = 1"
            },
            "buffer_type": "flat",
            "buffer_size": "174",
            "join_type": "BNL",
            "r_filtered": null
          },
          "table": {
            "table_name": "jt",
            "access_type": "ALL",
            "r_loops": 0,
            "rows": 40,
            "r_rows": null,
            "filtered": 100,
            "r_filtered": null,
            "table_function": "json_table",
            "attached_condition": "jt.o = 1"
          }
        }
      }
      1 row in set (0.002 sec)
      

      MySQL returns ER_UNKNOWN_TABLE upon the plain SELECT and CREATE VIEW.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.