OK the issue here is entirely un-subtle and its a little weird that none of the
tests we have hit upon it. this is fixed in tip.
On May 3, 2010, at 12:06 PM, Kent Bower wrote:
> Note that this was ok in 0.5.8, so it must have been something related to
> 0.6...
>
> On 5/3/2010 12:04 PM, Michael Bayer wrote:
>> You can file a ticket for this but note that not everything is possible with
>> use_ansi=False. that mode of operation is somewhat miraculous that it even
>> exists. this may be a trivial issue but I cant assert that until I've had
>> time to study it.
>>
>>
>> On May 3, 2010, at 8:47 AM, Kent wrote:
>>
>>
>>> The following script works as expected (also in 0.5.8) with
>>> use_ansi=True. However, with use_ansi=False, the SQL is ill-formed:
>>>
>>> ===============================================================
>>> from sqlalchemy import *
>>> from sqlalchemy.orm import *
>>>
>>> engine = create_engine('oracle://arc:a...@localhost:1521/xe?
>>> use_ansi=False',echo=True)
>>> metadata = MetaData()
>>> Session = sessionmaker(bind=engine)
>>> session = Session()
>>>
>>> orders_table = Table("orders", metadata,
>>> Column("orderid", Unicode, primary_key=True)
>>> )
>>>
>>> orderdetails_table = Table("orderdetails",metadata,
>>> Column("orderid", Unicode, ForeignKey('orders.orderid'),
>>> primary_key=True),
>>> Column("lineid", Integer, primary_key=True),
>>> Column("saleprice", Numeric, nullable=False),
>>> Column("qtyordered",Numeric)
>>> )
>>>
>>> class Order(object):
>>> pass
>>>
>>> class OrderDetail(object):
>>> pass
>>>
>>> order_mapper = mapper(Order, orders_table,
>>> properties=dict(orderdetails=relation(OrderDetail,
>>> cascade='all,delete-orphan',
>>> single_parent=True,
>>> lazy=False,
>>> backref=backref('parentorder',
>>> cascade='refresh-expire,expunge'))))
>>>
>>> # ----------- totalsale -----------
>>> # note, I needed to add aliases because if you join with these tables
>>> in the rest of the query,
>>> # we need it to be un-ambiguous
>>> od_alias=orderdetails_table.alias('od__a')
>>> order_mapper.add_property('totalsale',
>>> # totalsale is an inline view column
>>> column_property(
>>> select([func.sum(od_alias.c.qtyordered *
>>> od_alias.c.saleprice)],
>>> orders_table.c.orderid==od_alias.c.orderid
>>> ).label('totalsale')))
>>>
>>>
>>> orderdetail_mapper = mapper(OrderDetail, orderdetails_table)
>>>
>>> #metadata.create_all(engine)
>>>
>>> o=session.query(Order).all()
>>> ===============================================================
>>>
>>>
>>>
>>> Expected SQL (use_ansi=True):
>>> -------------------------------------------
>>> 2010-04-30 21:09:10,359 INFO sqlalchemy.engine.base.Engine.0x...9450
>>> SELECT orders.orderid AS orders_orderid, (SELECT sum(od__a.qtyordered
>>> * od__a.saleprice) AS sum_1
>>> FROM orderdetails od__a
>>> WHERE orders.orderid = od__a.orderid) AS totalsale,
>>> orderdetails_1.orderid AS orderdetails_1_orderid,
>>> orderdetails_1.lineid AS orderdetails_1_lineid,
>>> orderdetails_1.saleprice AS orderdetails_1_saleprice,
>>> orderdetails_1.qtyordered AS orderdetails_1_qtyordered
>>> FROM orders LEFT OUTER JOIN orderdetails orderdetails_1 ON
>>> orders.orderid = orderdetails_1.orderid
>>> 2010-04-30 21:09:10,360 INFO sqlalchemy.engine.base.Engine.0x...9450
>>> {}
>>>
>>>
>>> SQL with use_ansi=False:
>>> ------------------------------------------
>>> sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00936: missing
>>> expression
>>> 'SELECT orders.orderid AS orders_orderid, (SELECT
>>> sum(od__a.qtyordered * od__a.saleprice) AS sum_1 \nFROM orderdetails
>>> od__a \nWHERE orders.orderid = od__a.orderid AND ) AS totalsale,
>>> orderdetails_1.orderid AS orderdetails_1_orderid,
>>> orderdetails_1.lineid AS orderdetails_1_lineid,
>>> orderdetails_1.saleprice AS orderdetails_1_saleprice,
>>> orderdetails_1.qtyordered AS orderdetails_1_qtyordered \nFROM orders,
>>> orderdetails orderdetails_1 \nWHERE orders.orderid =
>>> orderdetails_1.orderid(+)' {}
>>>
>>>
>>> Note the " AND )" before "AS totalsale" is causing the missing
>>> expression database error.
>>>
>>>
>>>
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> sqlalchemy+unsubscr...@googlegroups.com.
>>> For more options, visit this group at
>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>>
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
