南大通用GBase 8a递归查询Start with connect by 报错 Restrict: Connect by clause must be used with table not deleted

GBase 8a支持递归查询，要求复制表的数据不能有删除的，否则报DETAIL: (GBA-01EX-700) Gbase general error: Restrict: Connect by clause must be used with table not deleted

报错样例

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

gbase> delete t3 where id=3;
Query OK, 1 row affected (Elapsed: 00:00:00.04)

gbase> select * from t3 connect by pid=prior id start with pid=0;
ERROR 1708 (HY000): [gbase_rh7_001:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: (GBA-01EX-700) Gbase general error: Restrict: Connect by clause must be used with table not deleted
SQL: SELECT /*gbase_rh7_001_12_16_2020-11-16_17:37:32*/ /*+ TID('589860') */ `vcname000001.testdb.t3`.`id` AS `id`, `vcname000001.testdb.t3`.`name` AS `name`, `vcname000001.testdb.t3`.`pid` AS `pid` FROM `testdb`.`t3` `vcname000001.testdb.t3` START WITH (`vcname000001.testdb.t3`.`pid` = 0) CONNECT BY (`vcname000001.testdb.t3`.`pid` = PRIOR
gbase>

解决方案

1、新建一个表，不要删除数据，或者用子查询。

gbase> select * from (select * from t3) a 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 rows in set (Elapsed: 00:00:00.03)

2、打开参数 _gbase_connect_by_support_table_with_deleted_records

gbase> show variables like '%deleted%';
+------------------------------------------------------+-------+
| Variable_name                                        | Value |
+------------------------------------------------------+-------+
| _gbase_connect_by_support_table_with_deleted_records | OFF   |
+------------------------------------------------------+-------+
1 row in set (Elapsed: 00:00:00.00)

gbase> set _gbase_connect_by_support_table_with_deleted_records=1;
ERROR 1229 (HY000): Variable '_gbase_connect_by_support_table_with_deleted_records' is a GLOBAL variable and should be set with SET GLOBAL
gbase> set global _gbase_connect_by_support_table_with_deleted_records=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

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 rows in set (Elapsed: 00:00:00.01)