开发者您好,
环境:
版本:nocobase:0.19.0-alpha.5,Node:v20.10.0,Yarn:1.22.21
操作系统:Alinos 23 x86_64(el8兼容),数据库:greatsql-8.0.32-25.1.el8(MySQL8兼容),
复现情况:
100%复现
现象 :
通过 数据表管理 --> 创建数据表 --> 连接数据库视图 时,视图部分列不支持选择字段类型,导致添加失败(未勾选 允许增删改操作)。
视图v1的创建语句为:
SELECT b.shiyongren as user,b.room,b.bumeng,a.MAC_Address,a.h ,a.IP_Address
FROM
(SELECT MAC_Address, COUNT()/6 as h ,IP_Address
FROM t_arpaistroy
WHERE (HOUR(createdAt)<1 OR HOUR(createdAt)>10 ) #UTC时间=CST-8
AND IP_Address LIKE '172%' #筛选客户端IP
GROUP BY MAC_Address,IP_Address) AS a
LEFT JOIN
(SELECT MAC_Address,shiyongren,room,bumeng
FROM djb) AS b
ON a.MAC_Address = b.MAC_Address
ORDER BY b.room DESC,b.bumeng DESC,
其中t_arpaistroy为通过nocobase建立,通过其他方法写入的表,djb为通过nocobase建立,通过nocobase添加和导入的表。通过连接数据库视图,其中h 既( COUNT()/6 )列无法选择 字段类型。
视图v1数据
配置界面
报错提示
其他补充说明
数据表管理 --> 创建数据表 --> SQL数据表 时,功能正常
t_arpaistroy的ddl为:
CREATE TABLE t_arpaistroy
(
createdAt
datetime(3) NULL DEFAULT NULL,
updatedAt
datetime(3) NULL DEFAULT NULL,
sort
bigint NULL DEFAULT NULL,
createdById
bigint NULL DEFAULT NULL,
updatedById
bigint NULL DEFAULT NULL,
id
bigint NOT NULL AUTO_INCREMENT,
IP_Address
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
MAC_Address
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
VLAN_ID
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
Interface
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
Aging
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
Type
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (id
) USING BTREE,
INDEX t_arpaistroy_created_by_id
(createdById
ASC) USING BTREE,
INDEX t_arpaistroy_updated_by_id
(updatedById
ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
djb的ddl为:
CREATE TABLE djb
(
createdAt
datetime(3) NULL DEFAULT NULL,
updatedAt
datetime(3) NULL DEFAULT NULL,
sort
bigint NULL DEFAULT NULL,
createdById
bigint NULL DEFAULT NULL,
updatedById
bigint NULL DEFAULT NULL,
id
bigint NOT NULL AUTO_INCREMENT,
room
bigint NULL DEFAULT NULL,
BuMeng
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
ShiYongRen
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
shebeixinghao
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
yingpanhao
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
MAC_Address
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
chuchangshijian
datetime(3) NULL DEFAULT NULL,
beizhu
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (id
) USING BTREE,
INDEX djb_created_by_id
(createdById
ASC) USING BTREE,
INDEX djb_updated_by_id
(updatedById
ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 117 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;