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

JSON_TABLE: Inconsistent name resolution with right joins

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • N/A
    • 10.6.0
    • JSON, Optimizer
    • None

    Description

      This works:

      bb-10.6-mdev17399-hf 8611fb30b

      MariaDB [test]> CREATE TABLE t (a INT) ENGINE=MyISAM;
      Query OK, 0 rows affected (0.031 sec)
       
      MariaDB [test]> SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE;
      Empty set (0.034 sec)
      

      But via a view doesn't, it complains about an unknown table (the query within the view definition is the same):

      MariaDB [test]> CREATE TABLE t (a INT) ENGINE=MyISAM;
      Query OK, 0 rows affected (0.015 sec)
       
      MariaDB [test]> CREATE VIEW v AS SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE;
      Query OK, 0 rows affected (0.016 sec)
       
      MariaDB [test]> SELECT * FROM v;
      ERROR 1109 (42S02): Unknown table 'test.t' in JSON_TABLE argument
      

      And with InnoDB istead of MyISAM (or with a non-empty MyISAM table) it also doesn't, now it complains about a cross-dependency:

      MariaDB [test]> CREATE TABLE t (a INT) ENGINE=InnoDB;
      Query OK, 0 rows affected (0.035 sec)
       
      MariaDB [test]> SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE;
      ERROR 1120 (42000): Cross dependency found in OUTER JOIN; examine your ON conditions
      

      All this seems to be inconsistent, or at least not obvious. Either there should be a simple rule for what is allowed or not, or it should be clearly stated that name resolution is non-deterministic as it depends on an execution plan (or whatever else).

      In MySQL all three variants fail with the same ER_UNKNOWN_TABLE (and the variant with view fails upon the view creation):

      MySQL 8.0.23

      MySQL [test]> CREATE TABLE t (a INT) ENGINE=MyISAM;
      Query OK, 0 rows affected (0.035 sec)
       
      MySQL [test]> SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE;
      ERROR 1109 (42S02): Unknown table 't' in a table function argument
       
      MySQL [test]> CREATE VIEW v AS SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE;
      ERROR 1109 (42S02): Unknown table 't' in a table function argument
       
      MySQL [test]> ALTER TABLE t ENGINE=InnoDB;
      Query OK, 0 rows affected (0.579 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MySQL [test]> SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE;
      ERROR 1109 (42S02): Unknown table 't' in a table function argument
      

      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.