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