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