oulexue opened a new issue, #14100:
URL: https://github.com/apache/shardingsphere/issues/14100
### Which version of ShardingSphere did you use?
5.0.0
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
sharding-jdbc
### Expected behavior
```sql
SELECT
b.hashchildarea,
b.regname regionName,
ifnull( t.settleNum, 0 ) settleNum,
ifnull( t.settleNumAll, 0 ) settleNumAll,
ifnull( t2.shouldSettle, 0 ) shouldSettle,
ifnull( t2.totalSob, 0 ) totalSob,
ifnull( t2.totalSobVile, 0 ) totalSobVile,
ifnull( t2.totalSobComm, 0 ) totalSobComm,
b.regionid regionId,
ifnull( t1.totalVoucher, 0 ) totalVoucher,
ifnull( t1.totalVoucherCur, 0 ) totalVoucherCur,(
SELECT
count( id )
FROM
bus_regions
WHERE
regionid LIKE concat( b.regionid, '%' )
AND hashchildarea = 1
) totalRegion,
(
SELECT
count( id )
FROM
bus_regions bu
WHERE
regionid LIKE concat( t2.ca, '%' )
AND hashchildarea = 1
AND EXISTS ( SELECT 1 FROM tb_finance_sob sob WHERE sob.region_id =
bu.regionid )) totalRegionOnSob,
(
SELECT
count(
DISTINCT ( region_id ))
FROM
tb_finance_sob s
WHERE
region_id LIKE concat( t2.ca, '%' )
AND length( region_id )> 12
AND EXISTS (
SELECT
1
FROM
tb_finance_settle_accounts_record r
WHERE
s.id = r.sob_id
AND settle_accounts_date BETWEEN ?
AND ?)) totalSobCommSettle,
(
SELECT
count(
DISTINCT ( region_id ))
FROM
tb_finance_sob s
WHERE
region_id LIKE concat( t2.ca, '%' )
AND length( region_id )= 12
AND EXISTS (
SELECT
1
FROM
tb_finance_settle_accounts_record r
WHERE
s.id = r.sob_id
AND settle_accounts_date BETWEEN ?
AND ?)) totalSobVileSettle
FROM
bus_regions b
LEFT JOIN (
SELECT
count( sob.id ) totalSob,
region_id,
substr(
region_id,
1,?) ca,
sum( CASE sob_profession_type WHEN ? THEN 1 END ) totalSobVile,
sum( CASE sob_profession_type WHEN ? THEN 1 END ) totalSobComm,
sum(
CASE
WHEN sob_enable_date BETWEEN ?
AND ? THEN
TIMESTAMPDIFF(
MONTH,
concat( sob_enable_date, '-01'
),
concat( ?, '-01' ))+ 1
WHEN sob_enable_date < ? THEN
TIMESTAMPDIFF(
MONTH,
concat( ?, '-01' ),
concat( ?, '-01' ))+ 1
END
) shouldSettle
FROM
tb_finance_sob sob
WHERE
region_id LIKE concat(?, '%' )
AND length(
substr(
region_id,
1,?))=?
GROUP BY
ca
) t2 ON b.regionid = t2.ca
LEFT JOIN (
SELECT
substr(
region_id,
1,?) ca,
sum( CASE WHEN settle_accounts_date = ? THEN 1
END ) settleNum,
sum( CASE WHEN settle_accounts_date BETWEEN ?
AND ? THEN 1 END ) settleNumAll
FROM
tb_finance_sob sob
LEFT JOIN tb_finance_settle_accounts_record
record ON sob.id = record.sob_id
WHERE
sob.region_id LIKE concat(?, '%' )
AND length(
substr(
region_id,
1,?))=?
GROUP BY
ca
) t ON t2.ca = t.ca
LEFT JOIN (
SELECT
sum( CASE WHEN date_format( v.voucher_date,
'%Y-%m' ) BETWEEN sob.sob_enable_date AND ? THEN 1 END ) totalVoucher,
sum( CASE WHEN date_format( v.voucher_date,
'%Y-%m' ) BETWEEN ? AND ? THEN 1 ELSE 0 END ) totalVoucherCur,
region_id,
substr(
region_id,
1,?) ca
FROM
tb_finance_sob sob
LEFT JOIN tb_finance_voucher v ON sob.id =
v.sob_id
WHERE
region_id LIKE concat(?, '%' )
AND length(
substr(
region_id,
1,?))=?
GROUP BY
ca
) t1 ON b.regionid = t1.ca
WHERE
b.regionid LIKE concat(?, '%' )
AND length( regionid )=?
LIMIT ?,?
```
### Actual behavior
```log
Caused by: java.lang.ClassCastException: java.lang.String cannot be cast to
java.lang.Integer
at
org.apache.shardingsphere.infra.binder.segment.select.pagination.PaginationContext.getValue(PaginationContext.java:57)
at
org.apache.shardingsphere.infra.binder.segment.select.pagination.PaginationContext.<init>(PaginationContext.java:50)
at
org.apache.shardingsphere.infra.binder.segment.select.pagination.engine.LimitPaginationContextEngine.createPaginationContext(LimitPaginationContextEngine.java:38)
at
org.apache.shardingsphere.infra.binder.segment.select.pagination.engine.PaginationContextEngine.createPaginationContext(PaginationContextEngine.java:57)
at
org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.<init>(SelectStatementContext.java:91)
at
org.apache.shardingsphere.infra.binder.SQLStatementContextFactory.getDMLStatementContext(SQLStatementContextFactory.java:127)
at
org.apache.shardingsphere.infra.binder.SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:110)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createLogicSQL(ShardingSpherePreparedStatement.java:385)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createExecutionContext(ShardingSpherePreparedStatement.java:375)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:286)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at sun.reflect.GeneratedMethodAccessor149.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy461.query(Unknown Source)
at sun.reflect.GeneratedMethodAccessor149.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy461.query(Unknown Source)
at
com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:67)
at
org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
```
--
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:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
