summaryrefslogtreecommitdiffstats
path: root/tests/test_transpile.py
diff options
context:
space:
mode:
Diffstat (limited to 'tests/test_transpile.py')
-rw-r--r--tests/test_transpile.py349
1 files changed, 349 insertions, 0 deletions
diff --git a/tests/test_transpile.py b/tests/test_transpile.py
new file mode 100644
index 0000000..28bcc7a
--- /dev/null
+++ b/tests/test_transpile.py
@@ -0,0 +1,349 @@
+import os
+import unittest
+from unittest import mock
+
+from sqlglot import parse_one, transpile
+from sqlglot.errors import ErrorLevel, ParseError, UnsupportedError
+from tests.helpers import (
+ assert_logger_contains,
+ load_sql_fixture_pairs,
+ load_sql_fixtures,
+)
+
+
+class TestTranspile(unittest.TestCase):
+ file_dir = os.path.dirname(__file__)
+ fixtures_dir = os.path.join(file_dir, "fixtures")
+ maxDiff = None
+
+ def validate(self, sql, target, **kwargs):
+ self.assertEqual(transpile(sql, **kwargs)[0], target)
+
+ def test_alias(self):
+ for key in ("union", "filter", "over", "from", "join"):
+ with self.subTest(f"alias {key}"):
+ self.validate(f"SELECT x AS {key}", f"SELECT x AS {key}")
+ self.validate(f'SELECT x "{key}"', f'SELECT x AS "{key}"')
+
+ with self.assertRaises(ParseError):
+ self.validate(f"SELECT x {key}", "")
+
+ def test_asc(self):
+ self.validate("SELECT x FROM y ORDER BY x ASC", "SELECT x FROM y ORDER BY x")
+
+ def test_paren(self):
+ with self.assertRaises(ParseError):
+ transpile("1 + (2 + 3")
+ transpile("select f(")
+
+ def test_some(self):
+ self.validate(
+ "SELECT * FROM x WHERE a = SOME (SELECT 1)",
+ "SELECT * FROM x WHERE a = ANY (SELECT 1)",
+ )
+
+ def test_space(self):
+ self.validate("SELECT MIN(3)>MIN(2)", "SELECT MIN(3) > MIN(2)")
+ self.validate("SELECT MIN(3)>=MIN(2)", "SELECT MIN(3) >= MIN(2)")
+ self.validate("SELECT 1>0", "SELECT 1 > 0")
+ self.validate("SELECT 3>=3", "SELECT 3 >= 3")
+
+ def test_comments(self):
+ self.validate("SELECT 1 FROM foo -- comment", "SELECT 1 FROM foo")
+ self.validate("SELECT 1 /* inline */ FROM foo -- comment", "SELECT 1 FROM foo")
+
+ self.validate(
+ """
+ SELECT 1 -- comment
+ FROM foo -- comment
+ """,
+ "SELECT 1 FROM foo",
+ )
+
+ self.validate(
+ """
+ SELECT 1 /* big comment
+ like this */
+ FROM foo -- comment
+ """,
+ "SELECT 1 FROM foo",
+ )
+
+ def test_types(self):
+ self.validate("INT x", "CAST(x AS INT)")
+ self.validate("VARCHAR x y", "CAST(x AS VARCHAR) AS y")
+ self.validate("STRING x y", "CAST(x AS TEXT) AS y")
+ self.validate("x::INT", "CAST(x AS INT)")
+ self.validate("x::INTEGER", "CAST(x AS INT)")
+ self.validate("x::INT y", "CAST(x AS INT) AS y")
+ self.validate("x::INT AS y", "CAST(x AS INT) AS y")
+ self.validate("x::INT::BOOLEAN", "CAST(CAST(x AS INT) AS BOOLEAN)")
+ self.validate("CAST(x::INT AS BOOLEAN)", "CAST(CAST(x AS INT) AS BOOLEAN)")
+ self.validate("CAST(x AS INT)::BOOLEAN", "CAST(CAST(x AS INT) AS BOOLEAN)")
+
+ with self.assertRaises(ParseError):
+ transpile("x::z")
+
+ def test_not_range(self):
+ self.validate("a NOT LIKE b", "NOT a LIKE b")
+ self.validate("a NOT BETWEEN b AND c", "NOT a BETWEEN b AND c")
+ self.validate("a NOT IN (1, 2)", "NOT a IN (1, 2)")
+ self.validate("a IS NOT NULL", "NOT a IS NULL")
+ self.validate("a LIKE TEXT y", "a LIKE CAST(y AS TEXT)")
+
+ def test_extract(self):
+ self.validate(
+ "EXTRACT(day FROM '2020-01-01'::TIMESTAMP)",
+ "EXTRACT(day FROM CAST('2020-01-01' AS TIMESTAMP))",
+ )
+ self.validate(
+ "EXTRACT(timezone FROM '2020-01-01'::TIMESTAMP)",
+ "EXTRACT(timezone FROM CAST('2020-01-01' AS TIMESTAMP))",
+ )
+ self.validate(
+ "EXTRACT(year FROM '2020-01-01'::TIMESTAMP WITH TIME ZONE)",
+ "EXTRACT(year FROM CAST('2020-01-01' AS TIMESTAMPTZ))",
+ )
+ self.validate(
+ "extract(month from '2021-01-31'::timestamp without time zone)",
+ "EXTRACT(month FROM CAST('2021-01-31' AS TIMESTAMP))",
+ )
+
+ def test_if(self):
+ self.validate(
+ "SELECT IF(a > 1, 1, 0) FROM foo",
+ "SELECT CASE WHEN a > 1 THEN 1 ELSE 0 END FROM foo",
+ )
+ self.validate(
+ "SELECT IF a > 1 THEN b END",
+ "SELECT CASE WHEN a > 1 THEN b END",
+ )
+ self.validate(
+ "SELECT IF a > 1 THEN b ELSE c END",
+ "SELECT CASE WHEN a > 1 THEN b ELSE c END",
+ )
+ self.validate(
+ "SELECT IF(a > 1, 1) FROM foo", "SELECT CASE WHEN a > 1 THEN 1 END FROM foo"
+ )
+
+ def test_ignore_nulls(self):
+ self.validate("SELECT COUNT(x RESPECT NULLS)", "SELECT COUNT(x)")
+
+ def test_time(self):
+ self.validate("TIMESTAMP '2020-01-01'", "CAST('2020-01-01' AS TIMESTAMP)")
+ self.validate(
+ "TIMESTAMP WITH TIME ZONE '2020-01-01'", "CAST('2020-01-01' AS TIMESTAMPTZ)"
+ )
+ self.validate(
+ "TIMESTAMP(9) WITH TIME ZONE '2020-01-01'",
+ "CAST('2020-01-01' AS TIMESTAMPTZ(9))",
+ )
+ self.validate(
+ "TIMESTAMP WITHOUT TIME ZONE '2020-01-01'",
+ "CAST('2020-01-01' AS TIMESTAMP)",
+ )
+ self.validate("'2020-01-01'::TIMESTAMP", "CAST('2020-01-01' AS TIMESTAMP)")
+ self.validate(
+ "'2020-01-01'::TIMESTAMP WITHOUT TIME ZONE",
+ "CAST('2020-01-01' AS TIMESTAMP)",
+ )
+ self.validate(
+ "'2020-01-01'::TIMESTAMP WITH TIME ZONE",
+ "CAST('2020-01-01' AS TIMESTAMPTZ)",
+ )
+ self.validate(
+ "timestamp with time zone '2025-11-20 00:00:00+00' AT TIME ZONE 'Africa/Cairo'",
+ "CAST('2025-11-20 00:00:00+00' AS TIMESTAMPTZ) AT TIME ZONE 'Africa/Cairo'",
+ )
+
+ self.validate("DATE '2020-01-01'", "CAST('2020-01-01' AS DATE)")
+ self.validate("'2020-01-01'::DATE", "CAST('2020-01-01' AS DATE)")
+ self.validate("STR_TO_TIME('x', 'y')", "STRPTIME('x', 'y')", write="duckdb")
+ self.validate(
+ "STR_TO_UNIX('x', 'y')", "EPOCH(STRPTIME('x', 'y'))", write="duckdb"
+ )
+ self.validate("TIME_TO_STR(x, 'y')", "STRFTIME(x, 'y')", write="duckdb")
+ self.validate("TIME_TO_UNIX(x)", "EPOCH(x)", write="duckdb")
+ self.validate(
+ "UNIX_TO_STR(123, 'y')",
+ "STRFTIME(TO_TIMESTAMP(CAST(123 AS BIGINT)), 'y')",
+ write="duckdb",
+ )
+ self.validate(
+ "UNIX_TO_TIME(123)",
+ "TO_TIMESTAMP(CAST(123 AS BIGINT))",
+ write="duckdb",
+ )
+
+ self.validate(
+ "STR_TO_TIME(x, 'y')",
+ "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'y')) AS TIMESTAMP)",
+ write="hive",
+ )
+ self.validate(
+ "STR_TO_TIME(x, 'yyyy-MM-dd HH:mm:ss')",
+ "CAST(x AS TIMESTAMP)",
+ write="hive",
+ )
+ self.validate(
+ "STR_TO_TIME(x, 'yyyy-MM-dd')",
+ "CAST(x AS TIMESTAMP)",
+ write="hive",
+ )
+
+ self.validate(
+ "STR_TO_UNIX('x', 'y')",
+ "UNIX_TIMESTAMP('x', 'y')",
+ write="hive",
+ )
+ self.validate("TIME_TO_STR(x, 'y')", "DATE_FORMAT(x, 'y')", write="hive")
+
+ self.validate("TIME_STR_TO_TIME(x)", "TIME_STR_TO_TIME(x)", write=None)
+ self.validate("TIME_STR_TO_UNIX(x)", "TIME_STR_TO_UNIX(x)", write=None)
+ self.validate("TIME_TO_TIME_STR(x)", "CAST(x AS TEXT)", write=None)
+ self.validate("TIME_TO_STR(x, 'y')", "TIME_TO_STR(x, 'y')", write=None)
+ self.validate("TIME_TO_UNIX(x)", "TIME_TO_UNIX(x)", write=None)
+ self.validate("UNIX_TO_STR(x, 'y')", "UNIX_TO_STR(x, 'y')", write=None)
+ self.validate("UNIX_TO_TIME(x)", "UNIX_TO_TIME(x)", write=None)
+ self.validate("UNIX_TO_TIME_STR(x)", "UNIX_TO_TIME_STR(x)", write=None)
+ self.validate("TIME_STR_TO_DATE(x)", "TIME_STR_TO_DATE(x)", write=None)
+
+ self.validate("TIME_STR_TO_DATE(x)", "TO_DATE(x)", write="hive")
+ self.validate(
+ "UNIX_TO_STR(x, 'yyyy-MM-dd HH:mm:ss')", "FROM_UNIXTIME(x)", write="hive"
+ )
+ self.validate(
+ "STR_TO_UNIX(x, 'yyyy-MM-dd HH:mm:ss')", "UNIX_TIMESTAMP(x)", write="hive"
+ )
+ self.validate("IF(x > 1, x + 1)", "IF(x > 1, x + 1)", write="presto")
+ self.validate("IF(x > 1, 1 + 1)", "IF(x > 1, 1 + 1)", write="hive")
+ self.validate("IF(x > 1, 1, 0)", "IF(x > 1, 1, 0)", write="hive")
+
+ self.validate(
+ "TIME_TO_UNIX(x)",
+ "UNIX_TIMESTAMP(x)",
+ write="hive",
+ )
+ self.validate("UNIX_TO_STR(123, 'y')", "FROM_UNIXTIME(123, 'y')", write="hive")
+ self.validate(
+ "UNIX_TO_TIME(123)",
+ "FROM_UNIXTIME(123)",
+ write="hive",
+ )
+
+ self.validate("STR_TO_TIME('x', 'y')", "DATE_PARSE('x', 'y')", write="presto")
+ self.validate(
+ "STR_TO_UNIX('x', 'y')", "TO_UNIXTIME(DATE_PARSE('x', 'y'))", write="presto"
+ )
+ self.validate("TIME_TO_STR(x, 'y')", "DATE_FORMAT(x, 'y')", write="presto")
+ self.validate("TIME_TO_UNIX(x)", "TO_UNIXTIME(x)", write="presto")
+ self.validate(
+ "UNIX_TO_STR(123, 'y')",
+ "DATE_FORMAT(FROM_UNIXTIME(123), 'y')",
+ write="presto",
+ )
+ self.validate("UNIX_TO_TIME(123)", "FROM_UNIXTIME(123)", write="presto")
+
+ self.validate("STR_TO_TIME('x', 'y')", "TO_TIMESTAMP('x', 'y')", write="spark")
+ self.validate(
+ "STR_TO_UNIX('x', 'y')", "UNIX_TIMESTAMP('x', 'y')", write="spark"
+ )
+ self.validate("TIME_TO_STR(x, 'y')", "DATE_FORMAT(x, 'y')", write="spark")
+
+ self.validate(
+ "TIME_TO_UNIX(x)",
+ "UNIX_TIMESTAMP(x)",
+ write="spark",
+ )
+ self.validate("UNIX_TO_STR(123, 'y')", "FROM_UNIXTIME(123, 'y')", write="spark")
+ self.validate(
+ "UNIX_TO_TIME(123)",
+ "FROM_UNIXTIME(123)",
+ write="spark",
+ )
+ self.validate(
+ "CREATE TEMPORARY TABLE test AS SELECT 1",
+ "CREATE TEMPORARY VIEW test AS SELECT 1",
+ write="spark",
+ )
+
+ @mock.patch("sqlglot.helper.logger")
+ def test_index_offset(self, mock_logger):
+ self.validate("x[0]", "x[1]", write="presto", identity=False)
+ self.validate("x[1]", "x[0]", read="presto", identity=False)
+ mock_logger.warning.assert_any_call("Applying array index offset (%s)", 1)
+ mock_logger.warning.assert_any_call("Applying array index offset (%s)", -1)
+
+ def test_identity(self):
+ self.assertEqual(transpile("")[0], "")
+ for sql in load_sql_fixtures("identity.sql"):
+ with self.subTest(sql):
+ self.assertEqual(transpile(sql)[0], sql.strip())
+
+ def test_partial(self):
+ for sql in load_sql_fixtures("partial.sql"):
+ with self.subTest(sql):
+ self.assertEqual(
+ transpile(sql, error_level=ErrorLevel.IGNORE)[0], sql.strip()
+ )
+
+ def test_pretty(self):
+ for _, sql, pretty in load_sql_fixture_pairs("pretty.sql"):
+ with self.subTest(sql[:100]):
+ generated = transpile(sql, pretty=True)[0]
+ self.assertEqual(generated, pretty)
+ self.assertEqual(parse_one(sql), parse_one(pretty))
+
+ @mock.patch("sqlglot.parser.logger")
+ def test_error_level(self, logger):
+ invalid = "x + 1. ("
+ errors = [
+ "Required keyword: 'expressions' missing for <class 'sqlglot.expressions.Aliases'>. Line 1, Col: 8.\n x + 1. \033[4m(\033[0m",
+ "Expecting ). Line 1, Col: 8.\n x + 1. \033[4m(\033[0m",
+ ]
+
+ transpile(invalid, error_level=ErrorLevel.WARN)
+ for error in errors:
+ assert_logger_contains(error, logger)
+
+ with self.assertRaises(ParseError) as ctx:
+ transpile(invalid, error_level=ErrorLevel.IMMEDIATE)
+ self.assertEqual(str(ctx.exception), errors[0])
+
+ with self.assertRaises(ParseError) as ctx:
+ transpile(invalid, error_level=ErrorLevel.RAISE)
+ self.assertEqual(str(ctx.exception), "\n\n".join(errors))
+
+ more_than_max_errors = "(((("
+ expected = (
+ "Expecting ). Line 1, Col: 4.\n (((\033[4m(\033[0m\n\n"
+ "Required keyword: 'this' missing for <class 'sqlglot.expressions.Paren'>. Line 1, Col: 4.\n (((\033[4m(\033[0m\n\n"
+ "Expecting ). Line 1, Col: 4.\n (((\033[4m(\033[0m\n\n"
+ "... and 2 more"
+ )
+ with self.assertRaises(ParseError) as ctx:
+ transpile(more_than_max_errors, error_level=ErrorLevel.RAISE)
+ self.assertEqual(str(ctx.exception), expected)
+
+ @mock.patch("sqlglot.generator.logger")
+ def test_unsupported_level(self, logger):
+ def unsupported(level):
+ transpile(
+ "SELECT MAP(a, b), MAP(a, b), MAP(a, b), MAP(a, b)",
+ read="presto",
+ write="hive",
+ unsupported_level=level,
+ )
+
+ error = "Cannot convert array columns into map use SparkSQL instead."
+
+ unsupported(ErrorLevel.WARN)
+ assert_logger_contains("\n".join([error] * 4), logger, level="warning")
+
+ with self.assertRaises(UnsupportedError) as ctx:
+ unsupported(ErrorLevel.RAISE)
+ self.assertEqual(str(ctx.exception).count(error), 3)
+
+ with self.assertRaises(UnsupportedError) as ctx:
+ unsupported(ErrorLevel.IMMEDIATE)
+ self.assertEqual(str(ctx.exception).count(error), 1)
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-01 22:20:23 +0000