You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "gs-liut (via GitHub)" <gi...@apache.org> on 2023/04/06 06:37:43 UTC
[GitHub] [shardingsphere] gs-liut opened a new issue, #25025: postgresql pagination by OFFSET FETCH
gs-liut opened a new issue, #25025:
URL: https://github.com/apache/shardingsphere/issues/25025
## Bug Report
### Which version of ShardingSphere did you use?
5.3.2
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
pom.xml
```
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.0.5</version>
<relativePath/>
</parent>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
```
RepositoryTest
`repository.findAll(PageRequest.of(0,10));`
throws an exception:
```
23-04-06 14:31:50.095 -DEBUG - [] - [main] org.hibernate.SQL : select p1_0.id,p1_0.activities_id from pay_log p1_0 offset ? rows fetch first ? rows only
java.lang.ClassCastException: class org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonExpressionSegment cannot be cast to class org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.LiteralExpressionSegment (org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonExpressionSegment and org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.LiteralExpressionSegment are in unnamed module of loader 'app')
at org.apache.shardingsphere.sql.parser.postgresql.visitor.statement.impl.PostgreSQLStatementSQLVisitor.visitSelectOffsetValue(PostgreSQLStatementSQLVisitor.java:1254)
at org.apache.shardingsphere.sql.parser.postgresql.visitor.statement.impl.PostgreSQLStatementSQLVisitor.visitSelectOffsetValue(PostgreSQLStatementSQLVisitor.java:205)
at org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$SelectOffsetValueContext.accept(PostgreSQLStatementParser.java:7772)
at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at org.apache.shardingsphere.sql.parser.postgresql.visitor.statement.impl.PostgreSQLStatementSQLVisitor.createLimitSegmentWhenLimitAndOffset(PostgreSQLStatementSQLVisitor.java:1276)
at org.apache.shardingsphere.sql.parser.postgresql.visitor.statement.impl.PostgreSQLStatementSQLVisitor.visitSelectLimit(PostgreSQLStatementSQLVisitor.java:1231)
at org.apache.shardingsphere.sql.parser.postgresql.visitor.statement.impl.PostgreSQLStatementSQLVisitor.visitSelectLimit(PostgreSQLStatementSQLVisitor.java:205)
at org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$SelectLimitContext.accept(PostgreSQLStatementParser.java:6951)
at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at org.apache.shardingsphere.sql.parser.postgresql.visitor.statement.impl.PostgreSQLStatementSQLVisitor.visitSelectNoParens(PostgreSQLStatementSQLVisitor.java:898)
at org.apache.shardingsphere.sql.parser.postgresql.visitor.statement.impl.PostgreSQLStatementSQLVisitor.visitSelectNoParens(PostgreSQLStatementSQLVisitor.java:205)
at org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$SelectNoParensContext.accept(PostgreSQLStatementParser.java:5303)
at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at org.apache.shardingsphere.sql.parser.postgresql.visitor.statement.impl.PostgreSQLStatementSQLVisitor.visitSelect(PostgreSQLStatementSQLVisitor.java:885)
at org.apache.shardingsphere.sql.parser.postgresql.visitor.statement.impl.PostgreSQLStatementSQLVisitor.visitSelect(PostgreSQLStatementSQLVisitor.java:205)
at org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$SelectContext.accept(PostgreSQLStatementParser.java:5180)
at org.apache.shardingsphere.sql.parser.api.SQLVisitorEngine.visit(SQLVisitorEngine.java:54)
at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserExecutor.parse(SQLStatementParserExecutor.java:48)
at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:41)
at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:30)
at com.github.benmanes.caffeine.cache.LocalLoadingCache.lambda$newMappingFunction$3(LocalLoadingCache.java:183)
at com.github.benmanes.caffeine.cache.BoundedLocalCache.lambda$doComputeIfAbsent$14(BoundedLocalCache.java:2677)
at java.base/java.util.concurrent.ConcurrentHashMap.compute(ConcurrentHashMap.java:1916)
at com.github.benmanes.caffeine.cache.BoundedLocalCache.doComputeIfAbsent(BoundedLocalCache.java:2675)
at com.github.benmanes.caffeine.cache.BoundedLocalCache.computeIfAbsent(BoundedLocalCache.java:2658)
at com.github.benmanes.caffeine.cache.LocalCache.computeIfAbsent(LocalCache.java:112)
at com.github.benmanes.caffeine.cache.LocalLoadingCache.get(LocalLoadingCache.java:58)
at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine.parse(SQLStatementParserEngine.java:47)
at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:58)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:205)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:171)
at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:87)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:90)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:75)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.lambda$list$0(JdbcSelectExecutorStandardImpl.java:100)
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:171)
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:146)
at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:205)
at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:85)
at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:29)
at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:88)
at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:197)
at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:443)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:166)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:91)
at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$0(ConcreteSqmSelectQueryPlan.java:113)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:335)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:276)
at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:571)
at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:363)
at org.hibernate.query.sqm.internal.QuerySqmImpl.list(QuerySqmImpl.java:1073)
at org.hibernate.query.Query.getResultList(Query.java:94)
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:692)
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:474)
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:451)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:288)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120)
at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:516)
at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:628)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:168)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:77)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:163)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:218)
at jdk.proxy2/jdk.proxy2.$Proxy186.findAll(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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #25025: postgresql pagination by OFFSET FETCH
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25025:
URL: https://github.com/apache/shardingsphere/issues/25025#issuecomment-1501381529
Hi @gs-liut, can you config sharding rule for pay_log?
--
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] gs-liut commented on issue #25025: postgresql pagination by OFFSET FETCH
Posted by "gs-liut (via GitHub)" <gi...@apache.org>.
gs-liut commented on issue #25025:
URL: https://github.com/apache/shardingsphere/issues/25025#issuecomment-1501399678
@strongduanmu yes,
ShardingDataSourceConfiguration.java:
```
public class ShardingDataSourceConfiguration {
private static final String DATABASE_NAME = "gold_coin_logic_db";
@Bean
public DataSource dataSource(DataSourceMapProperties properties) throws SQLException {
return ShardingSphereDataSourceFactory.createDataSource(DATABASE_NAME, createModeConfiguration(), createDataSourceMap(properties), createRuleConfiguration(), createProperties());
}
private ModeConfiguration createModeConfiguration() {
return new ModeConfiguration("Standalone", new StandalonePersistRepositoryConfiguration("JDBC", new Properties()));
}
private Collection<RuleConfiguration> createRuleConfiguration() {
ShardingRuleConfiguration configuration = new ShardingRuleConfiguration();
configuration.getAutoTables().add(getPayLogTableRuleConfiguration());
Properties payLogProps = new Properties();
payLogProps.setProperty("sharding-count", "40");
configuration.getShardingAlgorithms().put("pay_log_hash", new AlgorithmConfiguration("HASH_MOD", payLogProps));
Collection<RuleConfiguration> collection = new LinkedList<>();
collection.add(configuration);
return collection;
}
private ShardingAutoTableRuleConfiguration getPayLogTableRuleConfiguration() {
ShardingAutoTableRuleConfiguration configuration = new ShardingAutoTableRuleConfiguration("pay_log", "ds_${0..9}");
configuration.setShardingStrategy(new StandardShardingStrategyConfiguration("user_id", "pay_log_hash"));
return configuration;
}
private Properties createProperties() {
Properties properties = new Properties();
properties.setProperty(ConfigurationPropertyKey.SQL_SHOW.getKey(), "false");
properties.setProperty(ConfigurationPropertyKey.MAX_CONNECTIONS_SIZE_PER_QUERY.getKey(), "4");
return properties;
}
private Map<String, DataSource> createDataSourceMap(DataSourceMapProperties properties) {
Map<String, DataSource> dataSourceMap = new LinkedHashMap<>(properties.getDsMap().size());
properties.dsMap.forEach((k, v) -> {
v.setPoolName(properties.getPoolName() + k);
v.setAutoCommit(properties.getAutoCommit());
v.setReadOnly(properties.getReadOnly());
v.setMaximumPoolSize(properties.getMaxPoolSize());
v.setIdleTimeout(properties.getIdleTimeout());
v.setMaxLifetime(properties.getMaxLifetime());
dataSourceMap.put(k, v);
});
return dataSourceMap;
}
@Data
@Component
@ConfigurationProperties(prefix = "sharding")
@PropertySource(value = {"classpath:config/sharding-ds-${spring.profiles.active:dev}.yml"}, encoding = "UTF-8", factory = YamlPropertySourceFactory.class)
public static class DataSourceMapProperties {
private String poolName;
private Boolean autoCommit, readOnly;
private Integer maxPoolSize, idleTimeout, maxLifetime;
private LinkedHashMap<String, HikariDataSource> dsMap;
}
}
```
Spring JPA config:
```
spring:
jpa:
show-sql: false
open-in-view: true
database-platform: org.hibernate.dialect.PostgreSQLDialect
```
--
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] gs-liut closed issue #25025: postgresql pagination by OFFSET FETCH
Posted by "gs-liut (via GitHub)" <gi...@apache.org>.
gs-liut closed issue #25025: postgresql pagination by OFFSET FETCH
URL: https://github.com/apache/shardingsphere/issues/25025
--
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] liaojiexin commented on issue #25025: postgresql pagination by OFFSET FETCH
Posted by "liaojiexin (via GitHub)" <gi...@apache.org>.
liaojiexin commented on issue #25025:
URL: https://github.com/apache/shardingsphere/issues/25025#issuecomment-1499031126
I also ran into this error。
```sql
SELECT t.*,
CASE to_tsvector(replace(replace(w_mask,' or ',' | '),'not',' &! ')) @@
to_tsquery(replace(replace(replace(#{mask},' or ',' | '),' not ',' &! '),')not ',')&! '))
WHEN false THEN 0 ELSE 1 END AS writable
FROM DS_ATTACH_NODE t
WHERE t.STATUS != 1 and t.STATUS != 4
AND t.pid = #{nid}
AND to_tsvector(replace(replace(mask,' or ',' | '),'not',' &! ')) @@
to_tsquery(replace(replace(replace(#{mask},' or ',' | '),' not ',' &! '),')not ',')&! ')) = true
<if test="bimId !=null and bimId!=null">
and bimid=#{bimId}
</if>
ORDER BY t.is_file ASC,t.sindex ASC, t.sindex NULLS LAST, t.up_date ASC limit (#{end}-#{start}) offset
(#{start}-1)
```
--
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