Nested joins

[10bas10]

Hey Guys,

Im currently taking a look at nested joins, so for example:

SELECT
 a.id
,b.test
,c.test2
FROM a
LEFT JOIN (
    b
    INNER JOIN c
        ON c.id = b.id
)
    ON b.id = a.id

In the query above a INNER JOIN is performed first between table b and c, and then a LEFT JOIN is performed with that set.

Currently the AST is as like:

[(SELECT expressions: 
  (COLUMN this: 
    (IDENTIFIER this: id, quoted: False), table: 
    (IDENTIFIER this: a, quoted: False)), 
  (COLUMN this: 
    (IDENTIFIER this: test, quoted: False), table: 
    (IDENTIFIER this: b, quoted: False)), 
  (COLUMN this: 
    (IDENTIFIER this: test2, quoted: False), table: 
    (IDENTIFIER this: c, quoted: False)), from: 
  (FROM this: 
    (TABLE this: 
      (IDENTIFIER this: a, quoted: False))), joins: 
  (JOIN this: 
    (SUBQUERY this: 
      (TABLE this: 
        (IDENTIFIER this: b, quoted: False), joins: 
        (JOIN this: 
          (TABLE this: 
            (IDENTIFIER this: c, quoted: False)), kind: INNER, on: 
          (EQ this: 
            (COLUMN this: 
              (IDENTIFIER this: id, quoted: False), table: 
              (IDENTIFIER this: c, quoted: False)), expression: 
            (COLUMN this: 
              (IDENTIFIER this: id, quoted: False), table: 
              (IDENTIFIER this: b, quoted: False)))))), side: LEFT, on: 
    (EQ this: 
      (COLUMN this: 
        (IDENTIFIER this: id, quoted: False), table: 
        (IDENTIFIER this: b, quoted: False)), expression: 
      (COLUMN this: 
        (IDENTIFIER this: id, quoted: False), table: 
        (IDENTIFIER this: a, quoted: False)))))]

I would however expect something like this:

[(SELECT expressions: 
  (COLUMN this: 
    (IDENTIFIER this: id, quoted: False), table: 
    (IDENTIFIER this: a, quoted: False)), 
  (COLUMN this: 
    (IDENTIFIER this: test, quoted: False), table: 
    (IDENTIFIER this: b, quoted: False)), 
  (COLUMN this: 
    (IDENTIFIER this: test2, quoted: False), table: 
    (IDENTIFIER this: c, quoted: False)), from: 
  (FROM this: 
    (TABLE this: 
      (IDENTIFIER this: a, quoted: False))), joins: 
  (JOIN this: 
    (TABLE this: 
      (IDENTIFIER this: b, quoted: False), joins: 
      (JOIN this: 
        (TABLE this: 
          (IDENTIFIER this: c, quoted: False)), kind: INNER, on: 
        (EQ this: 
          (COLUMN this: 
            (IDENTIFIER this: id, quoted: False), table: 
            (IDENTIFIER this: c, quoted: False)), expression: 
          (COLUMN this: 
            (IDENTIFIER this: id, quoted: False), table: 
            (IDENTIFIER this: b, quoted: False))))), side: LEFT, on: 
    (EQ this: 
      (COLUMN this: 
        (IDENTIFIER this: id, quoted: False), table: 
        (IDENTIFIER this: b, quoted: False)), expression: 
      (COLUMN this: 
        (IDENTIFIER this: id, quoted: False), table: 
        (IDENTIFIER this: a, quoted: False)))))]

The AST above however loses the parenthesis in case its generated:

SELECT
  a.id,
  b.test,
  c.test2
FROM a
LEFT JOIN b  
  INNER JOIN c
    ON c.id = b.id
  ON b.id = a.id

Im thinking about have it parsing like the second AST and than in generation of the SQL it would create the parenthesis in case the join entity has underlying joins as well.

So in this case the table b has joins on itself, therefore parenthesis would be required at the join between table b and c.

This way such AST's can be optimized as well, because i got errors while optimizing due to directly having a table reference in a subquery, while a select for example is expected there.

What do you guys think?

[georgesittas]

Hey Sebastiaan :)

I agree that the current form of the AST is a bit awkward, but IIRC the Subquery node is used because Table doesn't have a "joins" arg, which makes sense imo (*). So, having the Subquery there makes things a bit more consistent.

The AST above however loses the parenthesis in case its generated:

Why does it lose the parentheses? I can't reproduce this:

>>> import sqlglot
>>>
>>> sqlglot.transpile("""
... SELECT
...  a.id
... ,b.test
... ,c.test2
... FROM a
... LEFT JOIN (
...     b
...     INNER JOIN c
...         ON c.id = b.id
... )
...     ON b.id = a.id
... """, "postgres")
['SELECT a.id, b.test, c.test2 FROM a LEFT JOIN (b INNER JOIN c ON c.id = b.id) ON b.id = a.id']

This way such AST's can be optimized as well, because i got errors while optimizing due to directly having a table reference in a subquery, while a select for example is expected there.

This might be a bug on our side. I recently implemented a transformation in qualify_tables that expands these so-called "join constructs" (postgres lingo) into SELECT * FROM tbl subqueries, so that they can later be handled like any other joined table:

sqlglot/sqlglot/optimizer/qualify_tables.py

Lines 46 to 50 in 146dc34

	# 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))

Do you have a query that fails to be optimized?

[10bas10]

Hey George,

Thanks for your response. Well actually the table class does have a property "joins", however it does not seem supported by the optimizer.
The following code is failing for example, as the table b and c does not exist within the most outer scope of the query due to the fact it is embedded in a subquery.

import sqlglot
from sqlglot import optimizer
expr = sqlglot.parse_one("""
SELECT
 a.id
,b.test
,c.test2
FROM a
LEFT JOIN (
    b
    INNER JOIN c
        ON c.id = b.id
)
    ON b.id = a.id
""", read="tsql")

# first_join = expr.args.get("joins")[0]
# table = first_join.this.this
# first_join.set("this", table)

schema={
    "testcat": {
        "testdb": {
            "a": {
                "id": "INT",
            },
            "b": {
                "id": "INT",
                "test": "STRING",
            },
            "c": {
                "id": "INT",
                "test2": "STRING",
            },
        }
    }
}

expr = optimizer.optimize(
    expression=expr,
    schema=schema,
)

dbx = sqlglot.Dialect.get_or_raise("postgres")()
dbx_tfm = dbx.Generator.TRANSFORMS

print(dbx.generate(expr, pretty=True))

Btw the nested join can also be written without the parenthesis, like:

SELECT
 a.id
,b.test
,c.test2
FROM a
LEFT JOIN 
    b
    INNER JOIN c
        ON c.id = b.id
    ON b.id = a.id

That also results in an error however on parsing ON b.id = a.id as this is expected directly after the table/subquery reference.

I think using the joins arg at the the table class would be nicest here as it is actually related to that table. However subqueries do not have this arg yet. So that would involve adjusting the parser so those nested joins are part of the joins arg at the exp.Table/exp.Subquery and adjusting the scope class so those joins are included.

What do you think?

[georgesittas]

Thanks for your response. Well actually the table class does have a property "joins", however it does not seem supported by the optimizer.

Idk how I missed this, but you're right! 🤦 It seems like this argument is only used when parsing UPDATE statements -- @tobymao do you remember why we added this?

However subqueries do not have this arg yet.

They have it too actually, it's under QUERY_MODIFIERS.

---

@10bas10 I'll take a look at your suggestion, maybe it makes sense to implement it but I'd need to investigate a bit more first. In the meantime, could you please open a couple of tickets for the issues you mentioned so we can track their progress more easily? They are:

• The optimizer error
• The parser error when there's an ON clause right after a join.

[georgesittas]

Fixed in 156afcd and 3a8f230.