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.from_str(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",
 365    }
 366
 367    TYPE_PARAM_SETTINGS = {
 368        **MySQLGenerator.TYPE_PARAM_SETTINGS,
 369        exp.DType.TIMESTAMP_MS: ((6,), ()),
 370    }
 371
 372    # https://docs.singlestore.com/cloud/reference/sql-reference/restricted-keywords/list-of-restricted-keywords/
 373    RESERVED_KEYWORDS = {
 374        "abs",
 375        "absolute",
 376        "access",
 377        "account",
 378        "acos",
 379        "action",
 380        "add",
 381        "adddate",
 382        "addtime",
 383        "admin",
 384        "aes_decrypt",
 385        "aes_encrypt",
 386        "after",
 387        "against",
 388        "aggregate",
 389        "aggregates",
 390        "aggregator",
 391        "aggregator_id",
 392        "aggregator_plan_hash",
 393        "aggregators",
 394        "algorithm",
 395        "all",
 396        "also",
 397        "alter",
 398        "always",
 399        "analyse",
 400        "analyze",
 401        "and",
 402        "anti_join",
 403        "any",
 404        "any_value",
 405        "approx_count_distinct",
 406        "approx_count_distinct_accumulate",
 407        "approx_count_distinct_combine",
 408        "approx_count_distinct_estimate",
 409        "approx_geography_intersects",
 410        "approx_percentile",
 411        "arghistory",
 412        "arrange",
 413        "arrangement",
 414        "array",
 415        "as",
 416        "asc",
 417        "ascii",
 418        "asensitive",
 419        "asin",
 420        "asm",
 421        "assertion",
 422        "assignment",
 423        "ast",
 424        "asymmetric",
 425        "async",
 426        "at",
 427        "atan",
 428        "atan2",
 429        "attach",
 430        "attribute",
 431        "authorization",
 432        "auto",
 433        "auto_increment",
 434        "auto_reprovision",
 435        "autostats",
 436        "autostats_cardinality_mode",
 437        "autostats_enabled",
 438        "autostats_histogram_mode",
 439        "autostats_sampling",
 440        "availability",
 441        "avg",
 442        "avg_row_length",
 443        "avro",
 444        "azure",
 445        "background",
 446        "_background_threads_for_cleanup",
 447        "backup",
 448        "backup_history",
 449        "backup_id",
 450        "backward",
 451        "batch",
 452        "batches",
 453        "batch_interval",
 454        "_batch_size_limit",
 455        "before",
 456        "begin",
 457        "between",
 458        "bigint",
 459        "bin",
 460        "binary",
 461        "_binary",
 462        "bit",
 463        "bit_and",
 464        "bit_count",
 465        "bit_or",
 466        "bit_xor",
 467        "blob",
 468        "bool",
 469        "boolean",
 470        "bootstrap",
 471        "both",
 472        "_bt",
 473        "btree",
 474        "bucket_count",
 475        "by",
 476        "byte",
 477        "byte_length",
 478        "cache",
 479        "call",
 480        "call_for_pipeline",
 481        "called",
 482        "capture",
 483        "cascade",
 484        "cascaded",
 485        "case",
 486        "cast",
 487        "catalog",
 488        "ceil",
 489        "ceiling",
 490        "chain",
 491        "change",
 492        "char",
 493        "character",
 494        "characteristics",
 495        "character_length",
 496        "char_length",
 497        "charset",
 498        "check",
 499        "checkpoint",
 500        "_check_can_connect",
 501        "_check_consistency",
 502        "checksum",
 503        "_checksum",
 504        "class",
 505        "clear",
 506        "client",
 507        "client_found_rows",
 508        "close",
 509        "cluster",
 510        "clustered",
 511        "cnf",
 512        "coalesce",
 513        "coercibility",
 514        "collate",
 515        "collation",
 516        "collect",
 517        "column",
 518        "columnar",
 519        "columns",
 520        "columnstore",
 521        "columnstore_segment_rows",
 522        "comment",
 523        "comments",
 524        "commit",
 525        "committed",
 526        "_commit_log_tail",
 527        "committed",
 528        "compact",
 529        "compile",
 530        "compressed",
 531        "compression",
 532        "concat",
 533        "concat_ws",
 534        "concurrent",
 535        "concurrently",
 536        "condition",
 537        "configuration",
 538        "connection",
 539        "connection_id",
 540        "connections",
 541        "config",
 542        "constraint",
 543        "constraints",
 544        "content",
 545        "continue",
 546        "_continue_replay",
 547        "conv",
 548        "conversion",
 549        "convert",
 550        "convert_tz",
 551        "copy",
 552        "_core",
 553        "cos",
 554        "cost",
 555        "cot",
 556        "count",
 557        "create",
 558        "credentials",
 559        "cross",
 560        "cube",
 561        "csv",
 562        "cume_dist",
 563        "curdate",
 564        "current",
 565        "current_catalog",
 566        "current_date",
 567        "current_role",
 568        "current_schema",
 569        "current_security_groups",
 570        "current_security_roles",
 571        "current_time",
 572        "current_timestamp",
 573        "current_user",
 574        "cursor",
 575        "curtime",
 576        "cycle",
 577        "data",
 578        "database",
 579        "databases",
 580        "date",
 581        "date_add",
 582        "datediff",
 583        "date_format",
 584        "date_sub",
 585        "date_trunc",
 586        "datetime",
 587        "day",
 588        "day_hour",
 589        "day_microsecond",
 590        "day_minute",
 591        "dayname",
 592        "dayofmonth",
 593        "dayofweek",
 594        "dayofyear",
 595        "day_second",
 596        "deallocate",
 597        "dec",
 598        "decimal",
 599        "declare",
 600        "decode",
 601        "default",
 602        "defaults",
 603        "deferrable",
 604        "deferred",
 605        "defined",
 606        "definer",
 607        "degrees",
 608        "delayed",
 609        "delay_key_write",
 610        "delete",
 611        "delimiter",
 612        "delimiters",
 613        "dense_rank",
 614        "desc",
 615        "describe",
 616        "detach",
 617        "deterministic",
 618        "dictionary",
 619        "differential",
 620        "directory",
 621        "disable",
 622        "discard",
 623        "_disconnect",
 624        "disk",
 625        "distinct",
 626        "distinctrow",
 627        "distributed_joins",
 628        "div",
 629        "do",
 630        "document",
 631        "domain",
 632        "dot_product",
 633        "double",
 634        "drop",
 635        "_drop_profile",
 636        "dual",
 637        "dump",
 638        "duplicate",
 639        "dynamic",
 640        "earliest",
 641        "each",
 642        "echo",
 643        "election",
 644        "else",
 645        "elseif",
 646        "elt",
 647        "enable",
 648        "enclosed",
 649        "encoding",
 650        "encrypted",
 651        "end",
 652        "engine",
 653        "engines",
 654        "enum",
 655        "errors",
 656        "escape",
 657        "escaped",
 658        "estimate",
 659        "euclidean_distance",
 660        "event",
 661        "events",
 662        "except",
 663        "exclude",
 664        "excluding",
 665        "exclusive",
 666        "execute",
 667        "exists",
 668        "exit",
 669        "exp",
 670        "explain",
 671        "extended",
 672        "extension",
 673        "external",
 674        "external_host",
 675        "external_port",
 676        "extract",
 677        "extractor",
 678        "extractors",
 679        "extra_join",
 680        "_failover",
 681        "failed_login_attempts",
 682        "failure",
 683        "false",
 684        "family",
 685        "fault",
 686        "fetch",
 687        "field",
 688        "fields",
 689        "file",
 690        "files",
 691        "fill",
 692        "first",
 693        "first_value",
 694        "fix_alter",
 695        "fixed",
 696        "float",
 697        "float4",
 698        "float8",
 699        "floor",
 700        "flush",
 701        "following",
 702        "for",
 703        "force",
 704        "force_compiled_mode",
 705        "force_interpreter_mode",
 706        "foreground",
 707        "foreign",
 708        "format",
 709        "forward",
 710        "found_rows",
 711        "freeze",
 712        "from",
 713        "from_base64",
 714        "from_days",
 715        "from_unixtime",
 716        "fs",
 717        "_fsync",
 718        "full",
 719        "fulltext",
 720        "function",
 721        "functions",
 722        "gc",
 723        "gcs",
 724        "get_format",
 725        "_gc",
 726        "_gcx",
 727        "generate",
 728        "geography",
 729        "geography_area",
 730        "geography_contains",
 731        "geography_distance",
 732        "geography_intersects",
 733        "geography_latitude",
 734        "geography_length",
 735        "geography_longitude",
 736        "geographypoint",
 737        "geography_point",
 738        "geography_within_distance",
 739        "geometry",
 740        "geometry_area",
 741        "geometry_contains",
 742        "geometry_distance",
 743        "geometry_filter",
 744        "geometry_intersects",
 745        "geometry_length",
 746        "geometrypoint",
 747        "geometry_point",
 748        "geometry_within_distance",
 749        "geometry_x",
 750        "geometry_y",
 751        "global",
 752        "_global_version_timestamp",
 753        "grant",
 754        "granted",
 755        "grants",
 756        "greatest",
 757        "group",
 758        "grouping",
 759        "groups",
 760        "group_concat",
 761        "gzip",
 762        "handle",
 763        "handler",
 764        "hard_cpu_limit_percentage",
 765        "hash",
 766        "has_temp_tables",
 767        "having",
 768        "hdfs",
 769        "header",
 770        "heartbeat_no_logging",
 771        "hex",
 772        "highlight",
 773        "high_priority",
 774        "hold",
 775        "holding",
 776        "host",
 777        "hosts",
 778        "hour",
 779        "hour_microsecond",
 780        "hour_minute",
 781        "hour_second",
 782        "identified",
 783        "identity",
 784        "if",
 785        "ifnull",
 786        "ignore",
 787        "ilike",
 788        "immediate",
 789        "immutable",
 790        "implicit",
 791        "import",
 792        "in",
 793        "including",
 794        "increment",
 795        "incremental",
 796        "index",
 797        "indexes",
 798        "inet_aton",
 799        "inet_ntoa",
 800        "inet6_aton",
 801        "inet6_ntoa",
 802        "infile",
 803        "inherit",
 804        "inherits",
 805        "_init_profile",
 806        "init",
 807        "initcap",
 808        "initialize",
 809        "initially",
 810        "inject",
 811        "inline",
 812        "inner",
 813        "inout",
 814        "input",
 815        "insensitive",
 816        "insert",
 817        "insert_method",
 818        "instance",
 819        "instead",
 820        "instr",
 821        "int",
 822        "int1",
 823        "int2",
 824        "int3",
 825        "int4",
 826        "int8",
 827        "integer",
 828        "_internal_dynamic_typecast",
 829        "interpreter_mode",
 830        "intersect",
 831        "interval",
 832        "into",
 833        "invoker",
 834        "is",
 835        "isnull",
 836        "isolation",
 837        "iterate",
 838        "join",
 839        "json",
 840        "json_agg",
 841        "json_array_contains_double",
 842        "json_array_contains_json",
 843        "json_array_contains_string",
 844        "json_array_push_double",
 845        "json_array_push_json",
 846        "json_array_push_string",
 847        "json_delete_key",
 848        "json_extract_double",
 849        "json_extract_json",
 850        "json_extract_string",
 851        "json_extract_bigint",
 852        "json_get_type",
 853        "json_length",
 854        "json_set_double",
 855        "json_set_json",
 856        "json_set_string",
 857        "json_splice_double",
 858        "json_splice_json",
 859        "json_splice_string",
 860        "kafka",
 861        "key",
 862        "key_block_size",
 863        "keys",
 864        "kill",
 865        "killall",
 866        "label",
 867        "lag",
 868        "language",
 869        "large",
 870        "last",
 871        "last_day",
 872        "last_insert_id",
 873        "last_value",
 874        "lateral",
 875        "latest",
 876        "lc_collate",
 877        "lc_ctype",
 878        "lcase",
 879        "lead",
 880        "leading",
 881        "leaf",
 882        "leakproof",
 883        "least",
 884        "leave",
 885        "leaves",
 886        "left",
 887        "length",
 888        "level",
 889        "license",
 890        "like",
 891        "limit",
 892        "lines",
 893        "listen",
 894        "llvm",
 895        "ln",
 896        "load",
 897        "loaddata_where",
 898        "_load",
 899        "local",
 900        "localtime",
 901        "localtimestamp",
 902        "locate",
 903        "location",
 904        "lock",
 905        "log",
 906        "log10",
 907        "log2",
 908        "long",
 909        "longblob",
 910        "longtext",
 911        "loop",
 912        "lower",
 913        "low_priority",
 914        "lpad",
 915        "_ls",
 916        "ltrim",
 917        "lz4",
 918        "management",
 919        "_management_thread",
 920        "mapping",
 921        "master",
 922        "match",
 923        "materialized",
 924        "max",
 925        "maxvalue",
 926        "max_concurrency",
 927        "max_errors",
 928        "max_partitions_per_batch",
 929        "max_queue_depth",
 930        "max_retries_per_batch_partition",
 931        "max_rows",
 932        "mbc",
 933        "md5",
 934        "mpl",
 935        "median",
 936        "mediumblob",
 937        "mediumint",
 938        "mediumtext",
 939        "member",
 940        "memory",
 941        "memory_percentage",
 942        "_memsql_table_id_lookup",
 943        "memsql",
 944        "memsql_deserialize",
 945        "memsql_imitating_kafka",
 946        "memsql_serialize",
 947        "merge",
 948        "metadata",
 949        "microsecond",
 950        "middleint",
 951        "min",
 952        "min_rows",
 953        "minus",
 954        "minute",
 955        "minute_microsecond",
 956        "minute_second",
 957        "minvalue",
 958        "mod",
 959        "mode",
 960        "model",
 961        "modifies",
 962        "modify",
 963        "month",
 964        "monthname",
 965        "months_between",
 966        "move",
 967        "mpl",
 968        "names",
 969        "named",
 970        "namespace",
 971        "national",
 972        "natural",
 973        "nchar",
 974        "next",
 975        "no",
 976        "node",
 977        "none",
 978        "no_query_rewrite",
 979        "noparam",
 980        "not",
 981        "nothing",
 982        "notify",
 983        "now",
 984        "nowait",
 985        "no_write_to_binlog",
 986        "no_query_rewrite",
 987        "norely",
 988        "nth_value",
 989        "ntile",
 990        "null",
 991        "nullcols",
 992        "nullif",
 993        "nulls",
 994        "numeric",
 995        "nvarchar",
 996        "object",
 997        "octet_length",
 998        "of",
 999        "off",
1000        "offline",
1001        "offset",
1002        "offsets",
1003        "oids",
1004        "on",
1005        "online",
1006        "only",
1007        "open",
1008        "operator",
1009        "optimization",
1010        "optimize",
1011        "optimizer",
1012        "optimizer_state",
1013        "option",
1014        "options",
1015        "optionally",
1016        "or",
1017        "order",
1018        "ordered_serialize",
1019        "orphan",
1020        "out",
1021        "out_of_order",
1022        "outer",
1023        "outfile",
1024        "over",
1025        "overlaps",
1026        "overlay",
1027        "owned",
1028        "owner",
1029        "pack_keys",
1030        "paired",
1031        "parser",
1032        "parquet",
1033        "partial",
1034        "partition",
1035        "partition_id",
1036        "partitioning",
1037        "partitions",
1038        "passing",
1039        "password",
1040        "password_lock_time",
1041        "parser",
1042        "pause",
1043        "_pause_replay",
1044        "percent_rank",
1045        "percentile_cont",
1046        "percentile_disc",
1047        "periodic",
1048        "persisted",
1049        "pi",
1050        "pipeline",
1051        "pipelines",
1052        "pivot",
1053        "placing",
1054        "plan",
1055        "plans",
1056        "plancache",
1057        "plugins",
1058        "pool",
1059        "pools",
1060        "port",
1061        "position",
1062        "pow",
1063        "power",
1064        "preceding",
1065        "precision",
1066        "prepare",
1067        "prepared",
1068        "preserve",
1069        "primary",
1070        "prior",
1071        "privileges",
1072        "procedural",
1073        "procedure",
1074        "procedures",
1075        "process",
1076        "processlist",
1077        "profile",
1078        "profiles",
1079        "program",
1080        "promote",
1081        "proxy",
1082        "purge",
1083        "quarter",
1084        "queries",
1085        "query",
1086        "query_timeout",
1087        "queue",
1088        "quote",
1089        "radians",
1090        "rand",
1091        "range",
1092        "rank",
1093        "read",
1094        "_read",
1095        "reads",
1096        "real",
1097        "reassign",
1098        "rebalance",
1099        "recheck",
1100        "record",
1101        "recursive",
1102        "redundancy",
1103        "redundant",
1104        "ref",
1105        "reference",
1106        "references",
1107        "refresh",
1108        "regexp",
1109        "reindex",
1110        "relative",
1111        "release",
1112        "reload",
1113        "rely",
1114        "remote",
1115        "remove",
1116        "rename",
1117        "repair",
1118        "_repair_table",
1119        "repeat",
1120        "repeatable",
1121        "_repl",
1122        "_reprovisioning",
1123        "replace",
1124        "replica",
1125        "replicate",
1126        "replicating",
1127        "replication",
1128        "durability",
1129        "require",
1130        "resource",
1131        "resource_pool",
1132        "reset",
1133        "restart",
1134        "restore",
1135        "restrict",
1136        "result",
1137        "_resurrect",
1138        "retry",
1139        "return",
1140        "returning",
1141        "returns",
1142        "reverse",
1143        "revoke",
1144        "rg_pool",
1145        "right",
1146        "right_anti_join",
1147        "right_semi_join",
1148        "right_straight_join",
1149        "rlike",
1150        "role",
1151        "roles",
1152        "rollback",
1153        "rollup",
1154        "round",
1155        "routine",
1156        "row",
1157        "row_count",
1158        "row_format",
1159        "row_number",
1160        "rows",
1161        "rowstore",
1162        "rule",
1163        "rpad",
1164        "_rpc",
1165        "rtrim",
1166        "running",
1167        "s3",
1168        "safe",
1169        "save",
1170        "savepoint",
1171        "scalar",
1172        "schema",
1173        "schemas",
1174        "schema_binding",
1175        "scroll",
1176        "search",
1177        "second",
1178        "second_microsecond",
1179        "sec_to_time",
1180        "security",
1181        "select",
1182        "semi_join",
1183        "_send_threads",
1184        "sensitive",
1185        "separator",
1186        "sequence",
1187        "sequences",
1188        "serial",
1189        "serializable",
1190        "series",
1191        "service_user",
1192        "server",
1193        "session",
1194        "session_user",
1195        "set",
1196        "setof",
1197        "security_lists_intersect",
1198        "sha",
1199        "sha1",
1200        "sha2",
1201        "shard",
1202        "sharded",
1203        "sharded_id",
1204        "share",
1205        "show",
1206        "shutdown",
1207        "sigmoid",
1208        "sign",
1209        "signal",
1210        "similar",
1211        "simple",
1212        "site",
1213        "signed",
1214        "sin",
1215        "skip",
1216        "skipped_batches",
1217        "sleep",
1218        "_sleep",
1219        "smallint",
1220        "snapshot",
1221        "_snapshot",
1222        "_snapshots",
1223        "soft_cpu_limit_percentage",
1224        "some",
1225        "soname",
1226        "sparse",
1227        "spatial",
1228        "spatial_check_index",
1229        "specific",
1230        "split",
1231        "sql",
1232        "sql_big_result",
1233        "sql_buffer_result",
1234        "sql_cache",
1235        "sql_calc_found_rows",
1236        "sqlexception",
1237        "sql_mode",
1238        "sql_no_cache",
1239        "sql_no_logging",
1240        "sql_small_result",
1241        "sqlstate",
1242        "sqlwarning",
1243        "sqrt",
1244        "ssl",
1245        "stable",
1246        "standalone",
1247        "start",
1248        "starting",
1249        "state",
1250        "statement",
1251        "statistics",
1252        "stats",
1253        "status",
1254        "std",
1255        "stddev",
1256        "stddev_pop",
1257        "stddev_samp",
1258        "stdin",
1259        "stdout",
1260        "stop",
1261        "storage",
1262        "str_to_date",
1263        "straight_join",
1264        "strict",
1265        "string",
1266        "strip",
1267        "subdate",
1268        "substr",
1269        "substring",
1270        "substring_index",
1271        "success",
1272        "sum",
1273        "super",
1274        "symmetric",
1275        "sync_snapshot",
1276        "sync",
1277        "_sync",
1278        "_sync2",
1279        "_sync_partitions",
1280        "_sync_snapshot",
1281        "synchronize",
1282        "sysid",
1283        "system",
1284        "table",
1285        "table_checksum",
1286        "tables",
1287        "tablespace",
1288        "tags",
1289        "tan",
1290        "target_size",
1291        "task",
1292        "temp",
1293        "template",
1294        "temporary",
1295        "temptable",
1296        "_term_bump",
1297        "terminate",
1298        "terminated",
1299        "test",
1300        "text",
1301        "then",
1302        "time",
1303        "timediff",
1304        "time_bucket",
1305        "time_format",
1306        "timeout",
1307        "timestamp",
1308        "timestampadd",
1309        "timestampdiff",
1310        "timezone",
1311        "time_to_sec",
1312        "tinyblob",
1313        "tinyint",
1314        "tinytext",
1315        "to",
1316        "to_base64",
1317        "to_char",
1318        "to_date",
1319        "to_days",
1320        "to_json",
1321        "to_number",
1322        "to_seconds",
1323        "to_timestamp",
1324        "tracelogs",
1325        "traditional",
1326        "trailing",
1327        "transform",
1328        "transaction",
1329        "_transactions_experimental",
1330        "treat",
1331        "trigger",
1332        "triggers",
1333        "trim",
1334        "true",
1335        "trunc",
1336        "truncate",
1337        "trusted",
1338        "two_phase",
1339        "_twopcid",
1340        "type",
1341        "types",
1342        "ucase",
1343        "unbounded",
1344        "uncommitted",
1345        "undefined",
1346        "undo",
1347        "unencrypted",
1348        "unenforced",
1349        "unhex",
1350        "unhold",
1351        "unicode",
1352        "union",
1353        "unique",
1354        "_unittest",
1355        "unix_timestamp",
1356        "unknown",
1357        "unlisten",
1358        "_unload",
1359        "unlock",
1360        "unlogged",
1361        "unpivot",
1362        "unsigned",
1363        "until",
1364        "update",
1365        "upgrade",
1366        "upper",
1367        "usage",
1368        "use",
1369        "user",
1370        "users",
1371        "using",
1372        "utc_date",
1373        "utc_time",
1374        "utc_timestamp",
1375        "_utf8",
1376        "vacuum",
1377        "valid",
1378        "validate",
1379        "validator",
1380        "value",
1381        "values",
1382        "varbinary",
1383        "varchar",
1384        "varcharacter",
1385        "variables",
1386        "variadic",
1387        "variance",
1388        "var_pop",
1389        "var_samp",
1390        "varying",
1391        "vector_sub",
1392        "verbose",
1393        "version",
1394        "view",
1395        "void",
1396        "volatile",
1397        "voting",
1398        "wait",
1399        "_wake",
1400        "warnings",
1401        "week",
1402        "weekday",
1403        "weekofyear",
1404        "when",
1405        "where",
1406        "while",
1407        "whitespace",
1408        "window",
1409        "with",
1410        "without",
1411        "within",
1412        "_wm_heartbeat",
1413        "work",
1414        "workload",
1415        "wrapper",
1416        "write",
1417        "xact_id",
1418        "xor",
1419        "year",
1420        "year_month",
1421        "yes",
1422        "zerofill",
1423        "zone",
1424    }
1425
1426    def jsonextractscalar_sql(self, expression: exp.JSONExtractScalar) -> str:
1427        json_type = expression.args.get("json_type")
1428        func_name = "JSON_EXTRACT_JSON" if json_type is None else f"JSON_EXTRACT_{json_type}"
1429        return json_extract_segments(func_name)(self, expression)
1430
1431    def jsonbextractscalar_sql(self, expression: exp.JSONBExtractScalar) -> str:
1432        json_type = expression.args.get("json_type")
1433        func_name = "BSON_EXTRACT_BSON" if json_type is None else f"BSON_EXTRACT_{json_type}"
1434        return json_extract_segments(func_name)(self, expression)
1435
1436    def jsonextractarray_sql(self, expression: exp.JSONExtractArray) -> str:
1437        self.unsupported("Arrays are not supported in SingleStore")
1438        return self.function_fallback_sql(expression)
1439
1440    def jsonvalue_sql(self, expression: exp.JSONValue) -> str:
1441        if expression.args.get("on_condition"):
1442            self.unsupported("JSON_VALUE does not support on_condition")
1443        res: exp.Expr = exp.JSONExtractScalar(
1444            this=expression.this,
1445            expression=expression.args.get("path"),
1446            json_type="STRING",
1447        )
1448
1449        returning = expression.args.get("returning")
1450        if returning is not None:
1451            res = exp.Cast(this=res, to=returning)
1452
1453        return self.sql(res)
1454
1455    def all_sql(self, expression: exp.All) -> str:
1456        self.unsupported("ALL subquery predicate is not supported in SingleStore")
1457        return super().all_sql(expression)
1458
1459    def jsonarraycontains_sql(self, expression: exp.JSONArrayContains) -> str:
1460        json_type = expression.text("json_type").upper()
1461
1462        if json_type:
1463            return self.func(
1464                f"JSON_ARRAY_CONTAINS_{json_type}", expression.expression, expression.this
1465            )
1466
1467        return self.func(
1468            "JSON_ARRAY_CONTAINS_JSON",
1469            expression.expression,
1470            self.func("TO_JSON", expression.this),
1471        )
1472
1473    def datatype_sql(self, expression: exp.DataType) -> str:
1474        for arg_name in ("kind", "values"):
1475            if expression.args.get(arg_name):
1476                self.unsupported(f"DATATYPE does not support {arg_name}")
1477        if expression.args.get("nested") and not expression.is_type(exp.DType.STRUCT):
1478            self.unsupported(
1479                f"Argument 'nested' is not supported for representation of '{expression.this.value}' in SingleStore"
1480            )
1481
1482        if expression.is_type(exp.DType.VARBINARY) and not expression.expressions:
1483            # `VARBINARY` must always have a size - if it doesn't, we always generate `BLOB`
1484            return "BLOB"
1485        if expression.is_type(
1486            exp.DType.DECIMAL32,
1487            exp.DType.DECIMAL64,
1488            exp.DType.DECIMAL128,
1489            exp.DType.DECIMAL256,
1490        ):
1491            scale = self.expressions(expression, flat=True)
1492
1493            if expression.is_type(exp.DType.DECIMAL32):
1494                precision = "9"
1495            elif expression.is_type(exp.DType.DECIMAL64):
1496                precision = "18"
1497            elif expression.is_type(exp.DType.DECIMAL128):
1498                precision = "38"
1499            else:
1500                # 65 is a maximum precision supported in SingleStore
1501                precision = "65"
1502            if scale is not None:
1503                return f"DECIMAL({precision}, {scale[0]})"
1504            else:
1505                return f"DECIMAL({precision})"
1506        if expression.is_type(exp.DType.VECTOR):
1507            expressions = expression.expressions
1508            if len(expressions) == 2:
1509                type_name = self.sql(expressions[0])
1510                if type_name in self.dialect.INVERSE_VECTOR_TYPE_ALIASES:
1511                    type_name = self.dialect.INVERSE_VECTOR_TYPE_ALIASES[type_name]
1512
1513                return f"VECTOR({self.sql(expressions[1])}, {type_name})"
1514
1515        return super().datatype_sql(expression)
1516
1517    def collate_sql(self, expression: exp.Collate) -> str:
1518        # SingleStore does not support setting a collation for column in the SELECT query,
1519        # so we cast column to a LONGTEXT type with specific collation
1520        return self.binary(expression, ":> LONGTEXT COLLATE")
1521
1522    def currentdate_sql(self, expression: exp.CurrentDate) -> str:
1523        timezone = expression.this
1524        if timezone:
1525            if isinstance(timezone, exp.Literal) and timezone.name.lower() == "utc":
1526                return self.func("UTC_DATE")
1527            self.unsupported("CurrentDate with timezone is not supported in SingleStore")
1528
1529        return self.func("CURRENT_DATE")
1530
1531    def currenttime_sql(self, expression: exp.CurrentTime) -> str:
1532        arg = expression.this
1533        if arg:
1534            if isinstance(arg, exp.Literal) and arg.name.lower() == "utc":
1535                return self.func("UTC_TIME")
1536            if isinstance(arg, exp.Literal) and arg.is_number:
1537                return self.func("CURRENT_TIME", arg)
1538            self.unsupported("CurrentTime with timezone is not supported in SingleStore")
1539
1540        return self.func("CURRENT_TIME")
1541
1542    def currenttimestamp_sql(self, expression: exp.CurrentTimestamp) -> str:
1543        arg = expression.this
1544        if arg:
1545            if isinstance(arg, exp.Literal) and arg.name.lower() == "utc":
1546                return self.func("UTC_TIMESTAMP")
1547            if isinstance(arg, exp.Literal) and arg.is_number:
1548                return self.func("CURRENT_TIMESTAMP", arg)
1549            self.unsupported("CurrentTimestamp with timezone is not supported in SingleStore")
1550
1551        return self.func("CURRENT_TIMESTAMP")
1552
1553    def standardhash_sql(self, expression: exp.StandardHash) -> str:
1554        hash_function = expression.expression
1555        if hash_function is None:
1556            return self.func("SHA", expression.this)
1557        if isinstance(hash_function, exp.Literal):
1558            if hash_function.name.lower() == "sha":
1559                return self.func("SHA", expression.this)
1560            if hash_function.name.lower() == "md5":
1561                return self.func("MD5", expression.this)
1562
1563            self.unsupported(f"{hash_function.this} hash method is not supported in SingleStore")
1564            return self.func("SHA", expression.this)
1565
1566        self.unsupported("STANDARD_HASH function is not supported in SingleStore")
1567        return self.func("SHA", expression.this)
1568
1569    def truncatetable_sql(self, expression: exp.TruncateTable) -> str:
1570        for arg_name in ("is_database", "exists", "cluster", "identity", "option", "partition"):
1571            if expression.args.get(arg_name):
1572                self.unsupported(f"TRUNCATE TABLE does not support {arg_name}")
1573        statements = []
1574        for table in expression.expressions:
1575            statements.append(f"TRUNCATE {self.sql(table)}")
1576
1577        return "; ".join(statements)
1578
1579    def renamecolumn_sql(self, expression: exp.RenameColumn) -> str:
1580        if expression.args.get("exists"):
1581            self.unsupported("RENAME COLUMN does not support exists")
1582        old_column = self.sql(expression, "this")
1583        new_column = self.sql(expression, "to")
1584        return f"CHANGE {old_column} {new_column}"
1585
1586    def altercolumn_sql(self, expression: exp.AlterColumn) -> str:
1587        for arg_name in ("drop", "comment", "allow_null", "visible", "using"):
1588            if expression.args.get(arg_name):
1589                self.unsupported(f"ALTER COLUMN does not support {arg_name}")
1590        alter = super().altercolumn_sql(expression)
1591
1592        collate = self.sql(expression, "collate")
1593        collate = f" COLLATE {collate}" if collate else ""
1594        return f"{alter}{collate}"
1595
1596    def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
1597        this = self.sql(expression, "this")
1598        not_null = " NOT NULL" if expression.args.get("not_null") else ""
1599        type = self.sql(expression, "data_type") or "AUTO"
1600        return f"AS {this} PERSISTED {type}{not_null}"