woshiluoshao opened a new issue, #23482:
URL: https://github.com/apache/shardingsphere/issues/23482
### SQL: SELECT count(1) FROM T_TEMPLATE_CFG T JOIN
readwrite_ds.zhsq.t_dc_org_entity_info B ON T.org_code = B.org_code WHERE
T.IS_VALID = '1'
### Cause: java.sql.SQLSyntaxErrorException: ORA-00905: 缺失关键字
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException:
ORA-00905: 缺失关键字
at
org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
at
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
at
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
at com.sun.proxy.$Proxy160.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.$Proxy217.selectList2(Unknown Source)
at
cn.ffcs.cty.modules.templatemanage.service.impl.TemplateCfgServiceImpl.searchList(TemplateCfgServiceImpl.java:64)
at
cn.ffcs.cty.modules.templatemanage.service.impl.TemplateCfgServiceImpl$$FastClassBySpringCGLIB$$1.invoke(<generated>)
at
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:783)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
at
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
at
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:698)
at
cn.ffcs.cty.modules.templatemanage.service.impl.TemplateCfgServiceImpl$$EnhancerBySpringCGLIB$$1.searchList(<generated>)
at
org.apache.dubbo.common.bytecode.Wrapper46.invokeMethod(Wrapper46.java)
at
org.apache.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:47)
at
org.apache.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:84)
at
org.apache.dubbo.config.invoker.DelegateProviderMetaDataInvoker.invoke(DelegateProviderMetaDataInvoker.java:56)
at
org.apache.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:56)
at
cn.ffcs.shequ.log.utils.exception.FFCSDubboExceptionFilter.invoke(FFCSDubboExceptionFilter.java:23)
at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61)
at
org.apache.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77)
at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61)
at
org.apache.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:44)
at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61)
at
org.apache.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:52)
at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61)
at
org.apache.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:192)
at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61)
at
org.apache.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)
at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61)
at org.apache.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:41)
at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61)
at
org.apache.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:129)
at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61)
at
org.apache.dubbo.rpc.proxy.InvokerInvocationHandler.invoke(InvokerInvocationHandler.java:96)
at org.apache.dubbo.common.bytecode.proxy39.searchList(proxy39.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
com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:302)
at
com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:198)
at
org.apache.dubbo.rpc.protocol.hessian.HessianProtocol$HessianHandler.handle(HessianProtocol.java:206)
at
org.apache.dubbo.remoting.http.servlet.DispatcherServlet.service(DispatcherServlet.java:64)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
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)
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?
apache-shardingsphere-5.2.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
SQL execute success
### Actual behavior
SQL execute fail
### Reason analyze (If you can)
when simply oracle table search ,it success, but table A JOIN table B ，it
fail ; for example [select count(1) from T_TEMPLATE_CFG T join
readwrite_ds.zhsq.t_dc_org_entity_info B on T.org_code = B.org_code] this Sql
execute success; but [SELECT count(1) FROM T_TEMPLATE_CFG T JOIN
zhsq.t_dc_org_entity_info B ON T.org_code = B.org_code] it execute fail , throw
exception [### Cause: java.sql.SQLSyntaxErrorException: ORA-00905: 缺失关键字]
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
1.application.properties
#dbuser.xxx 修改,以下为mybatis-plus的配置方式,主要是value前面需要添加 readwrite_ds.
逻辑数据库名,非mybatis的配置方式如下:dbuser.gbp=readwrite_ds.gbp
mybatis-plus.configurationProperties.dbuser.workflow=readwrite_ds.workflow
mybatis-plus.configurationProperties.dbuser.gbp=readwrite_ds.gbp
mybatis-plus.configurationProperties.dbuser.zhsq=readwrite_ds.zhsq
#oracle
jdbc.datasource.url=jdbc:oracle:thin:@ip:port:xxx
jdbc.datasource.username=username
jdbc.datasource.password=pwd
slave1.jdbc.datasource.url=jdbc\:wrap-jdbc\:filters\=default\:name\=dragoon\:jdbc\:oracle\:thin\:@(DESCRIPTION\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=ip)(PORT\=1521))(ADDRESS\=(PROTOCOL\=TCP)(HOST\=ip)(PORT\=1521))(LOAD_BALANCE\=on)(CONNECT_DATA\=(SERVER\=DEDICATED)(SERVICE_NAME\=betadb)))
slave1.jdbc.datasource.username=username
slave1.jdbc.datasource.password=pwd.123
#一些属性配置 see
org.apache.shardingsphere.infra.config.props.ConfigurationPropertyKey
spring.shardingsphere.enable=false
spring.shardingsphere.props.sql-show=true
spring.shardingsphere.props.check-table-metadata-enabled=false
spring.shardingsphere.props.max-connections-size-per-query=50
#spring.shardingsphere.props.sql-federation-type=NONE, ORIGINAL, ADVANCED
spring.shardingsphere.props.sql-federation-type=ORIGINAL
spring.shardingsphere.props.sql-federation-enabled=true
#模式配置,此处为单击模式,也支持集群,集群支持nacos或zookeeper为注册中心
spring.shardingsphere.mode.type=Standalone
spring.shardingsphere.mode.repository.type=JDBC
#逻辑数据库名称,默认值是logic_db
spring.shardingsphere.database.name=readwrite_ds
#真实数据源,多个使用逗号分隔,支持一写多读
spring.shardingsphere.datasource.names=write_ds,read_ds1
#数据源配置,写数据源
spring.shardingsphere.datasource.write_ds.url=${jdbc.datasource.url}
spring.shardingsphere.datasource.write_ds.username=${jdbc.datasource.username}
spring.shardingsphere.datasource.write_ds.password=${jdbc.datasource.password}
#数据源配置,第1个读数据源
spring.shardingsphere.datasource.read_ds1.url=${slave1.jdbc.datasource.url}
spring.shardingsphere.datasource.read_ds1.username=${slave1.jdbc.datasource.username}
spring.shardingsphere.datasource.read_ds1.password=${slave1.jdbc.datasource.password}
#其他的数据源配置参考durid,配置如下,请自行补充,建议这些配置放到yml文件
spring.shardingsphere.datasource.write_ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.write_ds.initial-size=${jdbc.datasource.initialSize}
spring.shardingsphere.datasource.write_ds.min-idle=${jdbc.datasource.minIdle}
spring.shardingsphere.datasource.write_ds.maxActive=${jdbc.datasource.maxActive}
spring.shardingsphere.datasource.write_ds.maxWait=${jdbc.datasource.maxWait}
spring.shardingsphere.datasource.write_ds.timeBetweenEvictionRunsMillis=${jdbc.datasource.timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.write_ds.minEvictableIdleTimeMillis=${jdbc.datasource.minEvictableIdleTimeMillis}
spring.shardingsphere.datasource.write_ds.validationQuery=${jdbc.datasource.validationQuery}
spring.shardingsphere.datasource.write_ds.testWhileIdle=${jdbc.datasource.testWhileIdle}
spring.shardingsphere.datasource.write_ds.testOnBorrow=${jdbc.datasource.testOnBorrow}
spring.shardingsphere.datasource.write_ds.testOnReturn=${jdbc.datasource.testOnReturn}
spring.shardingsphere.datasource.write_ds.poolPreparedStatements=${jdbc.datasource.poolPreparedStatements}
spring.shardingsphere.datasource.write_ds.maxPoolPreparedStatementPerConnectionSize=${jdbc.datasource.maxWait}
#filters开启貌似有问题
#spring.shardingsphere.datasource.write_ds.filters=${jdbc.datasource.filters}
spring.shardingsphere.datasource.write_ds.connectionProperties=config.decrypt=true
#slave
#spring.shardingsphere.datasource.read_ds1.dataSourceClassName=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.read_ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.read_ds1.initial-size=${jdbc.datasource.initialSize}
spring.shardingsphere.datasource.read_ds1.min-idle=${jdbc.datasource.minIdle}
spring.shardingsphere.datasource.read_ds1.maxActive=${jdbc.datasource.maxActive}
spring.shardingsphere.datasource.read_ds1.maxWait=${jdbc.datasource.maxWait}
spring.shardingsphere.datasource.read_ds1.timeBetweenEvictionRunsMillis=${jdbc.datasource.timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.read_ds1.minEvictableIdleTimeMillis=${jdbc.datasource.minEvictableIdleTimeMillis}
spring.shardingsphere.datasource.read_ds1.validationQuery=${jdbc.datasource.validationQuery}
spring.shardingsphere.datasource.read_ds1.testWhileIdle=${jdbc.datasource.testWhileIdle}
spring.shardingsphere.datasource.read_ds1.testOnBorrow=${jdbc.datasource.testOnBorrow}
spring.shardingsphere.datasource.read_ds1.testOnReturn=${jdbc.datasource.testOnReturn}
spring.shardingsphere.datasource.read_ds1.poolPreparedStatements=${jdbc.datasource.poolPreparedStatements}
spring.shardingsphere.datasource.read_ds1.maxPoolPreparedStatementPerConnectionSize=${jdbc.datasource.maxWait}
#spring.shardingsphere.datasource.read_ds1.filters=${jdbc.datasource.filters}
spring.shardingsphere.datasource.read_ds1.connectionProperties=config.decrypt=false
#读写分离配置
spring.shardingsphere.rules.readwrite-splitting.data-sources.logic_db.static-strategy.write-data-source-name=write_ds
spring.shardingsphere.rules.readwrite-splitting.data-sources.logic_db.static-strategy.read-data-source-names=read_ds1
#负载均衡算法名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.logic_db.load-balancer-name=round_robin
#负载均衡算法类型-轮询调度
spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin.type=ROUND_ROBIN
#h2数据库控制台输出,shardingphere默认使用h2数据库管理元数据
spring.h2.console.enabled=false
2.pom.xml
<!-- 引入shardingsphere,注意这个starter不能和数据源启动的starter混用 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
<exclusions>
<exclusion>
<artifactId>guava</artifactId>
<groupId>com.google.guava</groupId>
</exclusion>
<exclusion>
<artifactId>snakeyaml</artifactId>
<groupId>org.yaml</groupId>
</exclusion>
</exclusions>
</dependency>
<!-- 依赖版本为1.33,这个版本是shardingsphere所依赖的 -->
<dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.33</version>
</dependency>
<!-- <!– 依赖版本为30.0-jre,这个版本是shardingsphere所依赖的 –>-->
<!-- <dependency>-->
<!-- <groupId>com.google.guava</groupId>-->
<!-- <artifactId>guava</artifactId>-->
<!-- <version>30.0-jre</version>-->
<!-- </dependency>-->
<!-- oracle驱动版本升级,如果旧的oracle是groupId=oracle,artifactId=ojdbc通常是需要替换的
-->
<dependency>
<groupId>com.oracle.ojdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>19.3.0.0</version>
</dependency>
<dependency>
<groupId>com.oracle.ojdbc</groupId>
<artifactId>orai18n</artifactId>
<version>19.3.0.0</version>
</dependency>
3.SQl
**execute success Sql**
select id="selectList" resultMap="BaseResultMap">
select * from T_TEMPLATE_CFG T,
${dbuser.zhsq}.t_dc_org_entity_info B
where T.org_code = B.org_code and T.IS_VALID = '1'
</select>
**execute fial SQL**
<select id="selectList2" resultType="long">
select count(1) from T_TEMPLATE_CFG T
join ${dbuser.zhsq}.t_dc_org_entity_info B
on T.org_code = B.org_code
where T.IS_VALID = '1'
</select>
### Example codes for reproduce this issue (such as a github link).
--
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]
