liming0 opened a new issue, #25343: URL: https://github.com/apache/shardingsphere/issues/25343
## 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 The 'p.person_name' in the sub query of ‘Actual SQL’ should have an alias ### Actual behavior ``` Logic SQL: select tmp.person_id personId,tmp.person_name_plain personNamePlain,tmp.contact_text contactText from ( select p.person_id,p.person_name_plain,pc.contact_type,pc.contact_text from t_person p left join t_person_contacts pc on p.person_id = pc.person_id ) tmp where tmp.person_name_plain is not null Actual SQL: slave0 ::: select tmp.person_id personId,tmp.person_name_plain personNamePlain,tmp.contact_text contactText from ( select p.person_id,p.person_name, p.person_name_query,pc.contact_type,pc.contact_text from t_person p left join t_person_contacts pc on p.person_id = pc.person_id ) tmp where tmp.person_name_plain is not null ``` ERROR: ``` ### Error querying database. Cause: org.postgresql.util.PSQLException: 错误: 字段 tmp.person_name_plain 不存在 位置：31 ### The error may exist in file [E:\myWorkspaces\sharding-demo\target\classes\mapper\Person.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: select tmp.person_id personId,tmp.person_name_plain personNamePlain,tmp.contact_text contactText from ( select p.person_id,p.person_name_plain,pc.contact_type,pc.contact_text from t_person p left join t_person_contacts pc on p.person_id = pc.person_id ) tmp where tmp.person_name_plain is not null ### Cause: org.postgresql.util.PSQLException: 错误: 字段 tmp.person_name_plain 不存在 位置：31 ; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: 错误: 字段 tmp.person_name_plain 不存在 位置：31] with root cause org.postgresql.util.PSQLException: 错误: 字段 tmp.person_name_plain 不存在 位置：31 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143) at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132) at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:521) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:517) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:86) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:65) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:133) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:129) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:114) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:67) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:51) at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.doExecute(DriverJDBCExecutor.java:145) at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.execute(DriverJDBCExecutor.java:136) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.useDriverToExecute(ShardingSpherePreparedStatement.java:510) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:443) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.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.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) at com.sun.proxy.$Proxy130.execute(Unknown Source) 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.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.subquery(Unknown Source) at com.dawn.sharding.demo.service.impl.PersonServiceImpl.subquery(PersonServiceImpl.java:29) 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.subquery(<generated>) at com.dawn.sharding.demo.controller.v1.ShardingBugTestController.shardingBugSubquery(ShardingBugTestController.java:30) 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) I only know that this is related to the inclusion of cipherColumn in the subquery, I'm not sure if this is caused by the same reason as [#25342](https://github.com/apache/shardingsphere/issues/25342),So I created a new issue ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. ```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-subquery -- 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]
