Repository: reata/sqllineage
Pull request #615 (2024-05-20 04:06:24)

fix: exception for lateral subquery
making progress in #613 

codecov[bot]: ## [Codecov](https://app.codecov.io/gh/reata/sqllineage/pull/615?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) Report
All modified and coverable lines are covered by tests :white_check_mark:
> Project coverage is 99.54%. Comparing base [(`1a434fd`)](https://app.codecov.io/gh/reata/sqllineage/commit/1a434fdff29fd342ccdf3983a6c7573a0400f552?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) to head [(`5b23a96`)](https://app.codecov.io/gh/reata/sqllineage/pull/615?dropdown=coverage&src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None).


<details><summary>Additional details and impacted files</summary>


```diff
@@           Coverage Diff           @@
##           master     #615   +/-   ##
=======================================
  Coverage   99.54%   99.54%           
=======================================
  Files          41       41           
  Lines        2195     2198    +3     
=======================================
+ Hits         2185     2188    +3     
  Misses         10       10           
```



</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/reata/sqllineage/pull/615?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None).


Patch:
diff --git a/sqllineage/core/parser/sqlfluff/utils.py b/sqllineage/core/parser/sqlfluff/utils.py
index bdeaa06..56c1689 100644
--- a/sqllineage/core/parser/sqlfluff/utils.py
+++ b/sqllineage/core/parser/sqlfluff/utils.py
@@ -214,8 +214,11 @@ def extract_as_and_target_segment(
 ) -> Tuple[Optional[BaseSegment], BaseSegment]:
     as_segment = segment.get_child("alias_expression")
     sublist = list_child_segments(segment, False)
-    target = sublist[0] if is_subquery(sublist[0]) else sublist[0].segments[0]
-    return as_segment, target
+    target = sublist[0]
+    if target.type == "keyword" and target.raw_upper == "LATERAL":
+        target = sublist[1]
+    table_expr = target if is_subquery(target) else target.segments[0]
+    return as_segment, table_expr
 
 
 def extract_column_qualifier(segment: BaseSegment) -> Optional[ColumnQualifierTuple]:


Test patch:
diff --git a/tests/sql/table/test_select_dialect_specific.py b/tests/sql/table/test_select_dialect_specific.py
index 14aefc6..9755fa5 100644
--- a/tests/sql/table/test_select_dialect_specific.py
+++ b/tests/sql/table/test_select_dialect_specific.py
@@ -83,3 +83,28 @@ def test_select_from_unnest_with_ordinality(dialect: str):
     CROSS JOIN UNNEST(numbers) WITH ORDINALITY AS t (n, a);
     """
     assert_table_lineage_equal(sql, dialect=dialect)
+
+
+@pytest.mark.parametrize(
+    "dialect",
+    ["db2", "duckdb", "greenplum", "materialize", "oracle", "postgres", "snowflake"],
+)
+def test_select_from_lateral_subqueries(dialect: str):
+    """
+    db2: https://www.ibm.com/docs/en/informix-servers/12.10?topic=clause-lateral-derived-tables
+    duckdb: https://duckdb.org/docs/sql/query_syntax/from.html#lateral-joins
+    greenplum: https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/ref_guide-sql_commands-SELECT.html
+    materialize: https://materialize.com/docs/transform-data/join/#lateral-subqueries
+    oracle: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__BABFDGIJ  # noqa
+    postgres: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL
+    snowflake: https://docs.snowflake.com/en/sql-reference/constructs/join-lateral
+    """
+    sql = """SELECT *
+FROM foo,
+     LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss"""
+    assert_table_lineage_equal(sql, {"foo", "bar"}, dialect=dialect)
+
+    sql = """SELECT m.name
+FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
+WHERE pname IS NULL;"""
+    assert_table_lineage_equal(sql, {"manufacturers"}, dialect=dialect)