summaryrefslogtreecommitdiffstats
path: root/tests
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--tests/dialects/test_bigquery.py2
-rw-r--r--tests/dialects/test_snowflake.py12
-rw-r--r--tests/fixtures/identity.sql1
-rw-r--r--tests/fixtures/optimizer/merge_subqueries.sql22
-rw-r--r--tests/fixtures/optimizer/optimizer.sql8
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql17
-rw-r--r--tests/fixtures/optimizer/simplify.sql6
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql2
-rw-r--r--tests/helpers.py4
-rw-r--r--tests/test_optimizer.py11
10 files changed, 82 insertions, 3 deletions
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,