Adding upstream version 10.6.3.upstream/10.6.3

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

10 files changed, 205 insertions, 29 deletions

diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index e5b1c94..241f496 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -170,7 +170,7 @@ class TestBigQuery(Validator):
                 "bigquery": "CURRENT_TIMESTAMP()",
                 "duckdb": "CURRENT_TIMESTAMP()",
                 "postgres": "CURRENT_TIMESTAMP",
-                "presto": "CURRENT_TIMESTAMP()",
+                "presto": "CURRENT_TIMESTAMP",
                 "hive": "CURRENT_TIMESTAMP()",
                 "spark": "CURRENT_TIMESTAMP()",
             },
@@ -181,7 +181,7 @@ class TestBigQuery(Validator):
                 "bigquery": "CURRENT_TIMESTAMP()",
                 "duckdb": "CURRENT_TIMESTAMP()",
                 "postgres": "CURRENT_TIMESTAMP",
-                "presto": "CURRENT_TIMESTAMP()",
+                "presto": "CURRENT_TIMESTAMP",
                 "hive": "CURRENT_TIMESTAMP()",
                 "spark": "CURRENT_TIMESTAMP()",
             },
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 5a13655..a456415 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -1,6 +1,7 @@
 import unittest
 
 from sqlglot import Dialect, Dialects, ErrorLevel, UnsupportedError, parse_one
+from sqlglot.dialects import Hive
 
 
 class Validator(unittest.TestCase):
@@ -67,6 +68,11 @@ class TestDialect(Validator):
             self.assertIsNotNone(Dialect.get_or_raise(dialect))
             self.assertIsNotNone(Dialect[dialect.value])
 
+    def test_get_or_raise(self):
+        self.assertEqual(Dialect.get_or_raise(Hive), Hive)
+        self.assertEqual(Dialect.get_or_raise(Hive()), Hive)
+        self.assertEqual(Dialect.get_or_raise("hive"), Hive)
+
     def test_cast(self):
         self.validate_all(
             "CAST(a AS TEXT)",
@@ -280,6 +286,21 @@ class TestDialect(Validator):
             write={"oracle": "CAST(a AS NUMBER)"},
         )
 
+    def test_if_null(self):
+        self.validate_all(
+            "SELECT IFNULL(1, NULL) FROM foo",
+            write={
+                "": "SELECT COALESCE(1, NULL) FROM foo",
+                "redshift": "SELECT COALESCE(1, NULL) FROM foo",
+                "postgres": "SELECT COALESCE(1, NULL) FROM foo",
+                "mysql": "SELECT COALESCE(1, NULL) FROM foo",
+                "duckdb": "SELECT COALESCE(1, NULL) FROM foo",
+                "spark": "SELECT COALESCE(1, NULL) FROM foo",
+                "bigquery": "SELECT COALESCE(1, NULL) FROM foo",
+                "presto": "SELECT COALESCE(1, NULL) FROM foo",
+            },
+        )
+
     def test_time(self):
         self.validate_all(
             "STR_TO_TIME(x, '%Y-%m-%dT%H:%M:%S')",
@@ -287,10 +308,10 @@ class TestDialect(Validator):
                 "duckdb": "STRPTIME(x, '%Y-%m-%dT%H:%M:%S')",
             },
             write={
-                "mysql": "STR_TO_DATE(x, '%Y-%m-%dT%H:%i:%S')",
+                "mysql": "STR_TO_DATE(x, '%Y-%m-%dT%T')",
                 "duckdb": "STRPTIME(x, '%Y-%m-%dT%H:%M:%S')",
                 "hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'yyyy-MM-ddTHH:mm:ss')) AS TIMESTAMP)",
-                "presto": "DATE_PARSE(x, '%Y-%m-%dT%H:%i:%S')",
+                "presto": "DATE_PARSE(x, '%Y-%m-%dT%T')",
                 "drill": "TO_TIMESTAMP(x, 'yyyy-MM-dd''T''HH:mm:ss')",
                 "redshift": "TO_TIMESTAMP(x, 'YYYY-MM-DDTHH:MI:SS')",
                 "spark": "TO_TIMESTAMP(x, 'yyyy-MM-ddTHH:mm:ss')",
@@ -356,7 +377,7 @@ class TestDialect(Validator):
             write={
                 "duckdb": "EPOCH(CAST('2020-01-01' AS TIMESTAMP))",
                 "hive": "UNIX_TIMESTAMP('2020-01-01')",
-                "presto": "TO_UNIXTIME(DATE_PARSE('2020-01-01', '%Y-%m-%d %H:%i:%S'))",
+                "presto": "TO_UNIXTIME(DATE_PARSE('2020-01-01', '%Y-%m-%d %T'))",
             },
         )
         self.validate_all(
@@ -418,7 +439,7 @@ class TestDialect(Validator):
         self.validate_all(
             "UNIX_TO_STR(x, y)",
             write={
-                "duckdb": "STRFTIME(TO_TIMESTAMP(CAST(x AS BIGINT)), y)",
+                "duckdb": "STRFTIME(TO_TIMESTAMP(x), y)",
                 "hive": "FROM_UNIXTIME(x, y)",
                 "presto": "DATE_FORMAT(FROM_UNIXTIME(x), y)",
                 "starrocks": "FROM_UNIXTIME(x, y)",
@@ -427,7 +448,7 @@ class TestDialect(Validator):
         self.validate_all(
             "UNIX_TO_TIME(x)",
             write={
-                "duckdb": "TO_TIMESTAMP(CAST(x AS BIGINT))",
+                "duckdb": "TO_TIMESTAMP(x)",
                 "hive": "FROM_UNIXTIME(x)",
                 "oracle": "TO_DATE('1970-01-01','YYYY-MM-DD') + (x / 86400)",
                 "postgres": "TO_TIMESTAMP(x)",
@@ -438,7 +459,7 @@ class TestDialect(Validator):
         self.validate_all(
             "UNIX_TO_TIME_STR(x)",
             write={
-                "duckdb": "CAST(TO_TIMESTAMP(CAST(x AS BIGINT)) AS TEXT)",
+                "duckdb": "CAST(TO_TIMESTAMP(x) AS TEXT)",
                 "hive": "FROM_UNIXTIME(x)",
                 "presto": "CAST(FROM_UNIXTIME(x) AS VARCHAR)",
             },
@@ -575,10 +596,10 @@ class TestDialect(Validator):
             },
             write={
                 "drill": "TO_DATE(x, 'yyyy-MM-dd''T''HH:mm:ss')",
-                "mysql": "STR_TO_DATE(x, '%Y-%m-%dT%H:%i:%S')",
-                "starrocks": "STR_TO_DATE(x, '%Y-%m-%dT%H:%i:%S')",
+                "mysql": "STR_TO_DATE(x, '%Y-%m-%dT%T')",
+                "starrocks": "STR_TO_DATE(x, '%Y-%m-%dT%T')",
                 "hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'yyyy-MM-ddTHH:mm:ss')) AS DATE)",
-                "presto": "CAST(DATE_PARSE(x, '%Y-%m-%dT%H:%i:%S') AS DATE)",
+                "presto": "CAST(DATE_PARSE(x, '%Y-%m-%dT%T') AS DATE)",
                 "spark": "TO_DATE(x, 'yyyy-MM-ddTHH:mm:ss')",
             },
         )
@@ -709,6 +730,7 @@ class TestDialect(Validator):
                 "hive": "REDUCE(x, 0, (acc, x) -> acc + x, acc -> acc)",
                 "presto": "REDUCE(x, 0, (acc, x) -> acc + x, acc -> acc)",
                 "spark": "AGGREGATE(x, 0, (acc, x) -> acc + x, acc -> acc)",
+                "presto": "REDUCE(x, 0, (acc, x) -> acc + x, acc -> acc)",
             },
         )
 
@@ -1381,3 +1403,21 @@ SELECT
                 "spark": "MERGE INTO a AS b USING c AS d ON b.id = d.id WHEN MATCHED AND EXISTS(SELECT b.name EXCEPT SELECT d.name) THEN UPDATE SET b.name = d.name",
             },
         )
+
+    def test_substring(self):
+        self.validate_all(
+            "SUBSTR('123456', 2, 3)",
+            write={
+                "bigquery": "SUBSTR('123456', 2, 3)",
+                "oracle": "SUBSTR('123456', 2, 3)",
+                "postgres": "SUBSTR('123456', 2, 3)",
+            },
+        )
+        self.validate_all(
+            "SUBSTRING('123456', 2, 3)",
+            write={
+                "bigquery": "SUBSTRING('123456', 2, 3)",
+                "oracle": "SUBSTR('123456', 2, 3)",
+                "postgres": "SUBSTRING('123456' FROM 2 FOR 3)",
+            },
+        )
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index f6446ca..f01a604 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -22,7 +22,7 @@ class TestDuckDB(Validator):
             "EPOCH_MS(x)",
             write={
                 "bigquery": "UNIX_TO_TIME(x / 1000)",
-                "duckdb": "TO_TIMESTAMP(CAST(x / 1000 AS BIGINT))",
+                "duckdb": "TO_TIMESTAMP(x / 1000)",
                 "presto": "FROM_UNIXTIME(x / 1000)",
                 "spark": "FROM_UNIXTIME(x / 1000)",
             },
@@ -41,7 +41,7 @@ class TestDuckDB(Validator):
             "STRFTIME(x, '%Y-%m-%d %H:%M:%S')",
             write={
                 "duckdb": "STRFTIME(x, '%Y-%m-%d %H:%M:%S')",
-                "presto": "DATE_FORMAT(x, '%Y-%m-%d %H:%i:%S')",
+                "presto": "DATE_FORMAT(x, '%Y-%m-%d %T')",
                 "hive": "DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss')",
             },
         )
@@ -58,9 +58,10 @@ class TestDuckDB(Validator):
         self.validate_all(
             "TO_TIMESTAMP(x)",
             write={
-                "duckdb": "CAST(x AS TIMESTAMP)",
-                "presto": "CAST(x AS TIMESTAMP)",
-                "hive": "CAST(x AS TIMESTAMP)",
+                "bigquery": "UNIX_TO_TIME(x)",
+                "duckdb": "TO_TIMESTAMP(x)",
+                "presto": "FROM_UNIXTIME(x)",
+                "hive": "FROM_UNIXTIME(x)",
             },
         )
         self.validate_all(
@@ -334,6 +335,14 @@ class TestDuckDB(Validator):
             },
         )
 
+        self.validate_all(
+            "cast([[1]] as int[][])",
+            write={
+                "duckdb": "CAST(LIST_VALUE(LIST_VALUE(1)) AS INT[][])",
+                "spark": "CAST(ARRAY(ARRAY(1)) AS ARRAY<ARRAY<INT>>)",
+            },
+        )
+
     def test_bool_or(self):
         self.validate_all(
             "SELECT a, LOGICAL_OR(b) FROM table GROUP BY a",
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index c41e4f7..1f35d1d 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -259,7 +259,7 @@ class TestHive(Validator):
         self.validate_all(
             """from_unixtime(x, "yyyy-MM-dd'T'HH")""",
             write={
-                "duckdb": "STRFTIME(TO_TIMESTAMP(CAST(x AS BIGINT)), '%Y-%m-%d''T''%H')",
+                "duckdb": "STRFTIME(TO_TIMESTAMP(x), '%Y-%m-%d''T''%H')",
                 "presto": "DATE_FORMAT(FROM_UNIXTIME(x), '%Y-%m-%d''T''%H')",
                 "hive": "FROM_UNIXTIME(x, 'yyyy-MM-dd\\'T\\'HH')",
                 "spark": "FROM_UNIXTIME(x, 'yyyy-MM-dd\\'T\\'HH')",
@@ -269,7 +269,7 @@ class TestHive(Validator):
             "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
             write={
                 "duckdb": "STRFTIME(CAST('2020-01-01' AS TIMESTAMP), '%Y-%m-%d %H:%M:%S')",
-                "presto": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), '%Y-%m-%d %H:%i:%S')",
+                "presto": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), '%Y-%m-%d %T')",
                 "hive": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), 'yyyy-MM-dd HH:mm:ss')",
                 "spark": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), 'yyyy-MM-dd HH:mm:ss')",
             },
@@ -308,7 +308,7 @@ class TestHive(Validator):
             "UNIX_TIMESTAMP(x)",
             write={
                 "duckdb": "EPOCH(STRPTIME(x, '%Y-%m-%d %H:%M:%S'))",
-                "presto": "TO_UNIXTIME(DATE_PARSE(x, '%Y-%m-%d %H:%i:%S'))",
+                "presto": "TO_UNIXTIME(DATE_PARSE(x, '%Y-%m-%d %T'))",
                 "hive": "UNIX_TIMESTAMP(x)",
                 "spark": "UNIX_TIMESTAMP(x)",
                 "": "STR_TO_UNIX(x, '%Y-%m-%d %H:%M:%S')",
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index ce865e1..3e3b0d3 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -196,6 +196,26 @@ class TestMySQL(Validator):
 
     def test_mysql(self):
         self.validate_all(
+            "SELECT a FROM tbl FOR UPDATE",
+            write={
+                "": "SELECT a FROM tbl",
+                "mysql": "SELECT a FROM tbl FOR UPDATE",
+                "oracle": "SELECT a FROM tbl FOR UPDATE",
+                "postgres": "SELECT a FROM tbl FOR UPDATE",
+                "tsql": "SELECT a FROM tbl FOR UPDATE",
+            },
+        )
+        self.validate_all(
+            "SELECT a FROM tbl FOR SHARE",
+            write={
+                "": "SELECT a FROM tbl",
+                "mysql": "SELECT a FROM tbl FOR SHARE",
+                "oracle": "SELECT a FROM tbl FOR SHARE",
+                "postgres": "SELECT a FROM tbl FOR SHARE",
+                "tsql": "SELECT a FROM tbl FOR SHARE",
+            },
+        )
+        self.validate_all(
             "GROUP_CONCAT(DISTINCT x ORDER BY y DESC)",
             write={
                 "mysql": "GROUP_CONCAT(DISTINCT x ORDER BY y DESC SEPARATOR ',')",
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 8a17b78..5664a2a 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -113,6 +113,22 @@ class TestPostgres(Validator):
         self.validate_identity("x ~* 'y'")
 
         self.validate_all(
+            "GENERATE_SERIES(a, b, '  2   days  ')",
+            write={
+                "postgres": "GENERATE_SERIES(a, b, INTERVAL '2' days)",
+                "presto": "SEQUENCE(a, b, INTERVAL '2' days)",
+                "trino": "SEQUENCE(a, b, INTERVAL '2' days)",
+            },
+        )
+        self.validate_all(
+            "GENERATE_SERIES('2019-01-01'::TIMESTAMP, NOW(), '1day')",
+            write={
+                "postgres": "GENERATE_SERIES(CAST('2019-01-01' AS TIMESTAMP), CURRENT_TIMESTAMP, INTERVAL '1' day)",
+                "presto": "SEQUENCE(CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP AS TIMESTAMP), INTERVAL '1' day)",
+                "trino": "SEQUENCE(CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP AS TIMESTAMP), INTERVAL '1' day)",
+            },
+        )
+        self.validate_all(
             "END WORK AND NO CHAIN",
             write={"postgres": "COMMIT AND NO CHAIN"},
         )
@@ -249,7 +265,7 @@ class TestPostgres(Validator):
         )
         self.validate_all(
             "'[1,2,3]'::json->2",
-            write={"postgres": "CAST('[1,2,3]' AS JSON) -> '2'"},
+            write={"postgres": "CAST('[1,2,3]' AS JSON) -> 2"},
         )
         self.validate_all(
             """'{"a":1,"b":2}'::json->'b'""",
@@ -265,7 +281,7 @@ class TestPostgres(Validator):
         )
         self.validate_all(
             """'[1,2,3]'::json->>2""",
-            write={"postgres": "CAST('[1,2,3]' AS JSON) ->> '2'"},
+            write={"postgres": "CAST('[1,2,3]' AS JSON) ->> 2"},
         )
         self.validate_all(
             """'{"a":1,"b":2}'::json->>'b'""",
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 5ecd69a..9815dcc 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -111,7 +111,7 @@ class TestPresto(Validator):
             "DATE_FORMAT(x, '%Y-%m-%d %H:%i:%S')",
             write={
                 "duckdb": "STRFTIME(x, '%Y-%m-%d %H:%M:%S')",
-                "presto": "DATE_FORMAT(x, '%Y-%m-%d %H:%i:%S')",
+                "presto": "DATE_FORMAT(x, '%Y-%m-%d %T')",
                 "hive": "DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss')",
                 "spark": "DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss')",
             },
@@ -120,7 +120,7 @@ class TestPresto(Validator):
             "DATE_PARSE(x, '%Y-%m-%d %H:%i:%S')",
             write={
                 "duckdb": "STRPTIME(x, '%Y-%m-%d %H:%M:%S')",
-                "presto": "DATE_PARSE(x, '%Y-%m-%d %H:%i:%S')",
+                "presto": "DATE_PARSE(x, '%Y-%m-%d %T')",
                 "hive": "CAST(x AS TIMESTAMP)",
                 "spark": "TO_TIMESTAMP(x, 'yyyy-MM-dd HH:mm:ss')",
             },
@@ -135,6 +135,12 @@ class TestPresto(Validator):
             },
         )
         self.validate_all(
+            "DATE_FORMAT(x, '%T')",
+            write={
+                "hive": "DATE_FORMAT(x, 'HH:mm:ss')",
+            },
+        )
+        self.validate_all(
             "DATE_PARSE(SUBSTR(x, 1, 10), '%Y-%m-%d')",
             write={
                 "duckdb": "STRPTIME(SUBSTR(x, 1, 10), '%Y-%m-%d')",
@@ -146,7 +152,7 @@ class TestPresto(Validator):
         self.validate_all(
             "FROM_UNIXTIME(x)",
             write={
-                "duckdb": "TO_TIMESTAMP(CAST(x AS BIGINT))",
+                "duckdb": "TO_TIMESTAMP(x)",
                 "presto": "FROM_UNIXTIME(x)",
                 "hive": "FROM_UNIXTIME(x)",
                 "spark": "FROM_UNIXTIME(x)",
@@ -177,11 +183,51 @@ class TestPresto(Validator):
         self.validate_all(
             "NOW()",
             write={
-                "presto": "CURRENT_TIMESTAMP()",
+                "presto": "CURRENT_TIMESTAMP",
                 "hive": "CURRENT_TIMESTAMP()",
             },
         )
 
+        self.validate_all(
+            "DAY_OF_WEEK(timestamp '2012-08-08 01:00')",
+            write={
+                "spark": "DAYOFWEEK(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+                "presto": "DAY_OF_WEEK(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+            },
+        )
+
+        self.validate_all(
+            "DAY_OF_MONTH(timestamp '2012-08-08 01:00')",
+            write={
+                "spark": "DAYOFMONTH(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+                "presto": "DAY_OF_MONTH(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+            },
+        )
+
+        self.validate_all(
+            "DAY_OF_YEAR(timestamp '2012-08-08 01:00')",
+            write={
+                "spark": "DAYOFYEAR(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+                "presto": "DAY_OF_YEAR(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+            },
+        )
+
+        self.validate_all(
+            "WEEK_OF_YEAR(timestamp '2012-08-08 01:00')",
+            write={
+                "spark": "WEEKOFYEAR(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+                "presto": "WEEK_OF_YEAR(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+            },
+        )
+
+        self.validate_all(
+            "SELECT timestamp '2012-10-31 00:00' AT TIME ZONE 'America/Sao_Paulo'",
+            write={
+                "spark": "SELECT FROM_UTC_TIMESTAMP(CAST('2012-10-31 00:00' AS TIMESTAMP), 'America/Sao_Paulo')",
+                "presto": "SELECT CAST('2012-10-31 00:00' AS TIMESTAMP) AT TIME ZONE 'America/Sao_Paulo'",
+            },
+        )
+
     def test_ddl(self):
         self.validate_all(
             "CREATE TABLE test WITH (FORMAT = 'PARQUET') AS SELECT 1",
@@ -314,6 +360,11 @@ class TestPresto(Validator):
 
     def test_presto(self):
         self.validate_identity("SELECT BOOL_OR(a > 10) FROM asd AS T(a)")
+        self.validate_identity("SELECT * FROM (VALUES (1))")
+        self.validate_identity("START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE")
+        self.validate_identity("START TRANSACTION ISOLATION LEVEL REPEATABLE READ")
+        self.validate_identity("APPROX_PERCENTILE(a, b, c, d)")
+
         self.validate_all(
             'SELECT a."b" FROM "foo"',
             write={
@@ -455,10 +506,6 @@ class TestPresto(Validator):
                 "spark": UnsupportedError,
             },
         )
-        self.validate_identity("SELECT * FROM (VALUES (1))")
-        self.validate_identity("START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE")
-        self.validate_identity("START TRANSACTION ISOLATION LEVEL REPEATABLE READ")
-        self.validate_identity("APPROX_PERCENTILE(a, b, c, d)")
 
     def test_encode_decode(self):
         self.validate_all(
@@ -529,3 +576,27 @@ class TestPresto(Validator):
                 "presto": "FROM_HEX(x)",
             },
         )
+
+    def test_json(self):
+        self.validate_all(
+            "SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER))",
+            write={
+                "spark": "SELECT FROM_JSON('[1,23,456]', 'ARRAY<INT>')",
+                "presto": "SELECT CAST(CAST('[1,23,456]' AS JSON) AS ARRAY(INTEGER))",
+            },
+        )
+        self.validate_all(
+            """SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER))""",
+            write={
+                "spark": 'SELECT FROM_JSON(\'{"k1":1,"k2":23,"k3":456}\', \'MAP<STRING, INT>\')',
+                "presto": 'SELECT CAST(CAST(\'{"k1":1,"k2":23,"k3":456}\' AS JSON) AS MAP(VARCHAR, INTEGER))',
+            },
+        )
+
+        self.validate_all(
+            "SELECT CAST(ARRAY [1, 23, 456] AS JSON)",
+            write={
+                "spark": "SELECT TO_JSON(ARRAY(1, 23, 456))",
+                "presto": "SELECT CAST(ARRAY[1, 23, 456] AS JSON)",
+            },
+        )
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 02d43aa..be74a27 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -212,6 +212,17 @@ TBLPROPERTIES (
         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(
+            "AGGREGATE(my_arr, 0, (acc, x) -> acc + x, s -> s * 2)",
+            write={
+                "trino": "REDUCE(my_arr, 0, (acc, x) -> acc + x, s -> s * 2)",
+                "duckdb": "REDUCE(my_arr, 0, (acc, x) -> acc + x, s -> s * 2)",
+                "hive": "REDUCE(my_arr, 0, (acc, x) -> acc + x, s -> s * 2)",
+                "presto": "REDUCE(my_arr, 0, (acc, x) -> acc + x, s -> s * 2)",
+                "spark": "AGGREGATE(my_arr, 0, (acc, x) -> acc + x, s -> s * 2)",
+            },
+        )
         self.validate_all(
             "TRIM('SL', 'SSparkSQLS')", write={"spark": "TRIM('SL' FROM 'SSparkSQLS')"}
         )
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index e54a4bc..c4f4a6e 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -92,3 +92,9 @@ class TestSQLite(Validator):
                 "sqlite": "SELECT FIRST_VALUE(Name) OVER (PARTITION BY AlbumId ORDER BY Bytes DESC) AS LargestTrack FROM tracks"
             },
         )
+
+    def test_longvarchar_dtype(self):
+        self.validate_all(
+            "CREATE TABLE foo (bar LONGVARCHAR)",
+            write={"sqlite": "CREATE TABLE foo (bar TEXT)"},
+        )
diff --git a/tests/dialects/test_teradata.py b/tests/dialects/test_teradata.py
index e56de25..9e82961 100644
--- a/tests/dialects/test_teradata.py
+++ b/tests/dialects/test_teradata.py
@@ -21,3 +21,6 @@ class TestTeradata(Validator):
                 "mysql": "UPDATE A SET col2 = '' FROM schema.tableA AS A, (SELECT col1 FROM schema.tableA GROUP BY col1) AS B WHERE A.col1 = B.col1",
             },
         )
+
+    def test_create(self):
+        self.validate_identity("CREATE TABLE x (y INT) PRIMARY INDEX (y) PARTITION BY y INDEX (y)")