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 2021/12/27 04:35:31 UTC

[GitHub] [shardingsphere] HarrySheep opened a new issue #14334: Oracle pagination and sorting doesn' t work properly

HarrySheep opened a new issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334


   ## 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:**
   ![image](https://user-images.githubusercontent.com/26380795/147433185-aadbb926-5486-4476-b644-42a1f22cf7b0.png)
   
   **Configuration:**
   ![image](https://user-images.githubusercontent.com/26380795/147434065-e98d6e6f-cc0b-45bc-992b-e2a6d099a1e8.png)
   
   **Sharding Algorithm:**
   Plz ignore it for now. It seems to have nothing to do with this issue.
   
   **Test Code:**
   ![image](https://user-images.githubusercontent.com/26380795/147434346-7913331f-1b38-4d45-9754-8421e48d1dcf.png)
   
   ![image](https://user-images.githubusercontent.com/26380795/147432959-85cb18e5-6731-417c-a92d-94144587c05a.png)
   
   **Mybatis Mapper:**
   ![image](https://user-images.githubusercontent.com/26380795/147432983-db9155de-123f-4727-ab08-2bf0861859d5.png)
   
   **Here's the result sets:**
   ![image](https://user-images.githubusercontent.com/26380795/147434695-1b9ecf6e-fb36-497c-a67b-7f045a3b03d9.png)
   
   **The SQL Log:**
   ![image](https://user-images.githubusercontent.com/26380795/147434138-3356b5d9-2f4f-476b-b696-b778917e2fb7.png)
   
   
   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.


-- 
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] xszhe edited a comment on issue #14334: Oracle pagination and sorting doesn' t work properly

Posted by GitBox <gi...@apache.org>.
xszhe edited a comment on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1023808235


   我也出现同样的问题,是因为
   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,导致无法识别。
   
   ![Snipaste_2022-01-28_09-29-51](https://user-images.githubusercontent.com/49973087/151471374-3dfc8763-e82c-46b7-901d-295c7f8c66d5.jpg)
   
   我通过对分页组件 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();
           }
       }
   `
   
   ![image](https://user-images.githubusercontent.com/49973087/151471309-82f961aa-c001-4a2c-a3ab-c12a8292eb33.png)
   
   执行的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.


-- 
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] xszhe edited a comment on issue #14334: Oracle pagination and sorting doesn' t work properly

Posted by GitBox <gi...@apache.org>.
xszhe edited a comment on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1023808235


   我也出现同样的问题,是因为
   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,导致无法识别。
   ![Snipaste_2022-01-28_09-29-51](https://user-images.githubusercontent.com/49973087/151471374-3dfc8763-e82c-46b7-901d-295c7f8c66d5.jpg)
   我通过对分页组件 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();
           }
       }
   `
   
   ![image](https://user-images.githubusercontent.com/49973087/151471309-82f961aa-c001-4a2c-a3ab-c12a8292eb33.png)
   
   执行的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
   
   测试可分页及正常排序。
   
   
   


-- 
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] xszhe edited a comment on issue #14334: Oracle pagination and sorting doesn' t work properly

Posted by GitBox <gi...@apache.org>.
xszhe edited a comment on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1023808235


   我也出现同样的问题,是因为
   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,导致无法识别。
   
   ![Snipaste_2022-01-28_09-29-51](https://user-images.githubusercontent.com/49973087/151471374-3dfc8763-e82c-46b7-901d-295c7f8c66d5.jpg)
   
   我通过对分页组件 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();
           }
       }
   `
   
   ![image](https://user-images.githubusercontent.com/49973087/151471309-82f961aa-c001-4a2c-a3ab-c12a8292eb33.png)
   
   执行的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
   
   测试可分页及正常排序。
   
   
   


-- 
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] xszhe commented on issue #14334: Oracle pagination and sorting doesn' t work properly

Posted by GitBox <gi...@apache.org>.
xszhe commented on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1023808235


   我也出现同样的问题,是因为
   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,对分页内子查询的order by 无法正常解析,导致无法识别
   ![Snipaste_2022-01-28_09-29-51](https://user-images.githubusercontent.com/49973087/151471374-3dfc8763-e82c-46b7-901d-295c7f8c66d5.jpg)
   我通过对分页组件 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();
           }
       }
   `
   
   ![image](https://user-images.githubusercontent.com/49973087/151471309-82f961aa-c001-4a2c-a3ab-c12a8292eb33.png)
   
   执行的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
   
   测试可分页及正常排序。
   
   
   


-- 
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] tuichenchuxin commented on issue #14334: Oracle pagination and sorting doesn' t work properly

Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1005330940


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


-- 
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