summaryrefslogtreecommitdiffstats
path: root/tests
diff options
context:
space:
mode:
Diffstat (limited to 'tests')
-rw-r--r--tests/dialects/test_athena.py60
-rw-r--r--tests/dialects/test_bigquery.py27
-rw-r--r--tests/dialects/test_clickhouse.py20
-rw-r--r--tests/dialects/test_databricks.py6
-rw-r--r--tests/dialects/test_dialect.py4
-rw-r--r--tests/dialects/test_duckdb.py31
-rw-r--r--tests/dialects/test_mysql.py15
-rw-r--r--tests/dialects/test_oracle.py18
-rw-r--r--tests/dialects/test_postgres.py5
-rw-r--r--tests/dialects/test_presto.py21
-rw-r--r--tests/dialects/test_redshift.py33
-rw-r--r--tests/dialects/test_snowflake.py19
-rw-r--r--tests/dialects/test_starrocks.py6
-rw-r--r--tests/dialects/test_tsql.py7
-rw-r--r--tests/fixtures/identity.sql7
-rw-r--r--tests/fixtures/optimizer/simplify.sql3
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql68
-rw-r--r--tests/test_build.py10
-rw-r--r--tests/test_diff.py58
-rw-r--r--tests/test_expressions.py2
-rw-r--r--tests/test_transpile.py1
21 files changed, 341 insertions, 80 deletions
diff --git a/tests/dialects/test_athena.py b/tests/dialects/test_athena.py
index bf54914..ca91d4a 100644
--- a/tests/dialects/test_athena.py
+++ b/tests/dialects/test_athena.py
@@ -1,3 +1,4 @@
+from sqlglot import exp
from tests.dialects.test_dialect import Validator
@@ -68,6 +69,23 @@ class TestAthena(Validator):
"CREATE TABLE foo AS WITH foo AS (SELECT a, b FROM bar) SELECT * FROM foo"
)
+ # ALTER TABLE ADD COLUMN not supported, it needs to be generated as ALTER TABLE ADD COLUMNS
+ self.validate_identity(
+ "ALTER TABLE `foo`.`bar` ADD COLUMN `end_ts` BIGINT",
+ write_sql="ALTER TABLE `foo`.`bar` ADD COLUMNS (`end_ts` BIGINT)",
+ )
+
+ def test_dml(self):
+ self.validate_all(
+ "SELECT CAST(ds AS VARCHAR) AS ds FROM (VALUES ('2022-01-01')) AS t(ds)",
+ read={"": "SELECT CAST(ds AS STRING) AS ds FROM (VALUES ('2022-01-01')) AS t(ds)"},
+ write={
+ "hive": "SELECT CAST(ds AS STRING) AS ds FROM (VALUES ('2022-01-01')) AS t(ds)",
+ "trino": "SELECT CAST(ds AS VARCHAR) AS ds FROM (VALUES ('2022-01-01')) AS t(ds)",
+ "athena": "SELECT CAST(ds AS VARCHAR) AS ds FROM (VALUES ('2022-01-01')) AS t(ds)",
+ },
+ )
+
def test_ddl_quoting(self):
self.validate_identity("CREATE SCHEMA `foo`")
self.validate_identity("CREATE SCHEMA foo")
@@ -111,6 +129,10 @@ class TestAthena(Validator):
'CREATE VIEW `foo` AS SELECT "id" FROM `tbl`',
write_sql='CREATE VIEW "foo" AS SELECT "id" FROM "tbl"',
)
+ self.validate_identity(
+ "DROP VIEW IF EXISTS `foo`.`bar`",
+ write_sql='DROP VIEW IF EXISTS "foo"."bar"',
+ )
self.validate_identity(
'ALTER TABLE "foo" ADD COLUMNS ("id" STRING)',
@@ -128,6 +150,8 @@ class TestAthena(Validator):
write_sql='CREATE TABLE "foo" AS WITH "foo" AS (SELECT "a", "b" FROM "bar") SELECT * FROM "foo"',
)
+ self.validate_identity("DESCRIBE foo.bar", write_sql="DESCRIBE `foo`.`bar`", identify=True)
+
def test_dml_quoting(self):
self.validate_identity("SELECT a AS foo FROM tbl")
self.validate_identity('SELECT "a" AS "foo" FROM "tbl"')
@@ -167,3 +191,39 @@ class TestAthena(Validator):
write_sql='WITH "foo" AS (SELECT "a", "b" FROM "bar") SELECT * FROM "foo"',
identify=True,
)
+
+ def test_ctas(self):
+ # Hive tables use 'external_location' to specify the table location, Iceberg tables use 'location' to specify the table location
+ # The 'table_type' property is used to determine if it's a Hive or an Iceberg table
+ # ref: https://docs.aws.amazon.com/athena/latest/ug/create-table-as.html#ctas-table-properties
+ ctas_hive = exp.Create(
+ this=exp.to_table("foo.bar"),
+ kind="TABLE",
+ properties=exp.Properties(
+ expressions=[
+ exp.FileFormatProperty(this=exp.Literal.string("parquet")),
+ exp.LocationProperty(this=exp.Literal.string("s3://foo")),
+ ]
+ ),
+ expression=exp.select("1"),
+ )
+ self.assertEqual(
+ ctas_hive.sql(dialect=self.dialect, identify=True),
+ "CREATE TABLE \"foo\".\"bar\" WITH (format='parquet', external_location='s3://foo') AS SELECT 1",
+ )
+
+ ctas_iceberg = exp.Create(
+ this=exp.to_table("foo.bar"),
+ kind="TABLE",
+ properties=exp.Properties(
+ expressions=[
+ exp.Property(this=exp.var("table_type"), value=exp.Literal.string("iceberg")),
+ exp.LocationProperty(this=exp.Literal.string("s3://foo")),
+ ]
+ ),
+ expression=exp.select("1"),
+ )
+ self.assertEqual(
+ ctas_iceberg.sql(dialect=self.dialect, identify=True),
+ "CREATE TABLE \"foo\".\"bar\" WITH (table_type='iceberg', location='s3://foo') AS SELECT 1",
+ )
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index b883896..e2adfea 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -22,6 +22,8 @@ class TestBigQuery(Validator):
maxDiff = None
def test_bigquery(self):
+ self.validate_identity("REGEXP_EXTRACT(x, '(?<)')")
+
self.validate_all(
"EXTRACT(HOUR FROM DATETIME(2008, 12, 25, 15, 30, 00))",
write={
@@ -1502,6 +1504,8 @@ WHERE
},
)
+ self.validate_identity("SELECT * FROM a-b c", "SELECT * FROM a-b AS c")
+
def test_errors(self):
with self.assertRaises(TokenError):
transpile("'\\'", read="bigquery")
@@ -1958,3 +1962,26 @@ OPTIONS (
"duckdb": "WITH Races AS (SELECT '800M' AS race) SELECT race, participant FROM Races AS r CROSS JOIN (SELECT UNNEST([{'name': 'Rudisha', 'laps': [23.4, 26.3, 26.4, 26.1]}], max_depth => 2)) AS participant",
},
)
+
+ def test_range_type(self):
+ for type, value in (
+ ("RANGE<DATE>", "'[2020-01-01, 2020-12-31)'"),
+ ("RANGE<DATE>", "'[UNBOUNDED, 2020-12-31)'"),
+ ("RANGE<DATETIME>", "'[2020-01-01 12:00:00, 2020-12-31 12:00:00)'"),
+ ("RANGE<TIMESTAMP>", "'[2020-10-01 12:00:00+08, 2020-12-31 12:00:00+08)'"),
+ ):
+ with self.subTest(f"Testing BigQuery's RANGE<T> type: {type} {value}"):
+ self.validate_identity(f"SELECT {type} {value}", f"SELECT CAST({value} AS {type})")
+
+ self.assertEqual(self.parse_one(type), exp.DataType.build(type, dialect="bigquery"))
+
+ self.validate_identity(
+ "SELECT RANGE(CAST('2022-12-01' AS DATE), CAST('2022-12-31' AS DATE))"
+ )
+ self.validate_identity("SELECT RANGE(NULL, CAST('2022-12-31' AS DATE))")
+ self.validate_identity(
+ "SELECT RANGE(CAST('2022-10-01 14:53:27' AS DATETIME), CAST('2022-10-01 16:00:00' AS DATETIME))"
+ )
+ self.validate_identity(
+ "SELECT RANGE(CAST('2022-10-01 14:53:27 America/Los_Angeles' AS TIMESTAMP), CAST('2022-10-01 16:00:00 America/Los_Angeles' AS TIMESTAMP))"
+ )
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index d203141..b4fc587 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -139,10 +139,10 @@ class TestClickhouse(Validator):
"CREATE MATERIALIZED VIEW test_view ON CLUSTER cl1 (id UInt8) ENGINE=AggregatingMergeTree() ORDER BY tuple() AS SELECT * FROM test_data"
)
self.validate_identity(
- "CREATE MATERIALIZED VIEW test_view ON CLUSTER cl1 (id UInt8) TO table1 AS SELECT * FROM test_data"
+ "CREATE MATERIALIZED VIEW test_view ON CLUSTER cl1 TO table1 AS SELECT * FROM test_data"
)
self.validate_identity(
- "CREATE MATERIALIZED VIEW test_view (id UInt8) TO db.table1 AS SELECT * FROM test_data"
+ "CREATE MATERIALIZED VIEW test_view TO db.table1 (id UInt8) AS SELECT * FROM test_data"
)
self.validate_identity(
"CREATE TABLE t (foo String CODEC(LZ4HC(9), ZSTD, DELTA), size String ALIAS formatReadableSize(size_bytes), INDEX idx1 a TYPE bloom_filter(0.001) GRANULARITY 1, INDEX idx2 a TYPE set(100) GRANULARITY 2, INDEX idx3 a TYPE minmax GRANULARITY 3)"
@@ -519,6 +519,17 @@ class TestClickhouse(Validator):
self.validate_identity("SELECT TRIM(LEADING '(' FROM '( Hello, world! )')")
self.validate_identity("current_timestamp").assert_is(exp.Column)
+ self.validate_identity("SELECT * APPLY(sum) FROM columns_transformers")
+ self.validate_identity("SELECT COLUMNS('[jk]') APPLY(toString) FROM columns_transformers")
+ self.validate_identity(
+ "SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) FROM columns_transformers"
+ )
+ self.validate_identity("SELECT * APPLY(sum), COLUMNS('col') APPLY(sum) APPLY(avg) FROM t")
+ self.validate_identity(
+ "SELECT * FROM ABC WHERE hasAny(COLUMNS('.*field') APPLY(toUInt64) APPLY(to), (SELECT groupUniqArray(toUInt64(field))))"
+ )
+ self.validate_identity("SELECT col apply", "SELECT col AS apply")
+
def test_clickhouse_values(self):
values = exp.select("*").from_(
exp.values([exp.tuple_(1, 2, 3)], alias="subq", columns=["a", "b", "c"])
@@ -670,6 +681,7 @@ class TestClickhouse(Validator):
"CREATE TABLE foo ENGINE=Memory AS (SELECT * FROM db.other_table) COMMENT 'foo'",
)
+ self.validate_identity("CREATE MATERIALIZED VIEW a.b TO a.c (c Int32) AS SELECT * FROM a.d")
self.validate_identity("""CREATE TABLE ip_data (ip4 IPv4, ip6 IPv6) ENGINE=TinyLog()""")
self.validate_identity("""CREATE TABLE dates (dt1 Date32) ENGINE=TinyLog()""")
self.validate_identity("CREATE TABLE named_tuples (a Tuple(select String, i Int64))")
@@ -1084,3 +1096,7 @@ LIFETIME(MIN 0 MAX 0)""",
self.assertEqual(
convert(date(2020, 1, 1)).sql(dialect=self.dialect), "toDate('2020-01-01')"
)
+
+ def test_grant(self):
+ self.validate_identity("GRANT SELECT(x, y) ON db.table TO john WITH GRANT OPTION")
+ self.validate_identity("GRANT INSERT(x, y) ON db.table TO john")
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index c2965e8..65e8d5d 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -271,3 +271,9 @@ class TestDatabricks(Validator):
self.validate_identity(
"CREATE OR REFRESH STREAMING TABLE csv_data (id INT, ts TIMESTAMP, event STRING) AS SELECT * FROM STREAM READ_FILES('s3://bucket/path', format => 'csv', schema => 'id int, ts timestamp, event string')"
)
+
+ def test_grant(self):
+ self.validate_identity("GRANT CREATE ON SCHEMA my_schema TO `alf@melmak.et`")
+ self.validate_identity("GRANT SELECT ON TABLE sample_data TO `alf@melmak.et`")
+ self.validate_identity("GRANT ALL PRIVILEGES ON TABLE forecasts TO finance")
+ self.validate_identity("GRANT SELECT ON TABLE t TO `fab9e00e-ca35-11ec-9d64-0242ac120002`")
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 19effb3..96ce600 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -2221,9 +2221,9 @@ SELECT
self.validate_all(
"SUBSTR('123456', 2, 3)",
write={
- "bigquery": "SUBSTR('123456', 2, 3)",
+ "bigquery": "SUBSTRING('123456', 2, 3)",
"oracle": "SUBSTR('123456', 2, 3)",
- "postgres": "SUBSTR('123456', 2, 3)",
+ "postgres": "SUBSTRING('123456' FROM 2 FOR 3)",
},
)
self.validate_all(
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 84c82f1..e4788ec 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -256,6 +256,7 @@ class TestDuckDB(Validator):
parse_one("a // b", read="duckdb").assert_is(exp.IntDiv).sql(dialect="duckdb"), "a // b"
)
+ self.validate_identity("CREATE TABLE tbl1 (u UNION(num INT, str TEXT))")
self.validate_identity("INSERT INTO x BY NAME SELECT 1 AS y")
self.validate_identity("SELECT 1 AS x UNION ALL BY NAME SELECT 2 AS x")
self.validate_identity("SELECT SUM(x) FILTER (x = 1)", "SELECT SUM(x) FILTER(WHERE x = 1)")
@@ -294,6 +295,9 @@ class TestDuckDB(Validator):
self.validate_identity("SUMMARIZE tbl").assert_is(exp.Summarize)
self.validate_identity("SUMMARIZE SELECT * FROM tbl").assert_is(exp.Summarize)
self.validate_identity("CREATE TABLE tbl_summary AS SELECT * FROM (SUMMARIZE tbl)")
+ self.validate_identity("UNION_VALUE(k1 := 1)").find(exp.PropertyEQ).this.assert_is(
+ exp.Identifier
+ )
self.validate_identity(
"SELECT species, island, COUNT(*) FROM t GROUP BY GROUPING SETS (species), GROUPING SETS (island)"
)
@@ -310,6 +314,13 @@ class TestDuckDB(Validator):
"SELECT * FROM x LEFT JOIN UNNEST(y)", "SELECT * FROM x LEFT JOIN UNNEST(y) ON TRUE"
)
self.validate_identity(
+ """SELECT '{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }' ->> ['$.family', '$.species']""",
+ )
+ self.validate_identity(
+ """SELECT JSON_EXTRACT_STRING('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }', ['$.family', '$.species'])""",
+ """SELECT '{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }' ->> ['$.family', '$.species']""",
+ )
+ self.validate_identity(
"SELECT col FROM t WHERE JSON_EXTRACT_STRING(col, '$.id') NOT IN ('b')",
"SELECT col FROM t WHERE NOT (col ->> '$.id') IN ('b')",
)
@@ -524,8 +535,8 @@ class TestDuckDB(Validator):
write={
"duckdb": "STR_SPLIT(x, 'a')",
"presto": "SPLIT(x, 'a')",
- "hive": "SPLIT(x, CONCAT('\\\\Q', 'a'))",
- "spark": "SPLIT(x, CONCAT('\\\\Q', 'a'))",
+ "hive": "SPLIT(x, CONCAT('\\\\Q', 'a', '\\\\E'))",
+ "spark": "SPLIT(x, CONCAT('\\\\Q', 'a', '\\\\E'))",
},
)
self.validate_all(
@@ -533,8 +544,8 @@ class TestDuckDB(Validator):
write={
"duckdb": "STR_SPLIT(x, 'a')",
"presto": "SPLIT(x, 'a')",
- "hive": "SPLIT(x, CONCAT('\\\\Q', 'a'))",
- "spark": "SPLIT(x, CONCAT('\\\\Q', 'a'))",
+ "hive": "SPLIT(x, CONCAT('\\\\Q', 'a', '\\\\E'))",
+ "spark": "SPLIT(x, CONCAT('\\\\Q', 'a', '\\\\E'))",
},
)
self.validate_all(
@@ -835,6 +846,18 @@ class TestDuckDB(Validator):
"SELECT id, STRUCT_PACK(*COLUMNS('m\\d')) AS measurements FROM many_measurements",
"""SELECT id, {'_0': *COLUMNS('m\\d')} AS measurements FROM many_measurements""",
)
+ self.validate_identity("SELECT COLUMNS(c -> c LIKE '%num%') FROM numbers")
+ self.validate_identity(
+ "SELECT MIN(COLUMNS(* REPLACE (number + id AS number))), COUNT(COLUMNS(* EXCLUDE (number))) FROM numbers"
+ )
+ self.validate_identity("SELECT COLUMNS(*) + COLUMNS(*) FROM numbers")
+ self.validate_identity("SELECT COLUMNS('(id|numbers?)') FROM numbers")
+ self.validate_identity(
+ "SELECT COALESCE(COLUMNS(['a', 'b', 'c'])) AS result FROM (SELECT NULL AS a, 42 AS b, TRUE AS c)"
+ )
+ self.validate_identity(
+ "SELECT COALESCE(*COLUMNS(['a', 'b', 'c'])) AS result FROM (SELECT NULL AS a, 42 AS b, TRUE AS c)"
+ )
def test_array_index(self):
with self.assertLogs(helper_logger) as cm:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 2f1f7ba..835ee7c 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -1278,3 +1278,18 @@ COMMENT='客户账户表'"""
self.validate_identity(
f"""SELECT JSON_VALUE({json_doc}, '$.price' RETURNING DECIMAL(4, 2) {on_option} ON EMPTY {on_option} ON ERROR) AS price"""
)
+
+ def test_grant(self):
+ grant_cmds = [
+ "GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost'",
+ "GRANT SELECT ON world.* TO 'role3'",
+ "GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'",
+ "GRANT INSERT ON `d%`.* TO u",
+ "GRANT ALL ON test.* TO ''@'localhost'",
+ "GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost'",
+ "GRANT SELECT, INSERT, UPDATE ON *.* TO u2",
+ ]
+
+ for sql in grant_cmds:
+ with self.subTest(f"Testing MySQL's GRANT command statement: {sql}"):
+ self.validate_identity(sql, check_command_warning=True)
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 28a8da9..8675086 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -546,3 +546,21 @@ WHERE
self.validate_identity(
f"SELECT * FROM t WHERE JSON_EXISTS(name{format_json}, '$[1].middle'{passing}{on_cond})"
)
+
+ def test_grant(self):
+ grant_cmds = [
+ "GRANT purchases_reader_role TO george, maria",
+ "GRANT USAGE ON TYPE price TO finance_role",
+ "GRANT USAGE ON DERBY AGGREGATE types.maxPrice TO sales_role",
+ ]
+
+ for sql in grant_cmds:
+ with self.subTest(f"Testing Oracles's GRANT command statement: {sql}"):
+ self.validate_identity(sql, check_command_warning=True)
+
+ self.validate_identity("GRANT SELECT ON TABLE t TO maria, harry")
+ self.validate_identity("GRANT SELECT ON TABLE s.v TO PUBLIC")
+ self.validate_identity("GRANT SELECT ON TABLE t TO purchases_reader_role")
+ self.validate_identity("GRANT UPDATE, TRIGGER ON TABLE t TO anita, zhi")
+ self.validate_identity("GRANT EXECUTE ON PROCEDURE p TO george")
+ self.validate_identity("GRANT USAGE ON SEQUENCE order_id TO sales_role")
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index ab06683..63266a5 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -797,6 +797,11 @@ class TestPostgres(Validator):
self.validate_identity(
"MERGE INTO target_table USING source_table AS source ON target.id = source.id WHEN MATCHED THEN DO NOTHING WHEN NOT MATCHED THEN DO NOTHING RETURNING MERGE_ACTION(), *"
)
+ self.validate_identity(
+ "SELECT 1 FROM ((VALUES (1)) AS vals(id) LEFT OUTER JOIN tbl ON vals.id = tbl.id)"
+ )
+ self.validate_identity("SELECT OVERLAY(a PLACING b FROM 1)")
+ self.validate_identity("SELECT OVERLAY(a PLACING b FROM 1 FOR 1)")
def test_ddl(self):
# Checks that user-defined types are parsed into DataType instead of Identifier
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 9c61f62..3d5fbfe 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -158,8 +158,8 @@ class TestPresto(Validator):
write={
"duckdb": "STR_SPLIT(x, 'a.')",
"presto": "SPLIT(x, 'a.')",
- "hive": "SPLIT(x, CONCAT('\\\\Q', 'a.'))",
- "spark": "SPLIT(x, CONCAT('\\\\Q', 'a.'))",
+ "hive": "SPLIT(x, CONCAT('\\\\Q', 'a.', '\\\\E'))",
+ "spark": "SPLIT(x, CONCAT('\\\\Q', 'a.', '\\\\E'))",
},
)
self.validate_all(
@@ -276,10 +276,19 @@ class TestPresto(Validator):
self.validate_all(
"DATE_PARSE(SUBSTR(x, 1, 10), '%Y-%m-%d')",
write={
- "duckdb": "STRPTIME(SUBSTR(x, 1, 10), '%Y-%m-%d')",
- "presto": "DATE_PARSE(SUBSTR(x, 1, 10), '%Y-%m-%d')",
- "hive": "CAST(SUBSTR(x, 1, 10) AS TIMESTAMP)",
- "spark": "TO_TIMESTAMP(SUBSTR(x, 1, 10), 'yyyy-MM-dd')",
+ "duckdb": "STRPTIME(SUBSTRING(x, 1, 10), '%Y-%m-%d')",
+ "presto": "DATE_PARSE(SUBSTRING(x, 1, 10), '%Y-%m-%d')",
+ "hive": "CAST(SUBSTRING(x, 1, 10) AS TIMESTAMP)",
+ "spark": "TO_TIMESTAMP(SUBSTRING(x, 1, 10), 'yyyy-MM-dd')",
+ },
+ )
+ self.validate_all(
+ "DATE_PARSE(SUBSTRING(x, 1, 10), '%Y-%m-%d')",
+ write={
+ "duckdb": "STRPTIME(SUBSTRING(x, 1, 10), '%Y-%m-%d')",
+ "presto": "DATE_PARSE(SUBSTRING(x, 1, 10), '%Y-%m-%d')",
+ "hive": "CAST(SUBSTRING(x, 1, 10) AS TIMESTAMP)",
+ "spark": "TO_TIMESTAMP(SUBSTRING(x, 1, 10), 'yyyy-MM-dd')",
},
)
self.validate_all(
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index c13b61a..6f561da 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -626,3 +626,36 @@ FROM (
"TIME_TO_STR(a, '%Y-%m-%d %H:%M:%S.%f')",
write={"redshift": "TO_CHAR(a, 'YYYY-MM-DD HH24:MI:SS.US')"},
)
+
+ def test_grant(self):
+ grant_cmds = [
+ "GRANT SELECT ON ALL TABLES IN SCHEMA qa_tickit TO fred",
+ "GRANT USAGE ON DATASHARE salesshare TO NAMESPACE '13b8833d-17c6-4f16-8fe4-1a018f5ed00d'",
+ "GRANT USAGE FOR SCHEMAS IN DATABASE Sales_db TO ROLE Sales",
+ "GRANT EXECUTE FOR FUNCTIONS IN SCHEMA Sales_schema TO bob",
+ "GRANT SELECT FOR TABLES IN DATABASE Sales_db TO alice WITH GRANT OPTION",
+ "GRANT ALL FOR TABLES IN SCHEMA ShareSchema DATABASE ShareDb TO ROLE Sales",
+ "GRANT ASSUMEROLE ON 'arn:aws:iam::123456789012:role/Redshift-Exfunc' TO reg_user1 FOR EXTERNAL FUNCTION",
+ "GRANT ROLE sample_role1 TO ROLE sample_role2",
+ ]
+
+ for sql in grant_cmds:
+ with self.subTest(f"Testing Redshift's GRANT command statement: {sql}"):
+ self.validate_identity(sql, check_command_warning=True)
+
+ self.validate_identity("GRANT SELECT ON TABLE sales TO fred")
+ self.validate_identity("GRANT ALL ON SCHEMA qa_tickit TO GROUP qa_users")
+ self.validate_identity("GRANT ALL ON TABLE qa_tickit.sales TO GROUP qa_users")
+ self.validate_identity(
+ "GRANT ALL ON TABLE qa_tickit.sales TO GROUP qa_users, GROUP ro_users"
+ )
+ self.validate_identity("GRANT ALL ON view_date TO view_user")
+ self.validate_identity(
+ "GRANT SELECT(cust_name, cust_phone), UPDATE(cust_contact_preference) ON cust_profile TO GROUP sales_group"
+ )
+ self.validate_identity(
+ "GRANT ALL(cust_name, cust_phone, cust_contact_preference) ON cust_profile TO GROUP sales_admin"
+ )
+ self.validate_identity("GRANT USAGE ON DATABASE sales_db TO Bob")
+ self.validate_identity("GRANT USAGE ON SCHEMA sales_schema TO ROLE Analyst_role")
+ self.validate_identity("GRANT SELECT ON sales_db.sales_schema.tickit_sales_redshift TO Bob")
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 40413c8..6cde86b 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -755,6 +755,8 @@ WHERE
write={
"spark": "SELECT COLLECT_LIST(DISTINCT a)",
"snowflake": "SELECT ARRAY_AGG(DISTINCT a)",
+ "duckdb": "SELECT ARRAY_AGG(DISTINCT a) FILTER(WHERE a IS NOT NULL)",
+ "presto": "SELECT ARRAY_AGG(DISTINCT a) FILTER(WHERE a IS NOT NULL)",
},
)
self.validate_all(
@@ -2175,3 +2177,20 @@ SINGLE = TRUE""",
"""SELECT 1 FROM some_table CHANGES (INFORMATION => APPEND_ONLY) AT (TIMESTAMP => TO_TIMESTAMP_TZ('2024-07-01 00:00:00+00:00')) END (TIMESTAMP => TO_TIMESTAMP_TZ('2024-07-01 14:28:59.999999+00:00'))""",
"""SELECT 1 FROM some_table CHANGES (INFORMATION => APPEND_ONLY) AT (TIMESTAMP => CAST('2024-07-01 00:00:00+00:00' AS TIMESTAMPTZ)) END (TIMESTAMP => CAST('2024-07-01 14:28:59.999999+00:00' AS TIMESTAMPTZ))""",
)
+
+ def test_grant(self):
+ grant_cmds = [
+ "GRANT SELECT ON FUTURE TABLES IN DATABASE d1 TO ROLE r1",
+ "GRANT INSERT, DELETE ON FUTURE TABLES IN SCHEMA d1.s1 TO ROLE r2",
+ "GRANT SELECT ON ALL TABLES IN SCHEMA mydb.myschema to ROLE analyst",
+ "GRANT SELECT, INSERT ON FUTURE TABLES IN SCHEMA mydb.myschema TO ROLE role1",
+ "GRANT CREATE MATERIALIZED VIEW ON SCHEMA mydb.myschema TO DATABASE ROLE mydb.dr1",
+ ]
+
+ for sql in grant_cmds:
+ with self.subTest(f"Testing Snowflake's GRANT command statement: {sql}"):
+ self.validate_identity(sql, check_command_warning=True)
+
+ self.validate_identity(
+ "GRANT ALL PRIVILEGES ON FUNCTION mydb.myschema.ADD5(number) TO ROLE analyst"
+ )
diff --git a/tests/dialects/test_starrocks.py b/tests/dialects/test_starrocks.py
index ee4dc90..44c54a6 100644
--- a/tests/dialects/test_starrocks.py
+++ b/tests/dialects/test_starrocks.py
@@ -85,8 +85,8 @@ class TestStarrocks(Validator):
r"""SELECT id, t.type, t.scores FROM example_table, unnest(split(type, ";"), scores) AS t(type,scores)""",
write={
"postgres": "SELECT id, t.type, t.scores FROM example_table, UNNEST(SPLIT(type, ';'), scores) AS t(type, scores)",
- "spark": r"""SELECT id, t.type, t.scores FROM example_table LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';')), scores)) t AS type, scores""",
- "databricks": r"""SELECT id, t.type, t.scores FROM example_table LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';')), scores)) t AS type, scores""",
+ "spark": r"""SELECT id, t.type, t.scores FROM example_table LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';', '\\E')), scores)) t AS type, scores""",
+ "databricks": r"""SELECT id, t.type, t.scores FROM example_table LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';', '\\E')), scores)) t AS type, scores""",
"starrocks": r"""SELECT id, t.type, t.scores FROM example_table, UNNEST(SPLIT(type, ';'), scores) AS t(type, scores)""",
"hive": UnsupportedError,
},
@@ -95,7 +95,7 @@ class TestStarrocks(Validator):
self.validate_all(
r"""SELECT id, t.type, t.scores FROM example_table_2 CROSS JOIN LATERAL unnest(split(type, ";"), scores) AS t(type,scores)""",
write={
- "spark": r"""SELECT id, t.type, t.scores FROM example_table_2 LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';')), scores)) t AS type, scores""",
+ "spark": r"""SELECT id, t.type, t.scores FROM example_table_2 LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';', '\\E')), scores)) t AS type, scores""",
"starrocks": r"""SELECT id, t.type, t.scores FROM example_table_2 CROSS JOIN LATERAL UNNEST(SPLIT(type, ';'), scores) AS t(type, scores)""",
"hive": UnsupportedError,
},
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 7114750..453cd5a 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -1998,3 +1998,10 @@ FROM OPENJSON(@json) WITH (
"tsql": "SELECT COUNT(1) FROM x",
},
)
+
+ def test_grant(self):
+ self.validate_identity("GRANT EXECUTE ON TestProc TO User2")
+ self.validate_identity("GRANT EXECUTE ON TestProc TO TesterRole WITH GRANT OPTION")
+ self.validate_identity(
+ "GRANT EXECUTE ON TestProc TO User2 AS TesterRole", check_command_warning=True
+ )
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 013eed8..bed2502 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -131,7 +131,7 @@ x[ORDINAL(1)][SAFE_OFFSET(2)]
x GLOB '??-*'
x GLOB y
ILIKE(x, 'z')
-x LIKE SUBSTR('abc', 1, 1)
+x LIKE SUBSTRING('abc', 1, 1)
x LIKE y
x LIKE a.y
x LIKE '%y%'
@@ -879,3 +879,8 @@ SELECT COUNT(DISTINCT "foo bar") FROM (SELECT 1 AS "foo bar") AS t
SELECT vector
WITH all AS (SELECT 1 AS count) SELECT all.count FROM all
SELECT rename
+GRANT SELECT ON TABLE tbl TO user
+GRANT SELECT, INSERT ON FUNCTION tbl TO user
+GRANT SELECT ON orders TO ROLE PUBLIC
+GRANT SELECT ON nation TO alice WITH GRANT OPTION
+GRANT DELETE ON SCHEMA finance TO bob \ No newline at end of file
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 1f60741..fa2dc79 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -137,6 +137,9 @@ FALSE;
TRUE AND TRUE OR TRUE AND FALSE;
TRUE;
+COALESCE(x, y) <> ALL (SELECT z FROM w);
+COALESCE(x, y) <> ALL (SELECT z FROM w);
+
--------------------------------------
-- Absorption
--------------------------------------
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 7bbeb27..290d276 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -991,9 +991,9 @@ FROM store_sales,
date_dim,
store,
(SELECT ca_zip
- FROM (SELECT Substr(ca_zip, 1, 5) ca_zip
+ FROM (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip
FROM customer_address
- WHERE Substr(ca_zip, 1, 5) IN ( '67436', '26121', '38443',
+ WHERE SUBSTRING(ca_zip, 1, 5) IN ( '67436', '26121', '38443',
'63157',
'68856', '19485', '86425',
'26741',
@@ -1195,7 +1195,7 @@ FROM store_sales,
'92564' )
INTERSECT
SELECT ca_zip
- FROM (SELECT Substr(ca_zip, 1, 5) ca_zip,
+ FROM (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip,
Count(*) cnt
FROM customer_address,
customer
@@ -1207,13 +1207,13 @@ WHERE ss_store_sk = s_store_sk
AND ss_sold_date_sk = d_date_sk
AND d_qoy = 2
AND d_year = 2000
- AND ( Substr(s_zip, 1, 2) = Substr(V1.ca_zip, 1, 2) )
+ AND ( SUBSTRING(s_zip, 1, 2) = SUBSTRING(V1.ca_zip, 1, 2) )
GROUP BY s_store_name
ORDER BY s_store_name
LIMIT 100;
WITH "a1" AS (
SELECT
- SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip"
+ SUBSTRING("customer_address"."ca_zip", 1, 5) AS "ca_zip"
FROM "customer_address" AS "customer_address"
JOIN "customer" AS "customer"
ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
@@ -1224,10 +1224,10 @@ WITH "a1" AS (
COUNT(*) > 10
), "a2" AS (
SELECT
- SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip"
+ SUBSTRING("customer_address"."ca_zip", 1, 5) AS "ca_zip"
FROM "customer_address" AS "customer_address"
WHERE
- SUBSTR("customer_address"."ca_zip", 1, 5) IN ('67436', '26121', '38443', '63157', '68856', '19485', '86425', '26741', '70991', '60899', '63573', '47556', '56193', '93314', '87827', '62017', '85067', '95390', '48091', '10261', '81845', '41790', '42853', '24675', '12840', '60065', '84430', '57451', '24021', '91735', '75335', '71935', '34482', '56943', '70695', '52147', '56251', '28411', '86653', '23005', '22478', '29031', '34398', '15365', '42460', '33337', '59433', '73943', '72477', '74081', '74430', '64605', '39006', '11226', '49057', '97308', '42663', '18187', '19768', '43454', '32147', '76637', '51975', '11181', '45630', '33129', '45995', '64386', '55522', '26697', '20963', '35154', '64587', '49752', '66386', '30586', '59286', '13177', '66646', '84195', '74316', '36853', '32927', '12469', '11904', '36269', '17724', '55346', '12595', '53988', '65439', '28015', '63268', '73590', '29216', '82575', '69267', '13805', '91678', '79460', '94152', '14961', '15419', '48277', '62588', '55493', '28360', '14152', '55225', '18007', '53705', '56573', '80245', '71769', '57348', '36845', '13039', '17270', '22363', '83474', '25294', '43269', '77666', '15488', '99146', '64441', '43338', '38736', '62754', '48556', '86057', '23090', '38114', '66061', '18910', '84385', '23600', '19975', '27883', '65719', '19933', '32085', '49731', '40473', '27190', '46192', '23949', '44738', '12436', '64794', '68741', '15333', '24282', '49085', '31844', '71156', '48441', '17100', '98207', '44982', '20277', '71496', '96299', '37583', '22206', '89174', '30589', '61924', '53079', '10976', '13104', '42794', '54772', '15809', '56434', '39975', '13874', '30753', '77598', '78229', '59478', '12345', '55547', '57422', '42600', '79444', '29074', '29752', '21676', '32096', '43044', '39383', '37296', '36295', '63077', '16572', '31275', '18701', '40197', '48242', '27219', '49865', '84175', '30446', '25165', '13807', '72142', '70499', '70464', '71429', '18111', '70857', '29545', '36425', '52706', '36194', '42963', '75068', '47921', '74763', '90990', '89456', '62073', '88397', '73963', '75885', '62657', '12530', '81146', '57434', '25099', '41429', '98441', '48713', '52552', '31667', '14072', '13903', '44709', '85429', '58017', '38295', '44875', '73541', '30091', '12707', '23762', '62258', '33247', '78722', '77431', '14510', '35656', '72428', '92082', '35267', '43759', '24354', '90952', '11512', '21242', '22579', '56114', '32339', '52282', '41791', '24484', '95020', '28408', '99710', '11899', '43344', '72915', '27644', '62708', '74479', '17177', '32619', '12351', '91339', '31169', '57081', '53522', '16712', '34419', '71779', '44187', '46206', '96099', '61910', '53664', '12295', '31837', '33096', '10813', '63048', '31732', '79118', '73084', '72783', '84952', '46965', '77956', '39815', '32311', '75329', '48156', '30826', '49661', '13736', '92076', '74865', '88149', '92397', '52777', '68453', '32012', '21222', '52721', '24626', '18210', '42177', '91791', '75251', '82075', '44372', '45542', '20609', '60115', '17362', '22750', '90434', '31852', '54071', '33762', '14705', '40718', '56433', '30996', '40657', '49056', '23585', '66455', '41021', '74736', '72151', '37007', '21729', '60177', '84558', '59027', '93855', '60022', '86443', '19541', '86886', '30532', '39062', '48532', '34713', '52077', '22564', '64638', '15273', '31677', '36138', '62367', '60261', '80213', '42818', '25113', '72378', '69802', '69096', '55443', '28820', '13848', '78258', '37490', '30556', '77380', '28447', '44550', '26791', '70609', '82182', '33306', '43224', '22322', '86959', '68519', '14308', '46501', '81131', '34056', '61991', '19896', '87804', '65774', '92564')
+ SUBSTRING("customer_address"."ca_zip", 1, 5) IN ('67436', '26121', '38443', '63157', '68856', '19485', '86425', '26741', '70991', '60899', '63573', '47556', '56193', '93314', '87827', '62017', '85067', '95390', '48091', '10261', '81845', '41790', '42853', '24675', '12840', '60065', '84430', '57451', '24021', '91735', '75335', '71935', '34482', '56943', '70695', '52147', '56251', '28411', '86653', '23005', '22478', '29031', '34398', '15365', '42460', '33337', '59433', '73943', '72477', '74081', '74430', '64605', '39006', '11226', '49057', '97308', '42663', '18187', '19768', '43454', '32147', '76637', '51975', '11181', '45630', '33129', '45995', '64386', '55522', '26697', '20963', '35154', '64587', '49752', '66386', '30586', '59286', '13177', '66646', '84195', '74316', '36853', '32927', '12469', '11904', '36269', '17724', '55346', '12595', '53988', '65439', '28015', '63268', '73590', '29216', '82575', '69267', '13805', '91678', '79460', '94152', '14961', '15419', '48277', '62588', '55493', '28360', '14152', '55225', '18007', '53705', '56573', '80245', '71769', '57348', '36845', '13039', '17270', '22363', '83474', '25294', '43269', '77666', '15488', '99146', '64441', '43338', '38736', '62754', '48556', '86057', '23090', '38114', '66061', '18910', '84385', '23600', '19975', '27883', '65719', '19933', '32085', '49731', '40473', '27190', '46192', '23949', '44738', '12436', '64794', '68741', '15333', '24282', '49085', '31844', '71156', '48441', '17100', '98207', '44982', '20277', '71496', '96299', '37583', '22206', '89174', '30589', '61924', '53079', '10976', '13104', '42794', '54772', '15809', '56434', '39975', '13874', '30753', '77598', '78229', '59478', '12345', '55547', '57422', '42600', '79444', '29074', '29752', '21676', '32096', '43044', '39383', '37296', '36295', '63077', '16572', '31275', '18701', '40197', '48242', '27219', '49865', '84175', '30446', '25165', '13807', '72142', '70499', '70464', '71429', '18111', '70857', '29545', '36425', '52706', '36194', '42963', '75068', '47921', '74763', '90990', '89456', '62073', '88397', '73963', '75885', '62657', '12530', '81146', '57434', '25099', '41429', '98441', '48713', '52552', '31667', '14072', '13903', '44709', '85429', '58017', '38295', '44875', '73541', '30091', '12707', '23762', '62258', '33247', '78722', '77431', '14510', '35656', '72428', '92082', '35267', '43759', '24354', '90952', '11512', '21242', '22579', '56114', '32339', '52282', '41791', '24484', '95020', '28408', '99710', '11899', '43344', '72915', '27644', '62708', '74479', '17177', '32619', '12351', '91339', '31169', '57081', '53522', '16712', '34419', '71779', '44187', '46206', '96099', '61910', '53664', '12295', '31837', '33096', '10813', '63048', '31732', '79118', '73084', '72783', '84952', '46965', '77956', '39815', '32311', '75329', '48156', '30826', '49661', '13736', '92076', '74865', '88149', '92397', '52777', '68453', '32012', '21222', '52721', '24626', '18210', '42177', '91791', '75251', '82075', '44372', '45542', '20609', '60115', '17362', '22750', '90434', '31852', '54071', '33762', '14705', '40718', '56433', '30996', '40657', '49056', '23585', '66455', '41021', '74736', '72151', '37007', '21729', '60177', '84558', '59027', '93855', '60022', '86443', '19541', '86886', '30532', '39062', '48532', '34713', '52077', '22564', '64638', '15273', '31677', '36138', '62367', '60261', '80213', '42818', '25113', '72378', '69802', '69096', '55443', '28820', '13848', '78258', '37490', '30556', '77380', '28447', '44550', '26791', '70609', '82182', '33306', '43224', '22322', '86959', '68519', '14308', '46501', '81131', '34056', '61991', '19896', '87804', '65774', '92564')
INTERSECT
SELECT
"a1"."ca_zip" AS "ca_zip"
@@ -1244,7 +1244,7 @@ JOIN "date_dim" AS "date_dim"
JOIN "store" AS "store"
ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
JOIN "a2" AS "a2"
- ON SUBSTR("a2"."ca_zip", 1, 2) = SUBSTR("store"."s_zip", 1, 2)
+ ON SUBSTRING("a2"."ca_zip", 1, 2) = SUBSTRING("store"."s_zip", 1, 2)
GROUP BY
"store"."s_store_name"
ORDER BY
@@ -2319,7 +2319,7 @@ FROM catalog_sales,
date_dim
WHERE cs_bill_customer_sk = c_customer_sk
AND c_current_addr_sk = ca_address_sk
- AND ( Substr(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405',
+ AND ( SUBSTRING(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405',
'86475', '85392', '85460', '80348',
'81792' )
OR ca_state IN ( 'CA', 'WA', 'GA' )
@@ -2344,7 +2344,7 @@ JOIN "customer_address" AS "customer_address"
ON (
"catalog_sales"."cs_sales_price" > 500
OR "customer_address"."ca_state" IN ('CA', 'WA', 'GA')
- OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
+ OR SUBSTRING("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
)
AND "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
GROUP BY
@@ -2643,7 +2643,7 @@ WHERE d_date_sk = ss_sold_date_sk
AND d_year = 1998
AND ss_customer_sk = c_customer_sk
AND c_current_addr_sk = ca_address_sk
- AND Substr(ca_zip, 1, 5) <> Substr(s_zip, 1, 5)
+ AND SUBSTRING(ca_zip, 1, 5) <> SUBSTRING(s_zip, 1, 5)
AND ss_store_sk = s_store_sk
GROUP BY i_brand,
i_brand_id,
@@ -2672,7 +2672,7 @@ JOIN "customer_address" AS "customer_address"
ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
JOIN "store" AS "store"
ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
- AND SUBSTR("customer_address"."ca_zip", 1, 5) <> SUBSTR("store"."s_zip", 1, 5)
+ AND SUBSTRING("customer_address"."ca_zip", 1, 5) <> SUBSTRING("store"."s_zip", 1, 5)
WHERE
"date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1998
GROUP BY
@@ -2895,7 +2895,7 @@ LIMIT 100;
--------------------------------------
# execute: true
WITH frequent_ss_items
- AS (SELECT Substr(i_item_desc, 1, 30) itemdesc,
+ AS (SELECT SUBSTRING(i_item_desc, 1, 30) itemdesc,
i_item_sk item_sk,
d_date solddate,
Count(*) cnt
@@ -2905,7 +2905,7 @@ WITH frequent_ss_items
WHERE ss_sold_date_sk = d_date_sk
AND ss_item_sk = i_item_sk
AND d_year IN ( 1998, 1998 + 1, 1998 + 2, 1998 + 3 )
- GROUP BY Substr(i_item_desc, 1, 30),
+ GROUP BY SUBSTRING(i_item_desc, 1, 30),
i_item_sk,
d_date
HAVING Count(*) > 4),
@@ -2962,7 +2962,7 @@ WITH "frequent_ss_items" AS (
JOIN "item" AS "item"
ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
GROUP BY
- SUBSTR("item"."i_item_desc", 1, 30),
+ SUBSTRING("item"."i_item_desc", 1, 30),
"item"."i_item_sk",
"date_dim"."d_date"
HAVING
@@ -5296,7 +5296,7 @@ FROM web_sales,
WHERE ws_bill_customer_sk = c_customer_sk
AND c_current_addr_sk = ca_address_sk
AND ws_item_sk = i_item_sk
- AND ( Substr(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405',
+ AND ( SUBSTRING(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405',
'86475', '85392', '85460', '80348',
'81792' )
OR i_item_id IN (SELECT i_item_id
@@ -5340,7 +5340,7 @@ JOIN "customer_address" AS "customer_address"
ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
WHERE
NOT "_u_0"."i_item_id" IS NULL
- OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
+ OR SUBSTRING("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
GROUP BY
"customer_address"."ca_zip",
"customer_address"."ca_state"
@@ -7585,7 +7585,7 @@ LIMIT 100;
-- TPC-DS 62
--------------------------------------
# execute: true
-SELECT Substr(w_warehouse_name, 1, 20) AS "_col_0",
+SELECT SUBSTRING(w_warehouse_name, 1, 20) AS "_col_0",
sm_type,
web_name,
Sum(CASE
@@ -7622,15 +7622,15 @@ WHERE d_month_seq BETWEEN 1222 AND 1222 + 11
AND ws_warehouse_sk = w_warehouse_sk
AND ws_ship_mode_sk = sm_ship_mode_sk
AND ws_web_site_sk = web_site_sk
-GROUP BY Substr(w_warehouse_name, 1, 20),
+GROUP BY SUBSTRING(w_warehouse_name, 1, 20),
sm_type,
web_name
-ORDER BY Substr(w_warehouse_name, 1, 20),
+ORDER BY SUBSTRING(w_warehouse_name, 1, 20),
sm_type,
web_name
LIMIT 100;
SELECT
- SUBSTR("warehouse"."w_warehouse_name", 1, 20) AS "_col_0",
+ SUBSTRING("warehouse"."w_warehouse_name", 1, 20) AS "_col_0",
"ship_mode"."sm_type" AS "sm_type",
"web_site"."web_name" AS "web_name",
SUM(
@@ -7683,7 +7683,7 @@ JOIN "warehouse" AS "warehouse"
JOIN "web_site" AS "web_site"
ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
GROUP BY
- SUBSTR("warehouse"."w_warehouse_name", 1, 20),
+ SUBSTRING("warehouse"."w_warehouse_name", 1, 20),
"ship_mode"."sm_type",
"web_site"."web_name"
ORDER BY
@@ -10638,7 +10638,7 @@ LIMIT 100;
# execute: true
SELECT c_last_name,
c_first_name,
- Substr(s_city, 1, 30) AS "_col_2",
+ SUBSTRING(s_city, 1, 30) AS "_col_2",
ss_ticket_number,
amt,
profit
@@ -10667,7 +10667,7 @@ FROM (SELECT ss_ticket_number,
WHERE ss_customer_sk = c_customer_sk
ORDER BY c_last_name,
c_first_name,
- Substr(s_city, 1, 30),
+ SUBSTRING(s_city, 1, 30),
profit
LIMIT 100;
WITH "ms" AS (
@@ -10701,7 +10701,7 @@ WITH "ms" AS (
SELECT
"customer"."c_last_name" AS "c_last_name",
"customer"."c_first_name" AS "c_first_name",
- SUBSTR("ms"."s_city", 1, 30) AS "_col_2",
+ SUBSTRING("ms"."s_city", 1, 30) AS "_col_2",
"ms"."ss_ticket_number" AS "ss_ticket_number",
"ms"."amt" AS "amt",
"ms"."profit" AS "profit"
@@ -10711,7 +10711,7 @@ JOIN "customer" AS "customer"
ORDER BY
"c_last_name",
"c_first_name",
- SUBSTR("ms"."s_city", 1, 30),
+ SUBSTRING("ms"."s_city", 1, 30),
"profit"
LIMIT 100;
@@ -11371,7 +11371,7 @@ LIMIT 100;
-- TPC-DS 85
--------------------------------------
# execute: true
-SELECT Substr(r_reason_desc, 1, 20) AS "_col_0",
+SELECT SUBSTRING(r_reason_desc, 1, 20) AS "_col_0",
Avg(ws_quantity) AS "_col_1",
Avg(wr_refunded_cash) AS "_col_2",
Avg(wr_fee) AS "_col_3"
@@ -11417,13 +11417,13 @@ WHERE ws_web_page_sk = wp_web_page_sk
AND ca_state IN ( 'FL', 'WI', 'KS' )
AND ws_net_profit BETWEEN 50 AND 250 ) )
GROUP BY r_reason_desc
-ORDER BY Substr(r_reason_desc, 1, 20),
+ORDER BY SUBSTRING(r_reason_desc, 1, 20),
Avg(ws_quantity),
Avg(wr_refunded_cash),
Avg(wr_fee)
LIMIT 100;
SELECT
- SUBSTR("reason"."r_reason_desc", 1, 20) AS "_col_0",
+ SUBSTRING("reason"."r_reason_desc", 1, 20) AS "_col_0",
AVG("web_sales"."ws_quantity") AS "_col_1",
AVG("web_returns"."wr_refunded_cash") AS "_col_2",
AVG("web_returns"."wr_fee") AS "_col_3"
@@ -12617,7 +12617,7 @@ ORDER BY
-- TPC-DS 99
--------------------------------------
# execute: true
-SELECT Substr(w_warehouse_name, 1, 20) AS "_col_0",
+SELECT SUBSTRING(w_warehouse_name, 1, 20) AS "_col_0",
sm_type,
cc_name,
Sum(CASE
@@ -12654,15 +12654,15 @@ WHERE d_month_seq BETWEEN 1200 AND 1200 + 11
AND cs_warehouse_sk = w_warehouse_sk
AND cs_ship_mode_sk = sm_ship_mode_sk
AND cs_call_center_sk = cc_call_center_sk
-GROUP BY Substr(w_warehouse_name, 1, 20),
+GROUP BY SUBSTRING(w_warehouse_name, 1, 20),
sm_type,
cc_name
-ORDER BY Substr(w_warehouse_name, 1, 20),
+ORDER BY SUBSTRING(w_warehouse_name, 1, 20),
sm_type,
cc_name
LIMIT 100;
SELECT
- SUBSTR("warehouse"."w_warehouse_name", 1, 20) AS "_col_0",
+ SUBSTRING("warehouse"."w_warehouse_name", 1, 20) AS "_col_0",
"ship_mode"."sm_type" AS "sm_type",
"call_center"."cc_name" AS "cc_name",
SUM(
@@ -12715,7 +12715,7 @@ JOIN "ship_mode" AS "ship_mode"
JOIN "warehouse" AS "warehouse"
ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
GROUP BY
- SUBSTR("warehouse"."w_warehouse_name", 1, 20),
+ SUBSTRING("warehouse"."w_warehouse_name", 1, 20),
"ship_mode"."sm_type",
"call_center"."cc_name"
ORDER BY
diff --git a/tests/test_build.py b/tests/test_build.py
index d169530..7518b72 100644
--- a/tests/test_build.py
+++ b/tests/test_build.py
@@ -761,6 +761,16 @@ class TestBuild(unittest.TestCase):
),
"MERGE INTO target_table AS target USING source_table AS source ON target.id = source.id WHEN MATCHED THEN UPDATE SET target.name = source.name",
),
+ (
+ lambda: exp.merge(
+ "WHEN MATCHED THEN UPDATE SET target.name = source.name",
+ into=exp.table_("target_table").as_("target"),
+ using=exp.table_("source_table").as_("source"),
+ on="target.id = source.id",
+ returning="target.*",
+ ),
+ "MERGE INTO target_table AS target USING source_table AS source ON target.id = source.id WHEN MATCHED THEN UPDATE SET target.name = source.name RETURNING target.*",
+ ),
]:
with self.subTest(sql):
self.assertEqual(expression().sql(dialect[0] if dialect else None), sql)
diff --git a/tests/test_diff.py b/tests/test_diff.py
index fa012a8..f83c805 100644
--- a/tests/test_diff.py
+++ b/tests/test_diff.py
@@ -1,14 +1,18 @@
import unittest
from sqlglot import exp, parse_one
-from sqlglot.diff import Insert, Keep, Move, Remove, Update, diff
+from sqlglot.diff import Insert, Move, Remove, Update, diff
from sqlglot.expressions import Join, to_table
+def diff_delta_only(source, target, matchings=None, **kwargs):
+ return diff(source, target, matchings=matchings, delta_only=True, **kwargs)
+
+
class TestDiff(unittest.TestCase):
def test_simple(self):
self._validate_delta_only(
- diff(parse_one("SELECT a + b"), parse_one("SELECT a - b")),
+ diff_delta_only(parse_one("SELECT a + b"), parse_one("SELECT a - b")),
[
Remove(parse_one("a + b")), # the Add node
Insert(parse_one("a - b")), # the Sub node
@@ -16,21 +20,21 @@ class TestDiff(unittest.TestCase):
)
self._validate_delta_only(
- diff(parse_one("SELECT a, b, c"), parse_one("SELECT a, c")),
+ diff_delta_only(parse_one("SELECT a, b, c"), parse_one("SELECT a, c")),
[
Remove(parse_one("b")), # the Column node
],
)
self._validate_delta_only(
- diff(parse_one("SELECT a, b"), parse_one("SELECT a, b, c")),
+ diff_delta_only(parse_one("SELECT a, b"), parse_one("SELECT a, b, c")),
[
Insert(parse_one("c")), # the Column node
],
)
self._validate_delta_only(
- diff(
+ diff_delta_only(
parse_one("SELECT a FROM table_one"),
parse_one("SELECT a FROM table_two"),
),
@@ -44,7 +48,9 @@ class TestDiff(unittest.TestCase):
def test_lambda(self):
self._validate_delta_only(
- diff(parse_one("SELECT a, b, c, x(a -> a)"), parse_one("SELECT a, b, c, x(b -> b)")),
+ diff_delta_only(
+ parse_one("SELECT a, b, c, x(a -> a)"), parse_one("SELECT a, b, c, x(b -> b)")
+ ),
[
Update(
exp.Lambda(this=exp.to_identifier("a"), expressions=[exp.to_identifier("a")]),
@@ -55,14 +61,16 @@ class TestDiff(unittest.TestCase):
def test_udf(self):
self._validate_delta_only(
- diff(parse_one('SELECT a, b, "my.udf1"()'), parse_one('SELECT a, b, "my.udf2"()')),
+ diff_delta_only(
+ parse_one('SELECT a, b, "my.udf1"()'), parse_one('SELECT a, b, "my.udf2"()')
+ ),
[
Insert(parse_one('"my.udf2"()')),
Remove(parse_one('"my.udf1"()')),
],
)
self._validate_delta_only(
- diff(
+ diff_delta_only(
parse_one('SELECT a, b, "my.udf"(x, y, z)'),
parse_one('SELECT a, b, "my.udf"(x, y, w)'),
),
@@ -74,28 +82,28 @@ class TestDiff(unittest.TestCase):
def test_node_position_changed(self):
self._validate_delta_only(
- diff(parse_one("SELECT a, b, c"), parse_one("SELECT c, a, b")),
+ diff_delta_only(parse_one("SELECT a, b, c"), parse_one("SELECT c, a, b")),
[
Move(parse_one("c")), # the Column node
],
)
self._validate_delta_only(
- diff(parse_one("SELECT a + b"), parse_one("SELECT b + a")),
+ diff_delta_only(parse_one("SELECT a + b"), parse_one("SELECT b + a")),
[
Move(parse_one("a")), # the Column node
],
)
self._validate_delta_only(
- diff(parse_one("SELECT aaaa AND bbbb"), parse_one("SELECT bbbb AND aaaa")),
+ diff_delta_only(parse_one("SELECT aaaa AND bbbb"), parse_one("SELECT bbbb AND aaaa")),
[
Move(parse_one("aaaa")), # the Column node
],
)
self._validate_delta_only(
- diff(
+ diff_delta_only(
parse_one("SELECT aaaa OR bbbb OR cccc"),
parse_one("SELECT cccc OR bbbb OR aaaa"),
),
@@ -120,7 +128,7 @@ class TestDiff(unittest.TestCase):
"""
self._validate_delta_only(
- diff(parse_one(expr_src), parse_one(expr_tgt)),
+ diff_delta_only(parse_one(expr_src), parse_one(expr_tgt)),
[
Remove(parse_one("LOWER(c) AS c")), # the Alias node
Remove(parse_one("LOWER(c)")), # the Lower node
@@ -133,8 +141,7 @@ class TestDiff(unittest.TestCase):
expr_src = "SELECT a, b FROM t1 LEFT JOIN t2 ON t1.key = t2.key"
expr_tgt = "SELECT a, b FROM t1 RIGHT JOIN t2 ON t1.key = t2.key"
- changes = diff(parse_one(expr_src), parse_one(expr_tgt))
- changes = _delta_only(changes)
+ changes = diff_delta_only(parse_one(expr_src), parse_one(expr_tgt))
self.assertEqual(len(changes), 2)
self.assertTrue(isinstance(changes[0], Remove))
@@ -145,10 +152,10 @@ class TestDiff(unittest.TestCase):
expr_src = parse_one("SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b)")
expr_tgt = parse_one("SELECT RANK() OVER (PARTITION BY a ORDER BY b)")
- self._validate_delta_only(diff(expr_src, expr_src), [])
+ self._validate_delta_only(diff_delta_only(expr_src, expr_src), [])
self._validate_delta_only(
- diff(expr_src, expr_tgt),
+ diff_delta_only(expr_src, expr_tgt),
[
Remove(parse_one("ROW_NUMBER()")), # the Anonymous node
Insert(parse_one("RANK()")), # the Anonymous node
@@ -160,7 +167,7 @@ class TestDiff(unittest.TestCase):
expr_tgt = parse_one("SELECT 1, 2, 3, 4")
self._validate_delta_only(
- diff(expr_src, expr_tgt),
+ diff_delta_only(expr_src, expr_tgt),
[
Remove(expr_src),
Insert(expr_tgt),
@@ -171,7 +178,7 @@ class TestDiff(unittest.TestCase):
)
self._validate_delta_only(
- diff(expr_src, expr_tgt, matchings=[(expr_src, expr_tgt)]),
+ diff_delta_only(expr_src, expr_tgt, matchings=[(expr_src, expr_tgt)]),
[
Insert(exp.Literal.number(2)),
Insert(exp.Literal.number(3)),
@@ -180,23 +187,20 @@ class TestDiff(unittest.TestCase):
)
with self.assertRaises(ValueError):
- diff(expr_src, expr_tgt, matchings=[(expr_src, expr_tgt), (expr_src, expr_tgt)])
+ diff_delta_only(
+ expr_src, expr_tgt, matchings=[(expr_src, expr_tgt), (expr_src, expr_tgt)]
+ )
def test_identifier(self):
expr_src = parse_one("SELECT a FROM tbl")
expr_tgt = parse_one("SELECT a, tbl.b from tbl")
self._validate_delta_only(
- diff(expr_src, expr_tgt),
+ diff_delta_only(expr_src, expr_tgt),
[
Insert(expression=exp.to_column("tbl.b")),
],
)
- def _validate_delta_only(self, actual_diff, expected_delta):
- actual_delta = _delta_only(actual_diff)
+ def _validate_delta_only(self, actual_delta, expected_delta):
self.assertEqual(set(actual_delta), set(expected_delta))
-
-
-def _delta_only(changes):
- return [d for d in changes if not isinstance(d, Keep)]
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index 8e2ac45..e88740b 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -674,6 +674,8 @@ class TestExpressions(unittest.TestCase):
self.assertIsInstance(parse_one("STR_POSITION(a, 'test')"), exp.StrPosition)
self.assertIsInstance(parse_one("STR_TO_UNIX(a, 'format')"), exp.StrToUnix)
self.assertIsInstance(parse_one("STRUCT_EXTRACT(a, 'test')"), exp.StructExtract)
+ self.assertIsInstance(parse_one("SUBSTR('a', 1, 1)"), exp.Substring)
+ self.assertIsInstance(parse_one("SUBSTRING('a', 1, 1)"), exp.Substring)
self.assertIsInstance(parse_one("SUM(a)"), exp.Sum)
self.assertIsInstance(parse_one("SQRT(a)"), exp.Sqrt)
self.assertIsInstance(parse_one("STDDEV(a)"), exp.Stddev)
diff --git a/tests/test_transpile.py b/tests/test_transpile.py
index 1e0d9b6..07a915d 100644
--- a/tests/test_transpile.py
+++ b/tests/test_transpile.py
@@ -857,7 +857,6 @@ FROM x""",
"CREATE OR REPLACE STAGE",
"EXECUTE statement",
"EXPLAIN SELECT * FROM x",
- "GRANT INSERT ON foo TO bla",
"LOAD foo",
"OPTIMIZE TABLE y",
"PREPARE statement",
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-17 00:05:54 +0000