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