diff options
| -rw-r--r-- | lib/sqlalchemy/ansisql.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/oracle.py | 20 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql.py | 2 |
3 files changed, 20 insertions, 6 deletions
diff --git a/lib/sqlalchemy/ansisql.py b/lib/sqlalchemy/ansisql.py index 30060a93c..fc4af5198 100644 --- a/lib/sqlalchemy/ansisql.py +++ b/lib/sqlalchemy/ansisql.py @@ -319,7 +319,9 @@ class ANSICompiler(sql.Compiled): text += " \nWHERE " + t for tup in select.clauses: - text += " " + tup[0] + " " + self.get_str(tup[1]) + ss = self.get_str(tup[1]) + if ss: + text += " " + tup[0] + " " + ss if select.having is not None: t = self.get_str(select.having) diff --git a/lib/sqlalchemy/databases/oracle.py b/lib/sqlalchemy/databases/oracle.py index de4a76c3e..857b0c2fc 100644 --- a/lib/sqlalchemy/databases/oracle.py +++ b/lib/sqlalchemy/databases/oracle.py @@ -191,17 +191,29 @@ class OracleCompiler(ansisql.ANSICompiler): def visit_select(self, select): """looks for LIMIT and OFFSET in a select statement, and if so tries to wrap it in a - subquery with rownum criterion.""" + subquery with row_number() criterion.""" + # TODO: put a real copy-container on Select and copy, or somehow make this + # not modify the Select statement if getattr(select, '_oracle_visit', False): + # cancel out the compiled order_by on the select + if hasattr(select, "order_by_clause"): + self.strings[select.order_by_clause] = "" ansisql.ANSICompiler.visit_select(self, select) return if select.limit is not None or select.offset is not None: select._oracle_visit = True + if hasattr(select, "order_by_clause"): + orderby = self.strings[select.order_by_clause] + else: + orderby = "rowid ASC" + select.append_column(sql.ColumnClause("ROW_NUMBER() OVER (ORDER BY %s)" % orderby).label("ora_rn")) limitselect = select.select() - if select.limit is not None: - limitselect.append_whereclause("rownum<%d" % select.limit) if select.offset is not None: - limitselect.append_whereclause("rownum>%d" % select.offset) + limitselect.append_whereclause("ora_rn>%d" % select.offset) + if select.limit is not None: + limitselect.append_whereclause("ora_rn<=%d" % (select.limit + select.offset)) + else: + limitselect.append_whereclause("ora_rn<=%d" % select.limit) limitselect.accept_visitor(self) self.strings[select] = self.strings[limitselect] self.froms[select] = self.froms[limitselect] diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index 8be40ac1d..c8a05470c 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -987,7 +987,7 @@ class ColumnClause(ColumnElement): else: return BindParamClause(self.table.name + "_" + self.text, obj, shortname = self.text, type=self.type) def _make_proxy(self, selectable, name = None): - c = ColumnClause(self.text or name, selectable) + c = ColumnClause(name or self.text, selectable) selectable.columns[c.key] = c return c |