From de08740d7c21fa9dcef453bfd07a3defa428e88f Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 13 Jun 2019 16:35:12 -0400 Subject: Generate Oracle ROWNUM scheme using named subqueries The LIMIT / OFFSET scheme used in Oracle now makes use of named subqueries rather than unnamed subqueries when it transparently rewrites a SELECT statement to one that uses a subquery that includes ROWNUM. The change is part of a larger change where unnamed subqueries are no longer directly supported by Core, as well as to modernize the internal use of the select() construct within the Oracle dialect. Change-Id: I27605d7cf16ce79f9d577dbc84e3bd51b7c9b4ae --- lib/sqlalchemy/dialects/oracle/base.py | 53 ++++++++++++++++++++++++---------- 1 file changed, 38 insertions(+), 15 deletions(-) (limited to 'lib') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 9f0b23e12..33f9c8659 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -867,12 +867,28 @@ class OracleCompiler(compiler.SQLCompiler): # Outer select and "ROWNUM as ora_rn" can be dropped if # limit=0 - kwargs["select_wraps_for"] = select + kwargs["select_wraps_for"] = orig_select = select select = select._generate() select._oracle_visit = True + # add expressions to accommodate FOR UPDATE OF + for_update = select._for_update_arg + if for_update is not None and for_update.of: + for_update = for_update._clone() + for_update._copy_internals() + + for elem in for_update.of: + select = select.column(elem) + # Wrap the middle select and add the hint - limitselect = sql.select([c for c in select.c]) + inner_subquery = select.alias() + limitselect = sql.select( + [ + c + for c in inner_subquery.c + if orig_select.corresponding_column(c) is not None + ] + ) if ( limit_clause is not None and self.dialect.optimize_limits @@ -886,15 +902,9 @@ class OracleCompiler(compiler.SQLCompiler): limitselect._is_wrapper = True # add expressions to accommodate FOR UPDATE OF - for_update = select._for_update_arg if for_update is not None and for_update.of: - for_update = for_update._clone() - for_update._copy_internals() - - for elem in for_update.of: - select.append_column(elem) - adapter = sql_util.ClauseAdapter(select) + adapter = sql_util.ClauseAdapter(inner_subquery) for_update.of = [ adapter.traverse(elem) for elem in for_update.of ] @@ -913,7 +923,7 @@ class OracleCompiler(compiler.SQLCompiler): max_row = limit_clause if offset_clause is not None: max_row = max_row + offset_clause - limitselect.append_whereclause( + limitselect = limitselect.where( sql.literal_column("ROWNUM") <= max_row ) @@ -928,22 +938,35 @@ class OracleCompiler(compiler.SQLCompiler): limitselect._oracle_visit = True limitselect._is_wrapper = True + if for_update is not None and for_update.of: + + for elem in for_update.of: + if limitselect.corresponding_column(elem) is None: + limitselect = limitselect.column(elem) + + limit_subquery = limitselect.alias() offsetselect = sql.select( - [c for c in limitselect.c if c.key != "ora_rn"] + [ + c + for c in limit_subquery.c + if orig_select.corresponding_column(c) is not None + ] ) + offsetselect._oracle_visit = True offsetselect._is_wrapper = True if for_update is not None and for_update.of: - for elem in for_update.of: - if limitselect.corresponding_column(elem) is None: - limitselect.append_column(elem) + adapter = sql_util.ClauseAdapter(limit_subquery) + for_update.of = [ + adapter.traverse(elem) for elem in for_update.of + ] if not self.dialect.use_binds_for_limits: offset_clause = sql.literal_column( "%d" % select._offset ) - offsetselect.append_whereclause( + offsetselect = offsetselect.where( sql.literal_column("ora_rn") > offset_clause ) -- cgit v1.2.1