2021-11-03 使用shardingsphere后，jpa中的offset语句出现问题(postgre)

使用shardingsphere后，jpa中的offset语句出现问题(postgre)

使用的shardingsphere依赖为

 <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>

分表后，jpa查询中的动态分页查询，即

 Pageable pageable = PageRequest.of(current - 1, pageSize);
repository.findAll((Specification<LoopEntity>) (root, query, criteriaBuilder) -> {
...
}), pageable

中的分页查询会出现错误结果。
此时通过控制台hql语句，对比数据库软件的sql执行语句发现执行结果不同。
通过jpa自定义语句发现offset关键字意义变动。
使用limit x,y 语句后报错

[2021-11-03 16:51:30.687] [ERROR] [http-nio-18502-exec-3] 31352 --- o.h.e.jdbc.spi.SqlExceptionHelper : 错误: 不支持 LIMIT #,# 语法
  建议：LIMIT和OFFSET子句要分隔开
  位置：80
  ...
  Caused by: org.postgresql.util.PSQLException: 错误: 不支持 LIMIT #,# 语法
  建议：LIMIT和OFFSET子句要分隔开
  位置：80
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:117)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)
	at io.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.getQueryResult(PreparedStatementExecutor.java:113)
	at io.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.access$200(PreparedStatementExecutor.java:50)
	at io.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor$2.executeSQL(PreparedStatementExecutor.java:105)
	at io.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor$2.executeSQL(PreparedStatementExecutor.java:101)
	at io.shardingsphere.core.executor.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:72)
	at io.shardingsphere.core.executor.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:61)
	at io.shardingsphere.core.executor.ShardingExecuteEngine.syncGroupExecute(ShardingExecuteEngine.java:175)
	at io.shardingsphere.core.executor.ShardingExecuteEngine.groupExecute(ShardingExecuteEngine.java:152)
	at io.shardingsphere.core.executor.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:71)
	at io.shardingsphere.core.executor.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:54)
	at io.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:114)
	at io.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.executeQuery(PreparedStatementExecutor.java:108)
	at io.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.executeQuery(ShardingPreparedStatement.java:109)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
	... 99 common frames omitted

发现shardingsphere报错。可知分表后limit 和offset语义被改写。需注意。

发现是3.1版本的bug

https://www.oschina.net/question/3255774_2302827
退回3.0后可解决