1from __future__ import annotations
2
3import logging
4import re
5import typing as t
6from collections import defaultdict
7from decimal import Decimal
8from functools import reduce, wraps
9
10from sqlglot import exp
11from sqlglot.errors import ErrorLevel, UnsupportedError, concat_messages
12from sqlglot.expressions import apply_index_offset
13from sqlglot.expressions.core import maybe_parse
14from sqlglot.helper import csv, name_sequence, seq_get
15from sqlglot.jsonpath import ALL_JSON_PATH_PARTS, JSON_PATH_PART_TRANSFORMS
16from sqlglot.time import format_time
17from sqlglot.tokens import TokenType
18
19if t.TYPE_CHECKING:
20 from sqlglot._typing import E
21 from sqlglot.dialects.dialect import DialectType
22
23 G = t.TypeVar("G", bound="Generator")
24 GeneratorMethod = t.Callable[[G, E], str]
25
26logger = logging.getLogger("sqlglot")
27
28ESCAPED_UNICODE_RE = re.compile(r"\\(\d+)")
29UNSUPPORTED_TEMPLATE = "Argument '{}' is not supported for expression '{}' when targeting {}."
30
31
32def unsupported_args(
33 *args: str | tuple[str, str],
34) -> t.Callable[[GeneratorMethod], GeneratorMethod]:
35 """
36 Decorator that can be used to mark certain args of an `Expr` subclass as unsupported.
37 It expects a sequence of argument names or pairs of the form (argument_name, diagnostic_msg).
38 """
39 diagnostic_by_arg: dict[str, str | None] = {}
40 for arg in args:
41 if isinstance(arg, str):
42 diagnostic_by_arg[arg] = None
43 else:
44 diagnostic_by_arg[arg[0]] = arg[1]
45
46 def decorator(func: GeneratorMethod) -> GeneratorMethod:
47 @wraps(func)
48 def _func(generator: G, expression: E) -> str:
49 expression_name = expression.__class__.__name__
50 dialect_name = generator.dialect.__class__.__name__
51
52 for arg_name, diagnostic in diagnostic_by_arg.items():
53 if expression.args.get(arg_name):
54 diagnostic = diagnostic or UNSUPPORTED_TEMPLATE.format(
55 arg_name, expression_name, dialect_name
56 )
57 generator.unsupported(diagnostic)
58
59 return func(generator, expression)
60
61 return _func
62
63 return decorator
64
65
66AFTER_HAVING_MODIFIER_TRANSFORMS: dict[str, t.Any] = {
67 "windows": lambda self, e: (
68 self.seg("WINDOW ") + self.expressions(e, key="windows", flat=True)
69 if e.args.get("windows")
70 else ""
71 ),
72 "qualify": lambda self, e: self.sql(e, "qualify"),
73}
74
75
76_DISPATCH_CACHE: dict[type[Generator], dict[type[exp.Expr], t.Callable[..., str]]] = {}
77
78
79def _build_dispatch(
80 cls: type[Generator],
81) -> dict[type[exp.Expr], t.Callable[..., str]]:
82 dispatch: dict[type[exp.Expr], t.Callable[..., str]] = dict(cls.TRANSFORMS)
83
84 for attr_name in dir(cls):
85 if not attr_name.endswith("_sql") or attr_name.startswith("_"):
86 continue
87
88 expr_key = attr_name[:-4]
89 expr_cls = exp.EXPR_CLASSES.get(expr_key)
90
91 if expr_cls and expr_cls not in dispatch:
92 dispatch[expr_cls] = getattr(cls, attr_name)
93
94 return dispatch
95
96
97class Generator:
98 """
99 Generator converts a given syntax tree to the corresponding SQL string.
100
101 Args:
102 pretty: Whether to format the produced SQL string.
103 Default: False.
104 identify: Determines when an identifier should be quoted. Possible values are:
105 False (default): Never quote, except in cases where it's mandatory by the dialect.
106 True: Always quote except for specials cases.
107 'safe': Only quote identifiers that are case insensitive.
108 normalize: Whether to normalize identifiers to lowercase.
109 Default: False.
110 pad: The pad size in a formatted string. For example, this affects the indentation of
111 a projection in a query, relative to its nesting level.
112 Default: 2.
113 indent: The indentation size in a formatted string. For example, this affects the
114 indentation of subqueries and filters under a `WHERE` clause.
115 Default: 2.
116 normalize_functions: How to normalize function names. Possible values are:
117 "upper" or True (default): Convert names to uppercase.
118 "lower": Convert names to lowercase.
119 False: Disables function name normalization.
120 unsupported_level: Determines the generator's behavior when it encounters unsupported expressions.
121 Default ErrorLevel.WARN.
122 max_unsupported: Maximum number of unsupported messages to include in a raised UnsupportedError.
123 This is only relevant if unsupported_level is ErrorLevel.RAISE.
124 Default: 3
125 leading_comma: Whether the comma is leading or trailing in select expressions.
126 This is only relevant when generating in pretty mode.
127 Default: False
128 max_text_width: The max number of characters in a segment before creating new lines in pretty mode.
129 The default is on the smaller end because the length only represents a segment and not the true
130 line length.
131 Default: 80
132 comments: Whether to preserve comments in the output SQL code.
133 Default: True
134 """
135
136 TRANSFORMS: t.ClassVar[dict[type[exp.Expr], t.Callable[..., str]]] = {
137 **JSON_PATH_PART_TRANSFORMS,
138 exp.Adjacent: lambda self, e: self.binary(e, "-|-"),
139 exp.AllowedValuesProperty: lambda self, e: (
140 f"ALLOWED_VALUES {self.expressions(e, flat=True)}"
141 ),
142 exp.AnalyzeColumns: lambda self, e: self.sql(e, "this"),
143 exp.AnalyzeWith: lambda self, e: self.expressions(e, prefix="WITH ", sep=" "),
144 exp.ArrayContainedBy: lambda self, e: self.binary(e, "<@"),
145 exp.ArrayContainsAll: lambda self, e: self.binary(e, "@>"),
146 exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
147 exp.AssumeColumnConstraint: lambda self, e: f"ASSUME ({self.sql(e, 'this')})",
148 exp.AutoRefreshProperty: lambda self, e: f"AUTO REFRESH {self.sql(e, 'this')}",
149 exp.BackupProperty: lambda self, e: f"BACKUP {self.sql(e, 'this')}",
150 exp.BinaryColumnConstraint: lambda *_: "BINARY",
151 exp.CaseSpecificColumnConstraint: lambda _, e: (
152 f"{'NOT ' if e.args.get('not_') else ''}CASESPECIFIC"
153 ),
154 exp.CalledOnNullInputProperty: lambda *_: "CALLED ON NULL INPUT",
155 exp.Ceil: lambda self, e: self.ceil_floor(e),
156 exp.CharacterSetColumnConstraint: lambda self, e: f"CHARACTER SET {self.sql(e, 'this')}",
157 exp.CharacterSetProperty: lambda self, e: (
158 f"{'DEFAULT ' if e.args.get('default') else ''}CHARACTER SET={self.sql(e, 'this')}"
159 ),
160 exp.ClusteredColumnConstraint: lambda self, e: (
161 f"CLUSTERED ({self.expressions(e, 'this', indent=False)})"
162 ),
163 exp.CollateColumnConstraint: lambda self, e: f"COLLATE {self.sql(e, 'this')}",
164 exp.CommentColumnConstraint: lambda self, e: f"COMMENT {self.sql(e, 'this')}",
165 exp.ConnectByRoot: lambda self, e: f"CONNECT_BY_ROOT {self.sql(e, 'this')}",
166 exp.ConvertToCharset: lambda self, e: self.func(
167 "CONVERT", e.this, e.args["dest"], e.args.get("source")
168 ),
169 exp.CopyGrantsProperty: lambda *_: "COPY GRANTS",
170 exp.CredentialsProperty: lambda self, e: (
171 f"CREDENTIALS=({self.expressions(e, 'expressions', sep=' ')})"
172 ),
173 exp.CurrentCatalog: lambda *_: "CURRENT_CATALOG",
174 exp.SessionUser: lambda *_: "SESSION_USER",
175 exp.DateFormatColumnConstraint: lambda self, e: f"FORMAT {self.sql(e, 'this')}",
176 exp.DefaultColumnConstraint: lambda self, e: f"DEFAULT {self.sql(e, 'this')}",
177 exp.ApiProperty: lambda *_: "API",
178 exp.ApplicationProperty: lambda *_: "APPLICATION",
179 exp.CatalogProperty: lambda *_: "CATALOG",
180 exp.ComputeProperty: lambda *_: "COMPUTE",
181 exp.DatabaseProperty: lambda *_: "DATABASE",
182 exp.DynamicProperty: lambda *_: "DYNAMIC",
183 exp.EmptyProperty: lambda *_: "EMPTY",
184 exp.EncodeColumnConstraint: lambda self, e: f"ENCODE {self.sql(e, 'this')}",
185 exp.EndStatement: lambda *_: "END",
186 exp.EnviromentProperty: lambda self, e: f"ENVIRONMENT ({self.expressions(e, flat=True)})",
187 exp.HandlerProperty: lambda self, e: f"HANDLER {self.sql(e, 'this')}",
188 exp.ParameterStyleProperty: lambda self, e: f"PARAMETER STYLE {self.sql(e, 'this')}",
189 exp.EphemeralColumnConstraint: lambda self, e: (
190 f"EPHEMERAL{(' ' + self.sql(e, 'this')) if e.this else ''}"
191 ),
192 exp.ExcludeColumnConstraint: lambda self, e: f"EXCLUDE {self.sql(e, 'this').lstrip()}",
193 exp.ExecuteAsProperty: lambda self, e: self.naked_property(e),
194 exp.Except: lambda self, e: self.set_operations(e),
195 exp.ExternalProperty: lambda *_: "EXTERNAL",
196 exp.Floor: lambda self, e: self.ceil_floor(e),
197 exp.Get: lambda self, e: self.get_put_sql(e),
198 exp.GlobalProperty: lambda *_: "GLOBAL",
199 exp.HeapProperty: lambda *_: "HEAP",
200 exp.HybridProperty: lambda *_: "HYBRID",
201 exp.IcebergProperty: lambda *_: "ICEBERG",
202 exp.InheritsProperty: lambda self, e: f"INHERITS ({self.expressions(e, flat=True)})",
203 exp.InlineLengthColumnConstraint: lambda self, e: f"INLINE LENGTH {self.sql(e, 'this')}",
204 exp.InputModelProperty: lambda self, e: f"INPUT{self.sql(e, 'this')}",
205 exp.Intersect: lambda self, e: self.set_operations(e),
206 exp.IntervalSpan: lambda self, e: f"{self.sql(e, 'this')} TO {self.sql(e, 'expression')}",
207 exp.Int64: lambda self, e: self.sql(exp.cast(e.this, exp.DType.BIGINT)),
208 exp.JSONBContainsAnyTopKeys: lambda self, e: self.binary(e, "?|"),
209 exp.JSONBContainsAllTopKeys: lambda self, e: self.binary(e, "?&"),
210 exp.JSONBContainsTopKey: lambda self, e: self.binary(e, "?"),
211 exp.JSONBDeleteAtPath: lambda self, e: self.binary(e, "#-"),
212 exp.JSONBPathExists: lambda self, e: self.binary(e, "@?"),
213 exp.JSONObject: lambda self, e: self._jsonobject_sql(e),
214 exp.JSONObjectAgg: lambda self, e: self._jsonobject_sql(e),
215 exp.LanguageProperty: lambda self, e: self.naked_property(e),
216 exp.LocationProperty: lambda self, e: self.naked_property(e),
217 exp.LogProperty: lambda _, e: f"{'NO ' if e.args.get('no') else ''}LOG",
218 exp.MaskingProperty: lambda *_: "MASKING",
219 exp.MaterializedProperty: lambda *_: "MATERIALIZED",
220 exp.NetFunc: lambda self, e: f"NET.{self.sql(e, 'this')}",
221 exp.NetworkProperty: lambda *_: "NETWORK",
222 exp.NonClusteredColumnConstraint: lambda self, e: (
223 f"NONCLUSTERED ({self.expressions(e, 'this', indent=False)})"
224 ),
225 exp.NoPrimaryIndexProperty: lambda *_: "NO PRIMARY INDEX",
226 exp.NotForReplicationColumnConstraint: lambda *_: "NOT FOR REPLICATION",
227 exp.OnCommitProperty: lambda _, e: (
228 f"ON COMMIT {'DELETE' if e.args.get('delete') else 'PRESERVE'} ROWS"
229 ),
230 exp.OnProperty: lambda self, e: f"ON {self.sql(e, 'this')}",
231 exp.OnUpdateColumnConstraint: lambda self, e: f"ON UPDATE {self.sql(e, 'this')}",
232 exp.Operator: lambda self, e: self.binary(e, ""), # The operator is produced in `binary`
233 exp.OutputModelProperty: lambda self, e: f"OUTPUT{self.sql(e, 'this')}",
234 exp.ExtendsLeft: lambda self, e: self.binary(e, "&<"),
235 exp.ExtendsRight: lambda self, e: self.binary(e, "&>"),
236 exp.PathColumnConstraint: lambda self, e: f"PATH {self.sql(e, 'this')}",
237 exp.PartitionedByBucket: lambda self, e: self.func("BUCKET", e.this, e.expression),
238 exp.PartitionByTruncate: lambda self, e: self.func("TRUNCATE", e.this, e.expression),
239 exp.PivotAny: lambda self, e: f"ANY{self.sql(e, 'this')}",
240 exp.PositionalColumn: lambda self, e: f"#{self.sql(e, 'this')}",
241 exp.ProjectionPolicyColumnConstraint: lambda self, e: (
242 f"PROJECTION POLICY {self.sql(e, 'this')}"
243 ),
244 exp.InvisibleColumnConstraint: lambda self, e: "INVISIBLE",
245 exp.ZeroFillColumnConstraint: lambda self, e: "ZEROFILL",
246 exp.Put: lambda self, e: self.get_put_sql(e),
247 exp.RemoteWithConnectionModelProperty: lambda self, e: (
248 f"REMOTE WITH CONNECTION {self.sql(e, 'this')}"
249 ),
250 exp.ReturnsProperty: lambda self, e: (
251 "RETURNS NULL ON NULL INPUT" if e.args.get("null") else self.naked_property(e)
252 ),
253 exp.RowAccessProperty: lambda *_: "ROW ACCESS",
254 exp.SafeFunc: lambda self, e: f"SAFE.{self.sql(e, 'this')}",
255 exp.SampleProperty: lambda self, e: f"SAMPLE BY {self.sql(e, 'this')}",
256 exp.SecureProperty: lambda *_: "SECURE",
257 exp.SecurityIntegrationProperty: lambda *_: "SECURITY",
258 exp.SetConfigProperty: lambda self, e: self.sql(e, "this"),
259 exp.SetProperty: lambda _, e: f"{'MULTI' if e.args.get('multi') else ''}SET",
260 exp.SettingsProperty: lambda self, e: f"SETTINGS{self.seg('')}{(self.expressions(e))}",
261 exp.SharingProperty: lambda self, e: f"SHARING={self.sql(e, 'this')}",
262 exp.SqlReadWriteProperty: lambda _, e: e.name,
263 exp.SqlSecurityProperty: lambda self, e: f"SQL SECURITY {self.sql(e, 'this')}",
264 exp.StabilityProperty: lambda _, e: e.name,
265 exp.Stream: lambda self, e: f"STREAM {self.sql(e, 'this')}",
266 exp.StreamingTableProperty: lambda *_: "STREAMING",
267 exp.StrictProperty: lambda *_: "STRICT",
268 exp.SwapTable: lambda self, e: f"SWAP WITH {self.sql(e, 'this')}",
269 exp.TableColumn: lambda self, e: self.sql(e.this),
270 exp.Tags: lambda self, e: f"TAG ({self.expressions(e, flat=True)})",
271 exp.TemporaryProperty: lambda *_: "TEMPORARY",
272 exp.TitleColumnConstraint: lambda self, e: f"TITLE {self.sql(e, 'this')}",
273 exp.ToMap: lambda self, e: f"MAP {self.sql(e, 'this')}",
274 exp.ToTableProperty: lambda self, e: f"TO {self.sql(e.this)}",
275 exp.TransformModelProperty: lambda self, e: self.func("TRANSFORM", *e.expressions),
276 exp.TransientProperty: lambda *_: "TRANSIENT",
277 exp.VirtualProperty: lambda *_: "VIRTUAL",
278 exp.TriggerExecute: lambda self, e: f"EXECUTE FUNCTION {self.sql(e, 'this')}",
279 exp.Union: lambda self, e: self.set_operations(e),
280 exp.UnloggedProperty: lambda *_: "UNLOGGED",
281 exp.UsingTemplateProperty: lambda self, e: f"USING TEMPLATE {self.sql(e, 'this')}",
282 exp.UsingData: lambda self, e: f"USING DATA {self.sql(e, 'this')}",
283 exp.UppercaseColumnConstraint: lambda *_: "UPPERCASE",
284 exp.UtcDate: lambda self, e: self.sql(exp.CurrentDate(this=exp.Literal.string("UTC"))),
285 exp.UtcTime: lambda self, e: self.sql(exp.CurrentTime(this=exp.Literal.string("UTC"))),
286 exp.UtcTimestamp: lambda self, e: self.sql(
287 exp.CurrentTimestamp(this=exp.Literal.string("UTC"))
288 ),
289 exp.Variadic: lambda self, e: f"VARIADIC {self.sql(e, 'this')}",
290 exp.VarMap: lambda self, e: self.func("MAP", e.args["keys"], e.args["values"]),
291 exp.ViewAttributeProperty: lambda self, e: f"WITH {self.sql(e, 'this')}",
292 exp.VolatileProperty: lambda *_: "VOLATILE",
293 exp.WithJournalTableProperty: lambda self, e: f"WITH JOURNAL TABLE={self.sql(e, 'this')}",
294 exp.WithProcedureOptions: lambda self, e: f"WITH {self.expressions(e, flat=True)}",
295 exp.WithSchemaBindingProperty: lambda self, e: f"WITH SCHEMA {self.sql(e, 'this')}",
296 exp.WithOperator: lambda self, e: f"{self.sql(e, 'this')} WITH {self.sql(e, 'op')}",
297 exp.ForceProperty: lambda *_: "FORCE",
298 }
299
300 # Whether null ordering is supported in order by
301 # True: Full Support, None: No support, False: No support for certain cases
302 # such as window specifications, aggregate functions etc
303 NULL_ORDERING_SUPPORTED: bool | None = True
304
305 # Window functions that support NULLS FIRST/LAST
306 WINDOW_FUNCS_WITH_NULL_ORDERING: t.ClassVar[tuple[type[exp.Expression], ...]] = ()
307
308 # Whether ignore nulls is inside the agg or outside.
309 # FIRST(x IGNORE NULLS) OVER vs FIRST (x) IGNORE NULLS OVER
310 IGNORE_NULLS_IN_FUNC = False
311
312 # Whether IGNORE NULLS is placed before ORDER BY in the agg.
313 # FIRST(x IGNORE NULLS ORDER BY y) vs FIRST(x ORDER BY y IGNORE NULLS)
314 IGNORE_NULLS_BEFORE_ORDER = True
315
316 # Whether locking reads (i.e. SELECT ... FOR UPDATE/SHARE) are supported
317 LOCKING_READS_SUPPORTED = False
318
319 # Whether the EXCEPT and INTERSECT operations can return duplicates
320 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = True
321
322 # Wrap derived values in parens, usually standard but spark doesn't support it
323 WRAP_DERIVED_VALUES = True
324
325 # Whether create function uses an AS before the RETURN
326 CREATE_FUNCTION_RETURN_AS = True
327
328 # Whether MERGE ... WHEN MATCHED BY SOURCE is allowed
329 MATCHED_BY_SOURCE = True
330
331 # Whether MERGE ... WHEN MATCHED/NOT MATCHED THEN UPDATE/INSERT ... WHERE is supported
332 SUPPORTS_MERGE_WHERE = False
333
334 # Whether the INTERVAL expression works only with values like '1 day'
335 SINGLE_STRING_INTERVAL = False
336
337 # Whether the plural form of date parts like day (i.e. "days") is supported in INTERVALs
338 INTERVAL_ALLOWS_PLURAL_FORM = True
339
340 # Whether intervals in a REFRESH schedule (AutoRefreshProperty) are generated without the
341 # INTERVAL keyword, e.g. ClickHouse's REFRESH EVERY 30 SECOND
342 AUTO_REFRESH_BARE_INTERVALS = False
343
344 # Whether limit and fetch are supported (possible values: "ALL", "LIMIT", "FETCH")
345 LIMIT_FETCH = "ALL"
346
347 # Whether limit and fetch allows expresions or just limits
348 LIMIT_ONLY_LITERALS = False
349
350 # Whether a table is allowed to be renamed with a db
351 RENAME_TABLE_WITH_DB = True
352
353 # The separator for grouping sets and rollups
354 GROUPINGS_SEP = ","
355
356 # Whether GROUPING SETS can follow GROUP BY expressions without a comma
357 SUPPORTS_GROUPING_SETS_AS_SUFFIX = False
358
359 # The string used for creating an index on a table
360 INDEX_ON = "ON"
361
362 # Separator for IN/OUT parameter mode (Oracle uses " " for "IN OUT", PostgreSQL uses "" for "INOUT")
363 INOUT_SEPARATOR = " "
364
365 # Whether join hints should be generated
366 JOIN_HINTS = True
367
368 # Whether directed joins are supported
369 DIRECTED_JOINS = False
370
371 # Whether table hints should be generated
372 TABLE_HINTS = True
373
374 # Whether query hints should be generated
375 QUERY_HINTS = True
376
377 # What kind of separator to use for query hints
378 QUERY_HINT_SEP = ", "
379
380 # Whether comparing against booleans (e.g. x IS TRUE) is supported
381 IS_BOOL_ALLOWED = True
382
383 # Whether to include the "SET" keyword in the "INSERT ... ON DUPLICATE KEY UPDATE" statement
384 DUPLICATE_KEY_UPDATE_WITH_SET = True
385
386 # Whether to generate the limit as TOP <value> instead of LIMIT <value>
387 LIMIT_IS_TOP = False
388
389 # Whether to generate INSERT INTO ... RETURNING or INSERT INTO RETURNING ...
390 RETURNING_END = True
391
392 # Whether to generate an unquoted value for EXTRACT's date part argument
393 EXTRACT_ALLOWS_QUOTES = True
394
395 # Whether TIMETZ / TIMESTAMPTZ will be generated using the "WITH TIME ZONE" syntax
396 TZ_TO_WITH_TIME_ZONE = False
397
398 # Whether the NVL2 function is supported
399 NVL2_SUPPORTED = True
400
401 # https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
402 SELECT_KINDS: tuple[str, ...] = ("STRUCT", "VALUE")
403
404 # Whether VALUES statements can be used as derived tables.
405 # MySQL 5 and Redshift do not allow this, so when False, it will convert
406 # SELECT * VALUES into SELECT UNION
407 VALUES_AS_TABLE = True
408
409 # Whether the word COLUMN is included when adding a column with ALTER TABLE
410 ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = True
411
412 # UNNEST WITH ORDINALITY (presto) instead of UNNEST WITH OFFSET (bigquery)
413 UNNEST_WITH_ORDINALITY = True
414
415 # Whether JOIN sides (LEFT, RIGHT) are supported in conjunction with SEMI/ANTI join kinds
416 SEMI_ANTI_JOIN_WITH_SIDE = True
417
418 # Whether to include the type of a computed column in the CREATE DDL
419 COMPUTED_COLUMN_WITH_TYPE = True
420
421 # Whether CREATE TABLE .. COPY .. is supported. False means we'll generate CLONE instead of COPY
422 SUPPORTS_TABLE_COPY = True
423
424 # Whether parentheses are required around the table sample's expression
425 TABLESAMPLE_REQUIRES_PARENS = True
426
427 # Whether a table sample clause's size needs to be followed by the ROWS keyword
428 TABLESAMPLE_SIZE_IS_ROWS = True
429
430 # The keyword(s) to use when generating a sample clause
431 TABLESAMPLE_KEYWORDS = "TABLESAMPLE"
432
433 # Whether the TABLESAMPLE clause supports a method name, like BERNOULLI
434 TABLESAMPLE_WITH_METHOD = True
435
436 # The keyword to use when specifying the seed of a sample clause
437 TABLESAMPLE_SEED_KEYWORD = "SEED"
438
439 # Whether the historical data clause (AT ... / BEFORE ...) is generated after the table alias
440 HISTORICAL_DATA_POST_ALIAS = False
441
442 # Whether COLLATE is a function instead of a binary operator
443 COLLATE_IS_FUNC = False
444
445 # Whether data types support additional specifiers like e.g. CHAR or BYTE (oracle)
446 DATA_TYPE_SPECIFIERS_ALLOWED = False
447
448 # Whether conditions require booleans WHERE x = 0 vs WHERE x
449 ENSURE_BOOLS = False
450
451 # Whether the "RECURSIVE" keyword is required when defining recursive CTEs
452 CTE_RECURSIVE_KEYWORD_REQUIRED = True
453
454 # Whether CONCAT requires >1 arguments
455 SUPPORTS_SINGLE_ARG_CONCAT = True
456
457 # Whether LAST_DAY function supports a date part argument
458 LAST_DAY_SUPPORTS_DATE_PART = True
459
460 # Whether named columns are allowed in table aliases
461 SUPPORTS_TABLE_ALIAS_COLUMNS = True
462
463 # Whether named columns are allowed in CTE definitions
464 SUPPORTS_NAMED_CTE_COLUMNS = True
465
466 # Whether UNPIVOT aliases are Identifiers (False means they're Literals)
467 UNPIVOT_ALIASES_ARE_IDENTIFIERS = True
468
469 # Whether a (UN)PIVOT's alias is introduced with AS (Oracle rejects it, ORA-03048)
470 PIVOT_ALIAS_WITH_AS = True
471
472 # What delimiter to use for separating JSON key/value pairs
473 JSON_KEY_VALUE_PAIR_SEP = ":"
474
475 # INSERT OVERWRITE TABLE x override
476 INSERT_OVERWRITE = " OVERWRITE TABLE"
477
478 # Whether the SELECT .. INTO syntax is used instead of CTAS
479 SUPPORTS_SELECT_INTO = False
480
481 # Whether UNLOGGED tables can be created
482 SUPPORTS_UNLOGGED_TABLES = False
483
484 # Whether the CREATE TABLE LIKE statement is supported
485 SUPPORTS_CREATE_TABLE_LIKE = True
486
487 # Whether ALTER TABLE ... MODIFY COLUMN column-redefinition syntax is supported
488 SUPPORTS_MODIFY_COLUMN = False
489
490 # Whether ALTER TABLE ... CHANGE COLUMN column-rename-and-redefine syntax is supported
491 SUPPORTS_CHANGE_COLUMN = False
492
493 # Whether ALTER COLUMN can set a column's nullability together with its type
494 SUPPORTS_ALTER_COLUMN_NULLABILITY = False
495
496 # Whether ALTER COLUMN IF EXISTS is supported
497 SUPPORTS_ALTER_COLUMN_IF_EXISTS = False
498
499 # Whether the LikeProperty needs to be specified inside of the schema clause
500 LIKE_PROPERTY_INSIDE_SCHEMA = False
501
502 # Whether DISTINCT can be followed by multiple args in an AggFunc. If not, it will be
503 # transpiled into a series of CASE-WHEN-ELSE, ultimately using a tuple conseisting of the args
504 MULTI_ARG_DISTINCT = True
505
506 # Whether the JSON extraction operators expect a value of type JSON
507 JSON_TYPE_REQUIRED_FOR_EXTRACTION = False
508
509 # Whether bracketed keys like ["foo"] are supported in JSON paths
510 JSON_PATH_BRACKETED_KEY_SUPPORTED = True
511
512 # Whether to escape keys using single quotes in JSON paths
513 JSON_PATH_SINGLE_QUOTE_ESCAPE = False
514
515 # Whether a quoted JSON path key (e.g. from a quoted identifier or ['key'] bracket) must be
516 # rendered in bracket form to preserve its case-sensitivity, even if it would otherwise match
517 # SAFE_JSON_PATH_KEY_RE and render as a bare dotted key. Needed for dialects like Databricks
518 # where a bare colon key is case-insensitive but a bracketed key is case-sensitive.
519 JSON_PATH_KEY_QUOTED_FORCES_BRACKETS = False
520
521 # The JSONPathPart expressions supported by this dialect
522 SUPPORTED_JSON_PATH_PARTS: t.ClassVar = ALL_JSON_PATH_PARTS.copy()
523
524 # Whether any(f(x) for x in array) can be implemented by this dialect
525 CAN_IMPLEMENT_ARRAY_ANY = False
526
527 # Whether the function TO_NUMBER is supported
528 SUPPORTS_TO_NUMBER = True
529
530 # Whether EXCLUDE in window specification is supported
531 SUPPORTS_WINDOW_EXCLUDE = False
532
533 # Whether or not set op modifiers apply to the outer set op or select.
534 # SELECT * FROM x UNION SELECT * FROM y LIMIT 1
535 # True means limit 1 happens after the set op, False means it it happens on y.
536 SET_OP_MODIFIERS = True
537
538 # Whether a SELECT operand can have a branch-local LIMIT/TOP without parentheses.
539 SET_OP_LIMITS = False
540
541 # Whether set operation operands can be parenthesized without a SELECT wrapper.
542 SET_OP_PARENTHESIZED_OPERANDS = True
543
544 # Whether parameters from COPY statement are wrapped in parentheses
545 COPY_PARAMS_ARE_WRAPPED = True
546
547 # Whether values of params are set with "=" token or empty space
548 COPY_PARAMS_EQ_REQUIRED = False
549
550 # Whether COPY statement has INTO keyword
551 COPY_HAS_INTO_KEYWORD = True
552
553 # Whether the conditional TRY(expression) function is supported
554 TRY_SUPPORTED = True
555
556 # Whether the UESCAPE syntax in unicode strings is supported
557 SUPPORTS_UESCAPE = True
558
559 # Function used to replace escaped unicode codes in unicode strings
560 UNICODE_SUBSTITUTE: t.ClassVar[t.Any] = None
561
562 # The keyword to use when generating a star projection with excluded columns
563 STAR_EXCEPT = "EXCEPT"
564
565 # The HEX function name
566 HEX_FUNC = "HEX"
567
568 # The keywords to use when prefixing & separating WITH based properties
569 WITH_PROPERTIES_PREFIX = "WITH"
570
571 # Whether to quote the generated expression of exp.JsonPath
572 QUOTE_JSON_PATH = True
573
574 # Whether the text pattern/fill (3rd) parameter of RPAD()/LPAD() is optional (defaults to space)
575 PAD_FILL_PATTERN_IS_REQUIRED = False
576
577 # Whether a projection can explode into multiple rows, e.g. by unnesting an array.
578 SUPPORTS_EXPLODING_PROJECTIONS = True
579
580 # Whether ARRAY_CONCAT can be generated with varlen args or if it should be reduced to 2-arg version
581 ARRAY_CONCAT_IS_VAR_LEN = True
582
583 # Whether CONVERT_TIMEZONE() is supported; if not, it will be generated as exp.AtTimeZone
584 SUPPORTS_CONVERT_TIMEZONE = False
585
586 # Whether MEDIAN(expr) is supported; if not, it will be generated as PERCENTILE_CONT(expr, 0.5)
587 SUPPORTS_MEDIAN = True
588
589 # Whether UNIX_SECONDS(timestamp) is supported
590 SUPPORTS_UNIX_SECONDS = False
591
592 # Whether to wrap <props> in `AlterSet`, e.g., ALTER ... SET (<props>)
593 ALTER_SET_WRAPPED = False
594
595 # Whether to normalize the date parts in EXTRACT(<date_part> FROM <expr>) into a common representation
596 # For instance, to extract the day of week in ISO semantics, one can use ISODOW, DAYOFWEEKISO etc depending on the dialect.
597 # TODO: The normalization should be done by default once we've tested it across all dialects.
598 NORMALIZE_EXTRACT_DATE_PARTS = False
599
600 # The name to generate for the JSONPath expression. If `None`, only `this` will be generated
601 PARSE_JSON_NAME: str | None = "PARSE_JSON"
602
603 # The function name of the exp.ArraySize expression
604 ARRAY_SIZE_NAME: str = "ARRAY_LENGTH"
605
606 # The syntax to use when altering the type of a column
607 ALTER_SET_TYPE = "SET DATA TYPE"
608
609 # Whether exp.ArraySize should generate the dimension arg too (valid for Postgres & DuckDB)
610 # None -> Doesn't support it at all
611 # False (DuckDB) -> Has backwards-compatible support, but preferably generated without
612 # True (Postgres) -> Explicitly requires it
613 ARRAY_SIZE_DIM_REQUIRED: bool | None = None
614
615 # Whether a multi-argument DECODE(...) function is supported. If not, a CASE expression is generated
616 SUPPORTS_DECODE_CASE = True
617
618 # Whether SYMMETRIC and ASYMMETRIC flags are supported with BETWEEN expression
619 SUPPORTS_BETWEEN_FLAGS = False
620
621 # Whether LIKE and ILIKE support quantifiers such as LIKE ANY/ALL/SOME
622 SUPPORTS_LIKE_QUANTIFIERS = True
623
624 # Prefix which is appended to exp.Table expressions in MATCH AGAINST
625 MATCH_AGAINST_TABLE_PREFIX: str | None = None
626
627 # Whether to include the VARIABLE keyword for SET assignments
628 SET_ASSIGNMENT_REQUIRES_VARIABLE_KEYWORD = False
629
630 # The keyword to use for default value assignment in DECLARE statements
631 DECLARE_DEFAULT_ASSIGNMENT = "="
632
633 # Whether FROM is supported in UPDATE statements or if joins must be generated instead, e.g:
634 # Supported (Postgres, Doris etc): UPDATE t1 SET t1.a = t2.b FROM t2
635 # Unsupported (MySQL, SingleStore): UPDATE t1 JOIN t2 ON TRUE SET t1.a = t2.b
636 UPDATE_STATEMENT_SUPPORTS_FROM = True
637
638 # Whether SELECT *, ... EXCLUDE requires wrapping in a subquery for transpilation.
639 STAR_EXCLUDE_REQUIRES_DERIVED_TABLE = True
640
641 # Whether DROP and ALTER statements against Iceberg tables include 'ICEBERG', e.g.:
642 # - Snowflake: DROP ICEBERG TABLE a.b;
643 # - DuckDB: DROP TABLE a.b;
644 SUPPORTS_DROP_ALTER_ICEBERG_PROPERTY = True
645
646 TYPE_MAPPING: t.ClassVar = {
647 exp.DType.DATETIME2: "TIMESTAMP",
648 exp.DType.NCHAR: "CHAR",
649 exp.DType.NVARCHAR: "VARCHAR",
650 exp.DType.MEDIUMTEXT: "TEXT",
651 exp.DType.LONGTEXT: "TEXT",
652 exp.DType.TINYTEXT: "TEXT",
653 exp.DType.BLOB: "VARBINARY",
654 exp.DType.MEDIUMBLOB: "BLOB",
655 exp.DType.LONGBLOB: "BLOB",
656 exp.DType.TINYBLOB: "BLOB",
657 exp.DType.INET: "INET",
658 exp.DType.ROWVERSION: "VARBINARY",
659 exp.DType.SMALLDATETIME: "TIMESTAMP",
660 }
661
662 UNSUPPORTED_TYPES: t.ClassVar[set[exp.DType]] = set()
663
664 # mapping of DType to its default parameters, bounds
665 TYPE_PARAM_SETTINGS: t.ClassVar[
666 dict[exp.DType, tuple[tuple[int, ...], tuple[int | None, ...]]]
667 ] = {}
668
669 TIME_PART_SINGULARS: t.ClassVar = {
670 "MICROSECONDS": "MICROSECOND",
671 "SECONDS": "SECOND",
672 "MINUTES": "MINUTE",
673 "HOURS": "HOUR",
674 "DAYS": "DAY",
675 "WEEKS": "WEEK",
676 "MONTHS": "MONTH",
677 "QUARTERS": "QUARTER",
678 "YEARS": "YEAR",
679 }
680
681 AFTER_HAVING_MODIFIER_TRANSFORMS: t.ClassVar = {
682 "cluster": lambda self, e: self.sql(e, "cluster"),
683 "distribute": lambda self, e: self.sql(e, "distribute"),
684 "sort": lambda self, e: self.sql(e, "sort"),
685 **AFTER_HAVING_MODIFIER_TRANSFORMS,
686 }
687
688 TOKEN_MAPPING: t.ClassVar[dict[TokenType, str]] = {}
689
690 STRUCT_DELIMITER: t.ClassVar = ("<", ">")
691
692 PARAMETER_TOKEN = "@"
693 NAMED_PLACEHOLDER_TOKEN = ":"
694
695 EXPRESSION_PRECEDES_PROPERTIES_CREATABLES: t.ClassVar[set[str]] = set()
696
697 PROPERTIES_LOCATION: t.ClassVar = {
698 exp.AllowedValuesProperty: exp.Properties.Location.POST_SCHEMA,
699 exp.AlgorithmProperty: exp.Properties.Location.POST_CREATE,
700 exp.ApiProperty: exp.Properties.Location.POST_CREATE,
701 exp.ApplicationProperty: exp.Properties.Location.POST_CREATE,
702 exp.AutoIncrementProperty: exp.Properties.Location.POST_SCHEMA,
703 exp.AutoRefreshProperty: exp.Properties.Location.POST_SCHEMA,
704 exp.BackupProperty: exp.Properties.Location.POST_SCHEMA,
705 exp.BlockCompressionProperty: exp.Properties.Location.POST_NAME,
706 exp.CalledOnNullInputProperty: exp.Properties.Location.POST_SCHEMA,
707 exp.CatalogProperty: exp.Properties.Location.POST_CREATE,
708 exp.CharacterSetProperty: exp.Properties.Location.POST_SCHEMA,
709 exp.ChecksumProperty: exp.Properties.Location.POST_NAME,
710 exp.CollateProperty: exp.Properties.Location.POST_SCHEMA,
711 exp.ComputeProperty: exp.Properties.Location.POST_CREATE,
712 exp.CopyGrantsProperty: exp.Properties.Location.POST_SCHEMA,
713 exp.Cluster: exp.Properties.Location.POST_SCHEMA,
714 exp.ClusteredByProperty: exp.Properties.Location.POST_SCHEMA,
715 exp.ClusterProperty: exp.Properties.Location.POST_SCHEMA,
716 exp.DistributedByProperty: exp.Properties.Location.POST_SCHEMA,
717 exp.DuplicateKeyProperty: exp.Properties.Location.POST_SCHEMA,
718 exp.DataBlocksizeProperty: exp.Properties.Location.POST_NAME,
719 exp.DatabaseProperty: exp.Properties.Location.POST_CREATE,
720 exp.DataDeletionProperty: exp.Properties.Location.POST_SCHEMA,
721 exp.DefinerProperty: exp.Properties.Location.POST_CREATE,
722 exp.DictRange: exp.Properties.Location.POST_SCHEMA,
723 exp.DictProperty: exp.Properties.Location.POST_SCHEMA,
724 exp.DynamicProperty: exp.Properties.Location.POST_CREATE,
725 exp.DistKeyProperty: exp.Properties.Location.POST_SCHEMA,
726 exp.DistStyleProperty: exp.Properties.Location.POST_SCHEMA,
727 exp.EmptyProperty: exp.Properties.Location.POST_SCHEMA,
728 exp.EncodeProperty: exp.Properties.Location.POST_EXPRESSION,
729 exp.EngineProperty: exp.Properties.Location.POST_SCHEMA,
730 exp.EnviromentProperty: exp.Properties.Location.POST_SCHEMA,
731 exp.HandlerProperty: exp.Properties.Location.POST_SCHEMA,
732 exp.ParameterStyleProperty: exp.Properties.Location.POST_SCHEMA,
733 exp.ExecuteAsProperty: exp.Properties.Location.POST_SCHEMA,
734 exp.ExternalProperty: exp.Properties.Location.POST_CREATE,
735 exp.FallbackProperty: exp.Properties.Location.POST_NAME,
736 exp.FileFormatProperty: exp.Properties.Location.POST_WITH,
737 exp.FreespaceProperty: exp.Properties.Location.POST_NAME,
738 exp.GlobalProperty: exp.Properties.Location.POST_CREATE,
739 exp.HeapProperty: exp.Properties.Location.POST_WITH,
740 exp.HybridProperty: exp.Properties.Location.POST_CREATE,
741 exp.InheritsProperty: exp.Properties.Location.POST_SCHEMA,
742 exp.IcebergProperty: exp.Properties.Location.POST_CREATE,
743 exp.IncludeProperty: exp.Properties.Location.POST_SCHEMA,
744 exp.InputModelProperty: exp.Properties.Location.POST_SCHEMA,
745 exp.IsolatedLoadingProperty: exp.Properties.Location.POST_NAME,
746 exp.JournalProperty: exp.Properties.Location.POST_NAME,
747 exp.LanguageProperty: exp.Properties.Location.POST_SCHEMA,
748 exp.LikeProperty: exp.Properties.Location.POST_SCHEMA,
749 exp.LocationProperty: exp.Properties.Location.POST_SCHEMA,
750 exp.LockProperty: exp.Properties.Location.POST_SCHEMA,
751 exp.LockingProperty: exp.Properties.Location.POST_ALIAS,
752 exp.LogProperty: exp.Properties.Location.POST_NAME,
753 exp.MaskingProperty: exp.Properties.Location.POST_CREATE,
754 exp.MaterializedProperty: exp.Properties.Location.POST_CREATE,
755 exp.MergeBlockRatioProperty: exp.Properties.Location.POST_NAME,
756 exp.ModuleProperty: exp.Properties.Location.POST_SCHEMA,
757 exp.NetworkProperty: exp.Properties.Location.POST_CREATE,
758 exp.NoPrimaryIndexProperty: exp.Properties.Location.POST_EXPRESSION,
759 exp.OnProperty: exp.Properties.Location.POST_SCHEMA,
760 exp.OnCommitProperty: exp.Properties.Location.POST_EXPRESSION,
761 exp.Order: exp.Properties.Location.POST_SCHEMA,
762 exp.OutputModelProperty: exp.Properties.Location.POST_SCHEMA,
763 exp.PartitionedByProperty: exp.Properties.Location.POST_WITH,
764 exp.PartitionedOfProperty: exp.Properties.Location.POST_SCHEMA,
765 exp.PrimaryKey: exp.Properties.Location.POST_SCHEMA,
766 exp.Property: exp.Properties.Location.POST_WITH,
767 exp.RefreshTriggerProperty: exp.Properties.Location.POST_SCHEMA,
768 exp.RemoteWithConnectionModelProperty: exp.Properties.Location.POST_SCHEMA,
769 exp.ReturnsProperty: exp.Properties.Location.POST_SCHEMA,
770 exp.RollupProperty: exp.Properties.Location.UNSUPPORTED,
771 exp.RowAccessProperty: exp.Properties.Location.UNSUPPORTED,
772 exp.RowFormatProperty: exp.Properties.Location.POST_SCHEMA,
773 exp.RowFormatDelimitedProperty: exp.Properties.Location.POST_SCHEMA,
774 exp.RowFormatSerdeProperty: exp.Properties.Location.POST_SCHEMA,
775 exp.SampleProperty: exp.Properties.Location.POST_SCHEMA,
776 exp.SchemaCommentProperty: exp.Properties.Location.POST_SCHEMA,
777 exp.SecureProperty: exp.Properties.Location.POST_CREATE,
778 exp.SecurityIntegrationProperty: exp.Properties.Location.POST_CREATE,
779 exp.SerdeProperties: exp.Properties.Location.POST_SCHEMA,
780 exp.Set: exp.Properties.Location.POST_SCHEMA,
781 exp.SettingsProperty: exp.Properties.Location.POST_SCHEMA,
782 exp.SetProperty: exp.Properties.Location.POST_CREATE,
783 exp.SetConfigProperty: exp.Properties.Location.POST_SCHEMA,
784 exp.SharingProperty: exp.Properties.Location.POST_EXPRESSION,
785 exp.SequenceProperties: exp.Properties.Location.POST_EXPRESSION,
786 exp.TriggerProperties: exp.Properties.Location.POST_EXPRESSION,
787 exp.SortKeyProperty: exp.Properties.Location.POST_SCHEMA,
788 exp.SqlReadWriteProperty: exp.Properties.Location.POST_SCHEMA,
789 exp.SqlSecurityProperty: exp.Properties.Location.POST_SCHEMA,
790 exp.StabilityProperty: exp.Properties.Location.POST_SCHEMA,
791 exp.StorageHandlerProperty: exp.Properties.Location.POST_SCHEMA,
792 exp.StreamingTableProperty: exp.Properties.Location.POST_CREATE,
793 exp.StrictProperty: exp.Properties.Location.POST_SCHEMA,
794 exp.Tags: exp.Properties.Location.POST_WITH,
795 exp.TemporaryProperty: exp.Properties.Location.POST_CREATE,
796 exp.ToTableProperty: exp.Properties.Location.POST_SCHEMA,
797 exp.TransientProperty: exp.Properties.Location.POST_CREATE,
798 exp.TransformModelProperty: exp.Properties.Location.POST_SCHEMA,
799 exp.MergeTreeTTL: exp.Properties.Location.POST_SCHEMA,
800 exp.UnloggedProperty: exp.Properties.Location.POST_CREATE,
801 exp.UsingProperty: exp.Properties.Location.POST_EXPRESSION,
802 exp.UsingTemplateProperty: exp.Properties.Location.POST_SCHEMA,
803 exp.ViewAttributeProperty: exp.Properties.Location.POST_SCHEMA,
804 exp.VirtualProperty: exp.Properties.Location.POST_CREATE,
805 exp.VolatileProperty: exp.Properties.Location.POST_CREATE,
806 exp.WithDataProperty: exp.Properties.Location.POST_EXPRESSION,
807 exp.WithJournalTableProperty: exp.Properties.Location.POST_NAME,
808 exp.WithProcedureOptions: exp.Properties.Location.POST_SCHEMA,
809 exp.WithSchemaBindingProperty: exp.Properties.Location.POST_SCHEMA,
810 exp.WithSystemVersioningProperty: exp.Properties.Location.POST_SCHEMA,
811 exp.ForceProperty: exp.Properties.Location.POST_CREATE,
812 }
813
814 # Keywords that can't be used as unquoted identifier names
815 RESERVED_KEYWORDS: t.ClassVar[set[str]] = set()
816
817 # Exprs whose comments are separated from them for better formatting
818 WITH_SEPARATED_COMMENTS: t.ClassVar[tuple[type[exp.Expr], ...]] = (
819 exp.Command,
820 exp.Create,
821 exp.Describe,
822 exp.Delete,
823 exp.Drop,
824 exp.From,
825 exp.Insert,
826 exp.Join,
827 exp.MultitableInserts,
828 exp.Order,
829 exp.Group,
830 exp.Having,
831 exp.Select,
832 exp.SetOperation,
833 exp.Update,
834 exp.Where,
835 exp.With,
836 )
837
838 # Exprs that should not have their comments generated in maybe_comment
839 EXCLUDE_COMMENTS: t.ClassVar[tuple[type[exp.Expr], ...]] = (
840 exp.Binary,
841 exp.SetOperation,
842 )
843
844 # Exprs that can remain unwrapped when appearing in the context of an INTERVAL
845 UNWRAPPED_INTERVAL_VALUES: t.ClassVar[tuple[type[exp.Expr], ...]] = (
846 exp.Column,
847 exp.Literal,
848 exp.Neg,
849 exp.Paren,
850 )
851
852 PARAMETERIZABLE_TEXT_TYPES: t.ClassVar = {
853 exp.DType.NVARCHAR,
854 exp.DType.VARCHAR,
855 exp.DType.CHAR,
856 exp.DType.NCHAR,
857 }
858
859 # Exprs that need to have all CTEs under them bubbled up to them
860 EXPRESSIONS_WITHOUT_NESTED_CTES: t.ClassVar[set[type[exp.Expr]]] = set()
861
862 RESPECT_IGNORE_NULLS_UNSUPPORTED_EXPRESSIONS: t.ClassVar[tuple[type[exp.Expr], ...]] = ()
863
864 MOD_OPERATOR = "%"
865
866 # Infix operators that bind at least as tightly as %, so a Mod on their right side needs parentheses
867 MOD_PAREN_PARENT_TYPES: t.ClassVar[tuple[type[exp.Expr], ...]] = (
868 exp.Mul,
869 exp.Div,
870 exp.IntDiv,
871 exp.Mod,
872 )
873
874 SAFE_JSON_PATH_KEY_RE: t.ClassVar = exp.SAFE_IDENTIFIER_RE
875
876 SENTINEL_LINE_BREAK = "__SQLGLOT__LB__"
877
878 __slots__ = (
879 "pretty",
880 "identify",
881 "normalize",
882 "pad",
883 "_indent",
884 "normalize_functions",
885 "unsupported_level",
886 "max_unsupported",
887 "leading_comma",
888 "max_text_width",
889 "comments",
890 "dialect",
891 "unsupported_messages",
892 "_escaped_quote_end",
893 "_escaped_byte_quote_end",
894 "_escaped_identifier_end",
895 "_next_name",
896 "_identifier_start",
897 "_identifier_end",
898 "_quote_json_path_key_using_brackets",
899 "_dispatch",
900 )
901
902 def __init__(
903 self,
904 pretty: bool | int | None = None,
905 identify: str | bool = False,
906 normalize: bool = False,
907 pad: int = 2,
908 indent: int = 2,
909 normalize_functions: str | bool | None = None,
910 unsupported_level: ErrorLevel = ErrorLevel.WARN,
911 max_unsupported: int = 3,
912 leading_comma: bool = False,
913 max_text_width: int = 80,
914 comments: bool = True,
915 dialect: DialectType = None,
916 ):
917 import sqlglot
918 import sqlglot.dialects.dialect
919
920 self.pretty = pretty if pretty is not None else sqlglot.pretty
921 self.identify = identify
922 self.normalize = normalize
923 self.pad = pad
924 self._indent = indent
925 self.unsupported_level = unsupported_level
926 self.max_unsupported = max_unsupported
927 self.leading_comma = leading_comma
928 self.max_text_width = max_text_width
929 self.comments = comments
930 self.dialect = sqlglot.dialects.dialect.Dialect.get_or_raise(dialect)
931
932 # This is both a Dialect property and a Generator argument, so we prioritize the latter
933 self.normalize_functions = (
934 self.dialect.NORMALIZE_FUNCTIONS if normalize_functions is None else normalize_functions
935 )
936
937 self.unsupported_messages: list[str] = []
938 self._escaped_quote_end: str = (
939 self.dialect.tokenizer_class.STRING_ESCAPES[0] + self.dialect.QUOTE_END
940 )
941 self._escaped_byte_quote_end: str = (
942 self.dialect.tokenizer_class.STRING_ESCAPES[0] + self.dialect.BYTE_END
943 if self.dialect.BYTE_END
944 else ""
945 )
946 self._escaped_identifier_end = self.dialect.IDENTIFIER_END * 2
947
948 self._next_name = name_sequence("_t")
949
950 self._identifier_start = self.dialect.IDENTIFIER_START
951 self._identifier_end = self.dialect.IDENTIFIER_END
952
953 self._quote_json_path_key_using_brackets = True
954
955 cls = type(self)
956 dispatch = _DISPATCH_CACHE.get(cls)
957 if dispatch is None:
958 dispatch = _build_dispatch(cls)
959 _DISPATCH_CACHE[cls] = dispatch
960 self._dispatch = dispatch
961
962 def generate(self, expression: exp.Expr, copy: bool = True) -> str:
963 """
964 Generates the SQL string corresponding to the given syntax tree.
965
966 Args:
967 expression: The syntax tree.
968 copy: Whether to copy the expression. The generator performs mutations so
969 it is safer to copy.
970
971 Returns:
972 The SQL string corresponding to `expression`.
973 """
974 if copy:
975 expression = expression.copy()
976
977 expression = self.preprocess(expression)
978
979 self.unsupported_messages = []
980 sql = self.sql(expression).strip()
981
982 if self.pretty:
983 sql = sql.replace(self.SENTINEL_LINE_BREAK, "\n")
984
985 if self.unsupported_level == ErrorLevel.IGNORE:
986 return sql
987
988 if self.unsupported_level == ErrorLevel.WARN:
989 for msg in self.unsupported_messages:
990 logger.warning(msg)
991 elif self.unsupported_level == ErrorLevel.RAISE and self.unsupported_messages:
992 raise UnsupportedError(concat_messages(self.unsupported_messages, self.max_unsupported))
993
994 return sql
995
996 def preprocess(self, expression: exp.Expr) -> exp.Expr:
997 """Apply generic preprocessing transformations to a given expression."""
998 expression = self._move_ctes_to_top_level(expression)
999
1000 if self.ENSURE_BOOLS:
1001 import sqlglot.transforms
1002
1003 expression = sqlglot.transforms.ensure_bools(expression)
1004
1005 return expression
1006
1007 def _move_ctes_to_top_level(self, expression: E) -> E:
1008 if (
1009 not expression.parent
1010 and type(expression) in self.EXPRESSIONS_WITHOUT_NESTED_CTES
1011 and any(node.parent is not expression for node in expression.find_all(exp.With))
1012 ):
1013 import sqlglot.transforms
1014
1015 expression = sqlglot.transforms.move_ctes_to_top_level(expression)
1016 return expression
1017
1018 def unsupported(self, message: str) -> None:
1019 if self.unsupported_level == ErrorLevel.IMMEDIATE:
1020 raise UnsupportedError(message)
1021 self.unsupported_messages.append(message)
1022
1023 def sep(self, sep: str = " ") -> str:
1024 return f"{sep.strip()}\n" if self.pretty else sep
1025
1026 def seg(self, sql: str, sep: str = " ") -> str:
1027 return f"{self.sep(sep)}{sql}"
1028
1029 def sanitize_comment(self, comment: str) -> str:
1030 comment = " " + comment if comment[0].strip() else comment
1031 comment = comment + " " if comment[-1].strip() else comment
1032
1033 # Escape block comment markers to prevent premature closure or unintended nesting.
1034 # This is necessary because single-line comments (--) are converted to block comments
1035 # (/* */) on output, and any */ in the original text would close the comment early.
1036 comment = comment.replace("*/", "* /").replace("/*", "/ *")
1037
1038 return comment
1039
1040 def maybe_comment(
1041 self,
1042 sql: str,
1043 expression: exp.Expr | None = None,
1044 comments: list[str] | None = None,
1045 separated: bool = False,
1046 ) -> str:
1047 comments = (
1048 ((expression and expression.comments) if comments is None else comments) # type: ignore
1049 if self.comments
1050 else None
1051 )
1052
1053 if not comments or isinstance(expression, self.EXCLUDE_COMMENTS):
1054 return sql
1055
1056 comments_list = [
1057 f"/*{self._replace_line_breaks(self.sanitize_comment(comment))}*/"
1058 for comment in comments
1059 if comment
1060 ]
1061
1062 if not comments_list:
1063 return sql
1064
1065 if separated or isinstance(expression, self.WITH_SEPARATED_COMMENTS):
1066 comments_sql = self.sep().join(comments_list)
1067 return (
1068 f"{self.sep()}{comments_sql}{sql}"
1069 if not sql or sql[0].isspace()
1070 else f"{comments_sql}{self.sep()}{sql}"
1071 )
1072
1073 return f"{sql} {' '.join(comments_list)}"
1074
1075 def wrap(self, expression: exp.Expr | str) -> str:
1076 this_sql = (
1077 self.sql(expression)
1078 if isinstance(expression, exp.UNWRAPPED_QUERIES)
1079 else self.sql(expression, "this")
1080 )
1081 if not this_sql:
1082 return "()"
1083
1084 this_sql = self.indent(this_sql, level=1, pad=0)
1085 return f"({self.sep('')}{this_sql}{self.seg(')', sep='')}"
1086
1087 def no_identify(self, func: t.Callable[..., str], *args, **kwargs) -> str:
1088 original = self.identify
1089 self.identify = False
1090 result = func(*args, **kwargs)
1091 self.identify = original
1092 return result
1093
1094 def normalize_func(self, name: str) -> str:
1095 if self.normalize_functions == "upper" or self.normalize_functions is True:
1096 return name.upper()
1097 if self.normalize_functions == "lower":
1098 return name.lower()
1099 return name
1100
1101 def indent(
1102 self,
1103 sql: str,
1104 level: int = 0,
1105 pad: int | None = None,
1106 skip_first: bool = False,
1107 skip_last: bool = False,
1108 ) -> str:
1109 if not self.pretty or not sql:
1110 return sql
1111
1112 pad = self.pad if pad is None else pad
1113 lines = sql.split("\n")
1114
1115 return "\n".join(
1116 (
1117 line
1118 if (skip_first and i == 0) or (skip_last and i == len(lines) - 1)
1119 else f"{' ' * (level * self._indent + pad)}{line}"
1120 )
1121 for i, line in enumerate(lines)
1122 )
1123
1124 def sql(
1125 self,
1126 expression: str | exp.Expr | None,
1127 key: str | None = None,
1128 comment: bool = True,
1129 ) -> str:
1130 if not expression:
1131 return ""
1132
1133 if isinstance(expression, str):
1134 return expression
1135
1136 if key:
1137 value = expression.args.get(key)
1138 if value:
1139 return self.sql(value)
1140 return ""
1141
1142 handler = self._dispatch.get(expression.__class__)
1143
1144 if handler:
1145 sql = handler(self, expression)
1146 elif isinstance(expression, exp.Func):
1147 sql = self.function_fallback_sql(expression)
1148 elif isinstance(expression, exp.Property):
1149 sql = self.property_sql(expression)
1150 else:
1151 raise ValueError(f"Unsupported expression type {expression.__class__.__name__}")
1152
1153 return self.maybe_comment(sql, expression) if self.comments and comment else sql
1154
1155 def uncache_sql(self, expression: exp.Uncache) -> str:
1156 table = self.sql(expression, "this")
1157 exists_sql = " IF EXISTS" if expression.args.get("exists") else ""
1158 return f"UNCACHE TABLE{exists_sql} {table}"
1159
1160 def cache_sql(self, expression: exp.Cache) -> str:
1161 lazy = " LAZY" if expression.args.get("lazy") else ""
1162 table = self.sql(expression, "this")
1163 options = expression.args.get("options")
1164 options = f" OPTIONS({self.sql(options[0])} = {self.sql(options[1])})" if options else ""
1165 sql = self.sql(expression, "expression")
1166 sql = f" AS{self.sep()}{sql}" if sql else ""
1167 sql = f"CACHE{lazy} TABLE {table}{options}{sql}"
1168 return self.prepend_ctes(expression, sql)
1169
1170 def characterset_sql(self, expression: exp.CharacterSet) -> str:
1171 default = "DEFAULT " if expression.args.get("default") else ""
1172 return f"{default}CHARACTER SET={self.sql(expression, 'this')}"
1173
1174 def column_parts(self, expression: exp.Column) -> str:
1175 if expression.args.get("shadow") and self.dialect.PROJECTION_ALIASES_SHADOW_SOURCE_NAMES:
1176 # The qualifier would be captured by a colliding projection alias (see qualify_columns)
1177 return self.sql(expression, "this")
1178
1179 return ".".join(
1180 self.sql(part)
1181 for part in (
1182 expression.args.get("catalog"),
1183 expression.args.get("db"),
1184 expression.args.get("table"),
1185 expression.args.get("this"),
1186 )
1187 if part
1188 )
1189
1190 def column_sql(self, expression: exp.Column) -> str:
1191 join_mark = " (+)" if expression.args.get("join_mark") else ""
1192
1193 if join_mark and not self.dialect.SUPPORTS_COLUMN_JOIN_MARKS:
1194 join_mark = ""
1195 self.unsupported("Outer join syntax using the (+) operator is not supported.")
1196
1197 return f"{self.column_parts(expression)}{join_mark}"
1198
1199 def pseudocolumn_sql(self, expression: exp.Pseudocolumn) -> str:
1200 return self.column_sql(expression)
1201
1202 def columnposition_sql(self, expression: exp.ColumnPosition) -> str:
1203 this = self.sql(expression, "this")
1204 this = f" {this}" if this else ""
1205 position = self.sql(expression, "position")
1206 return f"{position}{this}"
1207
1208 def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
1209 column = self.sql(expression, "this")
1210 kind = self.sql(expression, "kind")
1211 constraints = self.expressions(expression, key="constraints", sep=" ", flat=True)
1212 exists = "IF NOT EXISTS " if expression.args.get("exists") else ""
1213 kind = f"{sep}{kind}" if kind else ""
1214 constraints = f" {constraints}" if constraints else ""
1215 position = self.sql(expression, "position")
1216 position = f" {position}" if position else ""
1217
1218 if expression.find(exp.ComputedColumnConstraint) and not self.COMPUTED_COLUMN_WITH_TYPE:
1219 kind = ""
1220
1221 return f"{exists}{column}{kind}{constraints}{position}"
1222
1223 def columnconstraint_sql(self, expression: exp.ColumnConstraint) -> str:
1224 this = self.sql(expression, "this")
1225 kind_sql = self.sql(expression, "kind").strip()
1226 return f"CONSTRAINT {this} {kind_sql}" if this else kind_sql
1227
1228 def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
1229 this = self.sql(expression, "this")
1230 if expression.args.get("not_null"):
1231 persisted = " PERSISTED NOT NULL"
1232 elif expression.args.get("persisted"):
1233 persisted = " PERSISTED"
1234 else:
1235 persisted = ""
1236
1237 return f"AS {this}{persisted}"
1238
1239 def autoincrementcolumnconstraint_sql(self, _: exp.AutoIncrementColumnConstraint) -> str:
1240 return self.token_sql(TokenType.AUTO_INCREMENT)
1241
1242 def compresscolumnconstraint_sql(self, expression: exp.CompressColumnConstraint) -> str:
1243 if isinstance(expression.this, list):
1244 this = self.wrap(self.expressions(expression, key="this", flat=True))
1245 else:
1246 this = self.sql(expression, "this")
1247
1248 return f"COMPRESS {this}"
1249
1250 def generatedasidentitycolumnconstraint_sql(
1251 self, expression: exp.GeneratedAsIdentityColumnConstraint
1252 ) -> str:
1253 this = ""
1254 if expression.this is not None:
1255 on_null = " ON NULL" if expression.args.get("on_null") else ""
1256 this = " ALWAYS" if expression.this else f" BY DEFAULT{on_null}"
1257
1258 start = expression.args.get("start")
1259 start = f"START WITH {start}" if start else ""
1260 increment = expression.args.get("increment")
1261 increment = f" INCREMENT BY {increment}" if increment else ""
1262 minvalue = expression.args.get("minvalue")
1263 minvalue = f" MINVALUE {minvalue}" if minvalue else ""
1264 maxvalue = expression.args.get("maxvalue")
1265 maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else ""
1266 cycle = expression.args.get("cycle")
1267 cycle_sql = ""
1268
1269 if cycle is not None:
1270 cycle_sql = f"{' NO' if not cycle else ''} CYCLE"
1271 cycle_sql = cycle_sql.strip() if not start and not increment else cycle_sql
1272
1273 sequence_opts = ""
1274 if start or increment or cycle_sql:
1275 sequence_opts = f"{start}{increment}{minvalue}{maxvalue}{cycle_sql}"
1276 sequence_opts = f" ({sequence_opts.strip()})"
1277
1278 expr = self.sql(expression, "expression")
1279 expr = f"({expr})" if expr else "IDENTITY"
1280
1281 return f"GENERATED{this} AS {expr}{sequence_opts}"
1282
1283 def generatedasrowcolumnconstraint_sql(
1284 self, expression: exp.GeneratedAsRowColumnConstraint
1285 ) -> str:
1286 start = "START" if expression.args.get("start") else "END"
1287 hidden = " HIDDEN" if expression.args.get("hidden") else ""
1288 return f"GENERATED ALWAYS AS ROW {start}{hidden}"
1289
1290 def periodforsystemtimeconstraint_sql(
1291 self, expression: exp.PeriodForSystemTimeConstraint
1292 ) -> str:
1293 return f"PERIOD FOR SYSTEM_TIME ({self.sql(expression, 'this')}, {self.sql(expression, 'expression')})"
1294
1295 def notnullcolumnconstraint_sql(self, expression: exp.NotNullColumnConstraint) -> str:
1296 return f"{'' if expression.args.get('allow_null') else 'NOT '}NULL"
1297
1298 def primarykeycolumnconstraint_sql(self, expression: exp.PrimaryKeyColumnConstraint) -> str:
1299 desc = expression.args.get("desc")
1300 if desc is not None:
1301 return f"PRIMARY KEY{' DESC' if desc else ' ASC'}"
1302 options = self.expressions(expression, key="options", flat=True, sep=" ")
1303 options = f" {options}" if options else ""
1304 return f"PRIMARY KEY{options}"
1305
1306 def uniquecolumnconstraint_sql(self, expression: exp.UniqueColumnConstraint) -> str:
1307 this = self.sql(expression, "this")
1308 this = f" {this}" if this else ""
1309 index_type = expression.args.get("index_type")
1310 index_type = f" USING {index_type}" if index_type else ""
1311 on_conflict = self.sql(expression, "on_conflict")
1312 on_conflict = f" {on_conflict}" if on_conflict else ""
1313 nulls_sql = " NULLS NOT DISTINCT" if expression.args.get("nulls") else ""
1314 options = self.expressions(expression, key="options", flat=True, sep=" ")
1315 options = f" {options}" if options else ""
1316 return f"UNIQUE{nulls_sql}{this}{index_type}{on_conflict}{options}"
1317
1318 def inoutcolumnconstraint_sql(self, expression: exp.InOutColumnConstraint) -> str:
1319 input_ = expression.args.get("input_")
1320 output = expression.args.get("output")
1321 variadic = expression.args.get("variadic")
1322
1323 # VARIADIC is mutually exclusive with IN/OUT/INOUT
1324 if variadic:
1325 return "VARIADIC"
1326
1327 if input_ and output:
1328 return f"IN{self.INOUT_SEPARATOR}OUT"
1329 if input_:
1330 return "IN"
1331 if output:
1332 return "OUT"
1333
1334 return ""
1335
1336 def createable_sql(self, expression: exp.Create, locations: defaultdict) -> str:
1337 return self.sql(expression, "this")
1338
1339 def create_sql(self, expression: exp.Create) -> str:
1340 kind = self.sql(expression, "kind")
1341 kind = self.dialect.INVERSE_CREATABLE_KIND_MAPPING.get(kind) or kind
1342
1343 properties = expression.args.get("properties")
1344
1345 if (
1346 kind == "TRIGGER"
1347 and properties
1348 and properties.expressions
1349 and isinstance(properties.expressions[0], exp.TriggerProperties)
1350 and properties.expressions[0].args.get("constraint")
1351 ):
1352 kind = f"CONSTRAINT {kind}"
1353
1354 properties_locs = self.locate_properties(properties) if properties else defaultdict()
1355
1356 this = self.createable_sql(expression, properties_locs)
1357
1358 properties_sql = ""
1359 if properties_locs.get(exp.Properties.Location.POST_SCHEMA) or properties_locs.get(
1360 exp.Properties.Location.POST_WITH
1361 ):
1362 props_ast = exp.Properties(
1363 expressions=[
1364 *properties_locs[exp.Properties.Location.POST_SCHEMA],
1365 *properties_locs[exp.Properties.Location.POST_WITH],
1366 ]
1367 )
1368 props_ast.parent = expression
1369 properties_sql = self.sql(props_ast)
1370
1371 if properties_locs.get(exp.Properties.Location.POST_SCHEMA):
1372 properties_sql = self.sep() + properties_sql
1373 elif not self.pretty:
1374 # Standalone POST_WITH properties need a leading whitespace in non-pretty mode
1375 properties_sql = f" {properties_sql}"
1376
1377 begin = " BEGIN" if expression.args.get("begin") else ""
1378
1379 expression_sql = self.sql(expression, "expression")
1380 if expression_sql:
1381 expression_sql = f"{begin}{self.sep()}{expression_sql}"
1382
1383 if not isinstance(expression.expression, exp.MacroOverloads) and (
1384 self.CREATE_FUNCTION_RETURN_AS or not isinstance(expression.expression, exp.Return)
1385 ):
1386 postalias_props_sql = ""
1387 if properties_locs.get(exp.Properties.Location.POST_ALIAS):
1388 postalias_props_sql = self.properties(
1389 exp.Properties(
1390 expressions=properties_locs[exp.Properties.Location.POST_ALIAS]
1391 ),
1392 wrapped=False,
1393 )
1394 postalias_props_sql = f" {postalias_props_sql}" if postalias_props_sql else ""
1395 expression_sql = f" AS{postalias_props_sql}{expression_sql}"
1396
1397 postindex_props_sql = ""
1398 if properties_locs.get(exp.Properties.Location.POST_INDEX):
1399 postindex_props_sql = self.properties(
1400 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_INDEX]),
1401 wrapped=False,
1402 prefix=" ",
1403 )
1404
1405 indexes = self.expressions(expression, key="indexes", indent=False, sep=" ")
1406 indexes = f" {indexes}" if indexes else ""
1407 index_sql = indexes + postindex_props_sql
1408
1409 replace = " OR REPLACE" if expression.args.get("replace") else ""
1410 refresh = " OR REFRESH" if expression.args.get("refresh") else ""
1411 unique = " UNIQUE" if expression.args.get("unique") else ""
1412
1413 clustered = expression.args.get("clustered")
1414 if clustered is None:
1415 clustered_sql = ""
1416 elif clustered:
1417 clustered_sql = " CLUSTERED COLUMNSTORE"
1418 else:
1419 clustered_sql = " NONCLUSTERED COLUMNSTORE"
1420
1421 postcreate_props_sql = ""
1422 if properties_locs.get(exp.Properties.Location.POST_CREATE):
1423 postcreate_props_sql = self.properties(
1424 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_CREATE]),
1425 sep=" ",
1426 prefix=" ",
1427 wrapped=False,
1428 )
1429
1430 modifiers = "".join((clustered_sql, replace, refresh, unique, postcreate_props_sql))
1431
1432 postexpression_props_sql = ""
1433 if properties_locs.get(exp.Properties.Location.POST_EXPRESSION):
1434 postexpression_props_sql = self.properties(
1435 exp.Properties(
1436 expressions=properties_locs[exp.Properties.Location.POST_EXPRESSION]
1437 ),
1438 sep=" ",
1439 prefix=" ",
1440 wrapped=False,
1441 )
1442
1443 concurrently = " CONCURRENTLY" if expression.args.get("concurrently") else ""
1444 exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else ""
1445 no_schema_binding = (
1446 " WITH NO SCHEMA BINDING" if expression.args.get("no_schema_binding") else ""
1447 )
1448
1449 clone = self.sql(expression, "clone")
1450 clone = f" {clone}" if clone else ""
1451
1452 if kind in self.EXPRESSION_PRECEDES_PROPERTIES_CREATABLES:
1453 properties_expression = f"{expression_sql}{properties_sql}"
1454 else:
1455 properties_expression = f"{properties_sql}{expression_sql}"
1456
1457 expression_sql = f"CREATE{modifiers} {kind}{concurrently}{exists_sql} {this}{properties_expression}{postexpression_props_sql}{index_sql}{no_schema_binding}{clone}"
1458 return self.prepend_ctes(expression, expression_sql)
1459
1460 def sequenceproperties_sql(self, expression: exp.SequenceProperties) -> str:
1461 start = self.sql(expression, "start")
1462 start = f"START WITH {start}" if start else ""
1463 increment = self.sql(expression, "increment")
1464 increment = f" INCREMENT BY {increment}" if increment else ""
1465 minvalue = self.sql(expression, "minvalue")
1466 minvalue = f" MINVALUE {minvalue}" if minvalue else ""
1467 maxvalue = self.sql(expression, "maxvalue")
1468 maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else ""
1469 owned = self.sql(expression, "owned")
1470 owned = f" OWNED BY {owned}" if owned else ""
1471
1472 cache = expression.args.get("cache")
1473 if cache is None:
1474 cache_str = ""
1475 elif cache is True:
1476 cache_str = " CACHE"
1477 else:
1478 cache_str = f" CACHE {cache}"
1479
1480 options = self.expressions(expression, key="options", flat=True, sep=" ")
1481 options = f" {options}" if options else ""
1482
1483 return f"{start}{increment}{minvalue}{maxvalue}{cache_str}{options}{owned}".lstrip()
1484
1485 def triggerproperties_sql(self, expression: exp.TriggerProperties) -> str:
1486 timing = expression.args.get("timing", "")
1487 events = " OR ".join(self.sql(event) for event in expression.args.get("events") or [])
1488 timing_events = f"{timing} {events}".strip() if timing or events else ""
1489
1490 parts = [timing_events, "ON", self.sql(expression, "table")]
1491
1492 if referenced_table := expression.args.get("referenced_table"):
1493 parts.extend(["FROM", self.sql(referenced_table)])
1494
1495 if deferrable := expression.args.get("deferrable"):
1496 parts.append(deferrable)
1497
1498 if initially := expression.args.get("initially"):
1499 parts.append(f"INITIALLY {initially}")
1500
1501 if referencing := expression.args.get("referencing"):
1502 parts.append(self.sql(referencing))
1503
1504 if for_each := expression.args.get("for_each"):
1505 parts.append(f"FOR EACH {for_each}")
1506
1507 if when := expression.args.get("when"):
1508 parts.append(f"WHEN ({self.sql(when)})")
1509
1510 parts.append(self.sql(expression, "execute"))
1511
1512 return self.sep().join(parts)
1513
1514 def triggerreferencing_sql(self, expression: exp.TriggerReferencing) -> str:
1515 parts = []
1516
1517 if old_alias := expression.args.get("old"):
1518 parts.append(f"OLD TABLE AS {self.sql(old_alias)}")
1519
1520 if new_alias := expression.args.get("new"):
1521 parts.append(f"NEW TABLE AS {self.sql(new_alias)}")
1522
1523 return f"REFERENCING {' '.join(parts)}"
1524
1525 def triggerevent_sql(self, expression: exp.TriggerEvent) -> str:
1526 columns = expression.args.get("columns")
1527 if columns:
1528 return f"{expression.this} OF {self.expressions(expression, key='columns', flat=True)}"
1529
1530 return self.sql(expression, "this")
1531
1532 def clone_sql(self, expression: exp.Clone) -> str:
1533 this = self.sql(expression, "this")
1534 shallow = "SHALLOW " if expression.args.get("shallow") else ""
1535 keyword = "COPY" if expression.args.get("copy") and self.SUPPORTS_TABLE_COPY else "CLONE"
1536 return f"{shallow}{keyword} {this}"
1537
1538 def describe_sql(self, expression: exp.Describe) -> str:
1539 style = expression.args.get("style")
1540 style = f" {style}" if style else ""
1541 partition = self.sql(expression, "partition")
1542 partition = f" {partition}" if partition else ""
1543 format = self.sql(expression, "format")
1544 format = f" {format}" if format else ""
1545 as_json = " AS JSON" if expression.args.get("as_json") else ""
1546
1547 return f"DESCRIBE{style}{format} {self.sql(expression, 'this')}{partition}{as_json}"
1548
1549 def heredoc_sql(self, expression: exp.Heredoc) -> str:
1550 tag = self.sql(expression, "tag")
1551 return f"${tag}${self.sql(expression, 'this')}${tag}$"
1552
1553 def prepend_ctes(self, expression: exp.Expr, sql: str) -> str:
1554 with_ = self.sql(expression, "with_")
1555 if with_:
1556 sql = f"{with_}{self.sep()}{sql}"
1557 return sql
1558
1559 def with_sql(self, expression: exp.With) -> str:
1560 udfs = self.expressions(expression, key="udfs", flat=True)
1561 udfs = f"WITH {udfs}" if udfs else ""
1562
1563 sql = self.expressions(expression, flat=True)
1564
1565 recursive = (
1566 "RECURSIVE "
1567 if self.CTE_RECURSIVE_KEYWORD_REQUIRED and expression.args.get("recursive")
1568 else ""
1569 )
1570 search = self.sql(expression, "search")
1571 search = f" {search}" if search else ""
1572
1573 sql = f"WITH {recursive}{sql}{search}" if sql else ""
1574 return f"{udfs} {sql}" if udfs and sql else f"{udfs}{sql}"
1575
1576 def cte_sql(self, expression: exp.CTE) -> str:
1577 alias = expression.args.get("alias")
1578 if alias:
1579 alias.add_comments(expression.pop_comments())
1580
1581 alias_sql = self.sql(expression, "alias")
1582
1583 materialized = expression.args.get("materialized")
1584 if materialized is False:
1585 materialized = "NOT MATERIALIZED "
1586 elif materialized:
1587 materialized = "MATERIALIZED "
1588
1589 key_expressions = self.expressions(expression, key="key_expressions", flat=True)
1590 key_expressions = f" USING KEY ({key_expressions})" if key_expressions else ""
1591
1592 return f"{alias_sql}{key_expressions} AS {materialized or ''}{self.wrap(expression)}"
1593
1594 def tablealias_sql(self, expression: exp.TableAlias) -> str:
1595 alias = self.sql(expression, "this")
1596 columns = self.expressions(expression, key="columns", flat=True)
1597 columns = f"({columns})" if columns else ""
1598
1599 if (
1600 columns
1601 and not self.SUPPORTS_TABLE_ALIAS_COLUMNS
1602 and not (self.SUPPORTS_NAMED_CTE_COLUMNS and isinstance(expression.parent, exp.CTE))
1603 ):
1604 columns = ""
1605 self.unsupported("Named columns are not supported in table alias.")
1606
1607 if not alias and not self.dialect.UNNEST_COLUMN_ONLY:
1608 alias = self._next_name()
1609
1610 return f"{alias}{columns}"
1611
1612 def bitstring_sql(self, expression: exp.BitString) -> str:
1613 this = self.sql(expression, "this")
1614 if self.dialect.BIT_START:
1615 return f"{self.dialect.BIT_START}{this}{self.dialect.BIT_END}"
1616 return f"{int(this, 2)}"
1617
1618 def hexstring_sql(
1619 self, expression: exp.HexString, binary_function_repr: str | None = None
1620 ) -> str:
1621 this = self.sql(expression, "this")
1622 is_integer_type = expression.args.get("is_integer")
1623
1624 if (is_integer_type and not self.dialect.HEX_STRING_IS_INTEGER_TYPE) or (
1625 not self.dialect.HEX_START and not binary_function_repr
1626 ):
1627 # Integer representation will be returned if:
1628 # - The read dialect treats the hex value as integer literal but not the write
1629 # - The transpilation is not supported (write dialect hasn't set HEX_START or the param flag)
1630 return f"{int(this, 16)}"
1631
1632 if not is_integer_type:
1633 # Read dialect treats the hex value as BINARY/BLOB
1634 if binary_function_repr:
1635 # The write dialect supports the transpilation to its equivalent BINARY/BLOB
1636 return self.func(binary_function_repr, exp.Literal.string(this))
1637 if self.dialect.HEX_STRING_IS_INTEGER_TYPE:
1638 # The write dialect does not support the transpilation, it'll treat the hex value as INTEGER
1639 self.unsupported("Unsupported transpilation from BINARY/BLOB hex string")
1640
1641 return f"{self.dialect.HEX_START}{this}{self.dialect.HEX_END}"
1642
1643 def bytestring_sql(self, expression: exp.ByteString) -> str:
1644 this = self.sql(expression, "this")
1645 if self.dialect.BYTE_START:
1646 escaped_byte_string = self.escape_str(
1647 this,
1648 escape_backslash=False,
1649 delimiter=self.dialect.BYTE_END,
1650 escaped_delimiter=self._escaped_byte_quote_end,
1651 is_byte_string=True,
1652 )
1653 is_bytes = expression.args.get("is_bytes", False)
1654 delimited_byte_string = (
1655 f"{self.dialect.BYTE_START}{escaped_byte_string}{self.dialect.BYTE_END}"
1656 )
1657 if is_bytes and not self.dialect.BYTE_STRING_IS_BYTES_TYPE:
1658 return self.sql(
1659 exp.cast(delimited_byte_string, exp.DType.BINARY, dialect=self.dialect)
1660 )
1661 if not is_bytes and self.dialect.BYTE_STRING_IS_BYTES_TYPE:
1662 return self.sql(
1663 exp.cast(delimited_byte_string, exp.DType.VARCHAR, dialect=self.dialect)
1664 )
1665
1666 return delimited_byte_string
1667
1668 if "\\" in self.dialect.tokenizer_class.STRING_ESCAPES:
1669 return self.sql(exp.Literal.string(this))
1670
1671 self.unsupported(f"Byte strings are not supported for {self.dialect.__class__.__name__}")
1672 return ""
1673
1674 def unicodestring_sql(self, expression: exp.UnicodeString) -> str:
1675 this = self.sql(expression, "this")
1676 escape = expression.args.get("escape")
1677 unicode_start = self.dialect.UNICODE_START
1678
1679 if unicode_start:
1680 escape_substitute = r"\\\1"
1681 left_quote, right_quote = unicode_start, self.dialect.UNICODE_END or ""
1682 else:
1683 escape_substitute = r"\\u\1"
1684 left_quote, right_quote = self.dialect.QUOTE_START, self.dialect.QUOTE_END
1685
1686 if escape:
1687 escape_pattern = re.compile(rf"{escape.name}(\d+)")
1688 escape_sql = f" UESCAPE {self.sql(escape)}" if self.SUPPORTS_UESCAPE else ""
1689 else:
1690 escape_pattern = ESCAPED_UNICODE_RE
1691 escape_sql = ""
1692
1693 if not unicode_start or (escape and not self.SUPPORTS_UESCAPE):
1694 this = escape_pattern.sub(self.UNICODE_SUBSTITUTE or escape_substitute, this)
1695
1696 if unicode_start:
1697 # A Unicode literal only escapes its delimiter by doubling it; the escape character
1698 # introduces a code point, so the dialect's ordinary string escapes don't apply here
1699 this = self._replace_line_breaks(this).replace(right_quote, right_quote * 2)
1700 else:
1701 this = self.escape_str(this, escape_backslash=False)
1702
1703 return f"{left_quote}{this}{right_quote}{escape_sql}"
1704
1705 def rawstring_sql(self, expression: exp.RawString) -> str:
1706 string = expression.this
1707 if "\\" in self.dialect.tokenizer_class.STRING_ESCAPES:
1708 string = string.replace("\\", "\\\\")
1709
1710 string = self.escape_str(string, escape_backslash=False)
1711 return f"{self.dialect.QUOTE_START}{string}{self.dialect.QUOTE_END}"
1712
1713 def datatypeparam_sql(self, expression: exp.DataTypeParam) -> str:
1714 this = self.sql(expression, "this")
1715 specifier = self.sql(expression, "expression")
1716 specifier = f" {specifier}" if specifier and self.DATA_TYPE_SPECIFIERS_ALLOWED else ""
1717 return f"{this}{specifier}"
1718
1719 def datatype_param_bound_limiter(
1720 self,
1721 expression: exp.DataType,
1722 type_value: exp.DType,
1723 defaults: tuple[int, ...],
1724 bounds: tuple[int | None, ...],
1725 ) -> exp.DataType:
1726 params = expression.expressions
1727
1728 if not params:
1729 if defaults:
1730 expression.set(
1731 "expressions",
1732 [exp.DataTypeParam(this=exp.Literal.number(d)) for d in defaults],
1733 )
1734 return expression
1735
1736 if not bounds:
1737 return expression
1738
1739 for i, param in enumerate(params):
1740 bound = bounds[i] if i < len(bounds) else None
1741 if bound is None:
1742 continue
1743
1744 param_value = param.this if isinstance(param, exp.DataTypeParam) else param
1745 value = (
1746 param_value.to_py()
1747 if isinstance(param_value, exp.Literal) and param_value.is_number
1748 else None
1749 )
1750 if isinstance(value, (int, Decimal)) and value > bound:
1751 self.unsupported(
1752 f"{type_value.value} parameter {param_value.name} exceeds "
1753 f"{self.dialect.__class__.__name__}'s maximum of {bound}; capping"
1754 )
1755 params[i] = exp.DataTypeParam(this=exp.Literal.number(bound))
1756
1757 return expression
1758
1759 def datatype_sql(self, expression: exp.DataType) -> str:
1760 nested = ""
1761 values = ""
1762
1763 expr_nested = expression.args.get("nested")
1764 type_value = expression.this
1765
1766 if (
1767 not expr_nested
1768 and isinstance(type_value, exp.DType)
1769 and (settings := self.TYPE_PARAM_SETTINGS.get(type_value))
1770 ):
1771 expression = self.datatype_param_bound_limiter(expression, type_value, *settings)
1772
1773 interior = (
1774 self.expressions(
1775 expression, dynamic=True, new_line=True, skip_first=True, skip_last=True
1776 )
1777 if expr_nested and self.pretty
1778 else self.expressions(expression, flat=True)
1779 )
1780
1781 if type_value in self.UNSUPPORTED_TYPES:
1782 self.unsupported(
1783 f"Data type {type_value.value} is not supported when targeting {self.dialect.__class__.__name__}"
1784 )
1785
1786 type_sql: t.Any = ""
1787 if type_value == exp.DType.USERDEFINED and expression.args.get("kind"):
1788 type_sql = self.sql(expression, "kind")
1789 elif type_value == exp.DType.CHARACTER_SET:
1790 return f"CHAR CHARACTER SET {self.sql(expression, 'kind')}"
1791 else:
1792 type_sql = (
1793 self.TYPE_MAPPING.get(type_value, type_value.value)
1794 if isinstance(type_value, exp.DType)
1795 else type_value
1796 )
1797
1798 if interior:
1799 if expr_nested:
1800 nested = f"{self.STRUCT_DELIMITER[0]}{interior}{self.STRUCT_DELIMITER[1]}"
1801 if expression.args.get("values") is not None:
1802 delimiters = ("[", "]") if type_value == exp.DType.ARRAY else ("(", ")")
1803 values = self.expressions(expression, key="values", flat=True)
1804 values = f"{delimiters[0]}{values}{delimiters[1]}"
1805 elif type_value == exp.DType.INTERVAL:
1806 nested = f" {interior}"
1807 else:
1808 nested = f"({interior})"
1809
1810 type_sql = f"{type_sql}{nested}{values}"
1811 if self.TZ_TO_WITH_TIME_ZONE and type_value in (
1812 exp.DType.TIMETZ,
1813 exp.DType.TIMESTAMPTZ,
1814 ):
1815 type_sql = f"{type_sql} WITH TIME ZONE"
1816
1817 collate = self.sql(expression, "collate")
1818 if collate:
1819 type_sql = f"{type_sql} COLLATE {collate}"
1820
1821 return type_sql
1822
1823 def directory_sql(self, expression: exp.Directory) -> str:
1824 local = "LOCAL " if expression.args.get("local") else ""
1825 row_format = self.sql(expression, "row_format")
1826 row_format = f" {row_format}" if row_format else ""
1827 return f"{local}DIRECTORY {self.sql(expression, 'this')}{row_format}"
1828
1829 def delete_sql(self, expression: exp.Delete) -> str:
1830 hint = self.sql(expression, "hint")
1831 this = self.sql(expression, "this")
1832 this = f" FROM {this}" if this else ""
1833 using = self.expressions(expression, key="using")
1834 using = f" USING {using}" if using else ""
1835 cluster = self.sql(expression, "cluster")
1836 cluster = f" {cluster}" if cluster else ""
1837 where = self.sql(expression, "where")
1838 returning = self.sql(expression, "returning")
1839 order = self.sql(expression, "order")
1840 limit = self.sql(expression, "limit")
1841 tables = self.expressions(expression, key="tables")
1842 tables = f" {tables}" if tables else ""
1843 if self.RETURNING_END:
1844 expression_sql = f"{this}{using}{cluster}{where}{returning}{order}{limit}"
1845 else:
1846 expression_sql = f"{returning}{this}{using}{cluster}{where}{order}{limit}"
1847 return self.prepend_ctes(expression, f"DELETE{hint}{tables}{expression_sql}")
1848
1849 def drop_sql(self, expression: exp.Drop) -> str:
1850 tables = self.expressions(expression, key="tables", flat=True)
1851 expressions = self.expressions(expression, flat=True)
1852 expressions = f" ({expressions})" if expressions else ""
1853 kind = expression.args["kind"]
1854 kind = self.dialect.INVERSE_CREATABLE_KIND_MAPPING.get(kind) or kind
1855 iceberg = (
1856 " ICEBERG"
1857 if expression.args.get("iceberg") and self.SUPPORTS_DROP_ALTER_ICEBERG_PROPERTY
1858 else ""
1859 )
1860 exists_sql = " IF EXISTS " if expression.args.get("exists") else " "
1861 concurrently_sql = " CONCURRENTLY" if expression.args.get("concurrently") else ""
1862 on_cluster = self.sql(expression, "cluster")
1863 on_cluster = f" {on_cluster}" if on_cluster else ""
1864 temporary = " TEMPORARY" if expression.args.get("temporary") else ""
1865 materialized = " MATERIALIZED" if expression.args.get("materialized") else ""
1866 cascade = " CASCADE" if expression.args.get("cascade") else ""
1867 restrict = " RESTRICT" if expression.args.get("restrict") else ""
1868 constraints = " CONSTRAINTS" if expression.args.get("constraints") else ""
1869 purge = " PURGE" if expression.args.get("purge") else ""
1870 sync = " SYNC" if expression.args.get("sync") else ""
1871 force = " FORCE" if expression.args.get("force") else ""
1872 return f"DROP{temporary}{materialized}{iceberg} {kind}{concurrently_sql}{exists_sql}{tables}{on_cluster}{expressions}{cascade}{restrict}{constraints}{purge}{sync}{force}"
1873
1874 def set_operation(self, expression: exp.SetOperation) -> str:
1875 op_type = type(expression)
1876 op_name = op_type.key.upper()
1877
1878 distinct = expression.args.get("distinct")
1879 if (
1880 distinct is False
1881 and op_type in (exp.Except, exp.Intersect)
1882 and not self.EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE
1883 ):
1884 self.unsupported(f"{op_name} ALL is not supported")
1885
1886 default_distinct = self.dialect.SET_OP_DISTINCT_BY_DEFAULT[op_type]
1887
1888 if distinct is None:
1889 distinct = default_distinct
1890 if distinct is None:
1891 self.unsupported(f"{op_name} requires DISTINCT or ALL to be specified")
1892
1893 if distinct is default_distinct:
1894 distinct_or_all = ""
1895 else:
1896 distinct_or_all = " DISTINCT" if distinct else " ALL"
1897
1898 side_kind = " ".join(filter(None, [expression.side, expression.kind]))
1899 side_kind = f"{side_kind} " if side_kind else ""
1900
1901 by_name = " BY NAME" if expression.args.get("by_name") else ""
1902 on = self.expressions(expression, key="on", flat=True)
1903 on = f" ON ({on})" if on else ""
1904
1905 return f"{side_kind}{op_name}{distinct_or_all}{by_name}{on}"
1906
1907 def set_operations(self, expression: exp.SetOperation) -> str:
1908 if not self.SET_OP_MODIFIERS:
1909 limit = expression.args.get("limit")
1910 order = expression.args.get("order")
1911 offset = expression.args.get("offset")
1912
1913 if limit or order or offset:
1914 select = self._move_ctes_to_top_level(
1915 exp.subquery(expression, "_l_0", copy=False).select("*", copy=False)
1916 )
1917
1918 for arg in ("limit", "order", "offset"):
1919 if value := expression.args.get(arg):
1920 select.set(arg, value.pop())
1921 return self.sql(select)
1922
1923 sqls: list[str] = []
1924 stack: list[str | exp.Expr] = [expression]
1925
1926 while stack:
1927 node = stack.pop()
1928
1929 if isinstance(node, exp.SetOperation):
1930 stack.append(node.expression)
1931 stack.append(
1932 self.maybe_comment(
1933 self.set_operation(node), comments=node.comments, separated=True
1934 )
1935 )
1936 stack.append(node.this)
1937 else:
1938 if (
1939 not self.SET_OP_LIMITS
1940 and isinstance(node, exp.Select)
1941 and node.args.get("limit")
1942 ):
1943 node = node.subquery(copy=False)
1944 if not self.SET_OP_PARENTHESIZED_OPERANDS:
1945 node = exp.select("*").from_(node, copy=False)
1946 sqls.append(self.sql(node))
1947
1948 this = self.sep().join(sqls)
1949 this = self.query_modifiers(expression, this)
1950 return self.prepend_ctes(expression, this)
1951
1952 def fetch_sql(self, expression: exp.Fetch) -> str:
1953 direction = expression.args.get("direction")
1954 direction = f" {direction}" if direction else ""
1955 count = self.sql(expression, "count")
1956 count = f" {count}" if count else ""
1957 limit_options = self.sql(expression, "limit_options")
1958 limit_options = f"{limit_options}" if limit_options else " ROWS ONLY"
1959 return f"{self.seg('FETCH')}{direction}{count}{limit_options}"
1960
1961 def limitoptions_sql(self, expression: exp.LimitOptions) -> str:
1962 percent = " PERCENT" if expression.args.get("percent") else ""
1963 rows = " ROWS" if expression.args.get("rows") else ""
1964 with_ties = " WITH TIES" if expression.args.get("with_ties") else ""
1965 if not with_ties and rows:
1966 with_ties = " ONLY"
1967 return f"{percent}{rows}{with_ties}"
1968
1969 def filter_sql(self, expression: exp.Filter) -> str:
1970 this = self.sql(expression, "this")
1971 where = self.sql(expression, "expression").strip()
1972 return f"{this} FILTER({where})"
1973
1974 def hint_sql(self, expression: exp.Hint) -> str:
1975 if not self.QUERY_HINTS:
1976 self.unsupported("Hints are not supported")
1977 return ""
1978
1979 return f" /*+ {self.expressions(expression, sep=self.QUERY_HINT_SEP).strip()} */"
1980
1981 def indexparameters_sql(self, expression: exp.IndexParameters) -> str:
1982 using = self.sql(expression, "using")
1983 using = f" USING {using}" if using else ""
1984 columns = self.expressions(expression, key="columns", flat=True)
1985 columns = f"({columns})" if columns else ""
1986 partition_by = self.expressions(expression, key="partition_by", flat=True)
1987 partition_by = f" PARTITION BY {partition_by}" if partition_by else ""
1988 where = self.sql(expression, "where")
1989 include = self.expressions(expression, key="include", flat=True)
1990 if include:
1991 include = f" INCLUDE ({include})"
1992 with_storage = self.expressions(expression, key="with_storage", flat=True)
1993 with_storage = f" WITH ({with_storage})" if with_storage else ""
1994 tablespace = self.sql(expression, "tablespace")
1995 tablespace = f" USING INDEX TABLESPACE {tablespace}" if tablespace else ""
1996 on = self.sql(expression, "on")
1997 on = f" ON {on}" if on else ""
1998
1999 return f"{using}{columns}{include}{with_storage}{tablespace}{partition_by}{where}{on}"
2000
2001 def index_sql(self, expression: exp.Index) -> str:
2002 unique = "UNIQUE " if expression.args.get("unique") else ""
2003 primary = "PRIMARY " if expression.args.get("primary") else ""
2004 amp = "AMP " if expression.args.get("amp") else ""
2005 name = self.sql(expression, "this")
2006 name = f"{name} " if name else ""
2007 table = self.sql(expression, "table")
2008 table = f"{self.INDEX_ON} {table}" if table else ""
2009
2010 index = "INDEX " if not table else ""
2011
2012 params = self.sql(expression, "params")
2013 return f"{unique}{primary}{amp}{index}{name}{table}{params}"
2014
2015 def dynamicidentifier_sql(self, expression: exp.DynamicIdentifier) -> str:
2016 this = expression.this
2017 if this and this.is_string:
2018 resolved = maybe_parse(this.name).sql(self.dialect)
2019 if "expressions" in expression.args:
2020 # `IDENTIFIER(...)` invoked as a function, e.g. `IDENTIFIER('my_func')(1, 2)`
2021 # We can't safely emit the call to other dialects since name/arg semantics may differ
2022 self.unsupported(
2023 "Transpiling dynamically-invoked IDENTIFIER() functions is unsupported"
2024 )
2025 return resolved
2026 self.unsupported("IDENTIFIER() with non-literal arguments is not supported")
2027 return self.func("IDENTIFIER", this)
2028
2029 def identifier_sql(self, expression: exp.Identifier) -> str:
2030 text = expression.name
2031 lower = text.lower()
2032 quoted = expression.quoted
2033 text = lower if self.normalize and not quoted else text
2034 text = text.replace(self._identifier_end, self._escaped_identifier_end)
2035 if (
2036 quoted
2037 or self.dialect.can_quote(expression, self.identify)
2038 or lower in self.RESERVED_KEYWORDS
2039 or (not self.dialect.IDENTIFIERS_CAN_START_WITH_DIGIT and text[:1].isdigit())
2040 ):
2041 text = (
2042 f"{self._identifier_start}{self._replace_line_breaks(text)}{self._identifier_end}"
2043 )
2044 return text
2045
2046 def hex_sql(self, expression: exp.Hex) -> str:
2047 text = self.func(self.HEX_FUNC, self.sql(expression, "this"))
2048 if self.dialect.HEX_LOWERCASE:
2049 text = self.func("LOWER", text)
2050
2051 return text
2052
2053 def lowerhex_sql(self, expression: exp.LowerHex) -> str:
2054 text = self.func(self.HEX_FUNC, self.sql(expression, "this"))
2055 if not self.dialect.HEX_LOWERCASE:
2056 text = self.func("LOWER", text)
2057 return text
2058
2059 def inputoutputformat_sql(self, expression: exp.InputOutputFormat) -> str:
2060 input_format = self.sql(expression, "input_format")
2061 input_format = f"INPUTFORMAT {input_format}" if input_format else ""
2062 output_format = self.sql(expression, "output_format")
2063 output_format = f"OUTPUTFORMAT {output_format}" if output_format else ""
2064 return self.sep().join((input_format, output_format))
2065
2066 def national_sql(self, expression: exp.National, prefix: str = "N") -> str:
2067 string = self.sql(exp.Literal.string(expression.name))
2068 return f"{prefix}{string}"
2069
2070 def partition_sql(self, expression: exp.Partition) -> str:
2071 partition_keyword = "SUBPARTITION" if expression.args.get("subpartition") else "PARTITION"
2072 return f"{partition_keyword}({self.expressions(expression, flat=True)})"
2073
2074 def properties_sql(self, expression: exp.Properties) -> str:
2075 root_properties = []
2076 with_properties = []
2077
2078 for p in expression.expressions:
2079 p_loc = self.PROPERTIES_LOCATION[p.__class__]
2080 if p_loc == exp.Properties.Location.POST_WITH:
2081 with_properties.append(p)
2082 elif p_loc == exp.Properties.Location.POST_SCHEMA:
2083 root_properties.append(p)
2084
2085 root_props_ast = exp.Properties(expressions=root_properties)
2086 root_props_ast.parent = expression.parent
2087
2088 with_props_ast = exp.Properties(expressions=with_properties)
2089 with_props_ast.parent = expression.parent
2090
2091 root_props = self.root_properties(root_props_ast)
2092 with_props = self.with_properties(with_props_ast)
2093
2094 if root_props and with_props and not self.pretty:
2095 with_props = " " + with_props
2096
2097 return root_props + with_props
2098
2099 def root_properties(self, properties: exp.Properties) -> str:
2100 if properties.expressions:
2101 return self.expressions(properties, indent=False, sep=" ")
2102 return ""
2103
2104 def properties(
2105 self,
2106 properties: exp.Properties,
2107 prefix: str = "",
2108 sep: str = ", ",
2109 suffix: str = "",
2110 wrapped: bool = True,
2111 ) -> str:
2112 if properties.expressions:
2113 expressions = self.expressions(properties, sep=sep, indent=False)
2114 if expressions:
2115 expressions = self.wrap(expressions) if wrapped else expressions
2116 return f"{prefix}{' ' if prefix.strip() else ''}{expressions}{suffix}"
2117 return ""
2118
2119 def with_properties(self, properties: exp.Properties) -> str:
2120 return self.properties(properties, prefix=self.seg(self.WITH_PROPERTIES_PREFIX, sep=""))
2121
2122 def locate_properties(self, properties: exp.Properties) -> defaultdict:
2123 properties_locs = defaultdict(list)
2124 for p in properties.expressions:
2125 p_loc = self.PROPERTIES_LOCATION[p.__class__]
2126 if p_loc != exp.Properties.Location.UNSUPPORTED:
2127 properties_locs[p_loc].append(p)
2128 else:
2129 self.unsupported(f"Unsupported property {p.key}")
2130
2131 return properties_locs
2132
2133 def property_name(self, expression: exp.Property, string_key: bool = False) -> str:
2134 if isinstance(expression.this, exp.Dot):
2135 return self.sql(expression, "this")
2136 return f"'{expression.name}'" if string_key else expression.name
2137
2138 def property_sql(self, expression: exp.Property) -> str:
2139 property_cls = expression.__class__
2140 if property_cls == exp.Property:
2141 return f"{self.property_name(expression)}={self.sql(expression, 'value')}"
2142
2143 property_name = exp.Properties.PROPERTY_TO_NAME.get(property_cls)
2144 if not property_name:
2145 self.unsupported(f"Unsupported property {expression.key}")
2146
2147 return f"{property_name}={self.sql(expression, 'this')}"
2148
2149 def uuidproperty_sql(self, expression: exp.UuidProperty) -> str:
2150 return f"UUID {self.sql(expression, 'this')}"
2151
2152 def likeproperty_sql(self, expression: exp.LikeProperty) -> str:
2153 if self.SUPPORTS_CREATE_TABLE_LIKE:
2154 options = " ".join(f"{e.name} {self.sql(e, 'value')}" for e in expression.expressions)
2155 options = f" {options}" if options else ""
2156
2157 like = f"LIKE {self.sql(expression, 'this')}{options}"
2158 if self.LIKE_PROPERTY_INSIDE_SCHEMA and not isinstance(expression.parent, exp.Schema):
2159 like = f"({like})"
2160
2161 return like
2162
2163 if expression.expressions:
2164 self.unsupported("Transpilation of LIKE property options is unsupported")
2165
2166 select = exp.select("*").from_(expression.this).limit(0)
2167 return f"AS {self.sql(select)}"
2168
2169 def fallbackproperty_sql(self, expression: exp.FallbackProperty) -> str:
2170 no = "NO " if expression.args.get("no") else ""
2171 protection = " PROTECTION" if expression.args.get("protection") else ""
2172 return f"{no}FALLBACK{protection}"
2173
2174 def journalproperty_sql(self, expression: exp.JournalProperty) -> str:
2175 no = "NO " if expression.args.get("no") else ""
2176 local = expression.args.get("local")
2177 local = f"{local} " if local else ""
2178 dual = "DUAL " if expression.args.get("dual") else ""
2179 before = "BEFORE " if expression.args.get("before") else ""
2180 after = "AFTER " if expression.args.get("after") else ""
2181 return f"{no}{local}{dual}{before}{after}JOURNAL"
2182
2183 def freespaceproperty_sql(self, expression: exp.FreespaceProperty) -> str:
2184 freespace = self.sql(expression, "this")
2185 percent = " PERCENT" if expression.args.get("percent") else ""
2186 return f"FREESPACE={freespace}{percent}"
2187
2188 def checksumproperty_sql(self, expression: exp.ChecksumProperty) -> str:
2189 if expression.args.get("default"):
2190 property = "DEFAULT"
2191 elif expression.args.get("on"):
2192 property = "ON"
2193 else:
2194 property = "OFF"
2195 return f"CHECKSUM={property}"
2196
2197 def mergeblockratioproperty_sql(self, expression: exp.MergeBlockRatioProperty) -> str:
2198 if expression.args.get("no"):
2199 return "NO MERGEBLOCKRATIO"
2200 if expression.args.get("default"):
2201 return "DEFAULT MERGEBLOCKRATIO"
2202
2203 percent = " PERCENT" if expression.args.get("percent") else ""
2204 return f"MERGEBLOCKRATIO={self.sql(expression, 'this')}{percent}"
2205
2206 def moduleproperty_sql(self, expression: exp.ModuleProperty) -> str:
2207 expressions = self.expressions(expression, flat=True)
2208 expressions = f"({expressions})" if expressions else ""
2209 return f"USING {self.sql(expression, 'this')}{expressions}"
2210
2211 def datablocksizeproperty_sql(self, expression: exp.DataBlocksizeProperty) -> str:
2212 default = expression.args.get("default")
2213 minimum = expression.args.get("minimum")
2214 maximum = expression.args.get("maximum")
2215 if default or minimum or maximum:
2216 if default:
2217 prop = "DEFAULT"
2218 elif minimum:
2219 prop = "MINIMUM"
2220 else:
2221 prop = "MAXIMUM"
2222 return f"{prop} DATABLOCKSIZE"
2223 units = expression.args.get("units")
2224 units = f" {units}" if units else ""
2225 return f"DATABLOCKSIZE={self.sql(expression, 'size')}{units}"
2226
2227 def blockcompressionproperty_sql(self, expression: exp.BlockCompressionProperty) -> str:
2228 autotemp = expression.args.get("autotemp")
2229 always = expression.args.get("always")
2230 default = expression.args.get("default")
2231 manual = expression.args.get("manual")
2232 never = expression.args.get("never")
2233
2234 if autotemp is not None:
2235 prop = f"AUTOTEMP({self.expressions(autotemp)})"
2236 elif always:
2237 prop = "ALWAYS"
2238 elif default:
2239 prop = "DEFAULT"
2240 elif manual:
2241 prop = "MANUAL"
2242 elif never:
2243 prop = "NEVER"
2244 return f"BLOCKCOMPRESSION={prop}"
2245
2246 def isolatedloadingproperty_sql(self, expression: exp.IsolatedLoadingProperty) -> str:
2247 no = expression.args.get("no")
2248 no = " NO" if no else ""
2249 concurrent = expression.args.get("concurrent")
2250 concurrent = " CONCURRENT" if concurrent else ""
2251 target = self.sql(expression, "target")
2252 target = f" {target}" if target else ""
2253 return f"WITH{no}{concurrent} ISOLATED LOADING{target}"
2254
2255 def partitionboundspec_sql(self, expression: exp.PartitionBoundSpec) -> str:
2256 if isinstance(expression.this, list):
2257 return f"IN ({self.expressions(expression, key='this', flat=True)})"
2258 if expression.this:
2259 modulus = self.sql(expression, "this")
2260 remainder = self.sql(expression, "expression")
2261 return f"WITH (MODULUS {modulus}, REMAINDER {remainder})"
2262
2263 from_expressions = self.expressions(expression, key="from_expressions", flat=True)
2264 to_expressions = self.expressions(expression, key="to_expressions", flat=True)
2265 return f"FROM ({from_expressions}) TO ({to_expressions})"
2266
2267 def partitionedofproperty_sql(self, expression: exp.PartitionedOfProperty) -> str:
2268 this = self.sql(expression, "this")
2269
2270 for_values_or_default = expression.expression
2271 if isinstance(for_values_or_default, exp.PartitionBoundSpec):
2272 for_values_or_default = f" FOR VALUES {self.sql(for_values_or_default)}"
2273 else:
2274 for_values_or_default = " DEFAULT"
2275
2276 return f"PARTITION OF {this}{for_values_or_default}"
2277
2278 def lockingproperty_sql(self, expression: exp.LockingProperty) -> str:
2279 kind = expression.args.get("kind")
2280 this = f" {self.sql(expression, 'this')}" if expression.this else ""
2281 for_or_in = expression.args.get("for_or_in")
2282 for_or_in = f" {for_or_in}" if for_or_in else ""
2283 lock_type = expression.args.get("lock_type")
2284 override = " OVERRIDE" if expression.args.get("override") else ""
2285 return f"LOCKING {kind}{this}{for_or_in} {lock_type}{override}"
2286
2287 def withdataproperty_sql(self, expression: exp.WithDataProperty) -> str:
2288 data_sql = f"WITH {'NO ' if expression.args.get('no') else ''}DATA"
2289 statistics = expression.args.get("statistics")
2290 statistics_sql = ""
2291 if statistics is not None:
2292 statistics_sql = f" AND {'NO ' if not statistics else ''}STATISTICS"
2293 return f"{data_sql}{statistics_sql}"
2294
2295 def withsystemversioningproperty_sql(self, expression: exp.WithSystemVersioningProperty) -> str:
2296 this = self.sql(expression, "this")
2297 this = f"HISTORY_TABLE={this}" if this else ""
2298 data_consistency: str | None = self.sql(expression, "data_consistency")
2299 data_consistency = (
2300 f"DATA_CONSISTENCY_CHECK={data_consistency}" if data_consistency else None
2301 )
2302 retention_period: str | None = self.sql(expression, "retention_period")
2303 retention_period = (
2304 f"HISTORY_RETENTION_PERIOD={retention_period}" if retention_period else None
2305 )
2306
2307 if this:
2308 on_sql = self.func("ON", this, data_consistency, retention_period)
2309 else:
2310 on_sql = "ON" if expression.args.get("on") else "OFF"
2311
2312 sql = f"SYSTEM_VERSIONING={on_sql}"
2313
2314 return f"WITH({sql})" if expression.args.get("with_") else sql
2315
2316 def insert_sql(self, expression: exp.Insert) -> str:
2317 hint = self.sql(expression, "hint")
2318 overwrite = expression.args.get("overwrite")
2319
2320 if isinstance(expression.this, exp.Directory):
2321 this = " OVERWRITE" if overwrite else " INTO"
2322 else:
2323 this = self.INSERT_OVERWRITE if overwrite else " INTO"
2324
2325 stored = self.sql(expression, "stored")
2326 stored = f" {stored}" if stored else ""
2327 alternative = expression.args.get("alternative")
2328 alternative = f" OR {alternative}" if alternative else ""
2329 ignore = " IGNORE" if expression.args.get("ignore") else ""
2330 is_function = expression.args.get("is_function")
2331 if is_function:
2332 this = f"{this} FUNCTION"
2333 this = f"{this} {self.sql(expression, 'this')}"
2334
2335 exists = " IF EXISTS" if expression.args.get("exists") else ""
2336 where = self.sql(expression, "where")
2337 where = f"{self.sep()}REPLACE WHERE {where}" if where else ""
2338 using = self.expressions(expression, key="using", flat=True)
2339 using = f"{self.sep()}REPLACE USING ({using})" if using else ""
2340 expression_sql = f"{self.sep()}{self.sql(expression, 'expression')}"
2341 on_conflict = self.sql(expression, "conflict")
2342 on_conflict = f" {on_conflict}" if on_conflict else ""
2343 by_name = " BY NAME" if expression.args.get("by_name") else ""
2344 default_values = "DEFAULT VALUES" if expression.args.get("default") else ""
2345 returning = self.sql(expression, "returning")
2346
2347 if self.RETURNING_END:
2348 expression_sql = f"{expression_sql}{on_conflict}{default_values}{returning}"
2349 else:
2350 expression_sql = f"{returning}{expression_sql}{on_conflict}"
2351
2352 partition_by = self.sql(expression, "partition")
2353 partition_by = f" {partition_by}" if partition_by else ""
2354 settings = self.sql(expression, "settings")
2355 settings = f" {settings}" if settings else ""
2356
2357 source = self.sql(expression, "source")
2358 source = f"TABLE {source}" if source else ""
2359
2360 sql = f"INSERT{hint}{alternative}{ignore}{this}{stored}{by_name}{exists}{partition_by}{settings}{where}{using}{expression_sql}{source}"
2361 return self.prepend_ctes(expression, sql)
2362
2363 def introducer_sql(self, expression: exp.Introducer) -> str:
2364 return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}"
2365
2366 def kill_sql(self, expression: exp.Kill) -> str:
2367 kind = self.sql(expression, "kind")
2368 kind = f" {kind}" if kind else ""
2369 this = self.sql(expression, "this")
2370 this = f" {this}" if this else ""
2371 return f"KILL{kind}{this}"
2372
2373 def pseudotype_sql(self, expression: exp.PseudoType) -> str:
2374 return expression.name
2375
2376 def objectidentifier_sql(self, expression: exp.ObjectIdentifier) -> str:
2377 return expression.name
2378
2379 def onconflict_sql(self, expression: exp.OnConflict) -> str:
2380 conflict = "ON DUPLICATE KEY" if expression.args.get("duplicate") else "ON CONFLICT"
2381
2382 constraint = self.sql(expression, "constraint")
2383 constraint = f" ON CONSTRAINT {constraint}" if constraint else ""
2384
2385 conflict_keys = self.expressions(expression, key="conflict_keys", flat=True)
2386 if conflict_keys:
2387 conflict_keys = f"({conflict_keys})"
2388
2389 index_predicate = self.sql(expression, "index_predicate")
2390 conflict_keys = f"{conflict_keys}{index_predicate} "
2391
2392 action = self.sql(expression, "action")
2393
2394 expressions = self.expressions(expression, flat=True)
2395 if expressions:
2396 set_keyword = "SET " if self.DUPLICATE_KEY_UPDATE_WITH_SET else ""
2397 expressions = f" {set_keyword}{expressions}"
2398
2399 where = self.sql(expression, "where")
2400 return f"{conflict}{constraint}{conflict_keys}{action}{expressions}{where}"
2401
2402 def returning_sql(self, expression: exp.Returning) -> str:
2403 return f"{self.seg('RETURNING')} {self.expressions(expression, flat=True)}"
2404
2405 def rowformatdelimitedproperty_sql(self, expression: exp.RowFormatDelimitedProperty) -> str:
2406 fields = self.sql(expression, "fields")
2407 fields = f" FIELDS TERMINATED BY {fields}" if fields else ""
2408 escaped = self.sql(expression, "escaped")
2409 escaped = f" ESCAPED BY {escaped}" if escaped else ""
2410 items = self.sql(expression, "collection_items")
2411 items = f" COLLECTION ITEMS TERMINATED BY {items}" if items else ""
2412 keys = self.sql(expression, "map_keys")
2413 keys = f" MAP KEYS TERMINATED BY {keys}" if keys else ""
2414 lines = self.sql(expression, "lines")
2415 lines = f" LINES TERMINATED BY {lines}" if lines else ""
2416 null = self.sql(expression, "null")
2417 null = f" NULL DEFINED AS {null}" if null else ""
2418 return f"ROW FORMAT DELIMITED{fields}{escaped}{items}{keys}{lines}{null}"
2419
2420 def withtablehint_sql(self, expression: exp.WithTableHint) -> str:
2421 return f"WITH ({self.expressions(expression, flat=True)})"
2422
2423 def indextablehint_sql(self, expression: exp.IndexTableHint) -> str:
2424 this = f"{self.sql(expression, 'this')} INDEX"
2425 target = self.sql(expression, "target")
2426 target = f" FOR {target}" if target else ""
2427 return f"{this}{target} ({self.expressions(expression, flat=True)})"
2428
2429 def historicaldata_sql(self, expression: exp.HistoricalData) -> str:
2430 this = self.sql(expression, "this")
2431 kind = self.sql(expression, "kind")
2432 expr = self.sql(expression, "expression")
2433 return f"{this} ({kind} => {expr})"
2434
2435 def table_parts(self, expression: exp.Table) -> str:
2436 return ".".join(
2437 self.sql(part)
2438 for part in (
2439 expression.args.get("catalog"),
2440 expression.args.get("db"),
2441 expression.args.get("this"),
2442 )
2443 if part is not None
2444 )
2445
2446 def table_sql(self, expression: exp.Table, sep: str = " AS ") -> str:
2447 table = self.table_parts(expression)
2448 only = "ONLY " if expression.args.get("only") else ""
2449 partition = self.sql(expression, "partition")
2450 partition = f" {partition}" if partition else ""
2451 version = self.sql(expression, "version")
2452 version = f" {version}" if version else ""
2453 alias = self.sql(expression, "alias")
2454 alias = f"{sep}{alias}" if alias else ""
2455
2456 sample = self.sql(expression, "sample")
2457 post_alias = ""
2458 pre_alias = ""
2459
2460 if self.dialect.ALIAS_POST_TABLESAMPLE:
2461 pre_alias = sample
2462 else:
2463 post_alias = sample
2464
2465 if self.dialect.ALIAS_POST_VERSION:
2466 pre_alias = f"{pre_alias}{version}"
2467 else:
2468 post_alias = f"{post_alias}{version}"
2469
2470 hints = self.expressions(expression, key="hints", sep=" ")
2471 hints = f" {hints}" if hints and self.TABLE_HINTS else ""
2472 pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2473 joins = self.indent(
2474 self.expressions(expression, key="joins", sep="", flat=True), skip_first=True
2475 )
2476 laterals = self.expressions(expression, key="laterals", sep="")
2477
2478 file_format = self.sql(expression, "format")
2479 pattern = self.sql(expression, "pattern")
2480 if file_format:
2481 pattern = f", PATTERN => {pattern}" if pattern else ""
2482 file_format = f" (FILE_FORMAT => {file_format}{pattern})"
2483 elif pattern:
2484 file_format = f" (PATTERN => {pattern})"
2485
2486 ordinality = expression.args.get("ordinality") or ""
2487 if ordinality:
2488 ordinality = f" WITH ORDINALITY{alias}"
2489 alias = ""
2490
2491 when = self.sql(expression, "when")
2492 if when:
2493 if self.HISTORICAL_DATA_POST_ALIAS:
2494 alias = f"{alias} {when}"
2495 else:
2496 table = f"{table} {when}"
2497
2498 changes = self.sql(expression, "changes")
2499 changes = f" {changes}" if changes else ""
2500
2501 rows_from = self.expressions(expression, key="rows_from")
2502 if rows_from:
2503 table = f"ROWS FROM {self.wrap(rows_from)}"
2504
2505 indexed = expression.args.get("indexed")
2506 if indexed is not None:
2507 indexed = f" INDEXED BY {self.sql(indexed)}" if indexed else " NOT INDEXED"
2508 else:
2509 indexed = ""
2510
2511 return f"{only}{table}{changes}{partition}{file_format}{pre_alias}{alias}{indexed}{hints}{pivots}{post_alias}{joins}{laterals}{ordinality}"
2512
2513 def tablefromrows_sql(self, expression: exp.TableFromRows) -> str:
2514 table = self.func("TABLE", expression.this)
2515 alias = self.sql(expression, "alias")
2516 alias = f" AS {alias}" if alias else ""
2517 sample = self.sql(expression, "sample")
2518 pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2519 joins = self.indent(
2520 self.expressions(expression, key="joins", sep="", flat=True), skip_first=True
2521 )
2522 return f"{table}{alias}{pivots}{sample}{joins}"
2523
2524 def tablesample_sql(
2525 self,
2526 expression: exp.TableSample,
2527 tablesample_keyword: str | None = None,
2528 ) -> str:
2529 method = self.sql(expression, "method")
2530 method = f"{method} " if method and self.TABLESAMPLE_WITH_METHOD else ""
2531 numerator = self.sql(expression, "bucket_numerator")
2532 denominator = self.sql(expression, "bucket_denominator")
2533 field = self.sql(expression, "bucket_field")
2534 field = f" ON {field}" if field else ""
2535 bucket = f"BUCKET {numerator} OUT OF {denominator}{field}" if numerator else ""
2536 seed = self.sql(expression, "seed")
2537 seed = f" {self.TABLESAMPLE_SEED_KEYWORD} ({seed})" if seed else ""
2538
2539 size = self.sql(expression, "size")
2540 if size and self.TABLESAMPLE_SIZE_IS_ROWS:
2541 size = f"{size} ROWS"
2542
2543 percent = self.sql(expression, "percent")
2544 if percent and not self.dialect.TABLESAMPLE_SIZE_IS_PERCENT:
2545 percent = f"{percent} PERCENT"
2546
2547 expr = f"{bucket}{percent}{size}"
2548 if self.TABLESAMPLE_REQUIRES_PARENS:
2549 expr = f"({expr})"
2550
2551 return f" {tablesample_keyword or self.TABLESAMPLE_KEYWORDS} {method}{expr}{seed}"
2552
2553 def _pivot_in_value_aliases(self, expression: exp.Pivot) -> list[exp.Expression] | None:
2554 # Returns the rewritten field.expressions list with PivotAlias wrappers injected where
2555 # the stored column name differs from the target dialect's natural output.
2556 columns = expression.args.get("columns")
2557 if not columns or len(expression.fields) != 1:
2558 return None
2559
2560 args = expression.args
2561 parser_cls = self.dialect.parser_class
2562
2563 tgt_identify_pivot_strings = parser_cls.IDENTIFY_PIVOT_STRINGS
2564 tgt_prefixed_pivot_columns = parser_cls.PREFIXED_PIVOT_COLUMNS
2565 tgt_pivot_column_naming = parser_cls.PIVOT_COLUMN_NAMING
2566
2567 src_identify_pivot_strings = args.get("identify_pivot_strings", tgt_identify_pivot_strings)
2568 src_prefixed_pivot_columns = args.get("prefixed_pivot_columns", tgt_prefixed_pivot_columns)
2569 src_pivot_column_naming = args.get("pivot_column_naming", tgt_pivot_column_naming)
2570
2571 if (
2572 src_identify_pivot_strings == tgt_identify_pivot_strings
2573 and src_prefixed_pivot_columns == tgt_prefixed_pivot_columns
2574 and src_pivot_column_naming == tgt_pivot_column_naming
2575 ):
2576 return None
2577
2578 in_exprs = expression.fields[0].expressions
2579 step = len(columns) // len(in_exprs)
2580
2581 # Derive the per-value suffix from the first stored column vs the first IN-list value.
2582 # This correctly handles dialects (e.g. Spark single-agg) that ignore agg aliases.
2583 first_base = in_exprs[0].sql() if src_identify_pivot_strings else in_exprs[0].alias_or_name
2584 first_stored = columns[0].name
2585
2586 # exit if only suffix matches, not prefix. (e.g. BigQuery, which cannot be fixed)
2587 if not first_stored.startswith(first_base):
2588 return None
2589
2590 suffix = first_stored[len(first_base) :]
2591
2592 # Whether the target dialect would append an agg-name suffix for this pivot.
2593 # Spark single-agg uniquely drops the agg alias entirely.
2594 target_has_suffix = (
2595 len(expression.expressions) > 1 or tgt_pivot_column_naming != "agg_name_if_multiple"
2596 ) and any(a.alias for a in expression.expressions)
2597 source_has_suffix = suffix != ""
2598
2599 new_exprs: list[exp.Expression] = []
2600 modified = False
2601 for val_idx, e in enumerate(in_exprs):
2602 if isinstance(e, exp.PivotAlias):
2603 new_exprs.append(e)
2604 continue
2605
2606 i = val_idx * step
2607 stored_full = columns[i].name
2608 stored_value = stored_full[: -len(suffix)] if suffix else stored_full
2609 target_value = e.sql() if tgt_identify_pivot_strings else e.alias_or_name
2610
2611 # Source had a suffix, but target won't apply one
2612 if source_has_suffix and not target_has_suffix:
2613 new_exprs.append(
2614 exp.PivotAlias(this=e, alias=exp.to_identifier(stored_full, quoted=True))
2615 )
2616 modified = True
2617 # Value-part mismatch (e.g. Snowflake's literal-style values vs others).
2618 elif stored_value != target_value:
2619 new_exprs.append(
2620 exp.PivotAlias(this=e, alias=exp.to_identifier(stored_value, quoted=True))
2621 )
2622 modified = True
2623 else:
2624 new_exprs.append(e)
2625
2626 return new_exprs if modified else None
2627
2628 def pivot_sql(self, expression: exp.Pivot) -> str:
2629 expressions = self.expressions(expression, flat=True)
2630 direction = "UNPIVOT" if expression.unpivot else "PIVOT"
2631
2632 group = self.sql(expression, "group")
2633
2634 if expression.this:
2635 this = self.sql(expression, "this")
2636 if not expressions:
2637 sql = f"UNPIVOT {this}"
2638 else:
2639 on = f"{self.seg('ON')} {expressions}"
2640 into = self.sql(expression, "into")
2641 into = f"{self.seg('INTO')} {into}" if into else ""
2642 using = self.expressions(expression, key="using", flat=True)
2643 using = f"{self.seg('USING')} {using}" if using else ""
2644 sql = f"{direction} {this}{on}{into}{using}{group}"
2645 return self.prepend_ctes(expression, sql)
2646
2647 if not expression.unpivot:
2648 # Wrap IN-list values with explicit aliases where the target dialect would differ
2649 new_field_exprs = self._pivot_in_value_aliases(expression)
2650 if new_field_exprs is not None:
2651 expression.fields[0].set("expressions", new_field_exprs)
2652
2653 alias = self.sql(expression, "alias")
2654 if alias:
2655 alias = f" AS {alias}" if self.PIVOT_ALIAS_WITH_AS else f" {alias}"
2656
2657 fields = self.expressions(
2658 expression,
2659 "fields",
2660 sep=" ",
2661 dynamic=True,
2662 new_line=True,
2663 skip_first=True,
2664 skip_last=True,
2665 )
2666
2667 include_nulls = expression.args.get("include_nulls")
2668 if include_nulls is not None:
2669 nulls = " INCLUDE NULLS " if include_nulls else " EXCLUDE NULLS "
2670 else:
2671 nulls = ""
2672
2673 default_on_null = self.sql(expression, "default_on_null")
2674 default_on_null = f" DEFAULT ON NULL ({default_on_null})" if default_on_null else ""
2675 sql = f"{self.seg(direction)}{nulls}({expressions} FOR {fields}{default_on_null}{group}){alias}"
2676 return self.prepend_ctes(expression, sql)
2677
2678 def version_sql(self, expression: exp.Version) -> str:
2679 this = f"FOR {expression.name}"
2680 kind = expression.text("kind")
2681 expr = self.sql(expression, "expression")
2682 return f"{this} {kind} {expr}"
2683
2684 def tuple_sql(self, expression: exp.Tuple) -> str:
2685 return f"({self.expressions(expression, dynamic=True, new_line=True, skip_first=True, skip_last=True)})"
2686
2687 def _update_from_joins_sql(self, expression: exp.Update) -> tuple[str, str]:
2688 """
2689 Returns (join_sql, from_sql) for UPDATE statements.
2690 - join_sql: placed after UPDATE table, before SET
2691 - from_sql: placed after SET clause (standard position)
2692 Dialects like MySQL need to convert FROM to JOIN syntax.
2693 """
2694 if self.UPDATE_STATEMENT_SUPPORTS_FROM or not (from_expr := expression.args.get("from_")):
2695 return ("", self.sql(expression, "from_"))
2696
2697 # Qualify unqualified columns in SET clause with the target table
2698 # MySQL requires qualified column names in multi-table UPDATE to avoid ambiguity
2699 target_table = expression.this
2700 if isinstance(target_table, exp.Table):
2701 target_name = exp.to_identifier(target_table.alias_or_name)
2702 for eq in expression.expressions:
2703 col = eq.this
2704 if isinstance(col, exp.Column) and not col.table:
2705 col.set("table", target_name)
2706
2707 table = from_expr.this
2708 if nested_joins := table.args.get("joins", []):
2709 table.set("joins", None)
2710
2711 join_sql = self.sql(exp.Join(this=table, on=exp.true()))
2712 for nested in nested_joins:
2713 if not nested.args.get("on") and not nested.args.get("using"):
2714 nested.set("on", exp.true())
2715 join_sql += self.sql(nested)
2716
2717 return (join_sql, "")
2718
2719 def update_sql(self, expression: exp.Update) -> str:
2720 hint = self.sql(expression, "hint")
2721 this = self.sql(expression, "this")
2722 join_sql, from_sql = self._update_from_joins_sql(expression)
2723 set_sql = self.expressions(expression, flat=True)
2724 where_sql = self.sql(expression, "where")
2725 returning = self.sql(expression, "returning")
2726 order = self.sql(expression, "order")
2727 limit = self.sql(expression, "limit")
2728 if self.RETURNING_END:
2729 expression_sql = f"{from_sql}{where_sql}{returning}"
2730 else:
2731 expression_sql = f"{returning}{from_sql}{where_sql}"
2732 options = self.expressions(expression, key="options")
2733 options = f" OPTION({options})" if options else ""
2734 sql = f"UPDATE{hint} {this}{join_sql} SET {set_sql}{expression_sql}{order}{limit}{options}"
2735 return self.prepend_ctes(expression, sql)
2736
2737 def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str:
2738 values_as_table = values_as_table and self.VALUES_AS_TABLE
2739
2740 # The VALUES clause is still valid in an `INSERT INTO ..` statement, for example
2741 if values_as_table or not expression.find_ancestor(exp.From, exp.Join):
2742 args = self.expressions(expression)
2743 alias = self.sql(expression, "alias")
2744 values = f"VALUES{self.seg('')}{args}"
2745 values = (
2746 f"({values})"
2747 if self.WRAP_DERIVED_VALUES
2748 and (alias or isinstance(expression.parent, (exp.From, exp.Table)))
2749 else values
2750 )
2751 values = self.query_modifiers(expression, values)
2752 return f"{values} AS {alias}" if alias else values
2753
2754 # Converts `VALUES...` expression into a series of select unions.
2755 alias_node = expression.args.get("alias")
2756 column_names = alias_node and alias_node.columns
2757
2758 selects: list[exp.Query] = []
2759
2760 for i, tup in enumerate(expression.expressions):
2761 row = tup.expressions
2762
2763 if i == 0 and column_names:
2764 row = [
2765 exp.alias_(value, column_name) for value, column_name in zip(row, column_names)
2766 ]
2767
2768 selects.append(exp.Select(expressions=row))
2769
2770 if self.pretty:
2771 # This may result in poor performance for large-cardinality `VALUES` tables, due to
2772 # the deep nesting of the resulting exp.Unions. If this is a problem, either increase
2773 # `sys.setrecursionlimit` to avoid RecursionErrors, or don't set `pretty`.
2774 query = reduce(lambda x, y: exp.union(x, y, distinct=False, copy=False), selects)
2775 return self.subquery_sql(query.subquery(alias_node and alias_node.this, copy=False))
2776
2777 alias = f" AS {self.sql(alias_node, 'this')}" if alias_node else ""
2778 unions = " UNION ALL ".join(self.sql(select) for select in selects)
2779 return f"({unions}){alias}"
2780
2781 def var_sql(self, expression: exp.Var) -> str:
2782 return self.sql(expression, "this")
2783
2784 @unsupported_args("expressions")
2785 def into_sql(self, expression: exp.Into) -> str:
2786 temporary = " TEMPORARY" if expression.args.get("temporary") else ""
2787 unlogged = " UNLOGGED" if expression.args.get("unlogged") else ""
2788 return f"{self.seg('INTO')}{temporary or unlogged} {self.sql(expression, 'this')}"
2789
2790 def from_sql(self, expression: exp.From) -> str:
2791 return f"{self.seg('FROM')} {self.sql(expression, 'this')}"
2792
2793 def groupingsets_sql(self, expression: exp.GroupingSets) -> str:
2794 grouping_sets = self.expressions(expression, indent=False)
2795 return f"GROUPING SETS {self.wrap(grouping_sets)}"
2796
2797 def rollup_sql(self, expression: exp.Rollup) -> str:
2798 expressions = self.expressions(expression, indent=False)
2799 return f"ROLLUP {self.wrap(expressions)}" if expressions else "WITH ROLLUP"
2800
2801 def rollupindex_sql(self, expression: exp.RollupIndex) -> str:
2802 this = self.sql(expression, "this")
2803
2804 columns = self.expressions(expression, flat=True)
2805
2806 from_sql = self.sql(expression, "from_index")
2807 from_sql = f" FROM {from_sql}" if from_sql else ""
2808
2809 properties = expression.args.get("properties")
2810 properties_sql = (
2811 f" {self.properties(properties, prefix='PROPERTIES')}" if properties else ""
2812 )
2813
2814 return f"{this}({columns}){from_sql}{properties_sql}"
2815
2816 def rollupproperty_sql(self, expression: exp.RollupProperty) -> str:
2817 return f"ROLLUP ({self.expressions(expression, flat=True)})"
2818
2819 def cube_sql(self, expression: exp.Cube) -> str:
2820 expressions = self.expressions(expression, indent=False)
2821 return f"CUBE {self.wrap(expressions)}" if expressions else "WITH CUBE"
2822
2823 def group_sql(self, expression: exp.Group) -> str:
2824 group_by_all = expression.args.get("all")
2825 if group_by_all is True:
2826 modifier = " ALL"
2827 elif group_by_all is False:
2828 modifier = " DISTINCT"
2829 else:
2830 modifier = ""
2831
2832 group_by = self.op_expressions(f"GROUP BY{modifier}", expression)
2833
2834 grouping_sets = self.expressions(expression, key="grouping_sets")
2835 cube = self.expressions(expression, key="cube")
2836 rollup = self.expressions(expression, key="rollup")
2837
2838 groupings = csv(
2839 self.seg(grouping_sets) if grouping_sets else "",
2840 self.seg(cube) if cube else "",
2841 self.seg(rollup) if rollup else "",
2842 self.seg("WITH TOTALS") if expression.args.get("totals") else "",
2843 sep=self.GROUPINGS_SEP,
2844 )
2845
2846 if (
2847 expression.expressions
2848 and groupings
2849 and groupings.strip() not in ("WITH CUBE", "WITH ROLLUP")
2850 ):
2851 add_separator = True
2852
2853 if grouping_sets:
2854 if self.SUPPORTS_GROUPING_SETS_AS_SUFFIX:
2855 add_separator = False
2856 else:
2857 self.unsupported(
2858 "GROUPING SETS without a comma after GROUP BY expressions is not supported"
2859 )
2860
2861 if add_separator:
2862 group_by = f"{group_by}{self.GROUPINGS_SEP}"
2863
2864 return f"{group_by}{groupings}"
2865
2866 def having_sql(self, expression: exp.Having) -> str:
2867 this = self.indent(self.sql(expression, "this"))
2868 return f"{self.seg('HAVING')}{self.sep()}{this}"
2869
2870 def connect_sql(self, expression: exp.Connect) -> str:
2871 start = self.sql(expression, "start")
2872 start = self.seg(f"START WITH {start}") if start else ""
2873 nocycle = " NOCYCLE" if expression.args.get("nocycle") else ""
2874 connect = self.sql(expression, "connect")
2875 connect = self.seg(f"CONNECT BY{nocycle} {connect}")
2876 return start + connect
2877
2878 def prior_sql(self, expression: exp.Prior) -> str:
2879 return f"PRIOR {self.sql(expression, 'this')}"
2880
2881 def join_sql(self, expression: exp.Join) -> str:
2882 if not self.SEMI_ANTI_JOIN_WITH_SIDE and expression.kind in ("SEMI", "ANTI"):
2883 side = None
2884 else:
2885 side = expression.side
2886
2887 op_sql = " ".join(
2888 op
2889 for op in (
2890 expression.method,
2891 "GLOBAL" if expression.args.get("global_") else None,
2892 side,
2893 expression.kind,
2894 expression.hint if self.JOIN_HINTS else None,
2895 "DIRECTED" if expression.args.get("directed") and self.DIRECTED_JOINS else None,
2896 )
2897 if op
2898 )
2899 match_cond = self.sql(expression, "match_condition")
2900 match_cond = f" MATCH_CONDITION ({match_cond})" if match_cond else ""
2901 on_sql = self.sql(expression, "on")
2902 using = expression.args.get("using")
2903
2904 if not on_sql and using:
2905 on_sql = csv(*(self.sql(column) for column in using))
2906
2907 this = expression.this
2908 this_sql = self.sql(this)
2909
2910 exprs = self.expressions(expression)
2911 if exprs:
2912 this_sql = f"{this_sql},{self.seg(exprs)}"
2913
2914 if on_sql:
2915 on_sql = self.indent(on_sql, skip_first=True)
2916 space = self.seg(" " * self.pad) if self.pretty else " "
2917 if using:
2918 on_sql = f"{space}USING ({on_sql})"
2919 else:
2920 on_sql = f"{space}ON {on_sql}"
2921 elif not op_sql:
2922 if isinstance(this, exp.Lateral) and this.args.get("cross_apply") is not None:
2923 return f" {this_sql}"
2924
2925 return f", {this_sql}"
2926
2927 if op_sql != "STRAIGHT_JOIN":
2928 op_sql = f"{op_sql} JOIN" if op_sql else "JOIN"
2929
2930 pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2931 return f"{self.seg(op_sql)} {this_sql}{match_cond}{on_sql}{pivots}"
2932
2933 def lambda_sql(self, expression: exp.Lambda, arrow_sep: str = "->", wrap: bool = True) -> str:
2934 args = self.expressions(expression, flat=True)
2935 args = f"({args})" if wrap and len(args.split(",")) > 1 else args
2936 return f"{args} {arrow_sep} {self.sql(expression, 'this')}"
2937
2938 def lateral_op(self, expression: exp.Lateral) -> str:
2939 cross_apply = expression.args.get("cross_apply")
2940
2941 # https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/
2942 if cross_apply is True:
2943 op = "INNER JOIN "
2944 elif cross_apply is False:
2945 op = "LEFT JOIN "
2946 else:
2947 op = ""
2948
2949 return f"{op}LATERAL"
2950
2951 def lateral_sql(self, expression: exp.Lateral) -> str:
2952 this = self.sql(expression, "this")
2953
2954 if expression.args.get("view"):
2955 alias = expression.args["alias"]
2956 columns = self.expressions(alias, key="columns", flat=True)
2957 table = f" {alias.name}" if alias.name else ""
2958 columns = f" AS {columns}" if columns else ""
2959 op_sql = self.seg(f"LATERAL VIEW{' OUTER' if expression.args.get('outer') else ''}")
2960 return f"{op_sql}{self.sep()}{this}{table}{columns}"
2961
2962 alias = self.sql(expression, "alias")
2963 alias = f" AS {alias}" if alias else ""
2964
2965 ordinality = expression.args.get("ordinality") or ""
2966 if ordinality:
2967 ordinality = f" WITH ORDINALITY{alias}"
2968 alias = ""
2969
2970 return f"{self.lateral_op(expression)} {this}{alias}{ordinality}"
2971
2972 def limit_sql(self, expression: exp.Limit, top: bool = False) -> str:
2973 this = self.sql(expression, "this")
2974
2975 args = [
2976 self._simplify_unless_literal(e) if self.LIMIT_ONLY_LITERALS else e
2977 for e in (expression.args.get(k) for k in ("offset", "expression"))
2978 if e
2979 ]
2980
2981 args_sql = ", ".join(self.sql(e) for e in args)
2982 args_sql = f"({args_sql})" if top and any(not e.is_number for e in args) else args_sql
2983 expressions = self.expressions(expression, flat=True)
2984 limit_options = self.sql(expression, "limit_options")
2985 expressions = f" BY {expressions}" if expressions else ""
2986
2987 return f"{this}{self.seg('TOP' if top else 'LIMIT')} {args_sql}{limit_options}{expressions}"
2988
2989 def offset_sql(self, expression: exp.Offset) -> str:
2990 this = self.sql(expression, "this")
2991 value = expression.expression
2992 value = self._simplify_unless_literal(value) if self.LIMIT_ONLY_LITERALS else value
2993 expressions = self.expressions(expression, flat=True)
2994 expressions = f" BY {expressions}" if expressions else ""
2995 return f"{this}{self.seg('OFFSET')} {self.sql(value)}{expressions}"
2996
2997 def setitem_sql(self, expression: exp.SetItem) -> str:
2998 kind = self.sql(expression, "kind")
2999 if not self.SET_ASSIGNMENT_REQUIRES_VARIABLE_KEYWORD and kind == "VARIABLE":
3000 kind = ""
3001 else:
3002 kind = f"{kind} " if kind else ""
3003 this = self.sql(expression, "this")
3004 expressions = self.expressions(expression)
3005 collate = self.sql(expression, "collate")
3006 collate = f" COLLATE {collate}" if collate else ""
3007 global_ = "GLOBAL " if expression.args.get("global_") else ""
3008 return f"{global_}{kind}{this}{expressions}{collate}"
3009
3010 def set_sql(self, expression: exp.Set) -> str:
3011 expressions = f" {self.expressions(expression, flat=True)}"
3012 tag = " TAG" if expression.args.get("tag") else ""
3013 return f"{'UNSET' if expression.args.get('unset') else 'SET'}{tag}{expressions}"
3014
3015 def queryband_sql(self, expression: exp.QueryBand) -> str:
3016 this = self.sql(expression, "this")
3017 update = " UPDATE" if expression.args.get("update") else ""
3018 scope = self.sql(expression, "scope")
3019 scope = f" FOR {scope}" if scope else ""
3020
3021 return f"QUERY_BAND = {this}{update}{scope}"
3022
3023 def pragma_sql(self, expression: exp.Pragma) -> str:
3024 return f"PRAGMA {self.sql(expression, 'this')}"
3025
3026 def lock_sql(self, expression: exp.Lock) -> str:
3027 if not self.LOCKING_READS_SUPPORTED:
3028 self.unsupported("Locking reads using 'FOR UPDATE/SHARE' are not supported")
3029 return ""
3030
3031 update = expression.args["update"]
3032 key = expression.args.get("key")
3033 if update:
3034 lock_type = "FOR NO KEY UPDATE" if key else "FOR UPDATE"
3035 else:
3036 lock_type = "FOR KEY SHARE" if key else "FOR SHARE"
3037 expressions = self.expressions(expression, flat=True)
3038 expressions = f" OF {expressions}" if expressions else ""
3039 wait = expression.args.get("wait")
3040
3041 if wait is not None:
3042 if isinstance(wait, exp.Literal):
3043 wait = f" WAIT {self.sql(wait)}"
3044 else:
3045 wait = " NOWAIT" if wait else " SKIP LOCKED"
3046
3047 return f"{lock_type}{expressions}{wait or ''}"
3048
3049 def literal_sql(self, expression: exp.Literal) -> str:
3050 text = expression.this or ""
3051 if expression.is_string:
3052 text = f"{self.dialect.QUOTE_START}{self.escape_str(text)}{self.dialect.QUOTE_END}"
3053 return text
3054
3055 def escape_str(
3056 self,
3057 text: str,
3058 escape_backslash: bool = True,
3059 delimiter: str | None = None,
3060 escaped_delimiter: str | None = None,
3061 is_byte_string: bool = False,
3062 ) -> str:
3063 if is_byte_string:
3064 supports_escape_sequences = self.dialect.BYTE_STRINGS_SUPPORT_ESCAPED_SEQUENCES
3065 else:
3066 supports_escape_sequences = self.dialect.STRINGS_SUPPORT_ESCAPED_SEQUENCES
3067
3068 if supports_escape_sequences:
3069 text = "".join(
3070 self.dialect.ESCAPED_SEQUENCES.get(ch, ch) if escape_backslash or ch != "\\" else ch
3071 for ch in text
3072 )
3073
3074 delimiter = delimiter or self.dialect.QUOTE_END
3075 escaped_delimiter = escaped_delimiter or self._escaped_quote_end
3076
3077 return self._replace_line_breaks(text).replace(delimiter, escaped_delimiter)
3078
3079 def loaddata_sql(self, expression: exp.LoadData) -> str:
3080 is_overwrite = expression.args.get("overwrite")
3081 overwrite = " OVERWRITE" if is_overwrite else ""
3082 this = self.sql(expression, "this")
3083
3084 files = expression.args.get("files")
3085 if files:
3086 files_sql = self.expressions(files, flat=True)
3087 files_sql = f"FILES{self.wrap(files_sql)}"
3088 if is_overwrite:
3089 this = f" {this}"
3090 elif expression.args.get("temp"):
3091 this = f" INTO TEMP TABLE {this}"
3092 else:
3093 this = f" INTO TABLE {this}"
3094 return f"LOAD DATA{overwrite}{this} FROM {files_sql}"
3095
3096 local = " LOCAL" if expression.args.get("local") else ""
3097 inpath = f" INPATH {self.sql(expression, 'inpath')}"
3098 this = f" INTO TABLE {this}"
3099 partition = self.sql(expression, "partition")
3100 partition = f" {partition}" if partition else ""
3101 input_format = self.sql(expression, "input_format")
3102 input_format = f" INPUTFORMAT {input_format}" if input_format else ""
3103 serde = self.sql(expression, "serde")
3104 serde = f" SERDE {serde}" if serde else ""
3105 return f"LOAD DATA{local}{inpath}{overwrite}{this}{partition}{input_format}{serde}"
3106
3107 def null_sql(self, *_) -> str:
3108 return "NULL"
3109
3110 def boolean_sql(self, expression: exp.Boolean) -> str:
3111 return "TRUE" if expression.this else "FALSE"
3112
3113 def booland_sql(self, expression: exp.Booland) -> str:
3114 return f"(({self.sql(expression, 'this')}) AND ({self.sql(expression, 'expression')}))"
3115
3116 def boolor_sql(self, expression: exp.Boolor) -> str:
3117 return f"(({self.sql(expression, 'this')}) OR ({self.sql(expression, 'expression')}))"
3118
3119 def order_sql(self, expression: exp.Order, flat: bool = False) -> str:
3120 this = self.sql(expression, "this")
3121 this = f"{this} " if this else this
3122 siblings = "SIBLINGS " if expression.args.get("siblings") else ""
3123 return self.op_expressions(f"{this}ORDER {siblings}BY", expression, flat=bool(this) or flat)
3124
3125 def withfill_sql(self, expression: exp.WithFill) -> str:
3126 from_sql = self.sql(expression, "from_")
3127 from_sql = f" FROM {from_sql}" if from_sql else ""
3128 to_sql = self.sql(expression, "to")
3129 to_sql = f" TO {to_sql}" if to_sql else ""
3130 step_sql = self.sql(expression, "step")
3131 step_sql = f" STEP {step_sql}" if step_sql else ""
3132 interpolated_values = [
3133 f"{self.sql(e, 'alias')} AS {self.sql(e, 'this')}"
3134 if isinstance(e, exp.Alias)
3135 else self.sql(e, "this")
3136 for e in expression.args.get("interpolate") or []
3137 ]
3138 interpolate = (
3139 f" INTERPOLATE ({', '.join(interpolated_values)})" if interpolated_values else ""
3140 )
3141 return f"WITH FILL{from_sql}{to_sql}{step_sql}{interpolate}"
3142
3143 def cluster_sql(self, expression: exp.Cluster) -> str:
3144 return self.op_expressions("CLUSTER BY", expression)
3145
3146 def clusterproperty_sql(self, expression: exp.ClusterProperty) -> str:
3147 if expression.this:
3148 self.unsupported(f"Unsupported CLUSTER BY {self.sql(expression, 'this')}")
3149 return ""
3150 expressions = self.expressions(expression, flat=True)
3151 return f"CLUSTER BY ({expressions})"
3152
3153 def distribute_sql(self, expression: exp.Distribute) -> str:
3154 return self.op_expressions("DISTRIBUTE BY", expression)
3155
3156 def sort_sql(self, expression: exp.Sort) -> str:
3157 return self.op_expressions("SORT BY", expression)
3158
3159 def _resolve_ordered_for_null_ordering_simulation(
3160 self, expression: exp.Ordered
3161 ) -> exp.Expr | None:
3162 """Resolve a bare ORDER BY name against the enclosing SELECT projection.
3163
3164 Returns the underlying expression of the uniquely-matching projection
3165 (Alias-stripped) for substitution into the NULLS FIRST/LAST CASE
3166 simulation, since the CASE is evaluated in FROM-clause scope rather
3167 than alias scope (MySQL error 1052). Returns None if no safe
3168 substitution applies, leaving the original behaviour unchanged.
3169 """
3170 this = expression.this
3171 if not (isinstance(this, exp.Column) and not this.table):
3172 return None
3173
3174 ancestor = expression.find_ancestor(exp.Select, exp.Window)
3175 if not isinstance(ancestor, exp.Select):
3176 return None
3177
3178 column_name = this.name
3179 matched: list[exp.Expr] = [
3180 p.this if isinstance(p, exp.Alias) else p
3181 for p in ancestor.selects
3182 if p.output_name == column_name
3183 ]
3184 match = matched[0] if len(matched) == 1 else None
3185
3186 # Skip the substitution when it would be identical to the existing
3187 # reference (e.g. ``SELECT col FROM t ORDER BY col``).
3188 if isinstance(match, exp.Column) and not match.table and match.name == column_name:
3189 return None
3190
3191 return match
3192
3193 def ordered_sql(self, expression: exp.Ordered) -> str:
3194 desc = expression.args.get("desc")
3195 asc = not desc
3196
3197 nulls_first = expression.args.get("nulls_first")
3198 nulls_last = not nulls_first
3199 nulls_are_large = self.dialect.NULL_ORDERING == "nulls_are_large"
3200 nulls_are_small = self.dialect.NULL_ORDERING == "nulls_are_small"
3201 nulls_are_last = self.dialect.NULL_ORDERING == "nulls_are_last"
3202
3203 this = self.sql(expression, "this")
3204
3205 sort_order = " DESC" if desc else (" ASC" if desc is False else "")
3206 nulls_sort_change = ""
3207 if nulls_first and (
3208 (asc and nulls_are_large) or (desc and nulls_are_small) or nulls_are_last
3209 ):
3210 nulls_sort_change = " NULLS FIRST"
3211 elif (
3212 nulls_last
3213 and ((asc and nulls_are_small) or (desc and nulls_are_large))
3214 and not nulls_are_last
3215 ):
3216 nulls_sort_change = " NULLS LAST"
3217
3218 # If the NULLS FIRST/LAST clause is unsupported, we add another sort key to simulate it
3219 if nulls_sort_change and not self.NULL_ORDERING_SUPPORTED:
3220 window = expression.find_ancestor(exp.Window, exp.Select)
3221
3222 if isinstance(window, exp.Window):
3223 window_this = window.this
3224 if isinstance(window_this, (exp.IgnoreNulls, exp.RespectNulls)):
3225 window_this = window_this.this
3226 spec = window.args.get("spec")
3227 else:
3228 window_this = None
3229 spec = None
3230
3231 # Some window functions (e.g. LAST_VALUE, RANK) support NULLS FIRST/LAST
3232 # without a spec or with a ROWS spec, but not with RANGE
3233 if not (
3234 isinstance(window_this, self.WINDOW_FUNCS_WITH_NULL_ORDERING)
3235 and (not spec or spec.text("kind").upper() == "ROWS")
3236 ):
3237 if window_this and spec:
3238 self.unsupported(
3239 f"'{nulls_sort_change.strip()}' translation not supported in window function {window_this.sql_name()}"
3240 )
3241 nulls_sort_change = ""
3242 elif self.NULL_ORDERING_SUPPORTED is False and (
3243 (asc and nulls_sort_change == " NULLS LAST")
3244 or (desc and nulls_sort_change == " NULLS FIRST")
3245 ):
3246 # BigQuery does not allow these ordering/nulls combinations when used under
3247 # an aggregation func or under a window containing one
3248 ancestor = expression.find_ancestor(exp.AggFunc, exp.Window, exp.Select)
3249
3250 if isinstance(ancestor, exp.Window):
3251 ancestor = ancestor.this
3252 if isinstance(ancestor, exp.AggFunc):
3253 self.unsupported(
3254 f"'{nulls_sort_change.strip()}' translation not supported for aggregate function {ancestor.sql_name()} with {sort_order} sort order"
3255 )
3256 nulls_sort_change = ""
3257 elif self.NULL_ORDERING_SUPPORTED is None:
3258 if expression.this.is_int:
3259 self.unsupported(
3260 f"'{nulls_sort_change.strip()}' translation not supported with positional ordering"
3261 )
3262 elif not isinstance(expression.this, exp.Rand):
3263 resolved = self._resolve_ordered_for_null_ordering_simulation(expression)
3264 target = self.sql(resolved) if resolved is not None else this
3265 null_sort_order = " DESC" if nulls_sort_change == " NULLS FIRST" else ""
3266 this = f"CASE WHEN {target} IS NULL THEN 1 ELSE 0 END{null_sort_order}, {target}"
3267 nulls_sort_change = ""
3268
3269 with_fill = self.sql(expression, "with_fill")
3270 with_fill = f" {with_fill}" if with_fill else ""
3271
3272 return f"{this}{sort_order}{nulls_sort_change}{with_fill}"
3273
3274 def matchrecognizemeasure_sql(self, expression: exp.MatchRecognizeMeasure) -> str:
3275 window_frame = self.sql(expression, "window_frame")
3276 window_frame = f"{window_frame} " if window_frame else ""
3277
3278 this = self.sql(expression, "this")
3279
3280 return f"{window_frame}{this}"
3281
3282 def matchrecognize_sql(self, expression: exp.MatchRecognize) -> str:
3283 partition = self.partition_by_sql(expression)
3284 order = self.sql(expression, "order")
3285 measures = self.expressions(expression, key="measures")
3286 measures = self.seg(f"MEASURES{self.seg(measures)}") if measures else ""
3287 rows = self.sql(expression, "rows")
3288 rows = self.seg(rows) if rows else ""
3289 after = self.sql(expression, "after")
3290 after = self.seg(after) if after else ""
3291 pattern = self.sql(expression, "pattern")
3292 pattern = self.seg(f"PATTERN ({pattern})") if pattern else ""
3293 definition_sqls = [
3294 f"{self.sql(definition, 'alias')} AS {self.sql(definition, 'this')}"
3295 for definition in expression.args.get("define", [])
3296 ]
3297 definitions = self.expressions(sqls=definition_sqls)
3298 define = self.seg(f"DEFINE{self.seg(definitions)}") if definitions else ""
3299 body = "".join(
3300 (
3301 partition,
3302 order,
3303 measures,
3304 rows,
3305 after,
3306 pattern,
3307 define,
3308 )
3309 )
3310 alias = self.sql(expression, "alias")
3311 alias = f" {alias}" if alias else ""
3312 return f"{self.seg('MATCH_RECOGNIZE')} {self.wrap(body)}{alias}"
3313
3314 def query_modifiers(self, expression: exp.Expr, *sqls: str) -> str:
3315 limit = expression.args.get("limit")
3316
3317 if self.LIMIT_FETCH == "LIMIT" and isinstance(limit, exp.Fetch):
3318 count = limit.args.get("count")
3319 # "FETCH FIRST ROWS ONLY" without a count means one row per the SQL
3320 # standard; emitting a bare "LIMIT" here would produce invalid SQL.
3321 limit = exp.Limit(
3322 expression=exp.maybe_copy(count) if count is not None else exp.Literal.number(1)
3323 )
3324 elif self.LIMIT_FETCH == "FETCH" and isinstance(limit, exp.Limit):
3325 limit = exp.Fetch(direction="FIRST", count=exp.maybe_copy(limit.expression))
3326
3327 return csv(
3328 *sqls,
3329 *[self.sql(join) for join in expression.args.get("joins") or []],
3330 self.sql(expression, "match"),
3331 *[self.sql(lateral) for lateral in expression.args.get("laterals") or []],
3332 self.sql(expression, "prewhere"),
3333 self.sql(expression, "where"),
3334 self.sql(expression, "connect"),
3335 self.sql(expression, "group"),
3336 self.sql(expression, "having"),
3337 *[gen(self, expression) for gen in self.AFTER_HAVING_MODIFIER_TRANSFORMS.values()],
3338 self.sql(expression, "order"),
3339 *self.offset_limit_modifiers(expression, isinstance(limit, exp.Fetch), limit),
3340 *self.after_limit_modifiers(expression),
3341 self.sql(expression, "for_"),
3342 self.options_modifier(expression),
3343 sep="",
3344 )
3345
3346 def options_modifier(self, expression: exp.Expr) -> str:
3347 options = self.expressions(expression, key="options")
3348 return f" {options}" if options else ""
3349
3350 def forclause_sql(self, expression: exp.ForClause) -> str:
3351 kind = expression.args["kind"]
3352 if kind == "BROWSE":
3353 return f"{self.sep()}FOR BROWSE"
3354 # FOR XML/JSON always carry at least AUTO/PATH. An empty rendering means
3355 # the target dialect doesn't support QueryOption, so we drop the clause.
3356 options = self.expressions(expression, key="expressions")
3357 if not options:
3358 return ""
3359 return f"{self.sep()}FOR {kind}{self.seg(options)}"
3360
3361 def queryoption_sql(self, expression: exp.QueryOption) -> str:
3362 self.unsupported("Unsupported query option.")
3363 return ""
3364
3365 def offset_limit_modifiers(
3366 self, expression: exp.Expr, fetch: bool, limit: exp.Fetch | exp.Limit | None
3367 ) -> list[str]:
3368 return [
3369 self.sql(expression, "offset") if fetch else self.sql(limit),
3370 self.sql(limit) if fetch else self.sql(expression, "offset"),
3371 ]
3372
3373 def after_limit_modifiers(self, expression: exp.Expr) -> list[str]:
3374 locks = self.expressions(expression, key="locks", sep=" ")
3375 locks = f" {locks}" if locks else ""
3376 return [locks, self.sql(expression, "sample")]
3377
3378 def select_sql(self, expression: exp.Select) -> str:
3379 into = expression.args.get("into")
3380 if not self.SUPPORTS_SELECT_INTO and into:
3381 into.pop()
3382
3383 hint = self.sql(expression, "hint")
3384 distinct = self.sql(expression, "distinct")
3385 distinct = f" {distinct}" if distinct else ""
3386 kind = self.sql(expression, "kind")
3387
3388 limit = expression.args.get("limit")
3389 if isinstance(limit, exp.Limit) and self.LIMIT_IS_TOP:
3390 top = self.limit_sql(limit, top=True)
3391 limit.pop()
3392 else:
3393 top = ""
3394
3395 expressions = self.expressions(expression)
3396
3397 if kind:
3398 if kind in self.SELECT_KINDS:
3399 kind = f" AS {kind}"
3400 else:
3401 if kind == "STRUCT":
3402 expressions = self.expressions(
3403 sqls=[
3404 self.sql(
3405 exp.Struct(
3406 expressions=[
3407 exp.PropertyEQ(this=e.args.get("alias"), expression=e.this)
3408 if isinstance(e, exp.Alias)
3409 else e
3410 for e in expression.expressions
3411 ]
3412 )
3413 )
3414 ]
3415 )
3416 kind = ""
3417
3418 operation_modifiers = self.expressions(expression, key="operation_modifiers", sep=" ")
3419 operation_modifiers = f"{self.sep()}{operation_modifiers}" if operation_modifiers else ""
3420
3421 exclude = expression.args.get("exclude")
3422
3423 if not self.STAR_EXCLUDE_REQUIRES_DERIVED_TABLE and exclude:
3424 exclude_sql = self.expressions(sqls=exclude, flat=True)
3425 expressions = f"{expressions}{self.seg('EXCLUDE')} ({exclude_sql})"
3426
3427 # We use LIMIT_IS_TOP as a proxy for whether DISTINCT should go first because tsql and Teradata
3428 # are the only dialects that use LIMIT_IS_TOP and both place DISTINCT first.
3429 top_distinct = f"{distinct}{hint}{top}" if self.LIMIT_IS_TOP else f"{top}{hint}{distinct}"
3430 expressions = f"{self.sep()}{expressions}" if expressions else expressions
3431 sql = self.query_modifiers(
3432 expression,
3433 f"SELECT{top_distinct}{operation_modifiers}{kind}{expressions}",
3434 self.sql(expression, "into", comment=False),
3435 self.sql(expression, "from_", comment=False),
3436 )
3437
3438 # If both the CTE and SELECT clauses have comments, generate the latter earlier
3439 if expression.args.get("with_"):
3440 sql = self.maybe_comment(sql, expression)
3441 expression.pop_comments()
3442
3443 sql = self.prepend_ctes(expression, sql)
3444
3445 if self.STAR_EXCLUDE_REQUIRES_DERIVED_TABLE and exclude:
3446 expression.set("exclude", None)
3447 subquery = expression.subquery(copy=False)
3448 star = exp.Star(except_=exclude)
3449 sql = self.sql(exp.select(star).from_(subquery, copy=False))
3450
3451 if not self.SUPPORTS_SELECT_INTO and into:
3452 if into.args.get("temporary"):
3453 table_kind = " TEMPORARY"
3454 elif self.SUPPORTS_UNLOGGED_TABLES and into.args.get("unlogged"):
3455 table_kind = " UNLOGGED"
3456 else:
3457 table_kind = ""
3458 sql = f"CREATE{table_kind} TABLE {self.sql(into.this)} AS {sql}"
3459
3460 return sql
3461
3462 def schema_sql(self, expression: exp.Schema) -> str:
3463 this = self.sql(expression, "this")
3464 sql = self.schema_columns_sql(expression)
3465 return f"{this} {sql}" if this and sql else this or sql
3466
3467 def schema_columns_sql(self, expression: exp.Expr) -> str:
3468 if expression.expressions:
3469 return f"({self.sep('')}{self.expressions(expression)}{self.seg(')', sep='')}"
3470 return ""
3471
3472 def star_sql(self, expression: exp.Star) -> str:
3473 except_ = self.expressions(expression, key="except_", flat=True)
3474 except_ = f"{self.seg(self.STAR_EXCEPT)} ({except_})" if except_ else ""
3475 replace = self.expressions(expression, key="replace", flat=True)
3476 replace = f"{self.seg('REPLACE')} ({replace})" if replace else ""
3477 rename = self.expressions(expression, key="rename", flat=True)
3478 rename = f"{self.seg('RENAME')} ({rename})" if rename else ""
3479 ilike = self.sql(expression, "ilike")
3480 ilike = f"{self.seg('ILIKE')} {ilike}" if ilike else ""
3481 return f"*{ilike}{except_}{replace}{rename}"
3482
3483 def parameter_sql(self, expression: exp.Parameter) -> str:
3484 this = self.sql(expression, "this")
3485 return f"{self.PARAMETER_TOKEN}{this}"
3486
3487 def sessionparameter_sql(self, expression: exp.SessionParameter) -> str:
3488 this = self.sql(expression, "this")
3489 kind = expression.text("kind")
3490 if kind:
3491 kind = f"{kind}."
3492 return f"@@{kind}{this}"
3493
3494 def placeholder_sql(self, expression: exp.Placeholder) -> str:
3495 return f"{self.NAMED_PLACEHOLDER_TOKEN}{expression.name}" if expression.this else "?"
3496
3497 def subquery_sql(self, expression: exp.Subquery, sep: str = " AS ") -> str:
3498 alias = self.sql(expression, "alias")
3499 alias = f"{sep}{alias}" if alias else ""
3500 sample = self.sql(expression, "sample")
3501 if self.dialect.ALIAS_POST_TABLESAMPLE and sample:
3502 alias = f"{sample}{alias}"
3503
3504 # Set to None so it's not generated again by self.query_modifiers()
3505 expression.set("sample", None)
3506
3507 pivots = self.expressions(expression, key="pivots", sep="", flat=True)
3508 sql = self.query_modifiers(expression, self.wrap(expression), alias, pivots)
3509 return self.prepend_ctes(expression, sql)
3510
3511 def qualify_sql(self, expression: exp.Qualify) -> str:
3512 this = self.indent(self.sql(expression, "this"))
3513 return f"{self.seg('QUALIFY')}{self.sep()}{this}"
3514
3515 def unnest_sql(self, expression: exp.Unnest) -> str:
3516 args = self.expressions(expression, flat=True)
3517
3518 alias = expression.args.get("alias")
3519 offset = expression.args.get("offset")
3520
3521 if self.UNNEST_WITH_ORDINALITY:
3522 if alias and isinstance(offset, exp.Expr):
3523 alias.append("columns", offset)
3524 expression.set("offset", None)
3525
3526 if alias and self.dialect.UNNEST_COLUMN_ONLY:
3527 columns = alias.columns
3528 alias = self.sql(columns[0]) if columns else ""
3529 else:
3530 alias = self.sql(alias)
3531
3532 alias = f" AS {alias}" if alias else alias
3533 if self.UNNEST_WITH_ORDINALITY:
3534 suffix = f" WITH ORDINALITY{alias}" if offset else alias
3535 else:
3536 if isinstance(offset, exp.Expr):
3537 suffix = f"{alias} WITH OFFSET AS {self.sql(offset)}"
3538 elif offset:
3539 suffix = f"{alias} WITH OFFSET"
3540 else:
3541 suffix = alias
3542
3543 return f"UNNEST({args}){suffix}"
3544
3545 def prewhere_sql(self, expression: exp.PreWhere) -> str:
3546 return ""
3547
3548 def where_sql(self, expression: exp.Where) -> str:
3549 this = self.indent(self.sql(expression, "this"))
3550 return f"{self.seg('WHERE')}{self.sep()}{this}"
3551
3552 def window_sql(self, expression: exp.Window) -> str:
3553 this = self.sql(expression, "this")
3554 partition = self.partition_by_sql(expression)
3555 order = expression.args.get("order")
3556 order = self.order_sql(order, flat=True) if order else ""
3557 spec = self.sql(expression, "spec")
3558 alias = self.sql(expression, "alias")
3559 over = self.sql(expression, "over") or "OVER"
3560
3561 this = f"{this} {'AS' if expression.arg_key == 'windows' else over}"
3562
3563 first = expression.args.get("first")
3564 if first is None:
3565 first = ""
3566 else:
3567 first = "FIRST" if first else "LAST"
3568
3569 if not partition and not order and not spec and alias:
3570 return f"{this} {alias}"
3571
3572 args = self.format_args(
3573 *[arg for arg in (alias, first, partition, order, spec) if arg], sep=" "
3574 )
3575 return f"{this} ({args})"
3576
3577 def partition_by_sql(self, expression: exp.Window | exp.MatchRecognize) -> str:
3578 partition = self.expressions(expression, key="partition_by", flat=True)
3579 return f"PARTITION BY {partition}" if partition else ""
3580
3581 def windowspec_sql(self, expression: exp.WindowSpec) -> str:
3582 kind = self.sql(expression, "kind")
3583 start = csv(self.sql(expression, "start"), self.sql(expression, "start_side"), sep=" ")
3584 end = (
3585 csv(self.sql(expression, "end"), self.sql(expression, "end_side"), sep=" ")
3586 or "CURRENT ROW"
3587 )
3588
3589 window_spec = f"{kind} BETWEEN {start} AND {end}"
3590
3591 exclude = self.sql(expression, "exclude")
3592 if exclude:
3593 if self.SUPPORTS_WINDOW_EXCLUDE:
3594 window_spec += f" EXCLUDE {exclude}"
3595 else:
3596 self.unsupported("EXCLUDE clause is not supported in the WINDOW clause")
3597
3598 return window_spec
3599
3600 def withingroup_sql(self, expression: exp.WithinGroup) -> str:
3601 this = self.sql(expression, "this")
3602 expression_sql = self.sql(expression, "expression")[1:] # order has a leading space
3603 return f"{this} WITHIN GROUP ({expression_sql})"
3604
3605 def between_sql(self, expression: exp.Between) -> str:
3606 this = self.sql(expression, "this")
3607 low = self.sql(expression, "low")
3608 high = self.sql(expression, "high")
3609 symmetric = expression.args.get("symmetric")
3610
3611 if symmetric and not self.SUPPORTS_BETWEEN_FLAGS:
3612 return f"({this} BETWEEN {low} AND {high} OR {this} BETWEEN {high} AND {low})"
3613
3614 flag = (
3615 " SYMMETRIC"
3616 if symmetric
3617 else " ASYMMETRIC"
3618 if symmetric is False and self.SUPPORTS_BETWEEN_FLAGS
3619 else "" # silently drop ASYMMETRIC – semantics identical
3620 )
3621 return f"{this} BETWEEN{flag} {low} AND {high}"
3622
3623 def bracket_offset_expressions(
3624 self, expression: exp.Bracket, index_offset: int | None = None
3625 ) -> list[exp.Expr]:
3626 if expression.args.get("json_access"):
3627 return expression.expressions
3628
3629 return apply_index_offset(
3630 expression.this,
3631 expression.expressions,
3632 (index_offset or self.dialect.INDEX_OFFSET) - expression.args.get("offset", 0),
3633 dialect=self.dialect,
3634 )
3635
3636 def bracket_sql(self, expression: exp.Bracket) -> str:
3637 expressions = self.bracket_offset_expressions(expression)
3638 expressions_sql = ", ".join(self.sql(e) for e in expressions)
3639 return f"{self.sql(expression, 'this')}[{expressions_sql}]"
3640
3641 def all_sql(self, expression: exp.All) -> str:
3642 this = self.sql(expression, "this")
3643 if not isinstance(expression.this, (exp.Tuple, exp.Paren)):
3644 this = self.wrap(this)
3645 return f"ALL {this}"
3646
3647 def any_sql(self, expression: exp.Any) -> str:
3648 this = self.sql(expression, "this")
3649 if isinstance(expression.this, (*exp.UNWRAPPED_QUERIES, exp.Paren)):
3650 if isinstance(expression.this, exp.UNWRAPPED_QUERIES):
3651 this = self.wrap(this)
3652 return f"ANY{this}"
3653 return f"ANY {this}"
3654
3655 def exists_sql(self, expression: exp.Exists) -> str:
3656 return f"EXISTS{self.wrap(expression)}"
3657
3658 def case_sql(self, expression: exp.Case) -> str:
3659 this = self.sql(expression, "this")
3660 statements = [f"CASE {this}" if this else "CASE"]
3661
3662 for e in expression.args["ifs"]:
3663 statements.append(f"WHEN {self.sql(e, 'this')}")
3664 statements.append(f"THEN {self.sql(e, 'true')}")
3665
3666 default = self.sql(expression, "default")
3667
3668 if default:
3669 statements.append(f"ELSE {default}")
3670
3671 statements.append("END")
3672
3673 if self.pretty and self.too_wide(statements):
3674 return self.indent("\n".join(statements), skip_first=True, skip_last=True)
3675
3676 return " ".join(statements)
3677
3678 def constraint_sql(self, expression: exp.Constraint) -> str:
3679 this = self.sql(expression, "this")
3680 expressions = self.expressions(expression, flat=True)
3681 return f"CONSTRAINT {this} {expressions}"
3682
3683 def nextvaluefor_sql(self, expression: exp.NextValueFor) -> str:
3684 order = expression.args.get("order")
3685 order = f" OVER ({self.order_sql(order, flat=True)})" if order else ""
3686 return f"NEXT VALUE FOR {self.sql(expression, 'this')}{order}"
3687
3688 def extract_sql(self, expression: exp.Extract) -> str:
3689 import sqlglot.dialects.dialect
3690
3691 this = (
3692 sqlglot.dialects.dialect.map_date_part(expression.this, self.dialect)
3693 if self.NORMALIZE_EXTRACT_DATE_PARTS
3694 else expression.this
3695 )
3696 if self.EXTRACT_ALLOWS_QUOTES:
3697 this_sql = self.sql(this)
3698 elif isinstance(this, exp.WeekStart):
3699 this_sql = self.weekstart_name(this)
3700 else:
3701 this_sql = this.name
3702 expression_sql = self.sql(expression, "expression")
3703
3704 return f"EXTRACT({this_sql} FROM {expression_sql})"
3705
3706 def trim_sql(self, expression: exp.Trim) -> str:
3707 trim_type = self.sql(expression, "position")
3708
3709 if trim_type == "LEADING":
3710 func_name = "LTRIM"
3711 elif trim_type == "TRAILING":
3712 func_name = "RTRIM"
3713 else:
3714 func_name = "TRIM"
3715
3716 return self.func(func_name, expression.this, expression.expression)
3717
3718 def convert_concat_args(self, expression: exp.Func) -> list[exp.Expr]:
3719 args = expression.expressions
3720 if isinstance(expression, exp.ConcatWs):
3721 args = args[1:] # Skip the delimiter
3722
3723 if self.dialect.STRICT_STRING_CONCAT and expression.args.get("safe"):
3724 args = [exp.cast(e, exp.DType.TEXT) for e in args]
3725
3726 concat_coalesce = (
3727 self.dialect.CONCAT_WS_COALESCE
3728 if isinstance(expression, exp.ConcatWs)
3729 else self.dialect.CONCAT_COALESCE
3730 )
3731
3732 if not concat_coalesce and expression.args.get("coalesce"):
3733
3734 def _wrap_with_coalesce(e: exp.Expr) -> exp.Expr:
3735 if not e.type:
3736 import sqlglot.optimizer.annotate_types
3737
3738 e = sqlglot.optimizer.annotate_types.annotate_types(e, dialect=self.dialect)
3739
3740 if e.is_string or e.is_type(exp.DType.ARRAY):
3741 return e
3742
3743 return exp.func("coalesce", e, exp.Literal.string(""))
3744
3745 args = [_wrap_with_coalesce(e) for e in args]
3746
3747 return args
3748
3749 def concat_sql(self, expression: exp.Concat) -> str:
3750 if self.dialect.CONCAT_COALESCE and not expression.args.get("coalesce"):
3751 # Dialect's CONCAT function coalesces NULLs to empty strings, but the expression does not.
3752 # Transpile to double pipe operators, which typically returns NULL if any args are NULL
3753 # instead of coalescing them to empty string.
3754 import sqlglot.dialects.dialect
3755
3756 return sqlglot.dialects.dialect.concat_to_dpipe_sql(self, expression)
3757
3758 expressions = self.convert_concat_args(expression)
3759
3760 # Some dialects don't allow a single-argument CONCAT call
3761 if not self.SUPPORTS_SINGLE_ARG_CONCAT and len(expressions) == 1:
3762 return self.sql(expressions[0])
3763
3764 return self.func("CONCAT", *expressions)
3765
3766 def concatws_sql(self, expression: exp.ConcatWs) -> str:
3767 if self.dialect.CONCAT_WS_COALESCE and not expression.args.get("coalesce"):
3768 # Dialect's CONCAT_WS function skips NULL args, but the expression does not.
3769 # Wrap the entire call in a CASE expression that returns NULL if any input IS NULL.
3770 all_args = expression.expressions
3771 expression.set("coalesce", True)
3772 return self.sql(
3773 exp.case()
3774 .when(exp.or_(*(arg.is_(exp.null()) for arg in all_args)), exp.null())
3775 .else_(expression)
3776 )
3777
3778 return self.func(
3779 "CONCAT_WS", seq_get(expression.expressions, 0), *self.convert_concat_args(expression)
3780 )
3781
3782 def check_sql(self, expression: exp.Check) -> str:
3783 this = self.sql(expression, key="this")
3784 return f"CHECK ({this})"
3785
3786 def foreignkey_sql(self, expression: exp.ForeignKey) -> str:
3787 expressions = self.expressions(expression, flat=True)
3788 expressions = f" ({expressions})" if expressions else ""
3789 reference = self.sql(expression, "reference")
3790 reference = f" {reference}" if reference else ""
3791 delete = self.sql(expression, "delete")
3792 delete = f" ON DELETE {delete}" if delete else ""
3793 update = self.sql(expression, "update")
3794 update = f" ON UPDATE {update}" if update else ""
3795 options = self.expressions(expression, key="options", flat=True, sep=" ")
3796 options = f" {options}" if options else ""
3797 return f"FOREIGN KEY{expressions}{reference}{delete}{update}{options}"
3798
3799 def primarykey_sql(self, expression: exp.PrimaryKey) -> str:
3800 this = self.sql(expression, "this")
3801 this = f" {this}" if this else ""
3802 expressions = self.expressions(expression, flat=True)
3803 include = self.sql(expression, "include")
3804 options = self.expressions(expression, key="options", flat=True, sep=" ")
3805 options = f" {options}" if options else ""
3806 return f"PRIMARY KEY{this} ({expressions}){include}{options}"
3807
3808 def timeserieskey_sql(self, expression: exp.TimeseriesKey) -> str:
3809 self.unsupported("TIMESERIES primary key columns are not supported")
3810 return self.sql(expression, "this")
3811
3812 def if_sql(self, expression: exp.If) -> str:
3813 return self.case_sql(exp.Case(ifs=[expression], default=expression.args.get("false")))
3814
3815 def matchagainst_sql(self, expression: exp.MatchAgainst) -> str:
3816 if self.MATCH_AGAINST_TABLE_PREFIX:
3817 expressions = []
3818 for expr in expression.expressions:
3819 if isinstance(expr, exp.Table):
3820 expressions.append(f"TABLE {self.sql(expr)}")
3821 else:
3822 expressions.append(expr)
3823 else:
3824 expressions = expression.expressions
3825
3826 modifier = expression.args.get("modifier")
3827 modifier = f" {modifier}" if modifier else ""
3828 return (
3829 f"{self.func('MATCH', *expressions)} AGAINST({self.sql(expression, 'this')}{modifier})"
3830 )
3831
3832 def jsonkeyvalue_sql(self, expression: exp.JSONKeyValue) -> str:
3833 return f"{self.sql(expression, 'this')}{self.JSON_KEY_VALUE_PAIR_SEP} {self.sql(expression, 'expression')}"
3834
3835 def jsonpath_sql(self, expression: exp.JSONPath) -> str:
3836 path = self.expressions(expression, sep="", flat=True).lstrip(".")
3837
3838 if self.QUOTE_JSON_PATH:
3839 path = self.escape_str(path)
3840 path = f"{self.dialect.QUOTE_START}{path}{self.dialect.QUOTE_END}"
3841
3842 return path
3843
3844 def json_path_part(self, expression: int | str | exp.JSONPathPart) -> str:
3845 if isinstance(expression, exp.JSONPathPart):
3846 transform = self.TRANSFORMS.get(expression.__class__)
3847 if not callable(transform):
3848 self.unsupported(f"Unsupported JSONPathPart type {expression.__class__.__name__}")
3849 return ""
3850
3851 return transform(self, expression)
3852
3853 if isinstance(expression, int):
3854 return str(expression)
3855
3856 if self._quote_json_path_key_using_brackets and self.JSON_PATH_SINGLE_QUOTE_ESCAPE:
3857 escaped = expression.replace("'", "\\'")
3858 escaped = f"'{escaped}'"
3859 else:
3860 escaped = expression.replace('"', '\\"')
3861 escaped = f'"{escaped}"'
3862
3863 return escaped
3864
3865 def formatjson_sql(self, expression: exp.FormatJson) -> str:
3866 return f"{self.sql(expression, 'this')} FORMAT JSON"
3867
3868 def formatphrase_sql(self, expression: exp.FormatPhrase) -> str:
3869 # Output the Teradata column FORMAT override.
3870 # https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Data-Types-and-Literals/Data-Type-Formats-and-Format-Phrases/FORMAT
3871 this = self.sql(expression, "this")
3872 fmt = self.sql(expression, "format")
3873 return f"{this} (FORMAT {fmt})"
3874
3875 def _jsonobject_sql(
3876 self, expression: exp.JSONObject | exp.JSONObjectAgg, name: str = ""
3877 ) -> str:
3878 null_handling = expression.args.get("null_handling")
3879 null_handling = f" {null_handling}" if null_handling else ""
3880
3881 unique_keys = expression.args.get("unique_keys")
3882 if unique_keys is not None:
3883 unique_keys = f" {'WITH' if unique_keys else 'WITHOUT'} UNIQUE KEYS"
3884 else:
3885 unique_keys = ""
3886
3887 return_type = self.sql(expression, "return_type")
3888 return_type = f" RETURNING {return_type}" if return_type else ""
3889 encoding = self.sql(expression, "encoding")
3890 encoding = f" ENCODING {encoding}" if encoding else ""
3891
3892 if not name:
3893 name = "JSON_OBJECT" if isinstance(expression, exp.JSONObject) else "JSON_OBJECTAGG"
3894
3895 return self.func(
3896 name,
3897 *expression.expressions,
3898 suffix=f"{null_handling}{unique_keys}{return_type}{encoding})",
3899 )
3900
3901 def jsonarray_sql(self, expression: exp.JSONArray) -> str:
3902 null_handling = expression.args.get("null_handling")
3903 null_handling = f" {null_handling}" if null_handling else ""
3904 return_type = self.sql(expression, "return_type")
3905 return_type = f" RETURNING {return_type}" if return_type else ""
3906 strict = " STRICT" if expression.args.get("strict") else ""
3907 return self.func(
3908 "JSON_ARRAY", *expression.expressions, suffix=f"{null_handling}{return_type}{strict})"
3909 )
3910
3911 def jsonarrayagg_sql(self, expression: exp.JSONArrayAgg) -> str:
3912 this = self.sql(expression, "this")
3913 order = self.sql(expression, "order")
3914 null_handling = expression.args.get("null_handling")
3915 null_handling = f" {null_handling}" if null_handling else ""
3916 return_type = self.sql(expression, "return_type")
3917 return_type = f" RETURNING {return_type}" if return_type else ""
3918 strict = " STRICT" if expression.args.get("strict") else ""
3919 return self.func(
3920 "JSON_ARRAYAGG",
3921 this,
3922 suffix=f"{order}{null_handling}{return_type}{strict})",
3923 )
3924
3925 def jsoncolumndef_sql(self, expression: exp.JSONColumnDef) -> str:
3926 path = self.sql(expression, "path")
3927 path = f" PATH {path}" if path else ""
3928 nested_schema = self.sql(expression, "nested_schema")
3929
3930 if nested_schema:
3931 return f"NESTED{path} {nested_schema}"
3932
3933 this = self.sql(expression, "this")
3934 kind = self.sql(expression, "kind")
3935 kind = f" {kind}" if kind else ""
3936 format_json = " FORMAT JSON" if expression.args.get("format_json") else ""
3937
3938 ordinality = " FOR ORDINALITY" if expression.args.get("ordinality") else ""
3939 return f"{this}{kind}{format_json}{path}{ordinality}"
3940
3941 def jsonschema_sql(self, expression: exp.JSONSchema) -> str:
3942 return self.func("COLUMNS", *expression.expressions)
3943
3944 def jsontable_sql(self, expression: exp.JSONTable) -> str:
3945 this = self.sql(expression, "this")
3946 path = self.sql(expression, "path")
3947 path = f", {path}" if path else ""
3948 error_handling = expression.args.get("error_handling")
3949 error_handling = f" {error_handling}" if error_handling else ""
3950 empty_handling = expression.args.get("empty_handling")
3951 empty_handling = f" {empty_handling}" if empty_handling else ""
3952 schema = self.sql(expression, "schema")
3953 return self.func(
3954 "JSON_TABLE", this, suffix=f"{path}{error_handling}{empty_handling} {schema})"
3955 )
3956
3957 def openjsoncolumndef_sql(self, expression: exp.OpenJSONColumnDef) -> str:
3958 this = self.sql(expression, "this")
3959 kind = self.sql(expression, "kind")
3960 path = self.sql(expression, "path")
3961 path = f" {path}" if path else ""
3962 as_json = " AS JSON" if expression.args.get("as_json") else ""
3963 return f"{this} {kind}{path}{as_json}"
3964
3965 def openjson_sql(self, expression: exp.OpenJSON) -> str:
3966 this = self.sql(expression, "this")
3967 path = self.sql(expression, "path")
3968 path = f", {path}" if path else ""
3969 expressions = self.expressions(expression)
3970 with_ = (
3971 f" WITH ({self.seg(self.indent(expressions), sep='')}{self.seg(')', sep='')}"
3972 if expressions
3973 else ""
3974 )
3975 return f"OPENJSON({this}{path}){with_}"
3976
3977 def in_sql(self, expression: exp.In) -> str:
3978 query = expression.args.get("query")
3979 unnest = expression.args.get("unnest")
3980 field = expression.args.get("field")
3981 is_global = " GLOBAL" if expression.args.get("is_global") else ""
3982
3983 if query:
3984 in_sql = self.sql(query)
3985 elif unnest:
3986 in_sql = self.in_unnest_op(unnest)
3987 elif field:
3988 in_sql = self.sql(field)
3989 else:
3990 in_sql = f"({self.expressions(expression, dynamic=True, new_line=True, skip_first=True, skip_last=True)})"
3991
3992 return f"{self.sql(expression, 'this')}{is_global} IN {in_sql}"
3993
3994 def in_unnest_op(self, unnest: exp.Unnest) -> str:
3995 return f"(SELECT {self.sql(unnest)})"
3996
3997 def interval_sql(self, expression: exp.Interval) -> str:
3998 include_keyword = not self.AUTO_REFRESH_BARE_INTERVALS or not isinstance(
3999 expression.find_ancestor(exp.AutoRefreshProperty, exp.Select),
4000 exp.AutoRefreshProperty,
4001 )
4002 interval_keyword = "INTERVAL" if include_keyword else ""
4003 unit_expression = expression.args.get("unit")
4004 unit = self.sql(unit_expression) if unit_expression else ""
4005 if not self.INTERVAL_ALLOWS_PLURAL_FORM:
4006 unit = self.TIME_PART_SINGULARS.get(unit, unit)
4007 unit = f" {unit}" if unit else ""
4008
4009 if self.SINGLE_STRING_INTERVAL:
4010 this = expression.this.name if expression.this else ""
4011 if this:
4012 interval_keyword = f"{interval_keyword} " if interval_keyword else ""
4013 if unit_expression and isinstance(unit_expression, exp.IntervalSpan):
4014 return f"{interval_keyword}'{this}'{unit}"
4015 return f"{interval_keyword}'{this}{unit}'"
4016 return f"{interval_keyword}{unit}"
4017
4018 this = self.sql(expression, "this")
4019 if this:
4020 if not include_keyword and expression.this.is_string:
4021 this = expression.this.name
4022 if not isinstance(expression.this, self.UNWRAPPED_INTERVAL_VALUES):
4023 this = f"({this})"
4024 if include_keyword:
4025 this = f" {this}"
4026
4027 return f"{interval_keyword}{this}{unit}"
4028
4029 def return_sql(self, expression: exp.Return) -> str:
4030 return f"RETURN {self.sql(expression, 'this')}"
4031
4032 def reference_sql(self, expression: exp.Reference) -> str:
4033 this = self.sql(expression, "this")
4034 expressions = self.expressions(expression, flat=True)
4035 expressions = f"({expressions})" if expressions else ""
4036 options = self.expressions(expression, key="options", flat=True, sep=" ")
4037 options = f" {options}" if options else ""
4038 return f"REFERENCES {this}{expressions}{options}"
4039
4040 def anonymous_sql(self, expression: exp.Anonymous) -> str:
4041 # We don't normalize qualified functions such as a.b.foo(), because they can be case-sensitive
4042 parent = expression.parent
4043 is_qualified = isinstance(parent, exp.Dot) and expression is parent.expression
4044
4045 return self.func(
4046 self.sql(expression, "this"), *expression.expressions, normalize=not is_qualified
4047 )
4048
4049 def paren_sql(self, expression: exp.Paren) -> str:
4050 sql = self.seg(self.indent(self.sql(expression, "this")), sep="")
4051 return f"({sql}{self.seg(')', sep='')}"
4052
4053 def neg_sql(self, expression: exp.Neg) -> str:
4054 # This makes sure we don't convert "- - 5" to "--5", which is a comment
4055 this_sql = self.sql(expression, "this")
4056 sep = " " if this_sql[0] == "-" else ""
4057 return f"-{sep}{this_sql}"
4058
4059 def not_sql(self, expression: exp.Not) -> str:
4060 return f"NOT {self.sql(expression, 'this')}"
4061
4062 def alias_sql(self, expression: exp.Alias) -> str:
4063 alias = self.sql(expression, "alias")
4064 alias = f" AS {alias}" if alias else ""
4065 return f"{self.sql(expression, 'this')}{alias}"
4066
4067 def pivotalias_sql(self, expression: exp.PivotAlias) -> str:
4068 alias = expression.args["alias"]
4069
4070 parent = expression.parent
4071 pivot = parent and parent.parent
4072
4073 if isinstance(pivot, exp.Pivot) and pivot.unpivot:
4074 identifier_alias = isinstance(alias, exp.Identifier)
4075 literal_alias = isinstance(alias, exp.Literal)
4076
4077 if identifier_alias and not self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
4078 alias.replace(exp.Literal.string(alias.output_name))
4079 elif not identifier_alias and literal_alias and self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
4080 alias.replace(exp.to_identifier(alias.output_name))
4081
4082 return self.alias_sql(expression)
4083
4084 def aliases_sql(self, expression: exp.Aliases) -> str:
4085 return f"{self.sql(expression, 'this')} AS ({self.expressions(expression, flat=True)})"
4086
4087 def atindex_sql(self, expression: exp.AtIndex) -> str:
4088 this = self.sql(expression, "this")
4089 index = self.sql(expression, "expression")
4090 return f"{this} AT {index}"
4091
4092 def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
4093 this = self.sql(expression, "this")
4094 zone = self.sql(expression, "zone")
4095 return f"{this} AT TIME ZONE {zone}"
4096
4097 def fromtimezone_sql(self, expression: exp.FromTimeZone) -> str:
4098 this = self.sql(expression, "this")
4099 zone = self.sql(expression, "zone")
4100 return f"{this} AT TIME ZONE {zone} AT TIME ZONE 'UTC'"
4101
4102 def fromiso8601date_sql(self, expression: exp.FromISO8601Date) -> str:
4103 return self.sql(exp.cast(expression.this, exp.DType.DATE))
4104
4105 def fromiso8601timestamp_sql(self, expression: exp.FromISO8601Timestamp) -> str:
4106 return self.sql(exp.cast(expression.this, exp.DType.TIMESTAMPTZ))
4107
4108 def fromiso8601timestampnanos_sql(self, expression: exp.FromISO8601TimestampNanos) -> str:
4109 return self.sql(exp.cast(expression.this, exp.DType.TIMESTAMPTZ))
4110
4111 def add_sql(self, expression: exp.Add) -> str:
4112 return self.binary(expression, "+")
4113
4114 def and_sql(self, expression: exp.And, stack: list[str | exp.Expr] | None = None) -> str:
4115 return self.connector_sql(expression, "AND", stack)
4116
4117 def or_sql(self, expression: exp.Or, stack: list[str | exp.Expr] | None = None) -> str:
4118 return self.connector_sql(expression, "OR", stack)
4119
4120 def xor_sql(self, expression: exp.Xor, stack: list[str | exp.Expr] | None = None) -> str:
4121 return self.connector_sql(expression, "XOR", stack)
4122
4123 def connector_sql(
4124 self,
4125 expression: exp.Connector,
4126 op: str,
4127 stack: list[str | exp.Expr] | None = None,
4128 ) -> str:
4129 if stack is not None:
4130 stack.append(expression.right)
4131 if expression.comments and self.comments:
4132 op = self.maybe_comment(op, comments=expression.comments)
4133
4134 stack.extend((op, expression.left))
4135 return op
4136
4137 stack = [expression]
4138 sqls: list[str] = []
4139 ops = set()
4140
4141 while stack:
4142 node = stack.pop()
4143 if isinstance(node, exp.Connector):
4144 ops.add(getattr(self, f"{node.key}_sql")(node, stack))
4145 else:
4146 sql = self.sql(node)
4147 if sqls and sqls[-1] in ops:
4148 sqls[-1] += f" {sql}"
4149 else:
4150 sqls.append(sql)
4151
4152 sep = "\n" if self.pretty and self.too_wide(sqls) else " "
4153 return sep.join(sqls)
4154
4155 def bitwiseand_sql(self, expression: exp.BitwiseAnd) -> str:
4156 return self.binary(expression, "&")
4157
4158 def bitwiseleftshift_sql(self, expression: exp.BitwiseLeftShift) -> str:
4159 return self.binary(expression, "<<")
4160
4161 def bitwisenot_sql(self, expression: exp.BitwiseNot) -> str:
4162 return f"~{self.sql(expression, 'this')}"
4163
4164 def bitwiseor_sql(self, expression: exp.BitwiseOr) -> str:
4165 return self.binary(expression, "|")
4166
4167 def bitwiserightshift_sql(self, expression: exp.BitwiseRightShift) -> str:
4168 return self.binary(expression, ">>")
4169
4170 def bitwisexor_sql(self, expression: exp.BitwiseXor) -> str:
4171 return self.binary(expression, "^")
4172
4173 def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
4174 format_sql = self.sql(expression, "format")
4175 format_sql = f" FORMAT {format_sql}" if format_sql else ""
4176 to_sql = self.sql(expression, "to")
4177 to_sql = f" {to_sql}" if to_sql else ""
4178 action = self.sql(expression, "action")
4179 action = f" {action}" if action else ""
4180 default = self.sql(expression, "default")
4181 default = f" DEFAULT {default} ON CONVERSION ERROR" if default else ""
4182 return f"{safe_prefix or ''}CAST({self.sql(expression, 'this')} AS{to_sql}{default}{format_sql}{action})"
4183
4184 # Base implementation that excludes safe, zone, and target_type metadata args
4185 def strtotime_sql(self, expression: exp.StrToTime) -> str:
4186 return self.func("STR_TO_TIME", expression.this, expression.args.get("format"))
4187
4188 # Base implementation that excludes the safe and default_year metadata args
4189 def strtodate_sql(self, expression: exp.StrToDate) -> str:
4190 return self.func("STR_TO_DATE", expression.this, expression.args.get("format"))
4191
4192 def parsedatetime_sql(self, expression: exp.ParseDatetime) -> str:
4193 return self.func(
4194 "PARSE_DATETIME",
4195 expression.this,
4196 expression.args.get("format"),
4197 expression.args.get("zone"),
4198 )
4199
4200 def currentdate_sql(self, expression: exp.CurrentDate) -> str:
4201 zone = self.sql(expression, "this")
4202 return f"CURRENT_DATE({zone})" if zone else "CURRENT_DATE"
4203
4204 def collate_sql(self, expression: exp.Collate) -> str:
4205 if self.COLLATE_IS_FUNC:
4206 return self.function_fallback_sql(expression)
4207 return self.binary(expression, "COLLATE")
4208
4209 def command_sql(self, expression: exp.Command) -> str:
4210 return f"{self.sql(expression, 'this')} {expression.text('expression').strip()}"
4211
4212 def comment_sql(self, expression: exp.Comment) -> str:
4213 this = self.sql(expression, "this")
4214 kind = expression.args["kind"]
4215 materialized = " MATERIALIZED" if expression.args.get("materialized") else ""
4216 exists_sql = " IF EXISTS " if expression.args.get("exists") else " "
4217 expression_sql = self.sql(expression, "expression")
4218 return f"COMMENT{exists_sql}ON{materialized} {kind} {this} IS {expression_sql}"
4219
4220 def mergetreettlaction_sql(self, expression: exp.MergeTreeTTLAction) -> str:
4221 this = self.sql(expression, "this")
4222 delete = " DELETE" if expression.args.get("delete") else ""
4223 recompress = self.sql(expression, "recompress")
4224 recompress = f" RECOMPRESS {recompress}" if recompress else ""
4225 to_disk = self.sql(expression, "to_disk")
4226 to_disk = f" TO DISK {to_disk}" if to_disk else ""
4227 to_volume = self.sql(expression, "to_volume")
4228 to_volume = f" TO VOLUME {to_volume}" if to_volume else ""
4229 return f"{this}{delete}{recompress}{to_disk}{to_volume}"
4230
4231 def mergetreettl_sql(self, expression: exp.MergeTreeTTL) -> str:
4232 where = self.sql(expression, "where")
4233 group = self.sql(expression, "group")
4234 aggregates = self.expressions(expression, key="aggregates")
4235 aggregates = self.seg("SET") + self.seg(aggregates) if aggregates else ""
4236
4237 if not (where or group or aggregates) and len(expression.expressions) == 1:
4238 return f"TTL {self.expressions(expression, flat=True)}"
4239
4240 return f"TTL{self.seg(self.expressions(expression))}{where}{group}{aggregates}"
4241
4242 def transaction_sql(self, expression: exp.Transaction) -> str:
4243 modes = self.expressions(expression, key="modes")
4244 modes = f" {modes}" if modes else ""
4245 return f"BEGIN{modes}"
4246
4247 def commit_sql(self, expression: exp.Commit) -> str:
4248 chain = expression.args.get("chain")
4249 if chain is not None:
4250 chain = " AND CHAIN" if chain else " AND NO CHAIN"
4251
4252 return f"COMMIT{chain or ''}"
4253
4254 def rollback_sql(self, expression: exp.Rollback) -> str:
4255 savepoint = expression.args.get("savepoint")
4256 savepoint = f" TO {savepoint}" if savepoint else ""
4257 return f"ROLLBACK{savepoint}"
4258
4259 def altercolumn_sql(self, expression: exp.AlterColumn) -> str:
4260 this = self.sql(expression, "this")
4261
4262 exists = ""
4263 if expression.args.get("exists"):
4264 if self.SUPPORTS_ALTER_COLUMN_IF_EXISTS:
4265 exists = " IF EXISTS"
4266 else:
4267 self.unsupported("ALTER COLUMN IF EXISTS is not supported by this dialect")
4268
4269 dtype = self.sql(expression, "dtype")
4270 if dtype:
4271 collate = self.sql(expression, "collate")
4272 collate = f" COLLATE {collate}" if collate else ""
4273 using = self.sql(expression, "using")
4274 using = f" USING {using}" if using else ""
4275 alter_set_type = self.ALTER_SET_TYPE + " " if self.ALTER_SET_TYPE else ""
4276 null_constraint = self._alter_column_null_constraint_sql(expression)
4277
4278 return (
4279 f"ALTER COLUMN{exists} {this} {alter_set_type}{dtype}"
4280 f"{collate}{using}{null_constraint}"
4281 )
4282
4283 default = self.sql(expression, "default")
4284 if default:
4285 return f"ALTER COLUMN{exists} {this} SET DEFAULT {default}"
4286
4287 comment = self.sql(expression, "comment")
4288 if comment:
4289 return f"ALTER COLUMN{exists} {this} COMMENT {comment}"
4290
4291 visible = expression.args.get("visible")
4292 if visible:
4293 return f"ALTER COLUMN{exists} {this} SET {visible}"
4294
4295 allow_null = expression.args.get("allow_null")
4296 drop = expression.args.get("drop")
4297
4298 if not drop and not allow_null:
4299 self.unsupported("Unsupported ALTER COLUMN syntax")
4300
4301 if allow_null is not None:
4302 keyword = "DROP" if drop else "SET"
4303 return f"ALTER COLUMN{exists} {this} {keyword} NOT NULL"
4304
4305 return f"ALTER COLUMN{exists} {this} DROP DEFAULT"
4306
4307 def _alter_column_null_constraint_sql(self, expression: exp.AlterColumn) -> str:
4308 allow_null = expression.args.get("allow_null")
4309 if allow_null is None:
4310 return ""
4311
4312 if not self.SUPPORTS_ALTER_COLUMN_NULLABILITY:
4313 self.unsupported("ALTER COLUMN cannot set nullability along with a type")
4314 return ""
4315
4316 return " NULL" if allow_null else " NOT NULL"
4317
4318 def modifycolumn_sql(self, expression: exp.ModifyColumn) -> str:
4319 this = self.sql(expression, "this")
4320 rename_from = self.sql(expression, "rename_from")
4321 if rename_from:
4322 if not self.SUPPORTS_CHANGE_COLUMN:
4323 self.unsupported("CHANGE COLUMN is not supported in this dialect")
4324 return f"CHANGE COLUMN {rename_from} {this}"
4325 if not self.SUPPORTS_MODIFY_COLUMN:
4326 self.unsupported("MODIFY COLUMN is not supported in this dialect")
4327 return f"MODIFY COLUMN {this}"
4328
4329 def alterindex_sql(self, expression: exp.AlterIndex) -> str:
4330 this = self.sql(expression, "this")
4331
4332 visible = expression.args.get("visible")
4333 visible_sql = "VISIBLE" if visible else "INVISIBLE"
4334
4335 return f"ALTER INDEX {this} {visible_sql}"
4336
4337 def alterdiststyle_sql(self, expression: exp.AlterDistStyle) -> str:
4338 this = self.sql(expression, "this")
4339 if not isinstance(expression.this, exp.Var):
4340 this = f"KEY DISTKEY {this}"
4341 return f"ALTER DISTSTYLE {this}"
4342
4343 def altersortkey_sql(self, expression: exp.AlterSortKey) -> str:
4344 compound = " COMPOUND" if expression.args.get("compound") else ""
4345 this = self.sql(expression, "this")
4346 expressions = self.expressions(expression, flat=True)
4347 expressions = f"({expressions})" if expressions else ""
4348 return f"ALTER{compound} SORTKEY {this or expressions}"
4349
4350 def alterrename_sql(self, expression: exp.AlterRename, include_to: bool = True) -> str:
4351 if not self.RENAME_TABLE_WITH_DB:
4352 # Remove db from tables
4353 expression = expression.transform(
4354 lambda n: exp.table_(n.this) if isinstance(n, exp.Table) else n
4355 ).assert_is(exp.AlterRename)
4356 this = self.sql(expression, "this")
4357 to_kw = " TO" if include_to else ""
4358 return f"RENAME{to_kw} {this}"
4359
4360 def renamecolumn_sql(self, expression: exp.RenameColumn) -> str:
4361 exists = " IF EXISTS" if expression.args.get("exists") else ""
4362 old_column = self.sql(expression, "this")
4363 new_column = self.sql(expression, "to")
4364 return f"RENAME COLUMN{exists} {old_column} TO {new_column}"
4365
4366 def alterset_sql(self, expression: exp.AlterSet) -> str:
4367 exprs = self.expressions(expression, flat=True)
4368 if self.ALTER_SET_WRAPPED:
4369 exprs = f"({exprs})"
4370
4371 return f"SET {exprs}"
4372
4373 def alter_sql(self, expression: exp.Alter) -> str:
4374 actions = expression.args["actions"]
4375
4376 if not self.dialect.ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN and isinstance(
4377 actions[0], exp.ColumnDef
4378 ):
4379 actions_sql = self.expressions(expression, key="actions", flat=True)
4380 actions_sql = f"ADD {actions_sql}"
4381 else:
4382 actions_list = []
4383 for action in actions:
4384 if isinstance(action, (exp.ColumnDef, exp.Schema)):
4385 action_sql = self.add_column_sql(action)
4386 else:
4387 action_sql = self.sql(action)
4388 if isinstance(action, exp.Query):
4389 action_sql = f"AS {action_sql}"
4390
4391 actions_list.append(action_sql)
4392
4393 actions_sql = self.format_args(*actions_list).lstrip("\n")
4394
4395 iceberg = (
4396 "ICEBERG "
4397 if expression.args.get("iceberg") and self.SUPPORTS_DROP_ALTER_ICEBERG_PROPERTY
4398 else ""
4399 )
4400 exists = " IF EXISTS" if expression.args.get("exists") else ""
4401 on_cluster = self.sql(expression, "cluster")
4402 on_cluster = f" {on_cluster}" if on_cluster else ""
4403 only = " ONLY" if expression.args.get("only") else ""
4404 options = self.expressions(expression, key="options")
4405 options = f", {options}" if options else ""
4406 kind = self.sql(expression, "kind")
4407 not_valid = " NOT VALID" if expression.args.get("not_valid") else ""
4408 check = " WITH CHECK" if expression.args.get("check") else ""
4409 cascade = (
4410 " CASCADE"
4411 if expression.args.get("cascade") and self.dialect.ALTER_TABLE_SUPPORTS_CASCADE
4412 else ""
4413 )
4414 this = self.sql(expression, "this")
4415 this = f" {this}" if this else ""
4416
4417 return f"ALTER {iceberg}{kind}{exists}{only}{this}{on_cluster}{check}{self.sep()}{actions_sql}{not_valid}{options}{cascade}"
4418
4419 def altersession_sql(self, expression: exp.AlterSession) -> str:
4420 items_sql = self.expressions(expression, flat=True)
4421 keyword = "UNSET" if expression.args.get("unset") else "SET"
4422 return f"{keyword} {items_sql}"
4423
4424 def add_column_sql(self, expression: exp.Expr) -> str:
4425 sql = self.sql(expression)
4426 if isinstance(expression, exp.Schema):
4427 column_text = " COLUMNS"
4428 elif isinstance(expression, exp.ColumnDef) and self.ALTER_TABLE_INCLUDE_COLUMN_KEYWORD:
4429 column_text = " COLUMN"
4430 else:
4431 column_text = ""
4432
4433 return f"ADD{column_text} {sql}"
4434
4435 def droppartition_sql(self, expression: exp.DropPartition) -> str:
4436 expressions = self.expressions(expression)
4437 exists = " IF EXISTS " if expression.args.get("exists") else " "
4438 return f"DROP{exists}{expressions}"
4439
4440 def dropprimarykey_sql(self, expression: exp.DropPrimaryKey) -> str:
4441 return "DROP PRIMARY KEY"
4442
4443 def addconstraint_sql(self, expression: exp.AddConstraint) -> str:
4444 return f"ADD {self.expressions(expression, indent=False)}"
4445
4446 def addpartition_sql(self, expression: exp.AddPartition) -> str:
4447 exists = "IF NOT EXISTS " if expression.args.get("exists") else ""
4448 location = self.sql(expression, "location")
4449 location = f" {location}" if location else ""
4450 return f"ADD {exists}{self.sql(expression.this)}{location}"
4451
4452 def distinct_sql(self, expression: exp.Distinct) -> str:
4453 this = self.expressions(expression, flat=True)
4454
4455 if not self.MULTI_ARG_DISTINCT and len(expression.expressions) > 1:
4456 case = exp.case()
4457 for arg in expression.expressions:
4458 case = case.when(arg.is_(exp.null()), exp.null())
4459 this = self.sql(case.else_(f"({this})"))
4460
4461 this = f" {this}" if this else ""
4462
4463 on = self.sql(expression, "on")
4464 on = f" ON {on}" if on else ""
4465 return f"DISTINCT{this}{on}"
4466
4467 def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
4468 return self._embed_ignore_nulls(expression, "IGNORE NULLS")
4469
4470 def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
4471 return self._embed_ignore_nulls(expression, "RESPECT NULLS")
4472
4473 def havingmax_sql(self, expression: exp.HavingMax) -> str:
4474 this_sql = self.sql(expression, "this")
4475 expression_sql = self.sql(expression, "expression")
4476 kind = "MAX" if expression.args.get("max") else "MIN"
4477 return f"{this_sql} HAVING {kind} {expression_sql}"
4478
4479 def intdiv_sql(self, expression: exp.IntDiv) -> str:
4480 return self.sql(
4481 exp.Cast(
4482 this=exp.Div(this=expression.this, expression=expression.expression),
4483 to=exp.DataType(this=exp.DType.INT),
4484 )
4485 )
4486
4487 def dpipe_sql(self, expression: exp.DPipe) -> str:
4488 if self.dialect.STRICT_STRING_CONCAT and expression.args.get("safe"):
4489 return self.func("CONCAT", *(exp.cast(e, exp.DType.TEXT) for e in expression.flatten()))
4490 return self.binary(expression, "||")
4491
4492 def div_sql(self, expression: exp.Div) -> str:
4493 l, r = expression.left, expression.right
4494
4495 if not self.dialect.SAFE_DIVISION and expression.args.get("safe"):
4496 r.replace(exp.Nullif(this=r.copy(), expression=exp.Literal.number(0)))
4497
4498 if self.dialect.TYPED_DIVISION and not expression.args.get("typed"):
4499 if not l.is_type(*exp.DataType.REAL_TYPES) and not r.is_type(*exp.DataType.REAL_TYPES):
4500 l.replace(exp.cast(l.copy(), to=exp.DType.DOUBLE))
4501
4502 elif not self.dialect.TYPED_DIVISION and expression.args.get("typed"):
4503 if l.is_type(*exp.DataType.INTEGER_TYPES) and r.is_type(*exp.DataType.INTEGER_TYPES):
4504 return self.sql(
4505 exp.cast(
4506 l / r,
4507 to=exp.DType.BIGINT,
4508 )
4509 )
4510
4511 return self.binary(expression, "/")
4512
4513 def safedivide_sql(self, expression: exp.SafeDivide) -> str:
4514 n = exp._wrap(expression.this, exp.Binary)
4515 d = exp._wrap(expression.expression, exp.Binary)
4516 return self.sql(exp.If(this=d.neq(0), true=n / d, false=exp.Null()))
4517
4518 def overlaps_sql(self, expression: exp.Overlaps) -> str:
4519 return self.binary(expression, "OVERLAPS")
4520
4521 def distance_sql(self, expression: exp.Distance) -> str:
4522 return self.binary(expression, "<->")
4523
4524 def distancend_sql(self, expression: exp.DistanceNd) -> str:
4525 return self.binary(expression, "<<->>")
4526
4527 def dot_sql(self, expression: exp.Dot) -> str:
4528 return f"{self.sql(expression, 'this')}.{self.sql(expression, 'expression')}"
4529
4530 def eq_sql(self, expression: exp.EQ) -> str:
4531 return self.binary(expression, "=")
4532
4533 def propertyeq_sql(self, expression: exp.PropertyEQ) -> str:
4534 return self.binary(expression, ":=")
4535
4536 def escape_sql(self, expression: exp.Escape) -> str:
4537 this = expression.this
4538 if (
4539 isinstance(this, (exp.Like, exp.ILike))
4540 and isinstance(this.expression, (exp.All, exp.Any))
4541 and not self.SUPPORTS_LIKE_QUANTIFIERS
4542 ):
4543 return self._like_sql(this, escape=expression)
4544 return self.binary(expression, "ESCAPE")
4545
4546 def glob_sql(self, expression: exp.Glob) -> str:
4547 return self.binary(expression, "GLOB")
4548
4549 def gt_sql(self, expression: exp.GT) -> str:
4550 return self.binary(expression, ">")
4551
4552 def gte_sql(self, expression: exp.GTE) -> str:
4553 return self.binary(expression, ">=")
4554
4555 def is_sql(self, expression: exp.Is) -> str:
4556 negate = expression.args.get("negate")
4557 if not self.IS_BOOL_ALLOWED and isinstance(expression.expression, exp.Boolean):
4558 positive = bool(expression.expression.this) != bool(negate)
4559 return self.sql(expression.this if positive else exp.not_(expression.this))
4560 return self.binary(expression, "IS NOT" if negate else "IS")
4561
4562 def _like_sql(
4563 self,
4564 expression: exp.Like | exp.ILike,
4565 escape: exp.Escape | None = None,
4566 ) -> str:
4567 this = expression.this
4568 rhs = expression.expression
4569
4570 if isinstance(expression, exp.Like):
4571 exp_class: type[exp.Like | exp.ILike] = exp.Like
4572 op = "LIKE"
4573 else:
4574 exp_class = exp.ILike
4575 op = "ILIKE"
4576
4577 if expression.args.get("negate"):
4578 op = f"NOT {op}"
4579
4580 if isinstance(rhs, (exp.All, exp.Any)) and not self.SUPPORTS_LIKE_QUANTIFIERS:
4581 exprs = rhs.this.unnest()
4582
4583 if isinstance(exprs, exp.Tuple):
4584 exprs = exprs.expressions
4585 else:
4586 exprs = [exprs]
4587
4588 connective = exp.or_ if isinstance(rhs, exp.Any) else exp.and_
4589
4590 def _make_like(expr: exp.Expression) -> exp.Expression:
4591 like: exp.Expression = exp_class(
4592 this=this, expression=expr, negate=expression.args.get("negate")
4593 )
4594 if escape:
4595 like = exp.Escape(this=like, expression=escape.expression.copy())
4596 return like
4597
4598 like_expr: exp.Expr = _make_like(exprs[0])
4599 for expr in exprs[1:]:
4600 like_expr = connective(like_expr, _make_like(expr), copy=False)
4601
4602 parent = escape.parent if escape else expression.parent
4603 if not isinstance(parent, (type(like_expr), exp.Paren)) and isinstance(
4604 parent, exp.Condition
4605 ):
4606 like_expr = exp.paren(like_expr, copy=False)
4607
4608 return self.sql(like_expr)
4609
4610 return self.binary(expression, op)
4611
4612 def like_sql(self, expression: exp.Like) -> str:
4613 return self._like_sql(expression)
4614
4615 def ilike_sql(self, expression: exp.ILike) -> str:
4616 return self._like_sql(expression)
4617
4618 def match_sql(self, expression: exp.Match) -> str:
4619 return self.binary(expression, "MATCH")
4620
4621 def similarto_sql(self, expression: exp.SimilarTo) -> str:
4622 return self.binary(expression, "SIMILAR TO")
4623
4624 def lt_sql(self, expression: exp.LT) -> str:
4625 return self.binary(expression, "<")
4626
4627 def lte_sql(self, expression: exp.LTE) -> str:
4628 return self.binary(expression, "<=")
4629
4630 def mod_sql(self, expression: exp.Mod) -> str:
4631 this = self.sql(expression, "this")
4632 expr = self.sql(expression, "expression")
4633 sql = f"{this} {self.maybe_comment(self.MOD_OPERATOR, comments=expression.comments)} {expr}"
4634
4635 parent = expression.parent
4636 if isinstance(parent, self.MOD_PAREN_PARENT_TYPES) and parent.expression is expression:
4637 return f"({sql})"
4638
4639 return sql
4640
4641 def mul_sql(self, expression: exp.Mul) -> str:
4642 return self.binary(expression, "*")
4643
4644 def neq_sql(self, expression: exp.NEQ) -> str:
4645 return self.binary(expression, "<>")
4646
4647 def nullsafeeq_sql(self, expression: exp.NullSafeEQ) -> str:
4648 return self.binary(expression, "IS NOT DISTINCT FROM")
4649
4650 def nullsafeneq_sql(self, expression: exp.NullSafeNEQ) -> str:
4651 return self.binary(expression, "IS DISTINCT FROM")
4652
4653 def sub_sql(self, expression: exp.Sub) -> str:
4654 return self.binary(expression, "-")
4655
4656 def trycast_sql(self, expression: exp.TryCast) -> str:
4657 return self.cast_sql(expression, safe_prefix="TRY_")
4658
4659 def jsoncast_sql(self, expression: exp.JSONCast) -> str:
4660 return self.cast_sql(expression)
4661
4662 def try_sql(self, expression: exp.Try) -> str:
4663 if not self.TRY_SUPPORTED:
4664 self.unsupported("Unsupported TRY function")
4665 return self.sql(expression, "this")
4666
4667 return self.func("TRY", expression.this)
4668
4669 def log_sql(self, expression: exp.Log) -> str:
4670 this = expression.this
4671 expr = expression.expression
4672
4673 if self.dialect.LOG_BASE_FIRST is False:
4674 this, expr = expr, this
4675 elif self.dialect.LOG_BASE_FIRST is None and expr:
4676 if this.name in ("2", "10"):
4677 return self.func(f"LOG{this.name}", expr)
4678
4679 self.unsupported(f"Unsupported logarithm with base {self.sql(this)}")
4680
4681 return self.func("LOG", this, expr)
4682
4683 def use_sql(self, expression: exp.Use) -> str:
4684 kind = self.sql(expression, "kind")
4685 kind = f" {kind}" if kind else ""
4686 this = self.sql(expression, "this") or self.expressions(expression, flat=True)
4687 this = f" {this}" if this else ""
4688 return f"USE{kind}{this}"
4689
4690 def binary(self, expression: exp.Binary, op: str) -> str:
4691 sqls: list[str] = []
4692 stack: list[None | str | exp.Expr] = [expression]
4693 binary_type = type(expression)
4694
4695 while stack:
4696 node = stack.pop()
4697
4698 if type(node) is binary_type:
4699 op_func = node.args.get("operator")
4700 if op_func:
4701 op = f"OPERATOR({self.sql(op_func)})"
4702
4703 stack.append(node.args.get("expression"))
4704 stack.append(f" {self.maybe_comment(op, comments=node.comments)} ")
4705 stack.append(node.args.get("this"))
4706 else:
4707 sqls.append(self.sql(node))
4708
4709 return "".join(sqls)
4710
4711 def ceil_floor(self, expression: exp.Ceil | exp.Floor) -> str:
4712 to_clause = self.sql(expression, "to")
4713 if to_clause:
4714 return f"{expression.sql_name()}({self.sql(expression, 'this')} TO {to_clause})"
4715
4716 return self.function_fallback_sql(expression)
4717
4718 def function_fallback_sql(self, expression: exp.Func) -> str:
4719 args = []
4720
4721 for key in expression.arg_types:
4722 arg_value = expression.args.get(key)
4723
4724 if isinstance(arg_value, list):
4725 for value in arg_value:
4726 args.append(value)
4727 elif arg_value is not None:
4728 args.append(arg_value)
4729
4730 if self.dialect.PRESERVE_ORIGINAL_NAMES:
4731 name = expression.meta_get("name") or expression.sql_name()
4732 else:
4733 name = expression.sql_name()
4734
4735 return self.func(name, *args)
4736
4737 def func(
4738 self,
4739 name: str,
4740 *args: t.Any,
4741 prefix: str = "(",
4742 suffix: str = ")",
4743 normalize: bool = True,
4744 ) -> str:
4745 name = self.normalize_func(name) if normalize else name
4746 return f"{name}{prefix}{self.format_args(*args)}{suffix}"
4747
4748 def format_args(self, *args: t.Any, sep: str = ", ") -> str:
4749 arg_sqls = tuple(
4750 self.sql(arg) for arg in args if arg is not None and not isinstance(arg, bool)
4751 )
4752 if self.pretty and self.too_wide(arg_sqls):
4753 return self.indent(
4754 "\n" + f"{sep.strip()}\n".join(arg_sqls) + "\n", skip_first=True, skip_last=True
4755 )
4756 return sep.join(arg_sqls)
4757
4758 def too_wide(self, args: t.Iterable) -> bool:
4759 return sum(len(arg) for arg in args) > self.max_text_width
4760
4761 def format_time(
4762 self,
4763 expression: exp.Expr,
4764 inverse_time_mapping: dict[str, str] | None = None,
4765 inverse_time_trie: dict | None = None,
4766 ) -> str | None:
4767 return format_time(
4768 self.sql(expression, "format"),
4769 inverse_time_mapping or self.dialect.INVERSE_TIME_MAPPING,
4770 inverse_time_trie or self.dialect.INVERSE_TIME_TRIE,
4771 )
4772
4773 def expressions(
4774 self,
4775 expression: exp.Expr | None = None,
4776 key: str | None = None,
4777 sqls: t.Collection[str | exp.Expr] | None = None,
4778 flat: bool = False,
4779 indent: bool = True,
4780 skip_first: bool = False,
4781 skip_last: bool = False,
4782 sep: str = ", ",
4783 prefix: str = "",
4784 dynamic: bool = False,
4785 new_line: bool = False,
4786 ) -> str:
4787 expressions = expression.args.get(key or "expressions") if expression else sqls
4788
4789 if not expressions:
4790 return ""
4791
4792 if flat:
4793 return sep.join(sql for sql in (self.sql(e) for e in expressions) if sql)
4794
4795 num_sqls = len(expressions)
4796 result_sqls = []
4797
4798 for i, e in enumerate(expressions):
4799 sql = self.sql(e, comment=False)
4800 if not sql:
4801 continue
4802
4803 comments = self.maybe_comment("", e) if isinstance(e, exp.Expr) else ""
4804
4805 if self.pretty:
4806 if self.leading_comma:
4807 result_sqls.append(f"{sep if i > 0 else ''}{prefix}{sql}{comments}")
4808 else:
4809 result_sqls.append(
4810 f"{prefix}{sql}{(sep.rstrip() if comments else sep) if i + 1 < num_sqls else ''}{comments}"
4811 )
4812 else:
4813 result_sqls.append(f"{prefix}{sql}{comments}{sep if i + 1 < num_sqls else ''}")
4814
4815 if self.pretty and (not dynamic or self.too_wide(result_sqls)):
4816 if new_line:
4817 result_sqls.insert(0, "")
4818 result_sqls.append("")
4819 result_sql = "\n".join(s.rstrip() for s in result_sqls)
4820 else:
4821 result_sql = "".join(result_sqls)
4822
4823 return (
4824 self.indent(result_sql, skip_first=skip_first, skip_last=skip_last)
4825 if indent
4826 else result_sql
4827 )
4828
4829 def op_expressions(self, op: str, expression: exp.Expr, flat: bool = False) -> str:
4830 flat = flat or isinstance(expression.parent, exp.Properties)
4831 expressions_sql = self.expressions(expression, flat=flat)
4832 if flat:
4833 return f"{op} {expressions_sql}"
4834 return f"{self.seg(op)}{self.sep() if expressions_sql else ''}{expressions_sql}"
4835
4836 def naked_property(self, expression: exp.Property) -> str:
4837 property_name = exp.Properties.PROPERTY_TO_NAME.get(expression.__class__)
4838 if not property_name:
4839 self.unsupported(f"Unsupported property {expression.__class__.__name__}")
4840 return f"{property_name} {self.sql(expression, 'this')}"
4841
4842 def tag_sql(self, expression: exp.Tag) -> str:
4843 return f"{expression.args.get('prefix')}{self.sql(expression.this)}{expression.args.get('postfix')}"
4844
4845 def token_sql(self, token_type: TokenType) -> str:
4846 return self.TOKEN_MAPPING.get(token_type, token_type.name)
4847
4848 def userdefinedfunction_sql(self, expression: exp.UserDefinedFunction) -> str:
4849 this = self.sql(expression, "this")
4850 expressions = self.no_identify(self.expressions, expression)
4851 expressions = (
4852 self.wrap(expressions) if expression.args.get("wrapped") else f" {expressions}"
4853 )
4854 return f"{this}{expressions}" if expressions.strip() != "" else this
4855
4856 def macrooverloads_sql(self, expression: exp.MacroOverloads) -> str:
4857 return self.expressions(expression, flat=True)
4858
4859 def macrooverload_sql(self, expression: exp.MacroOverload) -> str:
4860 params = self.no_identify(self.expressions, expression, flat=True)
4861 body = self.sql(expression, "this")
4862 prefix = "TABLE " if expression.args.get("is_table") else ""
4863 return f"({params}) AS {prefix}{body}"
4864
4865 def joinhint_sql(self, expression: exp.JoinHint) -> str:
4866 this = self.sql(expression, "this")
4867 expressions = self.expressions(expression, flat=True)
4868 return f"{this}({expressions})"
4869
4870 def kwarg_sql(self, expression: exp.Kwarg) -> str:
4871 return self.binary(expression, "=>")
4872
4873 def when_sql(self, expression: exp.When) -> str:
4874 matched = "MATCHED" if expression.args["matched"] else "NOT MATCHED"
4875 source = " BY SOURCE" if self.MATCHED_BY_SOURCE and expression.args.get("source") else ""
4876 condition = self.sql(expression, "condition")
4877 condition = f" AND {condition}" if condition else ""
4878
4879 then_expression = expression.args.get("then")
4880 if isinstance(then_expression, exp.Insert):
4881 this = self.sql(then_expression, "this")
4882 this = f"INSERT {this}" if this else "INSERT"
4883 then = self.sql(then_expression, "expression")
4884 then = f"{this} VALUES {then}" if then else this
4885 elif isinstance(then_expression, exp.Update):
4886 if isinstance(then_expression.args.get("expressions"), exp.Star):
4887 then = f"UPDATE {self.sql(then_expression, 'expressions')}"
4888 else:
4889 expressions_sql = self.expressions(then_expression)
4890 then = f"UPDATE SET{self.sep()}{expressions_sql}" if expressions_sql else "UPDATE"
4891 else:
4892 then = self.sql(then_expression)
4893
4894 if isinstance(then_expression, (exp.Insert, exp.Update)):
4895 where = self.sql(then_expression, "where")
4896 if where and not self.SUPPORTS_MERGE_WHERE:
4897 kind = "INSERT" if isinstance(then_expression, exp.Insert) else "UPDATE"
4898 self.unsupported(f"WHERE clause in MERGE {kind} is not supported")
4899 where = ""
4900 then = f"{then}{where}"
4901 return f"WHEN {matched}{source}{condition} THEN {then}"
4902
4903 def whens_sql(self, expression: exp.Whens) -> str:
4904 return self.expressions(expression, sep=" ", indent=False)
4905
4906 def merge_sql(self, expression: exp.Merge) -> str:
4907 table = expression.this
4908 table_alias = ""
4909
4910 hints = table.args.get("hints")
4911 if hints and table.alias and isinstance(hints[0], exp.WithTableHint):
4912 # T-SQL syntax is MERGE ... <target_table> [WITH (<merge_hint>)] [[AS] table_alias]
4913 table_alias = f" AS {self.sql(table.args['alias'].pop())}"
4914
4915 this = self.sql(table)
4916 using = f"USING {self.sql(expression, 'using')}"
4917 whens = self.sql(expression, "whens")
4918
4919 on = self.sql(expression, "on")
4920 on = f"ON {on}" if on else ""
4921
4922 if not on:
4923 on = self.expressions(expression, key="using_cond")
4924 on = f"USING ({on})" if on else ""
4925
4926 returning = self.sql(expression, "returning")
4927 if returning:
4928 whens = f"{whens}{returning}"
4929
4930 sep = self.sep()
4931
4932 return self.prepend_ctes(
4933 expression,
4934 f"MERGE INTO {this}{table_alias}{sep}{using}{sep}{on}{sep}{whens}",
4935 )
4936
4937 @unsupported_args("format")
4938 def tochar_sql(self, expression: exp.ToChar) -> str:
4939 return self.sql(exp.cast(expression.this, exp.DType.TEXT))
4940
4941 @unsupported_args("default")
4942 def tonumber_sql(self, expression: exp.ToNumber) -> str:
4943 if not self.SUPPORTS_TO_NUMBER:
4944 self.unsupported("Unsupported TO_NUMBER function")
4945 return self.sql(exp.cast(expression.this, exp.DType.DOUBLE))
4946
4947 fmt = expression.args.get("format")
4948 if not fmt:
4949 self.unsupported("Conversion format is required for TO_NUMBER")
4950 return self.sql(exp.cast(expression.this, exp.DType.DOUBLE))
4951
4952 return self.func("TO_NUMBER", expression.this, fmt)
4953
4954 def dictproperty_sql(self, expression: exp.DictProperty) -> str:
4955 this = self.sql(expression, "this")
4956 kind = self.sql(expression, "kind")
4957 settings_sql = self.expressions(expression, key="settings", sep=" ")
4958 args = f"({self.sep('')}{settings_sql}{self.seg(')', sep='')}" if settings_sql else "()"
4959 return f"{this}({kind}{args})"
4960
4961 def dictrange_sql(self, expression: exp.DictRange) -> str:
4962 this = self.sql(expression, "this")
4963 max = self.sql(expression, "max")
4964 min = self.sql(expression, "min")
4965 return f"{this}(MIN {min} MAX {max})"
4966
4967 def dictsubproperty_sql(self, expression: exp.DictSubProperty) -> str:
4968 return f"{self.sql(expression, 'this')} {self.sql(expression, 'value')}"
4969
4970 def duplicatekeyproperty_sql(self, expression: exp.DuplicateKeyProperty) -> str:
4971 return f"DUPLICATE KEY ({self.expressions(expression, flat=True)})"
4972
4973 # https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE/
4974 def uniquekeyproperty_sql(
4975 self, expression: exp.UniqueKeyProperty, prefix: str = "UNIQUE KEY"
4976 ) -> str:
4977 return f"{prefix} ({self.expressions(expression, flat=True)})"
4978
4979 # https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc
4980 def distributedbyproperty_sql(self, expression: exp.DistributedByProperty) -> str:
4981 expressions = self.expressions(expression, flat=True)
4982 expressions = f" {self.wrap(expressions)}" if expressions else ""
4983 buckets = self.sql(expression, "buckets")
4984 kind = self.sql(expression, "kind")
4985 buckets = f" BUCKETS {buckets}" if buckets else ""
4986 order = self.sql(expression, "order")
4987 return f"DISTRIBUTED BY {kind}{expressions}{buckets}{order}"
4988
4989 def oncluster_sql(self, expression: exp.OnCluster) -> str:
4990 return ""
4991
4992 def clusteredbyproperty_sql(self, expression: exp.ClusteredByProperty) -> str:
4993 expressions = self.expressions(expression, key="expressions", flat=True)
4994 sorted_by = self.expressions(expression, key="sorted_by", flat=True)
4995 sorted_by = f" SORTED BY ({sorted_by})" if sorted_by else ""
4996 buckets = self.sql(expression, "buckets")
4997 return f"CLUSTERED BY ({expressions}){sorted_by} INTO {buckets} BUCKETS"
4998
4999 def anyvalue_sql(self, expression: exp.AnyValue) -> str:
5000 this = self.sql(expression, "this")
5001 having = self.sql(expression, "having")
5002
5003 if having:
5004 this = f"{this} HAVING {'MAX' if expression.args.get('max') else 'MIN'} {having}"
5005
5006 return self.func("ANY_VALUE", this)
5007
5008 def querytransform_sql(self, expression: exp.QueryTransform) -> str:
5009 transform = self.func("TRANSFORM", *expression.expressions)
5010 row_format_before = self.sql(expression, "row_format_before")
5011 row_format_before = f" {row_format_before}" if row_format_before else ""
5012 record_writer = self.sql(expression, "record_writer")
5013 record_writer = f" RECORDWRITER {record_writer}" if record_writer else ""
5014 using = f" USING {self.sql(expression, 'command_script')}"
5015 schema = self.sql(expression, "schema")
5016 schema = f" AS {schema}" if schema else ""
5017 row_format_after = self.sql(expression, "row_format_after")
5018 row_format_after = f" {row_format_after}" if row_format_after else ""
5019 record_reader = self.sql(expression, "record_reader")
5020 record_reader = f" RECORDREADER {record_reader}" if record_reader else ""
5021 return f"{transform}{row_format_before}{record_writer}{using}{schema}{row_format_after}{record_reader}"
5022
5023 def indexconstraintoption_sql(self, expression: exp.IndexConstraintOption) -> str:
5024 key_block_size = self.sql(expression, "key_block_size")
5025 if key_block_size:
5026 return f"KEY_BLOCK_SIZE = {key_block_size}"
5027
5028 using = self.sql(expression, "using")
5029 if using:
5030 return f"USING {using}"
5031
5032 parser = self.sql(expression, "parser")
5033 if parser:
5034 return f"WITH PARSER {parser}"
5035
5036 comment = self.sql(expression, "comment")
5037 if comment:
5038 return f"COMMENT {comment}"
5039
5040 visible = expression.args.get("visible")
5041 if visible is not None:
5042 return "VISIBLE" if visible else "INVISIBLE"
5043
5044 engine_attr = self.sql(expression, "engine_attr")
5045 if engine_attr:
5046 return f"ENGINE_ATTRIBUTE = {engine_attr}"
5047
5048 secondary_engine_attr = self.sql(expression, "secondary_engine_attr")
5049 if secondary_engine_attr:
5050 return f"SECONDARY_ENGINE_ATTRIBUTE = {secondary_engine_attr}"
5051
5052 self.unsupported("Unsupported index constraint option.")
5053 return ""
5054
5055 def checkcolumnconstraint_sql(self, expression: exp.CheckColumnConstraint) -> str:
5056 enforced = " ENFORCED" if expression.args.get("enforced") else ""
5057 return f"CHECK ({self.sql(expression, 'this')}){enforced}"
5058
5059 def indexcolumnconstraint_sql(self, expression: exp.IndexColumnConstraint) -> str:
5060 kind = self.sql(expression, "kind")
5061 kind = f"{kind} INDEX" if kind else "INDEX"
5062 this = self.sql(expression, "this")
5063 this = f" {this}" if this else ""
5064 index_type = self.sql(expression, "index_type")
5065 index_type = f" USING {index_type}" if index_type else ""
5066 expressions = self.expressions(expression, flat=True)
5067 expressions = f" ({expressions})" if expressions else ""
5068 options = self.expressions(expression, key="options", sep=" ")
5069 options = f" {options}" if options else ""
5070 return f"{kind}{this}{index_type}{expressions}{options}"
5071
5072 def nvl2_sql(self, expression: exp.Nvl2) -> str:
5073 if self.NVL2_SUPPORTED:
5074 return self.function_fallback_sql(expression)
5075
5076 case = exp.Case().when(
5077 expression.this.is_(exp.null()).not_(copy=False),
5078 expression.args["true"],
5079 copy=False,
5080 )
5081 else_cond = expression.args.get("false")
5082 if else_cond:
5083 case.else_(else_cond, copy=False)
5084
5085 return self.sql(case)
5086
5087 def nthvalue_sql(self, expression: exp.NthValue) -> str:
5088 if expression.args.get("from_first") is False:
5089 self.unsupported("NTH_VALUE FROM LAST is not supported")
5090
5091 return self.function_fallback_sql(expression)
5092
5093 def comprehension_sql(self, expression: exp.Comprehension) -> str:
5094 this = self.sql(expression, "this")
5095 expr = self.sql(expression, "expression")
5096 position = self.sql(expression, "position")
5097 position = f", {position}" if position else ""
5098 iterator = self.sql(expression, "iterator")
5099 condition = self.sql(expression, "condition")
5100 condition = f" IF {condition}" if condition else ""
5101 return f"{this} FOR {expr}{position} IN {iterator}{condition}"
5102
5103 def columnprefix_sql(self, expression: exp.ColumnPrefix) -> str:
5104 return f"{self.sql(expression, 'this')}({self.sql(expression, 'expression')})"
5105
5106 def opclass_sql(self, expression: exp.Opclass) -> str:
5107 return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}"
5108
5109 def _ml_sql(self, expression: exp.Func, name: str) -> str:
5110 model = self.sql(expression, "this")
5111 model = f"MODEL {model}"
5112 expr = expression.expression
5113 if expr:
5114 expr_sql = self.sql(expression, "expression")
5115 expr_sql = f"TABLE {expr_sql}" if isinstance(expr, exp.Table) else expr_sql
5116 else:
5117 expr_sql = None
5118
5119 parameters = self.sql(expression, "params_struct") or None
5120
5121 return self.func(name, model, expr_sql, parameters)
5122
5123 def predict_sql(self, expression: exp.Predict) -> str:
5124 return self._ml_sql(expression, "PREDICT")
5125
5126 def generateembedding_sql(self, expression: exp.GenerateEmbedding) -> str:
5127 name = "GENERATE_TEXT_EMBEDDING" if expression.args.get("is_text") else "GENERATE_EMBEDDING"
5128 return self._ml_sql(expression, name)
5129
5130 def generatetext_sql(self, expression: exp.GenerateText) -> str:
5131 return self._ml_sql(expression, "GENERATE_TEXT")
5132
5133 def generatetable_sql(self, expression: exp.GenerateTable) -> str:
5134 return self._ml_sql(expression, "GENERATE_TABLE")
5135
5136 def generatebool_sql(self, expression: exp.GenerateBool) -> str:
5137 return self._ml_sql(expression, "GENERATE_BOOL")
5138
5139 def generateint_sql(self, expression: exp.GenerateInt) -> str:
5140 return self._ml_sql(expression, "GENERATE_INT")
5141
5142 def generatedouble_sql(self, expression: exp.GenerateDouble) -> str:
5143 return self._ml_sql(expression, "GENERATE_DOUBLE")
5144
5145 def mltranslate_sql(self, expression: exp.MLTranslate) -> str:
5146 return self._ml_sql(expression, "TRANSLATE")
5147
5148 def mlforecast_sql(self, expression: exp.MLForecast) -> str:
5149 return self._ml_sql(expression, "FORECAST")
5150
5151 def aiforecast_sql(self, expression: exp.AIForecast) -> str:
5152 this_sql = self.sql(expression, "this")
5153 if isinstance(expression.this, exp.Table):
5154 this_sql = f"TABLE {this_sql}"
5155
5156 return self.func(
5157 "FORECAST",
5158 this_sql,
5159 expression.args.get("data_col"),
5160 expression.args.get("timestamp_col"),
5161 expression.args.get("model"),
5162 expression.args.get("id_cols"),
5163 expression.args.get("horizon"),
5164 expression.args.get("forecast_end_timestamp"),
5165 expression.args.get("confidence_level"),
5166 expression.args.get("output_historical_time_series"),
5167 expression.args.get("context_window"),
5168 )
5169