diff options
Diffstat (limited to 'sqlglot/dialects/bigquery.py')
| -rw-r--r-- | sqlglot/dialects/bigquery.py | 54 |
1 files changed, 29 insertions, 25 deletions
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py index 5bfc3ea..2167ba2 100644 --- a/sqlglot/dialects/bigquery.py +++ b/sqlglot/dialects/bigquery.py @@ -24,6 +24,7 @@ from sqlglot.dialects.dialect import ( rename_func, timestrtotime_sql, ts_or_ds_add_cast, + unit_to_var, ) from sqlglot.helper import seq_get, split_num_words from sqlglot.tokens import TokenType @@ -41,14 +42,22 @@ def _derived_table_values_to_unnest(self: BigQuery.Generator, expression: exp.Va structs = [] alias = expression.args.get("alias") for tup in expression.find_all(exp.Tuple): - field_aliases = alias.columns if alias else (f"_c{i}" for i in range(len(tup.expressions))) + field_aliases = ( + alias.columns + if alias and alias.columns + else (f"_c{i}" for i in range(len(tup.expressions))) + ) expressions = [ exp.PropertyEQ(this=exp.to_identifier(name), expression=fld) for name, fld in zip(field_aliases, tup.expressions) ] structs.append(exp.Struct(expressions=expressions)) - return self.unnest_sql(exp.Unnest(expressions=[exp.array(*structs, copy=False)])) + # Due to `UNNEST_COLUMN_ONLY`, it is expected that the table alias be contained in the columns expression + alias_name_only = exp.TableAlias(columns=[alias.this]) if alias else None + return self.unnest_sql( + exp.Unnest(expressions=[exp.array(*structs, copy=False)], alias=alias_name_only) + ) def _returnsproperty_sql(self: BigQuery.Generator, expression: exp.ReturnsProperty) -> str: @@ -190,7 +199,7 @@ def _ts_or_ds_add_sql(self: BigQuery.Generator, expression: exp.TsOrDsAdd) -> st def _ts_or_ds_diff_sql(self: BigQuery.Generator, expression: exp.TsOrDsDiff) -> str: expression.this.replace(exp.cast(expression.this, "TIMESTAMP", copy=True)) expression.expression.replace(exp.cast(expression.expression, "TIMESTAMP", copy=True)) - unit = expression.args.get("unit") or "DAY" + unit = unit_to_var(expression) return self.func("DATE_DIFF", expression.this, expression.expression, unit) @@ -238,16 +247,6 @@ class BigQuery(Dialect): "%E6S": "%S.%f", } - ESCAPE_SEQUENCES = { - "\\a": "\a", - "\\b": "\b", - "\\f": "\f", - "\\n": "\n", - "\\r": "\r", - "\\t": "\t", - "\\v": "\v", - } - FORMAT_MAPPING = { "DD": "%d", "MM": "%m", @@ -315,6 +314,7 @@ class BigQuery(Dialect): "BEGIN TRANSACTION": TokenType.BEGIN, "BYTES": TokenType.BINARY, "CURRENT_DATETIME": TokenType.CURRENT_DATETIME, + "DATETIME": TokenType.TIMESTAMP, "DECLARE": TokenType.COMMAND, "ELSEIF": TokenType.COMMAND, "EXCEPTION": TokenType.COMMAND, @@ -486,14 +486,14 @@ class BigQuery(Dialect): table.set("db", exp.Identifier(this=parts[0])) table.set("this", exp.Identifier(this=parts[1])) - if isinstance(table.this, exp.Identifier) and "." in table.name: + if any("." in p.name for p in table.parts): catalog, db, this, *rest = ( - t.cast(t.Optional[exp.Expression], exp.to_identifier(x, quoted=True)) - for x in split_num_words(table.name, ".", 3) + exp.to_identifier(p, quoted=True) + for p in split_num_words(".".join(p.name for p in table.parts), ".", 3) ) if rest and this: - this = exp.Dot.build(t.cast(t.List[exp.Expression], [this, *rest])) + this = exp.Dot.build([this, *rest]) # type: ignore table = exp.Table(this=this, db=db, catalog=catalog) table.meta["quoted_table"] = True @@ -527,7 +527,9 @@ class BigQuery(Dialect): return json_object - def _parse_bracket(self, this: t.Optional[exp.Expression]) -> t.Optional[exp.Expression]: + def _parse_bracket( + self, this: t.Optional[exp.Expression] = None + ) -> t.Optional[exp.Expression]: bracket = super()._parse_bracket(this) if this is bracket: @@ -566,6 +568,7 @@ class BigQuery(Dialect): IGNORE_NULLS_IN_FUNC = True JSON_PATH_SINGLE_QUOTE_ESCAPE = True CAN_IMPLEMENT_ARRAY_ANY = True + SUPPORTS_TO_NUMBER = False NAMED_PLACEHOLDER_TOKEN = "@" TRANSFORMS = { @@ -588,7 +591,7 @@ class BigQuery(Dialect): exp.CTE: transforms.preprocess([_pushdown_cte_column_names]), exp.DateAdd: date_add_interval_sql("DATE", "ADD"), exp.DateDiff: lambda self, e: self.func( - "DATE_DIFF", e.this, e.expression, e.unit or "DAY" + "DATE_DIFF", e.this, e.expression, unit_to_var(e) ), exp.DateFromParts: rename_func("DATE"), exp.DateStrToDate: datestrtodate_sql, @@ -607,6 +610,7 @@ class BigQuery(Dialect): exp.IntDiv: rename_func("DIV"), exp.JSONFormat: rename_func("TO_JSON_STRING"), exp.Max: max_or_greatest, + exp.Mod: rename_func("MOD"), exp.MD5: lambda self, e: self.func("TO_HEX", self.func("MD5", e.this)), exp.MD5Digest: rename_func("MD5"), exp.Min: min_or_least, @@ -847,10 +851,10 @@ class BigQuery(Dialect): return inline_array_sql(self, expression) def bracket_sql(self, expression: exp.Bracket) -> str: - this = self.sql(expression, "this") + this = expression.this expressions = expression.expressions - if len(expressions) == 1: + if len(expressions) == 1 and this and this.is_type(exp.DataType.Type.STRUCT): arg = expressions[0] if arg.type is None: from sqlglot.optimizer.annotate_types import annotate_types @@ -858,10 +862,10 @@ class BigQuery(Dialect): arg = annotate_types(arg) if arg.type and arg.type.this in exp.DataType.TEXT_TYPES: - # BQ doesn't support bracket syntax with string values - return f"{this}.{arg.name}" + # BQ doesn't support bracket syntax with string values for structs + return f"{self.sql(this)}.{arg.name}" - expressions_sql = ", ".join(self.sql(e) for e in expressions) + expressions_sql = self.expressions(expression, flat=True) offset = expression.args.get("offset") if offset == 0: @@ -874,7 +878,7 @@ class BigQuery(Dialect): if expression.args.get("safe"): expressions_sql = f"SAFE_{expressions_sql}" - return f"{this}[{expressions_sql}]" + return f"{self.sql(this)}[{expressions_sql}]" def in_unnest_op(self, expression: exp.Unnest) -> str: return self.sql(expression) |