sqlglot.generators.mysql

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, transforms
  6from sqlglot.dialects.dialect import (
  7    arrow_json_extract_sql,
  8    build_date_delta,
  9    build_date_delta_with_interval,
 10    date_add_interval_sql,
 11    datestrtodate_sql,
 12    length_or_char_length_sql,
 13    max_or_greatest,
 14    min_or_least,
 15    no_ilike_sql,
 16    no_paren_current_date_sql,
 17    no_pivot_sql,
 18    no_tablesample_sql,
 19    no_trycast_sql,
 20    rename_func,
 21    strposition_sql,
 22    unit_to_var,
 23    trim_sql,
 24    timestrtotime_sql,
 25)
 26from sqlglot.generator import unsupported_args
 27from collections import defaultdict
 28
 29
 30def _date_trunc_sql(self: MySQLGenerator, expression: exp.DateTrunc) -> str:
 31    expr = self.sql(expression, "this")
 32    unit = expression.text("unit").upper()
 33
 34    if unit == "WEEK":
 35        concat = f"CONCAT(YEAR({expr}), ' ', WEEK({expr}, 1), ' 1')"
 36        date_format = "%Y %u %w"
 37    elif unit == "MONTH":
 38        concat = f"CONCAT(YEAR({expr}), ' ', MONTH({expr}), ' 1')"
 39        date_format = "%Y %c %e"
 40    elif unit == "QUARTER":
 41        concat = f"CONCAT(YEAR({expr}), ' ', QUARTER({expr}) * 3 - 2, ' 1')"
 42        date_format = "%Y %c %e"
 43    elif unit == "YEAR":
 44        concat = f"CONCAT(YEAR({expr}), ' 1 1')"
 45        date_format = "%Y %c %e"
 46    else:
 47        if unit != "DAY":
 48            self.unsupported(f"Unexpected interval unit: {unit}")
 49        return self.func("DATE", expr)
 50
 51    return self.func("STR_TO_DATE", concat, f"'{date_format}'")
 52
 53
 54def _str_to_date_sql(
 55    self: MySQLGenerator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate
 56) -> str:
 57    return self.func("STR_TO_DATE", expression.this, self.format_time(expression))
 58
 59
 60def _unix_to_time_sql(self: MySQLGenerator, expression: exp.UnixToTime) -> str:
 61    scale = expression.args.get("scale")
 62    timestamp = expression.this
 63
 64    if scale in (None, exp.UnixToTime.SECONDS):
 65        return self.func("FROM_UNIXTIME", timestamp, self.format_time(expression))
 66
 67    return self.func(
 68        "FROM_UNIXTIME",
 69        exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)),
 70        self.format_time(expression),
 71    )
 72
 73
 74def date_add_sql(
 75    kind: str,
 76) -> t.Callable[[generator.Generator, exp.Expr], str]:
 77    def func(self: generator.Generator, expression: exp.Expr) -> str:
 78        return self.func(
 79            f"DATE_{kind}",
 80            expression.this,
 81            exp.Interval(this=expression.expression, unit=unit_to_var(expression)),
 82        )
 83
 84    return func
 85
 86
 87_MAKE_INTERVAL_UNIT_ALIASES = {
 88    "years": "year",
 89    "months": "month",
 90    "weeks": "week",
 91    "days": "day",
 92    "hours": "hour",
 93    "minutes": "minute",
 94    "mins": "minute",
 95    "seconds": "second",
 96    "secs": "second",
 97}
 98
 99
100def _ts_or_ds_to_date_sql(self: MySQLGenerator, expression: exp.TsOrDsToDate) -> str:
101    time_format = expression.args.get("format")
102    return _str_to_date_sql(self, expression) if time_format else self.func("DATE", expression.this)
103
104
105def _remove_ts_or_ds_to_date(
106    to_sql: t.Callable[[MySQLGenerator, exp.Expr], str] | None = None,
107    args: tuple[str, ...] = ("this",),
108) -> t.Callable[[MySQLGenerator, exp.Func], str]:
109    def func(self: MySQLGenerator, expression: exp.Func) -> str:
110        for arg_key in args:
111            arg = expression.args.get(arg_key)
112            if isinstance(arg, (exp.TsOrDsToDate, exp.TsOrDsToTimestamp)) and not arg.args.get(
113                "format"
114            ):
115                expression.set(arg_key, arg.this)
116
117        return to_sql(self, expression) if to_sql else self.function_fallback_sql(expression)
118
119    return func
120
121
122class MySQLGenerator(generator.Generator):
123    SELECT_KINDS: tuple[str, ...] = ()
124    TRY_SUPPORTED = False
125    SUPPORTS_UESCAPE = False
126    SUPPORTS_DECODE_CASE = False
127    SUPPORTS_MODIFY_COLUMN = True
128    SUPPORTS_CHANGE_COLUMN = True
129
130    AFTER_HAVING_MODIFIER_TRANSFORMS = generator.AFTER_HAVING_MODIFIER_TRANSFORMS
131
132    INTERVAL_ALLOWS_PLURAL_FORM = False
133    LOCKING_READS_SUPPORTED = True
134    NULL_ORDERING_SUPPORTED: bool | None = None
135    JOIN_HINTS = False
136    TABLE_HINTS = True
137    DUPLICATE_KEY_UPDATE_WITH_SET = False
138    QUERY_HINT_SEP = " "
139    VALUES_AS_TABLE = False
140    NVL2_SUPPORTED = False
141    LAST_DAY_SUPPORTS_DATE_PART = False
142    JSON_TYPE_REQUIRED_FOR_EXTRACTION = True
143    JSON_PATH_BRACKETED_KEY_SUPPORTED = False
144    JSON_KEY_VALUE_PAIR_SEP = ","
145    SUPPORTS_TO_NUMBER = False
146    PARSE_JSON_NAME: str | None = None
147    PAD_FILL_PATTERN_IS_REQUIRED = True
148    WRAP_DERIVED_VALUES = False
149    VARCHAR_REQUIRES_SIZE = True
150    SUPPORTS_MEDIAN = False
151    UPDATE_STATEMENT_SUPPORTS_FROM = False
152
153    TRANSFORMS = {
154        **generator.Generator.TRANSFORMS,
155        exp.ArrayAgg: rename_func("GROUP_CONCAT"),
156        exp.BitwiseAndAgg: rename_func("BIT_AND"),
157        exp.BitwiseOrAgg: rename_func("BIT_OR"),
158        exp.BitwiseXorAgg: rename_func("BIT_XOR"),
159        exp.BitwiseCount: rename_func("BIT_COUNT"),
160        exp.Chr: lambda self, e: self.chr_sql(e, "CHAR"),
161        exp.CurrentDate: no_paren_current_date_sql,
162        exp.CurrentVersion: rename_func("VERSION"),
163        exp.DateDiff: _remove_ts_or_ds_to_date(
164            lambda self, e: self.func("DATEDIFF", e.this, e.expression), ("this", "expression")
165        ),
166        exp.DateAdd: _remove_ts_or_ds_to_date(date_add_sql("ADD")),
167        exp.DateStrToDate: datestrtodate_sql,
168        exp.DateSub: _remove_ts_or_ds_to_date(date_add_sql("SUB")),
169        exp.DateTrunc: _date_trunc_sql,
170        exp.Day: _remove_ts_or_ds_to_date(),
171        exp.DayOfMonth: _remove_ts_or_ds_to_date(rename_func("DAYOFMONTH")),
172        exp.DayOfWeek: _remove_ts_or_ds_to_date(rename_func("DAYOFWEEK")),
173        exp.DayOfYear: _remove_ts_or_ds_to_date(rename_func("DAYOFYEAR")),
174        exp.GroupConcat: lambda self, e: (
175            f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})"""
176        ),
177        exp.ILike: no_ilike_sql,
178        exp.JSONExtractScalar: arrow_json_extract_sql,
179        exp.Length: length_or_char_length_sql,
180        exp.LogicalOr: rename_func("MAX"),
181        exp.LogicalAnd: rename_func("MIN"),
182        exp.Max: max_or_greatest,
183        exp.Min: min_or_least,
184        exp.Month: _remove_ts_or_ds_to_date(),
185        exp.NullSafeEQ: lambda self, e: self.binary(e, "<=>"),
186        exp.NullSafeNEQ: lambda self, e: f"NOT {self.binary(e, '<=>')}",
187        exp.NumberToStr: rename_func("FORMAT"),
188        exp.Pivot: no_pivot_sql,
189        exp.Select: transforms.preprocess(
190            [
191                transforms.eliminate_distinct_on,
192                transforms.eliminate_semi_and_anti_joins,
193                transforms.eliminate_qualify,
194                transforms.eliminate_full_outer_join,
195                transforms.unnest_generate_date_array_using_recursive_cte,
196            ]
197        ),
198        exp.StrPosition: lambda self, e: strposition_sql(
199            self, e, func_name="LOCATE", supports_position=True
200        ),
201        exp.StrToDate: _str_to_date_sql,
202        exp.StrToTime: _str_to_date_sql,
203        exp.Stuff: rename_func("INSERT"),
204        exp.SessionUser: lambda *_: "SESSION_USER()",
205        exp.TableSample: no_tablesample_sql,
206        exp.TimeFromParts: rename_func("MAKETIME"),
207        exp.TimestampAdd: date_add_interval_sql("DATE", "ADD"),
208        exp.TimestampDiff: lambda self, e: self.func(
209            "TIMESTAMPDIFF", unit_to_var(e), e.expression, e.this
210        ),
211        exp.TimestampSub: date_add_interval_sql("DATE", "SUB"),
212        exp.TimeStrToUnix: rename_func("UNIX_TIMESTAMP"),
213        exp.TimeStrToTime: lambda self, e: timestrtotime_sql(
214            self,
215            e,
216            include_precision=not e.args.get("zone"),
217        ),
218        exp.TimeToStr: _remove_ts_or_ds_to_date(
219            lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e))
220        ),
221        exp.Trim: trim_sql,
222        exp.Trunc: rename_func("TRUNCATE"),
223        exp.TryCast: no_trycast_sql,
224        exp.TsOrDsAdd: date_add_sql("ADD"),
225        exp.TsOrDsDiff: lambda self, e: self.func("DATEDIFF", e.this, e.expression),
226        exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
227        exp.Unicode: lambda self, e: f"ORD(CONVERT({self.sql(e.this)} USING utf32))",
228        exp.UnixToTime: _unix_to_time_sql,
229        exp.Week: _remove_ts_or_ds_to_date(),
230        exp.WeekOfYear: _remove_ts_or_ds_to_date(rename_func("WEEKOFYEAR")),
231        exp.Year: _remove_ts_or_ds_to_date(),
232        exp.UtcTimestamp: rename_func("UTC_TIMESTAMP"),
233        exp.UtcTime: rename_func("UTC_TIME"),
234    }
235
236    UNSIGNED_TYPE_MAPPING = {
237        exp.DType.UBIGINT: "BIGINT",
238        exp.DType.UINT: "INT",
239        exp.DType.UMEDIUMINT: "MEDIUMINT",
240        exp.DType.USMALLINT: "SMALLINT",
241        exp.DType.UTINYINT: "TINYINT",
242        exp.DType.UDECIMAL: "DECIMAL",
243        exp.DType.UDOUBLE: "DOUBLE",
244    }
245
246    TIMESTAMP_TYPE_MAPPING = {
247        exp.DType.DATETIME2: "DATETIME",
248        exp.DType.SMALLDATETIME: "DATETIME",
249        exp.DType.TIMESTAMP: "DATETIME",
250        exp.DType.TIMESTAMPNTZ: "DATETIME",
251        exp.DType.TIMESTAMPTZ: "TIMESTAMP",
252        exp.DType.TIMESTAMPLTZ: "TIMESTAMP",
253    }
254
255    TYPE_MAPPING: t.ClassVar = {
256        exp.DType.NCHAR: "CHAR",
257        exp.DType.NVARCHAR: "VARCHAR",
258        exp.DType.INET: "INET",
259        exp.DType.ROWVERSION: "VARBINARY",
260        exp.DType.UBIGINT: "BIGINT",
261        exp.DType.UINT: "INT",
262        exp.DType.UMEDIUMINT: "MEDIUMINT",
263        exp.DType.USMALLINT: "SMALLINT",
264        exp.DType.UTINYINT: "TINYINT",
265        exp.DType.UDECIMAL: "DECIMAL",
266        exp.DType.UDOUBLE: "DOUBLE",
267        exp.DType.DATETIME2: "DATETIME",
268        exp.DType.SMALLDATETIME: "DATETIME",
269        exp.DType.TIMESTAMP: "DATETIME",
270        exp.DType.TIMESTAMPNTZ: "DATETIME",
271        exp.DType.TIMESTAMPTZ: "TIMESTAMP",
272        exp.DType.TIMESTAMPLTZ: "TIMESTAMP",
273    }
274
275    PROPERTIES_LOCATION: t.ClassVar = {
276        **generator.Generator.PROPERTIES_LOCATION,
277        exp.TransientProperty: exp.Properties.Location.UNSUPPORTED,
278        exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
279        exp.PartitionedByProperty: exp.Properties.Location.UNSUPPORTED,
280        exp.PartitionByRangeProperty: exp.Properties.Location.POST_SCHEMA,
281        exp.PartitionByListProperty: exp.Properties.Location.POST_SCHEMA,
282    }
283
284    LIMIT_FETCH = "LIMIT"
285
286    LIMIT_ONLY_LITERALS = True
287
288    CHAR_CAST_MAPPING: t.ClassVar = dict.fromkeys(
289        (
290            exp.DType.LONGTEXT,
291            exp.DType.LONGBLOB,
292            exp.DType.MEDIUMBLOB,
293            exp.DType.MEDIUMTEXT,
294            exp.DType.TEXT,
295            exp.DType.TINYBLOB,
296            exp.DType.TINYTEXT,
297            exp.DType.VARCHAR,
298        ),
299        "CHAR",
300    )
301    SIGNED_CAST_MAPPING: t.ClassVar = dict.fromkeys(
302        (
303            exp.DType.BIGINT,
304            exp.DType.BOOLEAN,
305            exp.DType.INT,
306            exp.DType.SMALLINT,
307            exp.DType.TINYINT,
308            exp.DType.MEDIUMINT,
309        ),
310        "SIGNED",
311    )
312
313    # MySQL doesn't support many datatypes in cast.
314    # https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast
315    CAST_MAPPING = {
316        exp.DType.LONGTEXT: "CHAR",
317        exp.DType.LONGBLOB: "CHAR",
318        exp.DType.MEDIUMBLOB: "CHAR",
319        exp.DType.MEDIUMTEXT: "CHAR",
320        exp.DType.TEXT: "CHAR",
321        exp.DType.TINYBLOB: "CHAR",
322        exp.DType.TINYTEXT: "CHAR",
323        exp.DType.VARCHAR: "CHAR",
324        exp.DType.BIGINT: "SIGNED",
325        exp.DType.BOOLEAN: "SIGNED",
326        exp.DType.INT: "SIGNED",
327        exp.DType.SMALLINT: "SIGNED",
328        exp.DType.TINYINT: "SIGNED",
329        exp.DType.MEDIUMINT: "SIGNED",
330        exp.DType.UBIGINT: "UNSIGNED",
331    }
332
333    TIMESTAMP_FUNC_TYPES = {
334        exp.DType.TIMESTAMPTZ,
335        exp.DType.TIMESTAMPLTZ,
336    }
337
338    # https://dev.mysql.com/doc/refman/8.0/en/keywords.html
339    RESERVED_KEYWORDS = {
340        "accessible",
341        "add",
342        "all",
343        "alter",
344        "analyze",
345        "and",
346        "as",
347        "asc",
348        "asensitive",
349        "before",
350        "between",
351        "bigint",
352        "binary",
353        "blob",
354        "both",
355        "by",
356        "call",
357        "cascade",
358        "case",
359        "change",
360        "char",
361        "character",
362        "check",
363        "collate",
364        "column",
365        "condition",
366        "constraint",
367        "continue",
368        "convert",
369        "create",
370        "cross",
371        "cube",
372        "cume_dist",
373        "current_date",
374        "current_time",
375        "current_timestamp",
376        "current_user",
377        "cursor",
378        "database",
379        "databases",
380        "day_hour",
381        "day_microsecond",
382        "day_minute",
383        "day_second",
384        "dec",
385        "decimal",
386        "declare",
387        "default",
388        "delayed",
389        "delete",
390        "dense_rank",
391        "desc",
392        "describe",
393        "deterministic",
394        "distinct",
395        "distinctrow",
396        "div",
397        "double",
398        "drop",
399        "dual",
400        "each",
401        "else",
402        "elseif",
403        "empty",
404        "enclosed",
405        "escaped",
406        "except",
407        "exists",
408        "exit",
409        "explain",
410        "false",
411        "fetch",
412        "first_value",
413        "float",
414        "float4",
415        "float8",
416        "for",
417        "force",
418        "foreign",
419        "from",
420        "fulltext",
421        "function",
422        "generated",
423        "get",
424        "grant",
425        "group",
426        "grouping",
427        "groups",
428        "having",
429        "high_priority",
430        "hour_microsecond",
431        "hour_minute",
432        "hour_second",
433        "if",
434        "ignore",
435        "in",
436        "index",
437        "infile",
438        "inner",
439        "inout",
440        "insensitive",
441        "insert",
442        "int",
443        "int1",
444        "int2",
445        "int3",
446        "int4",
447        "int8",
448        "integer",
449        "intersect",
450        "interval",
451        "into",
452        "io_after_gtids",
453        "io_before_gtids",
454        "is",
455        "iterate",
456        "join",
457        "json_table",
458        "key",
459        "keys",
460        "kill",
461        "lag",
462        "last_value",
463        "lateral",
464        "lead",
465        "leading",
466        "leave",
467        "left",
468        "like",
469        "limit",
470        "linear",
471        "lines",
472        "load",
473        "localtime",
474        "localtimestamp",
475        "lock",
476        "long",
477        "longblob",
478        "longtext",
479        "loop",
480        "low_priority",
481        "master_bind",
482        "master_ssl_verify_server_cert",
483        "match",
484        "maxvalue",
485        "mediumblob",
486        "mediumint",
487        "mediumtext",
488        "middleint",
489        "minute_microsecond",
490        "minute_second",
491        "mod",
492        "modifies",
493        "natural",
494        "not",
495        "no_write_to_binlog",
496        "nth_value",
497        "ntile",
498        "null",
499        "numeric",
500        "of",
501        "on",
502        "optimize",
503        "optimizer_costs",
504        "option",
505        "optionally",
506        "or",
507        "order",
508        "out",
509        "outer",
510        "outfile",
511        "over",
512        "partition",
513        "percent_rank",
514        "precision",
515        "primary",
516        "procedure",
517        "purge",
518        "range",
519        "rank",
520        "read",
521        "reads",
522        "read_write",
523        "real",
524        "recursive",
525        "references",
526        "regexp",
527        "release",
528        "rename",
529        "repeat",
530        "replace",
531        "require",
532        "resignal",
533        "restrict",
534        "return",
535        "revoke",
536        "right",
537        "rlike",
538        "row",
539        "rows",
540        "row_number",
541        "schema",
542        "schemas",
543        "second_microsecond",
544        "select",
545        "sensitive",
546        "separator",
547        "set",
548        "show",
549        "signal",
550        "smallint",
551        "spatial",
552        "specific",
553        "sql",
554        "sqlexception",
555        "sqlstate",
556        "sqlwarning",
557        "sql_big_result",
558        "sql_calc_found_rows",
559        "sql_small_result",
560        "ssl",
561        "starting",
562        "stored",
563        "straight_join",
564        "system",
565        "table",
566        "terminated",
567        "then",
568        "tinyblob",
569        "tinyint",
570        "tinytext",
571        "to",
572        "trailing",
573        "trigger",
574        "true",
575        "undo",
576        "union",
577        "unique",
578        "unlock",
579        "unsigned",
580        "update",
581        "usage",
582        "use",
583        "using",
584        "utc_date",
585        "utc_time",
586        "utc_timestamp",
587        "values",
588        "varbinary",
589        "varchar",
590        "varcharacter",
591        "varying",
592        "virtual",
593        "when",
594        "where",
595        "while",
596        "window",
597        "with",
598        "write",
599        "xor",
600        "year_month",
601        "zerofill",
602    }
603
604    SQL_SECURITY_VIEW_LOCATION = exp.Properties.Location.POST_CREATE
605
606    def makeinterval_sql(self: MySQLGenerator, expression: exp.MakeInterval) -> str:
607        intervals: list[exp.Interval] = []
608        for arg_key, value in expression.args.items():
609            if value is None:
610                continue
611
612            if isinstance(value, exp.Kwarg):
613                unit_name = _MAKE_INTERVAL_UNIT_ALIASES.get(
614                    value.this.name.lower(), value.this.name.lower()
615                )
616                value = value.expression
617            else:
618                unit_name = arg_key
619
620            intervals.append(exp.Interval(this=value.copy(), unit=exp.var(unit_name.upper())))
621
622        if not intervals:
623            return self.function_fallback_sql(expression)
624
625        parent = expression.parent
626        sep = " - " if isinstance(parent, exp.Sub) and parent.expression is expression else " + "
627
628        return sep.join(self.sql(interval) for interval in intervals)
629
630    def locate_properties(self, properties: exp.Properties) -> defaultdict:
631        locations = super().locate_properties(properties)
632
633        # MySQL puts SQL SECURITY before VIEW but after the schema for functions/procedures
634        if isinstance(create := properties.parent, exp.Create) and create.kind == "VIEW":
635            post_schema = locations[exp.Properties.Location.POST_SCHEMA]
636            for i, p in enumerate(post_schema):
637                if isinstance(p, exp.SqlSecurityProperty):
638                    post_schema.pop(i)
639                    locations[self.SQL_SECURITY_VIEW_LOCATION].append(p)
640                    break
641
642        return locations
643
644    def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
645        persisted = "STORED" if expression.args.get("persisted") else "VIRTUAL"
646        return f"GENERATED ALWAYS AS ({self.sql(expression.this.unnest())}) {persisted}"
647
648    def array_sql(self, expression: exp.Array) -> str:
649        self.unsupported("Arrays are not supported by MySQL")
650        return self.function_fallback_sql(expression)
651
652    def arraycontainsall_sql(self, expression: exp.ArrayContainsAll) -> str:
653        self.unsupported("Array operations are not supported by MySQL")
654        return self.function_fallback_sql(expression)
655
656    def arraycontainedby_sql(self, expression: exp.ArrayContainedBy) -> str:
657        self.unsupported("Array operations are not supported by MySQL")
658        return self.function_fallback_sql(expression)
659
660    def dpipe_sql(self, expression: exp.DPipe) -> str:
661        return self.func("CONCAT", *expression.flatten())
662
663    def extract_sql(self, expression: exp.Extract) -> str:
664        unit = expression.name
665        if unit and unit.lower() == "epoch":
666            return self.func("UNIX_TIMESTAMP", expression.expression)
667
668        return super().extract_sql(expression)
669
670    def datatype_sql(self, expression: exp.DataType) -> str:
671        if (
672            self.VARCHAR_REQUIRES_SIZE
673            and expression.is_type(exp.DType.VARCHAR)
674            and not expression.expressions
675        ):
676            # `VARCHAR` must always have a size - if it doesn't, we always generate `TEXT`
677            return "TEXT"
678
679        # https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html
680        result = super().datatype_sql(expression)
681        if expression.this in self.UNSIGNED_TYPE_MAPPING:
682            result = f"{result} UNSIGNED"
683
684        return result
685
686    def jsonarraycontains_sql(self, expression: exp.JSONArrayContains) -> str:
687        return f"{self.sql(expression, 'this')} MEMBER OF({self.sql(expression, 'expression')})"
688
689    def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
690        if expression.to.this in self.TIMESTAMP_FUNC_TYPES:
691            return self.func("TIMESTAMP", expression.this)
692
693        to = self.CAST_MAPPING.get(expression.to.this)
694
695        if to:
696            expression.to.set("this", to)
697        return super().cast_sql(expression)
698
699    def show_sql(self, expression: exp.Show) -> str:
700        this = f" {expression.name}"
701        full = " FULL" if expression.args.get("full") else ""
702        global_ = " GLOBAL" if expression.args.get("global_") else ""
703
704        target = self.sql(expression, "target")
705        target = f" {target}" if target else ""
706        if expression.name in ("COLUMNS", "INDEX"):
707            target = f" FROM{target}"
708        elif expression.name == "GRANTS":
709            target = f" FOR{target}"
710        elif expression.name in ("LINKS", "PARTITIONS"):
711            target = f" ON{target}" if target else ""
712        elif expression.name == "PROJECTIONS":
713            target = f" ON TABLE{target}" if target else ""
714
715        db = self._prefixed_sql("FROM", expression, "db")
716
717        like = self._prefixed_sql("LIKE", expression, "like")
718        where = self.sql(expression, "where")
719
720        types = self.expressions(expression, key="types")
721        types = f" {types}" if types else types
722        query = self._prefixed_sql("FOR QUERY", expression, "query")
723
724        if expression.name == "PROFILE":
725            offset = self._prefixed_sql("OFFSET", expression, "offset")
726            limit = self._prefixed_sql("LIMIT", expression, "limit")
727        else:
728            offset = ""
729            limit = self._oldstyle_limit_sql(expression)
730
731        log = self._prefixed_sql("IN", expression, "log")
732        position = self._prefixed_sql("FROM", expression, "position")
733
734        channel = self._prefixed_sql("FOR CHANNEL", expression, "channel")
735
736        if expression.name == "ENGINE":
737            mutex_or_status = " MUTEX" if expression.args.get("mutex") else " STATUS"
738        else:
739            mutex_or_status = ""
740
741        for_table = self._prefixed_sql("FOR TABLE", expression, "for_table")
742        for_group = self._prefixed_sql("FOR GROUP", expression, "for_group")
743        for_user = self._prefixed_sql("FOR USER", expression, "for_user")
744        for_role = self._prefixed_sql("FOR ROLE", expression, "for_role")
745        into_outfile = self._prefixed_sql("INTO OUTFILE", expression, "into_outfile")
746        json = " JSON" if expression.args.get("json") else ""
747
748        return f"SHOW{full}{global_}{this}{json}{target}{for_table}{types}{db}{query}{log}{position}{channel}{mutex_or_status}{like}{where}{offset}{limit}{for_group}{for_user}{for_role}{into_outfile}"
749
750    def alterrename_sql(self, expression: exp.AlterRename, include_to: bool = True) -> str:
751        """To avoid TO keyword in ALTER ... RENAME statements.
752        It's moved from Doris, because it's the same for all MySQL, Doris, and StarRocks.
753        """
754        return super().alterrename_sql(expression, include_to=False)
755
756    def altercolumn_sql(self, expression: exp.AlterColumn) -> str:
757        dtype = self.sql(expression, "dtype")
758        if not dtype:
759            return super().altercolumn_sql(expression)
760
761        this = self.sql(expression, "this")
762        return f"MODIFY COLUMN {this} {dtype}"
763
764    def _prefixed_sql(self, prefix: str, expression: exp.Expr, arg: str) -> str:
765        sql = self.sql(expression, arg)
766        return f" {prefix} {sql}" if sql else ""
767
768    def _oldstyle_limit_sql(self, expression: exp.Show) -> str:
769        limit = self.sql(expression, "limit")
770        offset = self.sql(expression, "offset")
771        if limit:
772            limit_offset = f"{offset}, {limit}" if offset else limit
773            return f" LIMIT {limit_offset}"
774        return ""
775
776    def timestamptrunc_sql(self, expression: exp.TimestampTrunc) -> str:
777        unit = expression.args.get("unit")
778
779        # Pick an old-enough date to avoid negative timestamp diffs
780        start_ts = "'0000-01-01 00:00:00'"
781
782        # Source: https://stackoverflow.com/a/32955740
783        timestamp_diff = build_date_delta(exp.TimestampDiff)([unit, start_ts, expression.this])
784        interval = exp.Interval(this=timestamp_diff, unit=unit)
785        dateadd = build_date_delta_with_interval(exp.DateAdd)([start_ts, interval])
786
787        return self.sql(dateadd)
788
789    def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
790        from_tz = expression.args.get("source_tz")
791        to_tz = expression.args.get("target_tz")
792        dt = expression.args.get("timestamp")
793
794        return self.func("CONVERT_TZ", dt, from_tz, to_tz)
795
796    def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
797        self.unsupported("AT TIME ZONE is not supported by MySQL")
798        return self.sql(expression.this)
799
800    def isascii_sql(self, expression: exp.IsAscii) -> str:
801        return f"REGEXP_LIKE({self.sql(expression.this)}, '^[[:ascii:]]*$')"
802
803    def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
804        # https://dev.mysql.com/doc/refman/8.4/en/window-function-descriptions.html
805        self.unsupported("MySQL does not support IGNORE NULLS.")
806        return self.sql(expression.this)
807
808    @unsupported_args("this")
809    def currentschema_sql(self, expression: exp.CurrentSchema) -> str:
810        return self.func("SCHEMA")
811
812    def partition_sql(self, expression: exp.Partition) -> str:
813        parent = expression.parent
814        if isinstance(parent, (exp.PartitionByRangeProperty, exp.PartitionByListProperty)):
815            return self.expressions(expression, flat=True)
816        return super().partition_sql(expression)
817
818    def _partition_by_sql(
819        self, expression: exp.PartitionByRangeProperty | exp.PartitionByListProperty, kind: str
820    ) -> str:
821        partitions = self.expressions(expression, key="partition_expressions", flat=True)
822        create = self.expressions(expression, key="create_expressions", flat=True)
823        return f"PARTITION BY {kind} ({partitions}) ({create})"
824
825    def partitionbyrangeproperty_sql(self, expression: exp.PartitionByRangeProperty) -> str:
826        return self._partition_by_sql(expression, "RANGE")
827
828    def partitionbylistproperty_sql(self, expression: exp.PartitionByListProperty) -> str:
829        return self._partition_by_sql(expression, "LIST")
830
831    def partitionlist_sql(self, expression: exp.PartitionList) -> str:
832        name = self.sql(expression, "this")
833        values = self.expressions(expression, flat=True)
834        return f"PARTITION {name} VALUES IN ({values})"
835
836    def partitionrange_sql(self, expression: exp.PartitionRange) -> str:
837        name = self.sql(expression, "this")
838        values = self.expressions(expression, flat=True)
839        return f"PARTITION {name} VALUES LESS THAN ({values})"