Repository: tobymao/sqlglot

Issue:
bigquery user-defined function `OPTIONS` syntax doesn't parse
**Fully reproducible code snippet**

This BigQuery SQL fails to parse:

```
In [6]: import sqlglot as sg

In [7]: sql = '''
   ...: CREATE TEMPORARY FUNCTION string_length_0(strings ARRAY<STRING>)
   ...: RETURNS FLOAT64
   ...: LANGUAGE js AS """
   ...: 'use strict';
   ...: function string_length(strings) {
   ...:     return _.sum(_.map(strings, ((x) => x.length)));
   ...: }
   ...: return string_length(strings);
   ...: """
   ...: OPTIONS (
   ...:     library=['gs://ibis-testing-libraries/lodash.min.js']
   ...: );
   ...: '''

In [8]: sg.parse_one(sql)
...
ParseError: Invalid expression / Unexpected token. Line 11, Col: 9.
```

**Official Documentation**

https://cloud.google.com/bigquery/docs/user-defined-functions#including-javascript-libraries


Discussion:

Patch:
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index 655e92a6..a4278707 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -111,7 +111,7 @@ def _array_sort_sql(self: Hive.Generator, expression: exp.ArraySort) -> str:
 
 
 def _property_sql(self: Hive.Generator, expression: exp.Property) -> str:
-    return f"'{expression.name}'={self.sql(expression, 'value')}"
+    return f"{self.property_name(expression, string_key=True)}={self.sql(expression, 'value')}"
 
 
 def _str_to_unix_sql(self: Hive.Generator, expression: exp.StrToUnix) -> str:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index ae73fd3f..554241d9 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -520,7 +520,7 @@ class MySQL(Dialect):
 
             return self.expression(exp.SetItem, this=charset, collate=collate, kind="NAMES")
 
-        def _parse_type(self) -> t.Optional[exp.Expression]:
+        def _parse_type(self, parse_interval: bool = True) -> t.Optional[exp.Expression]:
             # mysql binary is special and can work anywhere, even in order by operations
             # it operates like a no paren func
             if self._match(TokenType.BINARY, advance=False):
@@ -529,7 +529,7 @@ class MySQL(Dialect):
                 if isinstance(data_type, exp.DataType):
                     return self.expression(exp.Cast, this=self._parse_column(), to=data_type)
 
-            return super()._parse_type()
+            return super()._parse_type(parse_interval=parse_interval)
 
     class Generator(generator.Generator):
         LOCKING_READS_SUPPORTED = True
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 37343332..ae143531 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1175,7 +1175,7 @@ class Column(Condition):
             if self.args.get(part)
         ]
 
-    def to_dot(self) -> Dot:
+    def to_dot(self) -> Dot | Identifier:
         """Converts the column into a dot expression."""
         parts = self.parts
         parent = self.parent
@@ -1185,7 +1185,7 @@ class Column(Condition):
                 parts.append(parent.expression)
             parent = parent.parent
 
-        return Dot.build(deepcopy(parts))
+        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
 
 
 class ColumnPosition(Expression):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index f3b77219..b1ee7838 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3,6 +3,7 @@ from __future__ import annotations
 import logging
 import typing as t
 from collections import defaultdict
+from functools import reduce
 
 from sqlglot import exp
 from sqlglot.errors import ErrorLevel, UnsupportedError, concat_messages
@@ -1084,10 +1085,15 @@ class Generator:
 
         return properties_locs
 
+    def property_name(self, expression: exp.Property, string_key: bool = False) -> str:
+        if isinstance(expression.this, exp.Dot):
+            return self.sql(expression, "this")
+        return f"'{expression.name}'" if string_key else expression.name
+
     def property_sql(self, expression: exp.Property) -> str:
         property_cls = expression.__class__
         if property_cls == exp.Property:
-            return f"{expression.name}={self.sql(expression, 'value')}"
+            return f"{self.property_name(expression)}={self.sql(expression, 'value')}"
 
         property_name = exp.Properties.PROPERTY_TO_NAME.get(property_cls)
         if not property_name:
@@ -1418,9 +1424,10 @@ class Generator:
 
         # Converts `VALUES...` expression into a series of select unions.
         expression = expression.copy()
-        column_names = expression.alias and expression.args["alias"].columns
+        alias_node = expression.args.get("alias")
+        column_names = alias_node and alias_node.columns
 
-        selects = []
+        selects: t.List[exp.Subqueryable] = []
 
         for i, tup in enumerate(expression.expressions):
             row = tup.expressions
@@ -1436,16 +1443,12 @@ class Generator:
             # This may result in poor performance for large-cardinality `VALUES` tables, due to
             # the deep nesting of the resulting exp.Unions. If this is a problem, either increase
             # `sys.setrecursionlimit` to avoid RecursionErrors, or don't set `pretty`.
-            subquery_expression: exp.Select | exp.Union = selects[0]
-            if len(selects) > 1:
-                for select in selects[1:]:
-                    subquery_expression = exp.union(
-                        subquery_expression, select, distinct=False, copy=False
-                    )
-
-            return self.subquery_sql(subquery_expression.subquery(expression.alias, copy=False))
+            subqueryable = reduce(lambda x, y: exp.union(x, y, distinct=False, copy=False), selects)
+            return self.subquery_sql(
+                subqueryable.subquery(alias_node and alias_node.this, copy=False)
+            )
 
-        alias = f" AS {expression.alias}" if expression.alias else ""
+        alias = f" AS {self.sql(alias_node, 'this')}" if alias_node else ""
         unions = " UNION ALL ".join(self.sql(select) for select in selects)
         return f"({unions}){alias}"
 
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 0ce24e36..84b26394 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1290,7 +1290,14 @@ class Parser(metaclass=_Parser):
             else:
                 begin = self._match(TokenType.BEGIN)
                 return_ = self._match_text_seq("RETURN")
-                expression = self._parse_statement()
+
+                if self._match(TokenType.STRING, advance=False):
+                    # Takes care of BigQuery's JavaScript UDF definitions that end in an OPTIONS property
+                    # # https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_function_statement
+                    expression = self._parse_string()
+                    extend_props(self._parse_properties())
+                else:
+                    expression = self._parse_statement()
 
                 if return_:
                     expression = self.expression(exp.Return, this=expression)
@@ -1410,20 +1417,18 @@ class Parser(metaclass=_Parser):
         if self._match_text_seq("SQL", "SECURITY"):
             return self.expression(exp.SqlSecurityProperty, definer=self._match_text_seq("DEFINER"))
 
-        assignment = self._match_pair(
-            TokenType.VAR, TokenType.EQ, advance=False
-        ) or self._match_pair(TokenType.STRING, TokenType.EQ, advance=False)
+        index = self._index
+        key = self._parse_column()
 
-        if assignment:
-            key = self._parse_var_or_string()
-            self._match(TokenType.EQ)
-            return self.expression(
-                exp.Property,
-                this=key,
-                value=self._parse_column() or self._parse_var(any_token=True),
-            )
+        if not self._match(TokenType.EQ):
+            self._retreat(index)
+            return None
 
-        return None
+        return self.expression(
+            exp.Property,
+            this=key.to_dot() if isinstance(key, exp.Column) else key,
+            value=self._parse_column() or self._parse_var(any_token=True),
+        )
 
     def _parse_stored(self) -> exp.FileFormatProperty:
         self._match(TokenType.ALIAS)
@@ -3249,8 +3254,8 @@ class Parser(metaclass=_Parser):
             return self.UNARY_PARSERS[self._prev.token_type](self)
         return self._parse_at_time_zone(self._parse_type())
 
-    def _parse_type(self) -> t.Optional[exp.Expression]:
-        interval = self._parse_interval()
+    def _parse_type(self, parse_interval: bool = True) -> t.Optional[exp.Expression]:
+        interval = parse_interval and self._parse_interval()
         if interval:
             return interval
 
@@ -3426,7 +3431,7 @@ class Parser(metaclass=_Parser):
         return this
 
     def _parse_struct_types(self) -> t.Optional[exp.Expression]:
-        this = self._parse_type() or self._parse_id_var()
+        this = self._parse_type(parse_interval=False) or self._parse_id_var()
         self._match(TokenType.COLON)
         return self._parse_column_def(this)
 


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 33d34a6a..1f5f9028 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -721,6 +721,10 @@ WHERE
         self.validate_identity(
             "CREATE TABLE FUNCTION a(x INT64) RETURNS TABLE <q STRING, r INT64> AS SELECT s, t"
         )
+        self.validate_identity(
+            '''CREATE TEMPORARY FUNCTION string_length_0(strings ARRAY<STRING>) RETURNS FLOAT64 LANGUAGE js AS """'use strict'; function string_length(strings) { return _.sum(_.map(strings, ((x) => x.length))); } return string_length(strings);""" OPTIONS (library=['gs://ibis-testing-libraries/lodash.min.js'])''',
+            "CREATE TEMPORARY FUNCTION string_length_0(strings ARRAY<STRING>) RETURNS FLOAT64 LANGUAGE js OPTIONS (library=['gs://ibis-testing-libraries/lodash.min.js']) AS '\\'use strict\\'; function string_length(strings) { return _.sum(_.map(strings, ((x) => x.length))); } return string_length(strings);'",
+        )
 
     def test_group_concat(self):
         self.validate_all(
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index d06e0f17..3df968b0 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -5,6 +5,9 @@ class TestDatabricks(Validator):
     dialect = "databricks"
 
     def test_databricks(self):
+        self.validate_identity("CREATE TABLE t (c STRUCT<interval: DOUBLE COMMENT 'aaa'>)")
+        self.validate_identity("CREATE TABLE my_table () TBLPROPERTIES (a.b=15)")
+        self.validate_identity("CREATE TABLE my_table () TBLPROPERTIES ('a.b'=15)")
         self.validate_identity("SELECT CAST('11 23:4:0' AS INTERVAL DAY TO HOUR)")
         self.validate_identity("SELECT CAST('11 23:4:0' AS INTERVAL DAY TO MINUTE)")
         self.validate_identity("SELECT CAST('11 23:4:0' AS INTERVAL DAY TO SECOND)")
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 5bf07e81..c75654c8 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -312,9 +312,9 @@ FROM (
             },
         )
         self.validate_all(
-            "SELECT a, b FROM (VALUES (1, 2), (3, 4)) AS t (a, b)",
+            'SELECT a, b FROM (VALUES (1, 2), (3, 4)) AS "t" (a, b)',
             write={
-                "redshift": "SELECT a, b FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3, 4) AS t",
+                "redshift": 'SELECT a, b FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3, 4) AS "t"',
             },
         )
         self.validate_all(
@@ -341,6 +341,16 @@ FROM (
                 "redshift": "INSERT INTO t (a, b) VALUES (1, 2), (3, 4)",
             },
         )
+        self.validate_identity(
+            'SELECT * FROM (VALUES (1)) AS "t"(a)',
+            '''SELECT
+  *
+FROM (
+  SELECT
+    1 AS a
+) AS "t"''',
+            pretty=True,
+        )
 
     def test_create_table_like(self):
         self.validate_all(