apache
Expected one result (or null) to be returned by selectOne(), but found: 2
Question
shardingsphere 5.3.0 mysql 8.0
SELECT IFNULL(sum(o.order_amount - o.refund_amount), 0.00) AS todayRealTimeTurnover, IFNULL(SUM(CASE WHEN ori.is_first_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFirstOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_follow_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFollowOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_fission = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFissionOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_repurchase = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayRepurchaseOrderTurnover FROM t_orders AS o INNER JOIN t_order_rel_info AS ori ON ori.orders_id = o.id AND o.tenant_id = -1 AND ori.tenant_id = -1 WHERE o.create_time >= date_format(now(), '%Y-%m-%d 00:00:00') AND o.create_time <= date_format(now(), '%Y-%m-%d 23:59:59') AND o.delete_state = 0 AND o.order_state NOT IN (6, 8, 10, 11) AND ori.no_performance_type IN (SELECT dict_value FROM sys_dict_data WHERE dict_type = 'statistics_no_performance_type' AND sys_dict_data.tenant_id = -1) AND NOT EXISTS (SELECT 1 FROM t_order_hang_up WHERE order_id = o.id AND t_order_hang_up.tenant_id = -1) 2023-02-08 17:23:02 [XNIO-1 task-4] ERROR c.r.f.w.e.GlobalExceptionHandler
- nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
at com.sun.proxy.$Proxy165.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:89)
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.$Proxy597.selectTodayTurnover(Unknown Source)
at com.ruoyi.shop.service.impl.SalesDataBigBoardServiceImpl.selectTodayAndYesterdayData(SalesDataBigBoardServiceImpl.java:123)
at com.ruoyi.web.controller.dashboard.SalesDataBigBoardController.list(SalesDataBigBoardController.java:44)
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:190)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
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:497)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:584)
at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
at io.undertow.websockets.jsr.JsrWebSocketFilter.doFilter(JsrWebSocketFilter.java:173)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at de.codecentric.boot.admin.server.ui.web.servlet.HomepageForwardingFilter.doFilter(HomepageForwardingFilter.java:73)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:113)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:113)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at com.ruoyi.common.filter.RepeatableFilter.doFilter(RepeatableFilter.java:35)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at com.dianping.cat.servlet.CatFilter.logTransaction(CatFilter.java:255)
at com.dianping.cat.servlet.CatFilter.doFilter(CatFilter.java:93)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at com.ruoyi.framework.tenant.context.TenantSecurityWebFilter.doFilterInternal(TenantSecurityWebFilter.java:103)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:126)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:90)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:118)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:158)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at com.ruoyi.framework.security.filter.JwtAuthenticationTokenFilter.doFilterInternal(JwtAuthenticationTokenFilter.java:46)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:92)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:92)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:77)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at com.ruoyi.framework.tenant.context.TenantContextWebFilter.doFilterInternal(TenantContextWebFilter.java:53)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:97)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)
at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)
at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:111)
at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:269)
at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:78)
at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:133)
at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:130)
at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:249)
at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:78)
at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:99)
at io.undertow.server.Connectors.executeRootHandler(Connectors.java:390)
at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:836)
at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:2019)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1558)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1449)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:80)
at sun.reflect.GeneratedMethodAccessor369.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)
... 140 common frames omitted
1.sql works fine when the project does not integrate a shardingsphere. 2.Two data sources are configured to route the database through the tenant id 3.I suspect that I checked the data in two libraries and summarized them into list, so list.size()>1
@zhoubin7您好，请将您的问题中文翻译成英文。
@zhaojinchao95 Hello, I have updated my question
请启用
sql-show（参考道具）并在日志中提供逻辑 SQL 和实际 SQL。
-- Logic SQL: SELECT IFNULL(sum(o.order_amount - o.refund_amount), 0.00) AS todayRealTimeTurnover, IFNULL(SUM(CASE WHEN ori.is_first_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFirstOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_follow_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFollowOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_fission = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFissionOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_repurchase = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayRepurchaseOrderTurnover FROM t_orders AS o INNER JOIN t_order_rel_info AS ori ON ori.orders_id = o.id AND o.tenant_id = -1 AND ori.tenant_id = -1 WHERE o.create_time >= date_format(now(), '%Y-%m-%d 00:00:00') AND o.create_time <= date_format(now(), '%Y-%m-%d 23:59:59') AND o.delete_state = 0 AND o.order_state NOT IN (6, 8, 10, 11) AND ori.no_performance_type IN (SELECT dict_value FROM sys_dict_data WHERE dict_type = 'statistics_no_performance_type' AND sys_dict_data.tenant_id = -1) AND NOT EXISTS (SELECT 1 FROM t_order_hang_up WHERE order_id = o.id AND t_order_hang_up.tenant_id = -1)
-- Actual SQL: ds_jnmt ::: SELECT IFNULL(sum(o.order_amount - o.refund_amount), 0.00) AS todayRealTimeTurnover, IFNULL(SUM(CASE WHEN ori.is_first_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFirstOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_follow_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFollowOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_fission = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFissionOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_repurchase = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayRepurchaseOrderTurnover FROM t_orders AS o INNER JOIN t_order_rel_info AS ori ON ori.orders_id = o.id AND o.tenant_id = -1 AND ori.tenant_id = -1 WHERE o.create_time >= date_format(now(), '%Y-%m-%d 00:00:00') AND o.create_time <= date_format(now(), '%Y-%m-%d 23:59:59') AND o.delete_state = 0 AND o.order_state NOT IN (6, 8, 10, 11) AND ori.no_performance_type IN (SELECT dict_value FROM sys_dict_data WHERE dict_type = 'statistics_no_performance_type' AND sys_dict_data.tenant_id = -1) AND NOT EXISTS (SELECT 1 FROM t_order_hang_up WHERE order_id = o.id AND t_order_hang_up.tenant_id = -1)
-- Actual SQL: ds_jnmt_ymlj ::: SELECT IFNULL(sum(o.order_amount - o.refund_amount), 0.00) AS todayRealTimeTurnover, IFNULL(SUM(CASE WHEN ori.is_first_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFirstOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_follow_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFollowOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_fission = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFissionOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_repurchase = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayRepurchaseOrderTurnover FROM t_orders AS o INNER JOIN t_order_rel_info AS ori ON ori.orders_id = o.id AND o.tenant_id = -1 AND ori.tenant_id = -1 WHERE o.create_time >= date_format(now(), '%Y-%m-%d 00:00:00') AND o.create_time <= date_format(now(), '%Y-%m-%d 23:59:59') AND o.delete_state = 0 AND o.order_state NOT IN (6, 8, 10, 11) AND ori.no_performance_type IN (SELECT dict_value FROM sys_dict_data WHERE dict_type = 'statistics_no_performance_type' AND sys_dict_data.tenant_id = -1) AND NOT EXISTS (SELECT 1 FROM t_order_hang_up WHERE order_id = o.id AND t_order_hang_up.tenant_id = -1)
@RaigorJiang hi,I removed the subquery of the in condition in the sql, and it worked normally
@zhoubin7 This is really great news, thanks for your feedback.
Perhaps the subquery in the sharding scenario needs to enable federated query, you can try to configure sql-federation-type.
@zhoubin7 这真是个好消息，感谢您的反馈。
可能分片场景下的子查询需要开启联合查询，可以尝试配置 sql-federation-type。
The error occurred while setting parameters
SQL: SELECT u.user_id, u.dept_id, u.user_name, u.nick_name, u.email, u.avatar, u.phonenumber, u.password, u.sex, u.status, u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark, u.user_type, u.last_password_modify_time, u.tenant_id, d.dept_id, d.parent_id, d.dept_name, d.order_num, d.leader, d.status AS dept_status, d.dept_type, d.ancestors, r.role_id, r.role_name, r.role_key, r.role_sort, r.data_scope, r.status AS role_status, t.name AS tenantName, r.status AS role_status, u.project_category_id FROM sys_user u LEFT JOIN sys_dept d ON u.dept_id = d.dept_id AND d.tenant_id = -1 LEFT JOIN sys_user_role ur ON u.user_id = ur.user_id AND ur.tenant_id = -1 LEFT JOIN sys_role r ON r.role_id = ur.role_id AND r.tenant_id = -1 LEFT JOIN sys_tenant t ON u.tenant_id = t.id WHERE u.user_name = ? AND u.tenant_id = -1
Cause: java.sql.SQLException: exception while executing query: java.lang.Long cannot be cast to java.lang.Integer
; uncategorized SQLException; SQL state [null]; error code [0]; exception while executing query: java.lang.Long cannot be cast to java.lang.Integer; nested exception is java.sql.SQLException: exception while executing query: java.lang.Long cannot be cast to java.lang.Integer at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) at com.sun.proxy.$Proxy165.selectOne(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:89) 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.$Proxy170.selectUserByUserName(Unknown Source) Caused by: java.sql.SQLException: exception while executing query: java.lang.Long cannot be cast to java.lang.Integer at org.apache.calcite.avatica.Helper.createException(Helper.java:56) at org.apache.calcite.avatica.Helper.createException(Helper.java:41) at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:579) at org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:137) at org.apache.shardingsphere.sqlfederation.original.OriginalSQLFederationExecutor.executeQuery(OriginalSQLFederationExecutor.java:111) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeFederationQuery(ShardingSpherePreparedStatement.java:312) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:402) at com.p6spy.engine.wrapper.PreparedStatementWrapper.execute(PreparedStatementWrapper.java:362) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) Caused by: java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer at Baz$14.apply(Unknown Source) at Baz$14.apply(Unknown Source) at org.apache.calcite.linq4j.EnumerableDefaults.toLookup_(EnumerableDefaults.java:3604) at org.apache.calcite.linq4j.EnumerableDefaults.toLookup(EnumerableDefaults.java:3594) at org.apache.calcite.linq4j.EnumerableDefaults.toLookup(EnumerableDefaults.java:3570) at org.apache.calcite.linq4j.DefaultEnumerable.toLookup(DefaultEnumerable.java:748)
@RaigorJiang Unfortunately,New problems have been discovered
Cause: java.sql.SQLException: exception while executing query: java.lang.Long cannot be cast to java.lang.Integer
- This looks a lot like the phenomenon described in #25320. This actually involves SQL intrinsics such as
TIMESTAMP_TRUNCwhich are not supported by older versions of Calcite. - Fixed at milestone 5.4.1, reference #28279.
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "Published by a pub.dev verified publisher"){kind=small}
- No one has provided further unit tests, and the only reason this issue is still open is because of an unrelated calcite issue. I don't see the point in keeping this issue open.