SELECT is_insertable_into FROM information_schema.tables 过滤视图，结果不准确

【标题描述】:SELECT is_insertable_into  FROM information_schema.tables的结果不准确
【测试类型：SQL功能】【测试版本：3.0.0】 问题描述

【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):
centos 7.6
【测试环境】(单机/1主x备x级联备):
单机
【被测功能】:
视图
【测试类型】:
功能测试
【数据库版本】(查询命令: gaussdb –V):
![Image description](https://images.gitee.com/uploads/images/2022/0506/183457_e10067f6_9337872.png "屏幕截图.png")
【预置条件】:
CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
【操作步骤】(请填写详细的操作步骤):

CREATE VIEW ro_view1 AS SELECT DISTINCT a, b FROM base_tbl; -- DISTINCT not supported

CREATE VIEW ro_view2 AS SELECT a, b FROM base_tbl GROUP BY a, b; -- GROUP BY not supported

CREATE VIEW ro_view3 AS SELECT 1 FROM base_tbl HAVING max(a) > 0; -- HAVING not supported

CREATE VIEW ro_view4 AS SELECT count(*) FROM base_tbl; -- Aggregate functions not supported

CREATE VIEW ro_view5 AS SELECT a, rank() OVER() FROM base_tbl; -- Window functions not supported

CREATE VIEW ro_view6 AS SELECT a, b FROM base_tbl UNION SELECT -a, b FROM base_tbl; -- Set ops not supported

CREATE VIEW ro_view7 AS WITH t AS (SELECT a, b FROM base_tbl) SELECT * FROM t; -- WITH not supported

CREATE VIEW ro_view8 AS SELECT a, b FROM base_tbl ORDER BY a OFFSET 1; -- OFFSET not supported

CREATE VIEW ro_view9 AS SELECT a, b FROM base_tbl ORDER BY a LIMIT 1; -- LIMIT not supported

CREATE VIEW ro_view10 AS SELECT 1 AS a; -- No base relations

CREATE VIEW ro_view11 AS SELECT b1.a, b2.b FROM base_tbl b1, base_tbl b2; -- Multiple base relations

CREATE VIEW ro_view12 AS SELECT * FROM generate_series(1, 10) AS g(a); -- SRF in rangetable

CREATE VIEW ro_view13 AS SELECT a, b FROM (SELECT * FROM base_tbl) AS t; -- Subselect in rangetable

CREATE VIEW rw_view14 AS SELECT ctid, a, b FROM base_tbl; -- System columns may be part of an updatable view

CREATE VIEW rw_view15 AS SELECT a, upper(b) FROM base_tbl; -- Expression/function may be part of an updatable view

CREATE VIEW rw_view16 AS SELECT a, b, a AS aa FROM base_tbl; -- Repeated column may be part of an updatable view

CREATE VIEW ro_view17 AS SELECT * FROM ro_view1; -- Base relation not updatable

CREATE VIEW ro_view18 AS SELECT * FROM (VALUES(1)) AS tmp(a); -- VALUES in rangetable

CREATE SEQUENCE seq;

CREATE VIEW ro_view19 AS SELECT * FROM seq; -- View based on a sequence

CREATE VIEW ro_view20 AS SELECT a, b, generate_series(1, a) g FROM base_tbl; -- SRF in targetlist not supported

SELECT table_name, is_insertable_into  FROM information_schema.tables WHERE table_name LIKE E'r_\\_view%' ORDER BY table_name;

【预期输出】:

![Image description](https://images.gitee.com/uploads/images/2022/0506/183611_14d3a872_9337872.png "屏幕截图.png")
【实际输出】:
![Image description](https://images.gitee.com/uploads/images/2022/0506/183629_557f24a5_9337872.png "屏幕截图.png")
【原因分析】:
1. 这个问题的根因
2. 问题推断过程
3. 还有哪些原因可能造成类似现象
4. 该问题是否有临时规避措施
5. 问题解决方案
6. 预计修复问题时间

【日志信息】(请附上日志文件、截图、coredump信息):

【测试代码】:

openGauss/openGauss-server

内容风险标识

评论 (3)

openGauss/openGauss-server .gitee-modal { width: 500px !important; }

内容风险标识