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