strange compilation behavior

[Антонио Антуан]

Hi I have such model:

class Transaction(BaseMixin, Base, FlagsMixin):
    __tablename__ = 'transactions'

    Source = _TransactionSources()

    id = Column(Integer, primary_key=True, nullable=False, autoincrement=True, index=True)
    ts_spawn = Column(Integer, nullable=False)
    user_id = Column(Integer, nullable=False, default=0, index=True)
    source_type = Column(SQLEnum(*Source.values(), name='transaction_sources'), index=True, nullable=False)

    @hybrid_property
    def is_from_leads(self):
        return self.source_type in (self.Source.buyouts, self.Source.orders, self.Source.leads)

    @is_from_leads.expression
    def is_from_leads(self):
        return and_(self.source_type != None,
                    self.source_type.in_([self.Source.buyouts, self.Source.orders, self.Source.leads]))

    @hybrid_property
    def considered_in_balance(self):
        return self.source_type in (Transaction.Source.compensations, Transaction.Source.balance_correcting,
                                    Transaction.Source.recalculations)

    @considered_in_balance.expression
    def considered_in_balance(self):
        return self.source_type.in_([Transaction.Source.compensations, Transaction.Source.balance_correcting,
                                     Transaction.Source.recalculations])

    @hybrid_method
    def exist_flag(self, flag):
        if not self.flags or not flag:
            return False
        return self.flags & flag == flag

    @exist_flag.expression
    def exist_flag(self, flag):
        return and_(flag != 0, self.flags != 0, self.flags.op('&')(flag) != 0)


class _TransactionSources(Enum):
    def __init__(self, **kwargs):
        super(_TransactionSources, self).__init__(**kwargs)
        self.orders = 'orders'
        self.leads = 'leads'
        self.transactions = 'transactions'
        self.compensations = 'compensations'
        self.invoices = 'invoices'
        self.balance_correcting = 'balance_correcting'
        self.translations = 'translations'
        self.recalculations = 'recalculations'

And such query:

Session.query(Transaction.user_id,
                             func.sum(Transaction.amount).label('sums')) \
            .filter(
                Transaction.ts_spawn > 1447701600,
                Transaction.user_id > 0,
                Transaction.state == Transaction.State.finished,
                or_(
                    and_(
                        Transaction.is_from_leads == False,
                        Transaction.considered_in_balance == False,
                        Transaction.source_type != Transaction.Source.transactions
                    ),
                    Transaction.exist_flag(Transaction.Flags.revoking)
                ))\
            .group_by(Transaction.user_id)

Compiled SQL query looks like this:

SELECT transactions.user_id, sum(transactions.amount) AS sums 
FROM transactions 
WHERE transactions.ts_spawn > 1447701600 AND transactions.user_id > 0 AND transactions.state = 'finished' AND (false OR transactions.flags != 0 AND (transactions.flags & 2) != 0) GROUP BY transactions.user_id

Why does compiler consider and_( Transaction.is_from_leads == False, Transaction.considered_in_balance == False, Transaction.source_type != Transaction.Source.transactions ) as false?

If I use such condition, compiled query looks right:

and_(
                        ~Transaction.is_from_leads,
                        ~Transaction.considered_in_balance,
                        Transaction.source_type != Transaction.Source.transactions
                    )

SELECT transactions.user_id, sum(transactions.amount) AS sums 
FROM transactions 
WHERE transactions.ts_spawn > 1447701600 AND transactions.user_id > 0 AND transactions.state = 'finished' AND (NOT (transactions.source_type IS NOT NULL AND transactions.source_type IN ('buyouts', 'orders', 'leads')) AND transactions.source_type NOT IN ('compensations', 'balance_correcting', 'recalculations') AND transactions.source_type != 'transactions' OR transactions.flags != 0 AND (transactions.flags & 2) != 0) GROUP BY transactions.user_id

[Mike Bayer]

this is a usage question. Something in your expression is doing an == comparison between two things where either neither of them is a sqlalchemy.sql.ColumnElement, or the left side implements an __eq__() method that is breaking things. I don't know what a SQLEnum is but that would be one place to look, I'm not able to run this because it relies upon many third party imports. I would advise doing each "x == y" at the pdb commandline individually to see which one produces "False", rather than a ColumnElement subclass. Pick this up on the mailing list at https://groups.google.com/forum/#!forum/sqlalchemy.