SQL compilation broken in 0.6 for Oracle use_ansi=False

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:arc@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([* od_alias.c.saleprice)](func.sum(od_alias.c.qtyordered),
            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 
FROM orderdetails od__a 
WHERE 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 
FROM orders, orderdetails orderdetails_1 
WHERE orders.orderid = orderdetails_1.orderid(+)' {}

Note the " AND )" before "AS totalsale" is causing the missing expression database error.

Comments (3)