Repository: tobymao/sqlglot

Issue:
Spark dialect for TRIM is incorrect
Current behavior

```python
sqlglot.transpile("select TRIM(both 'as' from 'as string as') as trim from x", 
                 read="postgres", write="spark")[0]
```
```
"SELECT TRIM('as string as', 'as') AS trim FROM x"
```
Instead of this behavior, [Spark uses the TRAILING / LEADING / BOTH syntax](https://spark.apache.org/docs/2.4.4/api/sql/#trim). When used with just two arguments, Spark also expects `strimStr` to be the first argument.

Discussion:

Patch:
diff --git a/.gitignore b/.gitignore
index 0297caf2..b5be650e 100644
--- a/.gitignore
+++ b/.gitignore
@@ -135,3 +135,4 @@ dmypy.json
 .vscode
 
 .DS_STORE
+metastore_db
diff --git a/sqlglot/dialects/spark.py b/sqlglot/dialects/spark.py
index 42d34c20..fc711abb 100644
--- a/sqlglot/dialects/spark.py
+++ b/sqlglot/dialects/spark.py
@@ -1,7 +1,7 @@
 from __future__ import annotations
 
 from sqlglot import exp, parser
-from sqlglot.dialects.dialect import create_with_partitions_sql, rename_func
+from sqlglot.dialects.dialect import create_with_partitions_sql, rename_func, trim_sql
 from sqlglot.dialects.hive import Hive
 from sqlglot.helper import seq_get
 
@@ -122,6 +122,7 @@ class Spark(Hive):
             exp.Reduce: rename_func("AGGREGATE"),
             exp.StructKwarg: lambda self, e: f"{self.sql(e, 'this')}: {self.sql(e, 'expression')}",
             exp.TimestampTrunc: lambda self, e: f"DATE_TRUNC({self.sql(e, 'unit')}, {self.sql(e, 'this')})",
+            exp.Trim: trim_sql,
             exp.VariancePop: rename_func("VAR_POP"),
             exp.DateFromParts: rename_func("MAKE_DATE"),
             exp.LogicalOr: rename_func("BOOL_OR"),
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index f06a9c8f..1dc60070 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2825,7 +2825,7 @@ class Parser(metaclass=_Parser):
             position = self._prev.text.upper()
 
         expression = self._parse_term()
-        if self._match(TokenType.FROM):
+        if self._match_set((TokenType.FROM, TokenType.COMMA)):
             this = self._parse_term()
         else:
             this = expression


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index ee01df54..90b07f8c 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -57,6 +57,7 @@ class TestPostgres(Validator):
 
     def test_postgres(self):
         self.validate_identity("SELECT ARRAY[1, 2, 3]")
+        self.validate_identity("SELECT ARRAY(SELECT 1)")
         self.validate_identity("SELECT ARRAY_LENGTH(ARRAY[1, 2, 3], 1)")
         self.validate_identity("STRING_AGG(x, y)")
         self.validate_identity("STRING_AGG(x, ',' ORDER BY y)")
@@ -88,6 +89,14 @@ class TestPostgres(Validator):
         self.validate_identity("SELECT e'\\xDEADBEEF'")
         self.validate_identity("SELECT CAST(e'\\176' AS BYTEA)")
         self.validate_identity("""SELECT * FROM JSON_TO_RECORDSET(z) AS y("rank" INT)""")
+        self.validate_identity(
+            "SELECT SUM(x) OVER a, SUM(y) OVER b FROM c WINDOW a AS (PARTITION BY d), b AS (PARTITION BY e)"
+        )
+        self.validate_identity(
+            "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
+        )
+        self.validate_identity("x ~ 'y'")
+        self.validate_identity("x ~* 'y'")
 
         self.validate_all(
             "END WORK AND NO CHAIN",
@@ -118,10 +127,6 @@ class TestPostgres(Validator):
             "SELECT to_timestamp(123)::time without time zone",
             write={"postgres": "SELECT CAST(TO_TIMESTAMP(123) AS TIME)"},
         )
-
-        self.validate_identity(
-            "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
-        )
         self.validate_all(
             "SELECT SUM(x) OVER (PARTITION BY a ORDER BY d ROWS 1 PRECEDING)",
             write={
@@ -283,9 +288,6 @@ class TestPostgres(Validator):
             "UPDATE MYTABLE T1 SET T1.COL = 13",
             write={"postgres": "UPDATE MYTABLE AS T1 SET T1.COL = 13"},
         )
-
-        self.validate_identity("x ~ 'y'")
-        self.validate_identity("x ~* 'y'")
         self.validate_all(
             "x !~ 'y'",
             write={"postgres": "NOT x ~ 'y'"},
@@ -319,15 +321,16 @@ class TestPostgres(Validator):
             "'x' 'y' 'z'",
             write={"postgres": "CONCAT('x', 'y', 'z')"},
         )
-        self.validate_identity("SELECT ARRAY(SELECT 1)")
-
         self.validate_all(
             "x::cstring",
             write={"postgres": "CAST(x AS CSTRING)"},
         )
-
-        self.validate_identity(
-            "SELECT SUM(x) OVER a, SUM(y) OVER b FROM c WINDOW a AS (PARTITION BY d), b AS (PARTITION BY e)"
+        self.validate_all(
+            "TRIM(BOTH 'as' FROM 'as string as')",
+            write={
+                "postgres": "TRIM(BOTH 'as' FROM 'as string as')",
+                "spark": "TRIM(BOTH 'as' FROM 'as string as')",
+            },
         )
 
     def test_bool_or(self):
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 294caa32..02d43aaa 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -208,6 +208,13 @@ TBLPROPERTIES (
 
     def test_spark(self):
         self.validate_identity("SELECT UNIX_TIMESTAMP()")
+        self.validate_identity("TRIM('    SparkSQL   ')")
+        self.validate_identity("TRIM(BOTH 'SL' FROM 'SSparkSQLS')")
+        self.validate_identity("TRIM(LEADING 'SL' FROM 'SSparkSQLS')")
+        self.validate_identity("TRIM(TRAILING 'SL' FROM 'SSparkSQLS')")
+        self.validate_all(
+            "TRIM('SL', 'SSparkSQLS')", write={"spark": "TRIM('SL' FROM 'SSparkSQLS')"}
+        )
         self.validate_all(
             "ARRAY_SORT(x, (left, right) -> -1)",
             write={