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