diff options
Diffstat (limited to 'tests/dialects/test_tsql.py')
| -rw-r--r-- | tests/dialects/test_tsql.py | 123 |
1 files changed, 107 insertions, 16 deletions
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py index f0a590f..5266bd4 100644 --- a/tests/dialects/test_tsql.py +++ b/tests/dialects/test_tsql.py @@ -336,7 +336,7 @@ class TestTSQL(Validator): "CAST(x as TIME(4))", write={ "spark": "CAST(x AS TIMESTAMP)", - "tsql": "CAST(x AS TIMESTAMP(4))", + "tsql": "CAST(x AS TIME(4))", }, ) @@ -352,7 +352,7 @@ class TestTSQL(Validator): "CAST(x as DATETIMEOFFSET)", write={ "spark": "CAST(x AS TIMESTAMP)", - "tsql": "CAST(x AS TIMESTAMPTZ)", + "tsql": "CAST(x AS DATETIMEOFFSET)", }, ) @@ -393,7 +393,30 @@ class TestTSQL(Validator): self.validate_all( "CREATE TABLE #mytemp (a INTEGER, b CHAR(2), c TIME(4), d FLOAT(24))", write={ - "tsql": "CREATE TABLE #mytemp (a INTEGER, b CHAR(2), c TIMESTAMP(4), d FLOAT(24))" + "spark": "CREATE TEMPORARY TABLE mytemp (a INT, b CHAR(2), c TIMESTAMP, d FLOAT)", + "tsql": "CREATE TABLE #mytemp (a INTEGER, b CHAR(2), c TIME(4), d FLOAT(24))", + }, + ) + self.validate_all( + "CREATE TABLE #mytemptable (a INTEGER)", + read={ + "duckdb": "CREATE TEMPORARY TABLE mytemptable (a INT)", + }, + write={ + "tsql": "CREATE TABLE #mytemptable (a INTEGER)", + "snowflake": "CREATE TEMPORARY TABLE mytemptable (a INT)", + "duckdb": "CREATE TEMPORARY TABLE mytemptable (a INT)", + "oracle": "CREATE TEMPORARY TABLE mytemptable (a NUMBER)", + }, + ) + self.validate_all( + "CREATE TABLE #mytemptable AS SELECT a FROM Source_Table", + write={ + "duckdb": "CREATE TEMPORARY TABLE mytemptable AS SELECT a FROM Source_Table", + "oracle": "CREATE TEMPORARY TABLE mytemptable AS SELECT a FROM Source_Table", + "snowflake": "CREATE TEMPORARY TABLE mytemptable AS SELECT a FROM Source_Table", + "spark": "CREATE TEMPORARY VIEW mytemptable AS SELECT a FROM Source_Table", + "tsql": "CREATE TABLE #mytemptable AS SELECT a FROM Source_Table", }, ) @@ -535,6 +558,30 @@ WHERE for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls): self.assertEqual(expr.sql(dialect="tsql"), expected_sql) + sql = """ + CREATE PROC [dbo].[transform_proc] AS + + DECLARE @CurrentDate VARCHAR(20); + SET @CurrentDate = CONVERT(VARCHAR(20), GETDATE(), 120); + + CREATE TABLE [target_schema].[target_table] + WITH (DISTRIBUTION = REPLICATE, HEAP) + AS + + SELECT + @CurrentDate AS DWCreatedDate + FROM source_schema.sourcetable; + """ + + expected_sqls = [ + 'CREATE PROC "dbo"."transform_proc" AS DECLARE @CurrentDate VARCHAR(20)', + "SET @CurrentDate = CAST(FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS VARCHAR(20))", + 'CREATE TABLE "target_schema"."target_table" WITH (DISTRIBUTION=REPLICATE, HEAP) AS SELECT @CurrentDate AS DWCreatedDate FROM source_schema.sourcetable', + ] + + for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls): + self.assertEqual(expr.sql(dialect="tsql"), expected_sql) + def test_charindex(self): self.validate_all( "CHARINDEX(x, y, 9)", @@ -795,31 +842,50 @@ WHERE ) def test_date_diff(self): - self.validate_identity("SELECT DATEDIFF(year, '2020/01/01', '2021/01/01')") - + self.validate_identity("SELECT DATEDIFF(hour, 1.5, '2021-01-01')") + self.validate_identity( + "SELECT DATEDIFF(year, '2020-01-01', '2021-01-01')", + "SELECT DATEDIFF(year, CAST('2020-01-01' AS DATETIME2), CAST('2021-01-01' AS DATETIME2))", + ) + self.validate_all( + "SELECT DATEDIFF(quarter, 0, '2021-01-01')", + write={ + "tsql": "SELECT DATEDIFF(quarter, CAST('1900-01-01' AS DATETIME2), CAST('2021-01-01' AS DATETIME2))", + "spark": "SELECT DATEDIFF(quarter, CAST('1900-01-01' AS TIMESTAMP), CAST('2021-01-01' AS TIMESTAMP))", + "duckdb": "SELECT DATE_DIFF('quarter', CAST('1900-01-01' AS TIMESTAMP), CAST('2021-01-01' AS TIMESTAMP))", + }, + ) + self.validate_all( + "SELECT DATEDIFF(day, 1, '2021-01-01')", + write={ + "tsql": "SELECT DATEDIFF(day, CAST('1900-01-02' AS DATETIME2), CAST('2021-01-01' AS DATETIME2))", + "spark": "SELECT DATEDIFF(day, CAST('1900-01-02' AS TIMESTAMP), CAST('2021-01-01' AS TIMESTAMP))", + "duckdb": "SELECT DATE_DIFF('day', CAST('1900-01-02' AS TIMESTAMP), CAST('2021-01-01' AS TIMESTAMP))", + }, + ) self.validate_all( "SELECT DATEDIFF(year, '2020/01/01', '2021/01/01')", write={ - "tsql": "SELECT DATEDIFF(year, '2020/01/01', '2021/01/01')", - "spark": "SELECT DATEDIFF(year, '2020/01/01', '2021/01/01')", - "spark2": "SELECT MONTHS_BETWEEN('2021/01/01', '2020/01/01') / 12", + "tsql": "SELECT DATEDIFF(year, CAST('2020/01/01' AS DATETIME2), CAST('2021/01/01' AS DATETIME2))", + "spark": "SELECT DATEDIFF(year, CAST('2020/01/01' AS TIMESTAMP), CAST('2021/01/01' AS TIMESTAMP))", + "spark2": "SELECT MONTHS_BETWEEN(CAST('2021/01/01' AS TIMESTAMP), CAST('2020/01/01' AS TIMESTAMP)) / 12", }, ) self.validate_all( - "SELECT DATEDIFF(mm, 'start','end')", + "SELECT DATEDIFF(mm, 'start', 'end')", write={ - "databricks": "SELECT DATEDIFF(month, 'start', 'end')", - "spark2": "SELECT MONTHS_BETWEEN('end', 'start')", - "tsql": "SELECT DATEDIFF(month, 'start', 'end')", + "databricks": "SELECT DATEDIFF(month, CAST('start' AS TIMESTAMP), CAST('end' AS TIMESTAMP))", + "spark2": "SELECT MONTHS_BETWEEN(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))", + "tsql": "SELECT DATEDIFF(month, CAST('start' AS DATETIME2), CAST('end' AS DATETIME2))", }, ) self.validate_all( "SELECT DATEDIFF(quarter, 'start', 'end')", write={ - "databricks": "SELECT DATEDIFF(quarter, 'start', 'end')", - "spark": "SELECT DATEDIFF(quarter, 'start', 'end')", - "spark2": "SELECT MONTHS_BETWEEN('end', 'start') / 3", - "tsql": "SELECT DATEDIFF(quarter, 'start', 'end')", + "databricks": "SELECT DATEDIFF(quarter, CAST('start' AS TIMESTAMP), CAST('end' AS TIMESTAMP))", + "spark": "SELECT DATEDIFF(quarter, CAST('start' AS TIMESTAMP), CAST('end' AS TIMESTAMP))", + "spark2": "SELECT MONTHS_BETWEEN(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP)) / 3", + "tsql": "SELECT DATEDIFF(quarter, CAST('start' AS DATETIME2), CAST('end' AS DATETIME2))", }, ) @@ -943,8 +1009,15 @@ WHERE expr = parse_one("#x", read="tsql") self.assertIsInstance(expr, exp.Column) self.assertIsInstance(expr.this, exp.Identifier) + self.assertTrue(expr.this.args.get("temporary")) self.assertEqual(expr.sql("tsql"), "#x") + expr = parse_one("##x", read="tsql") + self.assertIsInstance(expr, exp.Column) + self.assertIsInstance(expr.this, exp.Identifier) + self.assertTrue(expr.this.args.get("global")) + self.assertEqual(expr.sql("tsql"), "##x") + expr = parse_one("@x", read="tsql") self.assertIsInstance(expr, exp.Parameter) self.assertIsInstance(expr.this, exp.Var) @@ -955,6 +1028,24 @@ WHERE self.assertIsInstance(table.this, exp.Parameter) self.assertIsInstance(table.this.this, exp.Var) + def test_temp_table(self): + self.validate_all( + "SELECT * FROM #mytemptable", + write={ + "duckdb": "SELECT * FROM mytemptable", + "spark": "SELECT * FROM mytemptable", + "tsql": "SELECT * FROM #mytemptable", + }, + ) + self.validate_all( + "SELECT * FROM ##mytemptable", + write={ + "duckdb": "SELECT * FROM mytemptable", + "spark": "SELECT * FROM mytemptable", + "tsql": "SELECT * FROM ##mytemptable", + }, + ) + def test_system_time(self): self.validate_all( "SELECT [x] FROM [a].[b] FOR SYSTEM_TIME AS OF 'foo'", |