An ordering is needed either explicitly, in an ORDER BY query, or implicitly by merge-based operators such as m_join, m_union_distinct, and group_sorted.
An ordering is produced either explicitly, by the sort abstract plan operator (the optimizer build the sort key on all columns known to need an ordering), or implicitly by an i_scan on the indexed columns.
All merge-based operators that require ordering preserve it in their results for a parent that also requires it.
In the following example, the i_scan of t1 provides the ordering needed by the m_join. The i_scan of t2, and the sort over t3’s scan, provides the ordering needed by m_union_distinct. This ordering also provides the ordering needed by the m_join. Finally, no top sort is required as the ordering needed by ORDER BY is provided by the m_join.
select * from t1, (select c21, c22 from t2 union distinct select c31, c32 from t3 ) u(u1, u2) where c11=u1 order by c11, u2 plan “(m_join (m_union_distinct (i_scan i_c21_c22 t2) (sort (t_scan t3) ) ) (i_scan i_c11 t1) )”
