Repository: tobymao/sqlglot

Issue:
Trino first_value and last_value being incorrectly converted on sql generation
- Read dialect = Trino
- Write dialect = Trino
- Still exists on main

FIRST_VALUE and LAST_VALUE window functions are being converted to ARBITRARY. I suspect because of this [function](https://github.com/tobymao/sqlglot/blob/v26.3.8/sqlglot/dialects/presto.py#L132-L143).

mre
```sql
SELECT
  id,
  FIRST_VALUE(is_deleted) OVER w1 AS is_deleted,
  NTH_VALUE(is_deleted, 2) OVER w1 AS was_deleted,
  LAST_VALUE(is_deleted) OVER w1 AS was_first_deleted,
  order_by
FROM (
  VALUES
  (1, FALSE, 1),
  (1, FALSE, 2),
  (1, FALSE, 3),
  (1, TRUE, 4)
)AS t1 (
  id, is_deleted, order_by
)
WINDOW w1 AS(PARTITION BY id ORDER BY order_by DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
```
When you look at the AST it seems to be the correct functions
```
Select(
  expressions=[
    Column(
      this=Identifier(this=id, quoted=False)),
    Alias(
      this=Window(
        this=FirstValue(
          this=Column(
            this=Identifier(this=is_deleted, quoted=False))),
        alias=Identifier(this=w1, quoted=False),
        over=OVER),
      alias=Identifier(this=is_deleted, quoted=False)),
    Alias(
      this=Window(
        this=NthValue(
          this=Column(
            this=Identifier(this=is_deleted, quoted=False)),
          offset=Literal(this=2, is_string=False)),
        alias=Identifier(this=w1, quoted=False),
        over=OVER),
      alias=Identifier(this=was_deleted, quoted=False)),
    Alias(
      this=Window(
        this=LastValue(
          this=Column(
            this=Identifier(this=is_deleted, quoted=False))),
        alias=Identifier(this=w1, quoted=False),
        over=OVER),
      alias=Identifier(this=was_first_deleted, quoted=False)),
    Column(
      this=Identifier(this=order_by, quoted=False))],
  from=From(
    this=Values(
      expressions=[
        Tuple(
          expressions=[
            Literal(this=1, is_string=False),
            Boolean(this=False),
            Literal(this=1, is_string=False)]),
        Tuple(
          expressions=[
            Literal(this=1, is_string=False),
            Boolean(this=False),
            Literal(this=2, is_string=False)]),
        Tuple(
          expressions=[
            Literal(this=1, is_string=False),
            Boolean(this=False),
            Literal(this=3, is_string=False)]),
        Tuple(
          expressions=[
            Literal(this=1, is_string=False),
            Boolean(this=True),
            Literal(this=4, is_string=False)])],
      alias=TableAlias(
        this=Identifier(this=t1, quoted=False),
        columns=[
          Identifier(this=id, quoted=False),
          Identifier(this=is_deleted, quoted=False),
          Identifier(this=order_by, quoted=False)]))),
  windows=[
    Window(
      this=Identifier(this=w1, quoted=False),
      partition_by=[
        Column(
          this=Identifier(this=id, quoted=False))],
      order=Order(
        expressions=[
          Ordered(
            this=Column(
              this=Identifier(this=order_by, quoted=False)),
            desc=True,
            nulls_first=False)]),
      spec=WindowSpec(kind=ROWS, start=UNBOUNDED, start_side=PRECEDING, end=UNBOUNDED, end_side=FOLLOWING))])
```

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index de9e0a2c7e..9e42c4c53e 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -392,7 +392,6 @@ class Generator(generator.Generator):
             exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"),
             exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'",
             exp.First: _first_last_sql,
-            exp.FirstValue: _first_last_sql,
             exp.FromTimeZone: lambda self,
             e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'",
             exp.GenerateSeries: sequence_sql,
@@ -403,7 +402,6 @@ class Generator(generator.Generator):
             exp.Initcap: _initcap_sql,
             exp.JSONExtract: lambda self, e: self.jsonextract_sql(e),
             exp.Last: _first_last_sql,
-            exp.LastValue: _first_last_sql,
             exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this),
             exp.Lateral: explode_to_unnest_sql,
             exp.Left: left_to_substring_sql,
diff --git a/sqlglot/dialects/trino.py b/sqlglot/dialects/trino.py
index b8c3201b78..2fa4389895 100644
--- a/sqlglot/dialects/trino.py
+++ b/sqlglot/dialects/trino.py
@@ -57,11 +57,17 @@ def _parse_json_query(self) -> exp.JSONExtract:
             )
 
     class Generator(Presto.Generator):
+        PROPERTIES_LOCATION = {
+            **Presto.Generator.PROPERTIES_LOCATION,
+            exp.LocationProperty: exp.Properties.Location.POST_WITH,
+        }
+
         TRANSFORMS = {
             **Presto.Generator.TRANSFORMS,
             exp.ArraySum: lambda self,
             e: f"REDUCE({self.sql(e, 'this')}, 0, (acc, x) -> acc + x, acc -> acc)",
             exp.ArrayUniqueAgg: lambda self, e: f"ARRAY_AGG(DISTINCT {self.sql(e, 'this')})",
+            exp.LocationProperty: lambda self, e: self.property_sql(e),
             exp.Merge: merge_without_target_sql,
             exp.TimeStrToTime: lambda self, e: timestrtotime_sql(self, e, include_precision=True),
             exp.Trim: trim_sql,


Test patch:
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index c84f00035d..9443b4e638 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -1083,6 +1083,9 @@ def test_presto(self):
                 "snowflake": "CURRENT_USER()",
             },
         )
+        self.validate_identity(
+            "SELECT id, FIRST_VALUE(is_deleted) OVER (PARTITION BY id) AS first_is_deleted, NTH_VALUE(is_deleted, 2) OVER (PARTITION BY id) AS nth_is_deleted, LAST_VALUE(is_deleted) OVER (PARTITION BY id) AS last_is_deleted FROM my_table"
+        )
 
     def test_encode_decode(self):
         self.validate_identity("FROM_UTF8(x, y)")
diff --git a/tests/dialects/test_trino.py b/tests/dialects/test_trino.py
index 44ff1ea0a1..d597ef40ad 100644
--- a/tests/dialects/test_trino.py
+++ b/tests/dialects/test_trino.py
@@ -85,6 +85,10 @@ def test_ddl(self):
         self.validate_identity(
             "ALTER VIEW people SET AUTHORIZATION alice", check_command_warning=True
         )
+        self.validate_identity("CREATE SCHEMA foo WITH (LOCATION='s3://bucket/foo')")
+        self.validate_identity(
+            "CREATE TABLE foo.bar WITH (LOCATION='s3://bucket/foo/bar') AS SELECT 1"
+        )
 
     def test_analyze(self):
         self.validate_identity("ANALYZE tbl")