clarify docs regarding true()/false() constants, that these are not fully compatible on many backends

[Lev Panov]

Engine instance was created via

Code highlighting:
  {{{#!python
  engine = create_engine('mssql+pyodbc://user:[e-mail]/db?driver=SQL Server', echo=True)

}}}

When I do for example

Code highlighting:
  {{{#!python
  session.query(User).filter(or_(expression.false(), expression.true())).all()

}}}

Output:

2013-09-15 23:40:11,761 INFO sqlalchemy.engine.base.Engine SELECT [user](user).id AS user_id, [user](user).name AS user_name 
FROM [user](user) 
WHERE 0 OR 1
2013-09-15 23:40:11,761 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\orm\query.py", line 2249, in all
    return list(self)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\orm\query.py", line 2361, in __iter__
    return self._execute_and_instances(context)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\orm\query.py", line 2376, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\engine\base.py", line 661, in execute
    params)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\engine\base.py", line 762, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\engine\base.py", line 875, in _execute_context
    context)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\engine\base.py", line 1019, in _handle_dbapi_exception
    exc_info
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\util\compat.py", line 197, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\util\compat.py", line 190, in reraise
    raise value.with_traceback(tb)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\engine\base.py", line 868, in _execute_context
    context)
  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-amd64.egg\sqlalchemy\engine\default.py", line 372, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', "[42000](42000) [Microsoft](Microsoft)[SQL Server Driver](ODBC)[Server](SQL)An expression of non-boolean type specified in a context where a condition is expected, near 'OR'. (4145) (SQLExecDirectW)") 'SELECT [user](user).id AS user_id, [user](user).name AS user_name \nFROM [user](user) \nWHERE 0 OR 1' ()

[Mike Bayer]

SQL server doesn't have "true" or "false" constants so you can't refer to them in isolation of a comparison. otherwise, what SQL would you like the expression or_(true(), false()) to produce on SQL server ? There are of course hacks like "1==1" "1==0" and stuff like that but we prefer to do as few of those as possible (the only one we do is the IN () hack, which already confuses everyone).

[Lev Panov]

I'd not use such expressions at all, but that's the filter code which sqlalchemy-orm-tree extension produces (https://github.com/monetizeio/sqlalchemy-orm-tree). So I see two possible solutions for the problem with true()/false() in isolation of a comparison. The first one is to generate hacky constructions like you said (I don't think they will slow the things so much). The second one is to do expression analysys & simplifying in some cases (i.e. 'false OR someexpr' becomes 'someexpr', 'true AND someexpr' becomes 'someexpr' too, 'true OR someexpr' becomes '1=1 or someexpr', 'false AND someexpr' becomes '1=0'). I think the current behavior (generation of invalid sql code for sql server) is unacceptable!

[Mike Bayer]

unacceptable! OK well I'd like to see exactly why sqlalchemy-orm-tree generates this. I'm not sure how comfortable I am doing the collapsing. If someone says and_(x=='foo', y=='bar', false(), g==7), it should become....what exactly if not the "1=0" thing? Overall I think that an app which is trying to be database agnostic should not be using the true()/false() constants at all. I'm pretty sure SQLAlchemy Core or ORM doesn't spit these out anywhere, someone has to use them explicitly.

[Lev Panov]

my deeper research on why sqlalchemy-orm-tree generates this gives the following line of code https://github.com/monetizeio/sqlalchemy-orm-tree/blob/master/sqlalchemy_tree/manager/class_.py#L458

but i also want to know is there a workaround to make things work even with hacks (without modifying sqlalchemy-orm-tree's code, which is not so trivial) ?

[Mike Bayer]

OK, I may be reading this wrong but isn't that much more simply (and efficiently, no nesting) just or_(*[for arg in args](_filter_children_of_node_helper(arg))) ? it seems like it currently is spitting out a recursive or_(or_(or_ ... structure which will cause a recursion overflow if you go too far.

[Lev Panov]

nice, looks like it works just fine! I will send pull request with your enhancement to orm_tree repository on github soon

[Mike Bayer]

the true/false constants are a new thing overall and were to suit some usages on Postgresql where the exact expression "IS TRUE/FALSE" is needed. I'd prefer if people don't use these constructs otherwise, rather than trying to simulate their full effect on backends that don't support it.

[Mike Bayer]

actually this is a dupe of #2804. I might try to see if we can just coerce any non-compared boolean into "x == 1".

[Mike Bayer]

this is all completed, so that true/false work in as many cases as possible (with "IS" still being one that isn't going to work cross-platform at the moment). true/false will be factored out of and/or, or will flatten out other elements, and will also render as 1 = 1 or 0 = 1 on non-boolean backends, we'll see how that goes. If true/false aren't used then you never see their effects.

please see http://docs.sqlalchemy.org/en/latest/changelog/migration_09.html#improved-rendering-of-boolean-constants-null-constants-conjunctions.

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)