[Sqlalchemy-tickets] Issue #3862: strange compilation behavior (zzzeek/sqlalchemy)

[Антонио А. <iss...@bi...>]

New issue 3862: strange compilation behavior
https://bitbucket.org/zzzeek/sqlalchemy/issues/3862/strange-compilation-behavior

Антонио Антуан:

Hi
I have such model:

```
#!python
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:

```
#!python

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:


```
#!sql
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:
```
#!python
and_(
                        ~Transaction.is_from_leads,
                        ~Transaction.considered_in_balance,
                        Transaction.source_type != Transaction.Source.transactions
                    )
```


```
#!sql
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

```