sqlglot.generator

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