liulianqian0513  发表于 2020-5-29 11:27 显示全部楼层 楼主
insert into express引擎表 select from gssys引擎表无数据。
gbase> insert into columns_express select * from information_schema.columns;
Query OK, 0 rows affected (Elapsed: 00:00:01.08)
将gssys引擎表放入子查询可以正常插入数据。
insert into columns_express select * from (select * from information_schema.columns)t;

落笔映浮华  发表于 2020-5-29 12:35 显示全部楼层 沙发
问题分析:
测试发现,在如下两种情况下会导致insert无数据。
(1)表的列comment含有乱码
(2)视图的基表不存在或者有变更
测试过程如下:
set _gbase_query_path=1;
(1)正常插入数据
gbase> insert into columns_express select * from information_schema.columns;                 
Query OK, 1346 rows affected (Elapsed: 00:00:00.46)
Records: 1346  Duplicates: 0  Warnings: 0
库中创建了comment包含乱码的表
gbase> show create table t_comment;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                            |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------+
| t_comment | CREATE TABLE "t_comment" (
  "c1" int(11) DEFAULT NULL COMMENT '??'
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace'     |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
执行跨引擎insert
gbase> insert into columns_express select * from information_schema.columns;
Query OK, 0 rows affected (Elapsed: 00:00:01.08)
gbase> insert into columns_express select * from (select * from information_schema.columns)t;
Query OK, 1347 rows affected, 1 warning (Elapsed: 00:00:00.46)
Records: 1347  Duplicates: 0  Warnings: 0
gbase> show warnings;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xD6\xD0\xCE\xC4' for column 'COLUMN_COMMENT' at row 1 |
+---------+------+---------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
(2)
gbase> create table t(c1 int);
Query OK, 0 rows affected (Elapsed: 00:00:00.11)
gbase> create view v_t as select * from t;
Query OK, 0 rows affected (Elapsed: 00:00:00.12)
跨引擎插入数据正常。
gbase> insert into columns_express select * from information_schema.columns;
Query OK, 1348 rows affected (Elapsed: 00:00:00.44)
Records: 1348  Duplicates: 0  Warnings: 0
gbase> drop table t;
Query OK, 0 rows affected (Elapsed: 00:00:00.08)
跨引擎插入数据异常。
gbase> insert into columns_express select * from information_schema.columns;
Query OK, 0 rows affected (Elapsed: 00:00:01.09)
gbase> insert into columns_express select * from (select * from information_schema.columns)t;
Query OK, 1346 rows affected, 1 warning (Elapsed: 00:00:00.48)
Records: 1346  Duplicates: 0  Warnings: 0
gbase> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                   |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Warning | 1356 | View 'guo.v_t' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

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