diff options
Diffstat (limited to 'sqlglot/optimizer/qualify_tables.py')
| -rw-r--r-- | sqlglot/optimizer/qualify_tables.py | 34 |
1 files changed, 19 insertions, 15 deletions
diff --git a/sqlglot/optimizer/qualify_tables.py b/sqlglot/optimizer/qualify_tables.py index 9c931d6..af8c716 100644 --- a/sqlglot/optimizer/qualify_tables.py +++ b/sqlglot/optimizer/qualify_tables.py @@ -15,8 +15,7 @@ def qualify_tables( schema: t.Optional[Schema] = None, ) -> E: """ - Rewrite sqlglot AST to have fully qualified tables. Additionally, this - replaces "join constructs" (*) by equivalent SELECT * subqueries. + Rewrite sqlglot AST to have fully qualified, unnested tables. Examples: >>> import sqlglot @@ -24,9 +23,18 @@ def qualify_tables( >>> qualify_tables(expression, db="db").sql() 'SELECT 1 FROM db.tbl AS tbl' >>> + >>> expression = sqlglot.parse_one("SELECT * FROM (tbl)") + >>> qualify_tables(expression).sql() + 'SELECT * FROM tbl AS tbl' + >>> >>> expression = sqlglot.parse_one("SELECT * FROM (tbl1 JOIN tbl2 ON id1 = id2)") >>> qualify_tables(expression).sql() - 'SELECT * FROM (SELECT * FROM tbl1 AS tbl1 JOIN tbl2 AS tbl2 ON id1 = id2) AS _q_0' + 'SELECT * FROM tbl1 AS tbl1 JOIN tbl2 AS tbl2 ON id1 = id2' + + Note: + This rule effectively enforces a left-to-right join order, since all joins + are unnested. This means that the optimizer doesn't necessarily preserve the + original join order, e.g. when parentheses are used to specify it explicitly. Args: expression: Expression to qualify @@ -36,19 +44,11 @@ def qualify_tables( Returns: The qualified expression. - - (*) See section 7.2.1.2 in https://www.postgresql.org/docs/current/queries-table-expressions.html """ next_alias_name = name_sequence("_q_") for scope in traverse_scope(expression): for derived_table in itertools.chain(scope.ctes, scope.derived_tables): - # Expand join construct - if isinstance(derived_table, exp.Subquery): - unnested = derived_table.unnest() - if isinstance(unnested, exp.Table): - derived_table.this.replace(exp.select("*").from_(unnested.copy(), copy=False)) - if not derived_table.args.get("alias"): alias_ = next_alias_name() derived_table.set("alias", exp.TableAlias(this=exp.to_identifier(alias_))) @@ -66,13 +66,17 @@ def qualify_tables( if not source.args.get("catalog"): source.set("catalog", exp.to_identifier(catalog)) + # Unnest joins attached in tables by appending them to the closest query + for join in source.args.get("joins") or []: + scope.expression.append("joins", join) + + source.set("joins", None) + source.set("wrapped", None) + if not source.alias: source = source.replace( alias( - source, - name or source.name or next_alias_name(), - copy=True, - table=True, + source, name or source.name or next_alias_name(), copy=True, table=True ) ) |