cx_Oracle ignores order by clause
<p>I've created complex query builder in my project, and during tests stumbled upon strange issue: same query with the same plan produces different results on different clients: cx_Oracle ignores order by clause, while Oracle SQLDeveloper Studio process query correctly, however in both cases order by present in both plans.</p>

<p>Query in question is:</p>

<pre><code>select *
from
(
    select 
        a.*,
        ROWNUM tmp__rnum
    from
    (
        select base.* 
        from
        (
            select id
            from
            (
                (
                    select 
                    profile_id as id,
                    surname as sort__col 
                    from names
                )
                /* here usually are several other subqueries chained by unions */
            )
            group by id
            order by min(sort__col) asc
        ) tmp
        left join (profiles) base
        on tmp.id = base.id
        where exists 
        (
            select t.object_id 
            from object_rights t 
            where
                    t.object_id = base.id 
                and t.subject_id = :a__subject_id 
                and t.rights in ('r','w') 
        )
    ) a 
    where ROWNUM &lt; :rows_to 
) 
where tmp__rnum &gt;= :rows_from
</code></pre>

<p>and plan from cx_Oracle in case I missed anything:</p>

<pre><code>{'operation': 'SELECT STATEMENT', 'position': 9225, 'cardinality': 2164, 'time': 1, 'cost': 9225, 'depth': 0, 'bytes': 84396, 'optimizer': 'ALL_ROWS', 'id': 0, 'cpu_cost': 1983805801},

    {'operation': 'VIEW', 'position': 1, 'filter_predicates': '"TMP__RNUM"&gt;=TO_NUMBER(:ROWS_FROM)', 'parent_id': 0, 'object_instance': 1, 'cardinality': 2164SEL$1', 'projection': '"from$_subquery$_001"."ID"[NUMBER,22], "from$_subquery$_001"."CREATION_TIME"[TIMESTAMP,11], "TMP__RNUM"[NUMBER,22]', 'time': 1, 'cost': 9225, 'depth': 1, 'bytes': 84396, 'id': 1, 'cpu_cost': 1983805801},

        {'operation': 'COUNT', 'position': 1, 'filter_predicates': 'ROWNUM&lt;TO_NUMBER(:ROWS_TO)', 'parent_id': 1, 'projection': '"BASE"."ID"[NUMBER,22], "BASE"."CREATION_TIME"[TIMESTAMP,11], ROWNUM[8]', 'options': 'STOPKEY', 'depth': 2, 'id': 2, 

            {'operation': 'HASH JOIN', 'position': 1, 'parent_id': 2, 'access_predicates': '"TMP"."ID"="BASE"."ID"', 'cardinality': 2164, 'projection': '(#keys=1) "BASE"."ID"[NUMBER,22], "BASE"."CREATION_TIME"[TIMESTAMP,11]', 'time': 1, 'cost': 9225, 'depth': 3, 'bytes': 86560, 'id': 3, 'cpu_cost': 1983805801},

                {'operation': 'JOIN FILTER', 'position': 1, 'parent_id': 3, 'object_owner': 'SYS', 'cardinality': 2219, 'projection': '"BASE"."ID"[NUMBER,22], "BASE"."CREATION_TIME"[TIMESTAMP,11]', 'object_name': ':BF0000', 'time': 1, 'cost': 662, 'options': 'CREATE', 'depth': 4, 'bytes': 59913, 'id': 4, 'cpu_cost': 223290732},

                    {'operation': 'HASH JOIN', 'position': 1, 'parent_id': 4, 'access_predicates': '"T"."OBJECT_ID"="BASE"."ID"', 'cardinality': 2219, 'projection': '(#keys=1) "BASE"."ID"[NUMBER,22], "BASE"."CREATION_TIME"[TIMESTAMP,11]', 'time': 1, 'cost': 662, 'options': 'RIGHT SEMI', 'depth': 5, 'bytes': 59913, 'id': 5, 'cpu_cost': 223290732},

                        {'operation': 'TABLE ACCESS', 'position': 1, 'filter_predicates': '"T"."SUBJECT_ID"=TO_NUMBER(:A__SUBJECT_ID) AND ("T"."RIGHTS"=\'r\' OR "T"."RIGHTS"=\'w\')', 'parent_id': 5, 'object_type': 'TABLE', 'object_instance': 8, 'cardinality': 2219, 'projection': '"T"."OBJECT_ID"[NUMBER,22]', 'object_name': 'OBJECT_RIGHTS', 'time': 1, 'cost': 5, 'options': 'FULL', 'depth': 6, 'bytes': 24409, 'optimizer': 'ANALYZED', 'id': 6, 'cpu_cost': 1823386},

                        {'operation': 'TABLE ACCESS', 'position': 2, 'parent_id': 5, 'object_type': 'TABLE', 'object_instance': 6, 'cardinality': 753862, 'projection': '"BASE"."ID"[NUMBER,22], "BASE"."CREATION_TIME"[TIMESTAMP,11]', 'object_name': 'PROFILES', 'time': 1, 'cost': 654, 'options': 'FULL', 'depth': 6, 'bytes': 12061792, 'optimizer': 'ANALYZED', 'id': 7, 'cpu_cost': 145148296},

                {'operation': 'VIEW', 'position': 2, 'parent_id': 3, 'object_instance': 3, 'cardinality': 735296, 'projection': '"TMP"."ID"[NUMBER,22]', 'time': 1, 'cost': 8559, 'depth': 4, 'bytes': 9558848, 'id': 8, 'cpu_cost': 1686052619},

                    {'operation': 'SORT', 'position': 1, 'parent_id': 8, 'cardinality': 735296, 'projection': '(#keys=1) MIN("SURNAME")[50], "PROFILE_ID"[NUMBER,22]', 'time': 1, 'cost': 8559, 'options': 'ORDER BY', 'temp_space': 18244000, 'depth': 5, 'bytes': 10294144, 'id': 9, 'cpu_cost': 1686052619},

                        {'operation': 'HASH', 'position': 1, 'parent_id': 9, 'cardinality': 735296, 'projection': '(#keys=1; rowset=200) "PROFILE_ID"[NUMBER,22], MIN("SURNAME")[50]', 'time': 1, 'cost': 8559, 'options': 'GROUP BY', 'temp_space': 18244000, 'depth': 6, 'bytes': 10294144, 'id': 10, 'cpu_cost': 1686052619},

                            {'operation': 'JOIN FILTER', 'position': 1, 'parent_id': 10, 'object_owner': 'SYS', 'cardinality': 756586, 'projection': '(rowset=200) "PROFILE_ID"[NUMBER,22], "SURNAME"[VARCHAR2,50]', 'object_name': ':BF0000', 'time': 1, 'cost': 1202, 'options': 'USE', 'depth': 7, 'bytes': 10592204, 'id': 11, 'cpu_cost': 190231639},

                                {'operation': 'TABLE ACCESS', 'position': 1, 'filter_predicates': 'SYS_OP_BLOOM_FILTER(:BF0000,"PROFILE_ID")', 'parent_id': 11, 'object_type': 'TABLE', 'object_instance': 5, 'cardinality': 756586, 'projection': '(rowset=200) "PROFILE_ID"[NUMBER,22], "SURNAME"[VARCHAR2,50]', 'object_name': 'NAMES', 'time': 1, 'cost': 1202, 'options': 'FULL', 'depth': 8, 'bytes': 10592204, 'optimizer': 'ANALYZED', 'id': 12, 'cpu_cost': 190231639}
</code></pre>

<p>cx_Oracle output (appears to be ordered by id):</p>

<pre><code>ID,    Created,    rownum
(1829, 2016-08-24, 1)
(2438, 2016-08-24, 2)
</code></pre>

<p>SQLDeveloper Output (ordered by surname, as expected):</p>

<pre><code>ID,      Created,    rownum
(518926, 2016-08-28, 1)
(565556, 2016-08-29, 2)
</code></pre>

<p>I don't see an <code>ORDER BY</code> clause that would affect the ordering of the results of the query.  In SQL, the only way to guarantee the ordering of a result set is to have an <code>ORDER BY</code> clause for the outer-most <code>SELECT</code>.</p>

<p>In almost all cases, an <code>ORDER BY</code> in a subquery is not necessarily respected (Oracle makes an exception when there are <code>rownum</code> comparisons in the next level of the query -- and even that is now out of date with the support of <code>FETCH FIRST &lt;n&gt; ROWS</code>).</p>

<p>So, there is no reason to expect that an <code>ORDER BY</code> in the <em>innermost</em> subquery would have any effect, particularly with the <code>JOIN</code> that then happens.</p>

<p>Suggestions:</p>

<ul>
<li>Move the <code>ORDER BY</code> to the outermost query.</li>
<li>Use <code>FETCH FIRST</code> syntax, if you are using Oracle 12c+.</li>
<li>Move the <code>ORDER BY</code> <em>after</em> the <code>JOIN</code>.</li>
<li>Use <code>ROW_NUMBER()</code> instead of <code>rownum</code>.</li>
</ul>

python sql oracle cx-oracle