Order of joins/laterals

[10bas10]

Hi All,

Im currently dealing with issues regarding the order of query modifiers.

Take for example this example in mind:

SELECT
 tbl1.field1
FROM tbl1
LEFT JOIN LATERAL (
	SELECT
	 tbl2.field1
	FROM tbl2
	WHERE 1=1
	AND tbl2.field1 = tbl1.field1
) tbl2
LEFT JOIN tbl3
	ON tbl3.field1 = tbl2.field1

However the output currently outputs the joins first and then the laterals:

SELECT
 tbl1.field1
FROM tbl1
LEFT JOIN tbl3
	ON tbl3.field1 = tbl2.field1
LEFT JOIN LATERAL (
	SELECT
	 tbl2.field1
	FROM tbl2
	WHERE 1=1
	AND tbl2.field1 = tbl1.field1
) tbl2

I was thinking of introducting an attribute "position" in both the laterals as well as the joins and sort the list when beeing outputted in the generator. The other way could be to have only "joins" for example, but that would require quite some rework i think. What do you guys think would be the best approach to fix this?

[georgesittas]

What SQLGlot version are you currently using? I'm getting a different behavior locally (v10.2.9):

>>> import sqlglot
>>> sql = """
... SELECT
...  tbl1.field1
... FROM tbl1
... LEFT JOIN LATERAL (
...     SELECT
...      tbl2.field1
...     FROM tbl2
...     WHERE 1=1
...     AND tbl2.field1 = tbl1.field1
... ) tbl2
... LEFT JOIN tbl3
...     ON tbl3.field1 = tbl2.field1
... """
>>>
>>> print(sqlglot.transpile(sql, pretty=True)[0])
SELECT
  tbl1.field1
FROM tbl1
LEFT JOIN LATERAL (
  SELECT
    tbl2.field1
  FROM tbl2
  WHERE
    1 = 1 AND tbl2.field1 = tbl1.field1
) AS tbl2
LEFT JOIN tbl3
  ON tbl3.field1 = tbl2.field1

[10bas10]

Heya @barakalon, yeah i had trouble on multiple parts mainly regarding laterals. On scoping, annotating types and qualifying columns. Have a couple of fixed now, but still working through the last kinks.

Would be great to get some help on the SCOPE part. The issues I detected for now are:

• Scoping of the subquery at laterals;
• Passing on the column aliases to the subquery for the qualify_columns;
• Supporting VALUES or UNION SELECT within the lateral subquery on qualify_columns;
• Supporting joins between parenthesis in qualify_columns/scope, e.g.:

SELECT
 tbl1.field1
,tbl2.field2
FROM tbl1
LEFT JOIN (
	tbl2
	INNER JOIN tbl3
		ON tbl3.field1 = tbl2.field1
)
	ON tbl1.field1 = tbl2.field1

Got some of them fixed, but you would probably see through the issues a lot faster and have a neat solution for them. Let me know if you have the time, else ill continue debugging/fixing them.

[barakalon]

Scoping of the subquery at laterals;

First, we need to "collect" laterals properly. Right now they're probably getting treated like regular subqueries.

And I'm assuming these are Postgres LATERAL joins? In which case they case reference previous tables in the chain. We do something similar for CTES.

Passing on the column aliases to the subquery for the qualify_columns;
Supporting VALUES or UNION SELECT within the lateral subquery on qualify_columns;

Those both might be solved if we handle scoping properly.

Supporting joins between parenthesis in qualify_columns/scope

I think this is a separate issue.

Got some of them fixed, but you would probably see through the issues a lot faster and have a neat solution for them. Let me know if you have the time, else ill continue debugging/fixing them.

I won't have time until January, as I'm about to disappear for vacation. Happy to help then.

[barakalon]

I also wonder if the optimizer should replace these lateral joins with something more standard...

[10bas10]

Scoping of the subquery at laterals;

First, we need to "collect" laterals properly. Right now they're probably getting treated like regular subqueries.

They are not detected currently due to the fact it is being handled like a UDTF. I guess it would be best to introduce a new scope type for lateral joins.

And I'm assuming these are Postgres LATERAL joins? In which case they case reference previous tables in the chain. We do something similar for CTES.

Indeed they are correlated subqueries. The difference with CTEs are that they can both reference attributes from CTEs and previous tables in the select.

Passing on the column aliases to the subquery for the qualify_columns;
Supporting VALUES or UNION SELECT within the lateral subquery on qualify_columns;

Those both might be solved if we handle scoping properly.

Supporting joins between parenthesis in qualify_columns/scope

I think this is a separate issue.

Got some of them fixed, but you would probably see through the issues a lot faster and have a neat solution for them. Let me know if you have the time, else ill continue debugging/fixing them.

I won't have time until January, as I'm about to disappear for vacation. Happy to help then.

Ill have a look at it the upcoming weeks then. Have a great vaction and narrow your scope of thoughts during 😄!

[barakalon]

They are not detected currently due to the fact it is being handled like a UDTF. I guess it would be best to introduce a new scope type for lateral joins.

Makes sense to me.

Indeed they are correlated subqueries. The difference with CTEs are that they can both reference attributes from CTEs and previous tables in the select.

Right. I mean we can use that "chain_sources" argument, similar to CTEs. We just need to make sure these are traversed after CTEs.

Ill have a look at it the upcoming weeks then. Have a great vaction and narrow your scope of thoughts during 😄!

Thanks 😄

[10bas10]

Ah found the problem btw @georgesittas regarding the order of laterals/joins. This is only currently occuring if the read dialect has the CROSS/OUTER APPLY. As the join class is being returned from the parse_lateral, therefore it is currently not beeing registered as a join at the select class and ends up at the end of the query modifiers. Ill send in a fix for this.