diff options
Diffstat (limited to 'tests/dialects/test_dialect.py')
| -rw-r--r-- | tests/dialects/test_dialect.py | 108 |
1 files changed, 105 insertions, 3 deletions
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index 85402e2..170b64b 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -1444,6 +1444,56 @@ class TestDialect(Validator): }, ) + # UNNEST without column alias + self.validate_all( + "SELECT * FROM x CROSS JOIN UNNEST(y) AS t", + write={ + "presto": "SELECT * FROM x CROSS JOIN UNNEST(y) AS t", + "spark": UnsupportedError, + "databricks": UnsupportedError, + }, + ) + + # UNNEST MAP Object into multiple columns, using single alias + self.validate_all( + "SELECT a, b FROM x CROSS JOIN UNNEST(y) AS t (a, b)", + write={ + "presto": "SELECT a, b FROM x CROSS JOIN UNNEST(y) AS t(a, b)", + "spark": "SELECT a, b FROM x LATERAL VIEW EXPLODE(y) t AS a, b", + "hive": "SELECT a, b FROM x LATERAL VIEW EXPLODE(y) t AS a, b", + }, + ) + + # Unnest multiple Expression into respective mapped alias + self.validate_all( + "SELECT numbers, animals, n, a FROM (SELECT ARRAY(2, 5) AS numbers, ARRAY('dog', 'cat', 'bird') AS animals UNION ALL SELECT ARRAY(7, 8, 9), ARRAY('cow', 'pig')) AS x CROSS JOIN UNNEST(numbers, animals) AS t(n, a)", + write={ + "presto": "SELECT numbers, animals, n, a FROM (SELECT ARRAY[2, 5] AS numbers, ARRAY['dog', 'cat', 'bird'] AS animals UNION ALL SELECT ARRAY[7, 8, 9], ARRAY['cow', 'pig']) AS x CROSS JOIN UNNEST(numbers, animals) AS t(n, a)", + "spark": "SELECT numbers, animals, n, a FROM (SELECT ARRAY(2, 5) AS numbers, ARRAY('dog', 'cat', 'bird') AS animals UNION ALL SELECT ARRAY(7, 8, 9), ARRAY('cow', 'pig')) AS x LATERAL VIEW INLINE(ARRAYS_ZIP(numbers, animals)) t AS n, a", + "hive": UnsupportedError, + }, + ) + + # Unnest column to more then 2 alias (STRUCT) + self.validate_all( + "SELECT a, b, c, d, e FROM x CROSS JOIN UNNEST(y) AS t(a, b, c, d)", + write={ + "presto": "SELECT a, b, c, d, e FROM x CROSS JOIN UNNEST(y) AS t(a, b, c, d)", + "spark": UnsupportedError, + "hive": UnsupportedError, + }, + ) + + def test_multiple_chained_unnest(self): + self.validate_all( + "SELECT * FROM x CROSS JOIN UNNEST(a) AS j(lista) CROSS JOIN UNNEST(b) AS k(listb) CROSS JOIN UNNEST(c) AS l(listc)", + write={ + "presto": "SELECT * FROM x CROSS JOIN UNNEST(a) AS j(lista) CROSS JOIN UNNEST(b) AS k(listb) CROSS JOIN UNNEST(c) AS l(listc)", + "spark": "SELECT * FROM x LATERAL VIEW EXPLODE(a) j AS lista LATERAL VIEW EXPLODE(b) k AS listb LATERAL VIEW EXPLODE(c) l AS listc", + "hive": "SELECT * FROM x LATERAL VIEW EXPLODE(a) j AS lista LATERAL VIEW EXPLODE(b) k AS listb LATERAL VIEW EXPLODE(c) l AS listc", + }, + ) + def test_lateral_subquery(self): self.validate_identity( "SELECT art FROM tbl1 INNER JOIN LATERAL (SELECT art FROM tbl2) AS tbl2 ON tbl1.art = tbl2.art" @@ -1761,16 +1811,68 @@ class TestDialect(Validator): ) self.validate_all( "LEVENSHTEIN(col1, col2)", - write={ + read={ "bigquery": "EDIT_DISTANCE(col1, col2)", - "duckdb": "LEVENSHTEIN(col1, col2)", + "clickhouse": "editDistance(col1, col2)", "drill": "LEVENSHTEIN_DISTANCE(col1, col2)", + "duckdb": "LEVENSHTEIN(col1, col2)", + "hive": "LEVENSHTEIN(col1, col2)", + "spark": "LEVENSHTEIN(col1, col2)", + "postgres": "LEVENSHTEIN(col1, col2)", "presto": "LEVENSHTEIN_DISTANCE(col1, col2)", + "snowflake": "EDITDISTANCE(col1, col2)", + "sqlite": "EDITDIST3(col1, col2)", + "trino": "LEVENSHTEIN_DISTANCE(col1, col2)", + }, + write={ + "bigquery": "EDIT_DISTANCE(col1, col2)", + "clickhouse": "editDistance(col1, col2)", + "drill": "LEVENSHTEIN_DISTANCE(col1, col2)", + "duckdb": "LEVENSHTEIN(col1, col2)", "hive": "LEVENSHTEIN(col1, col2)", "spark": "LEVENSHTEIN(col1, col2)", + "postgres": "LEVENSHTEIN(col1, col2)", + "presto": "LEVENSHTEIN_DISTANCE(col1, col2)", + "snowflake": "EDITDISTANCE(col1, col2)", + "sqlite": "EDITDIST3(col1, col2)", + "trino": "LEVENSHTEIN_DISTANCE(col1, col2)", + }, + ) + + self.validate_all( + "LEVENSHTEIN(col1, col2, 1, 2, 3)", + write={ + "bigquery": UnsupportedError, + "clickhouse": UnsupportedError, + "drill": UnsupportedError, + "duckdb": UnsupportedError, + "hive": UnsupportedError, + "spark": UnsupportedError, + "postgres": "LEVENSHTEIN(col1, col2, 1, 2, 3)", + "presto": UnsupportedError, + "snowflake": UnsupportedError, + "sqlite": UnsupportedError, + "trino": UnsupportedError, }, ) self.validate_all( + "LEVENSHTEIN(col1, col2, 1, 2, 3, 4)", + write={ + "bigquery": UnsupportedError, + "clickhouse": UnsupportedError, + "drill": UnsupportedError, + "duckdb": UnsupportedError, + "hive": UnsupportedError, + "spark": UnsupportedError, + "postgres": "LEVENSHTEIN_LESS_EQUAL(col1, col2, 1, 2, 3, 4)", + "presto": UnsupportedError, + "snowflake": UnsupportedError, + "sqlite": UnsupportedError, + "trino": UnsupportedError, + }, + ) + + self.validate_all( "LEVENSHTEIN(coalesce(col1, col2), coalesce(col2, col1))", write={ "bigquery": "EDIT_DISTANCE(COALESCE(col1, col2), COALESCE(col2, col1))", @@ -3007,7 +3109,7 @@ FROM subquery2""", "databricks": f"MEDIAN(x){suffix}", "redshift": f"MEDIAN(x){suffix}", "oracle": f"MEDIAN(x){suffix}", - "clickhouse": f"MEDIAN(x){suffix}", + "clickhouse": f"median(x){suffix}", "postgres": f"PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}", }, ) |