diff options
author | Mike Bayer <mike_mp@zzzcomputing.com> | 2014-04-30 19:06:26 -0400 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2014-04-30 19:07:45 -0400 |
commit | 2a458680a49376b8a11b342bc679a870cdc05da6 (patch) | |
tree | 53f80d9269d0d602a01b11b57e5db518f3d0e2b3 | |
parent | 1caa7fafbd3c9870e1bf2b2ac623872447fc804e (diff) | |
download | sqlalchemy-2a458680a49376b8a11b342bc679a870cdc05da6.tar.gz |
- Fixed bug where the combination of "limit" rendering as
"SELECT FIRST n ROWS" using a bound parameter (only firebird has both),
combined with column-level subqueries
which also feature "limit" as well as "positional" bound parameters
(e.g. qmark style) would erroneously assign the subquery-level positions
before that of the enclosing SELECT, thus returning parameters which
are out of order. Fixes #3038
-rw-r--r-- | doc/build/changelog/changelog_09.rst | 10 | ||||
-rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 27 | ||||
-rw-r--r-- | test/dialect/test_firebird.py | 7 | ||||
-rw-r--r-- | test/sql/test_compiler.py | 29 |
4 files changed, 58 insertions, 15 deletions
diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst index f51244815..cd8cb0ac1 100644 --- a/doc/build/changelog/changelog_09.rst +++ b/doc/build/changelog/changelog_09.rst @@ -36,6 +36,16 @@ needs to have a table-bound column present if the index is to be manually added to the table, either via inline declaration or via :meth:`.Table.append_constraint`. + :tags: bug, firebird + :tickets: 3038 + + Fixed bug where the combination of "limit" rendering as + "SELECT FIRST n ROWS" using a bound parameter (only firebird has both), + combined with column-level subqueries + which also feature "limit" as well as "positional" bound parameters + (e.g. qmark style) would erroneously assign the subquery-level positions + before that of the enclosing SELECT, thus returning parameters which + are out of order. .. change:: :tags: bug, mssql diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 169dc2cc3..a7465204a 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1481,19 +1481,6 @@ class SQLCompiler(Compiled): 'within_columns_clause': False }) - # the actual list of columns to print in the SELECT column list. - inner_columns = [ - c for c in [ - self._label_select_column(select, - column, - populate_result_map, asfrom, - column_clause_args, - name=name) - for name, column in select._columns_plus_names - ] - if c is not None - ] - text = "SELECT " # we're off to a good start ! if select._hints: @@ -1514,6 +1501,20 @@ class SQLCompiler(Compiled): text += self._generate_prefixes(select, select._prefixes, **kwargs) text += self.get_select_precolumns(select) + + # the actual list of columns to print in the SELECT column list. + inner_columns = [ + c for c in [ + self._label_select_column(select, + column, + populate_result_map, asfrom, + column_clause_args, + name=name) + for name, column in select._columns_plus_names + ] + if c is not None + ] + text += ', '.join(inner_columns) if froms: diff --git a/test/dialect/test_firebird.py b/test/dialect/test_firebird.py index 222e34b93..86464c8cb 100644 --- a/test/dialect/test_firebird.py +++ b/test/dialect/test_firebird.py @@ -415,8 +415,8 @@ class MiscTest(fixtures.TestBase): @testing.provide_metadata def test_rowcount_flag(self): metadata = self.metadata - engine = engines.testing_engine(options={'enable_rowcount' - : True}) + engine = engines.testing_engine( + options={'enable_rowcount': True}) assert engine.dialect.supports_sane_rowcount metadata.bind = engine t = Table('t1', metadata, Column('data', String(10))) @@ -431,6 +431,7 @@ class MiscTest(fixtures.TestBase): r = \ t.delete().execution_options(enable_rowcount=False).execute() eq_(r.rowcount, -1) + engine.dispose() engine = engines.testing_engine(options={'enable_rowcount' : False}) assert not engine.dialect.supports_sane_rowcount @@ -444,6 +445,8 @@ class MiscTest(fixtures.TestBase): eq_(r.rowcount, -1) r = t.delete().execution_options(enable_rowcount=True).execute() eq_(r.rowcount, 1) + r.close() + engine.dispose() def test_percents_in_text(self): for expr, result in (text("select '%' from rdb$database"), '%' diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index e96990c61..1be76c696 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -28,6 +28,7 @@ from sqlalchemy.engine import default from sqlalchemy.dialects import mysql, mssql, postgresql, oracle, \ sqlite, sybase from sqlalchemy.ext.compiler import compiles +from sqlalchemy.sql import compiler table1 = table('mytable', column('myid', Integer), @@ -181,6 +182,34 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): checkparams=params ) + def test_select_precol_compile_ordering(self): + s1 = select([column('x')]).select_from('a').limit(5).as_scalar() + s2 = select([s1]).limit(10) + + class MyCompiler(compiler.SQLCompiler): + def get_select_precolumns(self, select): + result = "" + if select._limit: + result += "FIRST %s " % self.process(literal(select._limit)) + if select._offset: + result += "SKIP %s " % self.process(literal(select._offset)) + return result + + def limit_clause(self, select): + return "" + + dialect = default.DefaultDialect() + dialect.statement_compiler = MyCompiler + dialect.paramstyle = 'qmark' + dialect.positional = True + self.assert_compile( + s2, + "SELECT FIRST ? (SELECT FIRST ? x FROM a) AS anon_1", + checkpositional=(10, 5), + dialect=dialect + ) + + def test_from_subquery(self): """tests placing select statements in the column clause of another select, for the |