Hi Jörg, I’ve played around a bit with your examples and somehow I don’t get how the > ORDER BY is actually handled. If I specify an index, the sort order of the > result seems not to be effected at all.
Now, I remember why I hadn't fixed this yet :-) OK, so Oracle seems to consider the "1" in OVER(ORDER BY 1) as a constant expression, similar as in OVER(PARTITION BY 1). In fact, most databases do this. So the ordering is actually completely random - but not necessarily corresponding to the surrounding query's ORDER BY clause, as shown by my SQL Fiddle examples. My claims, however, were correct for CUBRID and Sybase SQL Anywhere, which "cleverly" implemented the possibility of referencing column indexes from the surrounding query's projection. I recently objected to this feature at CUBRID: http://jira.cubrid.org/browse/ENGINE-85 In the execution plan anything else than a column name in the ORDER BY > clause is translated to ORDER BY NULL. Maybe the value is not treated as > index but as a constant? Even ORDER BY 'a' seems valid and is translated to > NULL. But then, what would be the sense of an ORDER BY CONSTANT?... The main point is that SQL result sets have no explicit order, which is why you have to issue an ORDER BY clause if you want to rely on any given order. Since the SELECT's ORDER BY clause is evaluated only *after* the projection (and in fact, after UNIONs, see http://www.jooq.org/doc/3.0/manual/sql-building/sql-statements/select-statement/select-lexical-vs-logical-orderfor details), there is no "implicit" ORDER BY clause available at "projection time". So leaving out the ORDER BY clause in ROW_NUMBER or in ranking functions is always bad practice, as the outcome is not defined = unpredictable. In chapter "§6.10 <window function>" The SQL:2003 standard says that the <window ordering clause> is mandatory for RANK and DENSE_RANK. Further down, it also contains this cryptic text here: 193) Specifications for Feature T612, “Advanced OLAP operations”: b) Subclause 6.10, “<window function>”: iii) Without Feature T612, “Advanced OLAP operations”, conforming SQL language shall not contain a <window function> that simply contains ROW_NUMBER and immediately contains a <window name or specification> whose window structure descriptor does not contain a window ordering clause. Feel free to dig a little further in the specs if you want to get behind this syntax "feature" ;-) Cheers Lukas 2013/3/6 Schönfisch, Jörg <[email protected]> > Hi Lukas,**** > > ** ** > > I didn’t think about looking in the open issues, sorry…**** > > ** ** > > I’ve played around a bit with your examples and somehow I don’t get how > the ORDER BY is actually handled. If I specify an index, the sort order of > the result seems not to be effected at all.**** > > I can even specify a negative index or one which should be too large and > the query still executes fine.**** > > Postgres: http://sqlfiddle.com/#!12/d41d8/556**** > > Oracle: http://sqlfiddle.com/#!4/d41d8/8513**** > > ** ** > > In the execution plan anything else than a column name in the ORDER BY > clause is translated to ORDER BY NULL. Maybe the value is not treated as > index but as a constant? Even ORDER BY 'a' seems valid and is translated to > NULL. But then, what would be the sense of an ORDER BY CONSTANT?...**** > > ** ** > > Cheers,**** > > Jörg**** > > ** ** > > ** ** > > ** ** > > *Von:* [email protected] [mailto:[email protected]] *Im > Auftrag von *Lukas Eder > *Gesendet:* Dienstag, 5. März 2013 19:38 > *An:* [email protected] > *Betreff:* Re: Window functions in Oracle**** > > ** ** > > Hi Jörg,**** > > ** ** > > Yes, this is a well-known issue:**** > > https://github.com/jOOQ/jOOQ/issues/1535**** > > ** ** > > In the mean time, you can work around this issue by explicitly specifying > an ORDER BY clause to your ranking function.**** > > ** ** > > Unfortunately, I hadn't found a reliable, database-agnostic way to > simulate OVER() in oracle. Contrary to your suggestion, OVER() and > OVER(ORDER BY 1) are not the same.**** > > ** ** > > - OVER(): Assign ranking values ordered by the query's ORDER BY clause (or > implicitly by "ROWNUM" in Oracle)**** > > - OVER(ORDER BY 1): Assign ranking values ordered by the first column from > the projection**** > > ** ** > > Examples:**** > > ** ** > > -- Postgres**** > > with d(a, b) as (**** > > select 1, 2 union all**** > > select 3, 4 union all**** > > select 5, 6**** > > )**** > > select d.a, d.b, *row_number() over()***** > > from d**** > > order by a desc**** > > -- Results:**** > > -- http://sqlfiddle.com/#!12/d41d8/549**** > > ** ** > > -- Oracle 1**** > > with d(a, b) as (**** > > select 1, 2 from dual union all**** > > select 3, 4 from dual union all**** > > select 5, 6 from dual**** > > )**** > > select d.a, d.b, *row_number() over(order by rownum)***** > > from d**** > > order by a desc**** > > -- Results:**** > > -- http://sqlfiddle.com/#!4/d41d8/8475**** > > ** ** > > -- Oracle 2**** > > with d(a, b) as (**** > > select 1, 2 from dual union all**** > > select 3, 4 from dual union all**** > > select 5, 6 from dual**** > > )**** > > select d.a, d.b, *row_number() over(order by 1)***** > > from d**** > > order by a desc**** > > -- Results:**** > > -- http://sqlfiddle.com/#!4/d41d8/8476**** > > > I'm not 100% sure yet, whether ORDER BY ROWNUM is correct. Optionally, > jOOQ could introspect the "surrounding" query's ORDER BY clause. Any > further input is welcome!**** > > ** ** > > Cheers**** > > Lukas**** > > ** ** > > ** ** > > 2013/3/5 Schönfisch, Jörg <[email protected]>**** > > Hi,**** > > **** > > I have encountered a problem using OVER() in an Oracle database. I am > adding two fields to a SelectQuery like this: **** > > Select.add(Factory.count().over(), Factory.rowNumber().over());**** > > **** > > This translates to **** > > SELECT count(*) OVER (), FROM …, **** > > which gives me an error about a missing ORDER BY in the OVER clause. In > Postgres, this works without problems.**** > > **** > > IMHO, jOOQ should expand the OVER() for Oracle to OVER(ORDER BY 1). Is > this possible?**** > > **** > > **** > > Cheers,**** > > Jörg**** > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > **** > > ** ** > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > **** > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
