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

          Ascending order - Click to sort in descending order
          tntnatbry Gagan Goel (Inactive) created issue -
          tntnatbry Gagan Goel (Inactive) made changes -
          Field Original Value New Value
          Status Open [ 1 ] In Progress [ 3 ]
          gdorman Gregory Dorman (Inactive) made changes -
          Fix Version/s 5.6.1 [ 25031 ]
          gdorman Gregory Dorman (Inactive) made changes -
          Issue Type Task [ 3 ] Bug [ 1 ]
          gdorman Gregory Dorman (Inactive) made changes -
          Sprint 2021-6 [ 509 ]
          gdorman Gregory Dorman (Inactive) made changes -
          Rank Ranked higher
          gdorman Gregory Dorman (Inactive) made changes -
          Sprint 2021-6 [ 509 ] 2021-6, 2021-7 [ 509, 514 ]
          tntnatbry Gagan Goel (Inactive) made changes -
          Assignee Gagan Goel [ tntnatbry ] Roman [ drrtuy ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          tntnatbry Gagan Goel (Inactive) added a comment -

          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.
          gdorman Gregory Dorman (Inactive) made changes -
          Affects Version/s All [ 25803 ]
          Affects Version/s 5.5.1 [ 25030 ]
          Affects Version/s 6.1.1 [ 25600 ]
          gdorman Gregory Dorman (Inactive) made changes -
          Labels tech_debt
          tntnatbry Gagan Goel (Inactive) made changes -
          Fix Version/s 6.1.1 [ 25600 ]
          gdorman Gregory Dorman (Inactive) made changes -
          Fix Version/s 6.1.1 [ 25600 ]
          drrtuy Roman made changes -
          Status In Review [ 10002 ] In Testing [ 10301 ]
          drrtuy Roman made changes -
          Assignee Roman [ drrtuy ] Daniel Lee [ dleeyh ]
          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

          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
          dleeyh Daniel Lee (Inactive) made changes -
          Resolution Fixed [ 1 ]
          Status In Testing [ 10301 ] Closed [ 6 ]
          toddstoffel Todd Stoffel (Inactive) made changes -
          Affects Version/s All [ 25803 ]

          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.