WHERE ... WHERE ROWNUM <= :ROWNUM_1

[Former user]

Using SQlAlchemy 0.5.6 with cx_oracle, if I make a query like this (from Pylons):

items = session.query(Item).filter(Item.type=='A').limit(11).all()

The generated SQL looks something like this:

... WHERE type = :type_1 WHERE ROWNUM <= :ROWNUM_1 ...

I'd expect the ROWNUM <= :ROWNUM_1 clause to be with the rest of the other clauses.

Note: You must have a filter of some sort in addition to limit / offset.

[Former user]

My name is Jonathan Gardner [e-mail]. Please cc me with responses and requests for information.

[Mike Bayer]

Here's an example of what it produces:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Item(Base):
    __tablename__ = 'items'

    id = Column(Integer, primary_key=True)
    type=Column(String(50))

session = create_session()

q = session.query(Item).filter(Item.type=='A').limit(11)

oracle_dialect = create_engine('oracle://').dialect
print q.statement.compile(dialect=oracle_dialect)

produces:

SELECT id, type 
FROM (SELECT items.id AS id, items.type AS type 
FROM items 
WHERE items.type = :type_1) 
WHERE ROWNUM <= :ROWNUM_1

The rationale for this application of LIMIT can be found at http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html .

ROWNUM needs to be outside of the inner query so that predicates like ORDER BY, GROUP BY, HAVING, etc. work correctly - these are all evaluated after ROWNUM. If you don't specify ORDER BY or any of those other predicates, then technically you don't need the subquery. But usually it doesn't make sense to LIMIT a query without sorting.

The only case though is when attempting to limit a result when it is known that the total number of rows should be exactly one - where SQLA's ORM does this in the first() and one() methods. In those cases, I can see a potential argument for, well if there's no ORDER BY, GROUP BY, etc. and we expect the total result to be only one row, the subquery can be skipped - but the ORM would then have to pass some kind of hint down to the SQL compiler and its not really clear if this one case really merits that extra complexity - since if its a one row result the usage of a derived selectable shouldn't be expensive at all.

[Former user]

Thanks for the reply and analysis. I see the closing parentheses now. You are correct about putting ROWNUM outside of the inner query.

However, I am still seeing the same behavior. I am engaging our DBA to see what about the two different queries should give different results.

[Mike Bayer]

if you are seeing a query with two WHERE's and no parenthesis, would need to see how you are generating that. that would be a bug if the expression constructs are generating that.

[Former user]

It appears that the queries are perfectly good. The database should be producing the right results. I'm going to try some more debugging to see where the rows get lost.

[Former user]

I've identified where the statement is actually executed. It appears that it's not throwing any errors, although the query is never sent to the database. There is something wrong with cx_Oracle. I'll engage them for help.

I can't identify any problems with the SQLAlchemy code. Everything is working exactly as designed. You can go ahead and resolve this.