Repository: tobymao/sqlglot

Issue:
Oracle Read Dialect Does not Transpile FETCH FIRST N ROWS ONLY
Thank you for this awesome tool. I noticed that with Oracle it is possible to transpile to the dialect properly to support fetching a set number of rows:

```python
>>> sqlglot.transpile("SELECT TOP 10 * FROM users", read="tsql", write="oracle")
['SELECT * FROM users FETCH FIRST 10 ROWS ONLY']
```

However, the inverse does not work:

```python
>>> sqlglot.transpile("SELECT * FROM users FETCH FIRST 10 ROWS ONLY", read="oracle", write="tsql")
['SELECT * FROM users FETCH FIRST 10 ROWS ONLY']
```

Discussion:
@VaggelisD: Hey @WillAyd, thank you for your kind words!

Sadly, transpilation is hard and while A -> B might work, it's not given that B -> A will also do.

Afaict, T-SQL has an OFFSET-FETCH filter that could be used here, right? However, it must be bound to an `ORDER BY` clause to work, maybe the transpilation could be:

```
SELECT * FROM table FETCH FIRST N ROWS ONLY --(Oracle to TSQL)--> SELECT * FROM table ORDER BY (NULL) OFFSET 0 ROWS FETCH NEXT N ROWS ONLY;
```@WillAyd: Thanks for the quick reply and turnaround. I think what you are suggesting is reasonable - it makes sense that we may not be able to restore the original query, but if that gets us back to the same result set then I think all is well

Solution patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 26adc94192..ff46073572 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -1229,3 +1229,11 @@ def contains_sql(self, expression: exp.Contains) -> str:
                 expr = expr.this
 
             return self.func("CONTAINS_SUBSTR", this, expr)
+
+        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
+            this = expression.this
+
+            if isinstance(this, exp.Array):
+                return f"{self.sql(expression, 'to')}{self.sql(this)}"
+
+            return super().cast_sql(expression, safe_prefix=safe_prefix)
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 1c67be6d26..8abe946a03 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -662,6 +662,8 @@ class Generator(generator.Generator):
             exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
                 rename_func("LEVENSHTEIN")
             ),
+            exp.JSONObjectAgg: rename_func("JSON_GROUP_OBJECT"),
+            exp.JSONBObjectAgg: rename_func("JSON_GROUP_OBJECT"),
         }
 
         SUPPORTED_JSON_PATH_PARTS = {
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 1d0c862c5e..6a3550252e 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -393,6 +393,8 @@ class Parser(parser.Parser):
             "SHA384": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(384)),
             "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)),
             "LEVENSHTEIN_LESS_EQUAL": _build_levenshtein_less_equal,
+            "JSON_OBJECT_AGG": lambda args: exp.JSONObjectAgg(expressions=args),
+            "JSONB_OBJECT_AGG": exp.JSONBObjectAgg.from_arg_list,
         }
 
         NO_PAREN_FUNCTIONS = {
@@ -617,6 +619,8 @@ class Generator(generator.Generator):
             exp.Unicode: rename_func("ASCII"),
             exp.UnixToTime: _unix_to_time_sql,
             exp.Levenshtein: _levenshtein_sql,
+            exp.JSONObjectAgg: rename_func("JSON_OBJECT_AGG"),
+            exp.JSONBObjectAgg: rename_func("JSONB_OBJECT_AGG"),
         }
 
         TRANSFORMS.pop(exp.CommentColumnConstraint)
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index b30dc6b84e..6a6723aa24 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -967,14 +967,22 @@ def scope_resolution(self, rhs: str, scope_name: str) -> str:
             return f"{scope_name}::{rhs}"
 
         def select_sql(self, expression: exp.Select) -> str:
-            if expression.args.get("offset"):
+            limit = expression.args.get("limit")
+            offset = expression.args.get("offset")
+
+            if isinstance(limit, exp.Fetch) and not offset:
+                # Dialects like Oracle can FETCH directly from a row set but
+                # T-SQL requires an ORDER BY + OFFSET clause in order to FETCH
+                offset = exp.Offset(expression=exp.Literal.number(0))
+                expression.set("offset", offset)
+
+            if offset:
                 if not expression.args.get("order"):
                     # ORDER BY is required in order to use OFFSET in a query, so we use
                     # a noop order by, since we don't really care about the order.
                     # See: https://www.microsoftpressstore.com/articles/article.aspx?p=2314819
                     expression.order_by(exp.select(exp.null()).subquery(), copy=False)
 
-                limit = expression.args.get("limit")
                 if isinstance(limit, exp.Limit):
                     # TOP and OFFSET can't be combined, we need use FETCH instead of TOP
                     # we replace here because otherwise TOP would be generated in select_sql
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index ac6ceede67..be4f1b374c 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -6068,6 +6068,11 @@ class JSONObjectAgg(AggFunc):
     }
 
 
+# https://www.postgresql.org/docs/9.5/functions-aggregate.html
+class JSONBObjectAgg(AggFunc):
+    arg_types = {"this": True, "expression": True}
+
+
 # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
 class JSONArray(Func):
     arg_types = {


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 15372fcf0b..0c647a925c 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -2035,10 +2035,10 @@ def test_inline_constructor(self):
         )
 
         self.validate_all(
-            "SELECT ARRAY<INT>[1, 2, 3]",
+            "SELECT ARRAY<FLOAT64>[1, 2, 3]",
             write={
-                "bigquery": "SELECT CAST([1, 2, 3] AS ARRAY<INT64>)",
-                "duckdb": "SELECT CAST([1, 2, 3] AS INT[])",
+                "bigquery": "SELECT ARRAY<FLOAT64>[1, 2, 3]",
+                "duckdb": "SELECT CAST([1, 2, 3] AS DOUBLE[])",
             },
         )
         self.validate_all(
@@ -2051,14 +2051,14 @@ def test_inline_constructor(self):
         self.validate_all(
             "SELECT * FROM UNNEST(ARRAY<STRUCT<x INT64>>[])",
             write={
-                "bigquery": "SELECT * FROM UNNEST(CAST([] AS ARRAY<STRUCT<x INT64>>))",
+                "bigquery": "SELECT * FROM UNNEST(ARRAY<STRUCT<x INT64>>[])",
                 "duckdb": "SELECT * FROM (SELECT UNNEST(CAST([] AS STRUCT(x BIGINT)[]), max_depth => 2))",
             },
         )
         self.validate_all(
             "SELECT * FROM UNNEST(ARRAY<STRUCT<device_id INT64, time DATETIME, signal INT64, state STRING>>[STRUCT(1, DATETIME '2023-11-01 09:34:01', 74, 'INACTIVE'),STRUCT(4, DATETIME '2023-11-01 09:38:01', 80, 'ACTIVE')])",
             write={
-                "bigquery": "SELECT * FROM UNNEST(CAST([STRUCT(1, CAST('2023-11-01 09:34:01' AS DATETIME), 74, 'INACTIVE'), STRUCT(4, CAST('2023-11-01 09:38:01' AS DATETIME), 80, 'ACTIVE')] AS ARRAY<STRUCT<device_id INT64, time DATETIME, signal INT64, state STRING>>))",
+                "bigquery": "SELECT * FROM UNNEST(ARRAY<STRUCT<device_id INT64, time DATETIME, signal INT64, state STRING>>[STRUCT(1, CAST('2023-11-01 09:34:01' AS DATETIME), 74, 'INACTIVE'), STRUCT(4, CAST('2023-11-01 09:38:01' AS DATETIME), 80, 'ACTIVE')])",
                 "duckdb": "SELECT * FROM (SELECT UNNEST(CAST([ROW(1, CAST('2023-11-01 09:34:01' AS TIMESTAMP), 74, 'INACTIVE'), ROW(4, CAST('2023-11-01 09:38:01' AS TIMESTAMP), 80, 'ACTIVE')] AS STRUCT(device_id BIGINT, time TIMESTAMP, signal BIGINT, state TEXT)[]), max_depth => 2))",
             },
         )
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index ac748cdd72..982b133235 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -300,6 +300,14 @@ def test_oracle(self):
             "SELECT /*+ ORDERED */ * /* test */ FROM tbl",
         )
 
+        self.validate_all(
+            "SELECT * FROM t FETCH FIRST 10 ROWS ONLY",
+            write={
+                "oracle": "SELECT * FROM t FETCH FIRST 10 ROWS ONLY",
+                "tsql": "SELECT * FROM t ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY",
+            },
+        )
+
     def test_join_marker(self):
         self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y (+) = e2.y")
 
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 2eb3fdfcd0..8ca32ff354 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -841,6 +841,22 @@ def test_postgres(self):
             "SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY a) FILTER(WHERE CAST(b AS BOOLEAN)) AS mean_value FROM (VALUES (0, 't')) AS fake_data(a, b)"
         )
 
+        self.validate_all(
+            "SELECT JSON_OBJECT_AGG(k, v) FROM t",
+            write={
+                "postgres": "SELECT JSON_OBJECT_AGG(k, v) FROM t",
+                "duckdb": "SELECT JSON_GROUP_OBJECT(k, v) FROM t",
+            },
+        )
+
+        self.validate_all(
+            "SELECT JSONB_OBJECT_AGG(k, v) FROM t",
+            write={
+                "postgres": "SELECT JSONB_OBJECT_AGG(k, v) FROM t",
+                "duckdb": "SELECT JSON_GROUP_OBJECT(k, v) FROM t",
+            },
+        )
+
     def test_ddl(self):
         # Checks that user-defined types are parsed into DataType instead of Identifier
         self.parse_one("CREATE TABLE t (a udt)").this.expressions[0].args["kind"].assert_is(
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 9443b4e638..7c1fa2873f 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -104,7 +104,7 @@ def test_cast(self):
         self.validate_all(
             "CAST(ARRAY[1, 2] AS ARRAY(BIGINT))",
             write={
-                "bigquery": "CAST([1, 2] AS ARRAY<INT64>)",
+                "bigquery": "ARRAY<INT64>[1, 2]",
                 "duckdb": "CAST([1, 2] AS BIGINT[])",
                 "presto": "CAST(ARRAY[1, 2] AS ARRAY(BIGINT))",
                 "spark": "CAST(ARRAY(1, 2) AS ARRAY<BIGINT>)",