Repository: tobymao/sqlglot
Pull request: Support lateral with subqueries
Fixes #424 

Problem statement:
[Postgres 14.4] Parser does not support subquery after lateral keyword
Example (valid sql):
```
select art from table1 inner join lateral (select art from table2) table2 on table2.art=table2.art
```
Result:
```
ParseError: Expected table name. Line 1, Col: 35.
  select art from table1 inner join lateral (select art from table2) table2 on table2.art=table2.art;

Required keyword: 'this' missing for <class 'sqlglot.expressions.Table'>. Line 1, Col: 35.
  select art from table1 inner join lateral (select art from table2) table2 on table2.art=table2.art;

Expected VIEW after LATERAL. Line 1, Col: 43.
  select art from table1 inner join lateral (select art from table2) table2 on table2.art=table2.art;

... and 2 more
```

Hints from issue discussion:

Commit message: Support lateral with subqueries

Patch to review:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index b569e12354..bb4d67647b 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -630,6 +630,8 @@ def lambda_sql(self, expression):
 
     def lateral_sql(self, expression):
         this = self.sql(expression, "this")
+        if isinstance(expression.this, exp.Subquery):
+            return f"LATERAL{self.sep()}{this}"
         op_sql = self.seg(
             f"LATERAL VIEW{' OUTER' if expression.args.get('outer') else ''}"
         )
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 630112665b..a264aa3bfd 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1070,8 +1070,12 @@ def _parse_lateral(self):
         if not self._match(TokenType.LATERAL):
             return None
 
-        if not self._match(TokenType.VIEW):
-            self.raise_error("Expected VIEW after LATERAL")
+        subquery = self._parse_select(table=True)
+
+        if subquery:
+            return self.expression(exp.Lateral, this=subquery)
+        elif not self._match(TokenType.VIEW):
+            self.raise_error("Expected subquery or VIEW after LATERAL")
 
         outer = self._match(TokenType.OUTER)
 
@@ -1105,7 +1109,12 @@ def _parse_join(self):
         if not self._match(TokenType.JOIN):
             return None
 
-        kwargs = {"this": self._parse_table()}
+        this = (
+            self._parse_lateral()
+            if self._curr and self._curr.token_type == TokenType.LATERAL
+            else self._parse_table()
+        )
+        kwargs = {"this": this}
 
         if side:
             kwargs["side"] = side.text
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 658d942bb2..d81b8b8db7 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -629,6 +629,14 @@ def test_cross_join(self):
             },
         )
 
+    def test_lateral_subquery(self):
+        self.validate_identity(
+            "SELECT art FROM tbl1 INNER JOIN LATERAL (SELECT art FROM tbl2) AS tbl2 ON tbl1.art = tbl2.art"
+        )
+        self.validate_identity(
+            "SELECT * FROM tbl AS t LEFT JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) AS t ON TRUE"
+        )
+
     def test_set_operators(self):
         self.validate_all(
             "SELECT * FROM a UNION SELECT * FROM b",


Code review comments:
diff_hunk: @@ -1105,7 +1109,12 @@ def _parse_join(self):
         if not self._match(TokenType.JOIN):
             return None
 
-        kwargs = {"this": self._parse_table()}
+        this = (
+            self._parse_lateral()
+            if self._curr and self._curr.token_type == TokenType.LATERAL
+            else self._parse_table()
+        )
+        kwargs = {"this": this}
path: sqlglot/parser.py
text: @user1:
this could just be {"this": self._parse_lateral() or self._parse_table()} right?

@author:
Yeah good point, will change it on the next commit