Repository: reata/sqllineage
Pull request #661 (2024-11-15 11:22:38)

fix: consistent lineage results
Closes https://github.com/reata/sqllineage/issues/652

* When resolving wildcards, we collect columns in a set which isn't guaranteed to be consistently order between executions
* When returning column lineage, we only sort by first and last element which leaves space for variation when more elements are in the lineage 

codecov[bot]: ## [Codecov](https://app.codecov.io/gh/reata/sqllineage/pull/661?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.55%. Comparing base [(`b5b5e10`)](https://app.codecov.io/gh/reata/sqllineage/commit/b5b5e10df55f0d153053f5e6fcb419dac89cd42d?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) to head [(`ed6ce89`)](https://app.codecov.io/gh/reata/sqllineage/commit/ed6ce8994737052628e3947b79758d7390b73c9c?dropdown=coverage&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     #661   +/-   ##
=======================================
  Coverage   99.55%   99.55%           
=======================================
  Files          41       41           
  Lines        2229     2237    +8     
=======================================
+ Hits         2219     2227    +8     
  Misses         10       10           
```

</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/reata/sqllineage/pull/661?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 6d841c4..62decf3 100644
--- a/sqllineage/core/parser/sqlfluff/utils.py
+++ b/sqllineage/core/parser/sqlfluff/utils.py
@@ -87,6 +87,18 @@ def list_join_clause(segment: BaseSegment) -> List[BaseSegment]:
     traverse from_clause, recursively goes into bracket by default
     """
     if segment.type in ["from_clause", "update_statement"]:
+        # for select from subquery, do not recursively go into subquery
+        if from_expression := segment.get_child("from_expression"):
+            join_clause = from_expression.get_child("join_clause")
+            if not join_clause:
+                try:
+                    next(from_expression.recursive_crawl("select_clause"))
+                except StopIteration:
+                    pass
+                else:
+                    # no join at top level, and there is select statement in from_clause
+                    return []
+        # otherwise, recursively find join_clause
         return list(segment.recursive_crawl("join_clause"))
     return []
 


Test patch:
diff --git a/tests/sql/column/test_metadata_wildcard.py b/tests/sql/column/test_metadata_wildcard.py
index 2d6c71a..81fa857 100644
--- a/tests/sql/column/test_metadata_wildcard.py
+++ b/tests/sql/column/test_metadata_wildcard.py
@@ -336,3 +336,21 @@ def test_wildcard_reference_from_previous_statements(provider: MetaDataProvider)
         ],
         metadata_provider=provider,
     )
+
+
+def test_output_consistency():
+    sql = """INSERT INTO tab_c
+SELECT *,
+       1 AS event_time
+FROM (SELECT tab_b.col_b AS col_a
+      FROM tab_b
+               JOIN tab_a) AS base"""
+    assert_column_lineage_equal(
+        sql,
+        [
+            (
+                ColumnQualifierTuple("col_b", "tab_b"),
+                ColumnQualifierTuple("col_a", "tab_c"),
+            ),
+        ],
+    )