MyBatis-Plus mybatisplus3.5.2版本用户角色关联表使用sql查询时，给表定义了别名 ur 报错

当前使用版本(必填,否则不予处理)

3.5.2

该问题是如何引起的？(确定最新版也有问题再提!!!)

用户角色关联表使用sql查询时，给表定义了别名 ur 报错

重现步骤(如果有就写完整)

报错信息

Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: SELECT

        u.id,
        u.username,
        u.nickname,
        u.mobile,
        u.gender,
        u.avatar,
        u.status,
        d.name AS deptName,
        GROUP_CONCAT( r.name ) AS roleNames,
        u.create_time AS createTime
    FROM
        sys_user u
        LEFT JOIN sys_dept d ON u.dept_id = d.id
        LEFT JOIN sys_user_role ur ON u.id = ur.user_id
        LEFT JOIN sys_role r ON ur.role_id = r.id
     WHERE u.deleted = 0 
    GROUP BY u.id] with root cause

net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "ur" at line 15, column 37.

Was expecting one of:

";"
"ACTION"
"ACTIVE"
"ALGORITHM"
"ARCHIVE"
"ARRAY"
"AS"
"AT"
"BYTE"
"CASCADE"
"CASE"
"CAST"
"CHANGE"
"CHAR"
"CHARACTER"
"CHECKPOINT"
"COLUMN"
"COLUMNS"
"COMMENT"
"COMMIT"
"CONNECT"
"COSTS"
"CYCLE"
"DBA_RECYCLEBIN"
"DEFAULT"
"DESC"
"DESCRIBE"
"DISABLE"
"DISCONNECT"
"DIV"
"DO"
"DUMP"
"DUPLICATE"
"EMIT"
"ENABLE"
"END"
"EXCLUDE"
"EXTRACT"
"FALSE"
"FILTER"
"FIRST"
"FLUSH"
"FN"
"FOLLOWING"
"FORMAT"
"FULLTEXT"
"GROUP"
"HAVING"
"HISTORY"
"INDEX"
"INSERT"
"INTERVAL"
"ISNULL"
"JSON"
"KEY"
"LAST"
"LEADING"
"LINK"
"LOCAL"
"LOG"
"MATERIALIZED"
"NO"
"NOLOCK"
"NULLS"
"OF"
"OPEN"
"OVER"
"PARALLEL"
"PARTITION"
"PATH"
"PERCENT"
"PIVOT"
"PRECISION"
"PRIMARY"
"PRIOR"
"QUERY"
"QUIESCE"
"RANGE"
"READ"
"RECYCLEBIN"
"REGISTER"
"REPLACE"
"RESTRICTED"
"RESUME"
"ROW"
"ROWS"
"SCHEMA"
"SEPARATOR"
"SEQUENCE"
"SESSION"
"SHUTDOWN"
"SIBLINGS"
"SIGNED"
"SIZE"
"SKIP"
"START"
"SUSPEND"
"SWITCH"
"SYNONYM"
"SYSTEM"
"TABLE"
"TABLESPACE"
"TEMP"
"TEMPORARY"
"TIMEOUT"
"TO"
"TOP"
"TRUE"
"TRUNCATE"
"TRY_CAST"
"TYPE"
"UNQIESCE"
"UNSIGNED"
"USER"
"VALIDATE"
"VALUE"
"VALUES"
"VIEW"
"WINDOW"
"XML"
"ZONE"
<EOF>
<K_DATETIMELITERAL>
<K_DATE_LITERAL>
<K_NEXTVAL>
<K_STRING_FUNCTION_NAME>
<S_CHAR_LITERAL>
<S_IDENTIFIER>
<S_QUOTED_IDENTIFIER>

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:31468)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:31301)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:163)
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)
at com.baomidou.mybatisplus.extension.plugins.inner.DataPermissionInterceptor.beforeQuery(DataPermissionInterceptor.java:56)
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.$Proxy241.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
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.GeneratedMethodAccessor188.invoke(Unknown Source)
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)
at com.sun.proxy.$Proxy128.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:121)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:85)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
at com.sun.proxy.$Proxy159.listUserPages(Unknown Source)
at io.jinyan.admin.service.impl.SysUserServiceImpl.listUserPages(SysUserServiceImpl.java:80)
at io.jinyan.admin.service.impl.SysUserServiceImpl$$FastClassBySpringCGLIB$$7682e304.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
at io.jinyan.admin.service.impl.SysUserServiceImpl$$EnhancerBySpringCGLIB$$28d79235.listUserPages(<generated>)
at io.jinyan.admin.controller.SysUserController.listUserPages(SysUserController.java:50)
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.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)

解决方案，希望官方能接收方案二，升级一下版本 mybatisplus启动器引入的jsqlparser4.4版本，会报异常，如果sql中表别名为 ‘ur’

方案一：升级jsqlparser4.5可以解决，但是为了不破坏依赖关系，还是使用jsqlparser4.4，但是mapper.xml 中的sql表名不要使用 ur做为表别名

方案二：下载mybatisplus3.5.2到本地，修改源码中引入的jsqlparser4.4为jsqlparser4.5版本，在引入依赖

Comment From: miemieYaho

pom依赖你可以自己升,4.5也有bug,建议你降级

Comment From: qq1461607240

pom你可以自己升，4.5依赖也有bug，建议你降级

那降级到什么版本呢

Comment From: lQIgithub

感谢，我是将别名换了，不使用ur

1018628825 @.***

------------------ 原始邮件 ------------------ 发件人: "baomidou/mybatis-plus" @.>; 发送时间: 2022年8月12日(星期五) 下午2:04 @.>; @.**@.**>; 主题: Re: [baomidou/mybatis-plus] mybatisplus2.5.2版本用户角色关联表使用sql查询时，给表定义了别名 ur 报错 (Issue #4737)

pom依赖你可以自己升,4.5也有bug,建议你降级

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>

Comment From: roostinghawk

找到 JSqlParser 源码的这里，请问这个 UR 保留字是因为 h2database 的保留字吗？对于 mysql 并不是 img_v3_02g7_7338e8a2-f347-4b98-a2aa-166c144ee58g

MyBatis-Plus mybatisplus3.5.2版本 用户角色关联表 使用sql查询时，给表定义了别名 ur 报错