5 files changed, 54 insertions, 1 deletions
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