Adding upstream version 7.1.3.upstream/7.1.3

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

7 files changed, 526 insertions, 70 deletions

diff --git a/tests/fixtures/optimizer/eliminate_ctes.sql b/tests/fixtures/optimizer/eliminate_ctes.sql
new file mode 100644
index 0000000..11e5e4f
--- /dev/null
+++ b/tests/fixtures/optimizer/eliminate_ctes.sql
@@ -0,0 +1,48 @@
+# title: CTE
+WITH q AS (
+  SELECT
+    a
+  FROM x
+)
+SELECT
+  a
+FROM x;
+SELECT
+  a
+FROM x;
+
+# title: Nested CTE
+SELECT
+  a
+FROM (
+  WITH q AS (
+    SELECT
+      a
+    FROM x
+  )
+  SELECT a FROM x
+);
+SELECT
+  a
+FROM (
+  SELECT
+    a
+  FROM x
+);
+
+# title: Chained CTE
+WITH q AS (
+  SELECT
+    a
+  FROM x
+), r AS (
+  SELECT
+    a
+  FROM q
+)
+SELECT
+  a
+FROM x;
+SELECT
+  a
+FROM x;
diff --git a/tests/fixtures/optimizer/eliminate_joins.sql b/tests/fixtures/optimizer/eliminate_joins.sql
new file mode 100644
index 0000000..ac3d92d
--- /dev/null
+++ b/tests/fixtures/optimizer/eliminate_joins.sql
@@ -0,0 +1,317 @@
+# title: Remove left join on distinct derived table
+SELECT
+  x.a
+FROM x
+LEFT JOIN (
+  SELECT DISTINCT
+    y.b
+  FROM y
+) AS y
+  ON x.b = y.b;
+SELECT
+  x.a
+FROM x;
+
+# title: Remove left join on grouped derived table
+SELECT
+  x.a
+FROM x
+LEFT JOIN (
+  SELECT
+    y.b,
+    SUM(y.c)
+  FROM y
+  GROUP BY y.b
+) AS y
+  ON x.b = y.b;
+SELECT
+  x.a
+FROM x;
+
+# title: Remove left join on aggregate derived table
+SELECT
+  x.a
+FROM x
+LEFT JOIN (
+  SELECT
+    SUM(y.b) AS b
+  FROM y
+) AS y
+  ON x.b = y.b;
+SELECT
+  x.a
+FROM x;
+
+# title: Noop - not all distinct columns in condition
+SELECT
+  x.a
+FROM x
+LEFT JOIN (
+  SELECT DISTINCT
+    y.b,
+    y.c
+  FROM y
+) AS y
+  ON x.b = y.b;
+SELECT
+  x.a
+FROM x
+LEFT JOIN (
+  SELECT DISTINCT
+    y.b,
+    y.c
+  FROM y
+) AS y
+  ON x.b = y.b;
+
+# title: Noop - not all grouped columns in condition
+SELECT
+  x.a
+FROM x
+LEFT JOIN (
+  SELECT
+    y.b,
+    y.c
+  FROM y
+  GROUP BY
+    y.b,
+    y.c
+) AS y
+  ON x.b = y.b;
+SELECT
+  x.a
+FROM x
+LEFT JOIN (
+  SELECT
+    y.b,
+    y.c
+  FROM y
+  GROUP BY
+    y.b,
+    y.c
+) AS y
+  ON x.b = y.b;
+
+# title: Noop - not left join
+SELECT
+  x.a
+FROM x
+JOIN (
+  SELECT DISTINCT
+    y.b
+  FROM y
+) AS y
+  ON x.b = y.b;
+SELECT
+  x.a
+FROM x
+JOIN (
+  SELECT DISTINCT
+    y.b
+  FROM y
+) AS y
+  ON x.b = y.b;
+
+# title: Noop - unqualified columns
+SELECT
+  a
+FROM x
+LEFT JOIN (
+  SELECT DISTINCT
+    y.b
+  FROM y
+) AS y
+  ON x.b = y.b;
+SELECT
+  a
+FROM x
+LEFT JOIN (
+  SELECT DISTINCT
+    y.b
+  FROM y
+) AS y
+  ON x.b = y.b;
+
+# title: Noop - cross join
+SELECT
+  a
+FROM x
+CROSS JOIN (
+  SELECT DISTINCT
+    y.b
+  FROM y
+) AS y;
+SELECT
+  a
+FROM x
+CROSS JOIN (
+  SELECT DISTINCT
+    y.b
+  FROM y
+) AS y;
+
+# title: Noop - column is used
+SELECT
+  x.a,
+  y.b
+FROM x
+LEFT JOIN (
+  SELECT DISTINCT
+    y.b
+  FROM y
+) AS y
+  ON x.b = y.b;
+SELECT
+  x.a,
+  y.b
+FROM x
+LEFT JOIN (
+  SELECT DISTINCT
+    y.b
+  FROM y
+) AS y
+  ON x.b = y.b;
+
+# title: Multiple group by columns
+SELECT
+  x.a
+FROM x
+LEFT JOIN (
+  SELECT
+    y.b AS b,
+    y.c + 1 AS d,
+    COUNT(1)
+  FROM y
+  GROUP BY y.b, y.c + 1
+) AS y
+  ON x.b = y.b
+  AND 1 = y.d;
+SELECT
+  x.a
+FROM x;
+
+# title: Chained left joins
+SELECT
+  x.a
+FROM x
+LEFT JOIN (
+  SELECT
+    y.b AS b
+  FROM y
+  GROUP BY y.b
+) AS y
+  ON x.b = y.b
+LEFT JOIN (
+  SELECT
+    y.b AS c
+  FROM y
+  GROUP BY y.b
+) AS z
+  ON y.b = z.c;
+SELECT
+  x.a
+FROM x;
+
+# title: CTE
+WITH z AS (
+  SELECT DISTINCT
+    y.b
+  FROM y
+)
+SELECT
+  x.a
+FROM x
+LEFT JOIN z
+  ON x.b = z.b;
+WITH z AS (
+  SELECT DISTINCT
+    y.b
+  FROM y
+)
+SELECT
+  x.a
+FROM x;
+
+# title: Noop - Not all grouped expressions are in outputs
+SELECT
+  x.a
+FROM x
+LEFT JOIN (
+  SELECT
+    y.b
+  FROM y
+  GROUP BY
+    y.b,
+    y.c
+) AS y
+  ON x.b = y.b;
+SELECT
+  x.a
+FROM x
+LEFT JOIN (
+  SELECT
+    y.b
+  FROM y
+  GROUP BY
+    y.b,
+    y.c
+) AS y
+  ON x.b = y.b;
+
+# title: Cross join on aggregate derived table
+SELECT
+  x.a
+FROM x
+CROSS JOIN (
+  SELECT
+    SUM(y.b) AS b
+  FROM y
+) AS y;
+SELECT
+  x.a
+FROM x;
+
+# title: Cross join on derived table with LIMIT 1
+SELECT
+  x.a
+FROM x
+CROSS JOIN (
+  SELECT
+    y.b AS b
+  FROM y
+  LIMIT 1
+) AS y;
+SELECT
+  x.a
+FROM x;
+
+# title: Cross join on derived table with no FROM clause
+SELECT
+  x.a
+FROM x
+CROSS JOIN (
+  SELECT
+    1 AS b,
+    2 AS c
+) AS y;
+SELECT
+  x.a
+FROM x;
+
+# title: Noop - cross join on non-aggregate subquery
+SELECT
+  x.a
+FROM x
+CROSS JOIN (
+  SELECT
+    y.b
+  FROM y
+) AS y;
+SELECT
+  x.a
+FROM x
+CROSS JOIN (
+  SELECT
+    y.b
+  FROM y
+) AS y;
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index c8186cc..a82e1ed 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -187,3 +187,103 @@ FROM (
   ) AS x
 ) AS x;
 SELECT /*+ BROADCAST(x) */ x.a AS a, x.c AS c FROM (SELECT x.a AS a, COUNT(1) AS c FROM x AS x GROUP BY x.a) AS x;
+
+# title: Test preventing merge of window expressions where clause
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  t1.a,
+  t1.b
+FROM
+  t1
+WHERE
+  row_num = 1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 WHERE t1.row_num = 1;
+
+# title: Test preventing merge of window expressions join clause
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  t1.a,
+  t1.b
+FROM t1 JOIN y ON t1.a = y.c AND t1.row_num = 1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 JOIN y AS y ON t1.a = y.c AND t1.row_num = 1;
+
+# title: Test preventing merge of window expressions agg function
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  SUM(t1.row_num) as total_rows
+FROM
+  t1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT SUM(t1.row_num) AS total_rows FROM t1;
+
+# title: Test prevent merging of window if in group by func
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  t1.row_num AS row_num,
+  SUM(t1.a) AS total
+FROM
+  t1
+GROUP BY t1.row_num
+ORDER BY t1.row_num;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, SUM(t1.a) AS total FROM t1 GROUP BY t1.row_num ORDER BY t1.row_num;
+
+# title: Test prevent merging of window if in order by func
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  t1.row_num AS row_num,
+  t1.a AS a
+FROM
+  t1
+ORDER BY t1.row_num, t1.a;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, t1.a AS a FROM t1 ORDER BY t1.row_num, t1.a;
+
+# title: Test allow merging of window function
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  t1.a,
+  t1.b,
+  t1.row_num
+FROM
+  t1;
+SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index ab4f769..eb7e9cb 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -105,9 +105,7 @@ LEFT JOIN "_u_0" AS "_u_0"
 JOIN "y" AS "y"
   ON "x"."b" = "y"."b"
 WHERE
-  "_u_0"."_col_0" >= 0
-  AND "x"."a" > 1
-  AND NOT "_u_0"."_u_1" IS NULL
+  "_u_0"."_col_0" >= 0 AND "x"."a" > 1 AND NOT "_u_0"."_u_1" IS NULL
 GROUP BY
   "x"."a";
 
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index 676cb96..ef591ec 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -30,3 +30,6 @@ SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.
 
 SELECT x.a AS a FROM x AS x CROSS JOIN (SELECT * FROM y AS y) AS y WHERE x.a = 1 AND x.b = 1 AND y.a = x.a AND y.a = 1;
 SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.a = x.a AND TRUE WHERE x.a = 1 AND x.b = 1 AND TRUE AND TRUE;
+
+with t1 as (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num FROM x) SELECT t1.a, t1.b FROM t1 WHERE row_num = 1;
+WITH t1 AS (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x) SELECT t1.a, t1.b FROM t1 WHERE row_num = 1;
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index 936a0af..b91205c 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -28,13 +28,15 @@ SELECT
   SUM("lineitem"."l_quantity") AS "sum_qty",
   SUM("lineitem"."l_extendedprice") AS "sum_base_price",
   SUM("lineitem"."l_extendedprice" * (
-      1 - "lineitem"."l_discount"
+    1 - "lineitem"."l_discount"
   )) AS "sum_disc_price",
-  SUM("lineitem"."l_extendedprice" * (
+  SUM(
+    "lineitem"."l_extendedprice" * (
       1 - "lineitem"."l_discount"
     ) * (
       1 + "lineitem"."l_tax"
-  )) AS "sum_charge",
+    )
+  ) AS "sum_charge",
   AVG("lineitem"."l_quantity") AS "avg_qty",
   AVG("lineitem"."l_extendedprice") AS "avg_price",
   AVG("lineitem"."l_discount") AS "avg_disc",
@@ -186,7 +188,7 @@ limit
 SELECT
   "lineitem"."l_orderkey" AS "l_orderkey",
   SUM("lineitem"."l_extendedprice" * (
-      1 - "lineitem"."l_discount"
+    1 - "lineitem"."l_discount"
   )) AS "revenue",
   CAST("orders"."o_orderdate" AS TEXT) AS "o_orderdate",
   "orders"."o_shippriority" AS "o_shippriority"
@@ -286,7 +288,7 @@ order by
 SELECT
   "nation"."n_name" AS "n_name",
   SUM("lineitem"."l_extendedprice" * (
-      1 - "lineitem"."l_discount"
+    1 - "lineitem"."l_discount"
   )) AS "revenue"
 FROM "customer" AS "customer"
 JOIN "orders" AS "orders"
@@ -377,15 +379,14 @@ WITH "n1" AS (
     "nation"."n_name" AS "n_name"
   FROM "nation" AS "nation"
   WHERE
-    "nation"."n_name" = 'FRANCE'
-    OR "nation"."n_name" = 'GERMANY'
+    "nation"."n_name" = 'FRANCE' OR "nation"."n_name" = 'GERMANY'
 )
 SELECT
   "n1"."n_name" AS "supp_nation",
   "n2"."n_name" AS "cust_nation",
   EXTRACT(year FROM "lineitem"."l_shipdate") AS "l_year",
   SUM("lineitem"."l_extendedprice" * (
-      1 - "lineitem"."l_discount"
+    1 - "lineitem"."l_discount"
   )) AS "revenue"
 FROM "supplier" AS "supplier"
 JOIN "lineitem" AS "lineitem"
@@ -400,12 +401,10 @@ JOIN "n1" AS "n1"
 JOIN "n1" AS "n2"
   ON "customer"."c_nationkey" = "n2"."n_nationkey"
   AND (
-    "n1"."n_name" = 'FRANCE'
-    OR "n2"."n_name" = 'FRANCE'
+    "n1"."n_name" = 'FRANCE' OR "n2"."n_name" = 'FRANCE'
   )
   AND (
-    "n1"."n_name" = 'GERMANY'
-    OR "n2"."n_name" = 'GERMANY'
+    "n1"."n_name" = 'GERMANY' OR "n2"."n_name" = 'GERMANY'
   )
 GROUP BY
   "n1"."n_name",
@@ -458,14 +457,16 @@ order by
         o_year;
 SELECT
   EXTRACT(year FROM "orders"."o_orderdate") AS "o_year",
-  SUM(CASE
+  SUM(
+    CASE
       WHEN "nation_2"."n_name" = 'BRAZIL'
       THEN "lineitem"."l_extendedprice" * (
-          1 - "lineitem"."l_discount"
-        )
+        1 - "lineitem"."l_discount"
+      )
       ELSE 0
-  END) / SUM("lineitem"."l_extendedprice" * (
-      1 - "lineitem"."l_discount"
+    END
+  ) / SUM("lineitem"."l_extendedprice" * (
+    1 - "lineitem"."l_discount"
   )) AS "mkt_share"
 FROM "part" AS "part"
 JOIN "region" AS "region"
@@ -529,9 +530,11 @@ order by
 SELECT
   "nation"."n_name" AS "nation",
   EXTRACT(year FROM "orders"."o_orderdate") AS "o_year",
-  SUM("lineitem"."l_extendedprice" * (
+  SUM(
+    "lineitem"."l_extendedprice" * (
       1 - "lineitem"."l_discount"
-  ) - "partsupp"."ps_supplycost" * "lineitem"."l_quantity") AS "sum_profit"
+    ) - "partsupp"."ps_supplycost" * "lineitem"."l_quantity"
+  ) AS "sum_profit"
 FROM "part" AS "part"
 JOIN "lineitem" AS "lineitem"
   ON "part"."p_partkey" = "lineitem"."l_partkey"
@@ -593,7 +596,7 @@ SELECT
   "customer"."c_custkey" AS "c_custkey",
   "customer"."c_name" AS "c_name",
   SUM("lineitem"."l_extendedprice" * (
-      1 - "lineitem"."l_discount"
+    1 - "lineitem"."l_discount"
   )) AS "revenue",
   "customer"."c_acctbal" AS "c_acctbal",
   "nation"."n_name" AS "n_name",
@@ -606,8 +609,7 @@ JOIN "orders" AS "orders"
   AND "orders"."o_orderdate" < CAST('1994-01-01' AS DATE)
   AND "orders"."o_orderdate" >= CAST('1993-10-01' AS DATE)
 JOIN "lineitem" AS "lineitem"
-  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
-  AND "lineitem"."l_returnflag" = 'R'
+  ON "lineitem"."l_orderkey" = "orders"."o_orderkey" AND "lineitem"."l_returnflag" = 'R'
 JOIN "nation" AS "nation"
   ON "customer"."c_nationkey" = "nation"."n_nationkey"
 GROUP BY
@@ -721,18 +723,20 @@ order by
         l_shipmode;
 SELECT
   "lineitem"."l_shipmode" AS "l_shipmode",
-  SUM(CASE
-      WHEN "orders"."o_orderpriority" = '1-URGENT'
-        OR "orders"."o_orderpriority" = '2-HIGH'
+  SUM(
+    CASE
+      WHEN "orders"."o_orderpriority" = '1-URGENT' OR "orders"."o_orderpriority" = '2-HIGH'
       THEN 1
       ELSE 0
-  END) AS "high_line_count",
-  SUM(CASE
-      WHEN "orders"."o_orderpriority" <> '1-URGENT'
-        AND "orders"."o_orderpriority" <> '2-HIGH'
+    END
+  ) AS "high_line_count",
+  SUM(
+    CASE
+      WHEN "orders"."o_orderpriority" <> '1-URGENT' AND "orders"."o_orderpriority" <> '2-HIGH'
       THEN 1
       ELSE 0
-  END) AS "low_line_count"
+    END
+  ) AS "low_line_count"
 FROM "orders" AS "orders"
 JOIN "lineitem" AS "lineitem"
   ON "lineitem"."l_commitdate" < "lineitem"."l_receiptdate"
@@ -813,14 +817,16 @@ where
         and l_shipdate >= date '1995-09-01'
         and l_shipdate < date '1995-09-01' + interval '1' month;
 SELECT
-  100.00 * SUM(CASE
+  100.00 * SUM(
+    CASE
       WHEN "part"."p_type" LIKE 'PROMO%'
       THEN "lineitem"."l_extendedprice" * (
-          1 - "lineitem"."l_discount"
-        )
+        1 - "lineitem"."l_discount"
+      )
       ELSE 0
-  END) / SUM("lineitem"."l_extendedprice" * (
-      1 - "lineitem"."l_discount"
+    END
+  ) / SUM("lineitem"."l_extendedprice" * (
+    1 - "lineitem"."l_discount"
   )) AS "promo_revenue"
 FROM "lineitem" AS "lineitem"
 JOIN "part" AS "part"
@@ -866,7 +872,7 @@ WITH "revenue" AS (
   SELECT
     "lineitem"."l_suppkey" AS "supplier_no",
     SUM("lineitem"."l_extendedprice" * (
-        1 - "lineitem"."l_discount"
+      1 - "lineitem"."l_discount"
     )) AS "total_revenue"
   FROM "lineitem" AS "lineitem"
   WHERE
@@ -997,8 +1003,7 @@ JOIN "part" AS "part"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "_u_0"."_u_1" = "part"."p_partkey"
 WHERE
-  "lineitem"."l_quantity" < "_u_0"."_col_0"
-  AND NOT "_u_0"."_u_1" IS NULL;
+  "lineitem"."l_quantity" < "_u_0"."_col_0" AND NOT "_u_0"."_u_1" IS NULL;
 
 --------------------------------------
 -- TPC-H 18
@@ -1114,7 +1119,7 @@ where
         );
 SELECT
   SUM("lineitem"."l_extendedprice" * (
-      1 - "lineitem"."l_discount"
+    1 - "lineitem"."l_discount"
   )) AS "revenue"
 FROM "lineitem" AS "lineitem"
 JOIN "part" AS "part"
@@ -1233,8 +1238,7 @@ WITH "_u_0" AS (
     "partsupp"."ps_suppkey" AS "ps_suppkey"
   FROM "partsupp" AS "partsupp"
   LEFT JOIN "_u_0" AS "_u_0"
-    ON "_u_0"."_u_1" = "partsupp"."ps_partkey"
-    AND "_u_0"."_u_2" = "partsupp"."ps_suppkey"
+    ON "_u_0"."_u_1" = "partsupp"."ps_partkey" AND "_u_0"."_u_2" = "partsupp"."ps_suppkey"
   LEFT JOIN "_u_3" AS "_u_3"
     ON "partsupp"."ps_partkey" = "_u_3"."p_partkey"
   WHERE
@@ -1252,8 +1256,7 @@ FROM "supplier" AS "supplier"
 LEFT JOIN "_u_4" AS "_u_4"
   ON "supplier"."s_suppkey" = "_u_4"."ps_suppkey"
 JOIN "nation" AS "nation"
-  ON "nation"."n_name" = 'CANADA'
-  AND "supplier"."s_nationkey" = "nation"."n_nationkey"
+  ON "nation"."n_name" = 'CANADA' AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 WHERE
   NOT "_u_4"."ps_suppkey" IS NULL
 ORDER BY
@@ -1332,8 +1335,7 @@ LEFT JOIN "_u_0" AS "_u_0"
 LEFT JOIN "_u_2" AS "_u_2"
   ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey"
 JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
-  AND "orders"."o_orderstatus" = 'F'
+  ON "orders"."o_orderkey" = "lineitem"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
 JOIN "nation" AS "nation"
   ON "nation"."n_name" = 'SAUDI ARABIA'
   AND "supplier"."s_nationkey" = "nation"."n_nationkey"
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index 9c4bd27..f53121a 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -108,14 +108,11 @@ LEFT JOIN (
     ARRAY_AGG(y.b) AS _u_13
   FROM y
   WHERE
-    TRUE
-    AND TRUE
-    AND TRUE
+    TRUE AND TRUE AND TRUE
   GROUP BY
     y.a
 ) AS "_u_11"
-  ON "_u_11"."_u_12" = x.a
-  AND "_u_11"."_u_12" = x.b
+  ON "_u_11"."_u_12" = x.a AND "_u_11"."_u_12" = x.b
 LEFT JOIN (
   SELECT
     y.a AS a
@@ -131,38 +128,30 @@ WHERE
   AND NOT "_u_1"."b" IS NULL
   AND NOT "_u_2"."a" IS NULL
   AND (
-    x.a = "_u_3".b
-    AND NOT "_u_3"."_u_4" IS NULL
+    x.a = "_u_3".b AND NOT "_u_3"."_u_4" IS NULL
   )
   AND (
-    x.a > "_u_5".b
-    AND NOT "_u_5"."_u_6" IS NULL
+    x.a > "_u_5".b AND NOT "_u_5"."_u_6" IS NULL
   )
   AND (
-    None = "_u_7".a
-    AND NOT "_u_7".a IS NULL
+    None = "_u_7".a AND NOT "_u_7".a IS NULL
   )
   AND NOT (
-    x.a = "_u_8".a
-    AND NOT "_u_8".a IS NULL
+    x.a = "_u_8".a AND NOT "_u_8".a IS NULL
   )
   AND (
-    ARRAY_ANY("_u_9".a, _x -> _x = x.a)
-    AND NOT "_u_9"."_u_10" IS NULL
+    ARRAY_ANY("_u_9".a, _x -> _x = x.a) AND NOT "_u_9"."_u_10" IS NULL
   )
   AND (
     (
       (
-        x.a < "_u_11".a
-        AND NOT "_u_11"."_u_12" IS NULL
-      )
-      AND NOT "_u_11"."_u_12" IS NULL
+        x.a < "_u_11".a AND NOT "_u_11"."_u_12" IS NULL
+      ) AND NOT "_u_11"."_u_12" IS NULL
     )
     AND ARRAY_ANY("_u_11"."_u_13", "_x" -> "_x" <> x.d)
   )
   AND (
-    NOT "_u_14".a IS NULL
-    AND NOT "_u_14".a IS NULL
+    NOT "_u_14".a IS NULL AND NOT "_u_14".a IS NULL
   )
   AND x.a IN (
     SELECT
@@ -203,4 +192,3 @@ WHERE
       y.a = x.a
     OFFSET 10
   );
-