summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_tsql.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-08-06 07:48:08 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-08-06 07:48:08 +0000
commitb737ee75da2515a4a53956e41ae85e29dd67f21d (patch)
tree3896f4cac8aebc31f5cdb32a111fa801aba7ca22 /tests/dialects/test_tsql.py
parentAdding upstream version 17.7.0. (diff)
downloadsqlglot-b737ee75da2515a4a53956e41ae85e29dd67f21d.tar.xz
sqlglot-b737ee75da2515a4a53956e41ae85e29dd67f21d.zip
Adding upstream version 17.9.1.upstream/17.9.1
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.py123
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'",