negated EXISTS result type not bool with sqlite dialect

[Sebastian Bank]

>>> from sqlalchemy import create_engine, select, exists, type_coerce, Boolean
>>> engine = create_engine('sqlite://')
>>> engine.scalar(select([exists([1])]))  # works
True
>>> engine.scalar(select([~exists([1])]))  # unexpected
0
>>> engine.scalar(select([type_coerce(~exists([1]), Boolean)]))  # expected
False

independent of dialect: can the extra parenthesis perhaps be saved?

>>> from sqlalchemy import text
>>> print(select([~exists([1])]))
SELECT NOT (EXISTS (SELECT 1))
>>> sql = 'SELECT NOT EXISTS (SELECT 1) AS b'  # proposed rendering
>>> engine.scalar(text(sql).columns(b=Boolean))
False

[Mike Bayer]

this is the bug:

>>> e1 = exists([1])
>>> e2 = ~exists([1])
>>> e1.type
Boolean()
>>> e2.type
NullType()

[Mike Bayer]

this is the patch:

diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 8256900..632e48c 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -472,6 +472,7 @@ class ClauseElement(Visitable):
         return UnaryExpression(
             self.self_group(against=operators.inv),
             operator=operators.inv,
+            type_=type_api.BOOLEANTYPE,
             negate=None)

     def __bool__(self):

however, it is at the core of ClauseElement and makes the assumption that "not (anything)" has to return a BOOLEAN. We can perhaps try to limit this by checking that the original return type is boolean, and even moving this logic to ColumnElement since I'm not sure what "not (thing that's not an expression)" even means. might make this 1.1.

[Mike Bayer]

• Fixed bug where the negation of an EXISTS expression would not be properly typed as boolean in the result, and also would fail to be anonymously aliased in a SELECT list as is the case with a non-negated EXISTS construct. fixes #3682

→ <<cset 07a4b6cbcda6>>

[Mike Bayer]

• Fixed bug where the negation of an EXISTS expression would not be properly typed as boolean in the result, and also would fail to be anonymously aliased in a SELECT list as is the case with a non-negated EXISTS construct. fixes #3682

(cherry picked from commit 07a4b6cbcda6e6ee6e67893c5a5d2fd01e5f125f)

→ <<cset dfe49c7b7f0c>>