From bd3a65252d2f9155b7f2c1c6284074ba6e555d1f Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 19 Jan 2008 20:11:29 +0000 Subject: [PATCH] - Oracle assembles the correct columns in the result set column mapping when generating a LIMIT/OFFSET subquery, allows columns to map properly to result sets even if long-name truncation kicks in [ticket:941] --- CHANGES | 5 +++++ lib/sqlalchemy/databases/oracle.py | 2 +- lib/sqlalchemy/sql/compiler.py | 13 ++++++++++--- test/dialect/oracle.py | 9 +++++++++ test/sql/labels.py | 25 ++++++++++++++++++++++++- 5 files changed, 49 insertions(+), 5 deletions(-) diff --git a/CHANGES b/CHANGES index 7087bcc84a..5d2d833754 100644 --- a/CHANGES +++ b/CHANGES @@ -84,6 +84,11 @@ CHANGES - reflect the sequence associated to a PK field (tipically with a BEFORE INSERT trigger) under Firebird + - Oracle assembles the correct columns in the result set + column mapping when generating a LIMIT/OFFSET subquery, + allows columns to map properly to result sets even + if long-name truncation kicks in [ticket:941] + 0.4.2p3 ------ - general diff --git a/lib/sqlalchemy/databases/oracle.py b/lib/sqlalchemy/databases/oracle.py index c7ab134417..55bdf74b1f 100644 --- a/lib/sqlalchemy/databases/oracle.py +++ b/lib/sqlalchemy/databases/oracle.py @@ -675,7 +675,7 @@ class OracleCompiler(compiler.DefaultCompiler): limitselect.append_whereclause("ora_rn<=%d" % (select._limit + select._offset)) else: limitselect.append_whereclause("ora_rn<=%d" % select._limit) - return self.process(limitselect, **kwargs) + return self.process(limitselect, iswrapper=True, **kwargs) else: return compiler.DefaultCompiler.visit_select(self, select, **kwargs) diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 666a38d397..71bfd17658 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -468,13 +468,20 @@ class DefaultCompiler(engine.Compiled): else: return column - def visit_select(self, select, asfrom=False, parens=True, **kwargs): + def visit_select(self, select, asfrom=False, parens=True, iswrapper=False, **kwargs): stack_entry = {'select':select} - - if asfrom or (self.stack and 'select' in self.stack[-1]): + prev_entry = self.stack and self.stack[-1] or None + + if asfrom or (prev_entry and 'select' in prev_entry): stack_entry['is_subquery'] = True + if prev_entry and 'iswrapper' in prev_entry: + column_clause_args = {'result_map':self.result_map} + else: + column_clause_args = {} + elif iswrapper: column_clause_args = {} + stack_entry['iswrapper'] = True else: column_clause_args = {'result_map':self.result_map} diff --git a/test/dialect/oracle.py b/test/dialect/oracle.py index cc171af5f9..9436d1915a 100644 --- a/test/dialect/oracle.py +++ b/test/dialect/oracle.py @@ -41,12 +41,21 @@ class CompileTest(SQLCompileTest): def test_limit(self): t = table('sometable', column('col1'), column('col2')) + s = select([t]) + c = s.compile(dialect=oracle.OracleDialect()) + assert t.c.col1 in set(c.result_map['col1'][1]) + s = select([t]).limit(10).offset(20) self.assert_compile(s, "SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2, " "ROW_NUMBER() OVER (ORDER BY sometable.rowid) AS ora_rn FROM sometable) WHERE ora_rn>20 AND ora_rn<=30" ) + # assert that despite the subquery, the columns from the table, + # not the select, get put into the "result_map" + c = s.compile(dialect=oracle.OracleDialect()) + assert t.c.col1 in set(c.result_map['col1'][1]) + s = select([s.c.col1, s.c.col2]) self.assert_compile(s, "SELECT col1, col2 FROM (SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, " diff --git a/test/sql/labels.py b/test/sql/labels.py index 3d4f0adde5..8164d7f778 100644 --- a/test/sql/labels.py +++ b/test/sql/labels.py @@ -44,7 +44,8 @@ class LongLabelsTest(SQLCompileTest): table1.insert().execute(**{"this_is_the_primarykey_column":3, "this_is_the_data_column":"data3"}) table1.insert().execute(**{"this_is_the_primarykey_column":4, "this_is_the_data_column":"data4"}) - r = table1.select(use_labels=True, order_by=[table1.c.this_is_the_primarykey_column]).execute() + s = table1.select(use_labels=True, order_by=[table1.c.this_is_the_primarykey_column]) + r = s.execute() result = [] for row in r: result.append((row[table1.c.this_is_the_primarykey_column], row[table1.c.this_is_the_data_column])) @@ -55,6 +56,28 @@ class LongLabelsTest(SQLCompileTest): (4, "data4"), ], repr(result) + # some dialects such as oracle (and possibly ms-sql in a future version) + # generate a subquery for limits/offsets. + # ensure that the generated result map corresponds to the selected table, not + # the select query + r = s.limit(2).execute() + result = [] + for row in r: + result.append((row[table1.c.this_is_the_primarykey_column], row[table1.c.this_is_the_data_column])) + assert result == [ + (1, "data1"), + (2, "data2"), + ], repr(result) + + r = s.limit(2).offset(1).execute() + result = [] + for row in r: + result.append((row[table1.c.this_is_the_primarykey_column], row[table1.c.this_is_the_data_column])) + assert result == [ + (2, "data2"), + (3, "data3"), + ], repr(result) + def test_colbinds(self): table1.insert().execute(**{"this_is_the_primarykey_column":1, "this_is_the_data_column":"data1"}) table1.insert().execute(**{"this_is_the_primarykey_column":2, "this_is_the_data_column":"data2"}) -- 2.47.3