Apply underscore naming to several more operators

The operator changes are:

* `isfalse` is now `is_false`
* `isnot_distinct_from` is now `is_not_distinct_from`
* `istrue` is now `is_true`
* `notbetween` is now `not_between`
* `notcontains` is now `not_contains`
* `notendswith` is now `not_endswith`
* `notilike` is now `not_ilike`
* `notlike` is now `not_like`
* `notmatch` is now `not_match`
* `notstartswith` is now `not_startswith`
* `nullsfirst` is now `nulls_first`
* `nullslast` is now `nulls_last`

Because these are core operators, the internal migration strategy for this
change is to support legacy terms for an extended period of time -- if not
indefinitely -- but update all documentation, tutorials, and internal usage
to the new terms.  The new terms are used to define the functions, and
the legacy terms have been deprecated into aliases of the new terms.

Fixes: #5435
Change-Id: Ifbd7cb1cdda5981990243c4fc4b4ff467dc132ac

4 files changed, 164 insertions, 53 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index c9e1d9ab4..d3f8b6a9f 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -1697,7 +1697,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(
             table2.select().order_by(
                 table2.c.otherid,
-                table2.c.othername.desc().nullsfirst(),
+                table2.c.othername.desc().nulls_first(),
             ),
             "SELECT myothertable.otherid, myothertable.othername FROM "
             "myothertable ORDER BY myothertable.otherid, "
@@ -1707,7 +1707,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(
             table2.select().order_by(
                 table2.c.otherid,
-                table2.c.othername.desc().nullslast(),
+                table2.c.othername.desc().nulls_last(),
             ),
             "SELECT myothertable.otherid, myothertable.othername FROM "
             "myothertable ORDER BY myothertable.otherid, "
@@ -1716,8 +1716,8 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
 
         self.assert_compile(
             table2.select().order_by(
-                table2.c.otherid.nullslast(),
-                table2.c.othername.desc().nullsfirst(),
+                table2.c.otherid.nulls_last(),
+                table2.c.othername.desc().nulls_first(),
             ),
             "SELECT myothertable.otherid, myothertable.othername FROM "
             "myothertable ORDER BY myothertable.otherid NULLS LAST, "
@@ -1726,7 +1726,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
 
         self.assert_compile(
             table2.select().order_by(
-                table2.c.otherid.nullsfirst(),
+                table2.c.otherid.nulls_first(),
                 table2.c.othername.desc(),
             ),
             "SELECT myothertable.otherid, myothertable.othername FROM "
@@ -1736,8 +1736,8 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
 
         self.assert_compile(
             table2.select().order_by(
-                table2.c.otherid.nullsfirst(),
-                table2.c.othername.desc().nullslast(),
+                table2.c.otherid.nulls_first(),
+                table2.c.othername.desc().nulls_last(),
             ),
             "SELECT myothertable.otherid, myothertable.othername FROM "
             "myothertable ORDER BY myothertable.otherid NULLS FIRST, "
diff --git a/test/sql/test_deprecations.py b/test/sql/test_deprecations.py
index d566a1f6b..5f86b0b89 100644
--- a/test/sql/test_deprecations.py
+++ b/test/sql/test_deprecations.py
@@ -47,6 +47,7 @@ from sqlalchemy.testing import engines
 from sqlalchemy.testing import eq_
 from sqlalchemy.testing import fixtures
 from sqlalchemy.testing import in_
+from sqlalchemy.testing import is_
 from sqlalchemy.testing import is_true
 from sqlalchemy.testing import mock
 from sqlalchemy.testing import not_in
@@ -2109,6 +2110,24 @@ class TableDeprecationTest(fixtures.TestBase):
 
 
 class LegacyOperatorTest(AssertsCompiledSQL, fixtures.TestBase):
+    """
+    Several operators were renamed for SqlAlchemy 2.0 in #5429 and #5435
+
+    This test class is designed to ensure the deprecated legacy operators
+    are still available and equivalent to their modern replacements.
+
+    These tests should be removed when the legacy operators are removed.
+
+    Note: Although several of these tests simply check to see if two functions
+    are the same, some platforms in the test matrix require an `==` comparison
+    and will fail on an `is` comparison.
+
+    .. seealso::
+
+        :ref:`change_5429`
+        :ref:`change_5435`
+    """
+
     __dialect__ = "default"
 
     def test_issue_5429_compile(self):
@@ -2123,27 +2142,19 @@ class LegacyOperatorTest(AssertsCompiledSQL, fixtures.TestBase):
         # is_not
         assert hasattr(operators, "is_not")  # modern
         assert hasattr(operators, "isnot")  # legacy
-        assert operators.is_not is operators.isnot
+        is_(operators.is_not, operators.isnot)
         # not_in
         assert hasattr(operators, "not_in_op")  # modern
         assert hasattr(operators, "notin_op")  # legacy
-        assert operators.not_in_op is operators.notin_op
+        is_(operators.not_in_op, operators.notin_op)
 
         # precedence mapping
+        # since they are the same item, only 1 precedence check needed
         # is_not
-        assert operators.is_not in operators._PRECEDENCE  # modern
         assert operators.isnot in operators._PRECEDENCE  # legacy
-        assert (
-            operators._PRECEDENCE[operators.is_not]
-            == operators._PRECEDENCE[operators.isnot]
-        )
+
         # not_in_op
-        assert operators.not_in_op in operators._PRECEDENCE  # modern
         assert operators.notin_op in operators._PRECEDENCE  # legacy
-        assert (
-            operators._PRECEDENCE[operators.not_in_op]
-            == operators._PRECEDENCE[operators.notin_op]
-        )
 
         # ColumnOperators
         # is_not
@@ -2168,8 +2179,101 @@ class LegacyOperatorTest(AssertsCompiledSQL, fixtures.TestBase):
         # is_not
         assert hasattr(assertions, "is_not")  # modern
         assert hasattr(assertions, "is_not_")  # legacy
-        assert assertions.is_not is assertions.is_not_
+        assert assertions.is_not == assertions.is_not_
         # not_in
         assert hasattr(assertions, "not_in")  # modern
         assert hasattr(assertions, "not_in_")  # legacy
-        assert assertions.not_in is assertions.not_in_
+        assert assertions.not_in == assertions.not_in_
+
+    @testing.combinations(
+        (
+            "is_not_distinct_from",
+            "isnot_distinct_from",
+            "a IS NOT DISTINCT FROM b",
+        ),
+        ("not_contains_op", "notcontains_op", "a NOT LIKE '%' || b || '%'"),
+        ("not_endswith_op", "notendswith_op", "a NOT LIKE '%' || b"),
+        ("not_ilike_op", "notilike_op", "lower(a) NOT LIKE lower(b)"),
+        ("not_like_op", "notlike_op", "a NOT LIKE b"),
+        ("not_match_op", "notmatch_op", "NOT a MATCH b"),
+        ("not_startswith_op", "notstartswith_op", "a NOT LIKE b || '%'"),
+    )
+    def test_issue_5435_binary_operators(self, modern, legacy, txt):
+        a, b = column("a"), column("b")
+        _op_modern = getattr(operators, modern)
+        _op_legacy = getattr(operators, legacy)
+
+        eq_(str(_op_modern(a, b)), txt)
+
+        eq_(str(_op_modern(a, b)), str(_op_legacy(a, b)))
+
+    @testing.combinations(
+        ("nulls_first_op", "nullsfirst_op", "a NULLS FIRST"),
+        ("nulls_last_op", "nullslast_op", "a NULLS LAST"),
+    )
+    def test_issue_5435_unary_operators(self, modern, legacy, txt):
+        a = column("a")
+        _op_modern = getattr(operators, modern)
+        _op_legacy = getattr(operators, legacy)
+
+        eq_(str(_op_modern(a)), txt)
+
+        eq_(str(_op_modern(a)), str(_op_legacy(a)))
+
+    @testing.combinations(
+        ("not_between_op", "notbetween_op", "a NOT BETWEEN b AND c")
+    )
+    def test_issue_5435_between_operators(self, modern, legacy, txt):
+        a, b, c = column("a"), column("b"), column("c")
+        _op_modern = getattr(operators, modern)
+        _op_legacy = getattr(operators, legacy)
+
+        eq_(str(_op_modern(a, b, c)), txt)
+
+        eq_(str(_op_modern(a, b, c)), str(_op_legacy(a, b, c)))
+
+    @testing.combinations(
+        ("is_false", "isfalse", True),
+        ("is_true", "istrue", True),
+        ("is_not_distinct_from", "isnot_distinct_from", True),
+        ("not_between_op", "notbetween_op", True),
+        ("not_contains_op", "notcontains_op", False),
+        ("not_endswith_op", "notendswith_op", False),
+        ("not_ilike_op", "notilike_op", True),
+        ("not_like_op", "notlike_op", True),
+        ("not_match_op", "notmatch_op", True),
+        ("not_startswith_op", "notstartswith_op", False),
+        ("nulls_first_op", "nullsfirst_op", False),
+        ("nulls_last_op", "nullslast_op", False),
+    )
+    def test_issue_5435_operators_precedence(
+        self, _modern, _legacy, _in_precedence
+    ):
+        # (modern, legacy, in_precendence)
+        # core operators
+        assert hasattr(operators, _modern)
+        assert hasattr(operators, _legacy)
+        _op_modern = getattr(operators, _modern)
+        _op_legacy = getattr(operators, _legacy)
+        assert _op_modern == _op_legacy
+        # since they are the same item, only 1 precedence check needed
+        if _in_precedence:
+            assert _op_legacy in operators._PRECEDENCE
+        else:
+            assert _op_legacy not in operators._PRECEDENCE
+
+    @testing.combinations(
+        ("is_not_distinct_from", "isnot_distinct_from"),
+        ("not_ilike", "notilike"),
+        ("not_like", "notlike"),
+        ("nulls_first", "nullsfirst"),
+        ("nulls_last", "nullslast"),
+    )
+    def test_issue_5435_operators_column(self, _modern, _legacy):
+        # (modern, legacy)
+        # Column operators
+        assert hasattr(operators.ColumnOperators, _modern)
+        assert hasattr(operators.ColumnOperators, _legacy)
+        _op_modern = getattr(operators.ColumnOperators, _modern)
+        _op_legacy = getattr(operators.ColumnOperators, _legacy)
+        assert _op_modern == _op_legacy
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 2f9273859..aaeed68dd 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -97,7 +97,8 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
         (operators.is_distinct_from, True),
         (operators.is_distinct_from, False),
         (operators.is_distinct_from, None),
-        (operators.isnot_distinct_from, True),
+        (operators.is_not_distinct_from, True),
+        (operators.isnot_distinct_from, True),  # deprecated 1.4; See #5429
         (operators.is_, True),
         (operators.is_not, True),
         (operators.isnot, True),  # deprecated 1.4; See #5429
@@ -105,9 +106,11 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
         (operators.is_not, False),
         (operators.isnot, False),  # deprecated 1.4; See #5429
         (operators.like_op, right_column),
-        (operators.notlike_op, right_column),
+        (operators.not_like_op, right_column),
+        (operators.notlike_op, right_column),  # deprecated 1.4; See #5435
         (operators.ilike_op, right_column),
-        (operators.notilike_op, right_column),
+        (operators.not_ilike_op, right_column),
+        (operators.notilike_op, right_column),  # deprecated 1.4; See #5435
         (operators.is_, right_column),
         (operators.is_not, right_column),
         (operators.isnot, right_column),  # deprecated 1.4; See #5429
@@ -1422,7 +1425,7 @@ class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     def test_operator_precedence_collate_6(self):
         self.assert_compile(
             select(self.table1.c.name).order_by(
-                self.table1.c.name.collate("utf-8").desc().nullslast()
+                self.table1.c.name.collate("utf-8").desc().nulls_last()
             ),
             "SELECT mytable.name FROM mytable "
             'ORDER BY mytable.name COLLATE "utf-8" DESC NULLS LAST',
@@ -1616,35 +1619,35 @@ class IsDistinctFromTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             dialect=postgresql.dialect(),
         )
 
-    def test_isnot_distinct_from(self):
+    def test_is_not_distinct_from(self):
         self.assert_compile(
-            self.table1.c.myid.isnot_distinct_from(1),
+            self.table1.c.myid.is_not_distinct_from(1),
             "mytable.myid IS NOT DISTINCT FROM :myid_1",
         )
 
-    def test_isnot_distinct_from_sqlite(self):
+    def test_is_not_distinct_from_sqlite(self):
         self.assert_compile(
-            self.table1.c.myid.isnot_distinct_from(1),
+            self.table1.c.myid.is_not_distinct_from(1),
             "mytable.myid IS ?",
             dialect=sqlite.dialect(),
         )
 
-    def test_isnot_distinct_from_postgresql(self):
+    def test_is_not_distinct_from_postgresql(self):
         self.assert_compile(
-            self.table1.c.myid.isnot_distinct_from(1),
+            self.table1.c.myid.is_not_distinct_from(1),
             "mytable.myid IS NOT DISTINCT FROM %(myid_1)s",
             dialect=postgresql.dialect(),
         )
 
-    def test_not_isnot_distinct_from(self):
+    def test_not_is_not_distinct_from(self):
         self.assert_compile(
-            ~self.table1.c.myid.isnot_distinct_from(1),
+            ~self.table1.c.myid.is_not_distinct_from(1),
             "mytable.myid IS DISTINCT FROM :myid_1",
         )
 
-    def test_not_isnot_distinct_from_postgresql(self):
+    def test_not_is_not_distinct_from_postgresql(self):
         self.assert_compile(
-            ~self.table1.c.myid.isnot_distinct_from(1),
+            ~self.table1.c.myid.is_not_distinct_from(1),
             "mytable.myid IS DISTINCT FROM %(myid_1)s",
             dialect=postgresql.dialect(),
         )
@@ -2656,30 +2659,30 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             checkparams={"x_1": "a%b_c"},
         )
 
-    def test_notlike(self):
+    def test_not_like(self):
         self.assert_compile(
-            column("x").notlike("y"),
+            column("x").not_like("y"),
             "x NOT LIKE :x_1",
             checkparams={"x_1": "y"},
         )
 
-    def test_notlike_escape(self):
+    def test_not_like_escape(self):
         self.assert_compile(
-            column("x").notlike("a%b_c", escape="\\"),
+            column("x").not_like("a%b_c", escape="\\"),
             "x NOT LIKE :x_1 ESCAPE '\\'",
             checkparams={"x_1": "a%b_c"},
         )
 
-    def test_notilike(self):
+    def test_not_ilike(self):
         self.assert_compile(
-            column("x").notilike("y"),
+            column("x").not_ilike("y"),
             "lower(x) NOT LIKE lower(:x_1)",
             checkparams={"x_1": "y"},
         )
 
-    def test_notilike_escape(self):
+    def test_not_ilike_escape(self):
         self.assert_compile(
-            column("x").notilike("a%b_c", escape="\\"),
+            column("x").not_ilike("a%b_c", escape="\\"),
             "lower(x) NOT LIKE lower(:x_1) ESCAPE '\\'",
             checkparams={"x_1": "a%b_c"},
         )
diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index 9f66a2ef5..7d05462ab 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -480,7 +480,7 @@ class QueryTest(fixtures.TestBase):
         for labels in False, True:
             a_eq(
                 users.select(
-                    order_by=[users.c.user_name.nullsfirst()],
+                    order_by=[users.c.user_name.nulls_first()],
                     use_labels=labels,
                 ),
                 [(1, None), (3, "a"), (2, "b")],
@@ -488,14 +488,15 @@ class QueryTest(fixtures.TestBase):
 
             a_eq(
                 users.select(
-                    order_by=[users.c.user_name.nullslast()], use_labels=labels
+                    order_by=[users.c.user_name.nulls_last()],
+                    use_labels=labels,
                 ),
                 [(3, "a"), (2, "b"), (1, None)],
             )
 
             a_eq(
                 users.select(
-                    order_by=[asc(users.c.user_name).nullsfirst()],
+                    order_by=[asc(users.c.user_name).nulls_first()],
                     use_labels=labels,
                 ),
                 [(1, None), (3, "a"), (2, "b")],
@@ -503,7 +504,7 @@ class QueryTest(fixtures.TestBase):
 
             a_eq(
                 users.select(
-                    order_by=[asc(users.c.user_name).nullslast()],
+                    order_by=[asc(users.c.user_name).nulls_last()],
                     use_labels=labels,
                 ),
                 [(3, "a"), (2, "b"), (1, None)],
@@ -511,7 +512,7 @@ class QueryTest(fixtures.TestBase):
 
             a_eq(
                 users.select(
-                    order_by=[users.c.user_name.desc().nullsfirst()],
+                    order_by=[users.c.user_name.desc().nulls_first()],
                     use_labels=labels,
                 ),
                 [(1, None), (2, "b"), (3, "a")],
@@ -519,7 +520,7 @@ class QueryTest(fixtures.TestBase):
 
             a_eq(
                 users.select(
-                    order_by=[users.c.user_name.desc().nullslast()],
+                    order_by=[users.c.user_name.desc().nulls_last()],
                     use_labels=labels,
                 ),
                 [(2, "b"), (3, "a"), (1, None)],
@@ -527,7 +528,7 @@ class QueryTest(fixtures.TestBase):
 
             a_eq(
                 users.select(
-                    order_by=[desc(users.c.user_name).nullsfirst()],
+                    order_by=[desc(users.c.user_name).nulls_first()],
                     use_labels=labels,
                 ),
                 [(1, None), (2, "b"), (3, "a")],
@@ -535,7 +536,7 @@ class QueryTest(fixtures.TestBase):
 
             a_eq(
                 users.select(
-                    order_by=[desc(users.c.user_name).nullslast()],
+                    order_by=[desc(users.c.user_name).nulls_last()],
                     use_labels=labels,
                 ),
                 [(2, "b"), (3, "a"), (1, None)],
@@ -543,7 +544,10 @@ class QueryTest(fixtures.TestBase):
 
             a_eq(
                 users.select(
-                    order_by=[users.c.user_name.nullsfirst(), users.c.user_id],
+                    order_by=[
+                        users.c.user_name.nulls_first(),
+                        users.c.user_id,
+                    ],
                     use_labels=labels,
                 ),
                 [(1, None), (3, "a"), (2, "b")],
@@ -551,7 +555,7 @@ class QueryTest(fixtures.TestBase):
 
             a_eq(
                 users.select(
-                    order_by=[users.c.user_name.nullslast(), users.c.user_id],
+                    order_by=[users.c.user_name.nulls_last(), users.c.user_id],
                     use_labels=labels,
                 ),
                 [(3, "a"), (2, "b"), (1, None)],