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/10/10 06:41:01 UTC
[GitHub] [shardingsphere] pcyan opened a new issue, #17477: sharding-jdbc: Oracle pagination query error
pcyan opened a new issue, #17477:
URL: https://github.com/apache/shardingsphere/issues/17477
version
```
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.1</version>
```
testcase
```java
def "testOraclePagination"() {
when: "pagination case 1"
def pagination_sql2 = """
select * from (
select t1."num",rownum as "rownum_" from(
select 'a' as "num" from dual)t1)
where "rownum_" >? and "rownum_"< ?
"""
try (def connection = sqlSessionFactory.openSession().getConnection()) {
connection.prepareStatement(pagination_sql2)
}
then:
noExceptionThrown()
when: "pagination case 2"
def pagination_sql3 = """
select t2."num",t2,"rownum_" from (
select t1."num",rownum as "rownum_" from(
select 'a' as "num" from dual)t1)t2
where t2."rownum_" > ? and t2."rownum_" <= (?+?)
"""
try (def connection = sqlSessionFactory.openSession().getConnection()) {
connection.prepareStatement(pagination_sql3)
}
then:
noExceptionThrown()
when: "pagination case 3"
def pagination_sql4 = """
select * from (
select t1."num",rownum as "rownum_" from(
select 'a' as "num" from dual)t1)
where t2."rownum_" > ? and t2."rownum_" <= (?+?)
"""
try (def connection = sqlSessionFactory.openSession().getConnection()) {
connection.prepareStatement(pagination_sql4)
}
then:
noExceptionThrown()
}
```
pagination case 3 throw exception
```
Caused by: 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.ParameterMarkerExpressionSegment (org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonExpressionSegment and org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.ParameterMarkerExpressionSegment are in unnamed module of loader 'app')
at org.apache.shardingsphere.infra.binder.segment.select.pagination.engine.RowNumberPaginationContextEngine.createRowNumberValueSegment(RowNumberPaginationContextEngine.java:142)
at org.apache.shardingsphere.infra.binder.segment.select.pagination.engine.RowNumberPaginationContextEngine.createPaginationWithRowNumber(RowNumberPaginationContextEngine.java:129)
at org.apache.shardingsphere.infra.binder.segment.select.pagination.engine.RowNumberPaginationContextEngine.createPaginationContext(RowNumberPaginationContextEngine.java:67)
```
--
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] pcyan commented on issue #17477: sharding-jdbc: Oracle pagination query error
Posted by GitBox <gi...@apache.org>.
pcyan commented on issue #17477:
URL: https://github.com/apache/shardingsphere/issues/17477#issuecomment-1124709782
> Hi @pcyan, thank you for your feedback. Can you provide your sharding configuration and create table statement?
@strongduanmu exception occurred while parsing sql, not about the configuration or create table statement。just use the case below and you will get ClassCastException
```
def pagination_sql4 = """
select * from (
select t1."num",rownum as "rownum_" from(
select 'a' as "num" from dual)t1)
where t2."rownum_" > ? and t2."rownum_" <= (?+?)
"""
```
seems that shardingSphere cannot deal with common expression while pagination
--
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] pcyan closed issue #17477: sharding-jdbc: Oracle pagination query error
Posted by GitBox <gi...@apache.org>.
pcyan closed issue #17477: sharding-jdbc: Oracle pagination query error
URL: https://github.com/apache/shardingsphere/issues/17477
--
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] xinglijun1973 commented on issue #17477: sharding-jdbc: Oracle pagination query error
Posted by GitBox <gi...@apache.org>.
xinglijun1973 commented on issue #17477:
URL: https://github.com/apache/shardingsphere/issues/17477#issuecomment-1229676154
@pcyan It seems that the table alias `t2` is missing from the above statement. I use the following statement to execute correctly:
`select *
from (select t1."num", rownum as "rownum_"
from (select 'a' as "num" from dual) t1) t2
where t2."rownum_" > 0
and t2."rownum_" <= 10
`
--
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] pcyan commented on issue #17477: sharding-jdbc: Oracle pagination query error
Posted by GitBox <gi...@apache.org>.
pcyan commented on issue #17477:
URL: https://github.com/apache/shardingsphere/issues/17477#issuecomment-1288041136
fixed in ShardingSphere 5.2.0
--
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 #17477: sharding-jdbc: Oracle pagination query error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #17477:
URL: https://github.com/apache/shardingsphere/issues/17477#issuecomment-1123866981
Hi @pcyan, thank you for your feedback. Can you provide your sharding configuration and create table statement?
--
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] github-actions[bot] commented on issue #17477: sharding-jdbc: Oracle pagination query error
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #17477:
URL: https://github.com/apache/shardingsphere/issues/17477#issuecomment-1272349839
Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.
--
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] github-actions[bot] closed issue #17477: sharding-jdbc: Oracle pagination query error
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #17477: sharding-jdbc: Oracle pagination query error
URL: https://github.com/apache/shardingsphere/issues/17477
--
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