summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_tsql.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-02 23:59:11 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-02 23:59:11 +0000
commitcaea5267cb8e1fea3702adbdf6f68fd37d13b3b7 (patch)
treef06f1da1ab3b6906beca1c3c7222d28ff00766ac /tests/dialects/test_tsql.py
parentAdding upstream version 12.2.0. (diff)
downloadsqlglot-caea5267cb8e1fea3702adbdf6f68fd37d13b3b7.tar.xz
sqlglot-caea5267cb8e1fea3702adbdf6f68fd37d13b3b7.zip
Adding upstream version 15.0.0.upstream/15.0.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_tsql.py')
-rw-r--r--tests/dialects/test_tsql.py52
1 files changed, 49 insertions, 3 deletions
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 3a3ac73..8789aed 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -467,6 +467,11 @@ WHERE
def test_add_date(self):
self.validate_identity("SELECT DATEADD(year, 1, '2017/08/25')")
+
+ self.validate_all(
+ "DATEADD(year, 50, '2006-07-31')",
+ write={"bigquery": "DATE_ADD('2006-07-31', INTERVAL 50 YEAR)"},
+ )
self.validate_all(
"SELECT DATEADD(year, 1, '2017/08/25')",
write={"spark": "SELECT ADD_MONTHS('2017/08/25', 12)"},
@@ -525,7 +530,7 @@ WHERE
self.validate_all(
"SELECT x.a, x.b, t.v, t.y FROM x CROSS APPLY (SELECT v, y FROM t) t(v, y)",
write={
- "spark": "SELECT x.a, x.b, t.v, t.y FROM x JOIN LATERAL (SELECT v, y FROM t) AS t(v, y)",
+ "spark": "SELECT x.a, x.b, t.v, t.y FROM x, LATERAL (SELECT v, y FROM t) AS t(v, y)",
},
)
self.validate_all(
@@ -545,7 +550,7 @@ WHERE
self.validate_all(
"SELECT t.x, y.z FROM x CROSS APPLY tvfTest(t.x)y(z)",
write={
- "spark": "SELECT t.x, y.z FROM x JOIN LATERAL TVFTEST(t.x) AS y(z)",
+ "spark": "SELECT t.x, y.z FROM x, LATERAL TVFTEST(t.x) AS y(z)",
},
)
self.validate_all(
@@ -637,7 +642,7 @@ WHERE
self.assertIsInstance(expr.this, exp.Var)
self.assertEqual(expr.sql("tsql"), "@x")
- table = parse_one("select * from @x", read="tsql").args["from"].expressions[0]
+ table = parse_one("select * from @x", read="tsql").args["from"].this
self.assertIsInstance(table, exp.Table)
self.assertIsInstance(table.this, exp.Parameter)
self.assertIsInstance(table.this.this, exp.Var)
@@ -724,3 +729,44 @@ WHERE
},
)
self.validate_identity("SELECT x FROM a INNER LOOP JOIN b ON b.id = a.id")
+
+ def test_openjson(self):
+ self.validate_identity("SELECT * FROM OPENJSON(@json)")
+
+ self.validate_all(
+ """SELECT [key], value FROM OPENJSON(@json,'$.path.to."sub-object"')""",
+ write={
+ "tsql": """SELECT "key", value FROM OPENJSON(@json, '$.path.to."sub-object"')""",
+ },
+ )
+ self.validate_all(
+ "SELECT * FROM OPENJSON(@array) WITH (month VARCHAR(3), temp int, month_id tinyint '$.sql:identity()') as months",
+ write={
+ "tsql": "SELECT * FROM OPENJSON(@array) WITH (month VARCHAR(3), temp INTEGER, month_id TINYINT '$.sql:identity()') AS months",
+ },
+ )
+ self.validate_all(
+ """
+ SELECT *
+ FROM OPENJSON ( @json )
+ WITH (
+ Number VARCHAR(200) '$.Order.Number',
+ Date DATETIME '$.Order.Date',
+ Customer VARCHAR(200) '$.AccountNumber',
+ Quantity INT '$.Item.Quantity',
+ [Order] NVARCHAR(MAX) AS JSON
+ )
+ """,
+ write={
+ "tsql": """SELECT
+ *
+FROM OPENJSON(@json) WITH (
+ Number VARCHAR(200) '$.Order.Number',
+ Date DATETIME2 '$.Order.Date',
+ Customer VARCHAR(200) '$.AccountNumber',
+ Quantity INTEGER '$.Item.Quantity',
+ "Order" TEXT AS JSON
+)"""
+ },
+ pretty=True,
+ )