wow, great ! a few months ago i could never fix these issues without
running people's test programs and bugfixing. guess we're getting
better !
On Sep 6, 2006, at 12:54 PM, Pete Taylor wrote:
> setting order_by on the mapper itself worked like a charm :)
>
> Thank you much!
>
> On 9/6/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
>> 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==endorsementDefi
>>> ni
>>> 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.policyEff
>>> Da
>>> 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
>>
>>
>
>
> --
> "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
