title: What does LISTAGG with ORDER BY NULL actually use as the order criteria?
body: <p>If I do</p>
  
  <pre><code>SELECT LISTAGG( COLUMN_VALUE ) WITHIN GROUP ( ORDER BY NULL )   AS OrderByNULL,
         LISTAGG( COLUMN_VALUE ) WITHIN GROUP ( ORDER BY 1 )      AS OrderByCONST,
         LISTAGG( COLUMN_VALUE ) WITHIN GROUP ( ORDER BY ROWNUM ) AS OrderByROWNUM
  FROM   TABLE( SYS.ODCIVARCHAR2LIST( '5', '222', '4' ) );
  </code></pre>
  
  <p>The output is:</p>
  
  <pre><code>ORDERBYNULL ORDERBYCONST ORDERBYROWNUM
  ----------- ------------ -------------
  222,4,5     222,4,5      5,222,4
  </code></pre>
  
  <p>The query appears to have done an alphanumerical sort when using <code>ORDER BY</code> with non-deterministic ordering (<code>NULL</code> or a constant) and has maintained the input order when using <code>ORDER BY ROWNUM</code> (deterministic).</p>
  
  <p>The <a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030" rel="noreferrer"><code>LISTAGG</code></a> documentation states that:</p>
  
  <blockquote>
    <p>The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering.</p>
  </blockquote>
  
  <p>And when looking at <a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174" rel="noreferrer">analytic functions</a> it states that:</p>
  
  <blockquote>
    <p>Whenever the order_by_clause results in identical values for multiple rows, the function behaves as follows: [...] For all other analytic functions, the result depends on the window specification. If you specify a logical window with the RANGE keyword, then the function returns the same result for each of the rows. If you specify a physical window with the ROWS keyword, then the result is nondeterministic.</p>
  </blockquote>
  
  <p>So as far as I can tell from the documentation a non-deterministic ordering is to be expected - however, the function gives a deterministic output based on an alphanumerical sort and not on the order in which the rows are processed (which is the <a href="https://stackoverflow.com/questions/25377014/what-does-this-mean-order-by-null">commonly held view</a>).</p>
  
  <p>This is different to the behaviour of other analytic functions (when using a physical window with the <code>ROWS</code> keyword):</p>
  
  <pre><code>SELECT LAST_VALUE( COLUMN_VALUE )
           OVER ( ORDER BY NULL   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
           AS BYNULL,
         LAST_VALUE( COLUMN_VALUE )
           OVER ( ORDER BY 1      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
           AS BYCONST,
         LAST_VALUE( COLUMN_VALUE )
           OVER ( ORDER BY ROWNUM ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
           AS BYROWNUM
  FROM   TABLE( SYS.ODCIVARCHAR2LIST( '5', '222', '4' ) );
  </code></pre>
  
  <p>Which gives a consistent output for all the different orderings:</p>
  
  <pre><code>BYNULL BYCONST BYROWNUM
  ------ ------- --------
  4      4       4
  4      4       4
  4      4       4
  </code></pre>
  
  <p><strong>Is there official documentation for how an ordering is applied when <code>LISTAGG</code> is supplied a non-deterministic ordering?</strong></p>
  
  <p>Note: The behaviour of <code>ORDER BY NULL</code> is commented on <a href="http://www.oracle-developer.net/display.php?id=515" rel="noreferrer">here</a> stating:</p>
  
  <blockquote>
    <p>In this example, the elements have been aggregated alphabetically, despite the NULL ordering clause. This appears to be the default behaviour when using a constant ORDER BY expression</p>
  </blockquote>
  
  <p>But this is just an comment on the behaviour in a non-Oracle site.</p>