From 8a62aa58fa5eeaad4e6bdd994a00293f41eb3a71 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Wed, 5 Jan 2022 12:20:46 -0500
Subject: implement second-level type resolution for literals

Added additional rule to the system that determines ``TypeEngine``
implementations from Python literals to apply a second level of adjustment
to the type, so that a Python datetime with or without tzinfo can set the
``timezone=True`` parameter on the returned :class:`.DateTime` object, as
well as :class:`.Time`. This helps with some round-trip scenarios on
type-sensitive PostgreSQL dialects such as asyncpg, psycopg3 (2.0 only).

For 1.4 specifically, the backport improves support for asyncpg handling of
TIME WITH TIMEZONE, which was not fully implemented. 2.0's reworked
PostgreSQL architecture had this handled already.

Fixes: #7537
Change-Id: Icdb07db85af5f7f39f1c1ef855fe27609770094b
---
 lib/sqlalchemy/testing/requirements.py     | 33 ++++++++++++++++++++++++++++++
 lib/sqlalchemy/testing/suite/test_types.py | 28 +++++++++++++++++++++++++
 2 files changed, 61 insertions(+)

(limited to 'lib/sqlalchemy/testing')

diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 5cf80a1fb..e87ed6edc 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -753,6 +753,29 @@ class SuiteRequirements(Requirements):
 
         return exclusions.open()
 
+    @property
+    def datetime_timezone(self):
+        """target dialect supports representation of Python
+        datetime.datetime() with tzinfo with DateTime(timezone=True)."""
+
+        return exclusions.closed()
+
+    @property
+    def time_timezone(self):
+        """target dialect supports representation of Python
+        datetime.time() with tzinfo with Time(timezone=True)."""
+
+        return exclusions.closed()
+
+    @property
+    def datetime_implicit_bound(self):
+        """target dialect when given a datetime object will bind it such
+        that the database server knows the object is a datetime, and not
+        a plain string.
+
+        """
+        return exclusions.open()
+
     @property
     def datetime_microseconds(self):
         """target dialect supports representation of Python
@@ -767,6 +790,16 @@ class SuiteRequirements(Requirements):
         if TIMESTAMP is used."""
         return exclusions.closed()
 
+    @property
+    def timestamp_microseconds_implicit_bound(self):
+        """target dialect when given a datetime object which also includes
+        a microseconds portion when using the TIMESTAMP data type
+        will bind it such that the database server knows
+        the object is a datetime with microseconds, and not a plain string.
+
+        """
+        return self.timestamp_microseconds
+
     @property
     def datetime_historic(self):
         """target dialect supports representation of Python
diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index 78596457e..796de5d93 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -302,6 +302,11 @@ class _DateFixture(_LiteralRoundTripFixture, fixtures.TestBase):
             Column("decorated_date_data", Decorated),
         )
 
+    @testing.requires.datetime_implicit_bound
+    def test_select_direct(self, connection):
+        result = connection.scalar(select(literal(self.data)))
+        eq_(result, self.data)
+
     def test_round_trip(self, connection):
         date_table = self.tables.date_table
 
@@ -376,6 +381,15 @@ class DateTimeTest(_DateFixture, fixtures.TablesTest):
     data = datetime.datetime(2012, 10, 15, 12, 57, 18)
 
 
+class DateTimeTZTest(_DateFixture, fixtures.TablesTest):
+    __requires__ = ("datetime_timezone",)
+    __backend__ = True
+    datatype = DateTime(timezone=True)
+    data = datetime.datetime(
+        2012, 10, 15, 12, 57, 18, tzinfo=datetime.timezone.utc
+    )
+
+
 class DateTimeMicrosecondsTest(_DateFixture, fixtures.TablesTest):
     __requires__ = ("datetime_microseconds",)
     __backend__ = True
@@ -389,6 +403,11 @@ class TimestampMicrosecondsTest(_DateFixture, fixtures.TablesTest):
     datatype = TIMESTAMP
     data = datetime.datetime(2012, 10, 15, 12, 57, 18, 396)
 
+    @testing.requires.timestamp_microseconds_implicit_bound
+    def test_select_direct(self, connection):
+        result = connection.scalar(select(literal(self.data)))
+        eq_(result, self.data)
+
 
 class TimeTest(_DateFixture, fixtures.TablesTest):
     __requires__ = ("time",)
@@ -397,6 +416,13 @@ class TimeTest(_DateFixture, fixtures.TablesTest):
     data = datetime.time(12, 57, 18)
 
 
+class TimeTZTest(_DateFixture, fixtures.TablesTest):
+    __requires__ = ("time_timezone",)
+    __backend__ = True
+    datatype = Time(timezone=True)
+    data = datetime.time(12, 57, 18, tzinfo=datetime.timezone.utc)
+
+
 class TimeMicrosecondsTest(_DateFixture, fixtures.TablesTest):
     __requires__ = ("time_microseconds",)
     __backend__ = True
@@ -1515,6 +1541,7 @@ __all__ = (
     "JSONLegacyStringCastIndexTest",
     "DateTest",
     "DateTimeTest",
+    "DateTimeTZTest",
     "TextTest",
     "NumericTest",
     "IntegerTest",
@@ -1524,6 +1551,7 @@ __all__ = (
     "TimeMicrosecondsTest",
     "TimestampMicrosecondsTest",
     "TimeTest",
+    "TimeTZTest",
     "TrueDivTest",
     "DateTimeMicrosecondsTest",
     "DateHistoricTest",
-- 
cgit v1.2.1