【DM版本】: DM8
【操作系统】： window,linux
【CPU】: 8
【问题描述】*：
框架版本：
shardingsphere 4.1.0
mybatis-plus 3.4.3.4
Dm8JdbcDriver18 ：8.1.4.6

跨分表查询时，ROWNUM会被赋值一个很大的数据，不跨分表单表查询就是传入的参数10 ，分片字段：sharding_route

ShardingSphere-SQL [] : Logic SQL: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( select
c.id as contract_id, c.name as contract_name, c.code as contract_code, c.sign_sortable,
c.expired_time,c.create_time,c.enterprise_id,c.enterprise_name,c.creator,c.create_name,
c.app_id,c.data_source,c.send_time,
cs.id as signer_id, cs.signer_type as signer_user_type,
cs.user_id as signer_user_id,cs.name as signer_user_name,
cs.enterprise_id as signer_enterprise_id,cs.enterprise_name as signer_enterprise_name

from contract c left join contract_signer cs
on c.id = cs.contract_id

WHERE c.deleted = 0 and cs.deleted=0
and c.sharding_route in ( ? )
and cs.sharding_route in (?)
and cs.user_id = ?
and c.status in (1,2)
and cs.status = 2
and cs.signer_type = ?
and c.create_time >= to_date(?,'yyyy-mm-dd hh24:mi:ss')
and c.create_time <= to_date(?,'yyyy-mm-dd hh24:mi:ss')
group by
c.id , c.name , c.code , c.sign_sortable,c.expired_time,c.create_time,c.enterprise_id,c.enterprise_name,c.creator,c.create_name,
c.app_id,c.data_source,c.send_time,
cs.id, cs.signer_type,
cs.user_id,cs.name,
cs.enterprise_id,cs.enterprise_name
order by c.id desc ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ?

实际sql ROWNUM 变成了 2147483647

2025-03-13 13:10:16.147 INFO [ main] ShardingSphere-SQL [] : Actual SQL: ds ::: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( select
c.id as contract_id, c.name as contract_name, c.code as contract_code, c.sign_sortable,
c.expired_time,c.create_time,c.enterprise_id,c.enterprise_name,c.creator,c.create_name,
c.app_id,c.data_source,c.send_time,
cs.id as signer_id, cs.signer_type as signer_user_type,
cs.user_id as signer_user_id,cs.name as signer_user_name,
cs.enterprise_id as signer_enterprise_id,cs.enterprise_name as signer_enterprise_name

from contract_2022 c left join contract_signer_2022 cs
on c.id = cs.contract_id

WHERE c.deleted = 0 and cs.deleted=0
and c.sharding_route in ( ? , ? , ? )
and cs.sharding_route in (?,?,?)
and cs.user_id = ?
and c.status in (1,2)
and cs.status = 2
and cs.signer_type = ?
and c.create_time >= to_date(?,'yyyy-mm-dd hh24:mi:ss')
and c.create_time <= to_date(?,'yyyy-mm-dd hh24:mi:ss')

group by
c.id , c.name , c.code , c.sign_sortable,
c.expired_time,c.create_time,c.enterprise_id,c.enterprise_name,c.creator,c.create_name,
c.app_id,c.data_source,c.send_time, cs.id, cs.signer_type, cs.user_id,cs.name, cs.enterprise_id,cs.enterprise_name

order by c.id desc ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ? ::: [2022, 2023, 2024, 2022, 2023, 2024, L53P00QVI8JNYE, 1, 2022-01-01 00:00:00.0, 2024-12-31 23:59:59.0, 2147483647, 0]
2025-03-13 13:10:16.148 INFO [ main] ShardingSphere-SQL [] : Actual SQL: ds ::: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( select
c.id as contract_id, c.name as contract_name, c.code as contract_code, c.sign_sortable,
c.expired_time,c.create_time,c.enterprise_id,c.enterprise_name,c.creator,c.create_name,
c.app_id,c.data_source,c.send_time,
cs.id as signer_id, cs.signer_type as signer_user_type,
cs.user_id as signer_user_id,cs.name as signer_user_name,
cs.enterprise_id as signer_enterprise_id,cs.enterprise_name as signer_enterprise_name

from contract_2023 c left join contract_signer_2023 cs
on c.id = cs.contract_id

WHERE c.deleted = 0 and cs.deleted=0
and c.sharding_route in ( ? , ? , ? )
and cs.sharding_route in (?,?,?)
and cs.user_id = ?
and c.status in (1,2)
and cs.status = 2
and cs.signer_type = ?
and c.create_time >= to_date(?,'yyyy-mm-dd hh24:mi:ss')
and c.create_time <= to_date(?,'yyyy-mm-dd hh24:mi:ss')
group by
c.id , c.name , c.code , c.sign_sortable,
c.expired_time,c.create_time,c.enterprise_id,c.enterprise_name,c.creator,c.create_name,
c.app_id,c.data_source,c.send_time, cs.id, cs.signer_type, cs.user_id,cs.name, cs.enterprise_id,cs.enterprise_name

order by c.id desc ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ? ::: [2022, 2023, 2024, 2022, 2023, 2024, L53P00QVI8JNYE, 1, 2022-01-01 00:00:00.0, 2024-12-31 23:59:59.0, 2147483647, 0]
2025-03-13 13:10:16.148 INFO [ main] ShardingSphere-SQL [] : Actual SQL: ds ::: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( select
c.id as contract_id, c.name as contract_name, c.code as contract_code, c.sign_sortable,
c.expired_time,c.create_time,c.enterprise_id,c.enterprise_name,c.creator,c.create_name,
c.app_id,c.data_source,c.send_time,
cs.id as signer_id, cs.signer_type as signer_user_type,
cs.user_id as signer_user_id,cs.name as signer_user_name,
cs.enterprise_id as signer_enterprise_id,cs.enterprise_name as signer_enterprise_name

from contract_2024 c left join contract_signer_2024 cs
on c.id = cs.contract_id

WHERE c.deleted = 0 and cs.deleted=0
and c.sharding_route in ( ? , ? , ? )
and cs.sharding_route in (?,?,?)
and cs.user_id = ?
and c.status in (1,2)
and cs.status = 2
and cs.signer_type = ?
and c.create_time >= to_date(?,'yyyy-mm-dd hh24:mi:ss')
and c.create_time <= to_date(?,'yyyy-mm-dd hh24:mi:ss')
group by
c.id , c.name , c.code , c.sign_sortable,
c.expired_time,c.create_time,c.enterprise_id,c.enterprise_name,c.creator,c.create_name,
c.app_id,c.data_source,c.send_time,
cs.id, cs.signer_type,
cs.user_id,cs.name,
cs.enterprise_id,cs.enterprise_name

order by c.id desc ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ? ::: [2022, 2023, 2024, 2022, 2023, 2024, L53P00QVI8JNYE, 1, 2022-01-01 00:00:00.0, 2024-12-31 23:59:59.0, 2147483647, 0]

如果使用单表查询，ROWNUM 就是传入参数10

2025-03-13 13:18:33.015 INFO [ main] ShardingSphere-SQL [] : Actual SQL: ds ::: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( select
c.id as contract_id, c.name as contract_name, c.code as contract_code, c.sign_sortable, c.expired_time,c.create_time,c.enterprise_id,c.enterprise_name,c.creator,c.create_name,
c.app_id,c.data_source,c.send_time,
cs.id as signer_id, cs.signer_type as signer_user_type,
cs.user_id as signer_user_id,cs.name as signer_user_name,
cs.enterprise_id as signer_enterprise_id,cs.enterprise_name as signer_enterprise_name
from contract_2024 c left join contract_signer_2024 cs
on c.id = cs.contract_id
WHERE c.deleted = 0 and cs.deleted=0
and c.sharding_route in ( ? )
and cs.sharding_route in (?)
and cs.user_id = ?
and c.status in (1,2)
and cs.status = 2
and cs.signer_type = ?
and c.create_time >= to_date(?,'yyyy-mm-dd hh24:mi:ss')
and c.create_time <= to_date(?,'yyyy-mm-dd hh24:mi:ss')
group by
c.id , c.name , c.code , c.sign_sortable,c.expired_time,c.create_time,c.enterprise_id,c.enterprise_name,c.creator,c.create_name,
c.app_id,c.data_source,c.send_time,
cs.id, cs.signer_type, cs.user_id,cs.name, cs.enterprise_id,cs.enterprise_name

order by c.id desc ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ? ::: [2024, 2024, L53P00QVI8JNYE, 1, 2024-01-01 00:00:00.0, 2024-12-31 23:59:59.0, 10, 0]