summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_tsql.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-07-24 08:03:45 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-07-24 08:03:45 +0000
commitca57dc468e5d8d0920e964d45ad25271ae6e633d (patch)
tree319d8bffcb5c3e9afe9e62beca9ef401480578d2 /tests/dialects/test_tsql.py
parentAdding upstream version 17.4.1. (diff)
downloadsqlglot-ca57dc468e5d8d0920e964d45ad25271ae6e633d.tar.xz
sqlglot-ca57dc468e5d8d0920e964d45ad25271ae6e633d.zip
Adding upstream version 17.7.0.upstream/17.7.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.py75
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):