Adding upstream version 17.9.1.upstream/17.9.1

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

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'",