Repository: tobymao/sqlglot

Issue:
bug in `nulls_first` default for oracle dialect. 
According to Oracle [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__I2171079) 

`NULLS LAST` is the default for ascending order, and `NULLS FIRST` is the default for descending order. I think this is not respected in sqlglot. See reproducible example:

```python
In [1]: import sqlglot.expressions as sge

In [2]: import sqlglot as sg

# descending (default should be NULLS_FIRST)
In [2]: sgexpr = sge.Ordered(this=sg.column("a"), desc=True)

In [3]: sgexpr.sql('oracle')
Out[3]: 'a DESC'

In [4]: sgexpr = sge.Ordered(this=sg.column("a"), desc=True, nulls_first=True) #this should match

In [5]: sgexpr.sql('oracle')
Out[5]: 'a DESC NULLS FIRST'

# for ascending it seems to be alright

In [6]: sgexpr = sge.Ordered(this=sg.column("a"), desc=False)

In [7]: sgexpr.sql('oracle')
Out[7]: 'a ASC NULLS LAST'

In [8]: sgexpr = sge.Ordered(this=sg.column("a"), desc=False, nulls_first=False)

In [9]: sgexpr.sql('oracle')
Out[9]: 'a ASC NULLS LAST'
```
cc: @cpcloud and @gforsyth 

Discussion:

Patch:
diff --git a/sqlglot/dialects/databricks.py b/sqlglot/dialects/databricks.py
index deddb6e1..fc1772d5 100644
--- a/sqlglot/dialects/databricks.py
+++ b/sqlglot/dialects/databricks.py
@@ -1,6 +1,8 @@
 from __future__ import annotations
 
-from sqlglot import exp, transforms
+import typing as t
+
+from sqlglot import exp, transforms, jsonpath
 from sqlglot.dialects.dialect import (
     date_delta_sql,
     build_date_delta,
@@ -10,16 +12,34 @@ from sqlglot.dialects.spark import Spark
 from sqlglot.tokens import TokenType
 
 
+def _build_json_extract(args: t.List) -> exp.JSONExtract:
+    # Transform GET_JSON_OBJECT(expr, '$.<path>') -> expr:<path>
+    this = args[0]
+    path = args[1].name.lstrip("$.")
+    return exp.JSONExtract(this=this, expression=path)
+
+
 def _timestamp_diff(
     self: Databricks.Generator, expression: exp.DatetimeDiff | exp.TimestampDiff
 ) -> str:
     return self.func("TIMESTAMPDIFF", expression.unit, expression.expression, expression.this)
 
 
+def _jsonextract_sql(
+    self: Databricks.Generator, expression: exp.JSONExtract | exp.JSONExtractScalar
+) -> str:
+    this = self.sql(expression, "this")
+    expr = self.sql(expression, "expression")
+    return f"{this}:{expr}"
+
+
 class Databricks(Spark):
     SAFE_DIVISION = False
     COPY_PARAMS_ARE_CSV = False
 
+    class JSONPathTokenizer(jsonpath.JSONPathTokenizer):
+        IDENTIFIERS = ["`", '"']
+
     class Parser(Spark.Parser):
         LOG_DEFAULTS_TO_LN = True
         STRICT_CAST = True
@@ -31,6 +51,7 @@ class Databricks(Spark):
             "DATE_ADD": build_date_delta(exp.DateAdd),
             "DATEDIFF": build_date_delta(exp.DateDiff),
             "TIMESTAMPDIFF": build_date_delta(exp.TimestampDiff),
+            "GET_JSON_OBJECT": _build_json_extract,
         }
 
         FACTOR = {
@@ -42,6 +63,8 @@ class Databricks(Spark):
         TABLESAMPLE_SEED_KEYWORD = "REPEATABLE"
         COPY_PARAMS_ARE_WRAPPED = False
         COPY_PARAMS_EQ_REQUIRED = True
+        JSON_PATH_SINGLE_QUOTE_ESCAPE = False
+        QUOTE_JSON_PATH = False
 
         TRANSFORMS = {
             **Spark.Generator.TRANSFORMS,
@@ -65,6 +88,9 @@ class Databricks(Spark):
                     transforms.unnest_to_explode,
                 ]
             ),
+            exp.JSONExtract: _jsonextract_sql,
+            exp.JSONExtractScalar: _jsonextract_sql,
+            exp.JSONPathRoot: lambda *_: "",
             exp.ToChar: lambda self, e: self.function_fallback_sql(e),
         }
 
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 01042a75..18b35a7d 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -9,7 +9,7 @@ from sqlglot import exp
 from sqlglot.errors import ParseError
 from sqlglot.generator import Generator
 from sqlglot.helper import AutoName, flatten, is_int, seq_get
-from sqlglot.jsonpath import parse as parse_json_path
+from sqlglot.jsonpath import JSONPathTokenizer, parse as parse_json_path
 from sqlglot.parser import Parser
 from sqlglot.time import TIMEZONES, format_time
 from sqlglot.tokens import Token, Tokenizer, TokenType
@@ -125,12 +125,16 @@ class _Dialect(type):
 
         base = seq_get(bases, 0)
         base_tokenizer = (getattr(base, "tokenizer_class", Tokenizer),)
+        base_jsonpath_tokenizer = (getattr(base, "jsonpath_tokenizer_class", JSONPathTokenizer),)
         base_parser = (getattr(base, "parser_class", Parser),)
         base_generator = (getattr(base, "generator_class", Generator),)
 
         klass.tokenizer_class = klass.__dict__.get(
             "Tokenizer", type("Tokenizer", base_tokenizer, {})
         )
+        klass.jsonpath_tokenizer_class = klass.__dict__.get(
+            "JSONPathTokenizer", type("JSONPathTokenizer", base_jsonpath_tokenizer, {})
+        )
         klass.parser_class = klass.__dict__.get("Parser", type("Parser", base_parser, {}))
         klass.generator_class = klass.__dict__.get(
             "Generator", type("Generator", base_generator, {})
@@ -324,6 +328,7 @@ class Dialect(metaclass=_Dialect):
     # --- Autofilled ---
 
     tokenizer_class = Tokenizer
+    jsonpath_tokenizer_class = JSONPathTokenizer
     parser_class = Parser
     generator_class = Generator
 
@@ -621,9 +626,8 @@ class Dialect(metaclass=_Dialect):
             path_text = path.name
             if path.is_number:
                 path_text = f"[{path_text}]"
-
             try:
-                return parse_json_path(path_text)
+                return parse_json_path(path_text, self)
             except ParseError as e:
                 logger.warning(f"Invalid JSON path syntax. {str(e)}")
 
@@ -655,6 +659,12 @@ class Dialect(metaclass=_Dialect):
             self._tokenizer = self.tokenizer_class(dialect=self)
         return self._tokenizer
 
+    @property
+    def jsonpath_tokenizer(self) -> JSONPathTokenizer:
+        if not hasattr(self, "_jsonpath_tokenizer"):
+            self._jsonpath_tokenizer = self.jsonpath_tokenizer_class(dialect=self)
+        return self._jsonpath_tokenizer
+
     def parser(self, **opts) -> Parser:
         return self.parser_class(dialect=self, **opts)
 
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 5dca635d..9de3766e 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -37,6 +37,7 @@ class Oracle(Dialect):
     ALIAS_POST_TABLESAMPLE = True
     LOCKING_READS_SUPPORTED = True
     TABLESAMPLE_SIZE_IS_PERCENT = True
+    NULL_ORDERING = "nulls_are_large"
 
     # See section 8: https://docs.oracle.com/cd/A97630_01/server.920/a96540/sql_elements9a.htm
     NORMALIZATION_STRATEGY = NormalizationStrategy.UPPERCASE
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 3fef2a14..74ae5466 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -369,6 +369,9 @@ class Generator(metaclass=_Generator):
     # The keywords to use when prefixing & separating WITH based properties
     WITH_PROPERTIES_PREFIX = "WITH"
 
+    # Whether to quote the generated expression of exp.JsonPath
+    QUOTE_JSON_PATH = True
+
     TYPE_MAPPING = {
         exp.DataType.Type.NCHAR: "CHAR",
         exp.DataType.Type.NVARCHAR: "VARCHAR",
@@ -2657,7 +2660,10 @@ class Generator(metaclass=_Generator):
 
     def jsonpath_sql(self, expression: exp.JSONPath) -> str:
         path = self.expressions(expression, sep="", flat=True).lstrip(".")
-        return f"{self.dialect.QUOTE_START}{path}{self.dialect.QUOTE_END}"
+        if self.QUOTE_JSON_PATH:
+            path = f"{self.dialect.QUOTE_START}{path}{self.dialect.QUOTE_END}"
+
+        return path
 
     def json_path_part(self, expression: int | str | exp.JSONPathPart) -> str:
         if isinstance(expression, exp.JSONPathPart):
diff --git a/sqlglot/jsonpath.py b/sqlglot/jsonpath.py
index 129a4e6f..911debe4 100644
--- a/sqlglot/jsonpath.py
+++ b/sqlglot/jsonpath.py
@@ -8,6 +8,7 @@ from sqlglot.tokens import Token, Tokenizer, TokenType
 
 if t.TYPE_CHECKING:
     from sqlglot._typing import Lit
+    from sqlglot.dialects.dialect import DialectType
 
 
 class JSONPathTokenizer(Tokenizer):
@@ -36,9 +37,12 @@ class JSONPathTokenizer(Tokenizer):
     STRING_ESCAPES = ["\\"]
 
 
-def parse(path: str) -> exp.JSONPath:
+def parse(path: str, dialect: DialectType = None) -> exp.JSONPath:
     """Takes in a JSON path string and parses it into a JSONPath expression."""
-    tokens = JSONPathTokenizer().tokenize(path)
+    from sqlglot.dialects import Dialect
+
+    jsonpath_tokenizer = Dialect.get_or_raise(dialect).jsonpath_tokenizer
+    tokens = jsonpath_tokenizer.tokenize(path)
     size = len(tokens)
 
     i = 0


Test patch:
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index 9ef3b86e..c88679dc 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -96,33 +96,30 @@ class TestDatabricks(Validator):
 
     # https://docs.databricks.com/sql/language-manual/functions/colonsign.html
     def test_json(self):
+        self.validate_identity("SELECT c1:price, c1:price.foo, c1:price.bar[1]")
         self.validate_identity(
-            """SELECT c1 : price FROM VALUES ('{ "price": 5 }') AS T(c1)""",
-            """SELECT GET_JSON_OBJECT(c1, '$.price') FROM VALUES ('{ "price": 5 }') AS T(c1)""",
+            """SELECT c1:item[1].price FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)"""
         )
         self.validate_identity(
-            """SELECT c1:['price'] FROM VALUES('{ "price": 5 }') AS T(c1)""",
-            """SELECT GET_JSON_OBJECT(c1, '$.price') FROM VALUES ('{ "price": 5 }') AS T(c1)""",
+            """SELECT c1:item[*].price FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)"""
         )
         self.validate_identity(
-            """SELECT c1:item[1].price FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""",
-            """SELECT GET_JSON_OBJECT(c1, '$.item[1].price') FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""",
+            """SELECT FROM_JSON(c1:item[*].price, 'ARRAY<DOUBLE>')[0] FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)"""
         )
         self.validate_identity(
-            """SELECT c1:item[*].price FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""",
-            """SELECT GET_JSON_OBJECT(c1, '$.item[*].price') FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""",
+            """SELECT INLINE(FROM_JSON(c1:item[*], 'ARRAY<STRUCT<model STRING, price DOUBLE>>')) FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)"""
         )
         self.validate_identity(
-            """SELECT from_json(c1:item[*].price, 'ARRAY<DOUBLE>')[0] FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""",
-            """SELECT FROM_JSON(GET_JSON_OBJECT(c1, '$.item[*].price'), 'ARRAY<DOUBLE>')[0] FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""",
+            """SELECT c1:['price'] FROM VALUES ('{ "price": 5 }') AS T(c1)""",
+            """SELECT c1:price FROM VALUES ('{ "price": 5 }') AS T(c1)""",
         )
         self.validate_identity(
-            """SELECT inline(from_json(c1:item[*], 'ARRAY<STRUCT<model STRING, price DOUBLE>>')) FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""",
-            """SELECT INLINE(FROM_JSON(GET_JSON_OBJECT(c1, '$.item[*]'), 'ARRAY<STRUCT<model STRING, price DOUBLE>>')) FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""",
+            """SELECT GET_JSON_OBJECT(c1, '$.price') FROM VALUES ('{ "price": 5 }') AS T(c1)""",
+            """SELECT c1:price FROM VALUES ('{ "price": 5 }') AS T(c1)""",
         )
         self.validate_identity(
-            "SELECT c1 : price",
-            "SELECT GET_JSON_OBJECT(c1, '$.price')",
+            """SELECT raw:`zip code`, raw:`fb:testid`, raw:store['bicycle'], raw:store["zip code"]""",
+            """SELECT raw:["zip code"], raw:["fb:testid"], raw:store.bicycle, raw:store["zip code"]""",
         )
 
     def test_datediff(self):
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 4bb32088..be262d4a 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -1156,7 +1156,6 @@ class TestDialect(Validator):
             write={
                 "bigquery": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname",
                 "duckdb": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC, lname NULLS FIRST",
-                "oracle": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname",
                 "presto": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC, lname NULLS FIRST",
                 "hive": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname",
                 "spark": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname",
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index d664dc8d..d89f12d2 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -244,6 +244,10 @@ class TestOracle(Validator):
                 "duckdb": "SELECT CAST(STRPTIME('2024-12-12', '%Y-%m-%d') AS DATE)",
             },
         )
+        self.validate_identity(
+            """SELECT * FROM t ORDER BY a ASC NULLS LAST, b ASC NULLS FIRST, c DESC NULLS LAST, d DESC NULLS FIRST""",
+            """SELECT * FROM t ORDER BY a ASC, b ASC NULLS FIRST, c DESC NULLS LAST, d DESC""",
+        )
 
     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_redshift.py b/tests/dialects/test_redshift.py
index 87076fe7..c4e7073a 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -28,7 +28,7 @@ class TestRedshift(Validator):
             """SELECT JSON_EXTRACT_PATH_TEXT('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', 'farm', 'barn', 'color')""",
             write={
                 "bigquery": """SELECT JSON_EXTRACT_SCALAR('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', '$.farm.barn.color')""",
-                "databricks": """SELECT GET_JSON_OBJECT('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', '$.farm.barn.color')""",
+                "databricks": """SELECT '{ "farm": {"barn": { "color": "red", "feed stocked": true }}}':farm.barn.color""",
                 "duckdb": """SELECT '{ "farm": {"barn": { "color": "red", "feed stocked": true }}}' ->> '$.farm.barn.color'""",
                 "postgres": """SELECT JSON_EXTRACT_PATH_TEXT('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', 'farm', 'barn', 'color')""",
                 "presto": """SELECT JSON_EXTRACT_SCALAR('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', '$.farm.barn.color')""",
@@ -228,7 +228,7 @@ class TestRedshift(Validator):
                 "drill": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
                 "hive": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
                 "mysql": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY CASE WHEN c IS NULL THEN 1 ELSE 0 END DESC, c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
-                "oracle": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) _t WHERE _row_number = 1",
+                "oracle": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) _t WHERE _row_number = 1",
                 "presto": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
                 "redshift": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
                 "snowflake": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 02615f24..9bdbf3ec 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -337,7 +337,7 @@ WHERE
             """SELECT PARSE_JSON('{"fruit":"banana"}'):fruit""",
             write={
                 "bigquery": """SELECT JSON_EXTRACT(PARSE_JSON('{"fruit":"banana"}'), '$.fruit')""",
-                "databricks": """SELECT GET_JSON_OBJECT('{"fruit":"banana"}', '$.fruit')""",
+                "databricks": """SELECT '{"fruit":"banana"}':fruit""",
                 "duckdb": """SELECT JSON('{"fruit":"banana"}') -> '$.fruit'""",
                 "mysql": """SELECT JSON_EXTRACT('{"fruit":"banana"}', '$.fruit')""",
                 "presto": """SELECT JSON_EXTRACT(JSON_PARSE('{"fruit":"banana"}'), '$.fruit')""",
diff --git a/tests/test_jsonpath.py b/tests/test_jsonpath.py
index 4daf3c1c..c939c523 100644
--- a/tests/test_jsonpath.py
+++ b/tests/test_jsonpath.py
@@ -2,8 +2,9 @@ import json
 import os
 import unittest
 
-from sqlglot import exp, jsonpath
+from sqlglot import exp
 from sqlglot.errors import ParseError, TokenError
+from sqlglot.jsonpath import parse
 from tests.helpers import FIXTURES_DIR
 
 
@@ -25,7 +26,7 @@ class TestJsonpath(unittest.TestCase):
             exp.JSONPathSelector(this=exp.JSONPathScript(this="@.x)")),
         ]
         self.assertEqual(
-            jsonpath.parse("$.*.a[0]['x'][*, 'y', 1].z[?(@.a == 'b'), 1:][1:5][1,?@.a][(@.x)]"),
+            parse("$.*.a[0]['x'][*, 'y', 1].z[?(@.a == 'b'), 1:][1:5][1,?@.a][(@.x)]"),
             exp.JSONPath(expressions=expected_expressions),
         )
 
@@ -36,7 +37,7 @@ class TestJsonpath(unittest.TestCase):
             ("$[((@.length-1))]", "$[((@.length-1))]"),
         ):
             with self.subTest(f"{selector} -> {expected}"):
-                self.assertEqual(jsonpath.parse(selector).sql(), f"'{expected}'")
+                self.assertEqual(parse(selector).sql(), f"'{expected}'")
 
     def test_cts_file(self):
         with open(os.path.join(FIXTURES_DIR, "jsonpath", "cts.json")) as file:
@@ -131,9 +132,9 @@ class TestJsonpath(unittest.TestCase):
             with self.subTest(f"{selector.strip()} /* {test['name']} */"):
                 if test.get("invalid_selector"):
                     try:
-                        jsonpath.parse(selector)
+                        parse(selector)
                     except (ParseError, TokenError):
                         pass
                 else:
-                    path = jsonpath.parse(selector)
+                    path = parse(selector)
                     self.assertEqual(path.sql(), f"'{overrides.get(selector, selector)}'")