diff options
Diffstat (limited to 'tests/dialects')
| -rw-r--r-- | tests/dialects/test_bigquery.py | 21 | ||||
| -rw-r--r-- | tests/dialects/test_duckdb.py | 32 | ||||
| -rw-r--r-- | tests/dialects/test_hive.py | 1 | ||||
| -rw-r--r-- | tests/dialects/test_presto.py | 31 | ||||
| -rw-r--r-- | tests/dialects/test_redshift.py | 43 | ||||
| -rw-r--r-- | tests/dialects/test_snowflake.py | 17 | ||||
| -rw-r--r-- | tests/dialects/test_starrocks.py | 16 | ||||
| -rw-r--r-- | tests/dialects/test_tsql.py | 8 |
8 files changed, 126 insertions, 43 deletions
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py index 8d01ebe..b5f91cf 100644 --- a/tests/dialects/test_bigquery.py +++ b/tests/dialects/test_bigquery.py @@ -200,8 +200,15 @@ class TestBigQuery(Validator): ) self.validate_all( "REGEXP_CONTAINS('foo', '.*')", - read={"bigquery": "REGEXP_CONTAINS('foo', '.*')"}, - write={"mysql": "REGEXP_LIKE('foo', '.*')"}, + read={ + "bigquery": "REGEXP_CONTAINS('foo', '.*')", + "mysql": "REGEXP_LIKE('foo', '.*')", + "starrocks": "REGEXP('foo', '.*')", + }, + write={ + "mysql": "REGEXP_LIKE('foo', '.*')", + "starrocks": "REGEXP('foo', '.*')", + }, ), self.validate_all( '"""x"""', @@ -481,13 +488,21 @@ class TestBigQuery(Validator): write={ "bigquery": "DATE_DIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE), DAY)", "mysql": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))", + "starrocks": "DATE_DIFF('DAY', CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))", + }, + ) + self.validate_all( + "DATE_DIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE), DAY)", + read={ + "mysql": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))", + "starrocks": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))", }, ) self.validate_all( "DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', MINUTE)", write={ "bigquery": "DATE_DIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE), MINUTE)", - "mysql": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))", + "starrocks": "DATE_DIFF('MINUTE', CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))", }, ) self.validate_all( diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py index 5284700..5c35d8f 100644 --- a/tests/dialects/test_duckdb.py +++ b/tests/dialects/test_duckdb.py @@ -583,6 +583,38 @@ class TestDuckDB(Validator): write={"duckdb": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"}, ) + def test_encode_decode(self): + self.validate_all( + "ENCODE(x)", + read={ + "spark": "ENCODE(x, 'utf-8')", + "presto": "TO_UTF8(x)", + }, + write={ + "duckdb": "ENCODE(x)", + "spark": "ENCODE(x, 'utf-8')", + "presto": "TO_UTF8(x)", + }, + ) + self.validate_all( + "DECODE(x)", + read={ + "spark": "DECODE(x, 'utf-8')", + "presto": "FROM_UTF8(x)", + }, + write={ + "duckdb": "DECODE(x)", + "spark": "DECODE(x, 'utf-8')", + "presto": "FROM_UTF8(x)", + }, + ) + self.validate_all( + "DECODE(x)", + read={ + "presto": "FROM_UTF8(x, y)", + }, + ) + def test_rename_table(self): self.validate_all( "ALTER TABLE db.t1 RENAME TO db.t2", diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py index 6dd484f..4c463f7 100644 --- a/tests/dialects/test_hive.py +++ b/tests/dialects/test_hive.py @@ -390,6 +390,7 @@ class TestHive(Validator): ) def test_hive(self): + self.validate_identity("SELECT transform") self.validate_identity("SELECT * FROM test DISTRIBUTE BY y SORT BY x DESC ORDER BY l") self.validate_identity( "SELECT * FROM test WHERE RAND() <= 0.1 DISTRIBUTE BY RAND() SORT BY RAND()" diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py index a2800bd..ec1ad30 100644 --- a/tests/dialects/test_presto.py +++ b/tests/dialects/test_presto.py @@ -466,6 +466,7 @@ class TestPresto(Validator): read={"spark": "STARTSWITH('abc', 'a')"}, write={ "presto": "STARTS_WITH('abc', 'a')", + "snowflake": "STARTSWITH('abc', 'a')", "spark": "STARTSWITH('abc', 'a')", }, ) @@ -740,46 +741,44 @@ class TestPresto(Validator): ) def test_encode_decode(self): + self.validate_identity("FROM_UTF8(x, y)") + self.validate_all( "TO_UTF8(x)", + read={ + "duckdb": "ENCODE(x)", + "spark": "ENCODE(x, 'utf-8')", + }, write={ + "duckdb": "ENCODE(x)", + "presto": "TO_UTF8(x)", "spark": "ENCODE(x, 'utf-8')", }, ) self.validate_all( "FROM_UTF8(x)", - write={ + read={ + "duckdb": "DECODE(x)", "spark": "DECODE(x, 'utf-8')", }, - ) - self.validate_all( - "FROM_UTF8(x, y)", - write={ - "presto": "FROM_UTF8(x, y)", - }, - ) - self.validate_all( - "ENCODE(x, 'utf-8')", - write={ - "presto": "TO_UTF8(x)", - }, - ) - self.validate_all( - "DECODE(x, 'utf-8')", write={ + "duckdb": "DECODE(x)", "presto": "FROM_UTF8(x)", + "spark": "DECODE(x, 'utf-8')", }, ) self.validate_all( "ENCODE(x, 'invalid')", write={ "presto": UnsupportedError, + "duckdb": UnsupportedError, }, ) self.validate_all( "DECODE(x, 'invalid')", write={ "presto": UnsupportedError, + "duckdb": UnsupportedError, }, ) diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py index 90c953f..96e9e20 100644 --- a/tests/dialects/test_redshift.py +++ b/tests/dialects/test_redshift.py @@ -5,15 +5,16 @@ class TestRedshift(Validator): dialect = "redshift" def test_redshift(self): - self.validate_identity("SELECT * FROM #x") - self.validate_identity("SELECT INTERVAL '5 day'") - self.validate_identity("foo$") - self.validate_identity("$foo") - + self.validate_all( + "SELECT INTERVAL '5 days'", + read={ + "": "SELECT INTERVAL '5' days", + }, + ) self.validate_all( "SELECT ADD_MONTHS('2008-03-31', 1)", write={ - "redshift": "SELECT DATEADD(month, 1, '2008-03-31')", + "redshift": "SELECT DATEADD(month, 1, CAST('2008-03-31' AS DATE))", "trino": "SELECT DATE_ADD('month', 1, CAST(CAST('2008-03-31' AS TIMESTAMP) AS DATE))", }, ) @@ -88,11 +89,6 @@ class TestRedshift(Validator): "snowflake": "SELECT DATE_PART(month, CAST('20220502' AS DATE))", }, ) - self.validate_all("SELECT INTERVAL '5 days'", read={"": "SELECT INTERVAL '5' days"}) - self.validate_all("CONVERT(INT, x)", write={"redshift": "CAST(x AS INTEGER)"}) - self.validate_all( - "DATEADD('day', ndays, caldate)", write={"redshift": "DATEADD(day, ndays, caldate)"} - ) self.validate_all( 'create table "group" ("col" char(10))', write={ @@ -186,7 +182,7 @@ class TestRedshift(Validator): self.validate_all( "DATEDIFF('day', a, b)", write={ - "redshift": "DATEDIFF(day, a, b)", + "redshift": "DATEDIFF(day, CAST(a AS DATE), CAST(b AS DATE))", "presto": "DATE_DIFF('day', CAST(CAST(a AS TIMESTAMP) AS DATE), CAST(CAST(b AS TIMESTAMP) AS DATE))", }, ) @@ -198,15 +194,18 @@ class TestRedshift(Validator): ) def test_identity(self): + self.validate_identity("SELECT * FROM #x") + self.validate_identity("SELECT INTERVAL '5 day'") + self.validate_identity("foo$") + self.validate_identity("$foo") self.validate_identity("CAST('bla' AS SUPER)") self.validate_identity("CREATE TABLE real1 (realcol REAL)") self.validate_identity("CAST('foo' AS HLLSKETCH)") - self.validate_identity("SELECT DATEADD(day, 1, 'today')") self.validate_identity("'abc' SIMILAR TO '(b|c)%'") + self.validate_identity("CREATE TABLE datetable (start_date DATE, end_date DATE)") self.validate_identity( "SELECT caldate + INTERVAL '1 second' AS dateplus FROM date WHERE caldate = '12-31-2008'" ) - self.validate_identity("CREATE TABLE datetable (start_date DATE, end_date DATE)") self.validate_identity( "SELECT COUNT(*) FROM event WHERE eventname LIKE '%Ring%' OR eventname LIKE '%Die%'" ) @@ -225,6 +224,22 @@ class TestRedshift(Validator): self.validate_identity( "CREATE TABLE SOUP (SOUP1 VARCHAR(50) NOT NULL ENCODE ZSTD, SOUP2 VARCHAR(70) NULL ENCODE DELTA)" ) + self.validate_identity( + "SELECT DATEADD(day, 1, 'today')", + "SELECT DATEADD(day, 1, CAST('today' AS DATE))", + ) + self.validate_identity( + "SELECT DATEADD('day', ndays, caldate)", + "SELECT DATEADD(day, ndays, CAST(caldate AS DATE))", + ) + self.validate_identity( + "CONVERT(INT, x)", + "CAST(x AS INTEGER)", + ) + self.validate_identity( + "SELECT DATE_ADD('day', 1, DATE('2023-01-01'))", + "SELECT DATEADD(day, 1, CAST(DATE('2023-01-01') AS DATE))", + ) def test_values(self): self.validate_all( diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index a889e1d..3053d47 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -8,6 +8,7 @@ class TestSnowflake(Validator): dialect = "snowflake" def test_snowflake(self): + self.validate_identity("WEEKOFYEAR(tstamp)") self.validate_identity("SELECT SUM(amount) FROM mytable GROUP BY ALL") self.validate_identity("WITH x AS (SELECT 1 AS foo) SELECT foo FROM IDENTIFIER('x')") self.validate_identity("WITH x AS (SELECT 1 AS foo) SELECT IDENTIFIER('foo') FROM x") @@ -33,12 +34,21 @@ class TestSnowflake(Validator): self.validate_identity("ALTER TABLE foo UNSET DATA_RETENTION_TIME_IN_DAYS, CHANGE_TRACKING") self.validate_identity("COMMENT IF EXISTS ON TABLE foo IS 'bar'") self.validate_identity("SELECT CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', col)") + self.validate_identity("REGEXP_REPLACE('target', 'pattern', '\n')") self.validate_identity( 'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)' ) self.validate_identity( "SELECT state, city, SUM(retail_price * quantity) AS gross_revenue FROM sales GROUP BY ALL" ) + self.validate_identity( + r"SELECT RLIKE(a, $$regular expression with \ characters: \d{2}-\d{3}-\d{4}$$, 'i') FROM log_source", + r"SELECT REGEXP_LIKE(a, 'regular expression with \\ characters: \\d{2}-\\d{3}-\\d{4}', 'i') FROM log_source", + ) + self.validate_identity( + r"SELECT $$a ' \ \t \x21 z $ $$", + r"SELECT 'a \' \\ \\t \\x21 z $ '", + ) self.validate_all("CAST(x AS BYTEINT)", write={"snowflake": "CAST(x AS INT)"}) self.validate_all("CAST(x AS CHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"}) @@ -385,13 +395,6 @@ class TestSnowflake(Validator): }, ) self.validate_all( - r"SELECT $$a ' \ \t \x21 z $ $$", - write={ - "snowflake": r"SELECT 'a \' \ \t \x21 z $ '", - }, - ) - self.validate_identity("REGEXP_REPLACE('target', 'pattern', '\n')") - self.validate_all( "SELECT RLIKE(a, b)", write={ "hive": "SELECT a RLIKE b", diff --git a/tests/dialects/test_starrocks.py b/tests/dialects/test_starrocks.py index 96e20da..21a89d7 100644 --- a/tests/dialects/test_starrocks.py +++ b/tests/dialects/test_starrocks.py @@ -1,7 +1,7 @@ from tests.dialects.test_dialect import Validator -class TestMySQL(Validator): +class TestStarrocks(Validator): dialect = "starrocks" def test_identity(self): @@ -10,11 +10,21 @@ class TestMySQL(Validator): def test_time(self): self.validate_identity("TIMESTAMP('2022-01-01')") + self.validate_identity( + "SELECT DATE_DIFF('second', '2010-11-30 23:59:59', '2010-11-30 20:58:59')" + ) + self.validate_identity( + "SELECT DATE_DIFF('minute', '2010-11-30 23:59:59', '2010-11-30 20:58:59')" + ) def test_regex(self): self.validate_all( - "SELECT REGEXP_LIKE(abc, '%foo%')", - write={ + "SELECT REGEXP(abc, '%foo%')", + read={ + "mysql": "SELECT REGEXP_LIKE(abc, '%foo%')", "starrocks": "SELECT REGEXP(abc, '%foo%')", }, + write={ + "mysql": "SELECT REGEXP_LIKE(abc, '%foo%')", + }, ) diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py index 5266bd4..f43b41b 100644 --- a/tests/dialects/test_tsql.py +++ b/tests/dialects/test_tsql.py @@ -6,6 +6,10 @@ class TestTSQL(Validator): dialect = "tsql" def test_tsql(self): + projection = parse_one("SELECT a = 1", read="tsql").selects[0] + projection.assert_is(exp.Alias) + projection.args["alias"].assert_is(exp.Identifier) + 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") @@ -25,6 +29,10 @@ class TestTSQL(Validator): self.validate_identity('SELECT "x"."y" FROM foo') self.validate_identity("SELECT * FROM #foo") self.validate_identity("SELECT * FROM ##foo") + self.validate_identity("SELECT a = 1", "SELECT 1 AS a") + self.validate_identity( + "SELECT a = 1 UNION ALL SELECT a = b", "SELECT 1 AS a UNION ALL SELECT b AS a" + ) self.validate_identity( "SELECT x FROM @MyTableVar AS m JOIN Employee ON m.EmployeeID = Employee.EmployeeID" ) |