You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/03/10 11:33:48 UTC
[GitHub] [shardingsphere] onevroad opened a new issue #15975: Cannot select count in Oracle
onevroad opened a new issue #15975:
URL: https://github.com/apache/shardingsphere/issues/15975
## Bug Report
When I select count in multi-tables by using ShardingSphere, it throw a NullPointerException error.
### Which version of ShardingSphere did you use?
sharding 5.1.0
java 1.8
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
### Actual behavior
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
my config:
```
spring.shardingsphere.datasource.names=ds0,ds1
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=oracle.jdbc.OracleDriver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
spring.shardingsphere.datasource.ds0.username=demo
spring.shardingsphere.datasource.ds0.password=123456
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=oracle.jdbc.OracleDriver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:oracle:thin:@127.0.0.1:1522:orcl
spring.shardingsphere.datasource.ds1.username=demo
spring.shardingsphere.datasource.ds1.password=123456
spring.shardingsphere.rules.readwrite-splitting.data-sources.ds.type=Static
spring.shardingsphere.rules.readwrite-splitting.data-sources.ds.props.write-data-source-name=ds0
spring.shardingsphere.rules.readwrite-splitting.data-sources.ds.props.read-data-source-names=ds1
spring.shardingsphere.rules.readwrite-splitting.data-sources.ds.load-balancer-name=round-robin
spring.shardingsphere.rules.readwrite-splitting.load-balancers.round-robin.type=ROUND_ROBIN
spring.shardingsphere.rules.sharding.tables.cobra_invoice.actual-data-nodes=ds.cobra_invoice_$->{0..9}
spring.shardingsphere.rules.sharding.tables.cobra_invoice.table-strategy.standard.sharding-column=creator_id
spring.shardingsphere.rules.sharding.tables.cobra_invoice.table-strategy.standard.sharding-algorithm-name=table-inline
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithm-expression=cobra_invoice_$->{creator_id % 10}
```
The error is:
```
2022-03-10 19:19:17.645 INFO 6972 --- [io-10099-exec-7] ShardingSphere-SQL : Logic SQL: SELECT count(0) FROM cobra_invoice WHERE ((id IN (?) AND creator_id IN (?, ?) AND flag = ?))
2022-03-10 19:19:17.646 INFO 6972 --- [io-10099-exec-7] ShardingSphere-SQL : SQLStatement: OracleSelectStatement(lock=Optional.empty, modelSegment=Optional.empty)
2022-03-10 19:19:17.646 INFO 6972 --- [io-10099-exec-7] ShardingSphere-SQL : Actual SQL: slave-01 ::: SELECT count(0) FROM cobra_invoice_8 WHERE ((id IN (?) AND creator_id IN (?, ?) AND flag = ?)) UNION ALL SELECT count(0) FROM cobra_invoice_9 WHERE ((id IN (?) AND creator_id IN (?, ?) AND flag = ?)) ::: [108, 108, 109, 1, 108, 108, 109, 1]
2022-03-10 19:19:17.655 ERROR 6972 --- [io-10099-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.lang.NullPointerException
### The error may exist in demo/data/mapper/CobraInvoiceMapper.java (best guess)
### The error may involve demo.data.mapper.CobraInvoiceMapper.selectByExampleAndRowBounds_COUNT
### The error occurred while handling results
### SQL: SELECT count(0) FROM cobra_invoice WHERE ((id IN (?) AND creator_id IN (?, ?) AND flag = ?))
### Cause: java.lang.NullPointerException] with root cause
java.lang.NullPointerException: null
at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.getValueCaseSensitiveFromTables(GroupByMemoryMergedResult.java:134)
at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.getValueCaseSensitive(GroupByMemoryMergedResult.java:124)
at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.init(GroupByMemoryMergedResult.java:73)
at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.init(GroupByMemoryMergedResult.java:53)
at org.apache.shardingsphere.infra.merge.result.impl.memory.MemoryMergedResult.<init>(MemoryMergedResult.java:51)
at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.<init>(GroupByMemoryMergedResult.java:56)
at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.getGroupByMergedResult(ShardingDQLResultMerger.java:112)
at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.build(ShardingDQLResultMerger.java:82)
at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:63)
at org.apache.shardingsphere.infra.merge.MergeEngine.executeMerge(MergeEngine.java:90)
at org.apache.shardingsphere.infra.merge.MergeEngine.merge(MergeEngine.java:80)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.mergeQuery(ShardingSpherePreparedStatement.java:446)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.getResultSet(ShardingSpherePreparedStatement.java:396)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getFirstResultSet(DefaultResultSetHandler.java:237)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:187)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
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:325)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at com.github.pagehelper.PageInterceptor.executeAutoCount(PageInterceptor.java:201)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:113)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy273.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
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.$Proxy236.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:231)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:145)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
at com.sun.proxy.$Proxy245.selectByExampleAndRowBounds(Unknown Source)
```
--
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] sam2099 commented on issue #15975: Cannot select count in Oracle
Posted by GitBox <gi...@apache.org>.
sam2099 commented on issue #15975:
URL: https://github.com/apache/shardingsphere/issues/15975#issuecomment-1068919757
Me too,
Exception as same.
Which version of ShardingSphere did you use?
sharding 5.1.0
java 11
JPA
Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
--
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] sam2099 edited a comment on issue #15975: Cannot select count in Oracle
Posted by GitBox <gi...@apache.org>.
sam2099 edited a comment on issue #15975:
URL: https://github.com/apache/shardingsphere/issues/15975#issuecomment-1068919757
Me too,
Exception as same.
Which version of ShardingSphere did you use?
sharding 5.1.0
java 11
JPA
Oracle
Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
--
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] cheese8 commented on issue #15975: Cannot select count in Oracle
Posted by GitBox <gi...@apache.org>.
cheese8 commented on issue #15975:
URL: https://github.com/apache/shardingsphere/issues/15975#issuecomment-1064670936
I will take a look.
--
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] qiannan5566 commented on issue #15975: Cannot select count in Oracle
Posted by GitBox <gi...@apache.org>.
qiannan5566 commented on issue #15975:
URL: https://github.com/apache/shardingsphere/issues/15975#issuecomment-1074784538
试试把配置的规则中表名配置为大写，
`S_USER$->{Math.abs(loan_no.hashCode())` % 2}
`ds0.S_USER$->{0..1}`
像这样，oracle中会有这个问题
rules:
sharding:
key-generators:
snowflake:
type: snowflake
props:
worker-id: 123
binding-tables:
- s_user
sharding-algorithms:
sharding-user-by-loan-no:
props:
algorithm-expression: S_USER$->{Math.abs(loan_no.hashCode()) % 2}
type: inline
tables:
s_user:
key-generate-strategy:
column: user_code
key-generator-name: snowflake
actual-data-nodes: ds0.S_USER$->{0..1}
table-strategy:
standard:
sharding-column: loan_no
sharding-algorithm-name: sharding-user-by-loan-no
--
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 #15975: Cannot select count in Oracle
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #15975:
URL: https://github.com/apache/shardingsphere/issues/15975#issuecomment-1064665585
@onevroad Thank you for your feedback, we will investigate this issue.
--
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] sam2099 edited a comment on issue #15975: Cannot select count in Oracle
Posted by GitBox <gi...@apache.org>.
sam2099 edited a comment on issue #15975:
URL: https://github.com/apache/shardingsphere/issues/15975#issuecomment-1068919757
Me too,
Exception as same.
Which version of ShardingSphere did you use?
sharding 5.1.0
java 11
JPA
Oracle
Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
--
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 #15975: Cannot select count in Oracle
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #15975:
URL: https://github.com/apache/shardingsphere/issues/15975#issuecomment-1064674841
@cheese8 Welcome.
--
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] qiannan5566 commented on issue #15975: Cannot select count in Oracle
Posted by GitBox <gi...@apache.org>.
qiannan5566 commented on issue #15975:
URL: https://github.com/apache/shardingsphere/issues/15975#issuecomment-1077386843
关键点是在启动时，加载表配置那块代码，可以看一下，如果表规则配置为小写，加载出来的表配置会有问题
--
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] onevroad commented on issue #15975: Cannot select count in Oracle
Posted by GitBox <gi...@apache.org>.
onevroad commented on issue #15975:
URL: https://github.com/apache/shardingsphere/issues/15975#issuecomment-1076237081
> 试试把配置的规则中表名配置为大写， `S_USER$->{Math.abs(loan_no.hashCode())` % 2} `ds0.S_USER$->{0..1}` 像这样，oracle中会有这个问题 rules: sharding: key-generators: snowflake: type: snowflake props: worker-id: 123
>
> ```
> binding-tables:
> - s_user
> sharding-algorithms:
> sharding-user-by-loan-no:
> props:
> algorithm-expression: S_USER$->{Math.abs(loan_no.hashCode()) % 2}
> type: inline
> tables:
> s_user:
> key-generate-strategy:
> column: user_code
> key-generator-name: snowflake
> actual-data-nodes: ds0.S_USER$->{0..1}
> table-strategy:
> standard:
> sharding-column: loan_no
> sharding-algorithm-name: sharding-user-by-loan-no
> ```
yes, it's work, when I change the table name to uppercase in config.
I find that the exception is only throw in order by or limit.
--
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