summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_tsql.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-09-07 11:39:43 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-09-07 11:39:43 +0000
commit341eb1a6bdf0dd5b015e5140d3b068c6fd3f4d87 (patch)
tree61fb7eca2238fb5d41d3906f4af41de03abd25ea /tests/dialects/test_tsql.py
parentAdding upstream version 17.12.0. (diff)
downloadsqlglot-341eb1a6bdf0dd5b015e5140d3b068c6fd3f4d87.tar.xz
sqlglot-341eb1a6bdf0dd5b015e5140d3b068c6fd3f4d87.zip
Adding upstream version 18.2.0.upstream/18.2.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.py111
1 files changed, 111 insertions, 0 deletions
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index f43b41b..c27b7fa 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -6,10 +6,55 @@ class TestTSQL(Validator):
dialect = "tsql"
def test_tsql(self):
+ self.validate_all(
+ "CREATE TABLE x ( A INTEGER NOT NULL, B INTEGER NULL )",
+ write={
+ "tsql": "CREATE TABLE x (A INTEGER NOT NULL, B INTEGER NULL)",
+ "hive": "CREATE TABLE x (A INT NOT NULL, B INT)",
+ },
+ )
+
+ self.validate_identity(
+ 'CREATE TABLE x (CONSTRAINT "pk_mytable" UNIQUE NONCLUSTERED (a DESC)) ON b (c)'
+ )
+
+ self.validate_identity(
+ """
+ CREATE TABLE x(
+ [zip_cd] [varchar](5) NULL NOT FOR REPLICATION
+ CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED
+ ([zip_cd_mkey] ASC)
+ WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY]
+ ) ON [PRIMARY]
+ """,
+ 'CREATE TABLE x ("zip_cd" VARCHAR(5) NULL NOT FOR REPLICATION CONSTRAINT "pk_mytable" PRIMARY KEY CLUSTERED ("zip_cd_mkey") WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF) ON "PRIMARY") ON "PRIMARY"',
+ )
+
+ self.validate_identity(
+ "CREATE TABLE tbl (a AS (x + 1) PERSISTED, b AS (y + 2), c AS (y / 3) PERSISTED NOT NULL)"
+ )
+
+ self.validate_identity(
+ "CREATE TABLE [db].[tbl]([a] [int])", 'CREATE TABLE "db"."tbl" ("a" INTEGER)'
+ )
+
projection = parse_one("SELECT a = 1", read="tsql").selects[0]
projection.assert_is(exp.Alias)
projection.args["alias"].assert_is(exp.Identifier)
+ self.validate_all(
+ "IF OBJECT_ID('tempdb.dbo.#TempTableName', 'U') IS NOT NULL DROP TABLE #TempTableName",
+ write={
+ "tsql": "DROP TABLE IF EXISTS #TempTableName",
+ "spark": "DROP TABLE IF EXISTS TempTableName",
+ },
+ )
+
+ self.validate_identity(
+ "MERGE INTO mytable WITH (HOLDLOCK) AS T USING mytable_merge AS S "
+ "ON (T.user_id = S.user_id) WHEN NOT MATCHED THEN INSERT (c1, c2) VALUES (S.c1, S.c2)"
+ )
+ self.validate_identity("UPDATE STATISTICS x")
self.validate_identity("UPDATE x SET y = 1 OUTPUT x.a, x.b INTO @y FROM y")
self.validate_identity("UPDATE x SET y = 1 OUTPUT x.a, x.b FROM y")
self.validate_identity("INSERT INTO x (y) OUTPUT x.a, x.b INTO l SELECT * FROM z")
@@ -397,8 +442,68 @@ class TestTSQL(Validator):
},
)
+ self.validate_all(
+ "CAST(x AS BOOLEAN)",
+ write={"tsql": "CAST(x AS BIT)"},
+ )
+
def test_ddl(self):
self.validate_all(
+ "CREATE TABLE tbl (id INTEGER IDENTITY PRIMARY KEY)",
+ read={
+ "mysql": "CREATE TABLE tbl (id INT AUTO_INCREMENT PRIMARY KEY)",
+ "tsql": "CREATE TABLE tbl (id INTEGER IDENTITY PRIMARY KEY)",
+ },
+ )
+ self.validate_all(
+ "CREATE TABLE tbl (id INTEGER NOT NULL IDENTITY(10, 1) PRIMARY KEY)",
+ read={
+ "postgres": "CREATE TABLE tbl (id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10) PRIMARY KEY)",
+ "tsql": "CREATE TABLE tbl (id INTEGER NOT NULL IDENTITY(10, 1) PRIMARY KEY)",
+ },
+ )
+ self.validate_all(
+ "IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('db.tbl') AND name = 'idx') EXEC('CREATE INDEX idx ON db.tbl')",
+ read={
+ "": "CREATE INDEX IF NOT EXISTS idx ON db.tbl",
+ },
+ )
+
+ self.validate_all(
+ "IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'foo') EXEC('CREATE SCHEMA foo')",
+ read={
+ "": "CREATE SCHEMA IF NOT EXISTS foo",
+ },
+ )
+ self.validate_all(
+ "IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'foo') EXEC('CREATE TABLE foo (a INTEGER)')",
+ read={
+ "": "CREATE TABLE IF NOT EXISTS foo (a INTEGER)",
+ },
+ )
+
+ self.validate_all(
+ "CREATE OR ALTER VIEW a.b AS SELECT 1",
+ read={
+ "": "CREATE OR REPLACE VIEW a.b AS SELECT 1",
+ },
+ write={
+ "tsql": "CREATE OR ALTER VIEW a.b AS SELECT 1",
+ },
+ )
+
+ self.validate_all(
+ "ALTER TABLE a ADD b INTEGER, c INTEGER",
+ read={
+ "": "ALTER TABLE a ADD COLUMN b INT, ADD COLUMN c INT",
+ },
+ write={
+ "": "ALTER TABLE a ADD COLUMN b INT, ADD COLUMN c INT",
+ "tsql": "ALTER TABLE a ADD b INTEGER, c INTEGER",
+ },
+ )
+
+ self.validate_all(
"CREATE TABLE #mytemp (a INTEGER, b CHAR(2), c TIME(4), d FLOAT(24))",
write={
"spark": "CREATE TEMPORARY TABLE mytemp (a INT, b CHAR(2), c TIMESTAMP, d FLOAT)",
@@ -898,6 +1003,9 @@ WHERE
)
def test_iif(self):
+ self.validate_identity(
+ "SELECT IF(cond, 'True', 'False')", "SELECT IIF(cond, 'True', 'False')"
+ )
self.validate_identity("SELECT IIF(cond, 'True', 'False')")
self.validate_all(
"SELECT IIF(cond, 'True', 'False');",
@@ -961,9 +1069,12 @@ WHERE
)
def test_format(self):
+ self.validate_identity("SELECT FORMAT(foo, 'dddd', 'de-CH')")
+ self.validate_identity("SELECT FORMAT(EndOfDayRate, 'N', 'en-us')")
self.validate_identity("SELECT FORMAT('01-01-1991', 'd.mm.yyyy')")
self.validate_identity("SELECT FORMAT(12345, '###.###.###')")
self.validate_identity("SELECT FORMAT(1234567, 'f')")
+
self.validate_all(
"SELECT FORMAT(1000000.01,'###,###.###')",
write={"spark": "SELECT FORMAT_NUMBER(1000000.01, '###,###.###')"},