- Reversing a change that was made in 0.9, the "singleton" nature

of the "constants" :func:`.null`, :func:`.true`, and :func:`.false`
has been reverted.   These functions returning a "singleton" object
had the effect that different instances would be treated as the
same regardless of lexical use, which in particular would impact
the rendering of the columns clause of a SELECT statement.
fixes #3170

5 files changed, 69 insertions, 18 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 18742f81e..8351b5cce 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -22,6 +22,21 @@
     on compatibility concerns, see :doc:`/changelog/migration_10`.
 
     .. change::
+        :tags: bug, sql
+        :tickets: 3170
+
+        Reversing a change that was made in 0.9, the "singleton" nature
+        of the "constants" :func:`.null`, :func:`.true`, and :func:`.false`
+        has been reverted.   These functions returning a "singleton" object
+        had the effect that different instances would be treated as the
+        same regardless of lexical use, which in particular would impact
+        the rendering of the columns clause of a SELECT statement.
+
+        .. seealso::
+
+            :ref:`bug_3170`
+
+    .. change::
         :tags: bug, orm
         :tickets: 3139
 
diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst
index c025390d2..65a8d4431 100644
--- a/doc/build/changelog/migration_10.rst
+++ b/doc/build/changelog/migration_10.rst
@@ -789,6 +789,27 @@ would again fail; these have also been fixed.
 
 :ticket:`3148` :ticket:`3188`
 
+.. _bug_3170:
+
+null(), false() and true() constants are no longer singletons
+-------------------------------------------------------------
+
+These three constants were changed to return a "singleton" value
+in 0.9; unfortunately, that would lead to a query like the following
+to not render as expected::
+
+    select([null(), null()])
+
+rendering only ``SELECT NULL AS anon_1``, because the two :func:`.null`
+constructs would come out as the same  ``NULL`` object, and
+SQLAlchemy's Core model is based on object identity in order to
+determine lexical significance.    The change in 0.9 had no
+importance other than the desire to save on object overhead; in general,
+an unnamed construct needs to stay lexically unique so that it gets
+labeled uniquely.
+
+:ticket:`3170`
+
 .. _behavioral_changes_orm_10:
 
 Behavioral Changes - ORM
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 444273e67..4d5bb9476 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -1617,10 +1617,10 @@ class Null(ColumnElement):
         return type_api.NULLTYPE
 
     @classmethod
-    def _singleton(cls):
+    def _instance(cls):
         """Return a constant :class:`.Null` construct."""
 
-        return NULL
+        return Null()
 
     def compare(self, other):
         return isinstance(other, Null)
@@ -1641,11 +1641,11 @@ class False_(ColumnElement):
         return type_api.BOOLEANTYPE
 
     def _negate(self):
-        return TRUE
+        return True_()
 
     @classmethod
-    def _singleton(cls):
-        """Return a constant :class:`.False_` construct.
+    def _instance(cls):
+        """Return a :class:`.False_` construct.
 
         E.g.::
 
@@ -1679,7 +1679,7 @@ class False_(ColumnElement):
 
         """
 
-        return FALSE
+        return False_()
 
     def compare(self, other):
         return isinstance(other, False_)
@@ -1700,17 +1700,17 @@ class True_(ColumnElement):
         return type_api.BOOLEANTYPE
 
     def _negate(self):
-        return FALSE
+        return False_()
 
     @classmethod
     def _ifnone(cls, other):
         if other is None:
-            return cls._singleton()
+            return cls._instance()
         else:
             return other
 
     @classmethod
-    def _singleton(cls):
+    def _instance(cls):
         """Return a constant :class:`.True_` construct.
 
         E.g.::
@@ -1745,15 +1745,11 @@ class True_(ColumnElement):
 
         """
 
-        return TRUE
+        return True_()
 
     def compare(self, other):
         return isinstance(other, True_)
 
-NULL = Null()
-FALSE = False_()
-TRUE = True_()
-
 
 class ClauseList(ClauseElement):
     """Describe a list of clauses, separated by an operator.
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 2e10b7370..2ffc5468c 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -89,9 +89,9 @@ asc = public_factory(UnaryExpression._create_asc, ".expression.asc")
 desc = public_factory(UnaryExpression._create_desc, ".expression.desc")
 distinct = public_factory(
     UnaryExpression._create_distinct, ".expression.distinct")
-true = public_factory(True_._singleton, ".expression.true")
-false = public_factory(False_._singleton, ".expression.false")
-null = public_factory(Null._singleton, ".expression.null")
+true = public_factory(True_._instance, ".expression.true")
+false = public_factory(False_._instance, ".expression.false")
+null = public_factory(Null._instance, ".expression.null")
 join = public_factory(Join._create_join, ".expression.join")
 outerjoin = public_factory(Join._create_outerjoin, ".expression.outerjoin")
 insert = public_factory(Insert, ".expression.insert")
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 5c401845b..e8ad88511 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -1,4 +1,4 @@
-from sqlalchemy.testing import fixtures, eq_, is_
+from sqlalchemy.testing import fixtures, eq_, is_, is_not_
 from sqlalchemy import testing
 from sqlalchemy.testing import assert_raises_message
 from sqlalchemy.sql import column, desc, asc, literal, collate, null, true, false
@@ -778,6 +778,25 @@ class ConjunctionTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             "SELECT x WHERE NOT NULL"
         )
 
+    def test_constant_non_singleton(self):
+        is_not_(null(), null())
+        is_not_(false(), false())
+        is_not_(true(), true())
+
+    def test_constant_render_distinct(self):
+        self.assert_compile(
+            select([null(), null()]),
+            "SELECT NULL AS anon_1, NULL AS anon_2"
+        )
+        self.assert_compile(
+            select([true(), true()]),
+            "SELECT true AS anon_1, true AS anon_2"
+        )
+        self.assert_compile(
+            select([false(), false()]),
+            "SELECT false AS anon_1, false AS anon_2"
+        )
+
 
 class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = 'default'