summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_dialect.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_dialect.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_dialect.py')
-rw-r--r--tests/dialects/test_dialect.py219
1 files changed, 190 insertions, 29 deletions
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 2546c98..49afc62 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -7,9 +7,10 @@ from sqlglot import (
ParseError,
TokenError,
UnsupportedError,
+ exp,
parse_one,
)
-from sqlglot.dialects import Hive
+from sqlglot.dialects import BigQuery, Hive, Snowflake
class Validator(unittest.TestCase):
@@ -78,9 +79,56 @@ class TestDialect(Validator):
self.assertIsNotNone(Dialect[dialect.value])
def test_get_or_raise(self):
- self.assertEqual(Dialect.get_or_raise(Hive), Hive)
- self.assertEqual(Dialect.get_or_raise(Hive()), Hive)
- self.assertEqual(Dialect.get_or_raise("hive"), Hive)
+ self.assertIsInstance(Dialect.get_or_raise(Hive), Hive)
+ self.assertIsInstance(Dialect.get_or_raise(Hive()), Hive)
+ self.assertIsInstance(Dialect.get_or_raise("hive"), Hive)
+
+ with self.assertRaises(ValueError):
+ Dialect.get_or_raise(1)
+
+ default_mysql = Dialect.get_or_raise("mysql")
+ self.assertEqual(default_mysql.normalization_strategy, "CASE_SENSITIVE")
+
+ lowercase_mysql = Dialect.get_or_raise("mysql,normalization_strategy=lowercase")
+ self.assertEqual(lowercase_mysql.normalization_strategy, "LOWERCASE")
+
+ lowercase_mysql = Dialect.get_or_raise("mysql, normalization_strategy = lowercase")
+ self.assertEqual(lowercase_mysql.normalization_strategy.value, "LOWERCASE")
+
+ with self.assertRaises(ValueError) as cm:
+ Dialect.get_or_raise("mysql, normalization_strategy")
+
+ self.assertEqual(
+ str(cm.exception),
+ "Invalid dialect format: 'mysql, normalization_strategy'. "
+ "Please use the correct format: 'dialect [, k1 = v2 [, ...]]'.",
+ )
+
+ def test_compare_dialects(self):
+ bigquery_class = Dialect["bigquery"]
+ bigquery_object = BigQuery()
+ bigquery_string = "bigquery"
+
+ snowflake_class = Dialect["snowflake"]
+ snowflake_object = Snowflake()
+ snowflake_string = "snowflake"
+
+ self.assertEqual(snowflake_class, snowflake_class)
+ self.assertEqual(snowflake_class, snowflake_object)
+ self.assertEqual(snowflake_class, snowflake_string)
+ self.assertEqual(snowflake_object, snowflake_object)
+ self.assertEqual(snowflake_object, snowflake_string)
+
+ self.assertNotEqual(snowflake_class, bigquery_class)
+ self.assertNotEqual(snowflake_class, bigquery_object)
+ self.assertNotEqual(snowflake_class, bigquery_string)
+ self.assertNotEqual(snowflake_object, bigquery_object)
+ self.assertNotEqual(snowflake_object, bigquery_string)
+
+ self.assertTrue(snowflake_class in {"snowflake", "bigquery"})
+ self.assertTrue(snowflake_object in {"snowflake", "bigquery"})
+ self.assertFalse(snowflake_class in {"bigquery", "redshift"})
+ self.assertFalse(snowflake_object in {"bigquery", "redshift"})
def test_cast(self):
self.validate_all(
@@ -561,6 +609,7 @@ class TestDialect(Validator):
self.validate_all(
"TIME_TO_STR(x, '%Y-%m-%d')",
write={
+ "bigquery": "FORMAT_DATE('%Y-%m-%d', x)",
"drill": "TO_CHAR(x, 'yyyy-MM-dd')",
"duckdb": "STRFTIME(x, '%Y-%m-%d')",
"hive": "DATE_FORMAT(x, 'yyyy-MM-dd')",
@@ -866,9 +915,9 @@ class TestDialect(Validator):
write={
"drill": "CAST(x AS DATE)",
"duckdb": "CAST(x AS DATE)",
- "hive": "TO_DATE(x)",
- "presto": "CAST(DATE_PARSE(x, '%Y-%m-%d') AS DATE)",
- "spark": "TO_DATE(x)",
+ "hive": "CAST(x AS DATE)",
+ "presto": "CAST(x AS DATE)",
+ "spark": "CAST(x AS DATE)",
"sqlite": "x",
},
)
@@ -893,7 +942,7 @@ class TestDialect(Validator):
self.validate_all(
"TS_OR_DS_ADD(CURRENT_DATE, 1, 'DAY')",
write={
- "presto": "DATE_ADD('DAY', 1, CURRENT_DATE)",
+ "presto": "DATE_ADD('DAY', 1, CAST(CAST(CURRENT_DATE AS TIMESTAMP) AS DATE))",
"hive": "DATE_ADD(CURRENT_DATE, 1)",
},
)
@@ -1269,13 +1318,6 @@ class TestDialect(Validator):
},
)
self.validate_all(
- "SELECT * FROM a ORDER BY col_a NULLS LAST",
- write={
- "mysql": UnsupportedError,
- "starrocks": UnsupportedError,
- },
- )
- self.validate_all(
"POSITION(needle in haystack)",
write={
"drill": "STRPOS(haystack, needle)",
@@ -1315,35 +1357,37 @@ class TestDialect(Validator):
self.validate_all(
"CONCAT_WS('-', 'a', 'b')",
write={
+ "clickhouse": "CONCAT_WS('-', 'a', 'b')",
"duckdb": "CONCAT_WS('-', 'a', 'b')",
- "presto": "CONCAT_WS('-', 'a', 'b')",
+ "presto": "CONCAT_WS('-', CAST('a' AS VARCHAR), CAST('b' AS VARCHAR))",
"hive": "CONCAT_WS('-', 'a', 'b')",
"spark": "CONCAT_WS('-', 'a', 'b')",
- "trino": "CONCAT_WS('-', 'a', 'b')",
+ "trino": "CONCAT_WS('-', CAST('a' AS VARCHAR), CAST('b' AS VARCHAR))",
},
)
self.validate_all(
"CONCAT_WS('-', x)",
write={
+ "clickhouse": "CONCAT_WS('-', x)",
"duckdb": "CONCAT_WS('-', x)",
"hive": "CONCAT_WS('-', x)",
- "presto": "CONCAT_WS('-', x)",
+ "presto": "CONCAT_WS('-', CAST(x AS VARCHAR))",
"spark": "CONCAT_WS('-', x)",
- "trino": "CONCAT_WS('-', x)",
+ "trino": "CONCAT_WS('-', CAST(x AS VARCHAR))",
},
)
self.validate_all(
"CONCAT(a)",
write={
- "clickhouse": "a",
- "presto": "a",
- "trino": "a",
+ "clickhouse": "CONCAT(a)",
+ "presto": "CAST(a AS VARCHAR)",
+ "trino": "CAST(a AS VARCHAR)",
"tsql": "a",
},
)
self.validate_all(
- "COALESCE(CAST(a AS TEXT), '')",
+ "CONCAT(COALESCE(a, ''))",
read={
"drill": "CONCAT(a)",
"duckdb": "CONCAT(a)",
@@ -1442,6 +1486,76 @@ class TestDialect(Validator):
"spark": "FILTER(the_array, x -> x > 0)",
},
)
+ self.validate_all(
+ "a / b",
+ write={
+ "bigquery": "a / b",
+ "clickhouse": "a / b",
+ "databricks": "a / b",
+ "duckdb": "a / b",
+ "hive": "a / b",
+ "mysql": "a / b",
+ "oracle": "a / b",
+ "snowflake": "a / b",
+ "spark": "a / b",
+ "starrocks": "a / b",
+ "drill": "CAST(a AS DOUBLE) / b",
+ "postgres": "CAST(a AS DOUBLE PRECISION) / b",
+ "presto": "CAST(a AS DOUBLE) / b",
+ "redshift": "CAST(a AS DOUBLE PRECISION) / b",
+ "sqlite": "CAST(a AS REAL) / b",
+ "teradata": "CAST(a AS DOUBLE) / b",
+ "trino": "CAST(a AS DOUBLE) / b",
+ "tsql": "CAST(a AS FLOAT) / b",
+ },
+ )
+
+ def test_typeddiv(self):
+ typed_div = exp.Div(this=exp.column("a"), expression=exp.column("b"), typed=True)
+ div = exp.Div(this=exp.column("a"), expression=exp.column("b"))
+ typed_div_dialect = "presto"
+ div_dialect = "hive"
+ INT = exp.DataType.Type.INT
+ FLOAT = exp.DataType.Type.FLOAT
+
+ for expression, types, dialect, expected in [
+ (typed_div, (None, None), typed_div_dialect, "a / b"),
+ (typed_div, (None, None), div_dialect, "a / b"),
+ (div, (None, None), typed_div_dialect, "CAST(a AS DOUBLE) / b"),
+ (div, (None, None), div_dialect, "a / b"),
+ (typed_div, (INT, INT), typed_div_dialect, "a / b"),
+ (typed_div, (INT, INT), div_dialect, "CAST(a / b AS BIGINT)"),
+ (div, (INT, INT), typed_div_dialect, "CAST(a AS DOUBLE) / b"),
+ (div, (INT, INT), div_dialect, "a / b"),
+ (typed_div, (FLOAT, FLOAT), typed_div_dialect, "a / b"),
+ (typed_div, (FLOAT, FLOAT), div_dialect, "a / b"),
+ (div, (FLOAT, FLOAT), typed_div_dialect, "a / b"),
+ (div, (FLOAT, FLOAT), div_dialect, "a / b"),
+ (typed_div, (INT, FLOAT), typed_div_dialect, "a / b"),
+ (typed_div, (INT, FLOAT), div_dialect, "a / b"),
+ (div, (INT, FLOAT), typed_div_dialect, "a / b"),
+ (div, (INT, FLOAT), div_dialect, "a / b"),
+ ]:
+ with self.subTest(f"{expression.__class__.__name__} {types} {dialect} -> {expected}"):
+ expression = expression.copy()
+ expression.left.type = types[0]
+ expression.right.type = types[1]
+ self.assertEqual(expected, expression.sql(dialect=dialect))
+
+ def test_safediv(self):
+ safe_div = exp.Div(this=exp.column("a"), expression=exp.column("b"), safe=True)
+ div = exp.Div(this=exp.column("a"), expression=exp.column("b"))
+ safe_div_dialect = "mysql"
+ div_dialect = "snowflake"
+
+ for expression, dialect, expected in [
+ (safe_div, safe_div_dialect, "a / b"),
+ (safe_div, div_dialect, "a / NULLIF(b, 0)"),
+ (div, safe_div_dialect, "a / b"),
+ (div, div_dialect, "a / b"),
+ ]:
+ with self.subTest(f"{expression.__class__.__name__} {dialect} -> {expected}"):
+ self.assertEqual(expected, expression.sql(dialect=dialect))
def test_limit(self):
self.validate_all(
@@ -1547,7 +1661,7 @@ class TestDialect(Validator):
"CREATE TABLE t (b1 BINARY, b2 BINARY(1024), c1 TEXT, c2 TEXT(1024))",
write={
"duckdb": "CREATE TABLE t (b1 BLOB, b2 BLOB(1024), c1 TEXT, c2 TEXT(1024))",
- "hive": "CREATE TABLE t (b1 BINARY, b2 BINARY(1024), c1 STRING, c2 STRING(1024))",
+ "hive": "CREATE TABLE t (b1 BINARY, b2 BINARY(1024), c1 STRING, c2 VARCHAR(1024))",
"oracle": "CREATE TABLE t (b1 BLOB, b2 BLOB(1024), c1 CLOB, c2 CLOB(1024))",
"postgres": "CREATE TABLE t (b1 BYTEA, b2 BYTEA(1024), c1 TEXT, c2 TEXT(1024))",
"sqlite": "CREATE TABLE t (b1 BLOB, b2 BLOB(1024), c1 TEXT, c2 TEXT(1024))",
@@ -1864,7 +1978,7 @@ SELECT
write={
"bigquery": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",
"clickhouse": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",
- "databricks": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",
+ "databricks": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT c FROM t) AS subq",
"duckdb": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",
"hive": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT c FROM t) AS subq",
"mysql": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",
@@ -1872,11 +1986,11 @@ SELECT
"presto": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",
"redshift": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",
"snowflake": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",
- "spark": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",
+ "spark": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT c FROM t) AS subq",
"spark2": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT c FROM t) AS subq",
"sqlite": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",
"trino": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq",
- "tsql": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT c FROM t) AS subq",
+ "tsql": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT c AS c FROM t) AS subq",
},
)
self.validate_all(
@@ -1885,13 +1999,60 @@ SELECT
"bigquery": "SELECT * FROM (SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq1) AS subq2",
"duckdb": "SELECT * FROM (SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq1) AS subq2",
"hive": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT * FROM (SELECT c FROM t) AS subq1) AS subq2",
- "tsql": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT * FROM (SELECT c FROM t) AS subq1) AS subq2",
+ "tsql": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT * FROM (SELECT c AS c FROM t) AS subq1) AS subq2",
},
)
self.validate_all(
"WITH t1(x) AS (SELECT 1) SELECT * FROM (WITH t2(y) AS (SELECT 2) SELECT y FROM t2) AS subq",
write={
"duckdb": "WITH t1(x) AS (SELECT 1) SELECT * FROM (WITH t2(y) AS (SELECT 2) SELECT y FROM t2) AS subq",
- "tsql": "WITH t1(x) AS (SELECT 1), t2(y) AS (SELECT 2) SELECT * FROM (SELECT y FROM t2) AS subq",
+ "tsql": "WITH t1(x) AS (SELECT 1), t2(y) AS (SELECT 2) SELECT * FROM (SELECT y AS y FROM t2) AS subq",
},
)
+
+ def test_unsupported_null_ordering(self):
+ # We'll transpile a portable query from the following dialects to MySQL / T-SQL, which
+ # both treat NULLs as small values, so the expected output queries should be equivalent
+ with_last_nulls = "duckdb"
+ with_small_nulls = "spark"
+ with_large_nulls = "postgres"
+
+ sql = "SELECT * FROM t ORDER BY c"
+ sql_nulls_last = "SELECT * FROM t ORDER BY CASE WHEN c IS NULL THEN 1 ELSE 0 END, c"
+ sql_nulls_first = "SELECT * FROM t ORDER BY CASE WHEN c IS NULL THEN 1 ELSE 0 END DESC, c"
+
+ for read_dialect, desc, nulls_first, expected_sql in (
+ (with_last_nulls, False, None, sql_nulls_last),
+ (with_last_nulls, True, None, sql),
+ (with_last_nulls, False, True, sql),
+ (with_last_nulls, True, True, sql_nulls_first),
+ (with_last_nulls, False, False, sql_nulls_last),
+ (with_last_nulls, True, False, sql),
+ (with_small_nulls, False, None, sql),
+ (with_small_nulls, True, None, sql),
+ (with_small_nulls, False, True, sql),
+ (with_small_nulls, True, True, sql_nulls_first),
+ (with_small_nulls, False, False, sql_nulls_last),
+ (with_small_nulls, True, False, sql),
+ (with_large_nulls, False, None, sql_nulls_last),
+ (with_large_nulls, True, None, sql_nulls_first),
+ (with_large_nulls, False, True, sql),
+ (with_large_nulls, True, True, sql_nulls_first),
+ (with_large_nulls, False, False, sql_nulls_last),
+ (with_large_nulls, True, False, sql),
+ ):
+ with self.subTest(
+ f"read: {read_dialect}, descending: {desc}, nulls first: {nulls_first}"
+ ):
+ sort_order = " DESC" if desc else ""
+ null_order = (
+ " NULLS FIRST"
+ if nulls_first
+ else (" NULLS LAST" if nulls_first is not None else "")
+ )
+
+ expected_sql = f"{expected_sql}{sort_order}"
+ expression = parse_one(f"{sql}{sort_order}{null_order}", read=read_dialect)
+
+ self.assertEqual(expression.sql(dialect="mysql"), expected_sql)
+ self.assertEqual(expression.sql(dialect="tsql"), expected_sql)