auto-cast PG range types

Range type handling has been enhanced so that it automatically
renders type casts, so that in-place round trips for statements that don't
provide the database with any context don't require the :func:`_sql.cast`
construct to be explicit for the database to know the desired type.

Change-Id: Id630b726f8a23059dd2f4cbc410bf5229d89cbfb
References: #8540

1 files changed, 89 insertions, 0 deletions

diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index b5c20bd8d..1f93a4023 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -3690,6 +3690,13 @@ class _RangeTypeCompilation(AssertsCompiledSQL, fixtures.TestBase):
             sqltypes.BOOLEANTYPE,
         )
 
+    def test_where_equal_obj(self):
+        self._test_clause(
+            self.col == self._data_obj(),
+            f"data_table.range = %(range_1)s::{self._col_str}",
+            sqltypes.BOOLEANTYPE,
+        )
+
     def test_where_not_equal(self):
         self._test_clause(
             self.col != self._data_str(),
@@ -3697,6 +3704,13 @@ class _RangeTypeCompilation(AssertsCompiledSQL, fixtures.TestBase):
             sqltypes.BOOLEANTYPE,
         )
 
+    def test_where_not_equal_obj(self):
+        self._test_clause(
+            self.col != self._data_obj(),
+            f"data_table.range <> %(range_1)s::{self._col_str}",
+            sqltypes.BOOLEANTYPE,
+        )
+
     def test_where_is_null(self):
         self._test_clause(
             self.col == None, "data_table.range IS NULL", sqltypes.BOOLEANTYPE
@@ -3744,6 +3758,13 @@ class _RangeTypeCompilation(AssertsCompiledSQL, fixtures.TestBase):
             sqltypes.BOOLEANTYPE,
         )
 
+    def test_contains_obj(self):
+        self._test_clause(
+            self.col.contains(self._data_obj()),
+            f"data_table.range @> %(range_1)s::{self._col_str}",
+            sqltypes.BOOLEANTYPE,
+        )
+
     def test_contained_by(self):
         self._test_clause(
             self.col.contained_by(self._data_str()),
@@ -3840,6 +3861,26 @@ class _RangeTypeRoundTrip(fixtures.TablesTest):
         )
         cls.col = table.c.range
 
+    def test_auto_cast_back_to_type(self, connection):
+        """test that a straight pass of the range type without any context
+        will send appropriate casting info so that the driver can round
+        trip it.
+
+        This doesn't happen in general across other backends and not for
+        types like JSON etc., although perhaps it should, as we now have
+        pretty straightforward infrastructure to turn it on; asyncpg
+        for example does cast JSONs now in place.  But that's a
+        bigger issue; for PG ranges it's likely useful to do this for
+        PG backends as this is a fairly narrow use case.
+
+        Brought up in #8540.
+
+        """
+        data_obj = self._data_obj()
+        stmt = select(literal(data_obj, type_=self._col_type))
+        round_trip = connection.scalar(stmt)
+        eq_(round_trip, data_obj)
+
     def test_actual_type(self):
         eq_(str(self._col_type()), self._col_str)
 
@@ -4093,6 +4134,13 @@ class _MultiRangeTypeCompilation(AssertsCompiledSQL, fixtures.TestBase):
             sqltypes.BOOLEANTYPE,
         )
 
+    def test_where_equal_obj(self):
+        self._test_clause(
+            self.col == self._data_obj(),
+            f"data_table.multirange = %(multirange_1)s::{self._col_str}",
+            sqltypes.BOOLEANTYPE,
+        )
+
     def test_where_not_equal(self):
         self._test_clause(
             self.col != self._data_str(),
@@ -4100,6 +4148,13 @@ class _MultiRangeTypeCompilation(AssertsCompiledSQL, fixtures.TestBase):
             sqltypes.BOOLEANTYPE,
         )
 
+    def test_where_not_equal_obj(self):
+        self._test_clause(
+            self.col != self._data_obj(),
+            f"data_table.multirange <> %(multirange_1)s::{self._col_str}",
+            sqltypes.BOOLEANTYPE,
+        )
+
     def test_where_is_null(self):
         self._test_clause(
             self.col == None,
@@ -4156,6 +4211,13 @@ class _MultiRangeTypeCompilation(AssertsCompiledSQL, fixtures.TestBase):
             sqltypes.BOOLEANTYPE,
         )
 
+    def test_contained_by_obj(self):
+        self._test_clause(
+            self.col.contained_by(self._data_obj()),
+            f"data_table.multirange <@ %(multirange_1)s::{self._col_str}",
+            sqltypes.BOOLEANTYPE,
+        )
+
     def test_overlaps(self):
         self._test_clause(
             self.col.overlaps(self._data_str()),
@@ -4208,6 +4270,13 @@ class _MultiRangeTypeCompilation(AssertsCompiledSQL, fixtures.TestBase):
             sqltypes.BOOLEANTYPE,
         )
 
+    def test_adjacent_to_obj(self):
+        self._test_clause(
+            self.col.adjacent_to(self._data_obj()),
+            f"data_table.multirange -|- %(multirange_1)s::{self._col_str}",
+            sqltypes.BOOLEANTYPE,
+        )
+
     def test_union(self):
         self._test_clause(
             self.col + self.col,
@@ -4245,6 +4314,26 @@ class _MultiRangeTypeRoundTrip(fixtures.TablesTest):
         )
         cls.col = table.c.range
 
+    def test_auto_cast_back_to_type(self, connection):
+        """test that a straight pass of the range type without any context
+        will send appropriate casting info so that the driver can round
+        trip it.
+
+        This doesn't happen in general across other backends and not for
+        types like JSON etc., although perhaps it should, as we now have
+        pretty straightforward infrastructure to turn it on; asyncpg
+        for example does cast JSONs now in place.  But that's a
+        bigger issue; for PG ranges it's likely useful to do this for
+        PG backends as this is a fairly narrow use case.
+
+        Brought up in #8540.
+
+        """
+        data_obj = self._data_obj()
+        stmt = select(literal(data_obj, type_=self._col_type))
+        round_trip = connection.scalar(stmt)
+        eq_(round_trip, data_obj)
+
     def test_actual_type(self):
         eq_(str(self._col_type()), self._col_str)