Adding upstream version 10.2.6.upstream/10.2.6

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

1 files changed, 30 insertions, 21 deletions

diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index 4893743..9c1f138 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -190,7 +190,7 @@ SELECT
   SUM("lineitem"."l_extendedprice" * (
     1 - "lineitem"."l_discount"
   )) AS "revenue",
-  CAST("orders"."o_orderdate" AS TEXT) AS "o_orderdate",
+  "orders"."o_orderdate" AS "o_orderdate",
   "orders"."o_shippriority" AS "o_shippriority"
 FROM "customer" AS "customer"
 JOIN "orders" AS "orders"
@@ -326,7 +326,8 @@ SELECT
   SUM("lineitem"."l_extendedprice" * "lineitem"."l_discount") AS "revenue"
 FROM "lineitem" AS "lineitem"
 WHERE
-  "lineitem"."l_discount" BETWEEN 0.05 AND 0.07
+  "lineitem"."l_discount" <= 0.07
+  AND "lineitem"."l_discount" >= 0.05
   AND "lineitem"."l_quantity" < 24
   AND CAST("lineitem"."l_shipdate" AS DATE) < CAST('1995-01-01' AS DATE)
   AND CAST("lineitem"."l_shipdate" AS DATE) >= CAST('1994-01-01' AS DATE);
@@ -344,7 +345,7 @@ from
                 select
                         n1.n_name as supp_nation,
                         n2.n_name as cust_nation,
-                        extract(year from l_shipdate) as l_year,
+                        extract(year from cast(l_shipdate as date)) as l_year,
                         l_extendedprice * (1 - l_discount) as volume
                 from
                         supplier,
@@ -384,13 +385,14 @@ WITH "n1" AS (
 SELECT
   "n1"."n_name" AS "supp_nation",
   "n2"."n_name" AS "cust_nation",
-  EXTRACT(year FROM CAST("lineitem"."l_shipdate" AS DATETIME)) AS "l_year",
+  EXTRACT(year FROM CAST("lineitem"."l_shipdate" AS DATE)) AS "l_year",
   SUM("lineitem"."l_extendedprice" * (
     1 - "lineitem"."l_discount"
   )) AS "revenue"
 FROM "supplier" AS "supplier"
 JOIN "lineitem" AS "lineitem"
-  ON CAST("lineitem"."l_shipdate" AS DATE) BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE)
+  ON 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)
   AND "supplier"."s_suppkey" = "lineitem"."l_suppkey"
 JOIN "orders" AS "orders"
   ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
@@ -409,7 +411,7 @@ JOIN "n1" AS "n2"
 GROUP BY
   "n1"."n_name",
   "n2"."n_name",
-  EXTRACT(year FROM CAST("lineitem"."l_shipdate" AS DATETIME))
+  EXTRACT(year FROM CAST("lineitem"."l_shipdate" AS DATE))
 ORDER BY
   "supp_nation",
   "cust_nation",
@@ -427,7 +429,7 @@ select
 from
         (
                 select
-                        extract(year from o_orderdate) as o_year,
+                        extract(year from cast(o_orderdate as date)) as o_year,
                         l_extendedprice * (1 - l_discount) as volume,
                         n2.n_name as nation
                 from
@@ -456,7 +458,7 @@ group by
 order by
         o_year;
 SELECT
-  EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATETIME)) AS "o_year",
+  EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATE)) AS "o_year",
   SUM(
     CASE
       WHEN "nation_2"."n_name" = 'BRAZIL'
@@ -477,7 +479,8 @@ JOIN "customer" AS "customer"
   ON "customer"."c_nationkey" = "nation"."n_nationkey"
 JOIN "orders" AS "orders"
   ON "orders"."o_custkey" = "customer"."c_custkey"
-  AND CAST("orders"."o_orderdate" AS DATE) BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE)
+  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"
@@ -488,7 +491,7 @@ JOIN "nation" AS "nation_2"
 WHERE
   "part"."p_type" = 'ECONOMY ANODIZED STEEL'
 GROUP BY
-  EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATETIME))
+  EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATE))
 ORDER BY
   "o_year";
 
@@ -503,7 +506,7 @@ from
         (
                 select
                         n_name as nation,
-                        extract(year from o_orderdate) as o_year,
+                        extract(year from cast(o_orderdate as date)) as o_year,
                         l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
                 from
                         part,
@@ -529,7 +532,7 @@ order by
         o_year desc;
 SELECT
   "nation"."n_name" AS "nation",
-  EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATETIME)) AS "o_year",
+  EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATE)) AS "o_year",
   SUM(
     "lineitem"."l_extendedprice" * (
       1 - "lineitem"."l_discount"
@@ -551,7 +554,7 @@ WHERE
   "part"."p_name" LIKE '%green%'
 GROUP BY
   "nation"."n_name",
-  EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATETIME))
+  EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATE))
 ORDER BY
   "nation",
   "o_year" DESC;
@@ -1016,7 +1019,7 @@ select
         o_orderkey,
         o_orderdate,
         o_totalprice,
-        sum(l_quantity)
+        sum(l_quantity) total_quantity
 from
         customer,
         orders,
@@ -1060,7 +1063,7 @@ SELECT
   "orders"."o_orderkey" AS "o_orderkey",
   "orders"."o_orderdate" AS "o_orderdate",
   "orders"."o_totalprice" AS "o_totalprice",
-  SUM("lineitem"."l_quantity") AS "_col_5"
+  SUM("lineitem"."l_quantity") AS "total_quantity"
 FROM "customer" AS "customer"
 JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
@@ -1129,19 +1132,22 @@ JOIN "part" AS "part"
     "part"."p_brand" = 'Brand#12'
     AND "part"."p_container" IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
     AND "part"."p_partkey" = "lineitem"."l_partkey"
-    AND "part"."p_size" BETWEEN 1 AND 5
+    AND "part"."p_size" <= 5
+    AND "part"."p_size" >= 1
   )
   OR (
     "part"."p_brand" = 'Brand#23'
     AND "part"."p_container" IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
     AND "part"."p_partkey" = "lineitem"."l_partkey"
-    AND "part"."p_size" BETWEEN 1 AND 10
+    AND "part"."p_size" <= 10
+    AND "part"."p_size" >= 1
   )
   OR (
     "part"."p_brand" = 'Brand#34'
     AND "part"."p_container" IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
     AND "part"."p_partkey" = "lineitem"."l_partkey"
-    AND "part"."p_size" BETWEEN 1 AND 15
+    AND "part"."p_size" <= 15
+    AND "part"."p_size" >= 1
   )
 WHERE
   (
@@ -1152,7 +1158,8 @@ WHERE
     AND "part"."p_brand" = 'Brand#12'
     AND "part"."p_container" IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
     AND "part"."p_partkey" = "lineitem"."l_partkey"
-    AND "part"."p_size" BETWEEN 1 AND 5
+    AND "part"."p_size" <= 5
+    AND "part"."p_size" >= 1
   )
   OR (
     "lineitem"."l_quantity" <= 20
@@ -1162,7 +1169,8 @@ WHERE
     AND "part"."p_brand" = 'Brand#23'
     AND "part"."p_container" IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
     AND "part"."p_partkey" = "lineitem"."l_partkey"
-    AND "part"."p_size" BETWEEN 1 AND 10
+    AND "part"."p_size" <= 10
+    AND "part"."p_size" >= 1
   )
   OR (
     "lineitem"."l_quantity" <= 30
@@ -1172,7 +1180,8 @@ WHERE
     AND "part"."p_brand" = 'Brand#34'
     AND "part"."p_container" IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
     AND "part"."p_partkey" = "lineitem"."l_partkey"
-    AND "part"."p_size" BETWEEN 1 AND 15
+    AND "part"."p_size" <= 15
+    AND "part"."p_size" >= 1
   );
 
 --------------------------------------