summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_tsql.py
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects/test_tsql.py')
-rw-r--r--tests/dialects/test_tsql.py112
1 files changed, 77 insertions, 35 deletions
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 1538d47..45a4657 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -1,5 +1,4 @@
from sqlglot import exp, parse, parse_one
-from sqlglot.parser import logger as parser_logger
from tests.dialects.test_dialect import Validator
from sqlglot.errors import ParseError
@@ -8,6 +7,8 @@ class TestTSQL(Validator):
dialect = "tsql"
def test_tsql(self):
+ self.validate_identity("CREATE view a.b.c", "CREATE VIEW b.c")
+ self.validate_identity("DROP view a.b.c", "DROP VIEW b.c")
self.validate_identity("ROUND(x, 1, 0)")
self.validate_identity("EXEC MyProc @id=7, @name='Lochristi'", check_command_warning=True)
# https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms187879(v=sql.105)?redirectedfrom=MSDN
@@ -191,16 +192,9 @@ class TestTSQL(Validator):
)
self.validate_all(
- """
- CREATE TABLE x(
- [zip_cd] [varchar](5) NULL NOT FOR REPLICATION,
- [zip_cd_mkey] [varchar](5) NOT NULL,
- CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC)
- WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF) ON [INDEX]
- ) ON [SECONDARY]
- """,
+ """CREATE TABLE x ([zip_cd] VARCHAR(5) NULL NOT FOR REPLICATION, [zip_cd_mkey] VARCHAR(5) NOT NULL, CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC) WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF) ON [INDEX]) ON [SECONDARY]""",
write={
- "tsql": "CREATE TABLE x ([zip_cd] VARCHAR(5) NULL NOT FOR REPLICATION, [zip_cd_mkey] VARCHAR(5) NOT NULL, CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC) WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF) ON [INDEX]) ON [SECONDARY]",
+ "tsql": "CREATE TABLE x ([zip_cd] VARCHAR(5) NULL NOT FOR REPLICATION, [zip_cd_mkey] VARCHAR(5) NOT NULL, CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC) WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF) ON [INDEX]) ON [SECONDARY]",
"spark2": "CREATE TABLE x (`zip_cd` VARCHAR(5), `zip_cd_mkey` VARCHAR(5) NOT NULL, CONSTRAINT `pk_mytable` PRIMARY KEY (`zip_cd_mkey`))",
},
)
@@ -259,7 +253,7 @@ class TestTSQL(Validator):
self.validate_identity("SELECT * FROM ##foo")
self.validate_identity("SELECT a = 1", "SELECT 1 AS a")
self.validate_identity(
- "DECLARE @TestVariable AS VARCHAR(100)='Save Our Planet'", check_command_warning=True
+ "DECLARE @TestVariable AS VARCHAR(100) = 'Save Our Planet'",
)
self.validate_identity(
"SELECT a = 1 UNION ALL SELECT a = b", "SELECT 1 AS a UNION ALL SELECT b AS a"
@@ -461,6 +455,7 @@ class TestTSQL(Validator):
self.validate_identity("CAST(x AS IMAGE)")
self.validate_identity("CAST(x AS SQL_VARIANT)")
self.validate_identity("CAST(x AS BIT)")
+
self.validate_all(
"CAST(x AS DATETIME2)",
read={
@@ -488,7 +483,7 @@ class TestTSQL(Validator):
},
)
- def test__types_ints(self):
+ def test_types_ints(self):
self.validate_all(
"CAST(X AS INT)",
write={
@@ -521,10 +516,14 @@ class TestTSQL(Validator):
self.validate_all(
"CAST(X AS TINYINT)",
+ read={
+ "duckdb": "CAST(X AS UTINYINT)",
+ },
write={
- "hive": "CAST(X AS TINYINT)",
- "spark2": "CAST(X AS TINYINT)",
- "spark": "CAST(X AS TINYINT)",
+ "duckdb": "CAST(X AS UTINYINT)",
+ "hive": "CAST(X AS SMALLINT)",
+ "spark2": "CAST(X AS SMALLINT)",
+ "spark": "CAST(X AS SMALLINT)",
"tsql": "CAST(X AS TINYINT)",
},
)
@@ -764,19 +763,33 @@ class TestTSQL(Validator):
expression.sql(dialect="tsql"), "ALTER TABLE dbo.DocExe DROP CONSTRAINT FK_Column_B"
)
- for clusterd_keyword in ("CLUSTERED", "NONCLUSTERED"):
+ for clustered_keyword in ("CLUSTERED", "NONCLUSTERED"):
self.validate_identity(
'CREATE TABLE "dbo"."benchmark" ('
'"name" CHAR(7) NOT NULL, '
'"internal_id" VARCHAR(10) NOT NULL, '
- f'UNIQUE {clusterd_keyword} ("internal_id" ASC))',
+ f'UNIQUE {clustered_keyword} ("internal_id" ASC))',
"CREATE TABLE [dbo].[benchmark] ("
"[name] CHAR(7) NOT NULL, "
"[internal_id] VARCHAR(10) NOT NULL, "
- f"UNIQUE {clusterd_keyword} ([internal_id] ASC))",
+ f"UNIQUE {clustered_keyword} ([internal_id] ASC))",
)
self.validate_identity(
+ "ALTER TABLE tbl SET SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, DATA_CONSISTENCY_CHECK=OFF, HISTORY_RETENTION_PERIOD=5 DAYS)"
+ )
+ self.validate_identity(
+ "ALTER TABLE tbl SET SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, HISTORY_RETENTION_PERIOD=INFINITE)"
+ )
+ self.validate_identity("ALTER TABLE tbl SET SYSTEM_VERSIONING=OFF")
+ self.validate_identity("ALTER TABLE tbl SET FILESTREAM_ON = 'test'")
+ self.validate_identity(
+ "ALTER TABLE tbl SET DATA_DELETION=ON(FILTER_COLUMN=col, RETENTION_PERIOD=5 MONTHS)"
+ )
+ self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=ON")
+ self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=OFF")
+
+ self.validate_identity(
"CREATE PROCEDURE foo AS BEGIN DELETE FROM bla WHERE foo < CURRENT_TIMESTAMP - 7 END",
"CREATE PROCEDURE foo AS BEGIN DELETE FROM bla WHERE foo < GETDATE() - 7 END",
)
@@ -900,8 +913,7 @@ class TestTSQL(Validator):
def test_udf(self):
self.validate_identity(
- "DECLARE @DWH_DateCreated DATETIME = CONVERT(DATETIME, getdate(), 104)",
- check_command_warning=True,
+ "DECLARE @DWH_DateCreated AS DATETIME2 = CONVERT(DATETIME2, GETDATE(), 104)",
)
self.validate_identity(
"CREATE PROCEDURE foo @a INTEGER, @b INTEGER AS SELECT @a = SUM(bla) FROM baz AS bar"
@@ -973,9 +985,9 @@ WHERE
BEGIN
SET XACT_ABORT ON;
- DECLARE @DWH_DateCreated DATETIME = CONVERT(DATETIME, getdate(), 104);
- DECLARE @DWH_DateModified DATETIME = CONVERT(DATETIME, getdate(), 104);
- DECLARE @DWH_IdUserCreated INTEGER = SUSER_ID (SYSTEM_USER);
+ DECLARE @DWH_DateCreated AS DATETIME = CONVERT(DATETIME, getdate(), 104);
+ DECLARE @DWH_DateModified DATETIME2 = CONVERT(DATETIME2, GETDATE(), 104);
+ DECLARE @DWH_IdUserCreated INTEGER = SUSER_ID (CURRENT_USER());
DECLARE @DWH_IdUserModified INTEGER = SUSER_ID (SYSTEM_USER);
DECLARE @SalesAmountBefore float;
@@ -985,18 +997,17 @@ WHERE
expected_sqls = [
"CREATE PROCEDURE [TRANSF].[SP_Merge_Sales_Real] @Loadid INTEGER, @NumberOfRows INTEGER AS BEGIN SET XACT_ABORT ON",
- "DECLARE @DWH_DateCreated DATETIME = CONVERT(DATETIME, getdate(), 104)",
- "DECLARE @DWH_DateModified DATETIME = CONVERT(DATETIME, getdate(), 104)",
- "DECLARE @DWH_IdUserCreated INTEGER = SUSER_ID (SYSTEM_USER)",
- "DECLARE @DWH_IdUserModified INTEGER = SUSER_ID (SYSTEM_USER)",
- "DECLARE @SalesAmountBefore float",
+ "DECLARE @DWH_DateCreated AS DATETIME2 = CONVERT(DATETIME2, GETDATE(), 104)",
+ "DECLARE @DWH_DateModified AS DATETIME2 = CONVERT(DATETIME2, GETDATE(), 104)",
+ "DECLARE @DWH_IdUserCreated AS INTEGER = SUSER_ID(CURRENT_USER())",
+ "DECLARE @DWH_IdUserModified AS INTEGER = SUSER_ID(CURRENT_USER())",
+ "DECLARE @SalesAmountBefore AS FLOAT",
"SELECT @SalesAmountBefore = SUM(SalesAmount) FROM TRANSF.[Pre_Merge_Sales_Real] AS S",
"END",
]
- with self.assertLogs(parser_logger):
- for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls):
- self.assertEqual(expr.sql(dialect="tsql"), expected_sql)
+ 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
@@ -1010,14 +1021,13 @@ WHERE
"""
expected_sqls = [
- "CREATE PROC [dbo].[transform_proc] AS DECLARE @CurrentDate VARCHAR(20)",
+ "CREATE PROC [dbo].[transform_proc] AS DECLARE @CurrentDate AS VARCHAR(20)",
"SET @CurrentDate = CONVERT(VARCHAR(20), GETDATE(), 120)",
"CREATE TABLE [target_schema].[target_table] (a INTEGER) WITH (DISTRIBUTION=REPLICATE, HEAP)",
]
- with self.assertLogs(parser_logger):
- for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls):
- self.assertEqual(expr.sql(dialect="tsql"), expected_sql)
+ 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_identity(
@@ -1823,3 +1833,35 @@ FROM OPENJSON(@json) WITH (
"duckdb": "WITH t1(c) AS (SELECT 1), t2 AS (SELECT CAST(c AS INTEGER) FROM t1) SELECT * FROM t2",
},
)
+
+ def test_declare(self):
+ # supported cases
+ self.validate_identity("DECLARE @X INT", "DECLARE @X AS INTEGER")
+ self.validate_identity("DECLARE @X INT = 1", "DECLARE @X AS INTEGER = 1")
+ self.validate_identity(
+ "DECLARE @X INT, @Y VARCHAR(10)", "DECLARE @X AS INTEGER, @Y AS VARCHAR(10)"
+ )
+ self.validate_identity(
+ "declare @X int = (select col from table where id = 1)",
+ "DECLARE @X AS INTEGER = (SELECT col FROM table WHERE id = 1)",
+ )
+ self.validate_identity(
+ "declare @X TABLE (Id INT NOT NULL, Name VARCHAR(100) NOT NULL)",
+ "DECLARE @X AS TABLE (Id INTEGER NOT NULL, Name VARCHAR(100) NOT NULL)",
+ )
+ self.validate_identity(
+ "declare @X TABLE (Id INT NOT NULL, constraint PK_Id primary key (Id))",
+ "DECLARE @X AS TABLE (Id INTEGER NOT NULL, CONSTRAINT PK_Id PRIMARY KEY (Id))",
+ )
+ self.validate_identity(
+ "declare @X UserDefinedTableType",
+ "DECLARE @X AS UserDefinedTableType",
+ )
+ self.validate_identity(
+ "DECLARE @MyTableVar TABLE (EmpID INT NOT NULL, PRIMARY KEY CLUSTERED (EmpID), UNIQUE NONCLUSTERED (EmpID), INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID))",
+ check_command_warning=True,
+ )
+ self.validate_identity(
+ "DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID",
+ check_command_warning=True,
+ )