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:42:19 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.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] terrymanu commented on issue #14334: Oracle pagination and sorting doesn' t work properly

Posted by GitBox <gi...@apache.org>.

terrymanu commented on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1164478834

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


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

Posted by GitBox <gi...@apache.org>.

github-actions[bot] commented on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1272350538

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

Posted by GitBox <gi...@apache.org>.

xinglijun1973 commented on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1220405377

   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.


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

Posted by GitBox <gi...@apache.org>.

xinglijun1973 commented on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1207429020

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


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

Posted by GitBox <gi...@apache.org>.

github-actions[bot] closed issue #14334: Oracle pagination and sorting doesn' t work properly
URL: https://github.com/apache/shardingsphere/issues/14334


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

Posted by GitBox <gi...@apache.org>.

xiajifei commented on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1162708452

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


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

Posted by GitBox <gi...@apache.org>.

xinglijun1973 commented on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1179730291

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


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

Re: [I] Oracle pagination and sorting doesn' t work properly [shardingsphere]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.

github-actions[bot] commented on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-2026022009

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


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