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