From c51a9844b869fd7cd69e5cc7658d34f61a865185 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Wed, 1 Nov 2023 06:12:42 +0100 Subject: Merging upstream version 19.0.1. Signed-off-by: Daniel Baumann --- tests/dialects/test_tsql.py | 44 ++++++++++++++++++++++++++++++++++++++++---- 1 file changed, 40 insertions(+), 4 deletions(-) (limited to 'tests/dialects/test_tsql.py') diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py index 4775020..0ac94f2 100644 --- a/tests/dialects/test_tsql.py +++ b/tests/dialects/test_tsql.py @@ -6,6 +6,36 @@ class TestTSQL(Validator): dialect = "tsql" def test_tsql(self): + self.validate_all( + "WITH t(c) AS (SELECT 1) SELECT * INTO foo FROM (SELECT c FROM t) AS temp", + read={ + "duckdb": "CREATE TABLE foo AS WITH t(c) AS (SELECT 1) SELECT c FROM t", + }, + ) + self.validate_all( + "WITH y AS (SELECT 2 AS c) INSERT INTO t SELECT * FROM y", + read={ + "duckdb": "WITH y AS (SELECT 2 AS c) INSERT INTO t SELECT * FROM y", + }, + ) + self.validate_all( + "WITH t(c) AS (SELECT 1) SELECT 1 AS c UNION (SELECT c FROM t)", + read={ + "duckdb": "SELECT 1 AS c UNION (WITH t(c) AS (SELECT 1) SELECT c FROM t)", + }, + ) + self.validate_all( + "WITH t(c) AS (SELECT 1) MERGE INTO x AS z USING (SELECT c FROM t) AS y ON a = b WHEN MATCHED THEN UPDATE SET a = y.b", + read={ + "postgres": "MERGE INTO x AS z USING (WITH t(c) AS (SELECT 1) SELECT c FROM t) AS y ON a = b WHEN MATCHED THEN UPDATE SET a = y.b", + }, + ) + self.validate_all( + "WITH t(n) AS (SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM t WHERE n < 4) SELECT * FROM (SELECT SUM(n) AS s4 FROM t) AS subq", + read={ + "duckdb": "SELECT * FROM (WITH RECURSIVE t(n) AS (SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM t WHERE n < 4) SELECT SUM(n) AS s4 FROM t) AS subq", + }, + ) self.validate_all( "CREATE TABLE #mytemptable (a INTEGER)", read={ @@ -825,12 +855,18 @@ WHERE def test_datename(self): self.validate_all( - "SELECT DATENAME(mm,'1970-01-01')", - write={"spark": "SELECT DATE_FORMAT(CAST('1970-01-01' AS TIMESTAMP), 'MMMM')"}, + "SELECT DATENAME(mm, '1970-01-01')", + write={ + "spark": "SELECT DATE_FORMAT(CAST('1970-01-01' AS TIMESTAMP), 'MMMM')", + "tsql": "SELECT FORMAT(CAST('1970-01-01' AS DATETIME2), 'MMMM')", + }, ) self.validate_all( - "SELECT DATENAME(dw,'1970-01-01')", - write={"spark": "SELECT DATE_FORMAT(CAST('1970-01-01' AS TIMESTAMP), 'EEEE')"}, + "SELECT DATENAME(dw, '1970-01-01')", + write={ + "spark": "SELECT DATE_FORMAT(CAST('1970-01-01' AS TIMESTAMP), 'EEEE')", + "tsql": "SELECT FORMAT(CAST('1970-01-01' AS DATETIME2), 'dddd')", + }, ) def test_datepart(self): -- cgit v1.2.3