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

FROM subquery containing nested joins returns an error

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

          For QA: Query to confirm the issue and verify the fix works is in the issue description.

          tntnatbry Gagan Goel (Inactive) added a comment - For QA: Query to confirm the issue and verify the fix works is in the issue description.

          Build verified: 5.6.1 ( Drone #2229 )

          MariaDB [mytest]> create table c1 (a int)engine=columnstore;
          Query OK, 0 rows affected (0.137 sec)
           
          MariaDB [mytest]> insert into c1 values (1), (2), (3);
          Query OK, 3 rows affected (0.201 sec)
          Records: 3  Duplicates: 0  Warnings: 0
           
          MariaDB [mytest]> create table c2 (a int)engine=columnstore;
          Query OK, 0 rows affected (0.126 sec)
           
          MariaDB [mytest]> insert into c2 values (2);
          Query OK, 1 row affected (0.117 sec)
           
          MariaDB [mytest]> create table c3 (a int)engine=columnstore;
          Query OK, 0 rows affected (0.127 sec)
           
          MariaDB [mytest]> create table c4 (a int)engine=columnstore;
          Query OK, 0 rows affected (0.118 sec)
           
          MariaDB [mytest]> create table c5 (a int)engine=columnstore;
          Query OK, 0 rows affected (0.670 sec)
           
          MariaDB [mytest]> 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;
          +------+------+
          | col1 | col2 |
          +------+------+
          |    1 | NULL |
          |    2 |    2 |
          |    3 | NULL |
          +------+------+
          3 rows in set
          

          dleeyh Daniel Lee (Inactive) added a comment - Build verified: 5.6.1 ( Drone #2229 ) MariaDB [mytest]> create table c1 (a int)engine=columnstore; Query OK, 0 rows affected (0.137 sec)   MariaDB [mytest]> insert into c1 values (1), (2), (3); Query OK, 3 rows affected (0.201 sec) Records: 3 Duplicates: 0 Warnings: 0   MariaDB [mytest]> create table c2 (a int)engine=columnstore; Query OK, 0 rows affected (0.126 sec)   MariaDB [mytest]> insert into c2 values (2); Query OK, 1 row affected (0.117 sec)   MariaDB [mytest]> create table c3 (a int)engine=columnstore; Query OK, 0 rows affected (0.127 sec)   MariaDB [mytest]> create table c4 (a int)engine=columnstore; Query OK, 0 rows affected (0.118 sec)   MariaDB [mytest]> create table c5 (a int)engine=columnstore; Query OK, 0 rows affected (0.670 sec)   MariaDB [mytest]> 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; +------+------+ | col1 | col2 | +------+------+ | 1 | NULL | | 2 | 2 | | 3 | NULL | +------+------+ 3 rows in set

          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.