From 9f19773cebdc9476f2a3266d3c01c967c38fcd1e Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Fri, 30 Jun 2023 10:03:58 +0200 Subject: Merging upstream version 16.7.7. Signed-off-by: Daniel Baumann --- tests/dialects/test_bigquery.py | 2 ++ tests/dialects/test_snowflake.py | 12 +++++++++++- tests/fixtures/identity.sql | 1 + tests/fixtures/optimizer/merge_subqueries.sql | 22 ++++++++++++++++++++++ tests/fixtures/optimizer/optimizer.sql | 8 ++++++++ tests/fixtures/optimizer/qualify_columns.sql | 17 +++++++++++++++++ tests/fixtures/optimizer/simplify.sql | 6 ++++++ tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 2 +- tests/helpers.py | 4 +++- tests/test_optimizer.py | 11 +++++++++++ 10 files changed, 82 insertions(+), 3 deletions(-) (limited to 'tests') diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py index e95ff3e..e3fc495 100644 --- a/tests/dialects/test_bigquery.py +++ b/tests/dialects/test_bigquery.py @@ -29,6 +29,8 @@ class TestBigQuery(Validator): with self.assertRaises(ParseError): transpile("SELECT * FROM UNNEST(x) AS x(y)", read="bigquery") + self.validate_identity("SELECT ANY_VALUE(fruit HAVING MAX sold) FROM fruits") + self.validate_identity("SELECT ANY_VALUE(fruit HAVING MIN sold) FROM fruits") self.validate_identity("SELECT `project-id`.udfs.func(call.dir)") self.validate_identity("SELECT CAST(CURRENT_DATE AS STRING FORMAT 'DAY') AS current_day") self.validate_identity("SAFE_CAST(encrypted_value AS STRING FORMAT 'BASE64')") diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index 4d2c392..e9826a6 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -550,7 +550,17 @@ class TestSnowflake(Validator): "snowflake": "DATEADD(DAY, 5, CAST('2008-12-25' AS DATE))", }, ) - self.validate_identity("DATEDIFF(DAY, 5, CAST('2008-12-25' AS DATE))") + self.validate_identity( + "DATEDIFF(DAY, CAST('2007-12-25' AS DATE), CAST('2008-12-25' AS DATE))" + ) + self.validate_identity( + "TIMEDIFF(DAY, CAST('2007-12-25' AS DATE), CAST('2008-12-25' AS DATE))", + "DATEDIFF(DAY, CAST('2007-12-25' AS DATE), CAST('2008-12-25' AS DATE))", + ) + self.validate_identity( + "TIMESTAMPDIFF(DAY, CAST('2007-12-25' AS DATE), CAST('2008-12-25' AS DATE))", + "DATEDIFF(DAY, CAST('2007-12-25' AS DATE), CAST('2008-12-25' AS DATE))", + ) def test_semi_structured_types(self): self.validate_identity("SELECT CAST(a AS VARIANT)") diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index ff3162b..60a655a 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -155,6 +155,7 @@ REGEXP_LIKE('new york', '.') REGEXP_SPLIT('new york', '.') SPLIT('new york', '.') X((y AS z)).1 +X(a.b = 1) (x AS y, y AS z) REPLACE(1) DATE(x) = DATE(y) diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index bd56e07..fb69ea7 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -2,6 +2,28 @@ SELECT a, b FROM (SELECT a, b FROM x); SELECT x.a AS a, x.b AS b FROM x AS x; +# title: Wrap addition in a multiplication +SELECT c * 2 AS d FROM (SELECT a + b AS c FROM x); +SELECT (x.a + x.b) * 2 AS d FROM x AS x; + +# title: Wrap addition in an addition +# note: The "simplify" rule will unwrap this +SELECT c + d AS e FROM (SELECT a + b AS c, a AS d FROM x); +SELECT (x.a + x.b) + x.a AS e FROM x AS x; + +# title: Wrap multiplication in an addition +# note: The "simplify" rule will unwrap this +WITH cte AS (SELECT a * b AS c, a AS d FROM x) SELECT c + d AS e FROM cte; +SELECT (x.a * x.b) + x.a AS e FROM x AS x; + +# title: Don't wrap function +SELECT 2 * foo AS bar FROM (SELECT CAST(b AS DOUBLE) AS foo FROM x); +SELECT 2 * CAST(x.b AS DOUBLE) AS bar FROM x AS x; + +# title: Don't wrap a wrapped expression +SELECT foo * 2 AS bar FROM (SELECT (1 + 2 + 3) AS foo FROM x); +SELECT (1 + 2 + 3) * 2 AS bar FROM x AS x; + # title: Inner table alias is merged SELECT a, b FROM (SELECT a, b FROM x AS q) AS r; SELECT q.a AS a, q.b AS b FROM x AS q; diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index f71ddde..38e64d7 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -693,3 +693,11 @@ GROUP BY "x"."a" + 1 + 1 HAVING "x"."a" + 1 + 1 + 1 + 1 > 1; + +# title: replace alias with mult expression without wrapping it +WITH cte AS (SELECT a * b AS c, a AS d, b as e FROM x) SELECT c + d - (c - e) AS f FROM cte; +SELECT + "x"."a" * "x"."b" + "x"."a" - ( + "x"."a" * "x"."b" - "x"."b" + ) AS "f" +FROM "x" AS "x"; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 81c0b5e..f7ece5b 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -87,6 +87,23 @@ SELECT x.a AS a, x.b AS b FROM x AS x GROUP BY x.a, x.b; SELECT a, b FROM x ORDER BY 1, 2; SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a, x.b; +SELECT 2 FROM x GROUP BY 1; +SELECT 2 AS "2" FROM x AS x GROUP BY 1; + +SELECT 'a' AS a FROM x GROUP BY 1; +SELECT 'a' AS a FROM x AS x GROUP BY 1; + +# execute: false +-- this query seems to be invalid in postgres and duckdb but valid in bigquery +SELECT 2 a FROM x GROUP BY 1 HAVING a > 1; +SELECT 2 AS a FROM x AS x GROUP BY 1 HAVING a > 1; + +SELECT 2 d FROM x GROUP BY d HAVING d > 1; +SELECT 2 AS d FROM x AS x GROUP BY 1 HAVING d > 1; + +SELECT 2 d FROM x GROUP BY 1 ORDER BY 1; +SELECT 2 AS d FROM x AS x GROUP BY 1 ORDER BY d; + # execute: false SELECT DATE(a), DATE(b) AS c FROM x GROUP BY 1, 2; SELECT DATE(x.a) AS _col_0, DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE(x.b); diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index e0aded4..f821575 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -354,6 +354,12 @@ a + 4; a + (1 + 1) + (10); a + 12; +a + (1 * 1) + (1 - (1 * 1)); +a + 1; + +a + (b * c) + (d - (e * f)); +a + b * c + (d - e * f); + 5 + 4 * 3; 17; diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index bbfd47f..8aaf50c 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -5985,7 +5985,7 @@ WITH "date_dim_2" AS ( WHERE "store"."currency_rank" <= 10 OR "store"."return_rank" <= 10 ORDER BY - 'store', + 1, "store"."return_rank", "store"."currency_rank" LIMIT 100 diff --git a/tests/helpers.py b/tests/helpers.py index 30aeff7..cc085b3 100644 --- a/tests/helpers.py +++ b/tests/helpers.py @@ -22,7 +22,9 @@ def _extract_meta(sql): def assert_logger_contains(message, logger, level="error"): output = "\n".join(str(args[0][0]) for args in getattr(logger, level).call_args_list) - assert message in output + if message not in output: + print(f"Expected '{message}' not in {output}") + raise def load_sql_fixtures(filename): diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py index b7425af..40eef9f 100644 --- a/tests/test_optimizer.py +++ b/tests/test_optimizer.py @@ -1,6 +1,7 @@ import unittest from concurrent.futures import ProcessPoolExecutor, as_completed from functools import partial +from unittest.mock import patch import duckdb from pandas.testing import assert_frame_equal @@ -14,6 +15,7 @@ from sqlglot.schema import MappingSchema from tests.helpers import ( TPCDS_SCHEMA, TPCH_SCHEMA, + assert_logger_contains, load_sql_fixture_pairs, load_sql_fixtures, string_to_bool, @@ -411,6 +413,15 @@ FROM READ_CSV('tests/fixtures/optimizer/tpc-h/nation.csv.gz', 'delimiter', '|') {"s.b"}, ) + @patch("sqlglot.optimizer.scope.logger") + def test_scope_warning(self, logger): + self.assertEqual(len(traverse_scope(parse_one("WITH q AS (@y) SELECT * FROM q"))), 1) + assert_logger_contains( + "Cannot traverse scope %s with type '%s'", + logger, + level="warning", + ) + def test_literal_type_annotation(self): tests = { "SELECT 5": exp.DataType.Type.INT, -- cgit v1.2.3