[GitHub] [shardingsphere] strongduanmu commented on issue #22480: In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is incorrect

[GitBox]

strongduanmu commented on issue #22480:
URL: 
https://github.com/apache/shardingsphere/issues/22480#issuecomment-1328742748

   I have found that view expander has lost the order by segment in view 
definition.
   
   <img width="708" alt="image" 
src="https://user-images.githubusercontent.com/10829171/204235141-8f4a0eb0-c3e8-4613-8ac1-1c5a2becba0f.png";>
   
   The following is the detailed logic.
   
   ```java
     /**
      * Implementation of {@link #convertSelect(SqlSelect, boolean)};
      * derived class may override.
      */
     protected void convertSelectImpl(
         final Blackboard bb,
         SqlSelect select) {
       convertFrom(
           bb,
           select.getFrom());
   
       // We would like to remove ORDER BY clause from an expanded view, except 
if
       // it is top-level or affects semantics.
       //
       // Top-level example. Given the view definition
       //   CREATE VIEW v AS SELECT * FROM t ORDER BY x
       // we would retain the view's ORDER BY in
       //   SELECT * FROM v
       // or
       //   SELECT * FROM v WHERE y = 5
       // but remove the view's ORDER BY in
       //   SELECT * FROM v ORDER BY z
       // and
       //   SELECT deptno, COUNT(*) FROM v GROUP BY deptno
       // because the ORDER BY and GROUP BY mean that the view is not 'top 
level' in
       // the query.
       //
       // Semantics example. Given the view definition
       //   CREATE VIEW v2 AS SELECT * FROM t ORDER BY x LIMIT 10
       // we would never remove the ORDER BY, because "ORDER BY ... LIMIT" is 
about
       // semantics. It is not a 'pure order'.
       if (RelOptUtil.isPureOrder(castNonNull(bb.root))
           && config.isRemoveSortInSubQuery()) {
         // Remove the Sort if the view is at the top level. Also remove the 
Sort
         // if there are other nodes, which will cause the view to be in the
         // sub-query.
         if (!bb.top
             || validator().isAggregate(select)
             || select.isDistinct()
             || select.hasOrderBy()
             || select.getFetch() != null
             || select.getOffset() != null) {
           bb.setRoot(castNonNull(bb.root).getInput(0), true);
         }
       }
   ```
   


-- 
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-unsubscr...@shardingsphere.apache.org

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