sqlglot.generators.singlestore

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