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)
|
Bug
Major