diff options
Diffstat (limited to 'tests/dialects/test_bigquery.py')
| -rw-r--r-- | tests/dialects/test_bigquery.py | 52 |
1 files changed, 31 insertions, 21 deletions
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py index 26b12a1..ec16dba 100644 --- a/tests/dialects/test_bigquery.py +++ b/tests/dialects/test_bigquery.py @@ -200,24 +200,7 @@ LANGUAGE js AS self.validate_identity("CAST(x AS NVARCHAR)", "CAST(x AS STRING)") self.validate_identity("CAST(x AS TIMESTAMPTZ)", "CAST(x AS TIMESTAMP)") self.validate_identity("CAST(x AS RECORD)", "CAST(x AS STRUCT)") - self.validate_all( - "EDIT_DISTANCE(col1, col2, max_distance => 3)", - write={ - "bigquery": "EDIT_DISTANCE(col1, col2, max_distance => 3)", - "clickhouse": UnsupportedError, - "databricks": UnsupportedError, - "drill": UnsupportedError, - "duckdb": UnsupportedError, - "hive": UnsupportedError, - "postgres": "LEVENSHTEIN_LESS_EQUAL(col1, col2, 3)", - "presto": UnsupportedError, - "snowflake": "EDITDISTANCE(col1, col2, 3)", - "spark": UnsupportedError, - "spark2": UnsupportedError, - "sqlite": UnsupportedError, - }, - ) - + self.validate_identity("SELECT * FROM x WHERE x.y >= (SELECT MAX(a) FROM b-c) - 20") self.validate_identity( "MERGE INTO dataset.NewArrivals USING (SELECT * FROM UNNEST([('microwave', 10, 'warehouse #1'), ('dryer', 30, 'warehouse #1'), ('oven', 20, 'warehouse #2')])) ON FALSE WHEN NOT MATCHED THEN INSERT ROW WHEN NOT MATCHED BY SOURCE THEN DELETE" ) @@ -333,6 +316,23 @@ LANGUAGE js AS ) self.validate_all( + "EDIT_DISTANCE(col1, col2, max_distance => 3)", + write={ + "bigquery": "EDIT_DISTANCE(col1, col2, max_distance => 3)", + "clickhouse": UnsupportedError, + "databricks": UnsupportedError, + "drill": UnsupportedError, + "duckdb": UnsupportedError, + "hive": UnsupportedError, + "postgres": "LEVENSHTEIN_LESS_EQUAL(col1, col2, 3)", + "presto": UnsupportedError, + "snowflake": "EDITDISTANCE(col1, col2, 3)", + "spark": UnsupportedError, + "spark2": UnsupportedError, + "sqlite": UnsupportedError, + }, + ) + self.validate_all( "EDIT_DISTANCE(a, b)", write={ "bigquery": "EDIT_DISTANCE(a, b)", @@ -1608,11 +1608,11 @@ WHERE ) self.validate_identity( - "CONTAINS_SUBSTRING(a, b, json_scope => 'JSON_KEYS_AND_VALUES')" + "CONTAINS_SUBSTR(a, b, json_scope => 'JSON_KEYS_AND_VALUES')" ).assert_is(exp.Anonymous) self.validate_all( - """CONTAINS_SUBSTRING(a, b)""", + """CONTAINS_SUBSTR(a, b)""", read={ "": "CONTAINS(a, b)", "spark": "CONTAINS(a, b)", @@ -1628,7 +1628,7 @@ WHERE "snowflake": "CONTAINS(LOWER(a), LOWER(b))", "duckdb": "CONTAINS(LOWER(a), LOWER(b))", "oracle": "CONTAINS(LOWER(a), LOWER(b))", - "bigquery": "CONTAINS_SUBSTRING(a, b)", + "bigquery": "CONTAINS_SUBSTR(a, b)", }, ) @@ -2131,6 +2131,16 @@ OPTIONS ( }, ) + self.validate_all( + f"SELECT SUM(f1) OVER (ORDER BY f2 {sort_order}) FROM t", + read={ + "": f"SELECT SUM(f1) OVER (ORDER BY f2 {sort_order} {null_order}) FROM t", + }, + write={ + "bigquery": f"SELECT SUM(f1) OVER (ORDER BY f2 {sort_order}) FROM t", + }, + ) + def test_json_extract(self): self.validate_all( """SELECT JSON_QUERY('{"class": {"students": []}}', '$.class')""", |