MybatisPlus执行sql语句报错：Caused by: net.sf.jsqlparser.parser.ParseException_error querying database. cause: com.baomidou.mybat

先看错误：

Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL:*******省略若干
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: “(” “(” ********省略若干

直接说结论：

mybatisplus多租户使用sql拦截导致的不能识别sql语句问题

解决方法：

根据版本不同，用一下三种：
①在Mapper上加入注解：@InterceptorIgnore(tenantLine =“true”)或者@InterceptorIgnore(tenantLine =“0”)，不走多租户过滤。
②老一点的版本用@SqlParser(filter=true)
③3.1.1以下版本添加如下配置
mybatis-plus:
global-config:
sql-parser-cache: true

总结

细究原因就是jsqlparser的问题，MybatisPlusInterceptor中使用了TenantLineHandler，如果既需要这个sql又需要拦截，可以在源码增加语法规则，一般没必要，实在不行在这儿手动增加tenant-id也可以。

部分主要错误如下：

Error querying database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: select * from (select avg(CAST((tag_value) AS DOUBLE)) as  tagValue from qwe.qwe interval(1m))
Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: select * from (select avg(CAST((tag_value) AS DOUBLE)) as  tagValue from qwe.qwe interval(1m))
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:153)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
... 133 common frames omitted


Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: select * from (select avg(CAST((tag_value) AS DOUBLE)) as  tagValue from qwe.qwe interval(1m))
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:65)
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.$Proxy447.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
... 140 common frames omitted
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "("
at line 1, column 109.
Was expecting one of:
")"
"CONNECT"
"EMIT"
"EXCEPT"
"GROUP"
"HAVING"
"INTERSECT"
"MINUS"
"PIVOT"
"START"
"UNION"
"WINDOW"

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:31234)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:31067)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItem(CCJSqlParser.java:7931)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:5618)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:5827)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:5499)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:5494)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:232)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:153)
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)
at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserSingle(JsqlParserSupport.java:49)
... 145 common frames omitted
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53