summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_clickhouse.py
diff options
context:
space:
mode:
authorDaniel Baumann <mail@daniel-baumann.ch>2023-12-10 10:45:55 +0000
committerDaniel Baumann <mail@daniel-baumann.ch>2023-12-10 10:45:55 +0000
commit02df6cdb000c8dbf739abda2af321a4f90d1b059 (patch)
tree2fc1daf848082ff67a11e60025cac260e3c318b2 /tests/dialects/test_clickhouse.py
parentAdding upstream version 19.0.1. (diff)
downloadsqlglot-02df6cdb000c8dbf739abda2af321a4f90d1b059.tar.xz
sqlglot-02df6cdb000c8dbf739abda2af321a4f90d1b059.zip
Adding upstream version 20.1.0.upstream/20.1.0
Signed-off-by: Daniel Baumann <mail@daniel-baumann.ch>
Diffstat (limited to 'tests/dialects/test_clickhouse.py')
-rw-r--r--tests/dialects/test_clickhouse.py68
1 files changed, 45 insertions, 23 deletions
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 93d1ced..86ddb00 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -6,22 +6,6 @@ class TestClickhouse(Validator):
dialect = "clickhouse"
def test_clickhouse(self):
- self.validate_identity("x <> y")
-
- self.validate_all(
- "has([1], x)",
- read={
- "postgres": "x = any(array[1])",
- },
- )
- self.validate_all(
- "NOT has([1], x)",
- read={
- "postgres": "any(array[1]) <> x",
- },
- )
- self.validate_identity("x = y")
-
string_types = [
"BLOB",
"LONGBLOB",
@@ -40,6 +24,8 @@ class TestClickhouse(Validator):
self.assertEqual(expr.sql(dialect="clickhouse"), "COUNT(x)")
self.assertIsNone(expr._meta)
+ self.validate_identity("x = y")
+ self.validate_identity("x <> y")
self.validate_identity("SELECT * FROM (SELECT a FROM b SAMPLE 0.01)")
self.validate_identity("SELECT * FROM (SELECT a FROM b SAMPLE 1 / 10 OFFSET 1 / 2)")
self.validate_identity("SELECT sum(foo * bar) FROM bla SAMPLE 10000000")
@@ -81,7 +67,17 @@ class TestClickhouse(Validator):
self.validate_identity("position(haystack, needle, position)")
self.validate_identity("CAST(x AS DATETIME)")
self.validate_identity("CAST(x as MEDIUMINT)", "CAST(x AS Int32)")
-
+ self.validate_identity("SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src")
+ self.validate_identity(
+ "SELECT SUM(1) AS impressions, arrayJoin(cities) AS city, arrayJoin(browsers) AS browser FROM (SELECT ['Istanbul', 'Berlin', 'Bobruisk'] AS cities, ['Firefox', 'Chrome', 'Chrome'] AS browsers) GROUP BY 2, 3"
+ )
+ self.validate_identity(
+ "SELECT sum(1) AS impressions, (arrayJoin(arrayZip(cities, browsers)) AS t).1 AS city, t.2 AS browser FROM (SELECT ['Istanbul', 'Berlin', 'Bobruisk'] AS cities, ['Firefox', 'Chrome', 'Chrome'] AS browsers) GROUP BY 2, 3"
+ )
+ self.validate_identity(
+ "SELECT SUM(1) AS impressions FROM (SELECT ['Istanbul', 'Berlin', 'Bobruisk'] AS cities) WHERE arrayJoin(cities) IN ['Istanbul', 'Berlin']",
+ "SELECT SUM(1) AS impressions FROM (SELECT ['Istanbul', 'Berlin', 'Bobruisk'] AS cities) WHERE arrayJoin(cities) IN ('Istanbul', 'Berlin')",
+ )
self.validate_identity(
'SELECT CAST(tuple(1 AS "a", 2 AS "b", 3.0 AS "c").2 AS Nullable(String))'
)
@@ -102,6 +98,25 @@ class TestClickhouse(Validator):
)
self.validate_all(
+ "SELECT arrayJoin([1,2,3])",
+ write={
+ "clickhouse": "SELECT arrayJoin([1, 2, 3])",
+ "postgres": "SELECT UNNEST(ARRAY[1, 2, 3])",
+ },
+ )
+ self.validate_all(
+ "has([1], x)",
+ read={
+ "postgres": "x = any(array[1])",
+ },
+ )
+ self.validate_all(
+ "NOT has([1], x)",
+ read={
+ "postgres": "any(array[1]) <> x",
+ },
+ )
+ self.validate_all(
"SELECT CAST('2020-01-01' AS TIMESTAMP) + INTERVAL '500' microsecond",
read={
"duckdb": "SELECT TIMESTAMP '2020-01-01' + INTERVAL '500 us'",
@@ -197,12 +212,15 @@ class TestClickhouse(Validator):
},
)
self.validate_all(
- "CONCAT(CASE WHEN COALESCE(CAST(a AS String), '') IS NULL THEN COALESCE(CAST(a AS String), '') ELSE CAST(COALESCE(CAST(a AS String), '') AS String) END, CASE WHEN COALESCE(CAST(b AS String), '') IS NULL THEN COALESCE(CAST(b AS String), '') ELSE CAST(COALESCE(CAST(b AS String), '') AS String) END)",
- read={"postgres": "CONCAT(a, b)"},
- )
- self.validate_all(
- "CONCAT(CASE WHEN a IS NULL THEN a ELSE CAST(a AS String) END, CASE WHEN b IS NULL THEN b ELSE CAST(b AS String) END)",
- read={"mysql": "CONCAT(a, b)"},
+ "CONCAT(a, b)",
+ read={
+ "clickhouse": "CONCAT(a, b)",
+ "mysql": "CONCAT(a, b)",
+ },
+ write={
+ "mysql": "CONCAT(a, b)",
+ "postgres": "CONCAT(a, b)",
+ },
)
self.validate_all(
r"'Enum8(\'Sunday\' = 0)'", write={"clickhouse": "'Enum8(''Sunday'' = 0)'"}
@@ -320,6 +338,10 @@ class TestClickhouse(Validator):
self.validate_identity("WITH (SELECT foo) AS bar SELECT bar + 5")
self.validate_identity("WITH test1 AS (SELECT i + 1, j + 1 FROM test1) SELECT * FROM test1")
+ query = parse_one("""WITH (SELECT 1) AS y SELECT * FROM y""", read="clickhouse")
+ self.assertIsInstance(query.args["with"].expressions[0].this, exp.Subquery)
+ self.assertEqual(query.args["with"].expressions[0].alias, "y")
+
def test_ternary(self):
self.validate_all("x ? 1 : 2", write={"clickhouse": "CASE WHEN x THEN 1 ELSE 2 END"})
self.validate_all(