diff options
| -rw-r--r-- | doc/build/changelog/unreleased_14/6863.rst | 10 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 2 | ||||
| -rw-r--r-- | test/dialect/mssql/test_compiler.py | 17 | ||||
| -rw-r--r-- | test/dialect/oracle/test_compiler.py | 23 | ||||
| -rw-r--r-- | test/sql/test_compiler.py | 18 |
5 files changed, 69 insertions, 1 deletions
diff --git a/doc/build/changelog/unreleased_14/6863.rst b/doc/build/changelog/unreleased_14/6863.rst new file mode 100644 index 000000000..8dc90c4ad --- /dev/null +++ b/doc/build/changelog/unreleased_14/6863.rst @@ -0,0 +1,10 @@ +.. change:: + :tags: bug, mssql, sql + :tickets: 6863 + + Fixed issue where the ``literal_binds`` compiler flag, as used externally + to render bound parameters inline, would fail to work when used with a + certain class of parameters known as "literal_execute", which covers things + like LIMIT and OFFSET values for dialects where the drivers don't allow a + bound parameter, such as SQL Server's "TOP" clause. The issue locally + seemed to affect only the MSSQL dialect. diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index a81507acb..af15de164 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -2297,7 +2297,7 @@ class SQLCompiler(Compiled): else: post_compile = False - if not literal_execute and (literal_binds): + if literal_binds: ret = self.render_literal_bindparam( bindparam, within_columns_clause=True, **kwargs ) diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py index 6afd90e87..cf8894f42 100644 --- a/test/dialect/mssql/test_compiler.py +++ b/test/dialect/mssql/test_compiler.py @@ -36,6 +36,7 @@ from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures from sqlalchemy.testing import is_ +from sqlalchemy.testing.assertions import eq_ignore_whitespace tbl = table("t", column("a")) @@ -976,6 +977,22 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): checkparams={"x_1": 5, "param_1": 10}, ) + def test_limit_using_top_literal_binds(self): + """test #6863""" + t = table("t", column("x", Integer), column("y", Integer)) + + s = select(t).where(t.c.x == 5).order_by(t.c.y).limit(10) + + eq_ignore_whitespace( + str( + s.compile( + dialect=mssql.dialect(), + compile_kwargs={"literal_binds": True}, + ) + ), + "SELECT TOP 10 t.x, t.y FROM t WHERE t.x = 5 ORDER BY t.y", + ) + def test_limit_zero_using_top(self): t = table("t", column("x", Integer), column("y", Integer)) diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py index e41a770ed..08158eed4 100644 --- a/test/dialect/oracle/test_compiler.py +++ b/test/dialect/oracle/test_compiler.py @@ -36,6 +36,7 @@ from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures +from sqlalchemy.testing.assertions import eq_ignore_whitespace from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table @@ -173,6 +174,28 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): eq_(len(c._result_columns), 2) assert t.c.col1 in set(c._create_result_map()["col1"][1]) + def test_limit_one_literal_binds(self): + """test for #6863. + + the bug does not appear to have affected Oracle's case. + + """ + t = table("sometable", column("col1"), column("col2")) + s = select(t).limit(10).offset(20) + c = s.compile( + dialect=oracle.OracleDialect(), + compile_kwargs={"literal_binds": True}, + ) + + eq_ignore_whitespace( + str(c), + "SELECT anon_1.col1, anon_1.col2 FROM " + "(SELECT anon_2.col1 AS col1, anon_2.col2 AS col2, " + "ROWNUM AS ora_rn FROM (SELECT sometable.col1 AS col1, " + "sometable.col2 AS col2 FROM sometable) anon_2 " + "WHERE ROWNUM <= 10 + 20) anon_1 WHERE ora_rn > 20", + ) + def test_limit_one_firstrows(self): t = table("sometable", column("col1"), column("col2")) s = select(t) diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index d270218b2..b150b9f64 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -4449,6 +4449,24 @@ class BindParameterTest(AssertsCompiledSQL, fixtures.TestBase): literal_binds=True, ) + def test_render_literal_execute_sent_parameter_literal_binds(self): + """test #6863""" + + stmt = select(table1.c.myid).where( + table1.c.myid == bindparam("foo", 5, literal_execute=True) + ) + eq_ignore_whitespace( + str( + stmt.compile( + compile_kwargs={ + "literal_binds": True, + "literal_execute": True, + } + ) + ), + "SELECT mytable.myid FROM mytable WHERE mytable.myid = 5", + ) + def test_render_literal_execute_parameter_render_postcompile(self): self.assert_compile( select(table1.c.myid).where( |