An overhaul of expression handling for special symbols particularly

with conjunctions, e.g.
``None`` :func:`.expression.null` :func:`.expression.true`
:func:`.expression.false`, including consistency in rendering NULL
in conjunctions, "short-circuiting" of :func:`.and_` and :func:`.or_`
expressions which contain boolean constants, and rendering of
boolean constants and expressions as compared to "1" or "0" for backends
that don't feature ``true``/``false`` constants. [ticket:2804]

3 files changed, 265 insertions, 22 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index cd8ac2aef..fbb88924d 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -18,7 +18,7 @@ from sqlalchemy import Integer, String, MetaData, Table, Column, select, \
     literal, and_, null, type_coerce, alias, or_, literal_column,\
     Float, TIMESTAMP, Numeric, Date, Text, collate, union, except_,\
     intersect, union_all, Boolean, distinct, join, outerjoin, asc, desc,\
-    over, subquery, case
+    over, subquery, case, true
 import decimal
 from sqlalchemy.util import u
 from sqlalchemy import exc, sql, util, types, schema
@@ -272,9 +272,10 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "SELECT foo() AS foo_1"
         )
 
+        # this is native_boolean=False for default dialect
         self.assert_compile(
             select([not_(True)], use_labels=True),
-            "SELECT NOT :param_1"
+            "SELECT :param_1 = 0"
         )
 
         self.assert_compile(
@@ -874,6 +875,26 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "SELECT t.x FROM t WHERE t.x = :x_1 OR t.x = :x_2"
         )
 
+    def test_true_short_circuit(self):
+        t = table('t', column('x'))
+
+        self.assert_compile(
+            select([t]).where(true()),
+            "SELECT t.x FROM t WHERE 1 = 1",
+            dialect=default.DefaultDialect(supports_native_boolean=False)
+        )
+        self.assert_compile(
+            select([t]).where(true()),
+            "SELECT t.x FROM t WHERE true",
+            dialect=default.DefaultDialect(supports_native_boolean=True)
+        )
+
+        self.assert_compile(
+            select([t]),
+            "SELECT t.x FROM t",
+            dialect=default.DefaultDialect(supports_native_boolean=True)
+        )
+
     def test_distinct(self):
         self.assert_compile(
             select([table1.c.myid.distinct()]),
@@ -2921,6 +2942,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL):
                     "(:rem_id, :datatype_id, :value)")
 
 
+
 class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = 'default'
 
@@ -3250,13 +3272,34 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL):
         )
 
 class CoercionTest(fixtures.TestBase, AssertsCompiledSQL):
-    __dialect__ = 'default'
+    __dialect__ = default.DefaultDialect(supports_native_boolean=True)
 
     def _fixture(self):
         m = MetaData()
         return Table('foo', m,
             Column('id', Integer))
 
+    bool_table = table('t', column('x', Boolean))
+
+    def test_coerce_bool_where(self):
+        self.assert_compile(
+            select([self.bool_table]).where(self.bool_table.c.x),
+            "SELECT t.x FROM t WHERE t.x"
+        )
+
+    def test_coerce_bool_where_non_native(self):
+        self.assert_compile(
+            select([self.bool_table]).where(self.bool_table.c.x),
+            "SELECT t.x FROM t WHERE t.x = 1",
+            dialect=default.DefaultDialect(supports_native_boolean=False)
+        )
+
+        self.assert_compile(
+            select([self.bool_table]).where(~self.bool_table.c.x),
+            "SELECT t.x FROM t WHERE t.x = 0",
+            dialect=default.DefaultDialect(supports_native_boolean=False)
+        )
+
     def test_null_constant(self):
         self.assert_compile(_literal_as_text(None), "NULL")
 
@@ -3269,12 +3312,12 @@ class CoercionTest(fixtures.TestBase, AssertsCompiledSQL):
     def test_val_and_false(self):
         t = self._fixture()
         self.assert_compile(and_(t.c.id == 1, False),
-                            "foo.id = :id_1 AND false")
+                            "false")
 
     def test_val_and_true_coerced(self):
         t = self._fixture()
         self.assert_compile(and_(t.c.id == 1, True),
-                            "foo.id = :id_1 AND true")
+                            "foo.id = :id_1")
 
     def test_val_is_null_coerced(self):
         t = self._fixture()
@@ -3282,26 +3325,21 @@ class CoercionTest(fixtures.TestBase, AssertsCompiledSQL):
                             "foo.id IS NULL")
 
     def test_val_and_None(self):
-        # current convention is None in and_() or
-        # other clauselist is ignored.  May want
-        # to revise this at some point.
         t = self._fixture()
         self.assert_compile(and_(t.c.id == 1, None),
-                            "foo.id = :id_1")
+                            "foo.id = :id_1 AND NULL")
 
     def test_None_and_val(self):
-        # current convention is None in and_() or
-        # other clauselist is ignored.  May want
-        # to revise this at some point.
         t = self._fixture()
-        self.assert_compile(and_(t.c.id == 1, None),
-                            "foo.id = :id_1")
+        self.assert_compile(and_(None, t.c.id == 1),
+                            "NULL AND foo.id = :id_1")
 
     def test_None_and_nothing(self):
         # current convention is None in and_()
         # returns None May want
         # to revise this at some point.
-        assert and_(None) is None
+        self.assert_compile(
+            and_(None), "NULL")
 
     def test_val_and_null(self):
         t = self._fixture()
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 97ce3d3dd..0124d85fa 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -9,14 +9,18 @@ from sqlalchemy.sql import operators, table
 import operator
 from sqlalchemy import String, Integer
 from sqlalchemy import exc
+from sqlalchemy.engine import default
+from sqlalchemy.sql.elements import _literal_as_text
 from sqlalchemy.schema import Column, Table, MetaData
-from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType
+from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, Boolean
 from sqlalchemy.dialects import mysql, firebird, postgresql, oracle, \
         sqlite, mssql
 from sqlalchemy import util
 import datetime
 import collections
 from sqlalchemy import text, literal_column
+from sqlalchemy import and_, not_, between, or_
+from sqlalchemy.sql import true, false, null
 
 class LoopOperate(operators.ColumnOperators):
     def operate(self, op, *other, **kwargs):
@@ -35,11 +39,11 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
         left = column('left')
 
         assert left.comparator.operate(operator, right).compare(
-            BinaryExpression(left, right, operator)
+            BinaryExpression(_literal_as_text(left), _literal_as_text(right), operator)
         )
 
         assert operator(left, right).compare(
-            BinaryExpression(left, right, operator)
+            BinaryExpression(_literal_as_text(left), _literal_as_text(right), operator)
         )
 
         self._loop_test(operator, right)
@@ -384,7 +388,205 @@ class ExtensionOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             "x -> :x_1"
         )
 
-from sqlalchemy import and_, not_, between
+
+class BooleanEvalTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    """test standalone booleans being wrapped in an AsBoolean, as well
+    as true/false compilation."""
+
+    def _dialect(self, native_boolean):
+        d = default.DefaultDialect()
+        d.supports_native_boolean = native_boolean
+        return d
+
+    def test_one(self):
+        c = column('x', Boolean)
+        self.assert_compile(
+            select([c]).where(c),
+            "SELECT x WHERE x",
+            dialect=self._dialect(True)
+        )
+
+    def test_two(self):
+        c = column('x', Boolean)
+        self.assert_compile(
+            select([c]).where(c),
+            "SELECT x WHERE x = 1",
+            dialect=self._dialect(False)
+        )
+
+    def test_three(self):
+        c = column('x', Boolean)
+        self.assert_compile(
+            select([c]).where(~c),
+            "SELECT x WHERE x = 0",
+            dialect=self._dialect(False)
+        )
+
+    def test_four(self):
+        c = column('x', Boolean)
+        self.assert_compile(
+            select([c]).where(~c),
+            "SELECT x WHERE NOT x",
+            dialect=self._dialect(True)
+        )
+
+    def test_five(self):
+        c = column('x', Boolean)
+        self.assert_compile(
+            select([c]).having(c),
+            "SELECT x HAVING x = 1",
+            dialect=self._dialect(False)
+        )
+
+    def test_six(self):
+        self.assert_compile(
+            or_(false(), true()),
+            "1 = 1",
+            dialect=self._dialect(False)
+        )
+
+    def test_eight(self):
+        self.assert_compile(
+            and_(false(), true()),
+            "false",
+            dialect=self._dialect(True)
+        )
+
+    def test_nine(self):
+        self.assert_compile(
+            and_(false(), true()),
+            "0 = 1",
+            dialect=self._dialect(False)
+        )
+
+    def test_ten(self):
+        c = column('x', Boolean)
+        self.assert_compile(
+            c == 1,
+            "x = :x_1",
+            dialect=self._dialect(False)
+        )
+
+    def test_eleven(self):
+        c = column('x', Boolean)
+        self.assert_compile(
+            c.is_(true()),
+            "x IS true",
+            dialect=self._dialect(True)
+        )
+
+    def test_twelve(self):
+        c = column('x', Boolean)
+        # I don't have a solution for this one yet,
+        # other than adding some heavy-handed conditionals
+        # into compiler
+        self.assert_compile(
+            c.is_(true()),
+            "x IS 1",
+            dialect=self._dialect(False)
+        )
+
+
+class ConjunctionTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    """test interaction of and_()/or_() with boolean , null constants
+    """
+    __dialect__ = default.DefaultDialect(supports_native_boolean=True)
+
+    def test_one(self):
+        self.assert_compile(~and_(true()), "false")
+
+    def test_two(self):
+        self.assert_compile(or_(~and_(true())), "false")
+
+    def test_three(self):
+        self.assert_compile(or_(and_()), "")
+
+    def test_four(self):
+        x = column('x')
+        self.assert_compile(
+                and_(or_(x == 5), or_(x == 7)),
+                "x = :x_1 AND x = :x_2")
+
+
+    def test_five(self):
+        x = column("x")
+        self.assert_compile(
+            and_(true()._ifnone(None), x == 7),
+            "x = :x_1"
+        )
+
+    def test_six(self):
+        x = column("x")
+        self.assert_compile(or_(true(), x == 7), "true")
+        self.assert_compile(or_(x == 7, true()), "true")
+        self.assert_compile(~or_(x == 7, true()), "false")
+
+    def test_six_pt_five(self):
+        x = column("x")
+        self.assert_compile(select([x]).where(or_(x == 7, true())),
+                "SELECT x WHERE true")
+
+        self.assert_compile(select([x]).where(or_(x == 7, true())),
+                "SELECT x WHERE 1 = 1",
+                dialect=default.DefaultDialect(supports_native_boolean=False))
+
+    def test_seven(self):
+        x = column("x")
+        self.assert_compile(
+                and_(true(), x == 7, true(), x == 9),
+                "x = :x_1 AND x = :x_2")
+
+    def test_eight(self):
+        x = column("x")
+        self.assert_compile(
+                or_(false(), x == 7, false(), x == 9),
+                "x = :x_1 OR x = :x_2")
+
+    def test_nine(self):
+        x = column("x")
+        self.assert_compile(
+            and_(x == 7, x == 9, false(), x == 5),
+            "false"
+        )
+        self.assert_compile(
+            ~and_(x == 7, x == 9, false(), x == 5),
+            "true"
+        )
+
+    def test_ten(self):
+        self.assert_compile(
+            and_(None, None),
+            "NULL AND NULL"
+        )
+
+    def test_eleven(self):
+        x = column("x")
+        self.assert_compile(
+            select([x]).where(None).where(None),
+            "SELECT x WHERE NULL AND NULL"
+        )
+
+    def test_twelve(self):
+        x = column("x")
+        self.assert_compile(
+            select([x]).where(and_(None, None)),
+            "SELECT x WHERE NULL AND NULL"
+        )
+
+    def test_thirteen(self):
+        x = column("x")
+        self.assert_compile(
+            select([x]).where(~and_(None, None)),
+            "SELECT x WHERE NOT (NULL AND NULL)"
+        )
+
+    def test_fourteen(self):
+        x = column("x")
+        self.assert_compile(
+            select([x]).where(~null()),
+            "SELECT x WHERE NOT NULL"
+        )
+
 
 class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = 'default'
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index 13d4e378e..30a00ca56 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -1234,15 +1234,18 @@ class ExpressionTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiled
         )
         self.assert_compile(
             and_(c1 == True, c2 == True, c3 == True),
-            "x = :x_1 AND x = true AND x = :x_2"
+            "x = :x_1 AND x = true AND x = :x_2",
+            dialect=default.DefaultDialect(supports_native_boolean=True)
         )
         self.assert_compile(
             and_(c1 == 3, c2 == 3, c3 == 3),
-            "x = :x_1 AND x = :x_2 AND x = :x_3"
+            "x = :x_1 AND x = :x_2 AND x = :x_3",
+            dialect=default.DefaultDialect(supports_native_boolean=True)
         )
         self.assert_compile(
             and_(c1.is_(True), c2.is_(True), c3.is_(True)),
-            "x IS :x_1 AND x IS true AND x IS :x_2"
+            "x IS :x_1 AND x IS true AND x IS :x_2",
+            dialect=default.DefaultDialect(supports_native_boolean=True)
         )