liming0 opened a new issue, #25342: URL: https://github.com/apache/shardingsphere/issues/25342
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Which version of ShardingSphere did you use? 5.3.2 ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-JDBC ### Expected behavior ``` Logic SQL: select * from (select person.*,ROW_NUMBER () OVER (PARTITION BY person_id ORDER BY person_id DESC ) AS rw from t_person person where person_name_plain is not null) p where p.person_name_plain is not null and p.rw=1 Actual SQL: slave0 ::: select * from (select person.*,ROW_NUMBER () OVER (PARTITION BY person_id ORDER BY person_id DESC ) AS rw from t_person person where person_name_query is not null) p where p.person_name_query is not null and p.rw=1 ``` table dll: ```sql CREATE TABLE t_person ( person_id BIGINT NOT NULL, gender SMALLINT NOT NULL, person_name CHARACTER VARYING(200) NOT NULL, person_name_plain CHARACTER VARYING(100), person_name_query CHARACTER VARYING(100), create_time TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL, update_time TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL, PRIMARY KEY (person_id) ); ``` ### Actual behavior ``` Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Error attempting to get column 'person_id' from result set. Cause: java.sql.SQLFeatureNotSupportedException: Can not get index from column label `person_id`. ; Can not get index from column label `person_id`.; nested exception is java.sql.SQLFeatureNotSupportedException: Can not get index from column label `person_id`.] with root cause java.sql.SQLFeatureNotSupportedException: Can not get index from column label `person_id`. at org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSet.lambda$getIndexFromColumnLabelAndIndexMap$0(ShardingSphereResultSet.java:394) at org.apache.shardingsphere.infra.util.exception.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41) at org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSet.getIndexFromColumnLabelAndIndexMap(ShardingSphereResultSet.java:394) at org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSet.getLong(ShardingSphereResultSet.java:136) at com.zaxxer.hikari.pool.HikariProxyResultSet.getLong(HikariProxyResultSet.java) 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.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:69) at com.sun.proxy.$Proxy131.getLong(Unknown Source) at org.apache.ibatis.type.LongTypeHandler.getNullableResult(LongTypeHandler.java:37) at org.apache.ibatis.type.LongTypeHandler.getNullableResult(LongTypeHandler.java:26) at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:85) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyAutomaticMappings(DefaultResultSetHandler.java:560) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:402) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:354) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:328) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:301) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:194) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) 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.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) at com.sun.proxy.$Proxy129.query(Unknown Source) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:69) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165) at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:64) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) at com.sun.proxy.$Proxy128.query(Unknown Source) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) 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:426) at com.sun.proxy.$Proxy91.selectList(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:173) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78) 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.$Proxy96.asterisk(Unknown Source) at com.dawn.sharding.demo.service.impl.PersonServiceImpl.asterisk(PersonServiceImpl.java:24) at com.dawn.sharding.demo.service.impl.PersonServiceImpl$$FastClassBySpringCGLIB$$1.invoke(<generated>) at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687) at com.dawn.sharding.demo.service.impl.PersonServiceImpl$$EnhancerBySpringCGLIB$$1.asterisk(<generated>) at com.dawn.sharding.demo.controller.v1.ShardingBugTestController.shardingBugAsterisk(ShardingBugTestController.java:24) 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:626) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) 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:119) 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:119) 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:119) 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:202) at org.apache.catalina.core.StandardContextValve.__invoke(StandardContextValve.java:97) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:41002) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143) 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:374) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1707) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745) ``` ### Reason analyze (If you can) This error occurs when SQL contains' select * 'and subqueries ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. config: ```yaml props: sql-show: true #sql-simple: true rules: # 加密策略 - !ENCRYPT encryptors: enc-aes: type: AES props: aes-key-value: 123456 enc-md5: type: MD5 enc-like: type: CHAR_DIGEST_LIKE tables: t_person: columns: person_name_plain: encryptorName: enc-aes cipherColumn: person_name assistedQueryColumn: person_name_query assistedQueryEncryptorName: enc-md5 #likeQueryColumn: person_name_like #likeQueryEncryptorName: enc-like #plainColumn: person_name_plain - !READWRITE_SPLITTING dataSources: # 读写分离逻辑数据源名称 zqsb-apply: loadBalancerName: roundRobin staticStrategy: # 读写分离类型，比如：Static，Dynamic # 写库数据源名称 writeDataSourceName: master # 读库数据源名称 readDataSourceNames: - slave0 # 负载均衡算法配置 loadBalancers: roundRobin: # 负载均衡算法名称 type: ROUND_ROBIN props: default: 0 #数据源配置 dataSources: #names: master,slave0 master: dataSourceClassName: com.alibaba.druid.pool.DruidDataSource url: jdbc:postgresql://地址:端口/databaseName?currentSchema=schemaName driverClassName: org.postgresql.Driver username: xxx password: xxx slave0: dataSourceClassName: com.alibaba.druid.pool.DruidDataSource url: jdbc:postgresql://地址:端口/databaseName?currentSchema=schemaName driverClassName: org.postgresql.Driver username: xxx password: xxx ``` ```sql --DROP TABLE t_person; --DROP TABLE t_person_contacts; CREATE TABLE t_person ( person_id int8 NOT NULL, gender int2 NOT NULL, person_name VARCHAR(200), person_name_plain VARCHAR(200), person_name_query VARCHAR(200), create_time TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL, update_time TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL, PRIMARY KEY (person_id) ); COMMENT ON TABLE t_person IS '人员表'; COMMENT ON COLUMN t_person.person_id IS '主键'; COMMENT ON COLUMN t_person.person_name IS '名字密文'; COMMENT ON COLUMN t_person.person_name_plain IS '名字明文'; COMMENT ON COLUMN t_person.person_name_query IS '名字查询'; COMMENT ON COLUMN t_person.gender IS '性别'; COMMENT ON COLUMN t_person.create_time IS '创建时间'; COMMENT ON COLUMN t_person.update_time IS '修改时间'; CREATE TABLE t_person_contacts ( person_contacts_id int8 NOT NULL, person_id int8 NOT NULL, gender int2 NOT NULL, contact_type INT2 NOT NULL, contact_text VARCHAR(100) NOT NULL, create_time TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL, update_time TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL, PRIMARY KEY (person_contacts_id) ); COMMENT ON TABLE t_person_contacts IS '人员联系方式'; COMMENT ON COLUMN t_person_contacts.person_contacts_id IS '人员联系方式主键'; COMMENT ON COLUMN t_person_contacts.person_id IS '人员表主键'; COMMENT ON COLUMN t_person_contacts.contact_type IS '联络方式类型'; COMMENT ON COLUMN t_person_contacts.create_time IS '创建时间'; COMMENT ON COLUMN t_person_contacts.update_time IS '修改时间'; COMMENT ON COLUMN t_person_contacts.contact_text IS '联络方式内容'; COMMENT ON COLUMN t_person_contacts.gender IS '性别'; INSERT INTO t_person (person_id, gender, person_name, person_name_plain, person_name_query, create_time, update_time) VALUES (1646795184307433473, 3, '/ozfXEOw8Ki+29HpVGRHCw==', null, '8de0dec104547486dda995545b8314fb', '2023-04-14 16:39:02', '2023-04-14 16:39:02'); INSERT INTO t_person (person_id, gender, person_name, person_name_plain, person_name_query, create_time, update_time) VALUES (1646797449030266881, 3, 'bMk/3EVtQtHRmAi+kDiYXQ==', null, '7f52f047968f23939230aec87f10a246', '2023-04-14 16:48:02', '2023-04-14 16:48:02'); INSERT INTO t_person (person_id, gender, person_name, person_name_plain, person_name_query, create_time, update_time) VALUES (1646797494655905793, 1, 'ufRYQNYzUMG7+4Ssju6pZw==', null, 'ed19805c3f6918c5d84c1c0e0d1811a2', '2023-04-14 16:48:13', '2023-04-14 16:48:13'); INSERT INTO t_person (person_id, gender, person_name, person_name_plain, person_name_query, create_time, update_time) VALUES (1646797516592115713, 1, 'JdiZoMCs9o6N00gyBO9lqw==', null, 'a98715c17ea0cf0e59d3d8a0fdc82e87', '2023-04-14 16:48:18', '2023-04-14 16:48:18'); INSERT INTO t_person (person_id, gender, person_name, person_name_plain, person_name_query, create_time, update_time) VALUES (1646797539849531394, 1, 'pIneVco457jzRhKL3Ij7BA==', null, 'f9e742471d1dfa649ff6e22d01439e1d', '2023-04-14 16:48:24', '2023-04-14 16:48:24'); INSERT INTO t_person_contacts (person_contacts_id, person_id, gender, contact_type, contact_text, create_time, update_time) VALUES (1646802458107105282, 1646797516592115713, 1, 1, '19541831234', '2023-04-14 17:07:56', '2023-04-14 17:07:56'); INSERT INTO t_person_contacts (person_contacts_id, person_id, gender, contact_type, contact_text, create_time, update_time) VALUES (1646802530593067010, 1646797539849531394, 1, 1, '18860174149', '2023-04-14 17:08:14', '2023-04-14 17:08:14'); ``` ### Example codes for reproduce this issue (such as a github link). [https://gitee.com/liming3/sharding-demo.git](https://github.com/apache/shardingsphere/issues/url) Select demo-5.3.2 branch Including SQL statements, testing methods: /api/v1/bug/shardingBug-asterisk -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
