Oracle pagination and sorting doesn' t work properly

[HarrySheep]

Question

What happens? I'm using sharding-jdbc springboot, trying to perform pagination (with PageHelper) with 2 sharding tables (in Oracle). The result sets seem like containing all the result I need, but they have not been sorted. So I wonder if i configure it wrong or what? I have searched for it for a long time, but I never found a proper solution.

Here's some configuration and the test code: Maven Dependency:

Configuration:

Sharding Algorithm: Plz ignore it for now. It seems to have nothing to do with this issue.

Test Code:

Mybatis Mapper:

Here's the result sets:

The SQL Log:

What it appears? And above all, we can see that it scanned through 2 tables, nfs_push_sent_message and nfs_push_sent_message_2021, and the sql did have the "order by STATUS_TIME desc" phase clearly. But still, the "order column" in result sets were not ordered.

If you need any further information, please contact me at any time. Thank for for taking a look at the question.

[tuichenchuxin]

From the actual sql, it seems have order clause. Can you try to offer a demo?

[xszhe]

我也出现同样的问题，是因为 D:/maven_bd/org/apache/shardingsphere/shardingsphere-jdbc-core/5.0.0/shardingsphere-jdbc-core-5.0.0.jar!/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSpherePreparedStatement.class:121

this.sqlStatement = sqlParserEngine.parse(sql, true); 这个语句无法正常解析分页组件组合的SQL，导致无法识别。

我通过对分页组件 com.github.pagehelper:pagehelper:5.3.0 的源码修改覆盖， 将 order by 的子查询提取到外部，让 sharding 能正常解析。

com.github.pagehelper.dialect.helper.OracleDialect

`

/**
 * 
 */
public String getPageSql(String sql, Page page, CacheKey pageKey) {
    StringBuilder sqlBuilder = new StringBuilder(sql.length() + 120);
    sqlBuilder.append("SELECT * FROM ( ");
    sqlBuilder.append(" SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM ( \n");
    sqlBuilder.append(sql);
    sqlBuilder.append("\n ) TMP_PAGE) TMP_PAGE_2");
    sqlBuilder.append(" WHERE TMP_PAGE_2.PAGEHELPER_ROW_ID <= ? AND TMP_PAGE_2.PAGEHELPER_ROW_ID > ?");
    addOrderBy(sql, sqlBuilder);
    return sqlBuilder.toString();
}

/**
 * add order by 
 *
 * @param sql
 * @param sqlBuilder
 */
private void addOrderBy(String sql, StringBuilder sqlBuilder) {
    CCJSqlParserManager parserManager = new CCJSqlParserManager();
    try {
        Statement statement = parserManager.parse(new StringReader(sql));
        if (statement instanceof Select) {
            Select select = (Select) statement;
            PlainSelect plain = (PlainSelect) select.getSelectBody();
            List<OrderByElement> orderByElements = plain.getOrderByElements();
            int index = 0;
            if (null != orderByElements && !orderByElements.isEmpty()) {
                for (OrderByElement orderByElement : orderByElements) {
                    if (null != orderByElement) {
                        if (0 == index) {
                            sqlBuilder.append(" ORDER BY ");
                        } else {
                            sqlBuilder.append(" ,");
                        }
                        sqlBuilder.append(orderByElement);
                        index++;
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

`

执行的sql是 SELECT FROM ( SELECT TMP_PAGE., ROWNUM PAGEHELPER_ROW_ID FROM ( SELECT * FROM SYS_LOG_12 WHERE ( ( log_type = ? ) ) order by log_date Desc ) TMP_PAGE) TMP_PAGE_2 WHERE TMP_PAGE_2.PAGEHELPER_ROW_ID <= ? AND TMP_PAGE_2.PAGEHELPER_ROW_ID > ? ORDER BY log_date DESC

测试可分页及正常排序。

希望能解决这个问题，有效识别oracle分页的 排序 功能。 Hope to solve this problem and effectively identify the sorting function of Oracle paging.

[xiajifei]

I have a same question;sql can't exe in Sharding; "SELECT FROM ( SELECT TMP_PAGE., ROWNUM PAGEHELPER_ROW_ID FROM ( select sys_user.id, sys_user.user_code, sys_user.user_name, sys_user.password, sys_user.phone_number,sys_user.email, sys_user.avatar, sys_user.is_manager, sys_user.open_id, sys_user.app_code, sys_user.remark, sys_user.create_time, sys_user.modify_time, sys_user.create_user_id,sys_user.modify_user_id, sys_user.is_delete,sys_user.card_no,sys_user.contacts , sys_user_app_rel.app_id as userAppRelAppId, sys_user_app_rel.user_type as userAppRelUserType, IFNULL(?,null) as appId from sys_user left join sys_user_app_rel on sys_user.id=sys_user_app_rel.user_id left join sys_user_corp_rel on sys_user.id=sys_user_corp_rel.user_id where 1=1 and sys_user.is_manager = 1 and sys_user.is_delete=0 and sys_user_app_rel.is_delete=0 /and upper(sys_user.user_code) != 'ADMIN'/ and sys_user_app_rel.app_id=? group by sys_user.user_code order by sys_user.create_time desc ) TMP_PAGE) WHERE PAGEHELPER_ROW_ID <= ? AND PAGEHELPER_ROW_ID > ?"

[terrymanu]

No plan to improve with oracle, I just set it as good amateur issue, does anyone want to fix it?

[xinglijun1973]

Can you post your Sharding Algorithm and PushSentMessage::getStatusTime? I'll try to repeat it.

[xinglijun1973]

No plan to improve with oracle, I just set it as good amateur issue, does anyone want to fix it? @terrymanu I'm almost done with it, please assign it to me.

[xinglijun1973]

if use oracle's rownun alias in where, it means use the "order by" exists in the rownum's from sub query. ex: select from (select t., rownum r from (select * from a order by a.name)t ) where r<10, we must add order by xxx when merging results although it is not in the outer statement.

[github-actions[bot]]

Hello , this issue has not received a reply for several days. This issue is supposed to be closed.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.