JSqlParser4.3版本无法解析mysql中JSON_OBJECT函数抛出ParseException异常
2022年3月31日，找了一下午的bug记录一次bug在mybatis-plus中使用拦截器时，一般都会使用到jsqlParser解析器，但是这个解析器还无法识别JSON_OBJECT函数中存在表字段问题，虽然4.3版本中已经增加了对JSON_OBJECT函数的关键词支持，并没有对JSON_OBJECT中使用表字段增加支持...
·
2022年3月31日，找了一下午的bug
bug 已经提交issues，详情请看GitHub，地址：https://github.com/JSQLParser/JSqlParser/issues/1504
使用JSqlparser4.3版作者提供的补丁版后解决，下载地址：https://download.csdn.net/download/qq_19749625/85099964
2022年4月9号 新增bug: JSqlParser4.3补丁版本无法解析mysql中JSON_OBJECT函数存在其它函数问题
以上问题使用JSqlparser4.4版作者提供的应急版后解决，下载地址：https://download.csdn.net/download/qq_19749625/85099990
作者提供的在线sql解析网站（访问非常慢）：jsqlformatter
2022年3月31日，找了一下午的bug
异常sql：
如下方代码中所展示的那样，JSON_OBJECT函数会报ParseException异常。
// this is true
SELECT JSON_OBJECT(key person value account,key personName value account_name) obj FROM tb_review_result trr LEFT JOIN tb_person tp ON trr.account = tp.account
// this is true
SELECT JSON_OBJECT(person:account,personName:account) obj FROM tb_review_result trr LEFT JOIN tb_person tp ON trr.account = tp.account
// this is bad
SELECT JSON_OBJECT(person:trr.account,personName:tp.account) obj FROM tb_review_result trr LEFT JOIN tb_person tp ON trr.account = tp.account
// this is bad
SELECT JSON_OBJECT(person:'1',personName:'1') obj FROM tb_review_result trr LEFT JOIN tb_person tp ON trr.account = tp.account
// this is bad
SELECT JSON_OBJECT(key person value '1',key personName value '1') obj FROM tb_review_result trr LEFT JOIN tb_person tp ON trr.account = tp.account
异常：
at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39)
at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserSingle(JsqlParserSupport.java:52)
at com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor.beforeQuery(TenantLineInnerInterceptor.java:72)
at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:78)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
at com.sun.proxy.$Proxy166.query(Unknown Source)
at com.github.pagehelper.util.ExecutorUtil.executeAutoCount(ExecutorUtil.java:169)
at com.github.pagehelper.PageInterceptor.count(PageInterceptor.java:178)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:121)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
at com.sun.proxy.$Proxy166.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
... 157 more
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "SELECT" <K_SELECT>
at line 18, column 10.
Was expecting one of:
"!"
"("
"NOT"
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:32047)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:31880)
at net.sf.jsqlparser.parser.CCJSqlParser.AndExpression(CCJSqlParser.java:9818)
at net.sf.jsqlparser.parser.CCJSqlParser.OrExpression(CCJSqlParser.java:9757)
at net.sf.jsqlparser.parser.CCJSqlParser.XorExpression(CCJSqlParser.java:9738)
at net.sf.jsqlparser.parser.CCJSqlParser.AndExpression(CCJSqlParser.java:9811)
at net.sf.jsqlparser.parser.CCJSqlParser.OrExpression(CCJSqlParser.java:9757)
at net.sf.jsqlparser.parser.CCJSqlParser.XorExpression(CCJSqlParser.java:9738)
at net.sf.jsqlparser.parser.CCJSqlParser.Expression(CCJSqlParser.java:9709)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectExpressionItem(CCJSqlParser.java:6338)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectItem(CCJSqlParser.java:6490)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectItemsList(CCJSqlParser.java:6328)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:5663)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:5892)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:5540)
at net.sf.jsqlparser.parser.CCJSqlParser.SubSelect(CCJSqlParser.java:14458)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItem(CCJSqlParser.java:8013)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:5676)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:5892)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:5540)
at net.sf.jsqlparser.parser.CCJSqlParser.SubSelect(CCJSqlParser.java:14458)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItem(CCJSqlParser.java:8013)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:5676)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:5892)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:5540)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:5534)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:223)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:144)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:188)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:63)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:38)
2022年4月9号新增bug: JSqlParser4.3补丁版本无法解析mysql中JSON_OBJECT函数存在其它函数问题
以上问题使用JSqlparser4.4版作者提供的应急版后解决，下载地址：https://download.csdn.net/download/qq_19749625/85099990
异常sql如下：
SELECT JSON_ARRAYAGG(obj) FROM (SELECT trt.relevance_id,JSON_OBJECT('id',CAST(trt.id AS CHAR),'taskName',trt.task_name,'openStatus',trt.open_status,'taskSort',trt.task_sort) as obj FROM tb_review_task trt ORDER BY trt.task_sort ASC)
异常如下
Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: select count(0) from (
SELECT
*
FROM
(SELECT
tct.id as relevance_id,
tct.declare_id,
'0' as review_type,
tct.contests_name,
tct.contests_level,
tct.contests_type,
tct.principals,
tp.person_name,
tct.deleted,
(SELECT JSON_ARRAYAGG(obj) FROM (SELECT trt.relevance_id,JSON_OBJECT('id',CAST(trt.id AS CHAR),'taskName',trt.task_name,'openStatus',trt.open_status,'taskSort',trt.task_sort) as obj FROM tb_review_task trt ORDER BY trt.task_sort ASC)tmp WHERE tmp.relevance_id = tct.id) as task_sort_list,
IFNULL((SELECT COUNT(id) FROM tb_contests_apply tcp WHERE tcp.handle_id = tct.id AND tcp.contests_state = 1 AND tcp.deleted = 0),0) as total,
-- (SELECT COUNT(id) FROM tb_review_task trt WHERE trt.relevance_id = tct.id AND trt.review_type = 0 AND trt.task_sort = 1 AND trt.deleted = 0) as calculated
IFNULL((SELECT COUNT(id) FROM tb_review_total_score WHERE relevance_id = tct.id AND deleted = 0 GROUP BY apply_id),0) as calculated
FROM tb_contests_transact tct
LEFT JOIN tb_person tp ON tp.account = tct.principals) tmp
WHERE (deleted = ?)
) tmp_count
at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39)
at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserSingle(JsqlParserSupport.java:52)
at com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor.beforeQuery(TenantLineInnerInterceptor.java:72)
at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:78)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
at com.sun.proxy.$Proxy166.query(Unknown Source)
at com.github.pagehelper.util.ExecutorUtil.executeAutoCount(ExecutorUtil.java:169)
at com.github.pagehelper.PageInterceptor.count(PageInterceptor.java:178)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:121)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
at com.sun.proxy.$Proxy166.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
... 157 more
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "SELECT" <K_SELECT>
at line 18, column 10.
Was expecting one of:
"!"
"("
"NOT"
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:32047)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:31880)
at net.sf.jsqlparser.parser.CCJSqlParser.AndExpression(CCJSqlParser.java:9818)
at net.sf.jsqlparser.parser.CCJSqlParser.OrExpression(CCJSqlParser.java:9757)
at net.sf.jsqlparser.parser.CCJSqlParser.XorExpression(CCJSqlParser.java:9738)
at net.sf.jsqlparser.parser.CCJSqlParser.AndExpression(CCJSqlParser.java:9811)
at net.sf.jsqlparser.parser.CCJSqlParser.OrExpression(CCJSqlParser.java:9757)
at net.sf.jsqlparser.parser.CCJSqlParser.XorExpression(CCJSqlParser.java:9738)
at net.sf.jsqlparser.parser.CCJSqlParser.Expression(CCJSqlParser.java:9709)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectExpressionItem(CCJSqlParser.java:6338)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectItem(CCJSqlParser.java:6490)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectItemsList(CCJSqlParser.java:6328)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:5663)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:5892)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:5540)
at net.sf.jsqlparser.parser.CCJSqlParser.SubSelect(CCJSqlParser.java:14458)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItem(CCJSqlParser.java:8013)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:5676)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:5892)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:5540)
at net.sf.jsqlparser.parser.CCJSqlParser.SubSelect(CCJSqlParser.java:14458)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItem(CCJSqlParser.java:8013)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:5676)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:5892)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:5540)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:5534)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:223)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:144)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:188)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:63)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:38)
Supported Standard Compliant Syntax is shown here.
// this was the correct syntax
SELECT JSON_OBJECT(key 'account' value account_col) as obj FROM tb_team;
// this was also the correct syntax
SELECT JSON_OBJECT(key account : account_col) as obj FROM tb_team;
// this was also the correct syntax
SELECT JSON_OBJECT( account : account_col) as obj FROM tb_team;
// this is success sql
SELECT JSON_OBJECT('account','some string') as obj FROM tb_team;
You can test it here online
老外给的答案，经过测试，可信、可行、可用，但是数据库连接工具会报错
记录一次bug
在mybatis-plus中使用拦截器时，一般都会使用到jsqlParser解析器，但是这个解析器还无法识别JSON_OBJECT函数中存在表字段问题，虽然4.3版本中已经增加了对JSON_OBJECT函数的关键词支持，并没有对JSON_OBJECT中使用表字段增加支持
为了让老外看到懂，我还用辣鸡英语写了一份英文版的，在GitHub上提iss…了
Mysql version: 8.0
JSQLParser verison: 4.3
sql example：
// this is bad sql
SELECT JSON_OBJECT('account',account) as obj FROM tb_team;
// this is success sql
SELECT JSON_OBJECT('account','some string') as obj FROM tb_team;
bad sql exception:
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-q8eifku1-1649207235975)(/Users/mt-mac/Library/Application Support/typora-user-images/image-20220406085901301.png)]](https://i-blog.csdnimg.cn/blog_migrate/71d33ba98fcef022e3b2e500ddaaf971.png)
DDL
CREATE TABLE `tb_team` (
`id` bigint(20) NOT NULL COMMENT '雪花id',
`process_instance_id` varchar(64) DEFAULT NULL COMMENT '流程id',
`relevance_id` bigint(20) DEFAULT NULL COMMENT '发布id',
`apply_id` bigint(20) DEFAULT NULL COMMENT '报名id',
`account` varchar(32) DEFAULT NULL COMMENT '学工号',
`originator` varchar(32) DEFAULT NULL COMMENT '发起人',
`team_type` tinyint(4) DEFAULT NULL COMMENT '类型',
`member_type` tinyint(4) DEFAULT NULL COMMENT '成员类型',
`join_type` tinyint(4) DEFAULT NULL COMMENT '加入类型',
`open_status` tinyint(4) DEFAULT NULL COMMENT '成员状态',
`creator` varchar(64) DEFAULT NULL COMMENT '创建者',
`updater` varchar(64) DEFAULT NULL COMMENT '修改者',
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
`deleted` bit(1) DEFAULT b'0' COMMENT '删除标志',
`tenant_id` bigint(20) DEFAULT NULL COMMENT '租户id',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='队伍成员表';
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yuGKXNXI-1649207235976)(/Users/mt-mac/Library/Application Support/typora-user-images/image-20220406090151758.png)]](https://i-blog.csdnimg.cn/blog_migrate/a050137e38b55da04517e4962fcda89f.png)
An exception will occur if the ‘account’ column comes from the tb_team table
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
5
0- 0
已为社区贡献14条内容
所有评论(0)