From 115e4d1a491173276821bdafd967666d71cdb7a8 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 1 Apr 2021 12:46:41 -0400 Subject: [PATCH] Apply quoting to render_derived() names Fixed bug in new :meth:`_functions.FunctionElement.render_derived` feature where column names rendered out explicitly in the alias SQL would not have proper quoting applied for case sensitive names and other non-alphanumeric names. Fixes: #6183 Change-Id: I33e2534affc6e1f449f564750028fd027cb0f352 --- doc/build/changelog/unreleased_14/6183.rst | 8 ++++++++ lib/sqlalchemy/sql/compiler.py | 2 +- test/dialect/postgresql/test_query.py | 22 ++++++++++++++++++++++ test/sql/test_functions.py | 22 ++++++++++++++++++++++ 4 files changed, 53 insertions(+), 1 deletion(-) create mode 100644 doc/build/changelog/unreleased_14/6183.rst diff --git a/doc/build/changelog/unreleased_14/6183.rst b/doc/build/changelog/unreleased_14/6183.rst new file mode 100644 index 0000000000..ed4a704884 --- /dev/null +++ b/doc/build/changelog/unreleased_14/6183.rst @@ -0,0 +1,8 @@ +.. change:: + :tags: bug, sql, postgresql + :tickets: 6183 + + Fixed bug in new :meth:`_functions.FunctionElement.render_derived` feature + where column names rendered out explicitly in the alias SQL would not have + proper quoting applied for case sensitive names and other non-alphanumeric + names. diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 2762091039..32530629b4 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -2662,7 +2662,7 @@ class SQLCompiler(Compiled): ", ".join( "%s%s" % ( - col.name, + self.preparer.quote(col.name), " %s" % self.dialect.type_compiler.process( col.type, **kwargs diff --git a/test/dialect/postgresql/test_query.py b/test/dialect/postgresql/test_query.py index ce64ca16fc..db76f61ffa 100644 --- a/test/dialect/postgresql/test_query.py +++ b/test/dialect/postgresql/test_query.py @@ -5,6 +5,7 @@ import datetime from sqlalchemy import and_ from sqlalchemy import cast from sqlalchemy import Column +from sqlalchemy import column from sqlalchemy import Date from sqlalchemy import DateTime from sqlalchemy import exc @@ -1331,3 +1332,24 @@ class TableValuedRoundTripTest(fixtures.TestBase): connection.execute(stmt).all(), [(14, 1), (41, 2), (7, 3), (54, 4), (9, 5), (49, 6)], ) + + @testing.only_on( + "postgresql+psycopg2", + "I cannot get this to run at all on other drivers, " + "even selecting from a table", + ) + def test_render_derived_quoting(self, connection): + fn = ( + func.json_to_recordset( # noqa + '[{"CaseSensitive":1,"the % value":"foo"}, ' + '{"CaseSensitive":"2","the % value":"bar"}]' + ) + .table_valued( + column("CaseSensitive", Integer), column("the % value", String) + ) + .render_derived(with_types=True) + ) + + stmt = select(fn.c.CaseSensitive, fn.c["the % value"]) + + eq_(connection.execute(stmt).all(), [(1, "foo"), (2, "bar")]) diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index c5aca5d7fa..43b505c997 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -1638,6 +1638,28 @@ class TableValuedCompileTest(fixtures.TestBase, AssertsCompiledSQL): "AS anon_1(a INTEGER, b VARCHAR)", ) + def test_named_table_valued_w_quoting(self): + + fn = ( + func.json_to_recordset( # noqa + '[{"CaseSensitive":1,"the % value":"foo"}, ' + '{"CaseSensitive":"2","the % value":"bar"}]' + ) + .table_valued( + column("CaseSensitive", Integer), column("the % value", String) + ) + .render_derived(with_types=True) + ) + + stmt = select(fn.c.CaseSensitive, fn.c["the % value"]) + + self.assert_compile( + stmt, + 'SELECT anon_1."CaseSensitive", anon_1."the % value" ' + "FROM json_to_recordset(:json_to_recordset_1) " + 'AS anon_1("CaseSensitive" INTEGER, "the % value" VARCHAR)', + ) + def test_named_table_valued_subquery(self): fn = ( -- 2.47.2