diff options
Diffstat (limited to 'tests/dialects/test_presto.py')
| -rw-r--r-- | tests/dialects/test_presto.py | 122 |
1 files changed, 100 insertions, 22 deletions
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py index 6a82756..97a387c 100644 --- a/tests/dialects/test_presto.py +++ b/tests/dialects/test_presto.py @@ -379,6 +379,16 @@ class TestPresto(Validator): "presto": "TIMESTAMP(x, '12:00:00')", }, ) + self.validate_all( + "DATE_ADD('DAY', CAST(x AS BIGINT), y)", + write={ + "presto": "DATE_ADD('DAY', CAST(x AS BIGINT), y)", + }, + read={ + "presto": "DATE_ADD('DAY', x, y)", + }, + ) + self.validate_identity("DATE_ADD('DAY', 1, y)") def test_ddl(self): self.validate_all( @@ -462,10 +472,10 @@ class TestPresto(Validator): ) self.validate_all( - 'CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" TEXT) WITH (PARTITIONED BY=("ds"))', + """CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" TEXT) COMMENT 'comment' WITH (PARTITIONED BY=("ds"))""", write={ - "spark": "CREATE TABLE IF NOT EXISTS x (`cola` INT, `ds` STRING) PARTITIONED BY (`ds`)", - "presto": """CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" VARCHAR) WITH (PARTITIONED_BY=ARRAY['ds'])""", + "spark": "CREATE TABLE IF NOT EXISTS x (`cola` INT, `ds` STRING) COMMENT 'comment' PARTITIONED BY (`ds`)", + "presto": """CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" VARCHAR) COMMENT 'comment' WITH (PARTITIONED_BY=ARRAY['ds'])""", }, ) @@ -534,26 +544,18 @@ class TestPresto(Validator): }, ) - def test_presto(self): - self.validate_identity("string_agg(x, ',')", "ARRAY_JOIN(ARRAY_AGG(x), ',')") - self.validate_identity( - "SELECT * FROM example.testdb.customer_orders FOR VERSION AS OF 8954597067493422955" - ) - self.validate_identity( - "SELECT * FROM example.testdb.customer_orders FOR TIMESTAMP AS OF CAST('2022-03-23 09:59:29.803 Europe/Vienna' AS TIMESTAMP)" - ) - - self.validate_identity("SELECT * FROM x OFFSET 1 LIMIT 1") - self.validate_identity("SELECT * FROM x OFFSET 1 FETCH FIRST 1 ROWS ONLY") - self.validate_identity("SELECT BOOL_OR(a > 10) FROM asd AS T(a)") - self.validate_identity("SELECT * FROM (VALUES (1))") - self.validate_identity("START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE") - self.validate_identity("START TRANSACTION ISOLATION LEVEL REPEATABLE READ") - self.validate_identity("APPROX_PERCENTILE(a, b, c, d)") - self.validate_identity( - "SELECT SPLIT_TO_MAP('a:1;b:2;a:3', ';', ':', (k, v1, v2) -> CONCAT(v1, v2))" - ) + def test_unicode_string(self): + for prefix in ("u&", "U&"): + self.validate_identity( + f"{prefix}'Hello winter \\2603 !'", + "U&'Hello winter \\2603 !'", + ) + self.validate_identity( + f"{prefix}'Hello winter #2603 !' UESCAPE '#'", + "U&'Hello winter #2603 !' UESCAPE '#'", + ) + def test_presto(self): with self.assertLogs(helper_logger) as cm: self.validate_all( "SELECT COALESCE(ELEMENT_AT(MAP_FROM_ENTRIES(ARRAY[(51, '1')]), id), quantity) FROM my_table", @@ -572,6 +574,24 @@ class TestPresto(Validator): }, ) + self.validate_identity("string_agg(x, ',')", "ARRAY_JOIN(ARRAY_AGG(x), ',')") + self.validate_identity("SELECT * FROM x OFFSET 1 LIMIT 1") + self.validate_identity("SELECT * FROM x OFFSET 1 FETCH FIRST 1 ROWS ONLY") + self.validate_identity("SELECT BOOL_OR(a > 10) FROM asd AS T(a)") + self.validate_identity("SELECT * FROM (VALUES (1))") + self.validate_identity("START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE") + self.validate_identity("START TRANSACTION ISOLATION LEVEL REPEATABLE READ") + self.validate_identity("APPROX_PERCENTILE(a, b, c, d)") + self.validate_identity( + "SELECT SPLIT_TO_MAP('a:1;b:2;a:3', ';', ':', (k, v1, v2) -> CONCAT(v1, v2))" + ) + self.validate_identity( + "SELECT * FROM example.testdb.customer_orders FOR VERSION AS OF 8954597067493422955" + ) + self.validate_identity( + "SELECT * FROM example.testdb.customer_orders FOR TIMESTAMP AS OF CAST('2022-03-23 09:59:29.803 Europe/Vienna' AS TIMESTAMP)" + ) + self.validate_all( "SELECT MAX_BY(a.id, a.timestamp) FROM a", read={ @@ -1044,3 +1064,61 @@ MATCH_RECOGNIZE ( )""", pretty=True, ) + + def test_to_char(self): + self.validate_all( + "TO_CHAR(ts, 'dd')", + write={ + "bigquery": "FORMAT_DATE('%d', ts)", + "presto": "DATE_FORMAT(ts, '%d')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'hh')", + write={ + "bigquery": "FORMAT_DATE('%H', ts)", + "presto": "DATE_FORMAT(ts, '%H')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'hh24')", + write={ + "bigquery": "FORMAT_DATE('%H', ts)", + "presto": "DATE_FORMAT(ts, '%H')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'mi')", + write={ + "bigquery": "FORMAT_DATE('%M', ts)", + "presto": "DATE_FORMAT(ts, '%i')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'mm')", + write={ + "bigquery": "FORMAT_DATE('%m', ts)", + "presto": "DATE_FORMAT(ts, '%m')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'ss')", + write={ + "bigquery": "FORMAT_DATE('%S', ts)", + "presto": "DATE_FORMAT(ts, '%s')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'yyyy')", + write={ + "bigquery": "FORMAT_DATE('%Y', ts)", + "presto": "DATE_FORMAT(ts, '%Y')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'yy')", + write={ + "bigquery": "FORMAT_DATE('%y', ts)", + "presto": "DATE_FORMAT(ts, '%y')", + }, + ) |