You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "liming0 (via GitHub)" <gi...@apache.org> on 2023/04/27 02:15:15 UTC
[GitHub] [shardingsphere] liming0 opened a new issue, #25342: Select * Cannot find corresponding column
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #25342: Select * Cannot find corresponding column
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25342:
URL: https://github.com/apache/shardingsphere/issues/25342#issuecomment-1524486291
@liming0 Thank you for your feedback. We will try to reproduce this exception.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #25342: Select * Cannot find corresponding column
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu closed issue #25342: Select * Cannot find corresponding column
URL: https://github.com/apache/shardingsphere/issues/25342
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #25342: Select * Cannot find corresponding column
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25342:
URL: https://github.com/apache/shardingsphere/issues/25342#issuecomment-1670450457
Since this feature is not supported now, I will close it - https://shardingsphere.apache.org/document/current/en/features/encrypt/appendix/, you can try modify shorthand to some columns.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #25342: Select * Cannot find corresponding column
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25342:
URL: https://github.com/apache/shardingsphere/issues/25342#issuecomment-1669409499
Hi @liming0, ShardingSphere can not support shorthand expand for subquery table now. Can you modify shorthand to some columns?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org