rowid in an oracle query in constelation with lazy=False property

[Former user]

On the current trunk r1377 the following programm

from sqlalchemy import *
engine = create_engine('oracle://dsn=mydb&user=myuser&password=mypass')

content_type = Table('content_type', engine,
    Column('id', Integer, Sequence('content_type_seq'), primary_key=True),
    )

content = Table('content', engine,
    Column('id', Integer, Sequence('content_seq'), primary_key=True),
    Column('content_type_id', Integer, ForeignKey('content_type.id')), #when you shift column definition one down error goes away
    Column('name', String(255)), 
    )

entity = Table('entity', engine,
    Column('id', Integer, ForeignKey('content.id'), primary_key=True),
    )

class ContentType(object): pass
class Content(object): pass
class Entity(Content): pass

contenttypes = mapper(ContentType, content_type)
contents = mapper(Content, content)
contents.add_property('content_type', relation(contenttypes, lazy=False)) #remove the property or set lazy=True and the error goes away
entities = mapper(Entity, entity, inherits=contents)
entities.selectfirst() #do not use selectfirst and the error goes away

leads to this exception

Traceback (most recent call last):
  File "rowid_issue.py", line 26, in ?
    entities.selectfirst()
  File "build/bdist.linux-i686/egg/sqlalchemy/mapping/mapper.py", line 276, in selectfirst
  File "build/bdist.linux-i686/egg/sqlalchemy/mapping/query.py", line 103, in selectfirst
  File "build/bdist.linux-i686/egg/sqlalchemy/mapping/query.py", line 137, in select_whereclause
  File "build/bdist.linux-i686/egg/sqlalchemy/mapping/query.py", line 213, in _select_statement
  File "build/bdist.linux-i686/egg/sqlalchemy/sql.py", line 473, in execute
  File "build/bdist.linux-i686/egg/sqlalchemy/sql.py", line 378, in execute
  File "build/bdist.linux-i686/egg/sqlalchemy/sql.py", line 355, in execute
  File "build/bdist.linux-i686/egg/sqlalchemy/engine.py", line 646, in execute_compiled
  File "build/bdist.linux-i686/egg/sqlalchemy/engine.py", line 641, in proxy
  File "build/bdist.linux-i686/egg/sqlalchemy/engine.py", line 692, in execute
  File "build/bdist.linux-i686/egg/sqlalchemy/engine.py", line 712, in _execute
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-00904: "CONTENT_TYPE"."ROWID": invalid identifier
 'SELECT content_type_2f72_id, entity_id, content_id, content_name, content_content_type_id \nFROM (SELECT content_type_2f72.id AS content_type_2f72_id, entity.id AS entity_id, content.id AS content_id, content.name AS content_name, content.content_type_id AS content_content_type_id, ROW_NUMBER() OVER (ORDER BY content_type.rowid ASC) AS ora_rn \nFROM content JOIN entity ON content.id = entity.id LEFT OUTER JOIN content_type content_type_2f72 ON content_type_2f72.id = content.content_type_id) \nWHERE ora_rn<=1' {}

[Mike Bayer]

there is another solution to the test program above, which is to specify an "order_by" for both the Content and Entity mappers, i.e.

  mapper(Entity, entities, order_by=content.c.id)

the problem is that in the absense of an "order by" clause, the oracle module has to make a guess waht it can order by, since its implementation of LIMIT requires it.

I have checked in a change to two things: first, if you specify "order_by" to the Content mapper, the Entity mapper will pick up on it automatically. secondly, the Oracle module is using a different method to "guess" what column it should order by, if you didnt specify one. it seems to work for this test case and also passes the unit tests, not sure why I didnt do it that way in the first place (but perhaps we'll find out). changeset changeset:1379.

[Mike Bayer]

Removing milestone: 0.2.0 (automated comment)