[gogf/gf]from子查询报SHOW FULL COLUMNS FROM错误

SourceIpList, err := g.Model(appListTableName).Fields("Address").Where("AppName=?", queryAppName).Array()
accessData := g.Model(`Appaccess ac`).
        LeftJoin(`hz_app_list ha`, `ac.dst_ip=ha.Address`).
        Fields(`ha.AppName,ac.proto,ac.dst_port,ac.count,ac.accesstype,ac.dst_ip`).
        WhereIn(`ac.src_ip`, SourceIpList).
        Where("ac.accesstype=?", "hzinner").
        Distinct()
res, _ := g.Model("? aa", accessData).
        Fields(`aa.AppName,aa.proto,aa.dst_port,SUM(count) as count,aa.accesstype`).
        Group(`AppName,proto,dst_port,accesstype`).
        All()

GoFrame CLI Tool v1.17.0, https://goframe.org
GoFrame Version: v1.16.6 in current go.mod
CLI Installed At: D:\Go\bin\gf.exe
CLI Built Detail:
  Go Version:  go1.16.3
  GF Version:  v1.16.4
  Git Commit:  971ed46f0b9d4dfebd1907cd3ed851cf9e1a5503
  Build Time:  2021-08-10 10:22:59

github.com/gogf/gf v1.16.6

2021-12-23 09:56:33.649 [ERRO] [ 19 ms] [default] SHOW FULL COLUMNS FROM
Error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
 '' at line 1
Stack:
1.  gfast/app/migrationtool/service.AppCallOtherInner
    D:/project/src/app/migrationtool/service/app_call.go:42
2.  gfast/app/migrationtool/api.AppCallOtherInner
    D:/project/src/app/migrationtool/api/call.go:29
3.  gfast/middleware.Auth
    D:/project/src/middleware/auth.go:72
4.  gfast/middleware.Ctx
    D:/project/src/middleware/auth.go:46
5.  gfast/app/system/api.(*auth).authAfterFunc
    D:/project/src/app/system/api/auth.go:126
6.  github.com/goflyfox/gtoken/gtoken.(*GfToken).authMiddleware
    D:/project/src/pkg/mod/github.com/tiger1103/gtoken@v1.4.8/gtoken/gtoken.go:331
7.  gfast/middleware.CORS
    D:/project/src/middleware/cors.go:15

2021-12-23 09:56:33.685 [ERRO] [  0 ms] [default] SHOW FULL COLUMNS FROM
Error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
 '' at line 1
Stack:
1.  gfast/app/migrationtool/service.AppCallOtherInner
    D:/project/src/app/migrationtool/service/app_call.go:44
2.  gfast/app/api.AppCallOtherInner
    D:/project/src/app/migrationtool/api/call.go:29
3.  gfast/middleware.Auth
    D:/project/src/middleware/auth.go:72
4.  gfast/middleware.Ctx
    D:/project/src/middleware/auth.go:46
5.  gfast/app/system/api.(*auth).authAfterFunc
    D:/project/src/app/system/api/auth.go:126
6.  github.com/goflyfox/gtoken/gtoken.(*GfToken).authMiddleware
    D:/project/src/pkg/mod/github.com/tiger1103/gtoken@v1.4.8/gtoken/gtoken.go:331
7.  gfast/middleware.CORS
    D:/project/src/middleware/cors.go:15

2021-12-23 09:56:33.764 [ERRO] [ 32 ms] [default] SHOW FULL COLUMNS FROM
Error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
 '' at line 1
Stack:
1.  gfast/app/migrationtool/service.AppCallOtherInner
    D:/project/src/app/migrationtool/service/app_call.go:44
2.  gfast/app/migrationtool/api.AppCallOtherInner
    D:/project/src/app/migrationtool/api/call.go:29
3.  gfast/middleware.Auth
    D:/project/src/middleware/auth.go:72
4.  gfast/middleware.Ctx
    D:/project/src/middleware/auth.go:46
5.  gfast/app/system/api.(*auth).authAfterFunc
    D:/project/src/app/system/api/auth.go:126
6.  github.com/goflyfox/gtoken/gtoken.(*GfToken).authMiddleware
    D:/project/src/pkg/mod/github.com/tiger1103/gtoken@v1.4.8/gtoken/gtoken.go:331
7.  gfast/middleware.CORS
    D:/project/src/middleware/cors.go:15

// 实际可执行到子查询这一步
2021-12-23 09:56:33.848 [DEBU] [ 35 ms] [default] SELECT aa.AppName,aa.proto,aa.dst_port,SUM(count) as count,aa.accesstype FROM (SELECT DISTINCT ha.AppName,ac.proto,ac.dst_port,ac.count,ac.accesstype,ac.dst_ip FROM `Appaccess` ac LEFT JOIN `hz_app_list` ha ON (ac.dst_ip=ha.Address) WHERE (ac.src_ip IN ('1.1.1.1','2.2.2.2')) AND (ac.accesstype='hzinner')) aa GROUP BY `AppName`,`proto`,`dst_port`,`accesstype`

lichunke

请提供一下2表的数据结构，建议查看一下mysql的版本，再执行select @sql_model查询数据库是否开启了开启严格模式

houseme

CREATE TABLE `Appaccess` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `src_ip` varchar(20) NOT NULL COMMENT '源IP',
  `dst_ip` varchar(20) NOT NULL COMMENT '目的IP',
  `dst_port` varchar(20) NOT NULL COMMENT '目标端口',
  `proto` varchar(20) DEFAULT NULL COMMENT '协议',
  `count` int(20) NOT NULL DEFAULT '1' COMMENT '访问次数统计',
  `accesstype` varchar(20) NOT NULL COMMENT '经典内网互访/ProdvpcToClassic/ClassicToProdvpc',
  `ContainerName` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '容器应用名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `IX_doctor_batch` (`src_ip`,`dst_ip`,`dst_port`,`proto`,`accesstype`,`ContainerName`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='应用互访信息'

CREATE TABLE `hz_app_list` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `InstanceName` varchar(50) NOT NULL COMMENT '实例名称',
  `Address` varchar(50) NOT NULL COMMENT 'IP地址',
  `AppName` varchar(100) NOT NULL COMMENT '应用名称',
  `AppType` varchar(50) DEFAULT NULL COMMENT '类型：app/rds/redis',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=241 DEFAULT CHARSET=utf8mb4 COMMENT='经典网络新核心应用和IP对照表'

数据库版本及是否开启严格模式：

lichunke

这个文档应该可以帮到您 https://www.cnblogs.com/clschao/articles/9962347.html

该sqlSELECT aa.AppName,aa.proto,aa.dst_port,SUM(count) as count,aa.accesstype FROM (SELECT DISTINCT ha.AppName,ac.proto,ac.dst_port,ac.count,ac.accesstype,ac.dst_ip FROM Appaccess ac LEFT JOIN hz_app_list ha ON (ac.dst_ip=ha.Address) WHERE (ac.src_ip IN ('1.1.1.1','2.2.2.2')) AND (ac.accesstype='hzinner')) aa GROUP BY AppName,proto,dst_port,accesstype 运行是没问题的，这个sql的逻辑后面还有其他的sql吗？

也可以贴一下这一块的代码

houseme

https://github.com/gogf/gf/issues/1530#issuecomment-1002460315 会拿到sql执行后的数据

可以跑通后面的逻辑个人猜想是：orm在执行sql之前都会SHOW FULL COLUMNS FROM 表名 from子查询时，g.Model("? aa", accessData) 这里是数据对象，在SHOW FULL COLUMNS FROM 的时候，就会报表不存在 2021-12-23 09:56:33.764 [ERRO] [ 32 ms] [default] SHOW FULL COLUMNS FROM Error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

lichunke

试试这个原生的方法吧 https://pkg.go.dev/github.com/gogf/gf/database/gdb?utm_source=godoc#Core.Query

houseme

可以试试v2.0的 ModelRaw https://goframe.org/pages/viewpage.action?pageId=1114373#ORM%E9%93%BE%E5%BC%8F%E6%93%8D%E4%BD%9C%E6%A8%A1%E5%9E%8B%E5%88%9B%E5%BB%BA-Raw

houseme

https://github.com/gogf/gf/issues/1551

houseme

@lichunke @houseme 大家好，这个子查询没有查询到表结构的报错其实并不影响功能，只是底层的日志组件会打印所有的错误。已处理。

gqcn

[gogf/gf]from子查询报SHOW FULL COLUMNS FROM错误

回答