Merging upstream version 21.0.1.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 61 insertions, 13 deletions

diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index a5e04da..70ea97e 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -14,7 +14,6 @@ from sqlglot.dialects.dialect import (
     max_or_greatest,
     min_or_least,
     parse_date_delta,
-    path_to_jsonpath,
     rename_func,
     timestrtotime_sql,
     trim_sql,
@@ -266,13 +265,32 @@ def _parse_timefromparts(args: t.List) -> exp.TimeFromParts:
     )
 
 
-def _parse_len(args: t.List) -> exp.Length:
-    this = seq_get(args, 0)
+def _parse_as_text(
+    klass: t.Type[exp.Expression],
+) -> t.Callable[[t.List[exp.Expression]], exp.Expression]:
+    def _parse(args: t.List[exp.Expression]) -> exp.Expression:
+        this = seq_get(args, 0)
+
+        if this and not this.is_string:
+            this = exp.cast(this, exp.DataType.Type.TEXT)
+
+        expression = seq_get(args, 1)
+        kwargs = {"this": this}
+
+        if expression:
+            kwargs["expression"] = expression
 
-    if this and not this.is_string:
-        this = exp.cast(this, exp.DataType.Type.TEXT)
+        return klass(**kwargs)
 
-    return exp.Length(this=this)
+    return _parse
+
+
+def _json_extract_sql(
+    self: TSQL.Generator, expression: exp.JSONExtract | exp.JSONExtractScalar
+) -> str:
+    json_query = rename_func("JSON_QUERY")(self, expression)
+    json_value = rename_func("JSON_VALUE")(self, expression)
+    return self.func("ISNULL", json_query, json_value)
 
 
 class TSQL(Dialect):
@@ -441,8 +459,11 @@ class TSQL(Dialect):
             "HASHBYTES": _parse_hashbytes,
             "IIF": exp.If.from_arg_list,
             "ISNULL": exp.Coalesce.from_arg_list,
-            "JSON_VALUE": exp.JSONExtractScalar.from_arg_list,
-            "LEN": _parse_len,
+            "JSON_QUERY": parser.parse_extract_json_with_path(exp.JSONExtract),
+            "JSON_VALUE": parser.parse_extract_json_with_path(exp.JSONExtractScalar),
+            "LEN": _parse_as_text(exp.Length),
+            "LEFT": _parse_as_text(exp.Left),
+            "RIGHT": _parse_as_text(exp.Right),
             "REPLICATE": exp.Repeat.from_arg_list,
             "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)),
             "SYSDATETIME": exp.CurrentTimestamp.from_arg_list,
@@ -677,6 +698,7 @@ class TSQL(Dialect):
         SUPPORTS_SINGLE_ARG_CONCAT = False
         TABLESAMPLE_SEED_KEYWORD = "REPEATABLE"
         SUPPORTS_SELECT_INTO = True
+        JSON_PATH_BRACKETED_KEY_SUPPORTED = False
 
         EXPRESSIONS_WITHOUT_NESTED_CTES = {
             exp.Delete,
@@ -688,6 +710,12 @@ class TSQL(Dialect):
             exp.Update,
         }
 
+        SUPPORTED_JSON_PATH_PARTS = {
+            exp.JSONPathKey,
+            exp.JSONPathRoot,
+            exp.JSONPathSubscript,
+        }
+
         TYPE_MAPPING = {
             **generator.Generator.TYPE_MAPPING,
             exp.DataType.Type.BOOLEAN: "BIT",
@@ -712,9 +740,10 @@ class TSQL(Dialect):
             exp.CurrentTimestamp: rename_func("GETDATE"),
             exp.Extract: rename_func("DATEPART"),
             exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql,
-            exp.GetPath: path_to_jsonpath("JSON_VALUE"),
             exp.GroupConcat: _string_agg_sql,
             exp.If: rename_func("IIF"),
+            exp.JSONExtract: _json_extract_sql,
+            exp.JSONExtractScalar: _json_extract_sql,
             exp.LastDay: lambda self, e: self.func("EOMONTH", e.this),
             exp.Max: max_or_greatest,
             exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this),
@@ -831,15 +860,21 @@ class TSQL(Dialect):
             exists = expression.args.pop("exists", None)
             sql = super().create_sql(expression)
 
+            like_property = expression.find(exp.LikeProperty)
+            if like_property:
+                ctas_expression = like_property.this
+            else:
+                ctas_expression = expression.expression
+
             table = expression.find(exp.Table)
 
             # Convert CTAS statement to SELECT .. INTO ..
-            if kind == "TABLE" and expression.expression:
-                ctas_with = expression.expression.args.get("with")
+            if kind == "TABLE" and ctas_expression:
+                ctas_with = ctas_expression.args.get("with")
                 if ctas_with:
                     ctas_with = ctas_with.pop()
 
-                subquery = expression.expression
+                subquery = ctas_expression
                 if isinstance(subquery, exp.Subqueryable):
                     subquery = subquery.subquery()
 
@@ -847,6 +882,9 @@ class TSQL(Dialect):
                 select_into.set("into", exp.Into(this=table))
                 select_into.set("with", ctas_with)
 
+                if like_property:
+                    select_into.limit(0, copy=False)
+
                 sql = self.sql(select_into)
 
             if exists:
@@ -937,9 +975,19 @@ class TSQL(Dialect):
             return f"CONSTRAINT {this} {expressions}"
 
         def length_sql(self, expression: exp.Length) -> str:
+            return self._uncast_text(expression, "LEN")
+
+        def right_sql(self, expression: exp.Right) -> str:
+            return self._uncast_text(expression, "RIGHT")
+
+        def left_sql(self, expression: exp.Left) -> str:
+            return self._uncast_text(expression, "LEFT")
+
+        def _uncast_text(self, expression: exp.Expression, name: str) -> str:
             this = expression.this
             if isinstance(this, exp.Cast) and this.is_type(exp.DataType.Type.TEXT):
                 this_sql = self.sql(this, "this")
             else:
                 this_sql = self.sql(this)
-            return self.func("LEN", this_sql)
+            expression_sql = self.sql(expression, "expression")
+            return self.func(name, this_sql, expression_sql if expression_sql else None)