Repository: tobymao/sqlglot

Issue:
LATERAL VIEW EXPLODE / INLINE incorrectly translated into Trino
**LATERAL VIEW EXPLODE**

This working Spark SQL gets incorrectly translated when the exploded array is of struct type.

**Spark SQL**
```
WITH test_table AS (
  SELECT 
    12345 AS id_column,
    ARRAY(
      STRUCT('John' AS name, 30 AS age),
      STRUCT('Mary' AS name, 20 AS age),
      STRUCT('Mike' AS name, 80 AS age),
      STRUCT('Dan' AS name, 50 AS age)
    ) AS struc_column 
)

SELECT
    id_column,
    new_column.name,
    new_column.age
FROM test_table
LATERAL VIEW EXPLODE(struc_column) explode_view AS new_column
```

<img width="291" alt="Image" src="https://github.com/user-attachments/assets/7ab58005-9fad-4a61-b654-55fe031bd704" />

**Translated version in Trino SQL**

```
WITH test_table AS (
  SELECT
    12345 AS id_column,
    ARRAY[CAST(ROW('John', 30) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Mary', 20) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Mike', 80) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Dan', 50) AS ROW(name VARCHAR, age INTEGER))] AS struc_column
)
SELECT
  id_column,
  new_column.name,
  new_column.age
FROM test_table
CROSS JOIN UNNEST(struc_column) AS explode_view(new_column)
```

Trino fails with:
```
trino error: TrinoUserError(type=USER_ERROR, name=MISMATCHED_COLUMN_ALIASES, message="line 11:12: Column alias list has 1 entries but 'explode_view' has 2 columns available"
```

Expected result is: 
```
WITH test_table AS (
  SELECT
    12345 AS id_column,
    ARRAY[CAST(ROW('John', 30) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Mary', 20) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Mike', 80) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Dan', 50) AS ROW(name VARCHAR, age INTEGER))] AS struc_column
)
SELECT
  id_column,
  name,
  age
FROM test_table
CROSS JOIN UNNEST(struc_column) AS explode_view(name, age)
```

<img width="533" alt="Image" src="https://github.com/user-attachments/assets/bfda9334-a536-4ac6-97b7-d54325bbc77d" />

**LATERAL VIEW INLINE**
The INLINE version of the LATERAL VIEW is not supported at all. 

Spark SQL
```
WITH test_table AS (
  SELECT 
    12345 AS id_column,
    ARRAY(
      STRUCT('John' AS name, 30 AS age),
      STRUCT('Mary' AS name, 20 AS age),
      STRUCT('Mike' AS name, 80 AS age),
      STRUCT('Dan' AS name, 50 AS age)
    ) AS struc_column 
)

SELECT
    id_column,
    name,
    age
FROM test_table
LATERAL VIEW INLINE(struc_column) explode_view AS name, age
```

**Translated version in Trino SQL**
```
WITH test_table AS (
  SELECT
    12345 AS id_column,
    ARRAY[CAST(ROW('John', 30) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Mary', 20) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Mike', 80) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Dan', 50) AS ROW(name VARCHAR, age INTEGER))] AS struc_column
)
SELECT
  id_column,
  name,
  age
FROM test_table
LATERAL VIEW
INLINE(struc_column) explode_view AS name, age
```

There is no translation at all.

Expected result is: 

```
WITH test_table AS (
  SELECT
    12345 AS id_column,
    ARRAY[CAST(ROW('John', 30) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Mary', 20) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Mike', 80) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Dan', 50) AS ROW(name VARCHAR, age INTEGER))] AS struc_column
)
SELECT
  id_column,
  name,
  age
FROM test_table
CROSS JOIN UNNEST(struc_column) AS explode_view(name, age)
```

**Official Documentation**
Trino [documentation](https://trino.io/docs/current/sql/select.html#unnest) for UNNEST

Discussion:
@georgesittas: I'll take a stab at enabling the transpilation for your first example, but it won't be possible without using `qualify` + `annotate_types`, so I'm gonna close this as not planned for now. If it's a low lift to enable a type-aware transpilation, I'll update this thread to show you how it could be done.@Juanpeterjuanpa: Hi @georgesittas, thanks for looking into it! Would it be easier to fix INLINE probably? It seems that the translation would be more straight forward as the columns are explicitly written in the SQL as opposed to EXPLODE which I agree it needs to be type aware. @georgesittas: @Juanpeterjuanpa finally found time to address this, check out the linked PR.@Juanpeterjuanpa: Thanks @georgesittas? Appreciate it :) 

Solution patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 331093e0a9..cd352fe408 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -941,7 +941,7 @@ class Generator(generator.Generator):
             exp.Rollback: lambda *_: "ROLLBACK TRANSACTION",
             exp.Select: transforms.preprocess(
                 [
-                    transforms.explode_to_unnest(),
+                    transforms.explode_projection_to_unnest(),
                     transforms.unqualify_unnest,
                     transforms.eliminate_distinct_on,
                     _alias_ordered_group,
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index 8436e64972..cab267b5a6 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -34,6 +34,7 @@
 from sqlglot.dialects.hive import Hive
 from sqlglot.dialects.mysql import MySQL
 from sqlglot.helper import apply_index_offset, seq_get
+from sqlglot.optimizer.scope import find_all_in_scope
 from sqlglot.tokens import TokenType
 from sqlglot.transforms import unqualify_columns
 from sqlglot.generator import unsupported_args
@@ -188,6 +189,56 @@ def _delta_sql(self: Presto.Generator, expression: DATE_ADD_OR_SUB) -> str:
     return _delta_sql
 
 
+def _explode_to_unnest_sql(self: Presto.Generator, expression: exp.Lateral) -> str:
+    explode = expression.this
+    if isinstance(explode, exp.Explode):
+        exploded_type = explode.this.type
+        alias = expression.args.get("alias")
+
+        # This attempts a best-effort transpilation of LATERAL VIEW EXPLODE on a struct array
+        if (
+            isinstance(alias, exp.TableAlias)
+            and isinstance(exploded_type, exp.DataType)
+            and exploded_type.is_type(exp.DataType.Type.ARRAY)
+            and exploded_type.expressions
+            and exploded_type.expressions[0].is_type(exp.DataType.Type.STRUCT)
+        ):
+            # When unnesting a ROW in Presto, it produces N columns, so we need to fix the alias
+            alias.set("columns", [c.this.copy() for c in exploded_type.expressions[0].expressions])
+    elif isinstance(explode, exp.Inline):
+        explode.replace(exp.Explode(this=explode.this.copy()))
+
+    return explode_to_unnest_sql(self, expression)
+
+
+def _amend_exploded_column_table(expression: exp.Expression) -> exp.Expression:
+    # We check for expression.type because the columns can be amended only if types were inferred
+    if isinstance(expression, exp.Select) and expression.type:
+        for lateral in expression.args.get("laterals") or []:
+            alias = lateral.args.get("alias")
+            if (
+                not isinstance(lateral.this, exp.Explode)
+                or not isinstance(alias, exp.TableAlias)
+                or len(alias.columns) != 1
+            ):
+                continue
+
+            new_table = alias.this
+            old_table = alias.columns[0].name.lower()
+
+            # When transpiling a LATERAL VIEW EXPLODE Spark query, the exploded fields may be qualified
+            # with the struct column, resulting in invalid Presto references that need to be amended
+            for column in find_all_in_scope(expression, exp.Column):
+                if column.db.lower() == old_table:
+                    column.set("table", column.args["db"].pop())
+                elif column.table.lower() == old_table:
+                    column.set("table", new_table.copy())
+                elif column.name.lower() == old_table and isinstance(column.parent, exp.Dot):
+                    column.parent.replace(exp.column(column.parent.expression, table=new_table))
+
+    return expression
+
+
 class Presto(Dialect):
     INDEX_OFFSET = 1
     NULL_ORDERING = "nulls_are_last"
@@ -403,7 +454,7 @@ class Generator(generator.Generator):
             exp.Initcap: _initcap_sql,
             exp.Last: _first_last_sql,
             exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this),
-            exp.Lateral: explode_to_unnest_sql,
+            exp.Lateral: _explode_to_unnest_sql,
             exp.Left: left_to_substring_sql,
             exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
                 rename_func("LEVENSHTEIN_DISTANCE")
@@ -421,8 +472,9 @@ class Generator(generator.Generator):
                 [
                     transforms.eliminate_qualify,
                     transforms.eliminate_distinct_on,
-                    transforms.explode_to_unnest(1),
+                    transforms.explode_projection_to_unnest(1),
                     transforms.eliminate_semi_and_anti_joins,
+                    _amend_exploded_column_table,
                 ]
             ),
             exp.SortArray: _no_sort_array,
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 6a36f055d3..e8d7a7ab6c 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -1030,7 +1030,7 @@ class Generator(generator.Generator):
             exp.Select: transforms.preprocess(
                 [
                     transforms.eliminate_distinct_on,
-                    transforms.explode_to_unnest(),
+                    transforms.explode_projection_to_unnest(),
                     transforms.eliminate_semi_and_anti_joins,
                     _transform_generate_date_array,
                 ]
diff --git a/sqlglot/transforms.py b/sqlglot/transforms.py
index bb728b1d8f..10c0faf2ba 100644
--- a/sqlglot/transforms.py
+++ b/sqlglot/transforms.py
@@ -410,10 +410,12 @@ def _udtf_type(u: exp.Unnest, has_multi_expr: bool) -> t.Type[exp.Func]:
     return expression
 
 
-def explode_to_unnest(index_offset: int = 0) -> t.Callable[[exp.Expression], exp.Expression]:
-    """Convert explode/posexplode into unnest."""
+def explode_projection_to_unnest(
+    index_offset: int = 0,
+) -> t.Callable[[exp.Expression], exp.Expression]:
+    """Convert explode/posexplode projections into unnests."""
 
-    def _explode_to_unnest(expression: exp.Expression) -> exp.Expression:
+    def _explode_projection_to_unnest(expression: exp.Expression) -> exp.Expression:
         if isinstance(expression, exp.Select):
             from sqlglot.optimizer.scope import Scope
 
@@ -558,7 +560,7 @@ def new_name(names: t.Set[str], name: str) -> str:
 
         return expression
 
-    return _explode_to_unnest
+    return _explode_projection_to_unnest
 
 
 def add_within_group_for_percentiles(expression: exp.Expression) -> exp.Expression:


Test patch:
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index f4d7c2e4fd..739e4de88e 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -322,6 +322,13 @@ def test_spark(self):
             },
         )
 
+        self.validate_all(
+            "SELECT id_column, name, age FROM test_table LATERAL VIEW INLINE(struc_column) explode_view AS name, age",
+            write={
+                "presto": "SELECT id_column, name, age FROM test_table CROSS JOIN UNNEST(struc_column) AS explode_view(name, age)",
+                "spark": "SELECT id_column, name, age FROM test_table LATERAL VIEW INLINE(struc_column) explode_view AS name, age",
+            },
+        )
         self.validate_all(
             "SELECT ARRAY_AGG(x) FILTER (WHERE x = 5) FROM (SELECT 1 UNION ALL SELECT NULL) AS t(x)",
             write={
@@ -843,7 +850,7 @@ def test_insert_cte(self):
             },
         )
 
-    def test_explode_to_unnest(self):
+    def test_explode_projection_to_unnest(self):
         self.validate_all(
             "SELECT EXPLODE(x) FROM tbl",
             write={
@@ -951,3 +958,42 @@ def test_analyze(self):
         self.validate_identity(
             "ANALYZE TABLE ctlg.db.tbl PARTITION(foo = 'foo', bar = 'bar') COMPUTE STATISTICS NOSCAN"
         )
+
+    def test_transpile_annotated_exploded_column(self):
+        from sqlglot.optimizer.annotate_types import annotate_types
+        from sqlglot.optimizer.qualify import qualify
+
+        for db_prefix in ("", "explode_view."):
+            with self.subTest(f"Annotated exploded column with prefix: {db_prefix}."):
+                sql = f"""
+                    WITH test_table AS (
+                      SELECT
+                        12345 AS id_column,
+                        ARRAY(
+                          STRUCT('John' AS name, 30 AS age),
+                          STRUCT('Mary' AS name, 20 AS age),
+                          STRUCT('Mike' AS name, 80 AS age),
+                          STRUCT('Dan' AS name, 50 AS age)
+                        ) AS struct_column
+                    )
+
+                    SELECT
+                        id_column,
+                        {db_prefix}new_column.name,
+                        {db_prefix}new_column.age
+                    FROM test_table
+                    LATERAL VIEW EXPLODE(struct_column) explode_view AS new_column
+                """
+
+                expr = self.parse_one(sql)
+                qualified = qualify(expr, dialect="spark")
+                annotated = annotate_types(qualified, dialect="spark")
+
+                self.assertEqual(
+                    annotated.sql("spark"),
+                    "WITH `test_table` AS (SELECT 12345 AS `id_column`, ARRAY(STRUCT('John' AS `name`, 30 AS `age`), STRUCT('Mary' AS `name`, 20 AS `age`), STRUCT('Mike' AS `name`, 80 AS `age`), STRUCT('Dan' AS `name`, 50 AS `age`)) AS `struct_column`) SELECT `test_table`.`id_column` AS `id_column`, `explode_view`.`new_column`.`name` AS `name`, `explode_view`.`new_column`.`age` AS `age` FROM `test_table` AS `test_table` LATERAL VIEW EXPLODE(`test_table`.`struct_column`) explode_view AS `new_column`",
+                )
+                self.assertEqual(
+                    annotated.sql("presto"),
+                    """WITH "test_table" AS (SELECT 12345 AS "id_column", ARRAY[CAST(ROW('John', 30) AS ROW("name" VARCHAR, "age" INTEGER)), CAST(ROW('Mary', 20) AS ROW("name" VARCHAR, "age" INTEGER)), CAST(ROW('Mike', 80) AS ROW("name" VARCHAR, "age" INTEGER)), CAST(ROW('Dan', 50) AS ROW("name" VARCHAR, "age" INTEGER))] AS "struct_column") SELECT "test_table"."id_column" AS "id_column", "explode_view"."name" AS "name", "explode_view"."age" AS "age" FROM "test_table" AS "test_table" CROSS JOIN UNNEST("test_table"."struct_column") AS "explode_view"("name", "age")""",
+                )