南大通用GBase 8a 递归查询Start with connect by 报错 FROM clause of hierarchical query can only include one replicated table.

GBase 8a 数据库集群支持递归查询，但要求其必须是复制表，否则报错。ERROR 1149 (42000): (GBA-02SC-1001) FROM clause of hierarchical query can only include one replicated table.

报错样例

gbase> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE "t2" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(100) DEFAULT NULL,
  "pid" int(11) DEFAULT NULL
) ENGINE=EXPRESS DISTRIBUTED BY('id') DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from t2;
+------+-----------+------+
| id   | name      | pid  |
+------+-----------+------+
|    1 | First     |    0 |
|    2 | Fist_1    |    1 |
|    3 | First_2   |    1 |
|    4 | First_1_1 |    2 |
+------+-----------+------+
4 rows in set (Elapsed: 00:00:00.01)

gbase> select * from t2 connect by pid=prior id start with pid=0;
ERROR 1149 (42000): (GBA-02SC-1001) FROM clause of hierarchical query can only include one replicated table.

解决方案

改成复制表

gbase> create table t3 replicated as select * from t2;
Query OK, 4 rows affected (Elapsed: 00:00:00.14)

gbase> select * from t3 connect by pid=prior id start with pid=0;
+------+-----------+------+
| id   | name      | pid  |
+------+-----------+------+
|    1 | First     |    0 |
|    2 | Fist_1    |    1 |
|    4 | First_1_1 |    2 |
|    3 | First_2   |    1 |
+------+-----------+------+
4 rows in set (Elapsed: 00:00:00.02)