Subquery with order by clause should not push down to worker in some case.

When execute the follow sql.

select * from ( select tt.* from  ( select * from dt2 order by a) as tt  ) as t ;

Subquery with order by clause is push down to worker, cause the returning result is out of our expect.

postgres=# select * from  ( select tt.* from  ( select * from dt2 order by a) as tt  ) as t ;
 a | b 
---+---
 2 | 2           -- not sort by a
 1 | 1
 3 | 0
 4 | 4
 5 | 3
 7 | 9
(6 rows)

---

Details as follows：

-- create distributed table
create table dt2(a int, b int); 
select create_distributed_table('dt2','a');

-- insert some values ,and select distributed table dt2, result as follows:
postgres=# select * from dt2;
 a | b 
---+---
 1 | 1
 5 | 3
 7 | 9
 4 | 4
 3 | 0
 2 | 2
(6 rows)

-- order by distributed table dt2, result as follows:
postgres=# select * from dt2 order by a;
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 0
 4 | 4
 5 | 3
 7 | 9
(6 rows)
-- explain is:
postgres=# explain select * from dt2 order by a;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Sort  (cost=8304.82..8554.82 rows=100000 width=8)
   Sort Key: remote_scan.a
   ->  Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=100000 width=8)
         Task Count: 2
         Tasks Shown: All
         ->  Task
               Node: host=127.0.0.1 port=6001 dbname=postgres
               ->  Seq Scan on dt2_102040 dt2  (cost=0.00..32.60 rows=2260 width=8)
         ->  Task
               Node: host=127.0.0.1 port=6002 dbname=postgres
               ->  Seq Scan on dt2_102041 dt2  (cost=0.00..32.60 rows=2260 width=8)
(11 rows)
-- we execute the follow sql with order by in subquery.  result is error. not order by a.
postgres=# select * from  ( select tt.* from  ( select * from dt2 order by a) as tt  ) as t ;
 a | b 
---+---
 2 | 2
 1 | 1
 3 | 0
 4 | 4
 5 | 3
 7 | 9
(6 rows)
-- see the subquery with order by clause is push down to workers.
postgres=# explain select * from  ( select tt.* from ( select * from dt2 order by a) as tt  ) as t ;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=100000 width=8)
   Task Count: 2
   Tasks Shown: All
   ->  Task
         Node: host=127.0.0.1 port=6001 dbname=postgres
         ->  Sort  (cost=158.51..164.16 rows=2260 width=8)
               Sort Key: dt2.a
               ->  Seq Scan on dt2_102040 dt2  (cost=0.00..32.60 rows=2260 width=8)
   ->  Task
         Node: host=127.0.0.1 port=6002 dbname=postgres
         ->  Sort  (cost=158.51..164.16 rows=2260 width=8)
               Sort Key: dt2.a
               ->  Seq Scan on dt2_102041 dt2  (cost=0.00..32.60 rows=2260 width=8)
(13 rows)

the execute result is out of expect.

postgres=# insert into dt2 values (1,1),(5,3),(7,9),(4,4),(3,0),(2,2);
INSERT 0 6
postgres=# set citus.log_remote_commands=on;
SET
postgres=# select * from  ( select tt.* from  ( select * from dt2 order by a) as tt  ) as t ;
NOTICE:  issuing SELECT worker_column_1 AS a, worker_column_2 AS b FROM (SELECT t.a AS worker_column_1, t.b AS worker_column_2 FROM (SELECT tt.a, tt.b FROM (SELECT dt2.a, dt2.b FROM public.dt2_102040 dt2 ORDER BY dt2.a) tt) t) worker_subquery
DETAIL:  on server postgres@localhost:5433 connectionId: 1
NOTICE:  issuing SELECT worker_column_1 AS a, worker_column_2 AS b FROM (SELECT t.a AS worker_column_1, t.b AS worker_column_2 FROM (SELECT tt.a, tt.b FROM (SELECT dt2.a, dt2.b FROM public.dt2_102041 dt2 ORDER BY dt2.a) tt) t) worker_subquery
DETAIL:  on server postgres@localhost:5433 connectionId: 1
NOTICE:  issuing SELECT worker_column_1 AS a, worker_column_2 AS b FROM (SELECT t.a AS worker_column_1, t.b AS worker_column_2 FROM (SELECT tt.a, tt.b FROM (SELECT dt2.a, dt2.b FROM public.dt2_102042 dt2 ORDER BY dt2.a) tt) t) worker_subquery
DETAIL:  on server postgres@localhost:5433 connectionId: 1
NOTICE:  issuing SELECT worker_column_1 AS a, worker_column_2 AS b FROM (SELECT t.a AS worker_column_1, t.b AS worker_column_2 FROM (SELECT tt.a, tt.b FROM (SELECT dt2.a, dt2.b FROM public.dt2_102043 dt2 ORDER BY dt2.a) tt) t) worker_subquery
DETAIL:  on server postgres@localhost:5433 connectionId: 1
 a | b 
---+---
 1 | 1
 5 | 3
 3 | 0
 4 | 4
 7 | 9
 2 | 2
(6 rows)

postgres=#

postgres=# set citus.override_table_visibility TO off;
SET
postgres=# \d
            List of relations
 Schema |     Name     | Type  |  Owner   
--------+--------------+-------+----------
 public | citus_tables | view  | postgres
 public | dt2          | table | postgres
 public | dt2_102040   | table | postgres
 public | dt2_102041   | table | postgres
 public | dt2_102042   | table | postgres
 public | dt2_102043   | table | postgres
(6 rows)

postgres=# table dt2_102040;
 a | b 
---+---
 1 | 1
 5 | 3
(2 rows)

postgres=# table dt2_102041;
 a | b 
---+---
 7 | 9
 4 | 4
 3 | 0
(3 rows)

postgres=# table dt2_102042;
 a | b 
---+---
(0 rows)

postgres=# table dt2_102043;
 a | b 
---+---
 2 | 2
(1 row)

postgres=#

Here is only the internal sorting of each shard, the total result set is not

This behaviour is different from postgres in this specific case, though not strictly speaking a bug (or rather, the bug is in the query).

Per the SQL standard, FROM clause entries do not have a well-defined order, and may thus be arbitrarily reordered. PostgreSQL will also do this sometimes, though probably never for this trivial query, while Citus does.

To get sorted output, the outer query should have an ORDER BY.