Adding upstream version 16.2.1.upstream/16.2.1

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

1 files changed, 60 insertions, 62 deletions

diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index a25e247..942295e 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -117,12 +117,12 @@ WITH "region_2" AS (
     MIN("partsupp"."ps_supplycost") AS "_col_0",
     "partsupp"."ps_partkey" AS "_u_1"
   FROM "partsupp_2" AS "partsupp"
-  CROSS JOIN "region_2" AS "region"
-  JOIN "nation" AS "nation"
-    ON "nation"."n_regionkey" = "region"."r_regionkey"
   JOIN "supplier" AS "supplier"
+    ON "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+  JOIN "nation" AS "nation"
     ON "supplier"."s_nationkey" = "nation"."n_nationkey"
-    AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+  JOIN "region_2" AS "region"
+    ON "nation"."n_regionkey" = "region"."r_regionkey"
   GROUP BY
     "partsupp"."ps_partkey"
 )
@@ -137,6 +137,8 @@ SELECT
   "supplier"."s_comment" AS "s_comment"
 FROM "part" AS "part"
 CROSS JOIN "region_2" AS "region"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "part"."p_partkey" = "_u_0"."_u_1"
 JOIN "nation" AS "nation"
   ON "nation"."n_regionkey" = "region"."r_regionkey"
 JOIN "partsupp_2" AS "partsupp"
@@ -144,8 +146,6 @@ JOIN "partsupp_2" AS "partsupp"
 JOIN "supplier" AS "supplier"
   ON "supplier"."s_nationkey" = "nation"."n_nationkey"
   AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "part"."p_partkey" = "_u_0"."_u_1"
 WHERE
   "part"."p_size" = 15
   AND "part"."p_type" LIKE '%BRASS'
@@ -294,16 +294,15 @@ JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
   AND CAST("orders"."o_orderdate" AS DATE) < CAST('1995-01-01' AS DATE)
   AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1994-01-01' AS DATE)
-JOIN "region" AS "region"
-  ON "region"."r_name" = 'ASIA'
-JOIN "nation" AS "nation"
-  ON "nation"."n_regionkey" = "region"."r_regionkey"
 JOIN "supplier" AS "supplier"
   ON "customer"."c_nationkey" = "supplier"."s_nationkey"
-  AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 JOIN "lineitem" AS "lineitem"
   ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
   AND "lineitem"."l_suppkey" = "supplier"."s_suppkey"
+JOIN "nation" AS "nation"
+  ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+JOIN "region" AS "region"
+  ON "nation"."n_regionkey" = "region"."r_regionkey" AND "region"."r_name" = 'ASIA'
 GROUP BY
   "nation"."n_name"
 ORDER BY
@@ -373,14 +372,6 @@ order by
         supp_nation,
         cust_nation,
         l_year;
-WITH "n1" AS (
-  SELECT
-    "nation"."n_nationkey" AS "n_nationkey",
-    "nation"."n_name" AS "n_name"
-  FROM "nation" AS "nation"
-  WHERE
-    "nation"."n_name" = 'FRANCE' OR "nation"."n_name" = 'GERMANY'
-)
 SELECT
   "n1"."n_name" AS "supp_nation",
   "n2"."n_name" AS "cust_nation",
@@ -393,20 +384,26 @@ JOIN "lineitem" AS "lineitem"
   ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
   AND CAST("lineitem"."l_shipdate" AS DATE) <= CAST('1996-12-31' AS DATE)
   AND CAST("lineitem"."l_shipdate" AS DATE) >= CAST('1995-01-01' AS DATE)
-JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
-JOIN "customer" AS "customer"
-  ON "customer"."c_custkey" = "orders"."o_custkey"
-JOIN "n1" AS "n1"
-  ON "supplier"."s_nationkey" = "n1"."n_nationkey"
-JOIN "n1" AS "n2"
-  ON "customer"."c_nationkey" = "n2"."n_nationkey"
-  AND (
+JOIN "nation" AS "n1"
+  ON (
+    "n1"."n_name" = 'FRANCE' OR "n1"."n_name" = 'GERMANY'
+  )
+  AND "supplier"."s_nationkey" = "n1"."n_nationkey"
+JOIN "nation" AS "n2"
+  ON (
     "n1"."n_name" = 'FRANCE' OR "n2"."n_name" = 'FRANCE'
   )
   AND (
     "n1"."n_name" = 'GERMANY' OR "n2"."n_name" = 'GERMANY'
   )
+  AND (
+    "n2"."n_name" = 'FRANCE' OR "n2"."n_name" = 'GERMANY'
+  )
+JOIN "customer" AS "customer"
+  ON "customer"."c_nationkey" = "n2"."n_nationkey"
+JOIN "orders" AS "orders"
+  ON "customer"."c_custkey" = "orders"."o_custkey"
+  AND "orders"."o_orderkey" = "lineitem"."l_orderkey"
 GROUP BY
   "n1"."n_name",
   "n2"."n_name",
@@ -460,7 +457,7 @@ SELECT
   EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATE)) AS "o_year",
   SUM(
     CASE
-      WHEN "nation_2"."n_name" = 'BRAZIL'
+      WHEN "n2"."n_name" = 'BRAZIL'
       THEN "lineitem"."l_extendedprice" * (
         1 - "lineitem"."l_discount"
       )
@@ -472,21 +469,21 @@ SELECT
 FROM "part" AS "part"
 JOIN "region" AS "region"
   ON "region"."r_name" = 'AMERICA'
-JOIN "nation" AS "nation"
-  ON "nation"."n_regionkey" = "region"."r_regionkey"
+JOIN "lineitem" AS "lineitem"
+  ON "part"."p_partkey" = "lineitem"."l_partkey"
+JOIN "nation" AS "n1"
+  ON "n1"."n_regionkey" = "region"."r_regionkey"
 JOIN "customer" AS "customer"
-  ON "customer"."c_nationkey" = "nation"."n_nationkey"
+  ON "customer"."c_nationkey" = "n1"."n_nationkey"
+JOIN "supplier" AS "supplier"
+  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+JOIN "nation" AS "n2"
+  ON "supplier"."s_nationkey" = "n2"."n_nationkey"
 JOIN "orders" AS "orders"
-  ON "orders"."o_custkey" = "customer"."c_custkey"
+  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
+  AND "orders"."o_custkey" = "customer"."c_custkey"
   AND CAST("orders"."o_orderdate" AS DATE) <= CAST('1996-12-31' AS DATE)
   AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1995-01-01' AS DATE)
-JOIN "lineitem" AS "lineitem"
-  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
-  AND "part"."p_partkey" = "lineitem"."l_partkey"
-JOIN "supplier" AS "supplier"
-  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
-JOIN "nation" AS "nation_2"
-  ON "supplier"."s_nationkey" = "nation_2"."n_nationkey"
 WHERE
   "part"."p_type" = 'ECONOMY ANODIZED STEEL'
 GROUP BY
@@ -540,13 +537,13 @@ SELECT
 FROM "part" AS "part"
 JOIN "lineitem" AS "lineitem"
   ON "part"."p_partkey" = "lineitem"."l_partkey"
-JOIN "supplier" AS "supplier"
-  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+JOIN "orders" AS "orders"
+  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
 JOIN "partsupp" AS "partsupp"
   ON "partsupp"."ps_partkey" = "lineitem"."l_partkey"
   AND "partsupp"."ps_suppkey" = "lineitem"."l_suppkey"
-JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
+JOIN "supplier" AS "supplier"
+  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
 JOIN "nation" AS "nation"
   ON "supplier"."s_nationkey" = "nation"."n_nationkey"
 WHERE
@@ -606,14 +603,14 @@ SELECT
   "customer"."c_phone" AS "c_phone",
   "customer"."c_comment" AS "c_comment"
 FROM "customer" AS "customer"
+JOIN "nation" AS "nation"
+  ON "customer"."c_nationkey" = "nation"."n_nationkey"
 JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
   AND CAST("orders"."o_orderdate" AS DATE) < CAST('1994-01-01' AS DATE)
   AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1993-10-01' AS DATE)
 JOIN "lineitem" AS "lineitem"
   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
   "customer"."c_custkey",
   "customer"."c_name",
@@ -681,11 +678,11 @@ SELECT
   "partsupp"."ps_partkey" AS "ps_partkey",
   SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") AS "value"
 FROM "partsupp" AS "partsupp"
+CROSS JOIN "_u_0" AS "_u_0"
 JOIN "supplier_2" AS "supplier"
   ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
 JOIN "nation_2" AS "nation"
   ON "supplier"."s_nationkey" = "nation"."n_nationkey"
-CROSS JOIN "_u_0" AS "_u_0"
 GROUP BY
   "partsupp"."ps_partkey"
 HAVING
@@ -950,13 +947,13 @@ SELECT
   "part"."p_size" AS "p_size",
   COUNT(DISTINCT "partsupp"."ps_suppkey") AS "supplier_cnt"
 FROM "partsupp" AS "partsupp"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey"
 JOIN "part" AS "part"
   ON "part"."p_brand" <> 'Brand#45'
   AND "part"."p_partkey" = "partsupp"."ps_partkey"
   AND "part"."p_size" IN (49, 14, 23, 45, 19, 3, 36, 9)
   AND NOT "part"."p_type" LIKE 'MEDIUM POLISHED%'
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey"
 WHERE
   "_u_0"."s_suppkey" IS NULL
 GROUP BY
@@ -1066,10 +1063,10 @@ SELECT
 FROM "customer" AS "customer"
 JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
-JOIN "lineitem" AS "lineitem"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "orders"."o_orderkey" = "_u_0"."l_orderkey"
+JOIN "lineitem" AS "lineitem"
+  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
 WHERE
   NOT "_u_0"."l_orderkey" IS NULL
 GROUP BY
@@ -1260,10 +1257,10 @@ SELECT
   "supplier"."s_name" AS "s_name",
   "supplier"."s_address" AS "s_address"
 FROM "supplier" AS "supplier"
-JOIN "nation" AS "nation"
-  ON "nation"."n_name" = 'CANADA' AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 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"
 WHERE
   NOT "_u_4"."ps_suppkey" IS NULL
 ORDER BY
@@ -1334,24 +1331,24 @@ SELECT
   "supplier"."s_name" AS "s_name",
   COUNT(*) AS "numwait"
 FROM "supplier" AS "supplier"
-JOIN "lineitem" AS "lineitem"
-  ON "lineitem"."l_receiptdate" > "lineitem"."l_commitdate"
-  AND "supplier"."s_suppkey" = "lineitem"."l_suppkey"
-JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
+JOIN "lineitem" AS "l1"
+  ON "l1"."l_receiptdate" > "l1"."l_commitdate"
+  AND "supplier"."s_suppkey" = "l1"."l_suppkey"
 JOIN "nation" AS "nation"
   ON "nation"."n_name" = 'SAUDI ARABIA'
   AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "_u_0"."l_orderkey" = "lineitem"."l_orderkey"
+  ON "_u_0"."l_orderkey" = "l1"."l_orderkey"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey"
+  ON "_u_2"."l_orderkey" = "l1"."l_orderkey"
+JOIN "orders" AS "orders"
+  ON "orders"."o_orderkey" = "l1"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
 WHERE
   (
     "_u_2"."l_orderkey" IS NULL
-    OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "lineitem"."l_suppkey")
+    OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "l1"."l_suppkey")
   )
-  AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "lineitem"."l_suppkey")
+  AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "l1"."l_suppkey")
   AND NOT "_u_0"."l_orderkey" IS NULL
 GROUP BY
   "supplier"."s_name"
@@ -1430,3 +1427,4 @@ GROUP BY
   SUBSTRING("customer"."c_phone", 1, 2)
 ORDER BY
   "cntrycode";
+