Repository: tobymao/sqlglot

Issue:
Cannot transpile `trino` to `postgres` when using Trino functions
Hi, 

sqlglot==13.3.0

When I run the below code snippet,
```
query = "SELECT COALESCE(ELEMENT_AT(MAP_FROM_ENTRIES(ARRAY[(51, '1')]), id), quantity) FROM my_table"
new_query = sqlglot.transpile(sql=query, read='trino', write='postgres')[0]
print(new_query)
```

 I get back the same input `query`.

**Output**
`SELECT COALESCE(ELEMENT_AT(MAP_FROM_ENTRIES(ARRAY[(51, '1')]), id), quantity) FROM my_table`

Discussion:

Patch:
diff --git a/sqlglot/dataframe/sql/functions.py b/sqlglot/dataframe/sql/functions.py
index 71385aa4..bdc1fb40 100644
--- a/sqlglot/dataframe/sql/functions.py
+++ b/sqlglot/dataframe/sql/functions.py
@@ -1119,7 +1119,7 @@ def map_entries(col: ColumnOrName) -> Column:
 
 
 def map_from_entries(col: ColumnOrName) -> Column:
-    return Column.invoke_anonymous_function(col, "MAP_FROM_ENTRIES")
+    return Column.invoke_expression_over_column(col, expression.MapFromEntries)
 
 
 def array_repeat(col: ColumnOrName, count: t.Union[ColumnOrName, int]) -> Column:
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 0e25b9bc..d258826a 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -388,6 +388,11 @@ def no_comment_column_constraint_sql(
     return ""
 
 
+def no_map_from_entries_sql(self: Generator, expression: exp.MapFromEntries) -> str:
+    self.unsupported("MAP_FROM_ENTRIES unsupported")
+    return ""
+
+
 def str_position_sql(self: Generator, expression: exp.StrPosition) -> str:
     this = self.sql(expression, "this")
     substr = self.sql(expression, "substr")
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 8c2a4ab8..766b584f 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -11,6 +11,7 @@ from sqlglot.dialects.dialect import (
     format_time_lambda,
     max_or_greatest,
     min_or_least,
+    no_map_from_entries_sql,
     no_paren_current_date_sql,
     no_pivot_sql,
     no_tablesample_sql,
@@ -346,6 +347,7 @@ class Postgres(Dialect):
             exp.LogicalOr: rename_func("BOOL_OR"),
             exp.LogicalAnd: rename_func("BOOL_AND"),
             exp.Max: max_or_greatest,
+            exp.MapFromEntries: no_map_from_entries_sql,
             exp.Min: min_or_least,
             exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
             exp.ArrayContains: lambda self, e: self.binary(e, "@>"),
@@ -378,3 +380,11 @@ class Postgres(Dialect):
             exp.TransientProperty: exp.Properties.Location.UNSUPPORTED,
             exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
         }
+
+        def bracket_sql(self, expression: exp.Bracket) -> str:
+            """Forms like ARRAY[1, 2, 3][3] aren't allowed; we need to wrap the ARRAY."""
+            if isinstance(expression.this, exp.Array):
+                expression = expression.copy()
+                expression.set("this", exp.paren(expression.this, copy=False))
+
+            return super().bracket_sql(expression)
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index 265780e4..24c439b0 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -20,7 +20,7 @@ from sqlglot.dialects.dialect import (
 )
 from sqlglot.dialects.mysql import MySQL
 from sqlglot.errors import UnsupportedError
-from sqlglot.helper import seq_get
+from sqlglot.helper import apply_index_offset, seq_get
 from sqlglot.tokens import TokenType
 
 
@@ -154,6 +154,13 @@ def _from_unixtime(args: t.List) -> exp.Expression:
     return exp.UnixToTime.from_arg_list(args)
 
 
+def _parse_element_at(args: t.List) -> exp.SafeBracket:
+    this = seq_get(args, 0)
+    index = seq_get(args, 1)
+    assert isinstance(this, exp.Expression) and isinstance(index, exp.Expression)
+    return exp.SafeBracket(this=this, expressions=apply_index_offset(this, [index], -1))
+
+
 def _unnest_sequence(expression: exp.Expression) -> exp.Expression:
     if isinstance(expression, exp.Table):
         if isinstance(expression.this, exp.GenerateSeries):
@@ -201,6 +208,7 @@ class Presto(Dialect):
             "DATE_FORMAT": format_time_lambda(exp.TimeToStr, "presto"),
             "DATE_PARSE": format_time_lambda(exp.StrToTime, "presto"),
             "DATE_TRUNC": date_trunc_to_time,
+            "ELEMENT_AT": _parse_element_at,
             "FROM_HEX": exp.Unhex.from_arg_list,
             "FROM_UNIXTIME": _from_unixtime,
             "FROM_UTF8": lambda args: exp.Decode(
@@ -285,6 +293,9 @@ class Presto(Dialect):
             exp.Pivot: no_pivot_sql,
             exp.Quantile: _quantile_sql,
             exp.Right: right_to_substring_sql,
+            exp.SafeBracket: lambda self, e: self.func(
+                "ELEMENT_AT", e.this, seq_get(apply_index_offset(e.this, e.expressions, 1), 0)
+            ),
             exp.SafeDivide: no_safe_divide_sql,
             exp.Schema: _schema_sql,
             exp.Select: transforms.preprocess(
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 7e53d697..6a045d20 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -3712,6 +3712,10 @@ class Bracket(Condition):
     arg_types = {"this": True, "expressions": True}
 
 
+class SafeBracket(Bracket):
+    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
+
+
 class Distinct(Expression):
     arg_types = {"expressions": False, "on": False}
 
@@ -4288,6 +4292,10 @@ class Map(Func):
     arg_types = {"keys": False, "values": False}
 
 
+class MapFromEntries(Func):
+    pass
+
+
 class StarMap(Func):
     pass
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 0030c8b3..2f99df77 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1750,6 +1750,9 @@ class Generator:
 
         return f"{self.sql(expression, 'this')}[{expressions_sql}]"
 
+    def safebracket_sql(self, expression: exp.SafeBracket) -> str:
+        return self.bracket_sql(expression)
+
     def all_sql(self, expression: exp.All) -> str:
         return f"ALL {self.wrap(expression)}"
 
diff --git a/sqlglot/optimizer/qualify_columns.py b/sqlglot/optimizer/qualify_columns.py
index 2ef6ce85..0e2c6109 100644
--- a/sqlglot/optimizer/qualify_columns.py
+++ b/sqlglot/optimizer/qualify_columns.py
@@ -79,7 +79,7 @@ def validate_qualify_columns(expression: E) -> E:
     return expression
 
 
-def _pop_table_column_aliases(derived_tables: t.List[exp.CTE]) -> None:
+def _pop_table_column_aliases(derived_tables: t.List[exp.CTE | exp.Subquery]) -> None:
     """
     Remove table column aliases.
 


Test patch:
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 852b494d..49139f90 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -1,3 +1,5 @@
+from unittest import mock
+
 from sqlglot import UnsupportedError
 from tests.dialects.test_dialect import Validator
 
@@ -439,7 +441,8 @@ class TestPresto(Validator):
             },
         )
 
-    def test_presto(self):
+    @mock.patch("sqlglot.helper.logger")
+    def test_presto(self, mock_logger):
         self.validate_identity("SELECT * FROM x OFFSET 1 LIMIT 1")
         self.validate_identity("SELECT * FROM x OFFSET 1 FETCH FIRST 1 ROWS ONLY")
         self.validate_identity("SELECT BOOL_OR(a > 10) FROM asd AS T(a)")
@@ -452,6 +455,21 @@ class TestPresto(Validator):
         self.validate_all("INTERVAL '1 day'", write={"trino": "INTERVAL '1' day"})
         self.validate_all("(5 * INTERVAL '7' day)", read={"": "INTERVAL '5' week"})
         self.validate_all("(5 * INTERVAL '7' day)", read={"": "INTERVAL '5' WEEKS"})
+        self.validate_all(
+            "SELECT COALESCE(ELEMENT_AT(MAP_FROM_ENTRIES(ARRAY[(51, '1')]), id), quantity) FROM my_table",
+            write={
+                "postgres": UnsupportedError,
+                "presto": "SELECT COALESCE(ELEMENT_AT(MAP_FROM_ENTRIES(ARRAY[(51, '1')]), id), quantity) FROM my_table",
+            },
+        )
+        self.validate_all(
+            "SELECT ELEMENT_AT(ARRAY[1, 2, 3], 4)",
+            write={
+                "": "SELECT ARRAY(1, 2, 3)[3]",
+                "postgres": "SELECT (ARRAY[1, 2, 3])[4]",
+                "presto": "SELECT ELEMENT_AT(ARRAY[1, 2, 3], 4)",
+            },
+        )
         self.validate_all(
             "SELECT SUBSTRING(a, 1, 3), SUBSTRING(a, LENGTH(a) - (3 - 1))",
             read={