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