diff options
Diffstat (limited to 'tests/dialects/test_tsql.py')
| -rw-r--r-- | tests/dialects/test_tsql.py | 52 |
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, + ) |