Skip to content

Commit fa0666c

Browse files
zzzeekzzzeek
committed
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 parent b56494b commit fa0666c

6 files changed

Lines changed: 96 additions & 40 deletions

File tree

‎doc/build/changelog/unreleased_20/9511.rst‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
.. change::
2+
:tags: bug, postgresql
3+
:tickets: 9511
4+
5+
Fixed critical regression in PostgreSQL dialects such as asyncpg which rely
6+
upon explicit casts in SQL in order for datatypes to be passed to the
7+
driver correctly, where a :class:`.String` datatype would be cast along
8+
with the exact column length being compared, leading to implicit truncation
9+
when comparing a ``VARCHAR`` of a smaller length to a string of greater
10+
length regardless of operator in use (e.g. LIKE, MATCH, etc.). The
11+
PostgreSQL dialect now omits the length from ``VARCHAR`` when rendering
12+
these casts.

‎lib/sqlalchemy/dialects/postgresql/base.py‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1717,6 +1717,10 @@ def _assert_pg_ts_ext(self, element, **kw):
17171717
return f"{element.name}{self.function_argspec(element, **kw)}"
17181718

17191719
def render_bind_cast(self, type_, dbapi_type, sqltext):
1720+
if dbapi_type._type_affinity is sqltypes.String:
1721+
# use VARCHAR with no length for VARCHAR cast.
1722+
# see #9511
1723+
dbapi_type = sqltypes.STRINGTYPE
17201724
return f"""{sqltext}::{
17211725
self.dialect.type_compiler_instance.process(
17221726
dbapi_type, identifier_preparer=self.preparer

‎lib/sqlalchemy/testing/suite/test_types.py‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -397,6 +397,25 @@ def test_literal(self, literal_round_trip):
397397
def test_literal_non_ascii(self, literal_round_trip):
398398
literal_round_trip(String(40), ["réve🐍 illé"], ["réve🐍 illé"])
399399

400+
@testing.combinations(
401+
("%B%", ["AB", "BC"]),
402+
("A%C", ["AC"]),
403+
("A%C%Z", []),
404+
argnames="expr, expected",
405+
)
406+
def test_dont_truncate_rightside(
407+
self, metadata, connection, expr, expected
408+
):
409+
t = Table("t", metadata, Column("x", String(2)))
410+
t.create(connection)
411+
412+
connection.execute(t.insert(), [{"x": "AB"}, {"x": "BC"}, {"x": "AC"}])
413+
414+
eq_(
415+
connection.scalars(select(t.c.x).where(t.c.x.like(expr))).all(),
416+
expected,
417+
)
418+
400419
def test_literal_quoting(self, literal_round_trip):
401420
data = """some 'text' hey "hi there" that's text"""
402421
literal_round_trip(String(40), [data], [data])

‎test/dialect/postgresql/test_query.py‎

Lines changed: 38 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -213,30 +213,30 @@ def _assert_data_autoincrement(self, connection, table):
213213
asserter.assert_(
214214
DialectSQL(
215215
"INSERT INTO testtable (id, data) VALUES "
216-
"(:id::INTEGER, :data::VARCHAR(30))",
216+
"(:id::INTEGER, :data::VARCHAR)",
217217
{"id": 30, "data": "d1"},
218218
),
219219
DialectSQL(
220220
"INSERT INTO testtable (id, data) VALUES "
221-
"(:id::INTEGER, :data::VARCHAR(30))",
221+
"(:id::INTEGER, :data::VARCHAR)",
222222
{"id": 1, "data": "d2"},
223223
),
224224
DialectSQL(
225225
"INSERT INTO testtable (id, data) VALUES "
226-
"(:id::INTEGER, :data::VARCHAR(30))",
226+
"(:id::INTEGER, :data::VARCHAR)",
227227
[{"id": 31, "data": "d3"}, {"id": 32, "data": "d4"}],
228228
),
229229
DialectSQL(
230-
"INSERT INTO testtable (data) VALUES (:data::VARCHAR(30))",
230+
"INSERT INTO testtable (data) VALUES (:data::VARCHAR)",
231231
[{"data": "d5"}, {"data": "d6"}],
232232
),
233233
DialectSQL(
234234
"INSERT INTO testtable (id, data) VALUES "
235-
"(:id::INTEGER, :data::VARCHAR(30))",
235+
"(:id::INTEGER, :data::VARCHAR)",
236236
[{"id": 33, "data": "d7"}],
237237
),
238238
DialectSQL(
239-
"INSERT INTO testtable (data) VALUES (:data::VARCHAR(30))",
239+
"INSERT INTO testtable (data) VALUES (:data::VARCHAR)",
240240
[{"data": "d8"}],
241241
),
242242
)
@@ -311,30 +311,30 @@ def _assert_data_autoincrement(self, connection, table):
311311
asserter.assert_(
312312
DialectSQL(
313313
"INSERT INTO testtable (id, data) VALUES "
314-
"(:id::INTEGER, :data::VARCHAR(30))",
314+
"(:id::INTEGER, :data::VARCHAR)",
315315
{"id": 30, "data": "d1"},
316316
),
317317
DialectSQL(
318318
"INSERT INTO testtable (id, data) VALUES "
319-
"(:id::INTEGER, :data::VARCHAR(30))",
319+
"(:id::INTEGER, :data::VARCHAR)",
320320
{"id": 5, "data": "d2"},
321321
),
322322
DialectSQL(
323323
"INSERT INTO testtable (id, data) VALUES "
324-
"(:id::INTEGER, :data::VARCHAR(30))",
324+
"(:id::INTEGER, :data::VARCHAR)",
325325
[{"id": 31, "data": "d3"}, {"id": 32, "data": "d4"}],
326326
),
327327
DialectSQL(
328-
"INSERT INTO testtable (data) VALUES (:data::VARCHAR(30))",
328+
"INSERT INTO testtable (data) VALUES (:data::VARCHAR)",
329329
[{"data": "d5"}, {"data": "d6"}],
330330
),
331331
DialectSQL(
332332
"INSERT INTO testtable (id, data) VALUES "
333-
"(:id::INTEGER, :data::VARCHAR(30))",
333+
"(:id::INTEGER, :data::VARCHAR)",
334334
[{"id": 33, "data": "d7"}],
335335
),
336336
DialectSQL(
337-
"INSERT INTO testtable (data) VALUES (:data::VARCHAR(30))",
337+
"INSERT INTO testtable (data) VALUES (:data::VARCHAR)",
338338
[{"data": "d8"}],
339339
),
340340
)
@@ -425,31 +425,31 @@ def _assert_data_autoincrement_returning(self, connection, table):
425425
asserter.assert_(
426426
DialectSQL(
427427
"INSERT INTO testtable (id, data) VALUES "
428-
"(:id::INTEGER, :data::VARCHAR(30))",
428+
"(:id::INTEGER, :data::VARCHAR)",
429429
{"id": 30, "data": "d1"},
430430
),
431431
DialectSQL(
432432
"INSERT INTO testtable (data) VALUES "
433-
"(:data::VARCHAR(30)) RETURNING "
433+
"(:data::VARCHAR) RETURNING "
434434
"testtable.id",
435435
{"data": "d2"},
436436
),
437437
DialectSQL(
438438
"INSERT INTO testtable (id, data) VALUES "
439-
"(:id::INTEGER, :data::VARCHAR(30))",
439+
"(:id::INTEGER, :data::VARCHAR)",
440440
[{"id": 31, "data": "d3"}, {"id": 32, "data": "d4"}],
441441
),
442442
DialectSQL(
443-
"INSERT INTO testtable (data) VALUES (:data::VARCHAR(30))",
443+
"INSERT INTO testtable (data) VALUES (:data::VARCHAR)",
444444
[{"data": "d5"}, {"data": "d6"}],
445445
),
446446
DialectSQL(
447447
"INSERT INTO testtable (id, data) VALUES "
448-
"(:id::INTEGER, :data::VARCHAR(30))",
448+
"(:id::INTEGER, :data::VARCHAR)",
449449
[{"id": 33, "data": "d7"}],
450450
),
451451
DialectSQL(
452-
"INSERT INTO testtable (data) VALUES (:data::VARCHAR(30))",
452+
"INSERT INTO testtable (data) VALUES (:data::VARCHAR)",
453453
[{"data": "d8"}],
454454
),
455455
)
@@ -527,31 +527,31 @@ def _assert_data_autoincrement_returning(self, connection, table):
527527
asserter.assert_(
528528
DialectSQL(
529529
"INSERT INTO testtable (id, data) VALUES "
530-
"(:id::INTEGER, :data::VARCHAR(30))",
530+
"(:id::INTEGER, :data::VARCHAR)",
531531
{"id": 30, "data": "d1"},
532532
),
533533
DialectSQL(
534534
"INSERT INTO testtable (data) VALUES "
535-
"(:data::VARCHAR(30)) RETURNING "
535+
"(:data::VARCHAR) RETURNING "
536536
"testtable.id",
537537
{"data": "d2"},
538538
),
539539
DialectSQL(
540540
"INSERT INTO testtable (id, data) VALUES "
541-
"(:id::INTEGER, :data::VARCHAR(30))",
541+
"(:id::INTEGER, :data::VARCHAR)",
542542
[{"id": 31, "data": "d3"}, {"id": 32, "data": "d4"}],
543543
),
544544
DialectSQL(
545-
"INSERT INTO testtable (data) VALUES (:data::VARCHAR(30))",
545+
"INSERT INTO testtable (data) VALUES (:data::VARCHAR)",
546546
[{"data": "d5"}, {"data": "d6"}],
547547
),
548548
DialectSQL(
549549
"INSERT INTO testtable (id, data) VALUES "
550-
"(:id::INTEGER, :data::VARCHAR(30))",
550+
"(:id::INTEGER, :data::VARCHAR)",
551551
[{"id": 33, "data": "d7"}],
552552
),
553553
DialectSQL(
554-
"INSERT INTO testtable (data) VALUES (:data::VARCHAR(30))",
554+
"INSERT INTO testtable (data) VALUES (:data::VARCHAR)",
555555
[{"data": "d8"}],
556556
),
557557
)
@@ -623,33 +623,33 @@ def _assert_data_with_sequence(self, connection, table, seqname):
623623
asserter.assert_(
624624
DialectSQL(
625625
"INSERT INTO testtable (id, data) VALUES "
626-
"(:id::INTEGER, :data::VARCHAR(30))",
626+
"(:id::INTEGER, :data::VARCHAR)",
627627
{"id": 30, "data": "d1"},
628628
),
629629
CursorSQL("select nextval('my_seq')", consume_statement=False),
630630
DialectSQL(
631631
"INSERT INTO testtable (id, data) VALUES "
632-
"(:id::INTEGER, :data::VARCHAR(30))",
632+
"(:id::INTEGER, :data::VARCHAR)",
633633
{"id": 1, "data": "d2"},
634634
),
635635
DialectSQL(
636636
"INSERT INTO testtable (id, data) VALUES "
637-
"(:id::INTEGER, :data::VARCHAR(30))",
637+
"(:id::INTEGER, :data::VARCHAR)",
638638
[{"id": 31, "data": "d3"}, {"id": 32, "data": "d4"}],
639639
),
640640
DialectSQL(
641641
"INSERT INTO testtable (id, data) VALUES (nextval('%s'), "
642-
":data::VARCHAR(30))" % seqname,
642+
":data::VARCHAR)" % seqname,
643643
[{"data": "d5"}, {"data": "d6"}],
644644
),
645645
DialectSQL(
646646
"INSERT INTO testtable (id, data) VALUES "
647-
"(:id::INTEGER, :data::VARCHAR(30))",
647+
"(:id::INTEGER, :data::VARCHAR)",
648648
[{"id": 33, "data": "d7"}],
649649
),
650650
DialectSQL(
651651
"INSERT INTO testtable (id, data) VALUES (nextval('%s'), "
652-
":data::VARCHAR(30))" % seqname,
652+
":data::VARCHAR)" % seqname,
653653
[{"data": "d8"}],
654654
),
655655
)
@@ -722,33 +722,33 @@ def _assert_data_with_sequence_returning(self, connection, table, seqname):
722722
asserter.assert_(
723723
DialectSQL(
724724
"INSERT INTO testtable (id, data) VALUES "
725-
"(:id::INTEGER, :data::VARCHAR(30))",
725+
"(:id::INTEGER, :data::VARCHAR)",
726726
{"id": 30, "data": "d1"},
727727
),
728728
DialectSQL(
729729
"INSERT INTO testtable (id, data) VALUES "
730-
"(nextval('my_seq'), :data::VARCHAR(30)) "
730+
"(nextval('my_seq'), :data::VARCHAR) "
731731
"RETURNING testtable.id",
732732
{"data": "d2"},
733733
),
734734
DialectSQL(
735735
"INSERT INTO testtable (id, data) VALUES "
736-
"(:id::INTEGER, :data::VARCHAR(30))",
736+
"(:id::INTEGER, :data::VARCHAR)",
737737
[{"id": 31, "data": "d3"}, {"id": 32, "data": "d4"}],
738738
),
739739
DialectSQL(
740740
"INSERT INTO testtable (id, data) VALUES (nextval('%s'), "
741-
":data::VARCHAR(30))" % seqname,
741+
":data::VARCHAR)" % seqname,
742742
[{"data": "d5"}, {"data": "d6"}],
743743
),
744744
DialectSQL(
745745
"INSERT INTO testtable (id, data) VALUES "
746-
"(:id::INTEGER, :data::VARCHAR(30))",
746+
"(:id::INTEGER, :data::VARCHAR)",
747747
[{"id": 33, "data": "d7"}],
748748
),
749749
DialectSQL(
750750
"INSERT INTO testtable (id, data) VALUES (nextval('%s'), "
751-
":data::VARCHAR(30))" % seqname,
751+
":data::VARCHAR)" % seqname,
752752
[{"data": "d8"}],
753753
),
754754
)
@@ -982,8 +982,7 @@ def test_expression_pyformat(self, connection):
982982
if self._strs_render_bind_casts(connection):
983983
self.assert_compile(
984984
matchtable.c.title.match("somstr"),
985-
"matchtable.title @@ "
986-
"plainto_tsquery(%(title_1)s::VARCHAR(200))",
985+
"matchtable.title @@ " "plainto_tsquery(%(title_1)s::VARCHAR)",
987986
)
988987
else:
989988
self.assert_compile(
@@ -998,7 +997,7 @@ def test_expression_positional(self, connection):
998997
if self._strs_render_bind_casts(connection):
999998
self.assert_compile(
1000999
matchtable.c.title.match("somstr"),
1001-
"matchtable.title @@ plainto_tsquery($1::VARCHAR(200))",
1000+
"matchtable.title @@ plainto_tsquery($1::VARCHAR)",
10021001
)
10031002
else:
10041003
self.assert_compile(

‎test/dialect/postgresql/test_types.py‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,28 @@
9191
from ...engine.test_ddlevents import DDLEventWCreateHarness
9292

9393

94+
class MiscTypesTest(AssertsCompiledSQL, fixtures.TestBase):
95+
@testing.combinations(
96+
("asyncpg", "x LIKE $1::VARCHAR"),
97+
("psycopg", "x LIKE %(x_1)s::VARCHAR"),
98+
("psycopg2", "x LIKE %(x_1)s"),
99+
("pg8000", "x LIKE %s::VARCHAR"),
100+
)
101+
def test_string_coercion_no_len(self, driver, expected):
102+
"""test #9511.
103+
104+
comparing to string does not include length in the cast for those
105+
dialects that require a cast.
106+
107+
"""
108+
109+
self.assert_compile(
110+
column("x", String(2)).like("%a%"),
111+
expected,
112+
dialect=f"postgresql+{driver}",
113+
)
114+
115+
94116
class FloatCoercionTest(fixtures.TablesTest, AssertsExecutionResults):
95117
__only_on__ = "postgresql"
96118
__dialect__ = postgresql.dialect()

‎test/sql/test_type_expressions.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -93,7 +93,7 @@ def column_expression(self, col):
9393
def _variant_fixture(self, inner_fixture):
9494
type_ = inner_fixture.c.y.type
9595

96-
variant = String().with_variant(type_, "default")
96+
variant = String(30).with_variant(type_, "default")
9797
return self._test_table(variant)
9898

9999
def _dialect_level_fixture(self):

0 commit comments

Comments
 (0)