dont render VARCHAR length for PG casts

Fixed critical regression in PostgreSQL dialects such as asyncpg which rely
upon explicit casts in SQL in order for datatypes to be passed to the
driver correctly, where a :class:`.String` datatype would be cast along
with the exact column length being compared, leading to implicit truncation
when comparing a ``VARCHAR`` of a smaller length to a string of greater
length regardless of operator in use (e.g. LIKE, MATCH, etc.). The
PostgreSQL dialect now omits the length from ``VARCHAR`` when rendering
these casts.

Fixes: #9511
Change-Id: If094146d8cfd989a0b780872f38e86fd41ebfec2

1 files changed, 19 insertions, 0 deletions

diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index bc2885341..44d174086 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -397,6 +397,25 @@ class StringTest(_LiteralRoundTripFixture, fixtures.TestBase):
     def test_literal_non_ascii(self, literal_round_trip):
         literal_round_trip(String(40), ["réve🐍 illé"], ["réve🐍 illé"])
 
+    @testing.combinations(
+        ("%B%", ["AB", "BC"]),
+        ("A%C", ["AC"]),
+        ("A%C%Z", []),
+        argnames="expr, expected",
+    )
+    def test_dont_truncate_rightside(
+        self, metadata, connection, expr, expected
+    ):
+        t = Table("t", metadata, Column("x", String(2)))
+        t.create(connection)
+
+        connection.execute(t.insert(), [{"x": "AB"}, {"x": "BC"}, {"x": "AC"}])
+
+        eq_(
+            connection.scalars(select(t.c.x).where(t.c.x.like(expr))).all(),
+            expected,
+        )
+
     def test_literal_quoting(self, literal_round_trip):
         data = """some 'text' hey "hi there" that's text"""
         literal_round_trip(String(40), [data], [data])