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 }