sqlglot.dialects.singlestore

   1import re
   2
   3from sqlglot import TokenType
   4import typing as t
   5
   6from sqlglot import exp
   7from sqlglot.dialects.dialect import (
   8    build_formatted_time,
   9    build_json_extract_path,
  10    json_extract_segments,
  11    json_path_key_only_name,
  12    rename_func,
  13    bool_xor_sql,
  14    count_if_to_sum,
  15    timestamptrunc_sql,
  16    date_add_interval_sql,
  17    timestampdiff_sql,
  18)
  19from sqlglot.dialects.mysql import MySQL, _remove_ts_or_ds_to_date, date_add_sql, _show_parser
  20from sqlglot.expressions import DataType
  21from sqlglot.generator import unsupported_args
  22from sqlglot.helper import seq_get
  23
  24
  25def cast_to_time6(
  26    expression: t.Optional[exp.Expression], time_type: DataType.Type = exp.DataType.Type.TIME
  27) -> exp.Cast:
  28    return exp.Cast(
  29        this=expression,
  30        to=exp.DataType.build(
  31            time_type,
  32            expressions=[exp.DataTypeParam(this=exp.Literal.number(6))],
  33        ),
  34    )
  35
  36
  37class SingleStore(MySQL):
  38    SUPPORTS_ORDER_BY_ALL = True
  39
  40    TIME_MAPPING: t.Dict[str, str] = {
  41        "D": "%u",  # Day of week (1-7)
  42        "DD": "%d",  # day of month (01-31)
  43        "DY": "%a",  # abbreviated name of day
  44        "HH": "%I",  # Hour of day (01-12)
  45        "HH12": "%I",  # alias for HH
  46        "HH24": "%H",  # Hour of day (00-23)
  47        "MI": "%M",  # Minute (00-59)
  48        "MM": "%m",  # Month (01-12; January = 01)
  49        "MON": "%b",  # Abbreviated name of month
  50        "MONTH": "%B",  # Name of month
  51        "SS": "%S",  # Second (00-59)
  52        "RR": "%y",  # 15
  53        "YY": "%y",  # 15
  54        "YYYY": "%Y",  # 2015
  55        "FF6": "%f",  # only 6 digits are supported in python formats
  56    }
  57
  58    VECTOR_TYPE_ALIASES = {
  59        "I8": "TINYINT",
  60        "I16": "SMALLINT",
  61        "I32": "INT",
  62        "I64": "BIGINT",
  63        "F32": "FLOAT",
  64        "F64": "DOUBLE",
  65    }
  66
  67    INVERSE_VECTOR_TYPE_ALIASES = {v: k for k, v in VECTOR_TYPE_ALIASES.items()}
  68
  69    class Tokenizer(MySQL.Tokenizer):
  70        BYTE_STRINGS = [("e'", "'"), ("E'", "'")]
  71
  72        KEYWORDS = {
  73            **MySQL.Tokenizer.KEYWORDS,
  74            "BSON": TokenType.JSONB,
  75            "GEOGRAPHYPOINT": TokenType.GEOGRAPHYPOINT,
  76            "TIMESTAMP": TokenType.TIMESTAMP,
  77            "UTC_DATE": TokenType.UTC_DATE,
  78            "UTC_TIME": TokenType.UTC_TIME,
  79            "UTC_TIMESTAMP": TokenType.UTC_TIMESTAMP,
  80            ":>": TokenType.COLON_GT,
  81            "!:>": TokenType.NCOLON_GT,
  82            "::$": TokenType.DCOLONDOLLAR,
  83            "::%": TokenType.DCOLONPERCENT,
  84        }
  85
  86    class Parser(MySQL.Parser):
  87        FUNCTIONS = {
  88            **MySQL.Parser.FUNCTIONS,
  89            "TO_DATE": build_formatted_time(exp.TsOrDsToDate, "singlestore"),
  90            "TO_TIMESTAMP": build_formatted_time(exp.StrToTime, "singlestore"),
  91            "TO_CHAR": build_formatted_time(exp.ToChar, "singlestore"),
  92            "STR_TO_DATE": build_formatted_time(exp.StrToDate, "mysql"),
  93            "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "mysql"),
  94            # The first argument of following functions is converted to TIME(6)
  95            # This is needed because exp.TimeToStr is converted to DATE_FORMAT
  96            # which interprets the first argument as DATETIME and fails to parse
  97            # string literals like '12:05:47' without a date part.
  98            "TIME_FORMAT": lambda args: exp.TimeToStr(
  99                this=cast_to_time6(seq_get(args, 0)),
 100                format=MySQL.format_time(seq_get(args, 1)),
 101            ),
 102            "HOUR": lambda args: exp.cast(
 103                exp.TimeToStr(
 104                    this=cast_to_time6(seq_get(args, 0)),
 105                    format=MySQL.format_time(exp.Literal.string("%k")),
 106                ),
 107                DataType.Type.INT,
 108            ),
 109            "MICROSECOND": lambda args: exp.cast(
 110                exp.TimeToStr(
 111                    this=cast_to_time6(seq_get(args, 0)),
 112                    format=MySQL.format_time(exp.Literal.string("%f")),
 113                ),
 114                DataType.Type.INT,
 115            ),
 116            "SECOND": lambda args: exp.cast(
 117                exp.TimeToStr(
 118                    this=cast_to_time6(seq_get(args, 0)),
 119                    format=MySQL.format_time(exp.Literal.string("%s")),
 120                ),
 121                DataType.Type.INT,
 122            ),
 123            "MINUTE": lambda args: exp.cast(
 124                exp.TimeToStr(
 125                    this=cast_to_time6(seq_get(args, 0)),
 126                    format=MySQL.format_time(exp.Literal.string("%i")),
 127                ),
 128                DataType.Type.INT,
 129            ),
 130            "MONTHNAME": lambda args: exp.TimeToStr(
 131                this=seq_get(args, 0),
 132                format=MySQL.format_time(exp.Literal.string("%M")),
 133            ),
 134            "WEEKDAY": lambda args: exp.paren(exp.DayOfWeek(this=seq_get(args, 0)) + 5, copy=False)
 135            % 7,
 136            "UNIX_TIMESTAMP": exp.StrToUnix.from_arg_list,
 137            "FROM_UNIXTIME": build_formatted_time(exp.UnixToTime, "mysql"),
 138            "TIME_BUCKET": lambda args: exp.DateBin(
 139                this=seq_get(args, 0),
 140                expression=seq_get(args, 1),
 141                origin=seq_get(args, 2),
 142            ),
 143            "BSON_EXTRACT_BSON": build_json_extract_path(exp.JSONBExtract),
 144            "BSON_EXTRACT_STRING": build_json_extract_path(
 145                exp.JSONBExtractScalar, json_type="STRING"
 146            ),
 147            "BSON_EXTRACT_DOUBLE": build_json_extract_path(
 148                exp.JSONBExtractScalar, json_type="DOUBLE"
 149            ),
 150            "BSON_EXTRACT_BIGINT": build_json_extract_path(
 151                exp.JSONBExtractScalar, json_type="BIGINT"
 152            ),
 153            "JSON_EXTRACT_JSON": build_json_extract_path(exp.JSONExtract),
 154            "JSON_EXTRACT_STRING": build_json_extract_path(
 155                exp.JSONExtractScalar, json_type="STRING"
 156            ),
 157            "JSON_EXTRACT_DOUBLE": build_json_extract_path(
 158                exp.JSONExtractScalar, json_type="DOUBLE"
 159            ),
 160            "JSON_EXTRACT_BIGINT": build_json_extract_path(
 161                exp.JSONExtractScalar, json_type="BIGINT"
 162            ),
 163            "JSON_ARRAY_CONTAINS_STRING": lambda args: exp.JSONArrayContains(
 164                this=seq_get(args, 1),
 165                expression=seq_get(args, 0),
 166                json_type="STRING",
 167            ),
 168            "JSON_ARRAY_CONTAINS_DOUBLE": lambda args: exp.JSONArrayContains(
 169                this=seq_get(args, 1),
 170                expression=seq_get(args, 0),
 171                json_type="DOUBLE",
 172            ),
 173            "JSON_ARRAY_CONTAINS_JSON": lambda args: exp.JSONArrayContains(
 174                this=seq_get(args, 1),
 175                expression=seq_get(args, 0),
 176                json_type="JSON",
 177            ),
 178            "JSON_PRETTY": exp.JSONFormat.from_arg_list,
 179            "JSON_BUILD_ARRAY": lambda args: exp.JSONArray(expressions=args),
 180            "JSON_BUILD_OBJECT": lambda args: exp.JSONObject(expressions=args),
 181            "DATE": exp.Date.from_arg_list,
 182            "DAYNAME": lambda args: exp.TimeToStr(
 183                this=seq_get(args, 0),
 184                format=MySQL.format_time(exp.Literal.string("%W")),
 185            ),
 186            "TIMESTAMPDIFF": lambda args: exp.TimestampDiff(
 187                this=seq_get(args, 2),
 188                expression=seq_get(args, 1),
 189                unit=seq_get(args, 0),
 190            ),
 191            "APPROX_COUNT_DISTINCT": exp.Hll.from_arg_list,
 192            "APPROX_PERCENTILE": lambda args, dialect: exp.ApproxQuantile(
 193                this=seq_get(args, 0),
 194                quantile=seq_get(args, 1),
 195                error_tolerance=seq_get(args, 2),
 196            ),
 197            "VARIANCE": exp.VariancePop.from_arg_list,
 198            "INSTR": exp.Contains.from_arg_list,
 199            "REGEXP_MATCH": lambda args: exp.RegexpExtractAll(
 200                this=seq_get(args, 0),
 201                expression=seq_get(args, 1),
 202                parameters=seq_get(args, 2),
 203            ),
 204            "REGEXP_SUBSTR": lambda args: exp.RegexpExtract(
 205                this=seq_get(args, 0),
 206                expression=seq_get(args, 1),
 207                position=seq_get(args, 2),
 208                occurrence=seq_get(args, 3),
 209                parameters=seq_get(args, 4),
 210            ),
 211            "REDUCE": lambda args: exp.Reduce(
 212                initial=seq_get(args, 0),
 213                this=seq_get(args, 1),
 214                merge=seq_get(args, 2),
 215            ),
 216        }
 217
 218        FUNCTION_PARSERS: t.Dict[str, t.Callable] = {
 219            **MySQL.Parser.FUNCTION_PARSERS,
 220            "JSON_AGG": lambda self: exp.JSONArrayAgg(
 221                this=self._parse_term(),
 222                order=self._parse_order(),
 223            ),
 224        }
 225
 226        NO_PAREN_FUNCTIONS = {
 227            **MySQL.Parser.NO_PAREN_FUNCTIONS,
 228            TokenType.UTC_DATE: exp.UtcDate,
 229            TokenType.UTC_TIME: exp.UtcTime,
 230            TokenType.UTC_TIMESTAMP: exp.UtcTimestamp,
 231        }
 232
 233        CAST_COLUMN_OPERATORS = {TokenType.COLON_GT, TokenType.NCOLON_GT}
 234
 235        COLUMN_OPERATORS = {
 236            **MySQL.Parser.COLUMN_OPERATORS,
 237            TokenType.COLON_GT: lambda self, this, to: self.expression(
 238                exp.Cast,
 239                this=this,
 240                to=to,
 241            ),
 242            TokenType.NCOLON_GT: lambda self, this, to: self.expression(
 243                exp.TryCast,
 244                this=this,
 245                to=to,
 246            ),
 247            TokenType.DCOLON: lambda self, this, path: build_json_extract_path(exp.JSONExtract)(
 248                [this, exp.Literal.string(path.name)]
 249            ),
 250            TokenType.DCOLONDOLLAR: lambda self, this, path: build_json_extract_path(
 251                exp.JSONExtractScalar, json_type="STRING"
 252            )([this, exp.Literal.string(path.name)]),
 253            TokenType.DCOLONPERCENT: lambda self, this, path: build_json_extract_path(
 254                exp.JSONExtractScalar, json_type="DOUBLE"
 255            )([this, exp.Literal.string(path.name)]),
 256        }
 257        COLUMN_OPERATORS.pop(TokenType.ARROW)
 258        COLUMN_OPERATORS.pop(TokenType.DARROW)
 259        COLUMN_OPERATORS.pop(TokenType.HASH_ARROW)
 260        COLUMN_OPERATORS.pop(TokenType.DHASH_ARROW)
 261        COLUMN_OPERATORS.pop(TokenType.PLACEHOLDER)
 262
 263        SHOW_PARSERS = {
 264            **MySQL.Parser.SHOW_PARSERS,
 265            "AGGREGATES": _show_parser("AGGREGATES"),
 266            "CDC EXTRACTOR POOL": _show_parser("CDC EXTRACTOR POOL"),
 267            "CREATE AGGREGATE": _show_parser("CREATE AGGREGATE", target=True),
 268            "CREATE PIPELINE": _show_parser("CREATE PIPELINE", target=True),
 269            "CREATE PROJECTION": _show_parser("CREATE PROJECTION", target=True),
 270            "DATABASE STATUS": _show_parser("DATABASE STATUS"),
 271            "DISTRIBUTED_PLANCACHE STATUS": _show_parser("DISTRIBUTED_PLANCACHE STATUS"),
 272            "FULLTEXT SERVICE METRICS LOCAL": _show_parser("FULLTEXT SERVICE METRICS LOCAL"),
 273            "FULLTEXT SERVICE METRICS FOR NODE": _show_parser(
 274                "FULLTEXT SERVICE METRICS FOR NODE", target=True
 275            ),
 276            "FULLTEXT SERVICE STATUS": _show_parser("FULLTEXT SERVICE STATUS"),
 277            "FUNCTIONS": _show_parser("FUNCTIONS"),
 278            "GROUPS": _show_parser("GROUPS"),
 279            "GROUPS FOR ROLE": _show_parser("GROUPS FOR ROLE", target=True),
 280            "GROUPS FOR USER": _show_parser("GROUPS FOR USER", target=True),
 281            "INDEXES": _show_parser("INDEX", target="FROM"),
 282            "KEYS": _show_parser("INDEX", target="FROM"),
 283            "LINKS": _show_parser("LINKS", target="ON"),
 284            "LOAD ERRORS": _show_parser("LOAD ERRORS"),
 285            "LOAD WARNINGS": _show_parser("LOAD WARNINGS"),
 286            "PARTITIONS": _show_parser("PARTITIONS", target="ON"),
 287            "PIPELINES": _show_parser("PIPELINES"),
 288            "PLAN": _show_parser("PLAN", target=True),
 289            "PLANCACHE": _show_parser("PLANCACHE"),
 290            "PROCEDURES": _show_parser("PROCEDURES"),
 291            "PROJECTIONS": _show_parser("PROJECTIONS", target="ON TABLE"),
 292            "REPLICATION STATUS": _show_parser("REPLICATION STATUS"),
 293            "REPRODUCTION": _show_parser("REPRODUCTION"),
 294            "RESOURCE POOLS": _show_parser("RESOURCE POOLS"),
 295            "ROLES": _show_parser("ROLES"),
 296            "ROLES FOR USER": _show_parser("ROLES FOR USER", target=True),
 297            "ROLES FOR GROUP": _show_parser("ROLES FOR GROUP", target=True),
 298            "STATUS EXTENDED": _show_parser("STATUS EXTENDED"),
 299            "USERS": _show_parser("USERS"),
 300            "USERS FOR ROLE": _show_parser("USERS FOR ROLE", target=True),
 301            "USERS FOR GROUP": _show_parser("USERS FOR GROUP", target=True),
 302        }
 303
 304        ALTER_PARSERS = {
 305            **MySQL.Parser.ALTER_PARSERS,
 306            "CHANGE": lambda self: self.expression(
 307                exp.RenameColumn, this=self._parse_column(), to=self._parse_column()
 308            ),
 309        }
 310
 311        def _parse_vector_expressions(
 312            self, expressions: t.List[exp.Expression]
 313        ) -> t.List[exp.Expression]:
 314            type_name = expressions[1].name.upper()
 315            if type_name in self.dialect.VECTOR_TYPE_ALIASES:
 316                type_name = self.dialect.VECTOR_TYPE_ALIASES[type_name]
 317
 318            return [exp.DataType.build(type_name, dialect=self.dialect), expressions[0]]
 319
 320    class Generator(MySQL.Generator):
 321        SUPPORTS_UESCAPE = False
 322        NULL_ORDERING_SUPPORTED = True
 323        MATCH_AGAINST_TABLE_PREFIX = "TABLE "
 324
 325        @staticmethod
 326        def _unicode_substitute(m: re.Match[str]) -> str:
 327            # Interpret the number as hex and convert it to the Unicode string
 328            return chr(int(m.group(1), 16))
 329
 330        UNICODE_SUBSTITUTE: t.Optional[t.Callable[[re.Match[str]], str]] = _unicode_substitute
 331
 332        SUPPORTED_JSON_PATH_PARTS = {
 333            exp.JSONPathKey,
 334            exp.JSONPathRoot,
 335            exp.JSONPathSubscript,
 336        }
 337
 338        TRANSFORMS = {
 339            **MySQL.Generator.TRANSFORMS,
 340            exp.TsOrDsToDate: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e))
 341            if e.args.get("format")
 342            else self.func("DATE", e.this),
 343            exp.StrToTime: lambda self, e: self.func("TO_TIMESTAMP", e.this, self.format_time(e)),
 344            exp.ToChar: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
 345            exp.StrToDate: lambda self, e: self.func(
 346                "STR_TO_DATE",
 347                e.this,
 348                self.format_time(
 349                    e,
 350                    inverse_time_mapping=MySQL.INVERSE_TIME_MAPPING,
 351                    inverse_time_trie=MySQL.INVERSE_TIME_TRIE,
 352                ),
 353            ),
 354            exp.TimeToStr: lambda self, e: self.func(
 355                "DATE_FORMAT",
 356                e.this,
 357                self.format_time(
 358                    e,
 359                    inverse_time_mapping=MySQL.INVERSE_TIME_MAPPING,
 360                    inverse_time_trie=MySQL.INVERSE_TIME_TRIE,
 361                ),
 362            ),
 363            exp.Date: unsupported_args("zone", "expressions")(rename_func("DATE")),
 364            exp.Cast: unsupported_args("format", "action", "default")(
 365                lambda self, e: f"{self.sql(e, 'this')} :> {self.sql(e, 'to')}"
 366            ),
 367            exp.TryCast: unsupported_args("format", "action", "default")(
 368                lambda self, e: f"{self.sql(e, 'this')} !:> {self.sql(e, 'to')}"
 369            ),
 370            exp.CastToStrType: lambda self, e: self.sql(
 371                exp.cast(e.this, DataType.build(e.args["to"].name))
 372            ),
 373            exp.StrToUnix: unsupported_args("format")(rename_func("UNIX_TIMESTAMP")),
 374            exp.TimeToUnix: rename_func("UNIX_TIMESTAMP"),
 375            exp.TimeStrToUnix: rename_func("UNIX_TIMESTAMP"),
 376            exp.UnixSeconds: rename_func("UNIX_TIMESTAMP"),
 377            exp.UnixToStr: lambda self, e: self.func(
 378                "FROM_UNIXTIME",
 379                e.this,
 380                self.format_time(
 381                    e,
 382                    inverse_time_mapping=MySQL.INVERSE_TIME_MAPPING,
 383                    inverse_time_trie=MySQL.INVERSE_TIME_TRIE,
 384                ),
 385            ),
 386            exp.UnixToTime: unsupported_args("scale", "zone", "hours", "minutes")(
 387                lambda self, e: self.func(
 388                    "FROM_UNIXTIME",
 389                    e.this,
 390                    self.format_time(
 391                        e,
 392                        inverse_time_mapping=MySQL.INVERSE_TIME_MAPPING,
 393                        inverse_time_trie=MySQL.INVERSE_TIME_TRIE,
 394                    ),
 395                ),
 396            ),
 397            exp.UnixToTimeStr: lambda self, e: f"FROM_UNIXTIME({self.sql(e, 'this')}) :> TEXT",
 398            exp.DateBin: unsupported_args("unit", "zone")(
 399                lambda self, e: self.func("TIME_BUCKET", e.this, e.expression, e.args.get("origin"))
 400            ),
 401            exp.TimeStrToDate: lambda self, e: self.sql(exp.cast(e.this, exp.DataType.Type.DATE)),
 402            exp.FromTimeZone: lambda self, e: self.func(
 403                "CONVERT_TZ", e.this, e.args.get("zone"), "'UTC'"
 404            ),
 405            exp.DiToDate: lambda self,
 406            e: f"STR_TO_DATE({self.sql(e, 'this')}, {SingleStore.DATEINT_FORMAT})",
 407            exp.DateToDi: lambda self,
 408            e: f"(DATE_FORMAT({self.sql(e, 'this')}, {SingleStore.DATEINT_FORMAT}) :> INT)",
 409            exp.TsOrDiToDi: lambda self,
 410            e: f"(DATE_FORMAT({self.sql(e, 'this')}, {SingleStore.DATEINT_FORMAT}) :> INT)",
 411            exp.Time: unsupported_args("zone")(lambda self, e: f"{self.sql(e, 'this')} :> TIME"),
 412            exp.DatetimeAdd: _remove_ts_or_ds_to_date(date_add_sql("ADD")),
 413            exp.DatetimeTrunc: unsupported_args("zone")(timestamptrunc_sql()),
 414            exp.DatetimeSub: date_add_interval_sql("DATE", "SUB"),
 415            exp.DatetimeDiff: timestampdiff_sql,
 416            exp.DateTrunc: unsupported_args("zone")(timestamptrunc_sql()),
 417            exp.DateDiff: unsupported_args("zone")(
 418                lambda self, e: timestampdiff_sql(self, e)
 419                if e.unit is not None
 420                else self.func("DATEDIFF", e.this, e.expression)
 421            ),
 422            exp.TsOrDsDiff: lambda self, e: timestampdiff_sql(self, e)
 423            if e.unit is not None
 424            else self.func("DATEDIFF", e.this, e.expression),
 425            exp.TimestampTrunc: unsupported_args("zone")(timestamptrunc_sql()),
 426            exp.CurrentDatetime: lambda self, e: self.sql(
 427                cast_to_time6(
 428                    exp.CurrentTimestamp(this=exp.Literal.number(6)), exp.DataType.Type.DATETIME
 429                )
 430            ),
 431            exp.JSONExtract: unsupported_args(
 432                "only_json_types",
 433                "expressions",
 434                "variant_extract",
 435                "json_query",
 436                "option",
 437                "quote",
 438                "on_condition",
 439                "requires_json",
 440            )(json_extract_segments("JSON_EXTRACT_JSON")),
 441            exp.JSONBExtract: json_extract_segments("BSON_EXTRACT_BSON"),
 442            exp.JSONPathKey: json_path_key_only_name,
 443            exp.JSONPathSubscript: lambda self, e: self.json_path_part(e.this),
 444            exp.JSONPathRoot: lambda *_: "",
 445            exp.JSONFormat: unsupported_args("options", "is_json")(rename_func("JSON_PRETTY")),
 446            exp.JSONArrayAgg: unsupported_args("null_handling", "return_type", "strict")(
 447                lambda self, e: self.func("JSON_AGG", e.this, suffix=f"{self.sql(e, 'order')})")
 448            ),
 449            exp.JSONArray: unsupported_args("null_handling", "return_type", "strict")(
 450                rename_func("JSON_BUILD_ARRAY")
 451            ),
 452            exp.JSONBExists: lambda self, e: self.func(
 453                "BSON_MATCH_ANY_EXISTS", e.this, e.args.get("path")
 454            ),
 455            exp.JSONExists: unsupported_args("passing", "on_condition")(
 456                lambda self, e: self.func("JSON_MATCH_ANY_EXISTS", e.this, e.args.get("path"))
 457            ),
 458            exp.JSONObject: unsupported_args(
 459                "null_handling", "unique_keys", "return_type", "encoding"
 460            )(rename_func("JSON_BUILD_OBJECT")),
 461            exp.DayOfWeekIso: lambda self, e: f"(({self.func('DAYOFWEEK', e.this)} % 7) + 1)",
 462            exp.DayOfMonth: rename_func("DAY"),
 463            exp.Hll: rename_func("APPROX_COUNT_DISTINCT"),
 464            exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"),
 465            exp.CountIf: count_if_to_sum,
 466            exp.LogicalOr: lambda self, e: f"MAX(ABS({self.sql(e, 'this')}))",
 467            exp.LogicalAnd: lambda self, e: f"MIN(ABS({self.sql(e, 'this')}))",
 468            exp.ApproxQuantile: unsupported_args("accuracy", "weight")(
 469                lambda self, e: self.func(
 470                    "APPROX_PERCENTILE",
 471                    e.this,
 472                    e.args.get("quantile"),
 473                    e.args.get("error_tolerance"),
 474                )
 475            ),
 476            exp.Variance: rename_func("VAR_SAMP"),
 477            exp.VariancePop: rename_func("VAR_POP"),
 478            exp.Xor: bool_xor_sql,
 479            exp.Cbrt: lambda self, e: self.sql(
 480                exp.Pow(this=e.this, expression=exp.Literal.number(1) / exp.Literal.number(3))
 481            ),
 482            exp.RegexpLike: lambda self, e: self.binary(e, "RLIKE"),
 483            exp.Repeat: lambda self, e: self.func(
 484                "LPAD",
 485                exp.Literal.string(""),
 486                exp.Mul(this=self.func("LENGTH", e.this), expression=e.args.get("times")),
 487                e.this,
 488            ),
 489            exp.IsAscii: lambda self, e: f"({self.sql(e, 'this')} RLIKE '^[\x00-\x7f]*$')",
 490            exp.MD5Digest: lambda self, e: self.func("UNHEX", self.func("MD5", e.this)),
 491            exp.Chr: rename_func("CHAR"),
 492            exp.Contains: rename_func("INSTR"),
 493            exp.RegexpExtractAll: unsupported_args("position", "occurrence", "group")(
 494                lambda self, e: self.func(
 495                    "REGEXP_MATCH",
 496                    e.this,
 497                    e.expression,
 498                    e.args.get("parameters"),
 499                )
 500            ),
 501            exp.RegexpExtract: unsupported_args("group")(
 502                lambda self, e: self.func(
 503                    "REGEXP_SUBSTR",
 504                    e.this,
 505                    e.expression,
 506                    e.args.get("position"),
 507                    e.args.get("occurrence"),
 508                    e.args.get("parameters"),
 509                )
 510            ),
 511            exp.StartsWith: lambda self, e: self.func(
 512                "REGEXP_INSTR", e.this, self.func("CONCAT", exp.Literal.string("^"), e.expression)
 513            ),
 514            exp.FromBase: lambda self, e: self.func(
 515                "CONV", e.this, e.expression, exp.Literal.number(10)
 516            ),
 517            exp.RegexpILike: lambda self, e: self.binary(
 518                exp.RegexpLike(
 519                    this=exp.Lower(this=e.this),
 520                    expression=exp.Lower(this=e.expression),
 521                ),
 522                "RLIKE",
 523            ),
 524            exp.Stuff: lambda self, e: self.func(
 525                "CONCAT",
 526                self.func("SUBSTRING", e.this, exp.Literal.number(1), e.args.get("start") - 1),
 527                e.expression,
 528                self.func("SUBSTRING", e.this, e.args.get("start") + e.args.get("length")),
 529            ),
 530            exp.National: lambda self, e: self.national_sql(e, prefix=""),
 531            exp.Reduce: unsupported_args("finish")(
 532                lambda self, e: self.func(
 533                    "REDUCE", e.args.get("initial"), e.this, e.args.get("merge")
 534                )
 535            ),
 536            exp.MatchAgainst: unsupported_args("modifier")(
 537                lambda self, e: super().matchagainst_sql(e)
 538            ),
 539            exp.Show: unsupported_args(
 540                "history",
 541                "terse",
 542                "offset",
 543                "starts_with",
 544                "limit",
 545                "from",
 546                "scope",
 547                "scope_kind",
 548                "mutex",
 549                "query",
 550                "channel",
 551                "log",
 552                "types",
 553                "privileges",
 554            )(lambda self, e: super().show_sql(e)),
 555            exp.Describe: unsupported_args(
 556                "style",
 557                "kind",
 558                "expressions",
 559                "partition",
 560                "format",
 561            )(lambda self, e: super().describe_sql(e)),
 562        }
 563        TRANSFORMS.pop(exp.JSONExtractScalar)
 564        TRANSFORMS.pop(exp.CurrentDate)
 565
 566        UNSUPPORTED_TYPES = {
 567            exp.DataType.Type.ARRAY,
 568            exp.DataType.Type.AGGREGATEFUNCTION,
 569            exp.DataType.Type.SIMPLEAGGREGATEFUNCTION,
 570            exp.DataType.Type.BIGSERIAL,
 571            exp.DataType.Type.BPCHAR,
 572            exp.DataType.Type.DATEMULTIRANGE,
 573            exp.DataType.Type.DATERANGE,
 574            exp.DataType.Type.DYNAMIC,
 575            exp.DataType.Type.HLLSKETCH,
 576            exp.DataType.Type.HSTORE,
 577            exp.DataType.Type.IMAGE,
 578            exp.DataType.Type.INET,
 579            exp.DataType.Type.INT128,
 580            exp.DataType.Type.INT256,
 581            exp.DataType.Type.INT4MULTIRANGE,
 582            exp.DataType.Type.INT4RANGE,
 583            exp.DataType.Type.INT8MULTIRANGE,
 584            exp.DataType.Type.INT8RANGE,
 585            exp.DataType.Type.INTERVAL,
 586            exp.DataType.Type.IPADDRESS,
 587            exp.DataType.Type.IPPREFIX,
 588            exp.DataType.Type.IPV4,
 589            exp.DataType.Type.IPV6,
 590            exp.DataType.Type.LIST,
 591            exp.DataType.Type.MAP,
 592            exp.DataType.Type.LOWCARDINALITY,
 593            exp.DataType.Type.MONEY,
 594            exp.DataType.Type.MULTILINESTRING,
 595            exp.DataType.Type.NAME,
 596            exp.DataType.Type.NESTED,
 597            exp.DataType.Type.NOTHING,
 598            exp.DataType.Type.NULL,
 599            exp.DataType.Type.NUMMULTIRANGE,
 600            exp.DataType.Type.NUMRANGE,
 601            exp.DataType.Type.OBJECT,
 602            exp.DataType.Type.RANGE,
 603            exp.DataType.Type.ROWVERSION,
 604            exp.DataType.Type.SERIAL,
 605            exp.DataType.Type.SMALLSERIAL,
 606            exp.DataType.Type.SMALLMONEY,
 607            exp.DataType.Type.STRUCT,
 608            exp.DataType.Type.SUPER,
 609            exp.DataType.Type.TIMETZ,
 610            exp.DataType.Type.TIMESTAMPNTZ,
 611            exp.DataType.Type.TIMESTAMPLTZ,
 612            exp.DataType.Type.TIMESTAMPTZ,
 613            exp.DataType.Type.TIMESTAMP_NS,
 614            exp.DataType.Type.TSMULTIRANGE,
 615            exp.DataType.Type.TSRANGE,
 616            exp.DataType.Type.TSTZMULTIRANGE,
 617            exp.DataType.Type.TSTZRANGE,
 618            exp.DataType.Type.UINT128,
 619            exp.DataType.Type.UINT256,
 620            exp.DataType.Type.UNION,
 621            exp.DataType.Type.UNKNOWN,
 622            exp.DataType.Type.USERDEFINED,
 623            exp.DataType.Type.UUID,
 624            exp.DataType.Type.VARIANT,
 625            exp.DataType.Type.XML,
 626            exp.DataType.Type.TDIGEST,
 627        }
 628
 629        TYPE_MAPPING = {
 630            **MySQL.Generator.TYPE_MAPPING,
 631            exp.DataType.Type.BIGDECIMAL: "DECIMAL",
 632            exp.DataType.Type.BIT: "BOOLEAN",
 633            exp.DataType.Type.DATE32: "DATE",
 634            exp.DataType.Type.DATETIME64: "DATETIME",
 635            exp.DataType.Type.DECIMAL32: "DECIMAL",
 636            exp.DataType.Type.DECIMAL64: "DECIMAL",
 637            exp.DataType.Type.DECIMAL128: "DECIMAL",
 638            exp.DataType.Type.DECIMAL256: "DECIMAL",
 639            exp.DataType.Type.ENUM8: "ENUM",
 640            exp.DataType.Type.ENUM16: "ENUM",
 641            exp.DataType.Type.FIXEDSTRING: "TEXT",
 642            exp.DataType.Type.GEOMETRY: "GEOGRAPHY",
 643            exp.DataType.Type.POINT: "GEOGRAPHYPOINT",
 644            exp.DataType.Type.RING: "GEOGRAPHY",
 645            exp.DataType.Type.LINESTRING: "GEOGRAPHY",
 646            exp.DataType.Type.POLYGON: "GEOGRAPHY",
 647            exp.DataType.Type.MULTIPOLYGON: "GEOGRAPHY",
 648            exp.DataType.Type.JSONB: "BSON",
 649            exp.DataType.Type.TIMESTAMP: "TIMESTAMP",
 650            exp.DataType.Type.TIMESTAMP_S: "TIMESTAMP",
 651            exp.DataType.Type.TIMESTAMP_MS: "TIMESTAMP(6)",
 652        }
 653
 654        # https://docs.singlestore.com/cloud/reference/sql-reference/restricted-keywords/list-of-restricted-keywords/
 655        RESERVED_KEYWORDS = {
 656            "abs",
 657            "absolute",
 658            "access",
 659            "account",
 660            "acos",
 661            "action",
 662            "add",
 663            "adddate",
 664            "addtime",
 665            "admin",
 666            "aes_decrypt",
 667            "aes_encrypt",
 668            "after",
 669            "against",
 670            "aggregate",
 671            "aggregates",
 672            "aggregator",
 673            "aggregator_id",
 674            "aggregator_plan_hash",
 675            "aggregators",
 676            "algorithm",
 677            "all",
 678            "also",
 679            "alter",
 680            "always",
 681            "analyse",
 682            "analyze",
 683            "and",
 684            "anti_join",
 685            "any",
 686            "any_value",
 687            "approx_count_distinct",
 688            "approx_count_distinct_accumulate",
 689            "approx_count_distinct_combine",
 690            "approx_count_distinct_estimate",
 691            "approx_geography_intersects",
 692            "approx_percentile",
 693            "arghistory",
 694            "arrange",
 695            "arrangement",
 696            "array",
 697            "as",
 698            "asc",
 699            "ascii",
 700            "asensitive",
 701            "asin",
 702            "asm",
 703            "assertion",
 704            "assignment",
 705            "ast",
 706            "asymmetric",
 707            "async",
 708            "at",
 709            "atan",
 710            "atan2",
 711            "attach",
 712            "attribute",
 713            "authorization",
 714            "auto",
 715            "auto_increment",
 716            "auto_reprovision",
 717            "autostats",
 718            "autostats_cardinality_mode",
 719            "autostats_enabled",
 720            "autostats_histogram_mode",
 721            "autostats_sampling",
 722            "availability",
 723            "avg",
 724            "avg_row_length",
 725            "avro",
 726            "azure",
 727            "background",
 728            "_background_threads_for_cleanup",
 729            "backup",
 730            "backup_history",
 731            "backup_id",
 732            "backward",
 733            "batch",
 734            "batches",
 735            "batch_interval",
 736            "_batch_size_limit",
 737            "before",
 738            "begin",
 739            "between",
 740            "bigint",
 741            "bin",
 742            "binary",
 743            "_binary",
 744            "bit",
 745            "bit_and",
 746            "bit_count",
 747            "bit_or",
 748            "bit_xor",
 749            "blob",
 750            "bool",
 751            "boolean",
 752            "bootstrap",
 753            "both",
 754            "_bt",
 755            "btree",
 756            "bucket_count",
 757            "by",
 758            "byte",
 759            "byte_length",
 760            "cache",
 761            "call",
 762            "call_for_pipeline",
 763            "called",
 764            "capture",
 765            "cascade",
 766            "cascaded",
 767            "case",
 768            "cast",
 769            "catalog",
 770            "ceil",
 771            "ceiling",
 772            "chain",
 773            "change",
 774            "char",
 775            "character",
 776            "characteristics",
 777            "character_length",
 778            "char_length",
 779            "charset",
 780            "check",
 781            "checkpoint",
 782            "_check_can_connect",
 783            "_check_consistency",
 784            "checksum",
 785            "_checksum",
 786            "class",
 787            "clear",
 788            "client",
 789            "client_found_rows",
 790            "close",
 791            "cluster",
 792            "clustered",
 793            "cnf",
 794            "coalesce",
 795            "coercibility",
 796            "collate",
 797            "collation",
 798            "collect",
 799            "column",
 800            "columnar",
 801            "columns",
 802            "columnstore",
 803            "columnstore_segment_rows",
 804            "comment",
 805            "comments",
 806            "commit",
 807            "committed",
 808            "_commit_log_tail",
 809            "committed",
 810            "compact",
 811            "compile",
 812            "compressed",
 813            "compression",
 814            "concat",
 815            "concat_ws",
 816            "concurrent",
 817            "concurrently",
 818            "condition",
 819            "configuration",
 820            "connection",
 821            "connection_id",
 822            "connections",
 823            "config",
 824            "constraint",
 825            "constraints",
 826            "content",
 827            "continue",
 828            "_continue_replay",
 829            "conv",
 830            "conversion",
 831            "convert",
 832            "convert_tz",
 833            "copy",
 834            "_core",
 835            "cos",
 836            "cost",
 837            "cot",
 838            "count",
 839            "create",
 840            "credentials",
 841            "cross",
 842            "cube",
 843            "csv",
 844            "cume_dist",
 845            "curdate",
 846            "current",
 847            "current_catalog",
 848            "current_date",
 849            "current_role",
 850            "current_schema",
 851            "current_security_groups",
 852            "current_security_roles",
 853            "current_time",
 854            "current_timestamp",
 855            "current_user",
 856            "cursor",
 857            "curtime",
 858            "cycle",
 859            "data",
 860            "database",
 861            "databases",
 862            "date",
 863            "date_add",
 864            "datediff",
 865            "date_format",
 866            "date_sub",
 867            "date_trunc",
 868            "datetime",
 869            "day",
 870            "day_hour",
 871            "day_microsecond",
 872            "day_minute",
 873            "dayname",
 874            "dayofmonth",
 875            "dayofweek",
 876            "dayofyear",
 877            "day_second",
 878            "deallocate",
 879            "dec",
 880            "decimal",
 881            "declare",
 882            "decode",
 883            "default",
 884            "defaults",
 885            "deferrable",
 886            "deferred",
 887            "defined",
 888            "definer",
 889            "degrees",
 890            "delayed",
 891            "delay_key_write",
 892            "delete",
 893            "delimiter",
 894            "delimiters",
 895            "dense_rank",
 896            "desc",
 897            "describe",
 898            "detach",
 899            "deterministic",
 900            "dictionary",
 901            "differential",
 902            "directory",
 903            "disable",
 904            "discard",
 905            "_disconnect",
 906            "disk",
 907            "distinct",
 908            "distinctrow",
 909            "distributed_joins",
 910            "div",
 911            "do",
 912            "document",
 913            "domain",
 914            "dot_product",
 915            "double",
 916            "drop",
 917            "_drop_profile",
 918            "dual",
 919            "dump",
 920            "duplicate",
 921            "dynamic",
 922            "earliest",
 923            "each",
 924            "echo",
 925            "election",
 926            "else",
 927            "elseif",
 928            "elt",
 929            "enable",
 930            "enclosed",
 931            "encoding",
 932            "encrypted",
 933            "end",
 934            "engine",
 935            "engines",
 936            "enum",
 937            "errors",
 938            "escape",
 939            "escaped",
 940            "estimate",
 941            "euclidean_distance",
 942            "event",
 943            "events",
 944            "except",
 945            "exclude",
 946            "excluding",
 947            "exclusive",
 948            "execute",
 949            "exists",
 950            "exit",
 951            "exp",
 952            "explain",
 953            "extended",
 954            "extension",
 955            "external",
 956            "external_host",
 957            "external_port",
 958            "extract",
 959            "extractor",
 960            "extractors",
 961            "extra_join",
 962            "_failover",
 963            "failed_login_attempts",
 964            "failure",
 965            "false",
 966            "family",
 967            "fault",
 968            "fetch",
 969            "field",
 970            "fields",
 971            "file",
 972            "files",
 973            "fill",
 974            "first",
 975            "first_value",
 976            "fix_alter",
 977            "fixed",
 978            "float",
 979            "float4",
 980            "float8",
 981            "floor",
 982            "flush",
 983            "following",
 984            "for",
 985            "force",
 986            "force_compiled_mode",
 987            "force_interpreter_mode",
 988            "foreground",
 989            "foreign",
 990            "format",
 991            "forward",
 992            "found_rows",
 993            "freeze",
 994            "from",
 995            "from_base64",
 996            "from_days",
 997            "from_unixtime",
 998            "fs",
 999            "_fsync",
1000            "full",
1001            "fulltext",
1002            "function",
1003            "functions",
1004            "gc",
1005            "gcs",
1006            "get_format",
1007            "_gc",
1008            "_gcx",
1009            "generate",
1010            "geography",
1011            "geography_area",
1012            "geography_contains",
1013            "geography_distance",
1014            "geography_intersects",
1015            "geography_latitude",
1016            "geography_length",
1017            "geography_longitude",
1018            "geographypoint",
1019            "geography_point",
1020            "geography_within_distance",
1021            "geometry",
1022            "geometry_area",
1023            "geometry_contains",
1024            "geometry_distance",
1025            "geometry_filter",
1026            "geometry_intersects",
1027            "geometry_length",
1028            "geometrypoint",
1029            "geometry_point",
1030            "geometry_within_distance",
1031            "geometry_x",
1032            "geometry_y",
1033            "global",
1034            "_global_version_timestamp",
1035            "grant",
1036            "granted",
1037            "grants",
1038            "greatest",
1039            "group",
1040            "grouping",
1041            "groups",
1042            "group_concat",
1043            "gzip",
1044            "handle",
1045            "handler",
1046            "hard_cpu_limit_percentage",
1047            "hash",
1048            "has_temp_tables",
1049            "having",
1050            "hdfs",
1051            "header",
1052            "heartbeat_no_logging",
1053            "hex",
1054            "highlight",
1055            "high_priority",
1056            "hold",
1057            "holding",
1058            "host",
1059            "hosts",
1060            "hour",
1061            "hour_microsecond",
1062            "hour_minute",
1063            "hour_second",
1064            "identified",
1065            "identity",
1066            "if",
1067            "ifnull",
1068            "ignore",
1069            "ilike",
1070            "immediate",
1071            "immutable",
1072            "implicit",
1073            "import",
1074            "in",
1075            "including",
1076            "increment",
1077            "incremental",
1078            "index",
1079            "indexes",
1080            "inet_aton",
1081            "inet_ntoa",
1082            "inet6_aton",
1083            "inet6_ntoa",
1084            "infile",
1085            "inherit",
1086            "inherits",
1087            "_init_profile",
1088            "init",
1089            "initcap",
1090            "initialize",
1091            "initially",
1092            "inject",
1093            "inline",
1094            "inner",
1095            "inout",
1096            "input",
1097            "insensitive",
1098            "insert",
1099            "insert_method",
1100            "instance",
1101            "instead",
1102            "instr",
1103            "int",
1104            "int1",
1105            "int2",
1106            "int3",
1107            "int4",
1108            "int8",
1109            "integer",
1110            "_internal_dynamic_typecast",
1111            "interpreter_mode",
1112            "intersect",
1113            "interval",
1114            "into",
1115            "invoker",
1116            "is",
1117            "isnull",
1118            "isolation",
1119            "iterate",
1120            "join",
1121            "json",
1122            "json_agg",
1123            "json_array_contains_double",
1124            "json_array_contains_json",
1125            "json_array_contains_string",
1126            "json_array_push_double",
1127            "json_array_push_json",
1128            "json_array_push_string",
1129            "json_delete_key",
1130            "json_extract_double",
1131            "json_extract_json",
1132            "json_extract_string",
1133            "json_extract_bigint",
1134            "json_get_type",
1135            "json_length",
1136            "json_set_double",
1137            "json_set_json",
1138            "json_set_string",
1139            "json_splice_double",
1140            "json_splice_json",
1141            "json_splice_string",
1142            "kafka",
1143            "key",
1144            "key_block_size",
1145            "keys",
1146            "kill",
1147            "killall",
1148            "label",
1149            "lag",
1150            "language",
1151            "large",
1152            "last",
1153            "last_day",
1154            "last_insert_id",
1155            "last_value",
1156            "lateral",
1157            "latest",
1158            "lc_collate",
1159            "lc_ctype",
1160            "lcase",
1161            "lead",
1162            "leading",
1163            "leaf",
1164            "leakproof",
1165            "least",
1166            "leave",
1167            "leaves",
1168            "left",
1169            "length",
1170            "level",
1171            "license",
1172            "like",
1173            "limit",
1174            "lines",
1175            "listen",
1176            "llvm",
1177            "ln",
1178            "load",
1179            "loaddata_where",
1180            "_load",
1181            "local",
1182            "localtime",
1183            "localtimestamp",
1184            "locate",
1185            "location",
1186            "lock",
1187            "log",
1188            "log10",
1189            "log2",
1190            "long",
1191            "longblob",
1192            "longtext",
1193            "loop",
1194            "lower",
1195            "low_priority",
1196            "lpad",
1197            "_ls",
1198            "ltrim",
1199            "lz4",
1200            "management",
1201            "_management_thread",
1202            "mapping",
1203            "master",
1204            "match",
1205            "materialized",
1206            "max",
1207            "maxvalue",
1208            "max_concurrency",
1209            "max_errors",
1210            "max_partitions_per_batch",
1211            "max_queue_depth",
1212            "max_retries_per_batch_partition",
1213            "max_rows",
1214            "mbc",
1215            "md5",
1216            "mpl",
1217            "median",
1218            "mediumblob",
1219            "mediumint",
1220            "mediumtext",
1221            "member",
1222            "memory",
1223            "memory_percentage",
1224            "_memsql_table_id_lookup",
1225            "memsql",
1226            "memsql_deserialize",
1227            "memsql_imitating_kafka",
1228            "memsql_serialize",
1229            "merge",
1230            "metadata",
1231            "microsecond",
1232            "middleint",
1233            "min",
1234            "min_rows",
1235            "minus",
1236            "minute",
1237            "minute_microsecond",
1238            "minute_second",
1239            "minvalue",
1240            "mod",
1241            "mode",
1242            "model",
1243            "modifies",
1244            "modify",
1245            "month",
1246            "monthname",
1247            "months_between",
1248            "move",
1249            "mpl",
1250            "names",
1251            "named",
1252            "namespace",
1253            "national",
1254            "natural",
1255            "nchar",
1256            "next",
1257            "no",
1258            "node",
1259            "none",
1260            "no_query_rewrite",
1261            "noparam",
1262            "not",
1263            "nothing",
1264            "notify",
1265            "now",
1266            "nowait",
1267            "no_write_to_binlog",
1268            "no_query_rewrite",
1269            "norely",
1270            "nth_value",
1271            "ntile",
1272            "null",
1273            "nullcols",
1274            "nullif",
1275            "nulls",
1276            "numeric",
1277            "nvarchar",
1278            "object",
1279            "octet_length",
1280            "of",
1281            "off",
1282            "offline",
1283            "offset",
1284            "offsets",
1285            "oids",
1286            "on",
1287            "online",
1288            "only",
1289            "open",
1290            "operator",
1291            "optimization",
1292            "optimize",
1293            "optimizer",
1294            "optimizer_state",
1295            "option",
1296            "options",
1297            "optionally",
1298            "or",
1299            "order",
1300            "ordered_serialize",
1301            "orphan",
1302            "out",
1303            "out_of_order",
1304            "outer",
1305            "outfile",
1306            "over",
1307            "overlaps",
1308            "overlay",
1309            "owned",
1310            "owner",
1311            "pack_keys",
1312            "paired",
1313            "parser",
1314            "parquet",
1315            "partial",
1316            "partition",
1317            "partition_id",
1318            "partitioning",
1319            "partitions",
1320            "passing",
1321            "password",
1322            "password_lock_time",
1323            "parser",
1324            "pause",
1325            "_pause_replay",
1326            "percent_rank",
1327            "percentile_cont",
1328            "percentile_disc",
1329            "periodic",
1330            "persisted",
1331            "pi",
1332            "pipeline",
1333            "pipelines",
1334            "pivot",
1335            "placing",
1336            "plan",
1337            "plans",
1338            "plancache",
1339            "plugins",
1340            "pool",
1341            "pools",
1342            "port",
1343            "position",
1344            "pow",
1345            "power",
1346            "preceding",
1347            "precision",
1348            "prepare",
1349            "prepared",
1350            "preserve",
1351            "primary",
1352            "prior",
1353            "privileges",
1354            "procedural",
1355            "procedure",
1356            "procedures",
1357            "process",
1358            "processlist",
1359            "profile",
1360            "profiles",
1361            "program",
1362            "promote",
1363            "proxy",
1364            "purge",
1365            "quarter",
1366            "queries",
1367            "query",
1368            "query_timeout",
1369            "queue",
1370            "quote",
1371            "radians",
1372            "rand",
1373            "range",
1374            "rank",
1375            "read",
1376            "_read",
1377            "reads",
1378            "real",
1379            "reassign",
1380            "rebalance",
1381            "recheck",
1382            "record",
1383            "recursive",
1384            "redundancy",
1385            "redundant",
1386            "ref",
1387            "reference",
1388            "references",
1389            "refresh",
1390            "regexp",
1391            "reindex",
1392            "relative",
1393            "release",
1394            "reload",
1395            "rely",
1396            "remote",
1397            "remove",
1398            "rename",
1399            "repair",
1400            "_repair_table",
1401            "repeat",
1402            "repeatable",
1403            "_repl",
1404            "_reprovisioning",
1405            "replace",
1406            "replica",
1407            "replicate",
1408            "replicating",
1409            "replication",
1410            "durability",
1411            "require",
1412            "resource",
1413            "resource_pool",
1414            "reset",
1415            "restart",
1416            "restore",
1417            "restrict",
1418            "result",
1419            "_resurrect",
1420            "retry",
1421            "return",
1422            "returning",
1423            "returns",
1424            "reverse",
1425            "revoke",
1426            "rg_pool",
1427            "right",
1428            "right_anti_join",
1429            "right_semi_join",
1430            "right_straight_join",
1431            "rlike",
1432            "role",
1433            "roles",
1434            "rollback",
1435            "rollup",
1436            "round",
1437            "routine",
1438            "row",
1439            "row_count",
1440            "row_format",
1441            "row_number",
1442            "rows",
1443            "rowstore",
1444            "rule",
1445            "rpad",
1446            "_rpc",
1447            "rtrim",
1448            "running",
1449            "s3",
1450            "safe",
1451            "save",
1452            "savepoint",
1453            "scalar",
1454            "schema",
1455            "schemas",
1456            "schema_binding",
1457            "scroll",
1458            "search",
1459            "second",
1460            "second_microsecond",
1461            "sec_to_time",
1462            "security",
1463            "select",
1464            "semi_join",
1465            "_send_threads",
1466            "sensitive",
1467            "separator",
1468            "sequence",
1469            "sequences",
1470            "serial",
1471            "serializable",
1472            "series",
1473            "service_user",
1474            "server",
1475            "session",
1476            "session_user",
1477            "set",
1478            "setof",
1479            "security_lists_intersect",
1480            "sha",
1481            "sha1",
1482            "sha2",
1483            "shard",
1484            "sharded",
1485            "sharded_id",
1486            "share",
1487            "show",
1488            "shutdown",
1489            "sigmoid",
1490            "sign",
1491            "signal",
1492            "similar",
1493            "simple",
1494            "site",
1495            "signed",
1496            "sin",
1497            "skip",
1498            "skipped_batches",
1499            "sleep",
1500            "_sleep",
1501            "smallint",
1502            "snapshot",
1503            "_snapshot",
1504            "_snapshots",
1505            "soft_cpu_limit_percentage",
1506            "some",
1507            "soname",
1508            "sparse",
1509            "spatial",
1510            "spatial_check_index",
1511            "specific",
1512            "split",
1513            "sql",
1514            "sql_big_result",
1515            "sql_buffer_result",
1516            "sql_cache",
1517            "sql_calc_found_rows",
1518            "sqlexception",
1519            "sql_mode",
1520            "sql_no_cache",
1521            "sql_no_logging",
1522            "sql_small_result",
1523            "sqlstate",
1524            "sqlwarning",
1525            "sqrt",
1526            "ssl",
1527            "stable",
1528            "standalone",
1529            "start",
1530            "starting",
1531            "state",
1532            "statement",
1533            "statistics",
1534            "stats",
1535            "status",
1536            "std",
1537            "stddev",
1538            "stddev_pop",
1539            "stddev_samp",
1540            "stdin",
1541            "stdout",
1542            "stop",
1543            "storage",
1544            "str_to_date",
1545            "straight_join",
1546            "strict",
1547            "string",
1548            "strip",
1549            "subdate",
1550            "substr",
1551            "substring",
1552            "substring_index",
1553            "success",
1554            "sum",
1555            "super",
1556            "symmetric",
1557            "sync_snapshot",
1558            "sync",
1559            "_sync",
1560            "_sync2",
1561            "_sync_partitions",
1562            "_sync_snapshot",
1563            "synchronize",
1564            "sysid",
1565            "system",
1566            "table",
1567            "table_checksum",
1568            "tables",
1569            "tablespace",
1570            "tags",
1571            "tan",
1572            "target_size",
1573            "task",
1574            "temp",
1575            "template",
1576            "temporary",
1577            "temptable",
1578            "_term_bump",
1579            "terminate",
1580            "terminated",
1581            "test",
1582            "text",
1583            "then",
1584            "time",
1585            "timediff",
1586            "time_bucket",
1587            "time_format",
1588            "timeout",
1589            "timestamp",
1590            "timestampadd",
1591            "timestampdiff",
1592            "timezone",
1593            "time_to_sec",
1594            "tinyblob",
1595            "tinyint",
1596            "tinytext",
1597            "to",
1598            "to_base64",
1599            "to_char",
1600            "to_date",
1601            "to_days",
1602            "to_json",
1603            "to_number",
1604            "to_seconds",
1605            "to_timestamp",
1606            "tracelogs",
1607            "traditional",
1608            "trailing",
1609            "transform",
1610            "transaction",
1611            "_transactions_experimental",
1612            "treat",
1613            "trigger",
1614            "triggers",
1615            "trim",
1616            "true",
1617            "trunc",
1618            "truncate",
1619            "trusted",
1620            "two_phase",
1621            "_twopcid",
1622            "type",
1623            "types",
1624            "ucase",
1625            "unbounded",
1626            "uncommitted",
1627            "undefined",
1628            "undo",
1629            "unencrypted",
1630            "unenforced",
1631            "unhex",
1632            "unhold",
1633            "unicode",
1634            "union",
1635            "unique",
1636            "_unittest",
1637            "unix_timestamp",
1638            "unknown",
1639            "unlisten",
1640            "_unload",
1641            "unlock",
1642            "unlogged",
1643            "unpivot",
1644            "unsigned",
1645            "until",
1646            "update",
1647            "upgrade",
1648            "upper",
1649            "usage",
1650            "use",
1651            "user",
1652            "users",
1653            "using",
1654            "utc_date",
1655            "utc_time",
1656            "utc_timestamp",
1657            "_utf8",
1658            "vacuum",
1659            "valid",
1660            "validate",
1661            "validator",
1662            "value",
1663            "values",
1664            "varbinary",
1665            "varchar",
1666            "varcharacter",
1667            "variables",
1668            "variadic",
1669            "variance",
1670            "var_pop",
1671            "var_samp",
1672            "varying",
1673            "vector_sub",
1674            "verbose",
1675            "version",
1676            "view",
1677            "void",
1678            "volatile",
1679            "voting",
1680            "wait",
1681            "_wake",
1682            "warnings",
1683            "week",
1684            "weekday",
1685            "weekofyear",
1686            "when",
1687            "where",
1688            "while",
1689            "whitespace",
1690            "window",
1691            "with",
1692            "without",
1693            "within",
1694            "_wm_heartbeat",
1695            "work",
1696            "workload",
1697            "wrapper",
1698            "write",
1699            "xact_id",
1700            "xor",
1701            "year",
1702            "year_month",
1703            "yes",
1704            "zerofill",
1705            "zone",
1706        }
1707
1708        def jsonextractscalar_sql(self, expression: exp.JSONExtractScalar) -> str:
1709            json_type = expression.args.get("json_type")
1710            func_name = "JSON_EXTRACT_JSON" if json_type is None else f"JSON_EXTRACT_{json_type}"
1711            return json_extract_segments(func_name)(self, expression)
1712
1713        def jsonbextractscalar_sql(self, expression: exp.JSONBExtractScalar) -> str:
1714            json_type = expression.args.get("json_type")
1715            func_name = "BSON_EXTRACT_BSON" if json_type is None else f"BSON_EXTRACT_{json_type}"
1716            return json_extract_segments(func_name)(self, expression)
1717
1718        def jsonextractarray_sql(self, expression: exp.JSONExtractArray) -> str:
1719            self.unsupported("Arrays are not supported in SingleStore")
1720            return self.function_fallback_sql(expression)
1721
1722        @unsupported_args("on_condition")
1723        def jsonvalue_sql(self, expression: exp.JSONValue) -> str:
1724            res: exp.Expression = exp.JSONExtractScalar(
1725                this=expression.this,
1726                expression=expression.args.get("path"),
1727                json_type="STRING",
1728            )
1729
1730            returning = expression.args.get("returning")
1731            if returning is not None:
1732                res = exp.Cast(this=res, to=returning)
1733
1734            return self.sql(res)
1735
1736        def all_sql(self, expression: exp.All) -> str:
1737            self.unsupported("ALL subquery predicate is not supported in SingleStore")
1738            return super().all_sql(expression)
1739
1740        def jsonarraycontains_sql(self, expression: exp.JSONArrayContains) -> str:
1741            json_type = expression.text("json_type").upper()
1742
1743            if json_type:
1744                return self.func(
1745                    f"JSON_ARRAY_CONTAINS_{json_type}", expression.expression, expression.this
1746                )
1747
1748            return self.func(
1749                "JSON_ARRAY_CONTAINS_JSON",
1750                expression.expression,
1751                self.func("TO_JSON", expression.this),
1752            )
1753
1754        @unsupported_args("kind", "nested", "values")
1755        def datatype_sql(self, expression: exp.DataType) -> str:
1756            if expression.is_type(exp.DataType.Type.VARBINARY) and not expression.expressions:
1757                # `VARBINARY` must always have a size - if it doesn't, we always generate `BLOB`
1758                return "BLOB"
1759            if expression.is_type(
1760                exp.DataType.Type.DECIMAL32,
1761                exp.DataType.Type.DECIMAL64,
1762                exp.DataType.Type.DECIMAL128,
1763                exp.DataType.Type.DECIMAL256,
1764            ):
1765                scale = self.expressions(expression, flat=True)
1766
1767                if expression.is_type(exp.DataType.Type.DECIMAL32):
1768                    precision = "9"
1769                elif expression.is_type(exp.DataType.Type.DECIMAL64):
1770                    precision = "18"
1771                elif expression.is_type(exp.DataType.Type.DECIMAL128):
1772                    precision = "38"
1773                else:
1774                    # 65 is a maximum precision supported in SingleStore
1775                    precision = "65"
1776                if scale is not None:
1777                    return f"DECIMAL({precision}, {scale[0]})"
1778                else:
1779                    return f"DECIMAL({precision})"
1780            if expression.is_type(exp.DataType.Type.VECTOR):
1781                expressions = expression.expressions
1782                if len(expressions) == 2:
1783                    type_name = self.sql(expressions[0])
1784                    if type_name in self.dialect.INVERSE_VECTOR_TYPE_ALIASES:
1785                        type_name = self.dialect.INVERSE_VECTOR_TYPE_ALIASES[type_name]
1786
1787                    return f"VECTOR({self.sql(expressions[1])}, {type_name})"
1788
1789            return super().datatype_sql(expression)
1790
1791        def collate_sql(self, expression: exp.Collate) -> str:
1792            # SingleStore does not support setting a collation for column in the SELECT query,
1793            # so we cast column to a LONGTEXT type with specific collation
1794            return self.binary(expression, ":> LONGTEXT COLLATE")
1795
1796        def currentdate_sql(self, expression: exp.CurrentDate) -> str:
1797            timezone = expression.this
1798            if timezone:
1799                if isinstance(timezone, exp.Literal) and timezone.name.lower() == "utc":
1800                    return self.func("UTC_DATE")
1801                self.unsupported("CurrentDate with timezone is not supported in SingleStore")
1802
1803            return self.func("CURRENT_DATE")
1804
1805        def currenttime_sql(self, expression: exp.CurrentTime) -> str:
1806            arg = expression.this
1807            if arg:
1808                if isinstance(arg, exp.Literal) and arg.name.lower() == "utc":
1809                    return self.func("UTC_TIME")
1810                if isinstance(arg, exp.Literal) and arg.is_number:
1811                    return self.func("CURRENT_TIME", arg)
1812                self.unsupported("CurrentTime with timezone is not supported in SingleStore")
1813
1814            return self.func("CURRENT_TIME")
1815
1816        def currenttimestamp_sql(self, expression: exp.CurrentTimestamp) -> str:
1817            arg = expression.this
1818            if arg:
1819                if isinstance(arg, exp.Literal) and arg.name.lower() == "utc":
1820                    return self.func("UTC_TIMESTAMP")
1821                if isinstance(arg, exp.Literal) and arg.is_number:
1822                    return self.func("CURRENT_TIMESTAMP", arg)
1823                self.unsupported("CurrentTimestamp with timezone is not supported in SingleStore")
1824
1825            return self.func("CURRENT_TIMESTAMP")
1826
1827        def standardhash_sql(self, expression: exp.StandardHash) -> str:
1828            hash_function = expression.expression
1829            if hash_function is None:
1830                return self.func("SHA", expression.this)
1831            if isinstance(hash_function, exp.Literal):
1832                if hash_function.name.lower() == "sha":
1833                    return self.func("SHA", expression.this)
1834                if hash_function.name.lower() == "md5":
1835                    return self.func("MD5", expression.this)
1836
1837                self.unsupported(
1838                    f"{hash_function.this} hash method is not supported in SingleStore"
1839                )
1840                return self.func("SHA", expression.this)
1841
1842            self.unsupported("STANDARD_HASH function is not supported in SingleStore")
1843            return self.func("SHA", expression.this)
1844
1845        @unsupported_args("is_database", "exists", "cluster", "identity", "option", "partition")
1846        def truncatetable_sql(self, expression: exp.TruncateTable) -> str:
1847            statements = []
1848            for expression in expression.expressions:
1849                statements.append(f"TRUNCATE {self.sql(expression)}")
1850
1851            return "; ".join(statements)
1852
1853        @unsupported_args("exists")
1854        def renamecolumn_sql(self, expression: exp.RenameColumn) -> str:
1855            old_column = self.sql(expression, "this")
1856            new_column = self.sql(expression, "to")
1857            return f"CHANGE {old_column} {new_column}"
1858
1859        @unsupported_args("drop", "comment", "allow_null", "visible", "using")
1860        def altercolumn_sql(self, expression: exp.AlterColumn) -> str:
1861            alter = super().altercolumn_sql(expression)
1862
1863            collate = self.sql(expression, "collate")
1864            collate = f" COLLATE {collate}" if collate else ""
1865            return f"{alter}{collate}"
1866
1867        def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
1868            this = self.sql(expression, "this")
1869            not_null = " NOT NULL" if expression.args.get("not_null") else ""
1870            type = self.sql(expression, "data_type") or "AUTO"
1871            return f"AS {this} PERSISTED {type}{not_null}"