[Sqlalchemy-tickets] Issue #4250: Boolean Column Filter in MS SQL Server (zzzeek/sqlalchemy)

[Hei <iss...@bi...>]

New issue 4250: Boolean Column Filter in MS SQL Server
https://bitbucket.org/zzzeek/sqlalchemy/issues/4250/boolean-column-filter-in-ms-sql-server

Hei:

# Summary #

After upgrading sqlalchemy from 1.1.5 to 1.2.7 (the latest version), the SQL complied for MS SQL Server that has a boolean check in the where clause is changed from `select * from table where bool_flag = 1` to `select * from table where bool_flag`, and from`select * from table where bool_flag = 0` to `select * from table where not bool_flag`. The new complied SQL is not supported by MS SQL Server (I am testing on MS SQL Server 11.0), the error is `An expression of non-boolean type specified in a context where a condition is expected`.

# Test Case #

## Environment ##
* SQLAlchemy Version: 1.2.7
* Database: MS SQL Server 11.0
* Platform: Windows 7
* Python Version: 3.5

## Code ##

```
#!python

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    flag = Column(Boolean)


engine = create_engine('mssql+pyodbc://user:pass@host/db?driver=SQL Server', echo=True)
Session = sessionmaker(engine)

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

conn = engine.connect()

engine.execute(select([A.id]).where(A.flag)).fetchall()
engine.execute(select([A.id]).where(~A.flag)).fetchall()
```

## Result ##
Failed in SQLAlchemy 1.2.7

```
#!python
Traceback (most recent call last):
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'flag'. (4145) (SQLExecDirectW)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:/Users/Public/Quant/Workspace/python/datastore/test_query.py", line 26, in <module>
    engine.execute(select([A.id]).where(A.flag)).fetchall()
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 2075, in execute
    return connection.execute(statement, *multiparams, **params)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'flag'. (4145) (SQLExecDirectW)") [SQL: 'SELECT a.id \nFROM a \nWHERE a.flag'] (Background on this error at: http://sqlalche.me/e/f405)
```

The executed SQL are:
```
#!sql
SELECT a.id 
FROM a 
WHERE a.flag

SELECT a.id 
FROM a 
WHERE not a.flag
```

SQLAlchemy 1.1.5 works correctly without error, the executed SQL are:
```
#!sql
SELECT a.id 
FROM a 
WHERE a.flag = 1

SELECT a.id 
FROM a 
WHERE a.flag = 0
```

# Potential Cause #
It seems to be related to Issue #4061, treating the BIT in MS SQL Server as "native boolean". I am not very familiar with the SQLAlchemy code base, but I found something suspicious [here](https://bitbucket.org/zzzeek/sqlalchemy/src/31f80b9eaeb3c3435b7f6679b41e434478b1d11c/lib/sqlalchemy/sql/compiler.py?at=oracle_numeric&fileviewer=file-view-default#compiler.py-1015). For dialect that support native boolean, the `visit_istrue_unary_operator` and `visit_isfalse_unary_operator` compile the where clause to the `WHERE bool_column` and `WHERE NOT bool_column`, which are not supported by MS SQL Server.