ylyly opened a new issue #3155: Sharding jdbc parse sql statement which have a table name alias as T whill case error? URL: https://github.com/apache/incubator-shardingsphere/issues/3155 ## Bug Report ShardingSphere version is 4.0.0-RC2. my project maven pom have import sharding-jdbc-core and sharding-jdbc-spring-boot-starter. This is my shard config ``` spring.shardingsphere.enabled=true spring.shardingsphere.datasource.names=mscheck-ha,mscheck-sd spring.shardingsphere.datasource.mscheck-ha.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.mscheck-ha.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.mscheck-ha.jdbc-url=jdbc:mysql://localhost:3306/mysqldb1?useUnicode=true&serverTimezone=GMT%2B8&characterEncoding=utf-8&nullCatalogMeansCurrent=false spring.shardingsphere.datasource.mscheck-ha.username=root spring.shardingsphere.datasource.mscheck-ha.password=123456 spring.shardingsphere.datasource.mscheck-ha.schema=mysqldb1 spring.shardingsphere.datasource.mscheck-sd.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.mscheck-sd.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.mscheck-sd.jdbc-url=jdbc:mysql://localhost:13306/mysqldb2?useUnicode=true&serverTimezone=GMT%2B8&characterEncoding=utf-8&nullCatalogMeansCurrent=false spring.shardingsphere.datasource.mscheck-sd.username=root spring.shardingsphere.datasource.mscheck-sd.password=123456 spring.shardingsphere.datasource.mscheck-sd.schema=mysqldb2 spring.shardingsphere.sharding.default-data-source-name=mscheck-ha` # 默认数据策略hint，默认分片算法使用自定义的ProvinceHintShardingAlgorithm spring.shardingsphere.sharding.default-database-strategy.hint.algorithm-class-name=com.xxxx.yyy.config.shard.ProvinceHintShardingAlgorithm spring.shardingsphere.props.sql.show=true ``` My Sharding Algorithm Class: ``` public class ProvinceHintShardingAlgorithm implements HintShardingAlgorithm<String> { private static final Logger LOGGER = LoggerFactory.getLogger(ProvinceHintShardingAlgorithm.class); @Override public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<String> shardingValue) { List<String> result = new ArrayList<>(); for (String value : shardingValue.getValues()) { if(availableTargetNames.contains(value)){ result.add(value); return result; } } //return null; if(result.isEmpty()){ result.add("mscheck-ha"); } return result; } } ``` Where shardingjdbc execute sql like this `select T.* from ACT_RU_TASK T where T.PROC_INST_ID_ = ?` shardingjdbc will throw a error: ``` line 1:7 mismatched input 'T' expecting {'!', '~', '+', '-', '*', '.', '(', '{', '?', '@', TRUNCATE, POSITION, VIEW, DISTINCT, CASE, CAST, TRIM, SUBSTRING, IF, NOT, NULL, TRUE, FALSE, EXISTS, ALL, ANY, OFFSET, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, BOOLEAN, CHAR, INTERVAL, DATE, TIME, TIMESTAMP, LOCALTIME, LOCALTIMESTAMP, YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND, MAX, MIN, SUM, COUNT, AVG, CURRENT, ENABLE, DISABLE, INSTANCE, DO, DEFINER, CASCADED, LOCAL, CLOSE, OPEN, NEXT, NAME, TABLES, TABLESPACE, COLUMNS, FIELDS, INDEXES, STATUS, REPLACE, MODIFY, DISTINCTROW, VALUE, DUPLICATE, FIRST, LAST, AFTER, OJ, ACCOUNT, USER, ROLE, START, TRANSACTION, ROW, WITHOUT, BINARY, ESCAPE, SUBPARTITION, STORAGE, SUPER, SUBSTR, TEMPORARY, THAN, UNBOUNDED, UPGRADE, VALIDATION, ROLLUP, SOUNDS, UNKNOWN, OFF, ALWAYS, COMMITTED, LEVEL, NO, PASSWORD, PRIVILEGES, ACTION, ALGORITHM, AUTOCOMMIT, BTREE, CHAIN, CHARSET, CHECKSUM, CIPHER, CLIENT, COALESCE, COMMENT, COMPACT, COMPRESSED, COMPRESSION, CONNECTION, CONSISTENT, CONVERT, DATA, DISCARD, DISK, ENCRYPTION, END, ENGINE, EVENT, EXCHANGE, EXECUTE, EXTRACT, FILE, FIXED, FOLLOWING, GLOBAL, HASH, IMPORT_, LESS, MATCH, MEMORY, NONE, NOW, PARSER, PARTIAL, PARTITIONING, PERSIST, PRECEDING, PROCESS, PROXY, QUICK, REBUILD, REDUNDANT, RELOAD, REMOVE, REORGANIZE, REPAIR, REVERSE, SESSION, SHUTDOWN, SIMPLE, SLAVE, VISIBLE, INVISIBLE, ENFORCED, AGAINST, LANGUAGE, MODE, QUERY, EXTENDED, EXPANSION, VARIANCE, MAX_ROWS, MIN_ROWS, HIGH_PRIORITY, SQL_BIG_RESULT, SQL_BUFFER_RESULT, SQL_CACHE, SQL_CALC_FOUND_ROWS, SQL_NO_CACHE, SQL_SMALL_RESULT, STATS_AUTO_RECALC, STATS_PERSISTENT, STATS_SAMPLE_PAGES, ROW_FORMAT, STRAIGHT_JOIN, WEIGHT_STRING, COLUMN_FORMAT, INSERT_METHOD, KEY_BLOCK_SIZE, PACK_KEYS, PERSIST_ONLY, BIT_AND, BIT_OR, BIT_XOR, GROUP_CONCAT, JSON_ARRAYAGG, JSON_OBJECTAGG, STD, STDDEV, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, AUTO_INCREMENT, AVG_ROW_LENGTH, DELAY_KEY_WRITE, CURRENT_TIMESTAMP, ROTATE, MASTER, BINLOG, ERROR, SCHEDULE, COMPLETION, EVERY, HOST, SOCKET, PORT, SERVER, WRAPPER, OPTIONS, OWNER, RETURNS, CONTAINS, SECURITY, INVOKER, TEMPTABLE, MERGE, UNDEFINED, DATAFILE, FILE_BLOCK_SIZE, EXTENT_SIZE, INITIAL_SIZE, AUTOEXTEND_SIZE, MAX_SIZE, NODEGROUP, WAIT, LOGFILE, UNDOFILE, UNDO_BUFFER_SIZE, REDO_BUFFER_SIZE, HANDLER, PREV, ORGANIZATION, DEFINITION, DESCRIPTION, REFERENCE, FOLLOWS, PRECEDES, IMPORT, CONCURRENT, XML, DUMPFILE, SHARE, IDENTIFIER_, STRING_, NUMBER_, HEX_DIGIT_, BIT_NUM_} 2019-09-28 15:55:52 | ERROR | http-nio-8090-exec-2 | org.flowable.common.engine.impl.interceptor.CommandContext | Error while closing command context org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: java.lang.IllegalStateException ### The error may exist in org/flowable/task/service/db/mapping/entity/Task.xml ### The error may involve org.flowable.task.service.impl.persistence.entity.TaskEntityImpl.selectTasksByProcessInstanceId-Inline ### The error occurred while setting parameters ### SQL: select T.* from ACT_RU_TASK T where T.PROC_INST_ID_ = ? ### Cause: java.lang.IllegalStateException at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) at org.flowable.common.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:202) at org.flowable.common.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:196) at org.flowable.common.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:157) at org.flowable.common.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:162) at org.flowable.common.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:142) at org.flowable.common.engine.impl.db.AbstractDataManager.getList(AbstractDataManager.java:149) at org.flowable.task.service.impl.persistence.entity.data.impl.MybatisTaskDataManager.findTasksByProcessInstanceId(MybatisTaskDataManager.java:79) at org.flowable.task.service.impl.persistence.entity.TaskEntityManagerImpl.findTasksByProcessInstanceId(TaskEntityManagerImpl.java:166) at org.flowable.task.service.impl.TaskServiceImpl.findTasksByProcessInstanceId(TaskServiceImpl.java:48) at org.flowable.engine.impl.util.TaskHelper.deleteTasksByProcessInstanceId(TaskHelper.java:411) at org.flowable.engine.impl.persistence.entity.ExecutionEntityManagerImpl.deleteProcessInstanceCascade(ExecutionEntityManagerImpl.java:482) at org.flowable.engine.impl.persistence.entity.ExecutionEntityManagerImpl.deleteProcessInstance(ExecutionEntityManagerImpl.java:423) at org.flowable.engine.impl.cmd.DeleteProcessInstanceCmd.execute(DeleteProcessInstanceCmd.java:59) at org.flowable.engine.impl.cmd.DeleteProcessInstanceCmd.execute(DeleteProcessInstanceCmd.java:30) at org.flowable.engine.impl.interceptor.CommandInvoker$1.run(CommandInvoker.java:51) at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperation(CommandInvoker.java:93) at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperations(CommandInvoker.java:72) at org.flowable.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:56) at org.flowable.engine.impl.interceptor.BpmnOverrideContextInterceptor.execute(BpmnOverrideContextInterceptor.java:25) at org.flowable.common.engine.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:53) at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:72) at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:51) at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56) at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51) at org.flowable.engine.impl.RuntimeServiceImpl.deleteProcessInstance(RuntimeServiceImpl.java:184) at com.cmiot.mscheck.workorder.service.impl.FlowServiceImpl.deleteFlowByInstanceId(FlowServiceImpl.java:401) at com.cmiot.mscheck.workorder.service.impl.FlowServiceImpl.deleteFlowByBusinessKey(FlowServiceImpl.java:395) at com.cmiot.mscheck.workorder.service.impl.FlowServiceImpl.deleteFlow(FlowServiceImpl.java:381) at com.cmiot.mscheck.workorder.service.impl.FlowServiceImpl$$FastClassBySpringCGLIB$$743b1de6.invoke(<generated>) at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) at com.cmiot.mscheck.workorder.service.impl.FlowServiceImpl$$EnhancerBySpringCGLIB$$3b141cc6.deleteFlow(<generated>) at com.cmiot.mscheck.workorder.rest.controller.ProcessRestImpl.deleteFlow(ProcessRestImpl.java:126) 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:104) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1039) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:908) at javax.servlet.http.HttpServlet.service(HttpServlet.java:660) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882) at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:853) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1587) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) Caused by: java.lang.IllegalStateException: null at com.google.common.base.Preconditions.checkState(Preconditions.java:492) at org.apache.shardingsphere.core.parse.core.extractor.util.ExtractorUtils.getFirstChildNode(ExtractorUtils.java:48) at org.apache.shardingsphere.core.parse.core.extractor.impl.dml.select.SelectItemsExtractor.extract(SelectItemsExtractor.java:54) at org.apache.shardingsphere.core.parse.core.extractor.SQLSegmentsExtractorEngine.extract(SQLSegmentsExtractorEngine.java:47) at org.apache.shardingsphere.core.parse.core.SQLParseKernel.parse(SQLParseKernel.java:62) at org.apache.shardingsphere.core.route.router.sharding.DatabaseHintSQLRouter.parse(DatabaseHintSQLRouter.java:50) at org.apache.shardingsphere.core.route.PreparedStatementRoutingEngine.route(PreparedStatementRoutingEngine.java:64) at org.apache.shardingsphere.core.PreparedQueryShardingEngine.route(PreparedQueryShardingEngine.java:60) at org.apache.shardingsphere.core.BaseShardingEngine.executeRoute(BaseShardingEngine.java:86) at org.apache.shardingsphere.core.BaseShardingEngine.shard(BaseShardingEngine.java:70) at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.shard(ShardingPreparedStatement.java:224) at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:170) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ... 94 common frames omitted ``` If i replace the sql as `select b.* from ACT_RU_TASK b where b.PROC_INST_ID_ = ?` to execute everthing is ok. So i think sharding jdbc parse sql statement which have a table name alias as T whill case error?
---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services
