分级查询表update后start with connect by报错

分级查询的表做update后,start with connect by查询报错。
测试版本8.6.2.19-R2.84004
drop table t;
create table t(id int,name varchar(16),pid int) REPLICATED;
insert into t values(1,'a',0);
insert into t values(2,'b',1);
insert into t values(3,'c',1);
select * from t connect by prior t.id=t.pid start with pid=0;
update t set name='abc' where id=3;
update表后再次执行查询。
select * from t connect by prior t.id=t.pid start with pid=0;
gbase> select * from t connect by prior t.id=t.pid start with pid=0;
ERROR 1708 (HY000): [10.10.10.101: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 /*10.10.10.102_2229_12_2017-09-04_17:28:45*/ `guo.t`.`id` AS `id`, `guo.t`.`name` AS `name`, `guo.t`.`pid` AS `pid` FROM `guo`.`t` `guo.t` START WITH (`guo.t`.`pid` = 0) CONNECT BY (PRIOR `guo.t`.`id` = `guo.t`.`pid`)


2 回答

gbase> show variables like '%gbase_fast_update%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| gbase_fast_update | ON    |
+-------------------+-------+
1 row in set (Elapsed: 00:00:00.01)
gbase> show variables like '%_gbase_connect_by_support_table_with_deleted_records%';
+------------------------------------------------------+-------+
| Variable_name                                        | Value |
+------------------------------------------------------+-------+
| _gbase_connect_by_support_table_with_deleted_records | OFF   |
+------------------------------------------------------+-------+
1 row in set (Elapsed: 00:00:00.00)
对表进行快速update,实际采用的是delete+insert,在_gbase_connect_by_support_table_with_deleted_records参数关闭的情况下报错。

2018-5-25 11:27
关闭快速update或者打开_gbase_connect_by_support_table_with_deleted_records参数,分级查询支持删除过记录的表。
gbase> set global _gbase_connect_by_support_table_with_deleted_records=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> show variables like '%_gbase_connect_by_support_table_with_deleted_records%';
+------------------------------------------------------+-------+
| Variable_name                                        | Value |
+------------------------------------------------------+-------+
| _gbase_connect_by_support_table_with_deleted_records | ON    |
+------------------------------------------------------+-------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select * from t connect by prior t.id=t.pid start with pid=0;
+------+------+------+
| id   | name | pid  |
+------+------+------+
|    1 | a    |    0 |
|    2 | b    |    1 |
|    3 | abc  |    1 |
+------+------+------+
3 rows in set (Elapsed: 00:00:00.02)

2018-5-25 11:28

撰写回答

您需要登录后才可以回帖 登录 | 立即注册

提问者

发布167
回答440

相关问题

相关资料