Window functions in Oracle

[Schönfisch, Jörg]

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

[Lukas Eder]

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 <joerg.sc...@softplant.de>

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Schönfisch, Jörg]

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

[Lukas Eder]

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-order for 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 <joerg.sc...@softplant.de>