sqlglot.dialects.mysql

   1from __future__ import annotations
   2
   3import typing as t
   4
   5from sqlglot import exp, generator, parser, tokens, transforms
   6from sqlglot.dialects.dialect import (
   7    Dialect,
   8    NormalizationStrategy,
   9    arrow_json_extract_sql,
  10    date_add_interval_sql,
  11    datestrtodate_sql,
  12    build_formatted_time,
  13    isnull_to_is_null,
  14    locate_to_strposition,
  15    max_or_greatest,
  16    min_or_least,
  17    no_ilike_sql,
  18    no_paren_current_date_sql,
  19    no_pivot_sql,
  20    no_tablesample_sql,
  21    no_trycast_sql,
  22    build_date_delta,
  23    build_date_delta_with_interval,
  24    rename_func,
  25    strposition_to_locate_sql,
  26    unit_to_var,
  27)
  28from sqlglot.helper import seq_get
  29from sqlglot.tokens import TokenType
  30
  31
  32def _show_parser(*args: t.Any, **kwargs: t.Any) -> t.Callable[[MySQL.Parser], exp.Show]:
  33    def _parse(self: MySQL.Parser) -> exp.Show:
  34        return self._parse_show_mysql(*args, **kwargs)
  35
  36    return _parse
  37
  38
  39def _date_trunc_sql(self: MySQL.Generator, expression: exp.DateTrunc) -> str:
  40    expr = self.sql(expression, "this")
  41    unit = expression.text("unit").upper()
  42
  43    if unit == "WEEK":
  44        concat = f"CONCAT(YEAR({expr}), ' ', WEEK({expr}, 1), ' 1')"
  45        date_format = "%Y %u %w"
  46    elif unit == "MONTH":
  47        concat = f"CONCAT(YEAR({expr}), ' ', MONTH({expr}), ' 1')"
  48        date_format = "%Y %c %e"
  49    elif unit == "QUARTER":
  50        concat = f"CONCAT(YEAR({expr}), ' ', QUARTER({expr}) * 3 - 2, ' 1')"
  51        date_format = "%Y %c %e"
  52    elif unit == "YEAR":
  53        concat = f"CONCAT(YEAR({expr}), ' 1 1')"
  54        date_format = "%Y %c %e"
  55    else:
  56        if unit != "DAY":
  57            self.unsupported(f"Unexpected interval unit: {unit}")
  58        return self.func("DATE", expr)
  59
  60    return self.func("STR_TO_DATE", concat, f"'{date_format}'")
  61
  62
  63# All specifiers for time parts (as opposed to date parts)
  64# https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
  65TIME_SPECIFIERS = {"f", "H", "h", "I", "i", "k", "l", "p", "r", "S", "s", "T"}
  66
  67
  68def _has_time_specifier(date_format: str) -> bool:
  69    i = 0
  70    length = len(date_format)
  71
  72    while i < length:
  73        if date_format[i] == "%":
  74            i += 1
  75            if i < length and date_format[i] in TIME_SPECIFIERS:
  76                return True
  77        i += 1
  78    return False
  79
  80
  81def _str_to_date(args: t.List) -> exp.StrToDate | exp.StrToTime:
  82    mysql_date_format = seq_get(args, 1)
  83    date_format = MySQL.format_time(mysql_date_format)
  84    this = seq_get(args, 0)
  85
  86    if mysql_date_format and _has_time_specifier(mysql_date_format.name):
  87        return exp.StrToTime(this=this, format=date_format)
  88
  89    return exp.StrToDate(this=this, format=date_format)
  90
  91
  92def _str_to_date_sql(
  93    self: MySQL.Generator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate
  94) -> str:
  95    return self.func("STR_TO_DATE", expression.this, self.format_time(expression))
  96
  97
  98def _trim_sql(self: MySQL.Generator, expression: exp.Trim) -> str:
  99    target = self.sql(expression, "this")
 100    trim_type = self.sql(expression, "position")
 101    remove_chars = self.sql(expression, "expression")
 102
 103    # Use TRIM/LTRIM/RTRIM syntax if the expression isn't mysql-specific
 104    if not remove_chars:
 105        return self.trim_sql(expression)
 106
 107    trim_type = f"{trim_type} " if trim_type else ""
 108    remove_chars = f"{remove_chars} " if remove_chars else ""
 109    from_part = "FROM " if trim_type or remove_chars else ""
 110    return f"TRIM({trim_type}{remove_chars}{from_part}{target})"
 111
 112
 113def _unix_to_time_sql(self: MySQL.Generator, expression: exp.UnixToTime) -> str:
 114    scale = expression.args.get("scale")
 115    timestamp = expression.this
 116
 117    if scale in (None, exp.UnixToTime.SECONDS):
 118        return self.func("FROM_UNIXTIME", timestamp, self.format_time(expression))
 119
 120    return self.func(
 121        "FROM_UNIXTIME",
 122        exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)),
 123        self.format_time(expression),
 124    )
 125
 126
 127def date_add_sql(
 128    kind: str,
 129) -> t.Callable[[generator.Generator, exp.Expression], str]:
 130    def func(self: generator.Generator, expression: exp.Expression) -> str:
 131        return self.func(
 132            f"DATE_{kind}",
 133            expression.this,
 134            exp.Interval(this=expression.expression, unit=unit_to_var(expression)),
 135        )
 136
 137    return func
 138
 139
 140def _ts_or_ds_to_date_sql(self: MySQL.Generator, expression: exp.TsOrDsToDate) -> str:
 141    time_format = expression.args.get("format")
 142    return _str_to_date_sql(self, expression) if time_format else self.func("DATE", expression.this)
 143
 144
 145def _remove_ts_or_ds_to_date(
 146    to_sql: t.Optional[t.Callable[[MySQL.Generator, exp.Expression], str]] = None,
 147    args: t.Tuple[str, ...] = ("this",),
 148) -> t.Callable[[MySQL.Generator, exp.Func], str]:
 149    def func(self: MySQL.Generator, expression: exp.Func) -> str:
 150        for arg_key in args:
 151            arg = expression.args.get(arg_key)
 152            if isinstance(arg, exp.TsOrDsToDate) and not arg.args.get("format"):
 153                expression.set(arg_key, arg.this)
 154
 155        return to_sql(self, expression) if to_sql else self.function_fallback_sql(expression)
 156
 157    return func
 158
 159
 160class MySQL(Dialect):
 161    # https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
 162    IDENTIFIERS_CAN_START_WITH_DIGIT = True
 163
 164    # We default to treating all identifiers as case-sensitive, since it matches MySQL's
 165    # behavior on Linux systems. For MacOS and Windows systems, one can override this
 166    # setting by specifying `dialect="mysql, normalization_strategy = lowercase"`.
 167    #
 168    # See also https://dev.mysql.com/doc/refman/8.2/en/identifier-case-sensitivity.html
 169    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_SENSITIVE
 170
 171    TIME_FORMAT = "'%Y-%m-%d %T'"
 172    DPIPE_IS_STRING_CONCAT = False
 173    SUPPORTS_USER_DEFINED_TYPES = False
 174    SUPPORTS_SEMI_ANTI_JOIN = False
 175    SAFE_DIVISION = True
 176
 177    # https://prestodb.io/docs/current/functions/datetime.html#mysql-date-functions
 178    TIME_MAPPING = {
 179        "%M": "%B",
 180        "%c": "%-m",
 181        "%e": "%-d",
 182        "%h": "%I",
 183        "%i": "%M",
 184        "%s": "%S",
 185        "%u": "%W",
 186        "%k": "%-H",
 187        "%l": "%-I",
 188        "%T": "%H:%M:%S",
 189        "%W": "%a",
 190    }
 191
 192    class Tokenizer(tokens.Tokenizer):
 193        QUOTES = ["'", '"']
 194        COMMENTS = ["--", "#", ("/*", "*/")]
 195        IDENTIFIERS = ["`"]
 196        STRING_ESCAPES = ["'", '"', "\\"]
 197        BIT_STRINGS = [("b'", "'"), ("B'", "'"), ("0b", "")]
 198        HEX_STRINGS = [("x'", "'"), ("X'", "'"), ("0x", "")]
 199
 200        KEYWORDS = {
 201            **tokens.Tokenizer.KEYWORDS,
 202            "CHARSET": TokenType.CHARACTER_SET,
 203            "FORCE": TokenType.FORCE,
 204            "IGNORE": TokenType.IGNORE,
 205            "KEY": TokenType.KEY,
 206            "LOCK TABLES": TokenType.COMMAND,
 207            "LONGBLOB": TokenType.LONGBLOB,
 208            "LONGTEXT": TokenType.LONGTEXT,
 209            "MEDIUMBLOB": TokenType.MEDIUMBLOB,
 210            "TINYBLOB": TokenType.TINYBLOB,
 211            "TINYTEXT": TokenType.TINYTEXT,
 212            "MEDIUMTEXT": TokenType.MEDIUMTEXT,
 213            "MEDIUMINT": TokenType.MEDIUMINT,
 214            "MEMBER OF": TokenType.MEMBER_OF,
 215            "SEPARATOR": TokenType.SEPARATOR,
 216            "START": TokenType.BEGIN,
 217            "SIGNED": TokenType.BIGINT,
 218            "SIGNED INTEGER": TokenType.BIGINT,
 219            "UNLOCK TABLES": TokenType.COMMAND,
 220            "UNSIGNED": TokenType.UBIGINT,
 221            "UNSIGNED INTEGER": TokenType.UBIGINT,
 222            "YEAR": TokenType.YEAR,
 223            "_ARMSCII8": TokenType.INTRODUCER,
 224            "_ASCII": TokenType.INTRODUCER,
 225            "_BIG5": TokenType.INTRODUCER,
 226            "_BINARY": TokenType.INTRODUCER,
 227            "_CP1250": TokenType.INTRODUCER,
 228            "_CP1251": TokenType.INTRODUCER,
 229            "_CP1256": TokenType.INTRODUCER,
 230            "_CP1257": TokenType.INTRODUCER,
 231            "_CP850": TokenType.INTRODUCER,
 232            "_CP852": TokenType.INTRODUCER,
 233            "_CP866": TokenType.INTRODUCER,
 234            "_CP932": TokenType.INTRODUCER,
 235            "_DEC8": TokenType.INTRODUCER,
 236            "_EUCJPMS": TokenType.INTRODUCER,
 237            "_EUCKR": TokenType.INTRODUCER,
 238            "_GB18030": TokenType.INTRODUCER,
 239            "_GB2312": TokenType.INTRODUCER,
 240            "_GBK": TokenType.INTRODUCER,
 241            "_GEOSTD8": TokenType.INTRODUCER,
 242            "_GREEK": TokenType.INTRODUCER,
 243            "_HEBREW": TokenType.INTRODUCER,
 244            "_HP8": TokenType.INTRODUCER,
 245            "_KEYBCS2": TokenType.INTRODUCER,
 246            "_KOI8R": TokenType.INTRODUCER,
 247            "_KOI8U": TokenType.INTRODUCER,
 248            "_LATIN1": TokenType.INTRODUCER,
 249            "_LATIN2": TokenType.INTRODUCER,
 250            "_LATIN5": TokenType.INTRODUCER,
 251            "_LATIN7": TokenType.INTRODUCER,
 252            "_MACCE": TokenType.INTRODUCER,
 253            "_MACROMAN": TokenType.INTRODUCER,
 254            "_SJIS": TokenType.INTRODUCER,
 255            "_SWE7": TokenType.INTRODUCER,
 256            "_TIS620": TokenType.INTRODUCER,
 257            "_UCS2": TokenType.INTRODUCER,
 258            "_UJIS": TokenType.INTRODUCER,
 259            # https://dev.mysql.com/doc/refman/8.0/en/string-literals.html
 260            "_UTF8": TokenType.INTRODUCER,
 261            "_UTF16": TokenType.INTRODUCER,
 262            "_UTF16LE": TokenType.INTRODUCER,
 263            "_UTF32": TokenType.INTRODUCER,
 264            "_UTF8MB3": TokenType.INTRODUCER,
 265            "_UTF8MB4": TokenType.INTRODUCER,
 266            "@@": TokenType.SESSION_PARAMETER,
 267        }
 268
 269        COMMANDS = {*tokens.Tokenizer.COMMANDS, TokenType.REPLACE} - {TokenType.SHOW}
 270
 271    class Parser(parser.Parser):
 272        FUNC_TOKENS = {
 273            *parser.Parser.FUNC_TOKENS,
 274            TokenType.DATABASE,
 275            TokenType.SCHEMA,
 276            TokenType.VALUES,
 277        }
 278
 279        CONJUNCTION = {
 280            **parser.Parser.CONJUNCTION,
 281            TokenType.DAMP: exp.And,
 282            TokenType.XOR: exp.Xor,
 283        }
 284
 285        DISJUNCTION = {
 286            **parser.Parser.DISJUNCTION,
 287            TokenType.DPIPE: exp.Or,
 288        }
 289
 290        TABLE_ALIAS_TOKENS = (
 291            parser.Parser.TABLE_ALIAS_TOKENS - parser.Parser.TABLE_INDEX_HINT_TOKENS
 292        )
 293
 294        RANGE_PARSERS = {
 295            **parser.Parser.RANGE_PARSERS,
 296            TokenType.MEMBER_OF: lambda self, this: self.expression(
 297                exp.JSONArrayContains,
 298                this=this,
 299                expression=self._parse_wrapped(self._parse_expression),
 300            ),
 301        }
 302
 303        FUNCTIONS = {
 304            **parser.Parser.FUNCTIONS,
 305            "DATE": lambda args: exp.TsOrDsToDate(this=seq_get(args, 0)),
 306            "DATE_ADD": build_date_delta_with_interval(exp.DateAdd),
 307            "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "mysql"),
 308            "DATE_SUB": build_date_delta_with_interval(exp.DateSub),
 309            "DAY": lambda args: exp.Day(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 310            "DAYOFMONTH": lambda args: exp.DayOfMonth(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 311            "DAYOFWEEK": lambda args: exp.DayOfWeek(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 312            "DAYOFYEAR": lambda args: exp.DayOfYear(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 313            "INSTR": lambda args: exp.StrPosition(substr=seq_get(args, 1), this=seq_get(args, 0)),
 314            "FROM_UNIXTIME": build_formatted_time(exp.UnixToTime, "mysql"),
 315            "ISNULL": isnull_to_is_null,
 316            "LOCATE": locate_to_strposition,
 317            "MAKETIME": exp.TimeFromParts.from_arg_list,
 318            "MONTH": lambda args: exp.Month(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 319            "MONTHNAME": lambda args: exp.TimeToStr(
 320                this=exp.TsOrDsToDate(this=seq_get(args, 0)),
 321                format=exp.Literal.string("%B"),
 322            ),
 323            "STR_TO_DATE": _str_to_date,
 324            "TIMESTAMPDIFF": build_date_delta(exp.TimestampDiff),
 325            "TO_DAYS": lambda args: exp.paren(
 326                exp.DateDiff(
 327                    this=exp.TsOrDsToDate(this=seq_get(args, 0)),
 328                    expression=exp.TsOrDsToDate(this=exp.Literal.string("0000-01-01")),
 329                    unit=exp.var("DAY"),
 330                )
 331                + 1
 332            ),
 333            "WEEK": lambda args: exp.Week(
 334                this=exp.TsOrDsToDate(this=seq_get(args, 0)), mode=seq_get(args, 1)
 335            ),
 336            "WEEKOFYEAR": lambda args: exp.WeekOfYear(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 337            "YEAR": lambda args: exp.Year(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 338        }
 339
 340        FUNCTION_PARSERS = {
 341            **parser.Parser.FUNCTION_PARSERS,
 342            "CHAR": lambda self: self._parse_chr(),
 343            "GROUP_CONCAT": lambda self: self._parse_group_concat(),
 344            # https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values
 345            "VALUES": lambda self: self.expression(
 346                exp.Anonymous, this="VALUES", expressions=[self._parse_id_var()]
 347            ),
 348        }
 349
 350        STATEMENT_PARSERS = {
 351            **parser.Parser.STATEMENT_PARSERS,
 352            TokenType.SHOW: lambda self: self._parse_show(),
 353        }
 354
 355        SHOW_PARSERS = {
 356            "BINARY LOGS": _show_parser("BINARY LOGS"),
 357            "MASTER LOGS": _show_parser("BINARY LOGS"),
 358            "BINLOG EVENTS": _show_parser("BINLOG EVENTS"),
 359            "CHARACTER SET": _show_parser("CHARACTER SET"),
 360            "CHARSET": _show_parser("CHARACTER SET"),
 361            "COLLATION": _show_parser("COLLATION"),
 362            "FULL COLUMNS": _show_parser("COLUMNS", target="FROM", full=True),
 363            "COLUMNS": _show_parser("COLUMNS", target="FROM"),
 364            "CREATE DATABASE": _show_parser("CREATE DATABASE", target=True),
 365            "CREATE EVENT": _show_parser("CREATE EVENT", target=True),
 366            "CREATE FUNCTION": _show_parser("CREATE FUNCTION", target=True),
 367            "CREATE PROCEDURE": _show_parser("CREATE PROCEDURE", target=True),
 368            "CREATE TABLE": _show_parser("CREATE TABLE", target=True),
 369            "CREATE TRIGGER": _show_parser("CREATE TRIGGER", target=True),
 370            "CREATE VIEW": _show_parser("CREATE VIEW", target=True),
 371            "DATABASES": _show_parser("DATABASES"),
 372            "SCHEMAS": _show_parser("DATABASES"),
 373            "ENGINE": _show_parser("ENGINE", target=True),
 374            "STORAGE ENGINES": _show_parser("ENGINES"),
 375            "ENGINES": _show_parser("ENGINES"),
 376            "ERRORS": _show_parser("ERRORS"),
 377            "EVENTS": _show_parser("EVENTS"),
 378            "FUNCTION CODE": _show_parser("FUNCTION CODE", target=True),
 379            "FUNCTION STATUS": _show_parser("FUNCTION STATUS"),
 380            "GRANTS": _show_parser("GRANTS", target="FOR"),
 381            "INDEX": _show_parser("INDEX", target="FROM"),
 382            "MASTER STATUS": _show_parser("MASTER STATUS"),
 383            "OPEN TABLES": _show_parser("OPEN TABLES"),
 384            "PLUGINS": _show_parser("PLUGINS"),
 385            "PROCEDURE CODE": _show_parser("PROCEDURE CODE", target=True),
 386            "PROCEDURE STATUS": _show_parser("PROCEDURE STATUS"),
 387            "PRIVILEGES": _show_parser("PRIVILEGES"),
 388            "FULL PROCESSLIST": _show_parser("PROCESSLIST", full=True),
 389            "PROCESSLIST": _show_parser("PROCESSLIST"),
 390            "PROFILE": _show_parser("PROFILE"),
 391            "PROFILES": _show_parser("PROFILES"),
 392            "RELAYLOG EVENTS": _show_parser("RELAYLOG EVENTS"),
 393            "REPLICAS": _show_parser("REPLICAS"),
 394            "SLAVE HOSTS": _show_parser("REPLICAS"),
 395            "REPLICA STATUS": _show_parser("REPLICA STATUS"),
 396            "SLAVE STATUS": _show_parser("REPLICA STATUS"),
 397            "GLOBAL STATUS": _show_parser("STATUS", global_=True),
 398            "SESSION STATUS": _show_parser("STATUS"),
 399            "STATUS": _show_parser("STATUS"),
 400            "TABLE STATUS": _show_parser("TABLE STATUS"),
 401            "FULL TABLES": _show_parser("TABLES", full=True),
 402            "TABLES": _show_parser("TABLES"),
 403            "TRIGGERS": _show_parser("TRIGGERS"),
 404            "GLOBAL VARIABLES": _show_parser("VARIABLES", global_=True),
 405            "SESSION VARIABLES": _show_parser("VARIABLES"),
 406            "VARIABLES": _show_parser("VARIABLES"),
 407            "WARNINGS": _show_parser("WARNINGS"),
 408        }
 409
 410        PROPERTY_PARSERS = {
 411            **parser.Parser.PROPERTY_PARSERS,
 412            "LOCK": lambda self: self._parse_property_assignment(exp.LockProperty),
 413        }
 414
 415        SET_PARSERS = {
 416            **parser.Parser.SET_PARSERS,
 417            "PERSIST": lambda self: self._parse_set_item_assignment("PERSIST"),
 418            "PERSIST_ONLY": lambda self: self._parse_set_item_assignment("PERSIST_ONLY"),
 419            "CHARACTER SET": lambda self: self._parse_set_item_charset("CHARACTER SET"),
 420            "CHARSET": lambda self: self._parse_set_item_charset("CHARACTER SET"),
 421            "NAMES": lambda self: self._parse_set_item_names(),
 422        }
 423
 424        CONSTRAINT_PARSERS = {
 425            **parser.Parser.CONSTRAINT_PARSERS,
 426            "FULLTEXT": lambda self: self._parse_index_constraint(kind="FULLTEXT"),
 427            "INDEX": lambda self: self._parse_index_constraint(),
 428            "KEY": lambda self: self._parse_index_constraint(),
 429            "SPATIAL": lambda self: self._parse_index_constraint(kind="SPATIAL"),
 430        }
 431
 432        ALTER_PARSERS = {
 433            **parser.Parser.ALTER_PARSERS,
 434            "MODIFY": lambda self: self._parse_alter_table_alter(),
 435        }
 436
 437        SCHEMA_UNNAMED_CONSTRAINTS = {
 438            *parser.Parser.SCHEMA_UNNAMED_CONSTRAINTS,
 439            "FULLTEXT",
 440            "INDEX",
 441            "KEY",
 442            "SPATIAL",
 443        }
 444
 445        PROFILE_TYPES: parser.OPTIONS_TYPE = {
 446            **dict.fromkeys(("ALL", "CPU", "IPC", "MEMORY", "SOURCE", "SWAPS"), tuple()),
 447            "BLOCK": ("IO",),
 448            "CONTEXT": ("SWITCHES",),
 449            "PAGE": ("FAULTS",),
 450        }
 451
 452        TYPE_TOKENS = {
 453            *parser.Parser.TYPE_TOKENS,
 454            TokenType.SET,
 455        }
 456
 457        ENUM_TYPE_TOKENS = {
 458            *parser.Parser.ENUM_TYPE_TOKENS,
 459            TokenType.SET,
 460        }
 461
 462        LOG_DEFAULTS_TO_LN = True
 463        STRING_ALIASES = True
 464        VALUES_FOLLOWED_BY_PAREN = False
 465        SUPPORTS_PARTITION_SELECTION = True
 466
 467        def _parse_primary_key_part(self) -> t.Optional[exp.Expression]:
 468            this = self._parse_id_var()
 469            if not self._match(TokenType.L_PAREN):
 470                return this
 471
 472            expression = self._parse_number()
 473            self._match_r_paren()
 474            return self.expression(exp.ColumnPrefix, this=this, expression=expression)
 475
 476        def _parse_index_constraint(
 477            self, kind: t.Optional[str] = None
 478        ) -> exp.IndexColumnConstraint:
 479            if kind:
 480                self._match_texts(("INDEX", "KEY"))
 481
 482            this = self._parse_id_var(any_token=False)
 483            index_type = self._match(TokenType.USING) and self._advance_any() and self._prev.text
 484            expressions = self._parse_wrapped_csv(self._parse_ordered)
 485
 486            options = []
 487            while True:
 488                if self._match_text_seq("KEY_BLOCK_SIZE"):
 489                    self._match(TokenType.EQ)
 490                    opt = exp.IndexConstraintOption(key_block_size=self._parse_number())
 491                elif self._match(TokenType.USING):
 492                    opt = exp.IndexConstraintOption(using=self._advance_any() and self._prev.text)
 493                elif self._match_text_seq("WITH", "PARSER"):
 494                    opt = exp.IndexConstraintOption(parser=self._parse_var(any_token=True))
 495                elif self._match(TokenType.COMMENT):
 496                    opt = exp.IndexConstraintOption(comment=self._parse_string())
 497                elif self._match_text_seq("VISIBLE"):
 498                    opt = exp.IndexConstraintOption(visible=True)
 499                elif self._match_text_seq("INVISIBLE"):
 500                    opt = exp.IndexConstraintOption(visible=False)
 501                elif self._match_text_seq("ENGINE_ATTRIBUTE"):
 502                    self._match(TokenType.EQ)
 503                    opt = exp.IndexConstraintOption(engine_attr=self._parse_string())
 504                elif self._match_text_seq("SECONDARY_ENGINE_ATTRIBUTE"):
 505                    self._match(TokenType.EQ)
 506                    opt = exp.IndexConstraintOption(secondary_engine_attr=self._parse_string())
 507                else:
 508                    opt = None
 509
 510                if not opt:
 511                    break
 512
 513                options.append(opt)
 514
 515            return self.expression(
 516                exp.IndexColumnConstraint,
 517                this=this,
 518                expressions=expressions,
 519                kind=kind,
 520                index_type=index_type,
 521                options=options,
 522            )
 523
 524        def _parse_show_mysql(
 525            self,
 526            this: str,
 527            target: bool | str = False,
 528            full: t.Optional[bool] = None,
 529            global_: t.Optional[bool] = None,
 530        ) -> exp.Show:
 531            if target:
 532                if isinstance(target, str):
 533                    self._match_text_seq(target)
 534                target_id = self._parse_id_var()
 535            else:
 536                target_id = None
 537
 538            log = self._parse_string() if self._match_text_seq("IN") else None
 539
 540            if this in ("BINLOG EVENTS", "RELAYLOG EVENTS"):
 541                position = self._parse_number() if self._match_text_seq("FROM") else None
 542                db = None
 543            else:
 544                position = None
 545                db = None
 546
 547                if self._match(TokenType.FROM):
 548                    db = self._parse_id_var()
 549                elif self._match(TokenType.DOT):
 550                    db = target_id
 551                    target_id = self._parse_id_var()
 552
 553            channel = self._parse_id_var() if self._match_text_seq("FOR", "CHANNEL") else None
 554
 555            like = self._parse_string() if self._match_text_seq("LIKE") else None
 556            where = self._parse_where()
 557
 558            if this == "PROFILE":
 559                types = self._parse_csv(lambda: self._parse_var_from_options(self.PROFILE_TYPES))
 560                query = self._parse_number() if self._match_text_seq("FOR", "QUERY") else None
 561                offset = self._parse_number() if self._match_text_seq("OFFSET") else None
 562                limit = self._parse_number() if self._match_text_seq("LIMIT") else None
 563            else:
 564                types, query = None, None
 565                offset, limit = self._parse_oldstyle_limit()
 566
 567            mutex = True if self._match_text_seq("MUTEX") else None
 568            mutex = False if self._match_text_seq("STATUS") else mutex
 569
 570            return self.expression(
 571                exp.Show,
 572                this=this,
 573                target=target_id,
 574                full=full,
 575                log=log,
 576                position=position,
 577                db=db,
 578                channel=channel,
 579                like=like,
 580                where=where,
 581                types=types,
 582                query=query,
 583                offset=offset,
 584                limit=limit,
 585                mutex=mutex,
 586                **{"global": global_},  # type: ignore
 587            )
 588
 589        def _parse_oldstyle_limit(
 590            self,
 591        ) -> t.Tuple[t.Optional[exp.Expression], t.Optional[exp.Expression]]:
 592            limit = None
 593            offset = None
 594            if self._match_text_seq("LIMIT"):
 595                parts = self._parse_csv(self._parse_number)
 596                if len(parts) == 1:
 597                    limit = parts[0]
 598                elif len(parts) == 2:
 599                    limit = parts[1]
 600                    offset = parts[0]
 601
 602            return offset, limit
 603
 604        def _parse_set_item_charset(self, kind: str) -> exp.Expression:
 605            this = self._parse_string() or self._parse_unquoted_field()
 606            return self.expression(exp.SetItem, this=this, kind=kind)
 607
 608        def _parse_set_item_names(self) -> exp.Expression:
 609            charset = self._parse_string() or self._parse_unquoted_field()
 610            if self._match_text_seq("COLLATE"):
 611                collate = self._parse_string() or self._parse_unquoted_field()
 612            else:
 613                collate = None
 614
 615            return self.expression(exp.SetItem, this=charset, collate=collate, kind="NAMES")
 616
 617        def _parse_type(
 618            self, parse_interval: bool = True, fallback_to_identifier: bool = False
 619        ) -> t.Optional[exp.Expression]:
 620            # mysql binary is special and can work anywhere, even in order by operations
 621            # it operates like a no paren func
 622            if self._match(TokenType.BINARY, advance=False):
 623                data_type = self._parse_types(check_func=True, allow_identifiers=False)
 624
 625                if isinstance(data_type, exp.DataType):
 626                    return self.expression(exp.Cast, this=self._parse_column(), to=data_type)
 627
 628            return super()._parse_type(
 629                parse_interval=parse_interval, fallback_to_identifier=fallback_to_identifier
 630            )
 631
 632        def _parse_chr(self) -> t.Optional[exp.Expression]:
 633            expressions = self._parse_csv(self._parse_assignment)
 634            kwargs: t.Dict[str, t.Any] = {"this": seq_get(expressions, 0)}
 635
 636            if len(expressions) > 1:
 637                kwargs["expressions"] = expressions[1:]
 638
 639            if self._match(TokenType.USING):
 640                kwargs["charset"] = self._parse_var()
 641
 642            return self.expression(exp.Chr, **kwargs)
 643
 644        def _parse_group_concat(self) -> t.Optional[exp.Expression]:
 645            def concat_exprs(
 646                node: t.Optional[exp.Expression], exprs: t.List[exp.Expression]
 647            ) -> exp.Expression:
 648                if isinstance(node, exp.Distinct) and len(node.expressions) > 1:
 649                    concat_exprs = [
 650                        self.expression(exp.Concat, expressions=node.expressions, safe=True)
 651                    ]
 652                    node.set("expressions", concat_exprs)
 653                    return node
 654                if len(exprs) == 1:
 655                    return exprs[0]
 656                return self.expression(exp.Concat, expressions=args, safe=True)
 657
 658            args = self._parse_csv(self._parse_lambda)
 659
 660            if args:
 661                order = args[-1] if isinstance(args[-1], exp.Order) else None
 662
 663                if order:
 664                    # Order By is the last (or only) expression in the list and has consumed the 'expr' before it,
 665                    # remove 'expr' from exp.Order and add it back to args
 666                    args[-1] = order.this
 667                    order.set("this", concat_exprs(order.this, args))
 668
 669                this = order or concat_exprs(args[0], args)
 670            else:
 671                this = None
 672
 673            separator = self._parse_field() if self._match(TokenType.SEPARATOR) else None
 674
 675            return self.expression(exp.GroupConcat, this=this, separator=separator)
 676
 677    class Generator(generator.Generator):
 678        INTERVAL_ALLOWS_PLURAL_FORM = False
 679        LOCKING_READS_SUPPORTED = True
 680        NULL_ORDERING_SUPPORTED = None
 681        JOIN_HINTS = False
 682        TABLE_HINTS = True
 683        DUPLICATE_KEY_UPDATE_WITH_SET = False
 684        QUERY_HINT_SEP = " "
 685        VALUES_AS_TABLE = False
 686        NVL2_SUPPORTED = False
 687        LAST_DAY_SUPPORTS_DATE_PART = False
 688        JSON_TYPE_REQUIRED_FOR_EXTRACTION = True
 689        JSON_PATH_BRACKETED_KEY_SUPPORTED = False
 690        JSON_KEY_VALUE_PAIR_SEP = ","
 691        SUPPORTS_TO_NUMBER = False
 692        PARSE_JSON_NAME = None
 693        PAD_FILL_PATTERN_IS_REQUIRED = True
 694        WRAP_DERIVED_VALUES = False
 695
 696        TRANSFORMS = {
 697            **generator.Generator.TRANSFORMS,
 698            exp.ArrayAgg: rename_func("GROUP_CONCAT"),
 699            exp.CurrentDate: no_paren_current_date_sql,
 700            exp.DateDiff: _remove_ts_or_ds_to_date(
 701                lambda self, e: self.func("DATEDIFF", e.this, e.expression), ("this", "expression")
 702            ),
 703            exp.DateAdd: _remove_ts_or_ds_to_date(date_add_sql("ADD")),
 704            exp.DateStrToDate: datestrtodate_sql,
 705            exp.DateSub: _remove_ts_or_ds_to_date(date_add_sql("SUB")),
 706            exp.DateTrunc: _date_trunc_sql,
 707            exp.Day: _remove_ts_or_ds_to_date(),
 708            exp.DayOfMonth: _remove_ts_or_ds_to_date(rename_func("DAYOFMONTH")),
 709            exp.DayOfWeek: _remove_ts_or_ds_to_date(rename_func("DAYOFWEEK")),
 710            exp.DayOfYear: _remove_ts_or_ds_to_date(rename_func("DAYOFYEAR")),
 711            exp.GroupConcat: lambda self,
 712            e: f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})""",
 713            exp.ILike: no_ilike_sql,
 714            exp.JSONExtractScalar: arrow_json_extract_sql,
 715            exp.Max: max_or_greatest,
 716            exp.Min: min_or_least,
 717            exp.Month: _remove_ts_or_ds_to_date(),
 718            exp.NullSafeEQ: lambda self, e: self.binary(e, "<=>"),
 719            exp.NullSafeNEQ: lambda self, e: f"NOT {self.binary(e, '<=>')}",
 720            exp.Pivot: no_pivot_sql,
 721            exp.Select: transforms.preprocess(
 722                [
 723                    transforms.eliminate_distinct_on,
 724                    transforms.eliminate_semi_and_anti_joins,
 725                    transforms.eliminate_qualify,
 726                    transforms.eliminate_full_outer_join,
 727                ]
 728            ),
 729            exp.StrPosition: strposition_to_locate_sql,
 730            exp.StrToDate: _str_to_date_sql,
 731            exp.StrToTime: _str_to_date_sql,
 732            exp.Stuff: rename_func("INSERT"),
 733            exp.TableSample: no_tablesample_sql,
 734            exp.TimeFromParts: rename_func("MAKETIME"),
 735            exp.TimestampAdd: date_add_interval_sql("DATE", "ADD"),
 736            exp.TimestampDiff: lambda self, e: self.func(
 737                "TIMESTAMPDIFF", unit_to_var(e), e.expression, e.this
 738            ),
 739            exp.TimestampSub: date_add_interval_sql("DATE", "SUB"),
 740            exp.TimeStrToUnix: rename_func("UNIX_TIMESTAMP"),
 741            exp.TimeStrToTime: lambda self, e: self.sql(
 742                exp.cast(e.this, exp.DataType.Type.DATETIME, copy=True)
 743            ),
 744            exp.TimeToStr: _remove_ts_or_ds_to_date(
 745                lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e))
 746            ),
 747            exp.Trim: _trim_sql,
 748            exp.TryCast: no_trycast_sql,
 749            exp.TsOrDsAdd: date_add_sql("ADD"),
 750            exp.TsOrDsDiff: lambda self, e: self.func("DATEDIFF", e.this, e.expression),
 751            exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
 752            exp.UnixToTime: _unix_to_time_sql,
 753            exp.Week: _remove_ts_or_ds_to_date(),
 754            exp.WeekOfYear: _remove_ts_or_ds_to_date(rename_func("WEEKOFYEAR")),
 755            exp.Year: _remove_ts_or_ds_to_date(),
 756        }
 757
 758        UNSIGNED_TYPE_MAPPING = {
 759            exp.DataType.Type.UBIGINT: "BIGINT",
 760            exp.DataType.Type.UINT: "INT",
 761            exp.DataType.Type.UMEDIUMINT: "MEDIUMINT",
 762            exp.DataType.Type.USMALLINT: "SMALLINT",
 763            exp.DataType.Type.UTINYINT: "TINYINT",
 764            exp.DataType.Type.UDECIMAL: "DECIMAL",
 765        }
 766
 767        TIMESTAMP_TYPE_MAPPING = {
 768            exp.DataType.Type.TIMESTAMP: "DATETIME",
 769            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
 770            exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
 771        }
 772
 773        TYPE_MAPPING = {
 774            **generator.Generator.TYPE_MAPPING,
 775            **UNSIGNED_TYPE_MAPPING,
 776            **TIMESTAMP_TYPE_MAPPING,
 777        }
 778
 779        TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMTEXT)
 780        TYPE_MAPPING.pop(exp.DataType.Type.LONGTEXT)
 781        TYPE_MAPPING.pop(exp.DataType.Type.TINYTEXT)
 782        TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMBLOB)
 783        TYPE_MAPPING.pop(exp.DataType.Type.LONGBLOB)
 784        TYPE_MAPPING.pop(exp.DataType.Type.TINYBLOB)
 785
 786        PROPERTIES_LOCATION = {
 787            **generator.Generator.PROPERTIES_LOCATION,
 788            exp.TransientProperty: exp.Properties.Location.UNSUPPORTED,
 789            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
 790        }
 791
 792        LIMIT_FETCH = "LIMIT"
 793
 794        LIMIT_ONLY_LITERALS = True
 795
 796        CHAR_CAST_MAPPING = dict.fromkeys(
 797            (
 798                exp.DataType.Type.LONGTEXT,
 799                exp.DataType.Type.LONGBLOB,
 800                exp.DataType.Type.MEDIUMBLOB,
 801                exp.DataType.Type.MEDIUMTEXT,
 802                exp.DataType.Type.TEXT,
 803                exp.DataType.Type.TINYBLOB,
 804                exp.DataType.Type.TINYTEXT,
 805                exp.DataType.Type.VARCHAR,
 806            ),
 807            "CHAR",
 808        )
 809        SIGNED_CAST_MAPPING = dict.fromkeys(
 810            (
 811                exp.DataType.Type.BIGINT,
 812                exp.DataType.Type.BOOLEAN,
 813                exp.DataType.Type.INT,
 814                exp.DataType.Type.SMALLINT,
 815                exp.DataType.Type.TINYINT,
 816                exp.DataType.Type.MEDIUMINT,
 817            ),
 818            "SIGNED",
 819        )
 820
 821        # MySQL doesn't support many datatypes in cast.
 822        # https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast
 823        CAST_MAPPING = {
 824            **CHAR_CAST_MAPPING,
 825            **SIGNED_CAST_MAPPING,
 826            exp.DataType.Type.UBIGINT: "UNSIGNED",
 827        }
 828
 829        TIMESTAMP_FUNC_TYPES = {
 830            exp.DataType.Type.TIMESTAMPTZ,
 831            exp.DataType.Type.TIMESTAMPLTZ,
 832        }
 833
 834        # https://dev.mysql.com/doc/refman/8.0/en/keywords.html
 835        RESERVED_KEYWORDS = {
 836            "accessible",
 837            "add",
 838            "all",
 839            "alter",
 840            "analyze",
 841            "and",
 842            "as",
 843            "asc",
 844            "asensitive",
 845            "before",
 846            "between",
 847            "bigint",
 848            "binary",
 849            "blob",
 850            "both",
 851            "by",
 852            "call",
 853            "cascade",
 854            "case",
 855            "change",
 856            "char",
 857            "character",
 858            "check",
 859            "collate",
 860            "column",
 861            "condition",
 862            "constraint",
 863            "continue",
 864            "convert",
 865            "create",
 866            "cross",
 867            "cube",
 868            "cume_dist",
 869            "current_date",
 870            "current_time",
 871            "current_timestamp",
 872            "current_user",
 873            "cursor",
 874            "database",
 875            "databases",
 876            "day_hour",
 877            "day_microsecond",
 878            "day_minute",
 879            "day_second",
 880            "dec",
 881            "decimal",
 882            "declare",
 883            "default",
 884            "delayed",
 885            "delete",
 886            "dense_rank",
 887            "desc",
 888            "describe",
 889            "deterministic",
 890            "distinct",
 891            "distinctrow",
 892            "div",
 893            "double",
 894            "drop",
 895            "dual",
 896            "each",
 897            "else",
 898            "elseif",
 899            "empty",
 900            "enclosed",
 901            "escaped",
 902            "except",
 903            "exists",
 904            "exit",
 905            "explain",
 906            "false",
 907            "fetch",
 908            "first_value",
 909            "float",
 910            "float4",
 911            "float8",
 912            "for",
 913            "force",
 914            "foreign",
 915            "from",
 916            "fulltext",
 917            "function",
 918            "generated",
 919            "get",
 920            "grant",
 921            "group",
 922            "grouping",
 923            "groups",
 924            "having",
 925            "high_priority",
 926            "hour_microsecond",
 927            "hour_minute",
 928            "hour_second",
 929            "if",
 930            "ignore",
 931            "in",
 932            "index",
 933            "infile",
 934            "inner",
 935            "inout",
 936            "insensitive",
 937            "insert",
 938            "int",
 939            "int1",
 940            "int2",
 941            "int3",
 942            "int4",
 943            "int8",
 944            "integer",
 945            "intersect",
 946            "interval",
 947            "into",
 948            "io_after_gtids",
 949            "io_before_gtids",
 950            "is",
 951            "iterate",
 952            "join",
 953            "json_table",
 954            "key",
 955            "keys",
 956            "kill",
 957            "lag",
 958            "last_value",
 959            "lateral",
 960            "lead",
 961            "leading",
 962            "leave",
 963            "left",
 964            "like",
 965            "limit",
 966            "linear",
 967            "lines",
 968            "load",
 969            "localtime",
 970            "localtimestamp",
 971            "lock",
 972            "long",
 973            "longblob",
 974            "longtext",
 975            "loop",
 976            "low_priority",
 977            "master_bind",
 978            "master_ssl_verify_server_cert",
 979            "match",
 980            "maxvalue",
 981            "mediumblob",
 982            "mediumint",
 983            "mediumtext",
 984            "middleint",
 985            "minute_microsecond",
 986            "minute_second",
 987            "mod",
 988            "modifies",
 989            "natural",
 990            "not",
 991            "no_write_to_binlog",
 992            "nth_value",
 993            "ntile",
 994            "null",
 995            "numeric",
 996            "of",
 997            "on",
 998            "optimize",
 999            "optimizer_costs",
1000            "option",
1001            "optionally",
1002            "or",
1003            "order",
1004            "out",
1005            "outer",
1006            "outfile",
1007            "over",
1008            "partition",
1009            "percent_rank",
1010            "precision",
1011            "primary",
1012            "procedure",
1013            "purge",
1014            "range",
1015            "rank",
1016            "read",
1017            "reads",
1018            "read_write",
1019            "real",
1020            "recursive",
1021            "references",
1022            "regexp",
1023            "release",
1024            "rename",
1025            "repeat",
1026            "replace",
1027            "require",
1028            "resignal",
1029            "restrict",
1030            "return",
1031            "revoke",
1032            "right",
1033            "rlike",
1034            "row",
1035            "rows",
1036            "row_number",
1037            "schema",
1038            "schemas",
1039            "second_microsecond",
1040            "select",
1041            "sensitive",
1042            "separator",
1043            "set",
1044            "show",
1045            "signal",
1046            "smallint",
1047            "spatial",
1048            "specific",
1049            "sql",
1050            "sqlexception",
1051            "sqlstate",
1052            "sqlwarning",
1053            "sql_big_result",
1054            "sql_calc_found_rows",
1055            "sql_small_result",
1056            "ssl",
1057            "starting",
1058            "stored",
1059            "straight_join",
1060            "system",
1061            "table",
1062            "terminated",
1063            "then",
1064            "tinyblob",
1065            "tinyint",
1066            "tinytext",
1067            "to",
1068            "trailing",
1069            "trigger",
1070            "true",
1071            "undo",
1072            "union",
1073            "unique",
1074            "unlock",
1075            "unsigned",
1076            "update",
1077            "usage",
1078            "use",
1079            "using",
1080            "utc_date",
1081            "utc_time",
1082            "utc_timestamp",
1083            "values",
1084            "varbinary",
1085            "varchar",
1086            "varcharacter",
1087            "varying",
1088            "virtual",
1089            "when",
1090            "where",
1091            "while",
1092            "window",
1093            "with",
1094            "write",
1095            "xor",
1096            "year_month",
1097            "zerofill",
1098        }
1099
1100        def array_sql(self, expression: exp.Array) -> str:
1101            self.unsupported("Arrays are not supported by MySQL")
1102            return self.function_fallback_sql(expression)
1103
1104        def arraycontainsall_sql(self, expression: exp.ArrayContainsAll) -> str:
1105            self.unsupported("Array operations are not supported by MySQL")
1106            return self.function_fallback_sql(expression)
1107
1108        def dpipe_sql(self, expression: exp.DPipe) -> str:
1109            return self.func("CONCAT", *expression.flatten())
1110
1111        def extract_sql(self, expression: exp.Extract) -> str:
1112            unit = expression.name
1113            if unit and unit.lower() == "epoch":
1114                return self.func("UNIX_TIMESTAMP", expression.expression)
1115
1116            return super().extract_sql(expression)
1117
1118        def datatype_sql(self, expression: exp.DataType) -> str:
1119            # https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html
1120            result = super().datatype_sql(expression)
1121            if expression.this in self.UNSIGNED_TYPE_MAPPING:
1122                result = f"{result} UNSIGNED"
1123            return result
1124
1125        def jsonarraycontains_sql(self, expression: exp.JSONArrayContains) -> str:
1126            return f"{self.sql(expression, 'this')} MEMBER OF({self.sql(expression, 'expression')})"
1127
1128        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
1129            if expression.to.this in self.TIMESTAMP_FUNC_TYPES:
1130                return self.func("TIMESTAMP", expression.this)
1131
1132            to = self.CAST_MAPPING.get(expression.to.this)
1133
1134            if to:
1135                expression.to.set("this", to)
1136            return super().cast_sql(expression)
1137
1138        def show_sql(self, expression: exp.Show) -> str:
1139            this = f" {expression.name}"
1140            full = " FULL" if expression.args.get("full") else ""
1141            global_ = " GLOBAL" if expression.args.get("global") else ""
1142
1143            target = self.sql(expression, "target")
1144            target = f" {target}" if target else ""
1145            if expression.name in ("COLUMNS", "INDEX"):
1146                target = f" FROM{target}"
1147            elif expression.name == "GRANTS":
1148                target = f" FOR{target}"
1149
1150            db = self._prefixed_sql("FROM", expression, "db")
1151
1152            like = self._prefixed_sql("LIKE", expression, "like")
1153            where = self.sql(expression, "where")
1154
1155            types = self.expressions(expression, key="types")
1156            types = f" {types}" if types else types
1157            query = self._prefixed_sql("FOR QUERY", expression, "query")
1158
1159            if expression.name == "PROFILE":
1160                offset = self._prefixed_sql("OFFSET", expression, "offset")
1161                limit = self._prefixed_sql("LIMIT", expression, "limit")
1162            else:
1163                offset = ""
1164                limit = self._oldstyle_limit_sql(expression)
1165
1166            log = self._prefixed_sql("IN", expression, "log")
1167            position = self._prefixed_sql("FROM", expression, "position")
1168
1169            channel = self._prefixed_sql("FOR CHANNEL", expression, "channel")
1170
1171            if expression.name == "ENGINE":
1172                mutex_or_status = " MUTEX" if expression.args.get("mutex") else " STATUS"
1173            else:
1174                mutex_or_status = ""
1175
1176            return f"SHOW{full}{global_}{this}{target}{types}{db}{query}{log}{position}{channel}{mutex_or_status}{like}{where}{offset}{limit}"
1177
1178        def altercolumn_sql(self, expression: exp.AlterColumn) -> str:
1179            dtype = self.sql(expression, "dtype")
1180            if not dtype:
1181                return super().altercolumn_sql(expression)
1182
1183            this = self.sql(expression, "this")
1184            return f"MODIFY COLUMN {this} {dtype}"
1185
1186        def _prefixed_sql(self, prefix: str, expression: exp.Expression, arg: str) -> str:
1187            sql = self.sql(expression, arg)
1188            return f" {prefix} {sql}" if sql else ""
1189
1190        def _oldstyle_limit_sql(self, expression: exp.Show) -> str:
1191            limit = self.sql(expression, "limit")
1192            offset = self.sql(expression, "offset")
1193            if limit:
1194                limit_offset = f"{offset}, {limit}" if offset else limit
1195                return f" LIMIT {limit_offset}"
1196            return ""
1197
1198        def chr_sql(self, expression: exp.Chr) -> str:
1199            this = self.expressions(sqls=[expression.this] + expression.expressions)
1200            charset = expression.args.get("charset")
1201            using = f" USING {self.sql(charset)}" if charset else ""
1202            return f"CHAR({this}{using})"
1203
1204        def timestamptrunc_sql(self, expression: exp.TimestampTrunc) -> str:
1205            unit = expression.args.get("unit")
1206
1207            # Pick an old-enough date to avoid negative timestamp diffs
1208            start_ts = "'0000-01-01 00:00:00'"
1209
1210            # Source: https://stackoverflow.com/a/32955740
1211            timestamp_diff = build_date_delta(exp.TimestampDiff)([unit, start_ts, expression.this])
1212            interval = exp.Interval(this=timestamp_diff, unit=unit)
1213            dateadd = build_date_delta_with_interval(exp.DateAdd)([start_ts, interval])
1214
1215            return self.sql(dateadd)