summaryrefslogtreecommitdiffstats
path: root/tests
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--tests/dialects/test_databricks.py15
-rw-r--r--tests/dialects/test_dialect.py4
-rw-r--r--tests/dialects/test_duckdb.py22
-rw-r--r--tests/dialects/test_hive.py38
-rw-r--r--tests/dialects/test_postgres.py45
-rw-r--r--tests/dialects/test_presto.py7
-rw-r--r--tests/dialects/test_snowflake.py32
-rw-r--r--tests/dialects/test_spark.py9
-rw-r--r--tests/fixtures/identity.sql76
-rw-r--r--tests/test_tokens.py6
-rw-r--r--tests/test_transforms.py11
11 files changed, 217 insertions, 48 deletions
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index 7560d61..1d80dc0 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -102,3 +102,18 @@ class TestDatabricks(Validator):
"databricks": "SELECT DATEADD(DAY, 1, '2020-01-01')",
},
)
+
+ def test_without_as(self):
+ self.validate_all(
+ "CREATE TABLE x (SELECT 1)",
+ write={
+ "databricks": "CREATE TABLE x AS (SELECT 1)",
+ },
+ )
+
+ self.validate_all(
+ "WITH x (select 1) SELECT * FROM x",
+ write={
+ "databricks": "WITH x AS (SELECT 1) SELECT * FROM x",
+ },
+ )
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index f1144ce..5a13655 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -9,9 +9,9 @@ class Validator(unittest.TestCase):
def parse_one(self, sql):
return parse_one(sql, read=self.dialect)
- def validate_identity(self, sql, write_sql=None):
+ def validate_identity(self, sql, write_sql=None, pretty=False):
expression = self.parse_one(sql)
- self.assertEqual(write_sql or sql, expression.sql(dialect=self.dialect))
+ self.assertEqual(write_sql or sql, expression.sql(dialect=self.dialect, pretty=pretty))
return expression
def validate_all(self, sql, read=None, write=None, pretty=False, identify=False):
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index a37062c..f6446ca 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -75,6 +75,19 @@ class TestDuckDB(Validator):
)
def test_duckdb(self):
+ self.validate_identity("SELECT {'a': 1} AS x")
+ self.validate_identity("SELECT {'a': {'b': {'c': 1}}, 'd': {'e': 2}} AS x")
+ self.validate_identity("SELECT {'x': 1, 'y': 2, 'z': 3}")
+ self.validate_identity(
+ "SELECT {'yes': 'duck', 'maybe': 'goose', 'huh': NULL, 'no': 'heron'}"
+ )
+ self.validate_identity("SELECT {'key1': 'string', 'key2': 1, 'key3': 12.345}")
+ self.validate_identity("SELECT ROW(x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y)")
+ self.validate_identity("SELECT (x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y)")
+ self.validate_identity("SELECT a.x FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a)")
+ self.validate_identity(
+ "SELECT a['x space'] FROM (SELECT {'x space': 1, 'y': 2, 'z': 3} AS a)"
+ )
self.validate_all(
"CREATE TABLE IF NOT EXISTS table (cola INT, colb STRING) USING ICEBERG PARTITIONED BY (colb)",
write={
@@ -229,11 +242,18 @@ class TestDuckDB(Validator):
self.validate_all(
"STRUCT_PACK(x := 1, y := '2')",
write={
- "duckdb": "STRUCT_PACK(x := 1, y := '2')",
+ "duckdb": "{'x': 1, 'y': '2'}",
"spark": "STRUCT(x = 1, y = '2')",
},
)
self.validate_all(
+ "STRUCT_PACK(key1 := 'value1', key2 := 42)",
+ write={
+ "duckdb": "{'key1': 'value1', 'key2': 42}",
+ "spark": "STRUCT(key1 = 'value1', key2 = 42)",
+ },
+ )
+ self.validate_all(
"ARRAY_SORT(x)",
write={
"duckdb": "ARRAY_SORT(x)",
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index d485593..c41e4f7 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -338,6 +338,27 @@ class TestHive(Validator):
)
def test_hive(self):
+ self.validate_identity(
+ "INSERT OVERWRITE TABLE zipcodes PARTITION(state = '0') VALUES (896, 'US', 'TAMPA', 33607)"
+ )
+ self.validate_identity(
+ "INSERT OVERWRITE TABLE zipcodes PARTITION(state = 0) VALUES (896, 'US', 'TAMPA', 33607)"
+ )
+ self.validate_identity(
+ "SELECT a, b, SUM(c) FROM tabl AS t GROUP BY a, b GROUPING SETS ((a, b), a)"
+ )
+ self.validate_identity(
+ "SELECT a, b, SUM(c) FROM tabl AS t GROUP BY a, b GROUPING SETS ((t.a, b), a)"
+ )
+ self.validate_identity(
+ "SELECT a, b, SUM(c) FROM tabl AS t GROUP BY a, FOO(b) GROUPING SETS ((a, FOO(b)), a)"
+ )
+ self.validate_identity(
+ "SELECT key, value, GROUPING__ID, COUNT(*) FROM T1 GROUP BY key, value WITH CUBE"
+ )
+ self.validate_identity(
+ "SELECT key, value, GROUPING__ID, COUNT(*) FROM T1 GROUP BY key, value WITH ROLLUP"
+ )
self.validate_all(
"SELECT A.1a AS b FROM test_a AS A",
write={
@@ -615,3 +636,20 @@ class TestHive(Validator):
"spark": "SELECT * FROM x TABLESAMPLE(1) AS foo",
},
)
+ self.validate_all(
+ "SELECT * FROM x TABLESAMPLE(1) AS foo",
+ read={
+ "presto": "SELECT * FROM x AS foo TABLESAMPLE(1)",
+ },
+ write={
+ "presto": "SELECT * FROM x AS foo TABLESAMPLE(1)",
+ "hive": "SELECT * FROM x TABLESAMPLE(1) AS foo",
+ "spark": "SELECT * FROM x TABLESAMPLE(1) AS foo",
+ },
+ )
+ self.validate_all(
+ "SELECT a, SUM(c) FROM t GROUP BY a, DATE_FORMAT(b, 'yyyy') GROUPING SETS ((a, DATE_FORMAT(b, 'yyyy')), a)",
+ write={
+ "hive": "SELECT a, SUM(c) FROM t GROUP BY a, DATE_FORMAT(CAST(b AS TIMESTAMP), 'yyyy') GROUPING SETS ((a, DATE_FORMAT(CAST(b AS TIMESTAMP), 'yyyy')), a)",
+ },
+ )
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 2351e3b..8a17b78 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -56,7 +56,22 @@ class TestPostgres(Validator):
)
def test_postgres(self):
+ self.validate_all(
+ "x ^ y",
+ write={
+ "": "POWER(x, y)",
+ "postgres": "x ^ y",
+ },
+ )
+ self.validate_all(
+ "x # y",
+ write={
+ "": "x ^ y",
+ "postgres": "x # y",
+ },
+ )
self.validate_identity("SELECT ARRAY[1, 2, 3]")
+ self.validate_identity("SELECT ARRAY(SELECT 1)")
self.validate_identity("SELECT ARRAY_LENGTH(ARRAY[1, 2, 3], 1)")
self.validate_identity("STRING_AGG(x, y)")
self.validate_identity("STRING_AGG(x, ',' ORDER BY y)")
@@ -88,6 +103,14 @@ class TestPostgres(Validator):
self.validate_identity("SELECT e'\\xDEADBEEF'")
self.validate_identity("SELECT CAST(e'\\176' AS BYTEA)")
self.validate_identity("""SELECT * FROM JSON_TO_RECORDSET(z) AS y("rank" INT)""")
+ self.validate_identity(
+ "SELECT SUM(x) OVER a, SUM(y) OVER b FROM c WINDOW a AS (PARTITION BY d), b AS (PARTITION BY e)"
+ )
+ self.validate_identity(
+ "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
+ )
+ self.validate_identity("x ~ 'y'")
+ self.validate_identity("x ~* 'y'")
self.validate_all(
"END WORK AND NO CHAIN",
@@ -118,10 +141,6 @@ class TestPostgres(Validator):
"SELECT to_timestamp(123)::time without time zone",
write={"postgres": "SELECT CAST(TO_TIMESTAMP(123) AS TIME)"},
)
-
- self.validate_identity(
- "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
- )
self.validate_all(
"SELECT SUM(x) OVER (PARTITION BY a ORDER BY d ROWS 1 PRECEDING)",
write={
@@ -283,9 +302,6 @@ class TestPostgres(Validator):
"UPDATE MYTABLE T1 SET T1.COL = 13",
write={"postgres": "UPDATE MYTABLE AS T1 SET T1.COL = 13"},
)
-
- self.validate_identity("x ~ 'y'")
- self.validate_identity("x ~* 'y'")
self.validate_all(
"x !~ 'y'",
write={"postgres": "NOT x ~ 'y'"},
@@ -319,13 +335,20 @@ class TestPostgres(Validator):
"'x' 'y' 'z'",
write={"postgres": "CONCAT('x', 'y', 'z')"},
)
- self.validate_identity("SELECT ARRAY(SELECT 1)")
-
self.validate_all(
"x::cstring",
write={"postgres": "CAST(x AS CSTRING)"},
)
+ self.validate_all(
+ "TRIM(BOTH 'as' FROM 'as string as')",
+ write={
+ "postgres": "TRIM(BOTH 'as' FROM 'as string as')",
+ "spark": "TRIM(BOTH 'as' FROM 'as string as')",
+ },
+ )
- self.validate_identity(
- "SELECT SUM(x) OVER a, SUM(y) OVER b FROM c WINDOW a AS (PARTITION BY d), b AS (PARTITION BY e)"
+ def test_bool_or(self):
+ self.validate_all(
+ "SELECT a, LOGICAL_OR(b) FROM table GROUP BY a",
+ write={"postgres": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
)
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 195e382..5ecd69a 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -174,6 +174,13 @@ class TestPresto(Validator):
"spark": "DATE_ADD(x, 1)",
},
)
+ self.validate_all(
+ "NOW()",
+ write={
+ "presto": "CURRENT_TIMESTAMP()",
+ "hive": "CURRENT_TIMESTAMP()",
+ },
+ )
def test_ddl(self):
self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 7bac166..f3e8e24 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -571,3 +571,35 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS f, LATERA
"spark": "DESCRIBE db.table",
},
)
+
+ def test_match_recognize(self):
+ for row in (
+ "ONE ROW PER MATCH",
+ "ALL ROWS PER MATCH",
+ "ALL ROWS PER MATCH SHOW EMPTY MATCHES",
+ "ALL ROWS PER MATCH OMIT EMPTY MATCHES",
+ "ALL ROWS PER MATCH WITH UNMATCHED ROWS",
+ ):
+ for after in (
+ "AFTER MATCH SKIP",
+ "AFTER MATCH SKIP PAST LAST ROW",
+ "AFTER MATCH SKIP TO NEXT ROW",
+ "AFTER MATCH SKIP TO FIRST x",
+ "AFTER MATCH SKIP TO LAST x",
+ ):
+ self.validate_identity(
+ f"""SELECT
+ *
+FROM x
+MATCH_RECOGNIZE (
+ PARTITION BY a, b
+ ORDER BY
+ x DESC
+ MEASURES y AS b
+ {row}
+ {after}
+ PATTERN (^ S1 S2*? ( {{- S3 -}} S4 )+ | PERMUTE(S1, S2){{1,2}} $)
+ DEFINE x AS y
+)""",
+ pretty=True,
+ )
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index fad858c..02d43aa 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -208,6 +208,13 @@ TBLPROPERTIES (
def test_spark(self):
self.validate_identity("SELECT UNIX_TIMESTAMP()")
+ self.validate_identity("TRIM(' SparkSQL ')")
+ self.validate_identity("TRIM(BOTH 'SL' FROM 'SSparkSQLS')")
+ self.validate_identity("TRIM(LEADING 'SL' FROM 'SSparkSQLS')")
+ self.validate_identity("TRIM(TRAILING 'SL' FROM 'SSparkSQLS')")
+ self.validate_all(
+ "TRIM('SL', 'SSparkSQLS')", write={"spark": "TRIM('SL' FROM 'SSparkSQLS')"}
+ )
self.validate_all(
"ARRAY_SORT(x, (left, right) -> -1)",
write={
@@ -314,5 +321,5 @@ TBLPROPERTIES (
def test_bool_or(self):
self.validate_all(
"SELECT a, LOGICAL_OR(b) FROM table GROUP BY a",
- write={"duckdb": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
+ write={"spark": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
)
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index d52b417..f2830b1 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -99,6 +99,8 @@ STR_POSITION(haystack, needle, pos)
LEVENSHTEIN('gumbo', 'gambol', 2, 1, 1)
SPLIT(SPLIT(referrer, 'utm_source=')[OFFSET(1)], "&")[OFFSET(0)]
x[ORDINAL(1)][SAFE_OFFSET(2)]
+x GLOB '??-*'
+x GLOB y
x LIKE SUBSTR('abc', 1, 1)
x LIKE y
x LIKE a.y
@@ -143,6 +145,10 @@ SET -v
SET x = ';'
COMMIT
USE db
+USE role x
+USE warehouse x
+USE database x
+USE schema x.y
NOT 1
NOT NOT 1
SELECT * FROM test
@@ -479,19 +485,6 @@ CREATE TABLE a.b AS (SELECT 1) UNIQUE PRIMARY INDEX index1 (a) UNIQUE INDEX inde
CREATE TABLE a.b AS (SELECT 1) PRIMARY AMP INDEX index1 (a) UNIQUE INDEX index2 (b)
CREATE TABLE a.b AS SELECT a FROM a.c
CREATE TABLE IF NOT EXISTS x AS SELECT a FROM d
-CREATE TEMPORARY TABLE x AS SELECT a FROM d
-CREATE TEMPORARY TABLE IF NOT EXISTS x AS SELECT a FROM d
-CREATE VIEW x AS SELECT a FROM b
-CREATE VIEW IF NOT EXISTS x AS SELECT a FROM b
-CREATE VIEW z (a, b COMMENT 'b', c COMMENT 'c') AS SELECT a, b, c FROM d
-CREATE VIEW IF NOT EXISTS z (a, b COMMENT 'b', c COMMENT 'c') AS SELECT a, b, c FROM d
-CREATE OR REPLACE VIEW x AS SELECT *
-CREATE OR REPLACE TEMPORARY VIEW x AS SELECT *
-CREATE TEMPORARY VIEW x AS SELECT a FROM d
-CREATE TEMPORARY VIEW IF NOT EXISTS x AS SELECT a FROM d
-CREATE TEMPORARY VIEW x AS WITH y AS (SELECT 1) SELECT * FROM y
-CREATE MATERIALIZED VIEW x.y.z AS SELECT a FROM b
-DROP MATERIALIZED VIEW x.y.z
CREATE TABLE z (a INT, b VARCHAR, c VARCHAR(100), d DECIMAL(5, 3))
CREATE TABLE z (end INT)
CREATE TABLE z (a ARRAY<TEXT>, b MAP<TEXT, DOUBLE>, c DECIMAL(5, 3))
@@ -517,6 +510,34 @@ CREATE TABLE z (a INT UNIQUE AUTO_INCREMENT)
CREATE TABLE z (a INT REFERENCES parent(b, c))
CREATE TABLE z (a INT PRIMARY KEY, b INT REFERENCES foo(id))
CREATE TABLE z (a INT, FOREIGN KEY (a) REFERENCES parent(b, c))
+CREATE TABLE asd AS SELECT asd FROM asd WITH NO DATA
+CREATE TABLE asd AS SELECT asd FROM asd WITH DATA
+CREATE TABLE products (x INT GENERATED BY DEFAULT AS IDENTITY)
+CREATE TABLE products (x INT GENERATED ALWAYS AS IDENTITY)
+CREATE TABLE IF NOT EXISTS customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT BY 1))
+CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 1))
+CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10))
+CREATE TABLE foo (baz_id INT REFERENCES baz(id) DEFERRABLE)
+CREATE TABLE a, FALLBACK, LOG, JOURNAL, CHECKSUM=DEFAULT, DEFAULT MERGEBLOCKRATIO, BLOCKCOMPRESSION=MANUAL (a INT)
+CREATE TABLE a, NO FALLBACK PROTECTION, NO LOG, NO JOURNAL, CHECKSUM=ON, NO MERGEBLOCKRATIO, BLOCKCOMPRESSION=ALWAYS (a INT)
+CREATE TABLE a, WITH JOURNAL TABLE=x.y.z, CHECKSUM=OFF, MERGEBLOCKRATIO=1, DATABLOCKSIZE=10 KBYTES (a INT)
+CREATE TABLE a, BEFORE JOURNAL, AFTER JOURNAL, FREESPACE=1, DEFAULT DATABLOCKSIZE, BLOCKCOMPRESSION=DEFAULT (a INT)
+CREATE TABLE a, DUAL JOURNAL, DUAL AFTER JOURNAL, MERGEBLOCKRATIO=1 PERCENT, DATABLOCKSIZE=10 KILOBYTES (a INT)
+CREATE TABLE a, DUAL BEFORE JOURNAL, LOCAL AFTER JOURNAL, MAXIMUM DATABLOCKSIZE, BLOCKCOMPRESSION=AUTOTEMP(c1 INT) (a INT)
+CREATE SET GLOBAL TEMPORARY TABLE a, NO BEFORE JOURNAL, NO AFTER JOURNAL, MINIMUM DATABLOCKSIZE, BLOCKCOMPRESSION=NEVER (a INT)
+CREATE MULTISET VOLATILE TABLE a, NOT LOCAL AFTER JOURNAL, FREESPACE=1 PERCENT, DATABLOCKSIZE=10 BYTES, WITH NO CONCURRENT ISOLATED LOADING FOR ALL (a INT)
+CREATE TEMPORARY TABLE x AS SELECT a FROM d
+CREATE TEMPORARY TABLE IF NOT EXISTS x AS SELECT a FROM d
+CREATE VIEW x AS SELECT a FROM b
+CREATE VIEW IF NOT EXISTS x AS SELECT a FROM b
+CREATE VIEW z (a, b COMMENT 'b', c COMMENT 'c') AS SELECT a, b, c FROM d
+CREATE VIEW IF NOT EXISTS z (a, b COMMENT 'b', c COMMENT 'c') AS SELECT a, b, c FROM d
+CREATE OR REPLACE VIEW x AS SELECT *
+CREATE OR REPLACE TEMPORARY VIEW x AS SELECT *
+CREATE TEMPORARY VIEW x AS SELECT a FROM d
+CREATE TEMPORARY VIEW IF NOT EXISTS x AS SELECT a FROM d
+CREATE TEMPORARY VIEW x AS WITH y AS (SELECT 1) SELECT * FROM y
+CREATE MATERIALIZED VIEW x.y.z AS SELECT a FROM b
CREATE VIEW z (a, b)
CREATE VIEW z (a, b COMMENT 'b', c COMMENT 'c')
CREATE TEMPORARY FUNCTION f
@@ -527,15 +548,17 @@ CREATE FUNCTION a(b INT, c VARCHAR) AS 'SELECT 1'
CREATE FUNCTION a() LANGUAGE sql
CREATE FUNCTION a() LANGUAGE sql RETURNS INT
CREATE FUNCTION a.b.c()
-DROP FUNCTION a.b.c (INT)
CREATE INDEX abc ON t (a)
CREATE INDEX abc ON t (a, b, b)
CREATE UNIQUE INDEX abc ON t (a, b, b)
CREATE UNIQUE INDEX IF NOT EXISTS my_idx ON tbl (a, b)
CREATE SCHEMA x
CREATE SCHEMA IF NOT EXISTS y
+CREATE PROCEDURE IF NOT EXISTS a.b.c() AS 'DECLARE BEGIN; END'
DESCRIBE x
DROP INDEX a.b.c
+DROP FUNCTION a.b.c (INT)
+DROP MATERIALIZED VIEW x.y.z
CACHE TABLE x
CACHE LAZY TABLE x
CACHE LAZY TABLE x OPTIONS('storageLevel' = 'value')
@@ -545,12 +568,11 @@ CACHE LAZY TABLE x AS WITH a AS (SELECT 1) SELECT a.* FROM a
CACHE TABLE x AS WITH a AS (SELECT 1) SELECT a.* FROM a
CACHE TABLE x AS (SELECT 1 AS y)
CALL catalog.system.iceberg_procedure_name(named_arg_1 => 'arg_1', named_arg_2 => 'arg_2')
-CREATE PROCEDURE IF NOT EXISTS a.b.c() AS 'DECLARE BEGIN; END'
DROP PROCEDURE a.b.c (INT)
INSERT OVERWRITE TABLE a.b PARTITION(ds) SELECT x FROM y
-INSERT OVERWRITE TABLE a.b PARTITION(ds='YYYY-MM-DD') SELECT x FROM y
+INSERT OVERWRITE TABLE a.b PARTITION(ds = 'YYYY-MM-DD') SELECT x FROM y
INSERT OVERWRITE TABLE a.b PARTITION(ds, hour) SELECT x FROM y
-INSERT OVERWRITE TABLE a.b PARTITION(ds='YYYY-MM-DD', hour='hh') SELECT x FROM y
+INSERT OVERWRITE TABLE a.b PARTITION(ds = 'YYYY-MM-DD', hour = 'hh') SELECT x FROM y
ALTER AGGREGATE bla(foo) OWNER TO CURRENT_USER
ALTER RULE foo ON bla RENAME TO baz
ALTER ROLE CURRENT_USER WITH REPLICATION
@@ -594,10 +616,10 @@ INSERT OVERWRITE TABLE a.b IF EXISTS SELECT * FROM y
INSERT OVERWRITE DIRECTORY 'x' SELECT 1
INSERT OVERWRITE LOCAL DIRECTORY 'x' SELECT 1
INSERT OVERWRITE LOCAL DIRECTORY 'x' ROW FORMAT DELIMITED FIELDS TERMINATED BY '1' COLLECTION ITEMS TERMINATED BY '2' MAP KEYS TERMINATED BY '3' LINES TERMINATED BY '4' NULL DEFINED AS '5' SELECT 1
-LOAD DATA INPATH 'x' INTO TABLE y PARTITION(ds='yyyy')
-LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds='yyyy')
-LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds='yyyy') INPUTFORMAT 'y'
-LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds='yyyy') INPUTFORMAT 'y' SERDE 'z'
+LOAD DATA INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy')
+LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy')
+LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy') INPUTFORMAT 'y'
+LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy') INPUTFORMAT 'y' SERDE 'z'
LOAD DATA INPATH 'x' INTO TABLE y INPUTFORMAT 'y' SERDE 'z'
LOAD DATA INPATH 'x' INTO TABLE y.b INPUTFORMAT 'y' SERDE 'z'
SELECT 1 FROM PARQUET_SCAN('/x/y/*') AS y
@@ -658,10 +680,12 @@ ALTER TABLE integers ALTER COLUMN i SET DEFAULT 10
ALTER TABLE integers ALTER COLUMN i DROP DEFAULT
ALTER TABLE mydataset.mytable DROP COLUMN A, DROP COLUMN IF EXISTS B
ALTER TABLE mydataset.mytable ADD COLUMN A TEXT, ADD COLUMN IF NOT EXISTS B INT
+ALTER TABLE orders DROP PARTITION(dt = '2014-05-14', country = 'IN')
+ALTER TABLE orders DROP IF EXISTS PARTITION(dt = '2014-05-14', country = 'IN')
+ALTER TABLE orders DROP PARTITION(dt = '2014-05-14', country = 'IN'), PARTITION(dt = '2014-05-15', country = 'IN')
+ALTER TABLE mydataset.mytable DELETE WHERE x = 1
SELECT div.a FROM test_table AS div
WITH view AS (SELECT 1 AS x) SELECT * FROM view
-CREATE TABLE asd AS SELECT asd FROM asd WITH NO DATA
-CREATE TABLE asd AS SELECT asd FROM asd WITH DATA
ARRAY<STRUCT<INT, DOUBLE, ARRAY<INT>>>
ARRAY<INT>[1, 2, 3]
ARRAY<INT>[]
@@ -672,11 +696,6 @@ STRUCT<INT>(5)
STRUCT<DATE>("2011-05-05")
STRUCT<x INT, y TEXT>(1, t.str_col)
SELECT CAST(NULL AS ARRAY<INT>) IS NULL AS array_is_null
-CREATE TABLE products (x INT GENERATED BY DEFAULT AS IDENTITY)
-CREATE TABLE products (x INT GENERATED ALWAYS AS IDENTITY)
-CREATE TABLE IF NOT EXISTS customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT BY 1))
-CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 1))
-CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10))
ALTER TABLE "schema"."tablename" ADD CONSTRAINT "CHK_Name" CHECK (NOT "IdDwh" IS NULL AND "IdDwh" <> (0))
ALTER TABLE persons ADD CONSTRAINT persons_pk PRIMARY KEY (first_name, last_name)
ALTER TABLE pets ADD CONSTRAINT pets_persons_fk FOREIGN KEY (owner_first_name, owner_last_name) REFERENCES persons
@@ -688,6 +707,5 @@ ALTER TABLE baa ADD CONSTRAINT boo PRIMARY KEY (x, y) NOT ENFORCED DEFERRABLE IN
ALTER TABLE baa ADD CONSTRAINT boo FOREIGN KEY (x, y) REFERENCES persons ON UPDATE NO ACTION ON DELETE NO ACTION MATCH FULL
ALTER TABLE a ADD PRIMARY KEY (x, y) NOT ENFORCED
ALTER TABLE a ADD FOREIGN KEY (x, y) REFERENCES bla
-CREATE TABLE foo (baz_id INT REFERENCES baz(id) DEFERRABLE)
SELECT end FROM a
SELECT id FROM b.a AS a QUALIFY ROW_NUMBER() OVER (PARTITION BY br ORDER BY sadf DESC) = 1
diff --git a/tests/test_tokens.py b/tests/test_tokens.py
index 1376849..2c3b874 100644
--- a/tests/test_tokens.py
+++ b/tests/test_tokens.py
@@ -40,7 +40,8 @@ class TestTokens(unittest.TestCase):
(TokenType.SELECT, "SELECT"),
(TokenType.BLOCK_START, "{{"),
(TokenType.VAR, "x"),
- (TokenType.BLOCK_END, "}}"),
+ (TokenType.R_BRACE, "}"),
+ (TokenType.R_BRACE, "}"),
(TokenType.COMMA, ","),
(TokenType.BLOCK_START, "{{-"),
(TokenType.VAR, "x"),
@@ -55,7 +56,8 @@ class TestTokens(unittest.TestCase):
(TokenType.VAR, "a"),
(TokenType.BLOCK_START, "{{+"),
(TokenType.VAR, "b"),
- (TokenType.BLOCK_END, "}}"),
+ (TokenType.R_BRACE, "}"),
+ (TokenType.R_BRACE, "}"),
(TokenType.BLOCK_START, "{%"),
(TokenType.VAR, "endfor"),
(TokenType.BLOCK_END, "%}"),
diff --git a/tests/test_transforms.py b/tests/test_transforms.py
index cc9af7e..76d63b6 100644
--- a/tests/test_transforms.py
+++ b/tests/test_transforms.py
@@ -9,6 +9,8 @@ from sqlglot.transforms import (
class TestTime(unittest.TestCase):
+ maxDiff = None
+
def validate(self, transform, sql, target):
with self.subTest(sql):
self.assertEqual(parse_one(sql).transform(transform).sql(), target)
@@ -17,7 +19,7 @@ class TestTime(unittest.TestCase):
self.validate(
unalias_group,
"SELECT a, b AS b, c AS c, 4 FROM x GROUP BY a, b, x.c, 4",
- "SELECT a, b AS b, c AS c, 4 FROM x GROUP BY a, b, x.c, 4",
+ "SELECT a, b AS b, c AS c, 4 FROM x GROUP BY a, 2, x.c, 4",
)
self.validate(
unalias_group,
@@ -37,7 +39,12 @@ class TestTime(unittest.TestCase):
self.validate(
unalias_group,
"SELECT the_date AS the_date, COUNT(*) AS the_count FROM x GROUP BY the_date",
- "SELECT the_date AS the_date, COUNT(*) AS the_count FROM x GROUP BY the_date",
+ "SELECT the_date AS the_date, COUNT(*) AS the_count FROM x GROUP BY 1",
+ )
+ self.validate(
+ unalias_group,
+ "SELECT a AS a FROM x GROUP BY DATE(a)",
+ "SELECT a AS a FROM x GROUP BY DATE(a)",
)
def test_eliminate_distinct_on(self):
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-01 23:00:48 +0000