hey Pete -
if you can provide a full example that runs, it would be helpful,
i.e. attach a script that has the partial table definitions, some
classes/mappers, and a "select()" that makes it acutally happen. i
can just replace the oracle connect string with something local here.
what is probably happening is a somewhat known issue where SA will
try to use a LIMIT when selecting just one instance, as it does in
the import_instance() method. Oracle "limits" are implemented using
ROW NUMBER OVER ORDER BY(something), so it adds "rowid" when it cant
find anything else to order by. In theory if you have "order by" set
up for things it shouldnt have to use rowid. I see below you have a
lot of "order by"s in there, but you probably need to specify the
"order by" on the mapper() definition itself, not just the relations
(in fact you might need it only on the mappers).
if that doesnt work, if you can send me a script to work with i can
narrow it down...at the very least I can have the oracle compiler not
allow "rowid" to be used if "DISTINCT" is turned on.
- mike
On Sep 6, 2006, at 10:14 AM, Pete Taylor wrote:
> Hi All,
> I'm running into a strange problem that I can't seem to constrain to a
> test case. I'm including the relevant pieces of the tables and
> relationships as reference for the following description of the
> problem. And, just for reference, I'm running SA from svn.
>
> As you can see below, the Endorsement object is mapped against a
> specific select statement on the endorsements table, with a set of
> relationships to the EndorsementDefinition and EndorsementLineItem
> objects. Endorsements themselves are children of Policy objects.
>
> The actual ORA error I'm seeing is:
> "(DatabaseError) ORA-01446: cannot select ROWID from view with
> DISTINCT, GROUP BY, etc."
>
> The select statement itself is too long to relate here, but the
> relevant piece is as follows:
> "ORDER BY distinct_ends_select.rowid,
> uhe_endorsement_7775.end_endorsement_type ASC,
> end_endorsement_3b96.end_endorsement_type ASC"
>
> When I've run into 01446 before, I've merely added an explict order_by
> to the relation, and this has taken care of it. But in this case,
> I've added order_by in each relationship, but the SQL is still being
> constructed with an attempt to order by rowid.
>
> There is a "however" to this whole thing though, and it's a strange
> one. If I build an Endorsement object explicitly, assigning all the
> relevant primary keys and then running a session.import_instance
> against the object, I get the above ORA error. However, if I just
> allow the Policy object to lazyload the endorsement objects, it
> retrieves them without any difficulty.
>
> Because the relationships seem to work in lazyload, and becaue I've
> already added all the "order_by" statements I can think to add, I'm
> not sure where to go next, or how to narrow the whole of it down to a
> test case. Any help, though, would be greatly appreciated. Thanks!
>
> The above references the following set of tables and relationships:
>
> policies = Table('pol_homepolicy_tbl', meta,
> Column('pol_policy_num', String(10), primary_key=True,
> key='policyNum'),
> Column('pol_policy_eff_date', DateTime, primary_key=True,
> key='effectiveDate'),
> # truncated
> ...
> )
>
> endorsements = Table('end_endorsement_tbl', meta,
> Column('end_policy_num', String(10),
> ForeignKey('pol_homepolicy_tbl.policyNum'), primary_key=True,
> key='policyNum'),
> Column('end_endorsement_type', String(20),
> ForeignKey('uhe_endorsement_tbl.type'), primary_key=True, key='type'),
> Column('end_endorsement_id', Integer, primary_key=True, key='id'),
> Column('end_policy_eff_date', DateTime,
> ForeignKey('pol_homepolicy_tbl.effectiveDate'), primary_key=True,
> key='policyEffDate'),
> Column('end_endorsement_edition_date', String(7),
> ForeignKey('uhe_endorsement_tbl.editionDate'), key='editionDate'),
> # truncated
> ...
> )
>
> endorsementDefinitions = Table('uhe_endorsement_tbl', meta,
> Column('end_endorsement_type', String(10), primary_key=True,
> key='type'),
> Column('uhe_effect_date', DateTime, primary_key=True,
> key='effectiveDate'),
> Column('end_endorsement_desc', String(100), key='description'),
> Column('end_endorsement_form_id', String(12), key='formNumber'),
> Column('r_end_ed_date', String(7), key='editionDate')
> )
>
> EndorsementDefinition.mapper = mapper(EndorsementDefinition,
> endorsementDefinitions)
>
> EndorsementLineItem.mapper = mapper(EndorsementLineItem, endorsements)
>
> distinct_ends_select = select(
> [
> endorsements.c.policyNum,
> endorsements.c.policyEffDate,
> endorsements.c.type,
> endorsements.c.editionDate
> ],
> order_by=[asc(endorsements.c.type)],
> distinct=True,
> ).alias('distinct_ends_select')
>
> Endorsement.mapper = mapper(Endorsement, distinct_ends_select,
> is_primary=True, properties=dict(
> definition = relation(EndorsementDefinition, lazy=False,
> private=False, order_by=asc(endorsementDefinitions.c.type),
> primaryjoin=and_(
>
> distinct_ends_select.c.end_endorsement_type==endorsementDefinitions.c.
> type,
>
> distinct_ends_select.c.end_endorsement_edition_date==endorsementDefini
> tions.c.editionDate,
> ),
> ),
> lineItems = relation(EndorsementLineItem, lazy=False,
> private=True, order_by=asc(endorsements.c.type),
> foreignkey=(
> endorsements.c.policyNum,
> endorsements.c.policyEffDate,
> endorsements.c.type,
> ),
> primaryjoin=and_(
>
> distinct_ends_select.c.end_policy_num==endorsements.c.policyNum,
>
> distinct_ends_select.c.end_policy_eff_date==endorsements.c.policyEffDa
> te,
>
> distinct_ends_select.c.end_endorsement_type==endorsements.c.type
> )
> )
> ))
>
> Policy.mapper = mapper(Policy, policies, extension=PolicyExtension(),
> properties=dict(
> endorsements = relation(Endorsement, lazy=True, private=True,
> order_by=asc(distinct_ends_select.c.end_endorsement_type)),
> # truncated
> ...
> ))
>
> --
> "All guilt is relative, loyalty counts, and never let your conscience
> be your guide."
> - Lucas Buck, American Gothic
>
> ----------------------------------------------------------------------
> ---
> Using Tomcat but need to do more? Need to support web services,
> security?
> Get stuff done quickly with pre-integrated technology to make your
> job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache
> Geronimo
> http://sel.as-us.falkag.net/sel?
> cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Sqlalchemy-users mailing list
> Sqlalchemy-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
