[GitHub] [dolphinscheduler] github-actions[bot] commented on issue #13227: [Bug] [DAO] There's an exception about sql when visiting the page of project list.

[GitBox <gi...@apache.org>]

github-actions[bot] commented on issue #13227:
URL: https://github.com/apache/dolphinscheduler/issues/13227#issuecomment-1358970864

   ### Search before asking
   
   - [X] I had searched in the [issues](https://github.com/apache/dolphinscheduler/issues?q=is%3Aissue) and found no similar issues.
   
   
   ### What happened
   
   When I visited the page of project list the system would throw an exception about PSQLException.
   
   <img width="1716" alt="image" src="https://user-images.githubusercontent.com/4928204/208613639-c13b03ff-e5bb-4396-aef1-2c58102c8186.png">
   
   
   ```
   [INFO] 2022-12-20 15:34:35.450 +0800 org.apache.dolphinscheduler.api.aspect.AccessLogAspect:[93] - REQUEST TRACE_ID:2575e092-2a4a-4ac9-8f36-e10be1e4b5a9, LOGIN_USER:admin, URI:/dolphinscheduler/projects, METHOD:GET, HANDLER:org.apache.dolphinscheduler.api.controller.ProjectController.queryProjectListPaging, ARGS:{searchVal=, pageNo=1, pageSize=10}
   [ERROR] 2022-12-20 15:34:35.496 +0800 org.apache.dolphinscheduler.api.exceptions.ApiExceptionHandler:[53] - login user query project list paging error
   org.springframework.jdbc.BadSqlGrammarException: 
   ### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: column "u.user_name" must appear in the GROUP BY clause or be used in an aggregate function
     位置：116
   ### The error may exist in file [/Users/calvin/MyCloud/zhilian/workspace/dolphinscheduler/dolphinscheduler-dao/target/classes/org/apache/dolphinscheduler/dao/mapper/ProjectMapper.xml]
   ### The error may involve defaultParameterMap
   ### The error occurred while setting parameters
   ### SQL: SELECT COUNT(*) FROM (SELECT p.id, p.name, p.code, p.description, p.user_id, p.flag, p.create_time, p.update_time, u.user_name AS user_name, count(DISTINCT def.id) AS def_count, count(DISTINCT inst.id) AS inst_running_count FROM t_ds_project p LEFT JOIN t_ds_user u ON u.id = p.user_id LEFT JOIN t_ds_process_definition def ON def.project_code = p.code LEFT JOIN t_ds_process_instance inst ON inst.process_definition_code = def.code AND inst.process_definition_version = def.version AND inst.state = 1 WHERE 1 = 1 AND p.id IN (?, ?) GROUP BY p.id ORDER BY p.id DESC) TOTAL
   ### Cause: org.postgresql.util.PSQLException: ERROR: column "u.user_name" must appear in the GROUP BY clause or be used in an aggregate function
     位置：116
   ; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "u.user_name" must appear in the GROUP BY clause or be used in an aggregate function
     位置：116
       at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
       at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
       at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
       at com.sun.proxy.$Proxy142.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.$Proxy182.queryProjectListPaging(Unknown Source)
       at org.apache.dolphinscheduler.api.service.impl.ProjectServiceImpl.queryProjectListPaging(ProjectServiceImpl.java:351)
       at org.apache.dolphinscheduler.api.service.impl.ProjectServiceImpl$$FastClassBySpringCGLIB$$345e6c02.invoke(<generated>)
       at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
       at org.springframework.aop.framework.CglibAopProxy.invokeMethod(CglibAopProxy.java:386)
       at org.springframework.aop.framework.CglibAopProxy.access$000(CglibAopProxy.java:85)
       at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:704)
       at org.apache.dolphinscheduler.api.service.impl.ProjectServiceImpl$$EnhancerBySpringCGLIB$$49769ea1.queryProjectListPaging(<generated>)
       at org.apache.dolphinscheduler.api.controller.ProjectController.queryProjectListPaging(ProjectController.java:173)
       at org.apache.dolphinscheduler.api.controller.ProjectController$$FastClassBySpringCGLIB$$1ee40f52.invoke(<generated>)
       at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
       at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
       at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
       at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89)
       at org.apache.dolphinscheduler.api.aspect.AccessLogAspect.doAround(AccessLogAspect.java:105)
       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.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634)
       at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624)
       at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72)
       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
       at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
       at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
       at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
       at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
       at org.apache.dolphinscheduler.api.controller.ProjectController$$EnhancerBySpringCGLIB$$f8b726cb.queryProjectListPaging(<generated>)
       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:1070)
       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:687)
       at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
       at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)
       at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1656)
       at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:91)
       at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
       at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
       at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
       at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
       at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
       at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
       at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
       at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
       at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
       at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
       at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
       at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96)
       at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
       at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
       at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
       at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
       at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
       at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
       at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
       at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:552)
       at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
       at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600)
       at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
       at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
       at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)
       at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
       at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)
       at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
       at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:505)
       at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)
       at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
       at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)
       at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
       at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:772)
       at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
       at org.eclipse.jetty.server.Server.handle(Server.java:516)
       at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)
       at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)
       at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)
       at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
       at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
       at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
       at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
       at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)
       at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)
       at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)
       at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
       at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)
       at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
       at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
       at java.lang.Thread.run(Thread.java:748)
   Caused by: org.postgresql.util.PSQLException: ERROR: column "u.user_name" must appear in the GROUP BY clause or be used in an aggregate function
     位置：116
       at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
       at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
       at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
       at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
       at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
       at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181)
       at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:170)
       at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
       at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
       at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
       at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
       at sun.reflect.GeneratedMethodAccessor141.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
       at java.lang.reflect.Method.invoke(Method.java:498)
       at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
       at com.sun.proxy.$Proxy256.query(Unknown Source)
       at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
       at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
       at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
       at com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor.willDoQuery(PaginationInnerInterceptor.java:135)
       at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:75)
       at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
       at com.sun.proxy.$Proxy255.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.GeneratedMethodAccessor183.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)
       ... 107 common frames omitted
   
   ```
   
   ### What you expected to happen
   
   This is a very serious bug.I expect that it can be fixed ASAP.
   
   ### How to reproduce
   
   1. Download the newest code from the project repository.
   2. Launch the system and visit the page of project list.
   3. Check out the log file and you will see those exception messages.
   
   ### Anything else
   
   _No response_
   
   ### Version
   
   dev
   
   ### Are you willing to submit PR?
   
   - [X] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org