Column property works in instance context, but produces bad SQL in class context.

[Former user]

Certain ''column_properties'' result in bad SQL code generation in class context, when correlated against the table of the mapper where the ''column_property'' is on.

To reproduce:

Run attached script.

I only checked 0.6.8.

[Mike Bayer]

I didn't notice this ticket since a milestone had been assigned, usually that's my job :)

A critical strategy to use when faced with issues like these is to watch the generated SQL. Turn on echo=True:

engine = create_engine("sqlite://", echo=True)

using that, we can see the SQL is incorrect as:

SELECT sum((SELECT transactions.net_amount * (? + vat_rates.vat_percentage) AS anon_1 
FROM vat_rates, orders 
WHERE transactions.order_id = orders.order_id AND orders.order_date > vat_rates.vat_validfrom AND (orders.order_date < vat_rates.vat_validuntil OR vat_rates.vat_validuntil IS NULL))) AS sum_1

See anything missing ? There's no "transactions" in the FROM clause. Why might this be ? Because you're selecting from a SELECT statement where you've explicitly told it not to put "transactions" in this clause:

    'gross_total': column_property(select(
         [ transactions.c.net_amount * (1 + vat_rates.c.vat_percentage) ],
         and_(
             transactions.c.order_id == orders.c.order_id,
             orders.c.order_date > vat_rates.c.vat_validfrom,
             or_(
                 orders.c.order_date < vat_rates.c.vat_validuntil,
                 vat_rates.c.vat_validuntil == None
            )
         )
       ).correlate(transactions).as_scalar()
    )

"correlate" means, "don't put 'transactions' in the FROM clause under any circumstances". You need to take that out:

    'gross_total': column_property(select(
         [ transactions.c.net_amount * (1 + vat_rates.c.vat_percentage) ],
         and_(
             transactions.c.order_id == orders.c.order_id,
             orders.c.order_date > vat_rates.c.vat_validfrom,
             or_(
                 orders.c.order_date < vat_rates.c.vat_validuntil,
                 vat_rates.c.vat_validuntil == None
            )
         )
       )as_scalar()
    )

query then auto-correlates as needed, or not - when the SQL expression is queried as-is, "transactions" is added to the FROM list normally:

SELECT sum((SELECT transactions.net_amount * (? + vat_rates.vat_percentage) AS anon_1 
FROM transactions, vat_rates, orders 
WHERE transactions.order_id = orders.order_id AND orders.order_date > vat_rates.vat_validfrom AND (orders.order_date < vat_rates.vat_validuntil OR vat_rates.vat_validuntil IS NULL))) AS sum_1

another technique to keep in mind, you can get a similar result ("transactions" on the outside) by setting the FROM clause on the query explicitly:

print Transaction.query.select_from(Transaction).value(func.sum(Transaction.gross_total))

result:

SELECT sum((SELECT transactions.net_amount * (? + vat_rates.vat_percentage) AS anon_1 
FROM vat_rates, orders 
WHERE transactions.order_id = orders.order_id AND orders.order_date > vat_rates.vat_validfrom AND (orders.order_date < vat_rates.vat_validuntil OR vat_rates.vat_validuntil IS NULL))) AS sum_1 
FROM transactions

[Mike Bayer]

Removing milestone: 0.6.9 (automated comment)