Adding upstream version 17.7.0.upstream/17.7.0

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

1 files changed, 67 insertions, 8 deletions

diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 065cdd0..f0a590f 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -33,15 +33,15 @@ class TestTSQL(Validator):
         )
 
         self.validate_all(
-            "SELECT DATEPART(year, TRY_CAST('2017-01-01' AS DATE))",
+            "SELECT DATEPART(year, CAST('2017-01-01' AS DATE))",
             read={"postgres": "SELECT DATE_PART('year', '2017-01-01'::DATE)"},
         )
         self.validate_all(
-            "SELECT DATEPART(month, TRY_CAST('2017-03-01' AS DATE))",
+            "SELECT DATEPART(month, CAST('2017-03-01' AS DATE))",
             read={"postgres": "SELECT DATE_PART('month', '2017-03-01'::DATE)"},
         )
         self.validate_all(
-            "SELECT DATEPART(day, TRY_CAST('2017-01-02' AS DATE))",
+            "SELECT DATEPART(day, CAST('2017-01-02' AS DATE))",
             read={"postgres": "SELECT DATE_PART('day', '2017-01-02'::DATE)"},
         )
         self.validate_all(
@@ -54,7 +54,7 @@ class TestTSQL(Validator):
         self.validate_all(
             "CONVERT(INT, CONVERT(NUMERIC, '444.75'))",
             write={
-                "mysql": "CAST(CAST('444.75' AS DECIMAL) AS INT)",
+                "mysql": "CAST(CAST('444.75' AS DECIMAL) AS SIGNED)",
                 "tsql": "CAST(CAST('444.75' AS NUMERIC) AS INTEGER)",
             },
         )
@@ -389,8 +389,59 @@ class TestTSQL(Validator):
             },
         )
 
+    def test_ddl(self):
+        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))"
+            },
+        )
+
+    def test_transaction(self):
+        # BEGIN { TRAN | TRANSACTION }
+        #    [ { transaction_name | @tran_name_variable }
+        #    [ WITH MARK [ 'description' ] ]
+        #    ]
+        # [ ; ]
+        self.validate_identity("BEGIN TRANSACTION")
+        self.validate_all("BEGIN TRAN", write={"tsql": "BEGIN TRANSACTION"})
+        self.validate_identity("BEGIN TRANSACTION transaction_name")
+        self.validate_identity("BEGIN TRANSACTION @tran_name_variable")
+        self.validate_identity("BEGIN TRANSACTION transaction_name WITH MARK 'description'")
+
+    def test_commit(self):
+        # COMMIT [ { TRAN | TRANSACTION }  [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] [ ; ]
+
+        self.validate_all("COMMIT", write={"tsql": "COMMIT TRANSACTION"})
+        self.validate_all("COMMIT TRAN", write={"tsql": "COMMIT TRANSACTION"})
+        self.validate_identity("COMMIT TRANSACTION")
+        self.validate_identity("COMMIT TRANSACTION transaction_name")
+        self.validate_identity("COMMIT TRANSACTION @tran_name_variable")
+
+        self.validate_identity(
+            "COMMIT TRANSACTION @tran_name_variable WITH (DELAYED_DURABILITY = ON)"
+        )
+        self.validate_identity(
+            "COMMIT TRANSACTION transaction_name WITH (DELAYED_DURABILITY = OFF)"
+        )
+
+    def test_rollback(self):
+        # Applies to SQL Server and Azure SQL Database
+        # ROLLBACK { TRAN | TRANSACTION }
+        #     [ transaction_name | @tran_name_variable
+        #     | savepoint_name | @savepoint_variable ]
+        # [ ; ]
+        self.validate_all("ROLLBACK", write={"tsql": "ROLLBACK TRANSACTION"})
+        self.validate_all("ROLLBACK TRAN", write={"tsql": "ROLLBACK TRANSACTION"})
+        self.validate_identity("ROLLBACK TRANSACTION")
+        self.validate_identity("ROLLBACK TRANSACTION transaction_name")
+        self.validate_identity("ROLLBACK TRANSACTION @tran_name_variable")
+
     def test_udf(self):
         self.validate_identity(
+            "DECLARE @DWH_DateCreated DATETIME = CONVERT(DATETIME, getdate(), 104)"
+        )
+        self.validate_identity(
             "CREATE PROCEDURE foo @a INTEGER, @b INTEGER AS SELECT @a = SUM(bla) FROM baz AS bar"
         )
         self.validate_identity(
@@ -446,6 +497,12 @@ WHERE
             pretty=True,
         )
 
+    def test_procedure_keywords(self):
+        self.validate_identity("BEGIN")
+        self.validate_identity("END")
+        self.validate_identity("SET XACT_ABORT ON")
+
+    def test_fullproc(self):
         sql = """
             CREATE procedure [TRANSF].[SP_Merge_Sales_Real]
                 @Loadid INTEGER
@@ -696,14 +753,14 @@ WHERE
         self.validate_all(
             "SELECT CONVERT(VARCHAR(10), testdb.dbo.test.x, 120) y FROM testdb.dbo.test",
             write={
-                "mysql": "SELECT CAST(DATE_FORMAT(testdb.dbo.test.x, '%Y-%m-%d %T') AS VARCHAR(10)) AS y FROM testdb.dbo.test",
+                "mysql": "SELECT CAST(DATE_FORMAT(testdb.dbo.test.x, '%Y-%m-%d %T') AS CHAR(10)) AS y FROM testdb.dbo.test",
                 "spark": "SELECT CAST(DATE_FORMAT(testdb.dbo.test.x, 'yyyy-MM-dd HH:mm:ss') AS VARCHAR(10)) AS y FROM testdb.dbo.test",
             },
         )
         self.validate_all(
             "SELECT CONVERT(VARCHAR(10), y.x) z FROM testdb.dbo.test y",
             write={
-                "mysql": "SELECT CAST(y.x AS VARCHAR(10)) AS z FROM testdb.dbo.test AS y",
+                "mysql": "SELECT CAST(y.x AS CHAR(10)) AS z FROM testdb.dbo.test AS y",
                 "spark": "SELECT CAST(y.x AS VARCHAR(10)) AS z FROM testdb.dbo.test AS y",
             },
         )
@@ -838,7 +895,8 @@ WHERE
             write={"spark": "SELECT FORMAT_NUMBER(1000000.01, '###,###.###')"},
         )
         self.validate_all(
-            "SELECT FORMAT(1234567, 'f')", write={"spark": "SELECT FORMAT_NUMBER(1234567, 'f')"}
+            "SELECT FORMAT(1234567, 'f')",
+            write={"spark": "SELECT FORMAT_NUMBER(1234567, 'f')"},
         )
         self.validate_all(
             "SELECT FORMAT('01-01-1991', 'dd.mm.yyyy')",
@@ -853,7 +911,8 @@ WHERE
             write={"spark": "SELECT DATE_FORMAT(date_col, 'MMMM d')"},
         )
         self.validate_all(
-            "SELECT FORMAT(num_col, 'c')", write={"spark": "SELECT FORMAT_NUMBER(num_col, 'c')"}
+            "SELECT FORMAT(num_col, 'c')",
+            write={"spark": "SELECT FORMAT_NUMBER(num_col, 'c')"},
         )
 
     def test_string(self):