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)