Repository: tobymao/sqlglot

Issue:
Oracle materialized views WITH READ ONLY has Invalid expression / Unexpected token / required keyword format
I am trying to parse some oracle views in sqlglot, some materialized view in oracle has a keyword WITH READ ONLY at the end of the view to indicate that view can't be updated, with use it a lot for many views. The errors are vary sometimes I see `required keyword format` and for other views I encounter  `Invalid expression / Unexpected token`

https://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_8004.htm

**WITH READ ONLY
Specify WITH READ ONLY to indicate that the table or view cannot be updated.**


Example: 

```
import sqlglot

res = sqlglot.transpile(
    ''' 
      select * from dual
      with read only
    ''''', read="oracle", write="oracle")[0]

print(res)
```


Traceback:
```

Traceback (most recent call last):
  File "/Users/deeb/PycharmProjects/datahub_app/parse.py", line 3, in <module>
    res = sqlglot.transpile(
          ^^^^^^^^^^^^^^^^^^
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/__init__.py", line 177, in transpile
    for expression in parse(sql, read, error_level=error_level)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/__init__.py", line 102, in parse
    return Dialect.get_or_raise(read or dialect).parse(sql, **opts)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/dialects/dialect.py", line 512, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/parser.py", line 1246, in parse
    return self._parse(
           ^^^^^^^^^^^^
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/parser.py", line 1318, in _parse
    self.raise_error("Invalid expression / Unexpected token")
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/parser.py", line 1359, in raise_error
    raise error
sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 3, Col: 4.
   
select * from dual
with read only

```






Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 34c9b3a7..9ceeacc5 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -13,6 +13,7 @@ from sqlglot.dialects.dialect import (
     date_add_interval_sql,
     datestrtodate_sql,
     build_formatted_time,
+    build_timestamp_from_parts,
     filter_array_using_unnest,
     if_sql,
     inline_array_unless_query,
@@ -322,6 +323,7 @@ class BigQuery(Dialect):
                 unit=exp.Literal.string(str(seq_get(args, 1))),
                 this=seq_get(args, 0),
             ),
+            "DATETIME": build_timestamp_from_parts,
             "DATETIME_ADD": build_date_delta_with_interval(exp.DatetimeAdd),
             "DATETIME_SUB": build_date_delta_with_interval(exp.DatetimeSub),
             "DIV": binary_from_function(exp.IntDiv),
@@ -648,6 +650,7 @@ class BigQuery(Dialect):
             ),
             exp.TimeAdd: date_add_interval_sql("TIME", "ADD"),
             exp.TimeFromParts: rename_func("TIME"),
+            exp.TimestampFromParts: rename_func("DATETIME"),
             exp.TimeSub: date_add_interval_sql("TIME", "SUB"),
             exp.TimestampAdd: date_add_interval_sql("TIMESTAMP", "ADD"),
             exp.TimestampDiff: rename_func("TIMESTAMP_DIFF"),
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 0ddcf4e9..8e11050d 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -169,6 +169,7 @@ class _Dialect(type):
 
         if enum not in ("", "athena", "presto", "trino"):
             klass.generator_class.TRY_SUPPORTED = False
+            klass.generator_class.SUPPORTS_UESCAPE = False
 
         if enum not in ("", "databricks", "hive", "spark", "spark2"):
             modifier_transforms = klass.generator_class.AFTER_HAVING_MODIFIER_TRANSFORMS.copy()
@@ -1191,5 +1192,14 @@ def build_default_decimal_type(
     return _builder
 
 
+def build_timestamp_from_parts(args: t.List) -> exp.Func:
+    if len(args) == 2:
+        # Other dialects don't have the TIMESTAMP_FROM_PARTS(date, time) concept,
+        # so we parse this into Anonymous for now instead of introducing complexity
+        return exp.Anonymous(this="TIMESTAMP_FROM_PARTS", expressions=args)
+
+    return exp.TimestampFromParts.from_arg_list(args)
+
+
 def sha256_sql(self: Generator, expression: exp.SHA2) -> str:
     return self.func(f"SHA{expression.text('length') or '256'}", expression.this)
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index b2db7fb7..beedbce6 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -13,6 +13,7 @@ from sqlglot.dialects.dialect import (
     trim_sql,
 )
 from sqlglot.helper import seq_get
+from sqlglot.parser import OPTIONS_TYPE
 from sqlglot.tokens import TokenType
 
 if t.TYPE_CHECKING:
@@ -36,8 +37,8 @@ def eliminate_join_marks(ast: exp.Expression) -> exp.Expression:
     from sqlglot.optimizer.scope import traverse_scope
 
     """Remove join marks from an expression
-    
-    SELECT * FROM a, b WHERE a.id = b.id(+)   
+
+    SELECT * FROM a, b WHERE a.id = b.id(+)
     becomes:
     SELECT * FROM a LEFT JOIN b ON a.id = b.id
 
@@ -231,6 +232,12 @@ class Oracle(Dialect):
     class Tokenizer(tokens.Tokenizer):
         VAR_SINGLE_TOKENS = {"@", "$", "#"}
 
+        UNICODE_STRINGS = [
+            (prefix + q, q)
+            for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES)
+            for prefix in ("U", "u")
+        ]
+
         KEYWORDS = {
             **tokens.Tokenizer.KEYWORDS,
             "(+)": TokenType.JOIN_MARKER,
@@ -293,6 +300,7 @@ class Oracle(Dialect):
         QUERY_MODIFIER_PARSERS = {
             **parser.Parser.QUERY_MODIFIER_PARSERS,
             TokenType.ORDER_SIBLINGS_BY: lambda self: ("order", self._parse_order()),
+            TokenType.WITH: lambda self: ("options", self._parse_query_restrictions()),
         }
 
         TYPE_LITERAL_PARSERS = {
@@ -305,6 +313,13 @@ class Oracle(Dialect):
         # Reference: https://stackoverflow.com/a/336455
         DISTINCT_TOKENS = {TokenType.DISTINCT, TokenType.UNIQUE}
 
+        QUERY_RESTRICTIONS: OPTIONS_TYPE = {
+            "WITH": (
+                ("READ", "ONLY"),
+                ("CHECK", "OPTION"),
+            ),
+        }
+
         def _parse_xml_table(self) -> exp.XMLTable:
             this = self._parse_string()
 
@@ -348,6 +363,18 @@ class Oracle(Dialect):
 
             return None
 
+        def _parse_query_restrictions(self) -> t.Optional[exp.Expression]:
+            kind = self._parse_var_from_options(self.QUERY_RESTRICTIONS, raise_unmatched=False)
+
+            if not kind:
+                return None
+
+            return self.expression(
+                exp.QueryOption,
+                this=kind,
+                expression=self._match(TokenType.CONSTRAINT) and self._parse_field(),
+            )
+
     class Generator(generator.Generator):
         LOCKING_READS_SUPPORTED = True
         JOIN_HINTS = False
@@ -436,3 +463,10 @@ class Oracle(Dialect):
             if len(expression.args.get("actions", [])) > 1:
                 return f"ADD ({actions})"
             return f"ADD {actions}"
+
+        def queryoption_sql(self, expression: exp.QueryOption) -> str:
+            option = self.sql(expression, "this")
+            value = self.sql(expression, "expression")
+            value = f" CONSTRAINT {value}" if value else ""
+
+            return f"{option}{value}"
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 65b66587..9b4d77a1 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -8,6 +8,7 @@ from sqlglot.dialects.dialect import (
     NormalizationStrategy,
     binary_from_function,
     build_default_decimal_type,
+    build_timestamp_from_parts,
     date_delta_sql,
     date_trunc_to_time,
     datestrtodate_sql,
@@ -236,15 +237,6 @@ def _date_trunc_to_time(args: t.List) -> exp.DateTrunc | exp.TimestampTrunc:
     return trunc
 
 
-def _build_timestamp_from_parts(args: t.List) -> exp.Func:
-    if len(args) == 2:
-        # Other dialects don't have the TIMESTAMP_FROM_PARTS(date, time) concept,
-        # so we parse this into Anonymous for now instead of introducing complexity
-        return exp.Anonymous(this="TIMESTAMP_FROM_PARTS", expressions=args)
-
-    return exp.TimestampFromParts.from_arg_list(args)
-
-
 def _unqualify_unpivot_columns(expression: exp.Expression) -> exp.Expression:
     """
     Snowflake doesn't allow columns referenced in UNPIVOT to be qualified,
@@ -391,8 +383,8 @@ class Snowflake(Dialect):
             "TIMEDIFF": _build_datediff,
             "TIMESTAMPADD": _build_date_time_add(exp.DateAdd),
             "TIMESTAMPDIFF": _build_datediff,
-            "TIMESTAMPFROMPARTS": _build_timestamp_from_parts,
-            "TIMESTAMP_FROM_PARTS": _build_timestamp_from_parts,
+            "TIMESTAMPFROMPARTS": build_timestamp_from_parts,
+            "TIMESTAMP_FROM_PARTS": build_timestamp_from_parts,
             "TRY_TO_DATE": _build_datetime("TRY_TO_DATE", exp.DataType.Type.DATE, safe=True),
             "TO_DATE": _build_datetime("TO_DATE", exp.DataType.Type.DATE),
             "TO_NUMBER": lambda args: exp.ToNumber(
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index ee403a71..7bf1da41 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -1118,3 +1118,7 @@ class TSQL(Dialect):
                 kind = f"TABLE {kind}"
 
             return f"{variable} AS {kind}{default}"
+
+        def options_modifier(self, expression: exp.Expression) -> str:
+            options = self.expressions(expression, key="options")
+            return f" OPTION{self.wrap(options)}" if options else ""
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index b5624fab..dbbba2bb 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -356,6 +356,9 @@ class Generator(metaclass=_Generator):
     # Whether the conditional TRY(expression) function is supported
     TRY_SUPPORTED = True
 
+    # Whether the UESCAPE syntax in unicode strings is supported
+    SUPPORTS_UESCAPE = True
+
     # The keyword to use when generating a star projection with excluded columns
     STAR_EXCEPT = "EXCEPT"
 
@@ -1143,16 +1146,23 @@ class Generator(metaclass=_Generator):
         escape = expression.args.get("escape")
 
         if self.dialect.UNICODE_START:
-            escape = f" UESCAPE {self.sql(escape)}" if escape else ""
-            return f"{self.dialect.UNICODE_START}{this}{self.dialect.UNICODE_END}{escape}"
+            escape_substitute = r"\\\1"
+            left_quote, right_quote = self.dialect.UNICODE_START, self.dialect.UNICODE_END
+        else:
+            escape_substitute = r"\\u\1"
+            left_quote, right_quote = self.dialect.QUOTE_START, self.dialect.QUOTE_END
 
         if escape:
-            pattern = re.compile(rf"{escape.name}(\d+)")
+            escape_pattern = re.compile(rf"{escape.name}(\d+)")
+            escape_sql = f" UESCAPE {self.sql(escape)}" if self.SUPPORTS_UESCAPE else ""
         else:
-            pattern = ESCAPED_UNICODE_RE
+            escape_pattern = ESCAPED_UNICODE_RE
+            escape_sql = ""
+
+        if not self.dialect.UNICODE_START or (escape and not self.SUPPORTS_UESCAPE):
+            this = escape_pattern.sub(escape_substitute, this)
 
-        this = pattern.sub(r"\\u\1", this)
-        return f"{self.dialect.QUOTE_START}{this}{self.dialect.QUOTE_END}"
+        return f"{left_quote}{this}{right_quote}{escape_sql}"
 
     def rawstring_sql(self, expression: exp.RawString) -> str:
         string = self.escape_str(expression.this.replace("\\", "\\\\"), escape_backslash=False)
@@ -2234,10 +2244,6 @@ class Generator(metaclass=_Generator):
         elif self.LIMIT_FETCH == "FETCH" and isinstance(limit, exp.Limit):
             limit = exp.Fetch(direction="FIRST", count=exp.maybe_copy(limit.expression))
 
-        options = self.expressions(expression, key="options")
-        if options:
-            options = f" OPTION{self.wrap(options)}"
-
         return csv(
             *sqls,
             *[self.sql(join) for join in expression.args.get("joins") or []],
@@ -2252,10 +2258,14 @@ class Generator(metaclass=_Generator):
             self.sql(expression, "order"),
             *self.offset_limit_modifiers(expression, isinstance(limit, exp.Fetch), limit),
             *self.after_limit_modifiers(expression),
-            options,
+            self.options_modifier(expression),
             sep="",
         )
 
+    def options_modifier(self, expression: exp.Expression) -> str:
+        options = self.sql(expression, "options")
+        return f" {options}" if options else ""
+
     def queryoption_sql(self, expression: exp.QueryOption) -> str:
         return ""
 


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 495943ea..ae8ed166 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -20,6 +20,14 @@ class TestBigQuery(Validator):
     maxDiff = None
 
     def test_bigquery(self):
+        self.validate_all(
+            "EXTRACT(HOUR FROM DATETIME(2008, 12, 25, 15, 30, 00))",
+            write={
+                "bigquery": "EXTRACT(HOUR FROM DATETIME(2008, 12, 25, 15, 30, 00))",
+                "duckdb": "EXTRACT(HOUR FROM MAKE_TIMESTAMP(2008, 12, 25, 15, 30, 00))",
+                "snowflake": "DATE_PART(HOUR, TIMESTAMP_FROM_PARTS(2008, 12, 25, 15, 30, 00))",
+            },
+        )
         self.validate_identity(
             """CREATE TEMPORARY FUNCTION FOO()
 RETURNS STRING
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index f3c25cc7..7cc4d721 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -468,3 +468,12 @@ WHERE
                     eliminate_join_marks(self.parse_one(original)).sql(dialect=self.dialect),
                     expected,
                 )
+
+    def test_query_restrictions(self):
+        for restriction in ("READ ONLY", "CHECK OPTION"):
+            for constraint_name in (" CONSTRAINT name", ""):
+                with self.subTest(f"Restriction: {restriction}"):
+                    self.validate_identity(f"SELECT * FROM tbl WITH {restriction}{constraint_name}")
+                    self.validate_identity(
+                        f"CREATE VIEW view AS SELECT * FROM tbl WITH {restriction}{constraint_name}"
+                    )
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index f1bbcc13..ebb270ab 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -564,6 +564,7 @@ class TestPresto(Validator):
             self.validate_all(
                 f"{prefix}'Hello winter \\2603 !'",
                 write={
+                    "oracle": "U'Hello winter \\2603 !'",
                     "presto": "U&'Hello winter \\2603 !'",
                     "snowflake": "'Hello winter \\u2603 !'",
                     "spark": "'Hello winter \\u2603 !'",
@@ -572,6 +573,7 @@ class TestPresto(Validator):
             self.validate_all(
                 f"{prefix}'Hello winter #2603 !' UESCAPE '#'",
                 write={
+                    "oracle": "U'Hello winter \\2603 !'",
                     "presto": "U&'Hello winter #2603 !' UESCAPE '#'",
                     "snowflake": "'Hello winter \\u2603 !'",
                     "spark": "'Hello winter \\u2603 !'",