Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4680

FROM subquery containing nested joins returns an error

    XMLWordPrintable

Details

    • 2021-6, 2021-7

    Description

      A FROM subquery containing nested joins returns an error. However, if the same subquery is used as an outer query, we get the expected output.

      Here are steps to reproduce the error:

      MariaDB [test]> create table c1 (a int)engine=columnstore;
      Query OK, 0 rows affected (0.381 sec)
       
      MariaDB [test]> insert into c1 values (1), (2), (3);
      Query OK, 3 rows affected (0.233 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> create table c2 (a int)engine=columnstore;
      Query OK, 0 rows affected (0.338 sec)
       
      MariaDB [test]> insert into c2 values (2);
      Query OK, 1 row affected (0.109 sec)
       
      MariaDB [test]> create table c3 (a int)engine=columnstore;
      Query OK, 0 rows affected (0.327 sec)
       
      MariaDB [test]> create table c4 (a int)engine=columnstore;
      Query OK, 0 rows affected (0.336 sec)
       
      MariaDB [test]> create table c5 (a int)engine=columnstore;
      Query OK, 0 rows affected (0.361 sec)
       
      MariaDB [test]> select * from
          -> (
          ->  select c1.a as col1, c2.a as col2 from
          ->   c1 left join
          ->   (
          ->    (c2 left join c3 on c2.a=c3.a) left join
          ->    (c4 left join c5 on c4.a=c5.a)
          ->    on c2.a=c4.a
          ->   )
          ->   on c1.a=c2.a
          -> ) h;
      ERROR 1815 (HY000): Internal error: IDB-1000: 'c1' and 'c2, c3, c4, c5' are not joined.
      

      Below is the same subquery as above, but used as an outer query:

      MariaDB [test]> select c1.a as col1, c2.a as col2 from
          ->   c1 left join
          ->   (
          ->    (c2 left join c3 on c2.a=c3.a) left join
          ->    (c4 left join c5 on c4.a=c5.a)
          ->    on c2.a=c4.a
          ->   )
          ->   on c1.a=c2.a;
      +------+------+
      | col1 | col2 |
      +------+------+
      |    1 | NULL |
      |    2 |    2 |
      |    3 | NULL |
      +------+------+
      3 rows in set (0.021 sec)
      

      Attachments

        Activity

          People

            dleeyh Daniel Lee (Inactive)
            tntnatbry Gagan Goel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.