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 2020/12/31 03:27:19 UTC
[GitHub] [shardingsphere] xuup opened a new issue #8841: Oracle Rownum not take effect in Sharding-JDBC
xuup opened a new issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841
## Bug Report
Oracle page result limit does not work. Results from all datasources are returned without limit.
excute sql:
```sql
select * from (select tmp.*,ROWNUM ROW_ID from (select * from t_order where order_id > 0 order by order_id) tmp where ROWNUM <=2) where ROW_ID > 0
```
We expect return two query results, but it actually returns all of results
##### ShardingSphere version：
The master branch
##### Use Project:
ShardingSphere-JDBC
##### Expected behavior:
Two query results should be returned
##### Acture behavior:
There's no merge, four query results are returned
##### Reason analyze
We think SelectStatementContext.paginationContext is not properly configured, result in function decorate() in RowNumberDecoratorMergedResult is not true.
Class ```RowNumberPaginationContextEngine```
```java
public PaginationContext createPaginationContext(final ExpressionSegment where, final ProjectionsContext projectionsContext, final List<Object> parameters) {
Optional<String> rowNumberAlias = isRowNumberAlias(projectionsContext);
if (!rowNumberAlias.isPresent()) { //in my case, rowNumberAlias is empty
return new PaginationContext(null, null, parameters);
}
Collection<AndPredicate> andPredicates = new ExpressionBuilder(where).extractAndPredicates().getAndPredicates();
Collection<BinaryOperationExpression> rowNumberPredicates = getRowNumberPredicates(andPredicates, rowNumberAlias.get());
return rowNumberPredicates.isEmpty() ? new PaginationContext(null, null, parameters) : createPaginationWithRowNumber(rowNumberPredicates, parameters);
}
```
Class ```ShardingDQLResultMerger```
property hasPagination is false in object paginationContext ,so return type is MergedResult ,not RowNumberDecoratorMergedResult.
```java
private MergedResult decorate(final List<QueryResult> queryResults, final SelectStatementContext selectStatementContext, final MergedResult mergedResult) throws SQLException {
PaginationContext paginationContext = selectStatementContext.getPaginationContext();
if (!paginationContext.isHasPagination() || 1 == queryResults.size()) {
//hasPagination is false as previous setup in RowNumberPaginationContextEngine
return mergedResult;
}
String trunkDatabaseName = DatabaseTypeRegistry.getTrunkDatabaseType(databaseType.getName()).getName();
if ("MySQL".equals(trunkDatabaseName) || "PostgreSQL".equals(trunkDatabaseName)) {
return new LimitDecoratorMergedResult(mergedResult, paginationContext);
}
if ("Oracle".equals(trunkDatabaseName)) {
return new RowNumberDecoratorMergedResult(mergedResult, paginationContext);
}
if ("SQLServer".equals(trunkDatabaseName)) {
return new TopAndRowNumberDecoratorMergedResult(mergedResult, paginationContext);
}
return mergedResult;
}
```
##### Steps to reproduce the behavior:
The test project base on example
> https://github.com/apache/shardingsphere/tree/master/examples
import maven dependency
```xml
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc7</artifactId>
<version>12.1.0.1</version>
</dependency>
```
excute sql:
```sql
select * from (select tmp.*,ROWNUM ROW_ID from (select * from t_order where order_id > 0 order by order_id) tmp where ROWNUM <=2) where ROW_ID > 0
```
sharding-database.yml
```yaml
datasources：
ds0: !!com.zaxxer.hikari.HikariDataSource
driverClassName: oracle.jdbc.driver.OracleDriver
jdbcUrl: jdbc:oracle:thin:@xx.xxx.xxx.xx:1521:oracle12c
username: xxx
password: xxx
ds1: !!com.zaxxer.hikari.HikariDataSource
driverClassName: oracle.jdbc.driver.OracleDriver
jdbcUrl: jdbc:oracle:thin:@xx.xxx.xxx.xx:1521:oracle12c
username: xxx
password: xxx
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item
keyGenerateStrategy:
column: order_item_id
keyGeneratorName: snowflake
bindingTables:
- t_order,t_order_item
broadcastTables:
- t_address
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
props:
sql-show: true
```
Test java code
```java
@Test
public void shardingSelect() throws SQLException {
DataSource dataSource = YamlDataSourceFactory.newInstance(ShardingType.SHARDING_DATABASES);
String oracle_sql = "select * from (select tmp.*,ROWNUM ROW_ID from (select * from t_order where order_id > 0 order by order_id) tmp where ROWNUM <=2) where ROW_ID > 0 ";
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(oracle_sql);
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()){
...
}
rs.close();
preparedStatement.close();
connection.close();
}
```
Test result
Two query data are expected to be returned, but actually return all query data.
if any question , please feel free to contact，thx
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-913093614
Hi #12199 was merged, is it time for everyone to have a test on this bug-fix? @strongduanmu
--
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] wenjian-li removed a comment on issue #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
wenjian-li removed a comment on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-904561074
Hi~ I encounter the same question.
--
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] wenjian-li commented on issue #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
wenjian-li commented on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-909018645
How soon will this issue be fixed?
--
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 #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-909082500
@wenjian-li We will fix this issue before next release. If you are interested, you are welcome to submit a PR fix.
--
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] xuup commented on issue #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
xuup commented on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-753771090
> Hi @xuup ,
>
> I like your detailed description, which will help us get your question simply. :) Please leave @strongduanmu some time to have a look at it.
Thanks for your reply.
One more piece of information
There is an exception in ojdbc6, the example is work in ojdbc7. The details are as follows
pom.xml
```xml
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>12.1.0.2.0.24012252</version>
</dependency>
```
Exception details：
```java
java.lang.AbstractMethodError: oracle.jdbc.driver.T4CConnection.getSchema()Ljava/lang/String;
at com.zaxxer.hikari.pool.HikariProxyConnection.getSchema(HikariProxyConnection.java)
at org.apache.shardingsphere.infra.metadata.schema.builder.loader.adapter.MetaDataLoaderConnectionAdapter.getSchema(MetaDataLoaderConnectionAdapter.java:77)
at org.apache.shardingsphere.infra.metadata.schema.builder.loader.adapter.MetaDataLoaderConnectionAdapter.getSchema(MetaDataLoaderConnectionAdapter.java:71)
at org.apache.shardingsphere.infra.metadata.schema.builder.loader.SchemaMetaDataLoader.loadAllTableNames(SchemaMetaDataLoader.java:70)
at org.apache.shardingsphere.infra.metadata.schema.builder.loader.SchemaMetaDataLoader.loadAllTableNames(SchemaMetaDataLoader.java:59)
at org.apache.shardingsphere.sharding.rule.single.SingleTableRuleLoader.load(SingleTableRuleLoader.java:60)
at org.apache.shardingsphere.sharding.rule.single.SingleTableRuleLoader.load(SingleTableRuleLoader.java:51)
at org.apache.shardingsphere.sharding.rule.ShardingRule.<init>(ShardingRule.java:104)
at org.apache.shardingsphere.sharding.rule.builder.ShardingRuleBuilder.build(ShardingRuleBuilder.java:43)
at org.apache.shardingsphere.sharding.rule.builder.ShardingRuleBuilder.build(ShardingRuleBuilder.java:34)
at org.apache.shardingsphere.infra.rule.builder.ShardingSphereRulesBuilder.lambda$build$0(ShardingSphereRulesBuilder.java:58)
at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
at java.util.Iterator.forEachRemaining(Iterator.java:116)
at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499)
at org.apache.shardingsphere.infra.rule.builder.ShardingSphereRulesBuilder.build(ShardingSphereRulesBuilder.java:58)
at org.apache.shardingsphere.infra.context.metadata.MetaDataContextsBuilder.buildMetaData(MetaDataContextsBuilder.java:102)
at org.apache.shardingsphere.infra.context.metadata.MetaDataContextsBuilder.build(MetaDataContextsBuilder.java:93)
at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.<init>(ShardingSphereDataSource.java:57)
at org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory.createDataSource(ShardingSphereDataSourceFactory.java:49)
at org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory.createDataSource(YamlShardingSphereDataSourceFactory.java:53)
```
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] wenjian-li commented on issue #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
wenjian-li commented on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-909018645
How soon will this issue be fixed?
--
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] tristaZero commented on issue #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-753735638
Hi @xuup ,
I like your detailed description, which will help us get your question simply. :) Please leave @strongduanmu some time to have a look at it.
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] wenjian-li commented on issue #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
wenjian-li commented on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-904561074
Hi~ I encounter the same question.
--
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 #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-909082500
@wenjian-li We will fix this issue before next release. If you are interested, you are welcome to submit a PR fix.
--
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] tristaZero commented on issue #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-906272380
@xuup Thanks for your additional feedback, @strongduanmu is working on 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] tristaZero closed issue #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841
--
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] wenjian-li commented on issue #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
wenjian-li commented on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-909018645
How soon will this issue be fixed?
--
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 #8841: Oracle Rownum not take effect in Sharding-JDBC
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #8841:
URL: https://github.com/apache/shardingsphere/issues/8841#issuecomment-909082500
@wenjian-li We will fix this issue before next release. If you are interested, you are welcome to submit a PR fix.
--
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