sqlglot.dialects.mysql

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, parser, tokens, transforms
  6from sqlglot.dialects.dialect import (
  7    Dialect,
  8    NormalizationStrategy,
  9    arrow_json_extract_sql,
 10    date_add_interval_sql,
 11    datestrtodate_sql,
 12    format_time_lambda,
 13    isnull_to_is_null,
 14    locate_to_strposition,
 15    max_or_greatest,
 16    min_or_least,
 17    no_ilike_sql,
 18    no_paren_current_date_sql,
 19    no_pivot_sql,
 20    no_tablesample_sql,
 21    no_trycast_sql,
 22    parse_date_delta,
 23    parse_date_delta_with_interval,
 24    rename_func,
 25    strposition_to_locate_sql,
 26)
 27from sqlglot.helper import seq_get
 28from sqlglot.tokens import TokenType
 29
 30
 31def _show_parser(*args: t.Any, **kwargs: t.Any) -> t.Callable[[MySQL.Parser], exp.Show]:
 32    def _parse(self: MySQL.Parser) -> exp.Show:
 33        return self._parse_show_mysql(*args, **kwargs)
 34
 35    return _parse
 36
 37
 38def _date_trunc_sql(self: MySQL.Generator, expression: exp.DateTrunc) -> str:
 39    expr = self.sql(expression, "this")
 40    unit = expression.text("unit").upper()
 41
 42    if unit == "DAY":
 43        return f"DATE({expr})"
 44
 45    if unit == "WEEK":
 46        concat = f"CONCAT(YEAR({expr}), ' ', WEEK({expr}, 1), ' 1')"
 47        date_format = "%Y %u %w"
 48    elif unit == "MONTH":
 49        concat = f"CONCAT(YEAR({expr}), ' ', MONTH({expr}), ' 1')"
 50        date_format = "%Y %c %e"
 51    elif unit == "QUARTER":
 52        concat = f"CONCAT(YEAR({expr}), ' ', QUARTER({expr}) * 3 - 2, ' 1')"
 53        date_format = "%Y %c %e"
 54    elif unit == "YEAR":
 55        concat = f"CONCAT(YEAR({expr}), ' 1 1')"
 56        date_format = "%Y %c %e"
 57    else:
 58        self.unsupported(f"Unexpected interval unit: {unit}")
 59        return f"DATE({expr})"
 60
 61    return f"STR_TO_DATE({concat}, '{date_format}')"
 62
 63
 64# All specifiers for time parts (as opposed to date parts)
 65# https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
 66TIME_SPECIFIERS = {"f", "H", "h", "I", "i", "k", "l", "p", "r", "S", "s", "T"}
 67
 68
 69def _has_time_specifier(date_format: str) -> bool:
 70    i = 0
 71    length = len(date_format)
 72
 73    while i < length:
 74        if date_format[i] == "%":
 75            i += 1
 76            if i < length and date_format[i] in TIME_SPECIFIERS:
 77                return True
 78        i += 1
 79    return False
 80
 81
 82def _str_to_date(args: t.List) -> exp.StrToDate | exp.StrToTime:
 83    mysql_date_format = seq_get(args, 1)
 84    date_format = MySQL.format_time(mysql_date_format)
 85    this = seq_get(args, 0)
 86
 87    if mysql_date_format and _has_time_specifier(mysql_date_format.name):
 88        return exp.StrToTime(this=this, format=date_format)
 89
 90    return exp.StrToDate(this=this, format=date_format)
 91
 92
 93def _str_to_date_sql(
 94    self: MySQL.Generator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate
 95) -> str:
 96    date_format = self.format_time(expression)
 97    return f"STR_TO_DATE({self.sql(expression.this)}, {date_format})"
 98
 99
100def _trim_sql(self: MySQL.Generator, expression: exp.Trim) -> str:
101    target = self.sql(expression, "this")
102    trim_type = self.sql(expression, "position")
103    remove_chars = self.sql(expression, "expression")
104
105    # Use TRIM/LTRIM/RTRIM syntax if the expression isn't mysql-specific
106    if not remove_chars:
107        return self.trim_sql(expression)
108
109    trim_type = f"{trim_type} " if trim_type else ""
110    remove_chars = f"{remove_chars} " if remove_chars else ""
111    from_part = "FROM " if trim_type or remove_chars else ""
112    return f"TRIM({trim_type}{remove_chars}{from_part}{target})"
113
114
115def _date_add_sql(
116    kind: str,
117) -> t.Callable[[MySQL.Generator, exp.Expression], str]:
118    def func(self: MySQL.Generator, expression: exp.Expression) -> str:
119        this = self.sql(expression, "this")
120        unit = expression.text("unit").upper() or "DAY"
121        return (
122            f"DATE_{kind}({this}, {self.sql(exp.Interval(this=expression.expression, unit=unit))})"
123        )
124
125    return func
126
127
128def _ts_or_ds_to_date_sql(self: MySQL.Generator, expression: exp.TsOrDsToDate) -> str:
129    time_format = expression.args.get("format")
130    if time_format:
131        return _str_to_date_sql(self, expression)
132    return f"DATE({self.sql(expression, 'this')})"
133
134
135def _remove_ts_or_ds_to_date(
136    to_sql: t.Optional[t.Callable[[MySQL.Generator, exp.Expression], str]] = None,
137    args: t.Tuple[str, ...] = ("this",),
138) -> t.Callable[[MySQL.Generator, exp.Func], str]:
139    def func(self: MySQL.Generator, expression: exp.Func) -> str:
140        for arg_key in args:
141            arg = expression.args.get(arg_key)
142            if isinstance(arg, exp.TsOrDsToDate) and not arg.args.get("format"):
143                expression.set(arg_key, arg.this)
144
145        return to_sql(self, expression) if to_sql else self.function_fallback_sql(expression)
146
147    return func
148
149
150class MySQL(Dialect):
151    # https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
152    IDENTIFIERS_CAN_START_WITH_DIGIT = True
153
154    # We default to treating all identifiers as case-sensitive, since it matches MySQL's
155    # behavior on Linux systems. For MacOS and Windows systems, one can override this
156    # setting by specifying `dialect="mysql, normalization_strategy = lowercase"`.
157    #
158    # See also https://dev.mysql.com/doc/refman/8.2/en/identifier-case-sensitivity.html
159    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_SENSITIVE
160
161    TIME_FORMAT = "'%Y-%m-%d %T'"
162    DPIPE_IS_STRING_CONCAT = False
163    SUPPORTS_USER_DEFINED_TYPES = False
164    SUPPORTS_SEMI_ANTI_JOIN = False
165    SAFE_DIVISION = True
166
167    # https://prestodb.io/docs/current/functions/datetime.html#mysql-date-functions
168    TIME_MAPPING = {
169        "%M": "%B",
170        "%c": "%-m",
171        "%e": "%-d",
172        "%h": "%I",
173        "%i": "%M",
174        "%s": "%S",
175        "%u": "%W",
176        "%k": "%-H",
177        "%l": "%-I",
178        "%T": "%H:%M:%S",
179        "%W": "%a",
180    }
181
182    class Tokenizer(tokens.Tokenizer):
183        QUOTES = ["'", '"']
184        COMMENTS = ["--", "#", ("/*", "*/")]
185        IDENTIFIERS = ["`"]
186        STRING_ESCAPES = ["'", '"', "\\"]
187        BIT_STRINGS = [("b'", "'"), ("B'", "'"), ("0b", "")]
188        HEX_STRINGS = [("x'", "'"), ("X'", "'"), ("0x", "")]
189
190        KEYWORDS = {
191            **tokens.Tokenizer.KEYWORDS,
192            "CHARSET": TokenType.CHARACTER_SET,
193            "ENUM": TokenType.ENUM,
194            "FORCE": TokenType.FORCE,
195            "IGNORE": TokenType.IGNORE,
196            "LOCK TABLES": TokenType.COMMAND,
197            "LONGBLOB": TokenType.LONGBLOB,
198            "LONGTEXT": TokenType.LONGTEXT,
199            "MEDIUMBLOB": TokenType.MEDIUMBLOB,
200            "TINYBLOB": TokenType.TINYBLOB,
201            "TINYTEXT": TokenType.TINYTEXT,
202            "MEDIUMTEXT": TokenType.MEDIUMTEXT,
203            "MEDIUMINT": TokenType.MEDIUMINT,
204            "MEMBER OF": TokenType.MEMBER_OF,
205            "SEPARATOR": TokenType.SEPARATOR,
206            "START": TokenType.BEGIN,
207            "SIGNED": TokenType.BIGINT,
208            "SIGNED INTEGER": TokenType.BIGINT,
209            "UNLOCK TABLES": TokenType.COMMAND,
210            "UNSIGNED": TokenType.UBIGINT,
211            "UNSIGNED INTEGER": TokenType.UBIGINT,
212            "YEAR": TokenType.YEAR,
213            "_ARMSCII8": TokenType.INTRODUCER,
214            "_ASCII": TokenType.INTRODUCER,
215            "_BIG5": TokenType.INTRODUCER,
216            "_BINARY": TokenType.INTRODUCER,
217            "_CP1250": TokenType.INTRODUCER,
218            "_CP1251": TokenType.INTRODUCER,
219            "_CP1256": TokenType.INTRODUCER,
220            "_CP1257": TokenType.INTRODUCER,
221            "_CP850": TokenType.INTRODUCER,
222            "_CP852": TokenType.INTRODUCER,
223            "_CP866": TokenType.INTRODUCER,
224            "_CP932": TokenType.INTRODUCER,
225            "_DEC8": TokenType.INTRODUCER,
226            "_EUCJPMS": TokenType.INTRODUCER,
227            "_EUCKR": TokenType.INTRODUCER,
228            "_GB18030": TokenType.INTRODUCER,
229            "_GB2312": TokenType.INTRODUCER,
230            "_GBK": TokenType.INTRODUCER,
231            "_GEOSTD8": TokenType.INTRODUCER,
232            "_GREEK": TokenType.INTRODUCER,
233            "_HEBREW": TokenType.INTRODUCER,
234            "_HP8": TokenType.INTRODUCER,
235            "_KEYBCS2": TokenType.INTRODUCER,
236            "_KOI8R": TokenType.INTRODUCER,
237            "_KOI8U": TokenType.INTRODUCER,
238            "_LATIN1": TokenType.INTRODUCER,
239            "_LATIN2": TokenType.INTRODUCER,
240            "_LATIN5": TokenType.INTRODUCER,
241            "_LATIN7": TokenType.INTRODUCER,
242            "_MACCE": TokenType.INTRODUCER,
243            "_MACROMAN": TokenType.INTRODUCER,
244            "_SJIS": TokenType.INTRODUCER,
245            "_SWE7": TokenType.INTRODUCER,
246            "_TIS620": TokenType.INTRODUCER,
247            "_UCS2": TokenType.INTRODUCER,
248            "_UJIS": TokenType.INTRODUCER,
249            # https://dev.mysql.com/doc/refman/8.0/en/string-literals.html
250            "_UTF8": TokenType.INTRODUCER,
251            "_UTF16": TokenType.INTRODUCER,
252            "_UTF16LE": TokenType.INTRODUCER,
253            "_UTF32": TokenType.INTRODUCER,
254            "_UTF8MB3": TokenType.INTRODUCER,
255            "_UTF8MB4": TokenType.INTRODUCER,
256            "@@": TokenType.SESSION_PARAMETER,
257        }
258
259        COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}
260
261    class Parser(parser.Parser):
262        FUNC_TOKENS = {
263            *parser.Parser.FUNC_TOKENS,
264            TokenType.DATABASE,
265            TokenType.SCHEMA,
266            TokenType.VALUES,
267        }
268
269        CONJUNCTION = {
270            **parser.Parser.CONJUNCTION,
271            TokenType.DAMP: exp.And,
272            TokenType.XOR: exp.Xor,
273            TokenType.DPIPE: exp.Or,
274        }
275
276        TABLE_ALIAS_TOKENS = (
277            parser.Parser.TABLE_ALIAS_TOKENS - parser.Parser.TABLE_INDEX_HINT_TOKENS
278        )
279
280        RANGE_PARSERS = {
281            **parser.Parser.RANGE_PARSERS,
282            TokenType.MEMBER_OF: lambda self, this: self.expression(
283                exp.JSONArrayContains,
284                this=this,
285                expression=self._parse_wrapped(self._parse_expression),
286            ),
287        }
288
289        FUNCTIONS = {
290            **parser.Parser.FUNCTIONS,
291            "DATE": lambda args: exp.TsOrDsToDate(this=seq_get(args, 0)),
292            "DATE_ADD": parse_date_delta_with_interval(exp.DateAdd),
293            "DATE_FORMAT": format_time_lambda(exp.TimeToStr, "mysql"),
294            "DATE_SUB": parse_date_delta_with_interval(exp.DateSub),
295            "DAY": lambda args: exp.Day(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
296            "DAYOFMONTH": lambda args: exp.DayOfMonth(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
297            "DAYOFWEEK": lambda args: exp.DayOfWeek(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
298            "DAYOFYEAR": lambda args: exp.DayOfYear(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
299            "INSTR": lambda args: exp.StrPosition(substr=seq_get(args, 1), this=seq_get(args, 0)),
300            "ISNULL": isnull_to_is_null,
301            "LOCATE": locate_to_strposition,
302            "MAKETIME": exp.TimeFromParts.from_arg_list,
303            "MONTH": lambda args: exp.Month(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
304            "MONTHNAME": lambda args: exp.TimeToStr(
305                this=exp.TsOrDsToDate(this=seq_get(args, 0)),
306                format=exp.Literal.string("%B"),
307            ),
308            "STR_TO_DATE": _str_to_date,
309            "TIMESTAMPDIFF": parse_date_delta(exp.TimestampDiff),
310            "TO_DAYS": lambda args: exp.paren(
311                exp.DateDiff(
312                    this=exp.TsOrDsToDate(this=seq_get(args, 0)),
313                    expression=exp.TsOrDsToDate(this=exp.Literal.string("0000-01-01")),
314                    unit=exp.var("DAY"),
315                )
316                + 1
317            ),
318            "WEEK": lambda args: exp.Week(
319                this=exp.TsOrDsToDate(this=seq_get(args, 0)), mode=seq_get(args, 1)
320            ),
321            "WEEKOFYEAR": lambda args: exp.WeekOfYear(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
322            "YEAR": lambda args: exp.Year(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
323        }
324
325        FUNCTION_PARSERS = {
326            **parser.Parser.FUNCTION_PARSERS,
327            "CHAR": lambda self: self._parse_chr(),
328            "GROUP_CONCAT": lambda self: self.expression(
329                exp.GroupConcat,
330                this=self._parse_lambda(),
331                separator=self._match(TokenType.SEPARATOR) and self._parse_field(),
332            ),
333            # https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values
334            "VALUES": lambda self: self.expression(
335                exp.Anonymous, this="VALUES", expressions=[self._parse_id_var()]
336            ),
337        }
338
339        STATEMENT_PARSERS = {
340            **parser.Parser.STATEMENT_PARSERS,
341            TokenType.SHOW: lambda self: self._parse_show(),
342        }
343
344        SHOW_PARSERS = {
345            "BINARY LOGS": _show_parser("BINARY LOGS"),
346            "MASTER LOGS": _show_parser("BINARY LOGS"),
347            "BINLOG EVENTS": _show_parser("BINLOG EVENTS"),
348            "CHARACTER SET": _show_parser("CHARACTER SET"),
349            "CHARSET": _show_parser("CHARACTER SET"),
350            "COLLATION": _show_parser("COLLATION"),
351            "FULL COLUMNS": _show_parser("COLUMNS", target="FROM", full=True),
352            "COLUMNS": _show_parser("COLUMNS", target="FROM"),
353            "CREATE DATABASE": _show_parser("CREATE DATABASE", target=True),
354            "CREATE EVENT": _show_parser("CREATE EVENT", target=True),
355            "CREATE FUNCTION": _show_parser("CREATE FUNCTION", target=True),
356            "CREATE PROCEDURE": _show_parser("CREATE PROCEDURE", target=True),
357            "CREATE TABLE": _show_parser("CREATE TABLE", target=True),
358            "CREATE TRIGGER": _show_parser("CREATE TRIGGER", target=True),
359            "CREATE VIEW": _show_parser("CREATE VIEW", target=True),
360            "DATABASES": _show_parser("DATABASES"),
361            "SCHEMAS": _show_parser("DATABASES"),
362            "ENGINE": _show_parser("ENGINE", target=True),
363            "STORAGE ENGINES": _show_parser("ENGINES"),
364            "ENGINES": _show_parser("ENGINES"),
365            "ERRORS": _show_parser("ERRORS"),
366            "EVENTS": _show_parser("EVENTS"),
367            "FUNCTION CODE": _show_parser("FUNCTION CODE", target=True),
368            "FUNCTION STATUS": _show_parser("FUNCTION STATUS"),
369            "GRANTS": _show_parser("GRANTS", target="FOR"),
370            "INDEX": _show_parser("INDEX", target="FROM"),
371            "MASTER STATUS": _show_parser("MASTER STATUS"),
372            "OPEN TABLES": _show_parser("OPEN TABLES"),
373            "PLUGINS": _show_parser("PLUGINS"),
374            "PROCEDURE CODE": _show_parser("PROCEDURE CODE", target=True),
375            "PROCEDURE STATUS": _show_parser("PROCEDURE STATUS"),
376            "PRIVILEGES": _show_parser("PRIVILEGES"),
377            "FULL PROCESSLIST": _show_parser("PROCESSLIST", full=True),
378            "PROCESSLIST": _show_parser("PROCESSLIST"),
379            "PROFILE": _show_parser("PROFILE"),
380            "PROFILES": _show_parser("PROFILES"),
381            "RELAYLOG EVENTS": _show_parser("RELAYLOG EVENTS"),
382            "REPLICAS": _show_parser("REPLICAS"),
383            "SLAVE HOSTS": _show_parser("REPLICAS"),
384            "REPLICA STATUS": _show_parser("REPLICA STATUS"),
385            "SLAVE STATUS": _show_parser("REPLICA STATUS"),
386            "GLOBAL STATUS": _show_parser("STATUS", global_=True),
387            "SESSION STATUS": _show_parser("STATUS"),
388            "STATUS": _show_parser("STATUS"),
389            "TABLE STATUS": _show_parser("TABLE STATUS"),
390            "FULL TABLES": _show_parser("TABLES", full=True),
391            "TABLES": _show_parser("TABLES"),
392            "TRIGGERS": _show_parser("TRIGGERS"),
393            "GLOBAL VARIABLES": _show_parser("VARIABLES", global_=True),
394            "SESSION VARIABLES": _show_parser("VARIABLES"),
395            "VARIABLES": _show_parser("VARIABLES"),
396            "WARNINGS": _show_parser("WARNINGS"),
397        }
398
399        SET_PARSERS = {
400            **parser.Parser.SET_PARSERS,
401            "PERSIST": lambda self: self._parse_set_item_assignment("PERSIST"),
402            "PERSIST_ONLY": lambda self: self._parse_set_item_assignment("PERSIST_ONLY"),
403            "CHARACTER SET": lambda self: self._parse_set_item_charset("CHARACTER SET"),
404            "CHARSET": lambda self: self._parse_set_item_charset("CHARACTER SET"),
405            "NAMES": lambda self: self._parse_set_item_names(),
406        }
407
408        CONSTRAINT_PARSERS = {
409            **parser.Parser.CONSTRAINT_PARSERS,
410            "FULLTEXT": lambda self: self._parse_index_constraint(kind="FULLTEXT"),
411            "INDEX": lambda self: self._parse_index_constraint(),
412            "KEY": lambda self: self._parse_index_constraint(),
413            "SPATIAL": lambda self: self._parse_index_constraint(kind="SPATIAL"),
414        }
415
416        SCHEMA_UNNAMED_CONSTRAINTS = {
417            *parser.Parser.SCHEMA_UNNAMED_CONSTRAINTS,
418            "FULLTEXT",
419            "INDEX",
420            "KEY",
421            "SPATIAL",
422        }
423
424        PROFILE_TYPES = {
425            "ALL",
426            "BLOCK IO",
427            "CONTEXT SWITCHES",
428            "CPU",
429            "IPC",
430            "MEMORY",
431            "PAGE FAULTS",
432            "SOURCE",
433            "SWAPS",
434        }
435
436        TYPE_TOKENS = {
437            *parser.Parser.TYPE_TOKENS,
438            TokenType.SET,
439        }
440
441        ENUM_TYPE_TOKENS = {
442            *parser.Parser.ENUM_TYPE_TOKENS,
443            TokenType.SET,
444        }
445
446        LOG_DEFAULTS_TO_LN = True
447        STRING_ALIASES = True
448        VALUES_FOLLOWED_BY_PAREN = False
449
450        def _parse_primary_key_part(self) -> t.Optional[exp.Expression]:
451            this = self._parse_id_var()
452            if not self._match(TokenType.L_PAREN):
453                return this
454
455            expression = self._parse_number()
456            self._match_r_paren()
457            return self.expression(exp.ColumnPrefix, this=this, expression=expression)
458
459        def _parse_index_constraint(
460            self, kind: t.Optional[str] = None
461        ) -> exp.IndexColumnConstraint:
462            if kind:
463                self._match_texts(("INDEX", "KEY"))
464
465            this = self._parse_id_var(any_token=False)
466            index_type = self._match(TokenType.USING) and self._advance_any() and self._prev.text
467            schema = self._parse_schema()
468
469            options = []
470            while True:
471                if self._match_text_seq("KEY_BLOCK_SIZE"):
472                    self._match(TokenType.EQ)
473                    opt = exp.IndexConstraintOption(key_block_size=self._parse_number())
474                elif self._match(TokenType.USING):
475                    opt = exp.IndexConstraintOption(using=self._advance_any() and self._prev.text)
476                elif self._match_text_seq("WITH", "PARSER"):
477                    opt = exp.IndexConstraintOption(parser=self._parse_var(any_token=True))
478                elif self._match(TokenType.COMMENT):
479                    opt = exp.IndexConstraintOption(comment=self._parse_string())
480                elif self._match_text_seq("VISIBLE"):
481                    opt = exp.IndexConstraintOption(visible=True)
482                elif self._match_text_seq("INVISIBLE"):
483                    opt = exp.IndexConstraintOption(visible=False)
484                elif self._match_text_seq("ENGINE_ATTRIBUTE"):
485                    self._match(TokenType.EQ)
486                    opt = exp.IndexConstraintOption(engine_attr=self._parse_string())
487                elif self._match_text_seq("ENGINE_ATTRIBUTE"):
488                    self._match(TokenType.EQ)
489                    opt = exp.IndexConstraintOption(engine_attr=self._parse_string())
490                elif self._match_text_seq("SECONDARY_ENGINE_ATTRIBUTE"):
491                    self._match(TokenType.EQ)
492                    opt = exp.IndexConstraintOption(secondary_engine_attr=self._parse_string())
493                else:
494                    opt = None
495
496                if not opt:
497                    break
498
499                options.append(opt)
500
501            return self.expression(
502                exp.IndexColumnConstraint,
503                this=this,
504                schema=schema,
505                kind=kind,
506                index_type=index_type,
507                options=options,
508            )
509
510        def _parse_show_mysql(
511            self,
512            this: str,
513            target: bool | str = False,
514            full: t.Optional[bool] = None,
515            global_: t.Optional[bool] = None,
516        ) -> exp.Show:
517            if target:
518                if isinstance(target, str):
519                    self._match_text_seq(target)
520                target_id = self._parse_id_var()
521            else:
522                target_id = None
523
524            log = self._parse_string() if self._match_text_seq("IN") else None
525
526            if this in ("BINLOG EVENTS", "RELAYLOG EVENTS"):
527                position = self._parse_number() if self._match_text_seq("FROM") else None
528                db = None
529            else:
530                position = None
531                db = None
532
533                if self._match(TokenType.FROM):
534                    db = self._parse_id_var()
535                elif self._match(TokenType.DOT):
536                    db = target_id
537                    target_id = self._parse_id_var()
538
539            channel = self._parse_id_var() if self._match_text_seq("FOR", "CHANNEL") else None
540
541            like = self._parse_string() if self._match_text_seq("LIKE") else None
542            where = self._parse_where()
543
544            if this == "PROFILE":
545                types = self._parse_csv(lambda: self._parse_var_from_options(self.PROFILE_TYPES))
546                query = self._parse_number() if self._match_text_seq("FOR", "QUERY") else None
547                offset = self._parse_number() if self._match_text_seq("OFFSET") else None
548                limit = self._parse_number() if self._match_text_seq("LIMIT") else None
549            else:
550                types, query = None, None
551                offset, limit = self._parse_oldstyle_limit()
552
553            mutex = True if self._match_text_seq("MUTEX") else None
554            mutex = False if self._match_text_seq("STATUS") else mutex
555
556            return self.expression(
557                exp.Show,
558                this=this,
559                target=target_id,
560                full=full,
561                log=log,
562                position=position,
563                db=db,
564                channel=channel,
565                like=like,
566                where=where,
567                types=types,
568                query=query,
569                offset=offset,
570                limit=limit,
571                mutex=mutex,
572                **{"global": global_},  # type: ignore
573            )
574
575        def _parse_oldstyle_limit(
576            self,
577        ) -> t.Tuple[t.Optional[exp.Expression], t.Optional[exp.Expression]]:
578            limit = None
579            offset = None
580            if self._match_text_seq("LIMIT"):
581                parts = self._parse_csv(self._parse_number)
582                if len(parts) == 1:
583                    limit = parts[0]
584                elif len(parts) == 2:
585                    limit = parts[1]
586                    offset = parts[0]
587
588            return offset, limit
589
590        def _parse_set_item_charset(self, kind: str) -> exp.Expression:
591            this = self._parse_string() or self._parse_id_var()
592            return self.expression(exp.SetItem, this=this, kind=kind)
593
594        def _parse_set_item_names(self) -> exp.Expression:
595            charset = self._parse_string() or self._parse_id_var()
596            if self._match_text_seq("COLLATE"):
597                collate = self._parse_string() or self._parse_id_var()
598            else:
599                collate = None
600
601            return self.expression(exp.SetItem, this=charset, collate=collate, kind="NAMES")
602
603        def _parse_type(self, parse_interval: bool = True) -> t.Optional[exp.Expression]:
604            # mysql binary is special and can work anywhere, even in order by operations
605            # it operates like a no paren func
606            if self._match(TokenType.BINARY, advance=False):
607                data_type = self._parse_types(check_func=True, allow_identifiers=False)
608
609                if isinstance(data_type, exp.DataType):
610                    return self.expression(exp.Cast, this=self._parse_column(), to=data_type)
611
612            return super()._parse_type(parse_interval=parse_interval)
613
614        def _parse_chr(self) -> t.Optional[exp.Expression]:
615            expressions = self._parse_csv(self._parse_conjunction)
616            kwargs: t.Dict[str, t.Any] = {"this": seq_get(expressions, 0)}
617
618            if len(expressions) > 1:
619                kwargs["expressions"] = expressions[1:]
620
621            if self._match(TokenType.USING):
622                kwargs["charset"] = self._parse_var()
623
624            return self.expression(exp.Chr, **kwargs)
625
626    class Generator(generator.Generator):
627        LOCKING_READS_SUPPORTED = True
628        NULL_ORDERING_SUPPORTED = None
629        JOIN_HINTS = False
630        TABLE_HINTS = True
631        DUPLICATE_KEY_UPDATE_WITH_SET = False
632        QUERY_HINT_SEP = " "
633        VALUES_AS_TABLE = False
634        NVL2_SUPPORTED = False
635        LAST_DAY_SUPPORTS_DATE_PART = False
636        JSON_TYPE_REQUIRED_FOR_EXTRACTION = True
637        JSON_PATH_BRACKETED_KEY_SUPPORTED = False
638        JSON_KEY_VALUE_PAIR_SEP = ","
639
640        TRANSFORMS = {
641            **generator.Generator.TRANSFORMS,
642            exp.CurrentDate: no_paren_current_date_sql,
643            exp.DateDiff: _remove_ts_or_ds_to_date(
644                lambda self, e: self.func("DATEDIFF", e.this, e.expression), ("this", "expression")
645            ),
646            exp.DateAdd: _remove_ts_or_ds_to_date(_date_add_sql("ADD")),
647            exp.DateStrToDate: datestrtodate_sql,
648            exp.DateSub: _remove_ts_or_ds_to_date(_date_add_sql("SUB")),
649            exp.DateTrunc: _date_trunc_sql,
650            exp.Day: _remove_ts_or_ds_to_date(),
651            exp.DayOfMonth: _remove_ts_or_ds_to_date(rename_func("DAYOFMONTH")),
652            exp.DayOfWeek: _remove_ts_or_ds_to_date(rename_func("DAYOFWEEK")),
653            exp.DayOfYear: _remove_ts_or_ds_to_date(rename_func("DAYOFYEAR")),
654            exp.GroupConcat: lambda self,
655            e: f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})""",
656            exp.ILike: no_ilike_sql,
657            exp.JSONExtractScalar: arrow_json_extract_sql,
658            exp.Max: max_or_greatest,
659            exp.Min: min_or_least,
660            exp.Month: _remove_ts_or_ds_to_date(),
661            exp.NullSafeEQ: lambda self, e: self.binary(e, "<=>"),
662            exp.NullSafeNEQ: lambda self, e: f"NOT {self.binary(e, '<=>')}",
663            exp.ParseJSON: lambda self, e: self.sql(e, "this"),
664            exp.Pivot: no_pivot_sql,
665            exp.Select: transforms.preprocess(
666                [
667                    transforms.eliminate_distinct_on,
668                    transforms.eliminate_semi_and_anti_joins,
669                    transforms.eliminate_qualify,
670                    transforms.eliminate_full_outer_join,
671                ]
672            ),
673            exp.StrPosition: strposition_to_locate_sql,
674            exp.StrToDate: _str_to_date_sql,
675            exp.StrToTime: _str_to_date_sql,
676            exp.Stuff: rename_func("INSERT"),
677            exp.TableSample: no_tablesample_sql,
678            exp.TimeFromParts: rename_func("MAKETIME"),
679            exp.TimestampAdd: date_add_interval_sql("DATE", "ADD"),
680            exp.TimestampDiff: lambda self, e: self.func(
681                "TIMESTAMPDIFF", e.text("unit"), e.expression, e.this
682            ),
683            exp.TimestampSub: date_add_interval_sql("DATE", "SUB"),
684            exp.TimeStrToUnix: rename_func("UNIX_TIMESTAMP"),
685            exp.TimeStrToTime: lambda self, e: self.sql(exp.cast(e.this, "datetime", copy=True)),
686            exp.TimeToStr: _remove_ts_or_ds_to_date(
687                lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e))
688            ),
689            exp.Trim: _trim_sql,
690            exp.TryCast: no_trycast_sql,
691            exp.TsOrDsAdd: _date_add_sql("ADD"),
692            exp.TsOrDsDiff: lambda self, e: self.func("DATEDIFF", e.this, e.expression),
693            exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
694            exp.Week: _remove_ts_or_ds_to_date(),
695            exp.WeekOfYear: _remove_ts_or_ds_to_date(rename_func("WEEKOFYEAR")),
696            exp.Year: _remove_ts_or_ds_to_date(),
697        }
698
699        UNSIGNED_TYPE_MAPPING = {
700            exp.DataType.Type.UBIGINT: "BIGINT",
701            exp.DataType.Type.UINT: "INT",
702            exp.DataType.Type.UMEDIUMINT: "MEDIUMINT",
703            exp.DataType.Type.USMALLINT: "SMALLINT",
704            exp.DataType.Type.UTINYINT: "TINYINT",
705            exp.DataType.Type.UDECIMAL: "DECIMAL",
706        }
707
708        TIMESTAMP_TYPE_MAPPING = {
709            exp.DataType.Type.TIMESTAMP: "DATETIME",
710            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
711            exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
712        }
713
714        TYPE_MAPPING = {
715            **generator.Generator.TYPE_MAPPING,
716            **UNSIGNED_TYPE_MAPPING,
717            **TIMESTAMP_TYPE_MAPPING,
718        }
719
720        TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMTEXT)
721        TYPE_MAPPING.pop(exp.DataType.Type.LONGTEXT)
722        TYPE_MAPPING.pop(exp.DataType.Type.TINYTEXT)
723        TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMBLOB)
724        TYPE_MAPPING.pop(exp.DataType.Type.LONGBLOB)
725        TYPE_MAPPING.pop(exp.DataType.Type.TINYBLOB)
726
727        PROPERTIES_LOCATION = {
728            **generator.Generator.PROPERTIES_LOCATION,
729            exp.TransientProperty: exp.Properties.Location.UNSUPPORTED,
730            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
731        }
732
733        LIMIT_FETCH = "LIMIT"
734
735        LIMIT_ONLY_LITERALS = True
736
737        # MySQL doesn't support many datatypes in cast.
738        # https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast
739        CAST_MAPPING = {
740            exp.DataType.Type.BIGINT: "SIGNED",
741            exp.DataType.Type.BOOLEAN: "SIGNED",
742            exp.DataType.Type.INT: "SIGNED",
743            exp.DataType.Type.TEXT: "CHAR",
744            exp.DataType.Type.UBIGINT: "UNSIGNED",
745            exp.DataType.Type.VARCHAR: "CHAR",
746        }
747
748        TIMESTAMP_FUNC_TYPES = {
749            exp.DataType.Type.TIMESTAMPTZ,
750            exp.DataType.Type.TIMESTAMPLTZ,
751        }
752
753        def datatype_sql(self, expression: exp.DataType) -> str:
754            # https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html
755            result = super().datatype_sql(expression)
756            if expression.this in self.UNSIGNED_TYPE_MAPPING:
757                result = f"{result} UNSIGNED"
758            return result
759
760        def xor_sql(self, expression: exp.Xor) -> str:
761            if expression.expressions:
762                return self.expressions(expression, sep=" XOR ")
763            return super().xor_sql(expression)
764
765        def jsonarraycontains_sql(self, expression: exp.JSONArrayContains) -> str:
766            return f"{self.sql(expression, 'this')} MEMBER OF({self.sql(expression, 'expression')})"
767
768        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
769            if expression.to.this in self.TIMESTAMP_FUNC_TYPES:
770                return self.func("TIMESTAMP", expression.this)
771
772            to = self.CAST_MAPPING.get(expression.to.this)
773
774            if to:
775                expression.to.set("this", to)
776            return super().cast_sql(expression)
777
778        def show_sql(self, expression: exp.Show) -> str:
779            this = f" {expression.name}"
780            full = " FULL" if expression.args.get("full") else ""
781            global_ = " GLOBAL" if expression.args.get("global") else ""
782
783            target = self.sql(expression, "target")
784            target = f" {target}" if target else ""
785            if expression.name in ("COLUMNS", "INDEX"):
786                target = f" FROM{target}"
787            elif expression.name == "GRANTS":
788                target = f" FOR{target}"
789
790            db = self._prefixed_sql("FROM", expression, "db")
791
792            like = self._prefixed_sql("LIKE", expression, "like")
793            where = self.sql(expression, "where")
794
795            types = self.expressions(expression, key="types")
796            types = f" {types}" if types else types
797            query = self._prefixed_sql("FOR QUERY", expression, "query")
798
799            if expression.name == "PROFILE":
800                offset = self._prefixed_sql("OFFSET", expression, "offset")
801                limit = self._prefixed_sql("LIMIT", expression, "limit")
802            else:
803                offset = ""
804                limit = self._oldstyle_limit_sql(expression)
805
806            log = self._prefixed_sql("IN", expression, "log")
807            position = self._prefixed_sql("FROM", expression, "position")
808
809            channel = self._prefixed_sql("FOR CHANNEL", expression, "channel")
810
811            if expression.name == "ENGINE":
812                mutex_or_status = " MUTEX" if expression.args.get("mutex") else " STATUS"
813            else:
814                mutex_or_status = ""
815
816            return f"SHOW{full}{global_}{this}{target}{types}{db}{query}{log}{position}{channel}{mutex_or_status}{like}{where}{offset}{limit}"
817
818        def altercolumn_sql(self, expression: exp.AlterColumn) -> str:
819            dtype = self.sql(expression, "dtype")
820            if not dtype:
821                return super().altercolumn_sql(expression)
822
823            this = self.sql(expression, "this")
824            return f"MODIFY COLUMN {this} {dtype}"
825
826        def _prefixed_sql(self, prefix: str, expression: exp.Expression, arg: str) -> str:
827            sql = self.sql(expression, arg)
828            return f" {prefix} {sql}" if sql else ""
829
830        def _oldstyle_limit_sql(self, expression: exp.Show) -> str:
831            limit = self.sql(expression, "limit")
832            offset = self.sql(expression, "offset")
833            if limit:
834                limit_offset = f"{offset}, {limit}" if offset else limit
835                return f" LIMIT {limit_offset}"
836            return ""
837
838        def chr_sql(self, expression: exp.Chr) -> str:
839            this = self.expressions(sqls=[expression.this] + expression.expressions)
840            charset = expression.args.get("charset")
841            using = f" USING {self.sql(charset)}" if charset else ""
842            return f"CHAR({this}{using})"