From d6dd6db5060d5d7a4abbc132cb1240973e6f9487 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Thu, 22 Sep 2022 06:30:57 +0200 Subject: Adding upstream version 6.1.1. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 7 + tests/fixtures/optimizer/merge_derived_tables.sql | 63 ++ tests/fixtures/optimizer/optimizer.sql | 57 +- tests/fixtures/optimizer/tpc-h/tpc-h.sql | 761 +++++----------------- 4 files changed, 270 insertions(+), 618 deletions(-) create mode 100644 tests/fixtures/optimizer/merge_derived_tables.sql (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 40f11a2..1b4168c 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -318,6 +318,9 @@ SELECT 1 FROM a JOIN b ON a.foo = b.bar JOIN c ON a.foo = c.bar SELECT 1 FROM a LEFT JOIN b ON a.foo = b.bar JOIN c ON a.foo = c.bar SELECT 1 FROM a LEFT INNER JOIN b ON a.foo = b.bar SELECT 1 FROM a LEFT OUTER JOIN b ON a.foo = b.bar +SELECT 1 FROM a NATURAL JOIN b +SELECT 1 FROM a NATURAL LEFT JOIN b +SELECT 1 FROM a NATURAL LEFT OUTER JOIN b SELECT 1 FROM a OUTER JOIN b ON a.foo = b.bar SELECT 1 FROM a FULL JOIN b ON a.foo = b.bar SELECT 1 UNION ALL SELECT 2 @@ -329,6 +332,7 @@ SELECT 1 AS delete, 2 AS alter SELECT * FROM (x) SELECT * FROM ((x)) SELECT * FROM ((SELECT 1)) +SELECT * FROM (x LATERAL VIEW EXPLODE(y) JOIN foo) SELECT * FROM (SELECT 1) AS x SELECT * FROM (SELECT 1 UNION SELECT 2) AS x SELECT * FROM (SELECT 1 UNION ALL SELECT 2) AS x @@ -430,6 +434,7 @@ CREATE TEMPORARY VIEW x AS SELECT a FROM d CREATE TEMPORARY VIEW IF NOT EXISTS x AS SELECT a FROM d CREATE TEMPORARY VIEW x AS WITH y AS (SELECT 1) SELECT * FROM y CREATE TABLE z (a INT, b VARCHAR, c VARCHAR(100), d DECIMAL(5, 3)) +CREATE TABLE z (end INT) CREATE TABLE z (a ARRAY, b MAP, c DECIMAL(5, 3)) CREATE TABLE z (a INT, b VARCHAR COMMENT 'z', c VARCHAR(100) COMMENT 'z', d DECIMAL(5, 3)) CREATE TABLE z (a INT(11) DEFAULT UUID()) @@ -466,6 +471,7 @@ CACHE LAZY TABLE x OPTIONS('storageLevel' = 'value') AS SELECT 1 CACHE LAZY TABLE x OPTIONS('storageLevel' = 'value') AS WITH a AS (SELECT 1) SELECT a.* FROM a CACHE LAZY TABLE x AS WITH a AS (SELECT 1) SELECT a.* FROM a CACHE TABLE x AS WITH a AS (SELECT 1) SELECT a.* FROM a +CACHE TABLE x AS (SELECT 1 AS y) CALL catalog.system.iceberg_procedure_name(named_arg_1 => 'arg_1', named_arg_2 => 'arg_2') INSERT OVERWRITE TABLE a.b PARTITION(ds) SELECT x FROM y INSERT OVERWRITE TABLE a.b PARTITION(ds='YYYY-MM-DD') SELECT x FROM y @@ -512,3 +518,4 @@ SELECT ? AS ? FROM x WHERE b BETWEEN ? AND ? GROUP BY ?, 1 LIMIT ? WITH a AS ((SELECT b.foo AS foo, b.bar AS bar FROM b) UNION ALL (SELECT c.foo AS foo, c.bar AS bar FROM c)) SELECT * FROM a WITH a AS ((SELECT 1 AS b) UNION ALL (SELECT 1 AS b)) SELECT * FROM a SELECT (WITH x AS (SELECT 1 AS y) SELECT * FROM x) AS z +SELECT ((SELECT 1) + 1) diff --git a/tests/fixtures/optimizer/merge_derived_tables.sql b/tests/fixtures/optimizer/merge_derived_tables.sql new file mode 100644 index 0000000..c5aa7e9 --- /dev/null +++ b/tests/fixtures/optimizer/merge_derived_tables.sql @@ -0,0 +1,63 @@ +-- Simple +SELECT a, b FROM (SELECT a, b FROM x); +SELECT x.a AS a, x.b AS b FROM x AS x; + +-- 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; + +-- Double nesting +SELECT a, b FROM (SELECT a, b FROM (SELECT a, b FROM x)); +SELECT x.a AS a, x.b AS b FROM x AS x; + +-- WHERE clause is merged +SELECT a, SUM(b) FROM (SELECT a, b FROM x WHERE a > 1) GROUP BY a; +SELECT x.a AS a, SUM(x.b) AS "_col_1" FROM x AS x WHERE x.a > 1 GROUP BY x.a; + +-- Outer query has join +SELECT a, c FROM (SELECT a, b FROM x WHERE a > 1) AS x JOIN y ON x.b = y.b; +SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b WHERE x.a > 1; + +-- Join on derived table +SELECT a, c FROM x JOIN (SELECT b, c FROM y) AS y ON x.b = y.b; +SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b; + +-- Inner query has a join +SELECT a, c FROM (SELECT a, c FROM x JOIN y ON x.b = y.b); +SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b; + +-- Inner query has conflicting name in outer query +SELECT a, c FROM (SELECT q.a, q.b FROM x AS q) AS x JOIN y AS q ON x.b = q.b; +SELECT q_2.a AS a, q.c AS c FROM x AS q_2 JOIN y AS q ON q_2.b = q.b; + +-- Inner query has conflicting name in joined source +SELECT x.a, q.c FROM (SELECT a, x.b FROM x JOIN y AS q ON x.b = q.b) AS x JOIN y AS q ON x.b = q.b; +SELECT x.a AS a, q.c AS c FROM x AS x JOIN y AS q_2 ON x.b = q_2.b JOIN y AS q ON x.b = q.b; + +-- Inner query has multiple conflicting names +SELECT x.a, q.c, r.c FROM (SELECT q.a, r.b FROM x AS q JOIN y AS r ON q.b = r.b) AS x JOIN y AS q ON x.b = q.b JOIN y AS r ON x.b = r.b; +SELECT q_2.a AS a, q.c AS c, r.c AS c FROM x AS q_2 JOIN y AS r_2 ON q_2.b = r_2.b JOIN y AS q ON r_2.b = q.b JOIN y AS r ON r_2.b = r.b; + +-- Inner queries have conflicting names with each other +SELECT r.b FROM (SELECT b FROM x AS x) AS q JOIN (SELECT b FROM x) AS r ON q.b = r.b; +SELECT x_2.b AS b FROM x AS x JOIN x AS x_2 ON x.b = x_2.b; + +-- WHERE clause in joined derived table is merged +SELECT x.a, y.c FROM x JOIN (SELECT b, c FROM y WHERE c > 1) AS y; +SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y WHERE y.c > 1; + +-- WHERE clause in outer joined derived table is merged to ON clause +SELECT x.a, y.c FROM x LEFT JOIN (SELECT b, c FROM y WHERE c > 1) AS y; +SELECT x.a AS a, y.c AS c FROM x AS x LEFT JOIN y AS y ON y.c > 1; + +-- Comma JOIN in outer query +SELECT x.a, y.c FROM (SELECT a FROM x) AS x, (SELECT c FROM y) AS y; +SELECT x.a AS a, y.c AS c FROM x AS x, y AS y; + +-- Comma JOIN in inner query +SELECT x.a, x.c FROM (SELECT x.a, z.c FROM x, y AS z) AS x; +SELECT x.a AS a, z.c AS c FROM x AS x CROSS JOIN y AS z; + +-- (Regression) Column in ORDER BY +SELECT * FROM (SELECT * FROM (SELECT * FROM x)) ORDER BY a LIMIT 1; +SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a LIMIT 1; diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index f7bbdda..f1d0f7d 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -2,11 +2,7 @@ SELECT a, m FROM z LATERAL VIEW EXPLODE([1, 2]) q AS m; SELECT "z"."a" AS "a", "q"."m" AS "m" -FROM ( - SELECT - "z"."a" AS "a" - FROM "z" AS "z" -) AS "z" +FROM "z" AS "z" LATERAL VIEW EXPLODE(ARRAY(1, 2)) q AS "m"; @@ -91,41 +87,26 @@ FROM ( WHERE (TRUE AND TRUE OR 'a' = 'b') AND a > 1 GROUP BY a; SELECT - "d"."a" AS "a", - SUM("d"."b") AS "_col_1" -FROM ( + "x"."a" AS "a", + SUM("y"."b") AS "_col_1" +FROM "x" AS "x" +LEFT JOIN ( SELECT - "x"."a" AS "a", - "y"."b" AS "b" - FROM ( - SELECT - "x"."a" AS "a" - FROM "x" AS "x" - WHERE - "x"."a" > 1 - ) AS "x" - LEFT JOIN ( - SELECT - MAX("y"."b") AS "_col_0", - "y"."a" AS "_u_1" - FROM "y" AS "y" - GROUP BY - "y"."a" - ) AS "_u_0" - ON "x"."a" = "_u_0"."_u_1" - JOIN ( - SELECT - "y"."a" AS "a", - "y"."b" AS "b" - FROM "y" AS "y" - ) AS "y" - ON "x"."a" = "y"."a" - WHERE - "_u_0"."_col_0" >= 0 - AND NOT "_u_0"."_u_1" IS NULL -) AS "d" + MAX("y"."b") AS "_col_0", + "y"."a" AS "_u_1" + FROM "y" AS "y" + GROUP BY + "y"."a" +) AS "_u_0" + ON "x"."a" = "_u_0"."_u_1" +JOIN "y" AS "y" + ON "x"."a" = "y"."a" +WHERE + "_u_0"."_col_0" >= 0 + AND "x"."a" > 1 + AND NOT "_u_0"."_u_1" IS NULL GROUP BY - "d"."a"; + "x"."a"; (SELECT a FROM x) LIMIT 1; ( diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql index 482e231..0b6d382 100644 --- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql +++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql @@ -120,36 +120,16 @@ SELECT "supplier"."s_address" AS "s_address", "supplier"."s_phone" AS "s_phone", "supplier"."s_comment" AS "s_comment" -FROM ( - SELECT - "part"."p_partkey" AS "p_partkey", - "part"."p_mfgr" AS "p_mfgr", - "part"."p_type" AS "p_type", - "part"."p_size" AS "p_size" - FROM "part" AS "part" - WHERE - "part"."p_size" = 15 - AND "part"."p_type" LIKE '%BRASS' -) AS "part" +FROM "part" AS "part" LEFT JOIN ( SELECT MIN("partsupp"."ps_supplycost") AS "_col_0", "partsupp"."ps_partkey" AS "_u_1" FROM "_e_0" AS "partsupp" CROSS JOIN "_e_1" AS "region" - JOIN ( - SELECT - "nation"."n_nationkey" AS "n_nationkey", - "nation"."n_regionkey" AS "n_regionkey" - FROM "nation" AS "nation" - ) AS "nation" + JOIN "nation" AS "nation" ON "nation"."n_regionkey" = "region"."r_regionkey" - JOIN ( - SELECT - "supplier"."s_suppkey" AS "s_suppkey", - "supplier"."s_nationkey" AS "s_nationkey" - FROM "supplier" AS "supplier" - ) AS "supplier" + JOIN "supplier" AS "supplier" ON "supplier"."s_nationkey" = "nation"."n_nationkey" AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey" GROUP BY @@ -157,31 +137,17 @@ LEFT JOIN ( ) AS "_u_0" ON "part"."p_partkey" = "_u_0"."_u_1" CROSS JOIN "_e_1" AS "region" -JOIN ( - SELECT - "nation"."n_nationkey" AS "n_nationkey", - "nation"."n_name" AS "n_name", - "nation"."n_regionkey" AS "n_regionkey" - FROM "nation" AS "nation" -) AS "nation" +JOIN "nation" AS "nation" ON "nation"."n_regionkey" = "region"."r_regionkey" JOIN "_e_0" AS "partsupp" ON "part"."p_partkey" = "partsupp"."ps_partkey" -JOIN ( - SELECT - "supplier"."s_suppkey" AS "s_suppkey", - "supplier"."s_name" AS "s_name", - "supplier"."s_address" AS "s_address", - "supplier"."s_nationkey" AS "s_nationkey", - "supplier"."s_phone" AS "s_phone", - "supplier"."s_acctbal" AS "s_acctbal", - "supplier"."s_comment" AS "s_comment" - FROM "supplier" AS "supplier" -) AS "supplier" +JOIN "supplier" AS "supplier" ON "supplier"."s_nationkey" = "nation"."n_nationkey" AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey" WHERE - "partsupp"."ps_supplycost" = "_u_0"."_col_0" + "part"."p_size" = 15 + AND "part"."p_type" LIKE '%BRASS' + AND "partsupp"."ps_supplycost" = "_u_0"."_col_0" AND NOT "_u_0"."_u_1" IS NULL ORDER BY "s_acctbal" DESC, @@ -224,36 +190,15 @@ SELECT )) AS "revenue", CAST("orders"."o_orderdate" AS TEXT) AS "o_orderdate", "orders"."o_shippriority" AS "o_shippriority" -FROM ( - SELECT - "customer"."c_custkey" AS "c_custkey", - "customer"."c_mktsegment" AS "c_mktsegment" - FROM "customer" AS "customer" - WHERE - "customer"."c_mktsegment" = 'BUILDING' -) AS "customer" -JOIN ( - SELECT - "orders"."o_orderkey" AS "o_orderkey", - "orders"."o_custkey" AS "o_custkey", - "orders"."o_orderdate" AS "o_orderdate", - "orders"."o_shippriority" AS "o_shippriority" - FROM "orders" AS "orders" - WHERE - "orders"."o_orderdate" < '1995-03-15' -) AS "orders" +FROM "customer" AS "customer" +JOIN "orders" AS "orders" ON "customer"."c_custkey" = "orders"."o_custkey" -JOIN ( - SELECT - "lineitem"."l_orderkey" AS "l_orderkey", - "lineitem"."l_extendedprice" AS "l_extendedprice", - "lineitem"."l_discount" AS "l_discount", - "lineitem"."l_shipdate" AS "l_shipdate" - FROM "lineitem" AS "lineitem" - WHERE - "lineitem"."l_shipdate" > '1995-03-15' -) AS "lineitem" +JOIN "lineitem" AS "lineitem" ON "lineitem"."l_orderkey" = "orders"."o_orderkey" +WHERE + "customer"."c_mktsegment" = 'BUILDING' + AND "lineitem"."l_shipdate" > '1995-03-15' + AND "orders"."o_orderdate" < '1995-03-15' GROUP BY "lineitem"."l_orderkey", "orders"."o_orderdate", @@ -342,57 +287,22 @@ SELECT SUM("lineitem"."l_extendedprice" * ( 1 - "lineitem"."l_discount" )) AS "revenue" -FROM ( - SELECT - "customer"."c_custkey" AS "c_custkey", - "customer"."c_nationkey" AS "c_nationkey" - FROM "customer" AS "customer" -) AS "customer" -JOIN ( - SELECT - "orders"."o_orderkey" AS "o_orderkey", - "orders"."o_custkey" AS "o_custkey", - "orders"."o_orderdate" AS "o_orderdate" - FROM "orders" AS "orders" - WHERE - "orders"."o_orderdate" < CAST('1995-01-01' AS DATE) - AND "orders"."o_orderdate" >= CAST('1994-01-01' AS DATE) -) AS "orders" +FROM "customer" AS "customer" +JOIN "orders" AS "orders" ON "customer"."c_custkey" = "orders"."o_custkey" -CROSS JOIN ( - SELECT - "region"."r_regionkey" AS "r_regionkey", - "region"."r_name" AS "r_name" - FROM "region" AS "region" - WHERE - "region"."r_name" = 'ASIA' -) AS "region" -JOIN ( - SELECT - "nation"."n_nationkey" AS "n_nationkey", - "nation"."n_name" AS "n_name", - "nation"."n_regionkey" AS "n_regionkey" - FROM "nation" AS "nation" -) AS "nation" +CROSS JOIN "region" AS "region" +JOIN "nation" AS "nation" ON "nation"."n_regionkey" = "region"."r_regionkey" -JOIN ( - SELECT - "supplier"."s_suppkey" AS "s_suppkey", - "supplier"."s_nationkey" AS "s_nationkey" - FROM "supplier" AS "supplier" -) AS "supplier" +JOIN "supplier" AS "supplier" ON "customer"."c_nationkey" = "supplier"."s_nationkey" AND "supplier"."s_nationkey" = "nation"."n_nationkey" -JOIN ( - SELECT - "lineitem"."l_orderkey" AS "l_orderkey", - "lineitem"."l_suppkey" AS "l_suppkey", - "lineitem"."l_extendedprice" AS "l_extendedprice", - "lineitem"."l_discount" AS "l_discount" - FROM "lineitem" AS "lineitem" -) AS "lineitem" +JOIN "lineitem" AS "lineitem" ON "lineitem"."l_orderkey" = "orders"."o_orderkey" AND "lineitem"."l_suppkey" = "supplier"."s_suppkey" +WHERE + "orders"."o_orderdate" < CAST('1995-01-01' AS DATE) + AND "orders"."o_orderdate" >= CAST('1994-01-01' AS DATE) + AND "region"."r_name" = 'ASIA' GROUP BY "nation"."n_name" ORDER BY @@ -471,67 +381,37 @@ WITH "_e_0" AS ( OR "nation"."n_name" = 'GERMANY' ) SELECT - "shipping"."supp_nation" AS "supp_nation", - "shipping"."cust_nation" AS "cust_nation", - "shipping"."l_year" AS "l_year", - SUM("shipping"."volume") AS "revenue" -FROM ( - SELECT - "n1"."n_name" AS "supp_nation", - "n2"."n_name" AS "cust_nation", - EXTRACT(year FROM "lineitem"."l_shipdate") AS "l_year", - "lineitem"."l_extendedprice" * ( + "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" - ) AS "volume" - FROM ( - SELECT - "supplier"."s_suppkey" AS "s_suppkey", - "supplier"."s_nationkey" AS "s_nationkey" - FROM "supplier" AS "supplier" - ) AS "supplier" - JOIN ( - SELECT - "lineitem"."l_orderkey" AS "l_orderkey", - "lineitem"."l_suppkey" AS "l_suppkey", - "lineitem"."l_extendedprice" AS "l_extendedprice", - "lineitem"."l_discount" AS "l_discount", - "lineitem"."l_shipdate" AS "l_shipdate" - FROM "lineitem" AS "lineitem" - WHERE - "lineitem"."l_shipdate" BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE) - ) AS "lineitem" - ON "supplier"."s_suppkey" = "lineitem"."l_suppkey" - JOIN ( - SELECT - "orders"."o_orderkey" AS "o_orderkey", - "orders"."o_custkey" AS "o_custkey" - FROM "orders" AS "orders" - ) AS "orders" - ON "orders"."o_orderkey" = "lineitem"."l_orderkey" - JOIN ( - SELECT - "customer"."c_custkey" AS "c_custkey", - "customer"."c_nationkey" AS "c_nationkey" - FROM "customer" AS "customer" - ) AS "customer" - ON "customer"."c_custkey" = "orders"."o_custkey" - JOIN "_e_0" AS "n1" - ON "supplier"."s_nationkey" = "n1"."n_nationkey" - JOIN "_e_0" AS "n2" - ON "customer"."c_nationkey" = "n2"."n_nationkey" - AND ( - "n1"."n_name" = 'FRANCE' - OR "n2"."n_name" = 'FRANCE' - ) - AND ( - "n1"."n_name" = 'GERMANY' - OR "n2"."n_name" = 'GERMANY' - ) -) AS "shipping" + )) AS "revenue" +FROM "supplier" AS "supplier" +JOIN "lineitem" AS "lineitem" + ON "supplier"."s_suppkey" = "lineitem"."l_suppkey" +JOIN "orders" AS "orders" + ON "orders"."o_orderkey" = "lineitem"."l_orderkey" +JOIN "customer" AS "customer" + ON "customer"."c_custkey" = "orders"."o_custkey" +JOIN "_e_0" AS "n1" + ON "supplier"."s_nationkey" = "n1"."n_nationkey" +JOIN "_e_0" AS "n2" + ON "customer"."c_nationkey" = "n2"."n_nationkey" + AND ( + "n1"."n_name" = 'FRANCE' + OR "n2"."n_name" = 'FRANCE' + ) + AND ( + "n1"."n_name" = 'GERMANY' + OR "n2"."n_name" = 'GERMANY' + ) +WHERE + "lineitem"."l_shipdate" BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE) GROUP BY - "shipping"."supp_nation", - "shipping"."cust_nation", - "shipping"."l_year" + "n1"."n_name", + "n2"."n_name", + EXTRACT(year FROM "lineitem"."l_shipdate") ORDER BY "supp_nation", "cust_nation", @@ -578,87 +458,37 @@ group by order by o_year; SELECT - "all_nations"."o_year" AS "o_year", + EXTRACT(year FROM "orders"."o_orderdate") AS "o_year", SUM(CASE - WHEN "all_nations"."nation" = 'BRAZIL' - THEN "all_nations"."volume" + WHEN "nation_2"."n_name" = 'BRAZIL' + THEN "lineitem"."l_extendedprice" * ( + 1 - "lineitem"."l_discount" + ) ELSE 0 - END) / SUM("all_nations"."volume") AS "mkt_share" -FROM ( - SELECT - EXTRACT(year FROM "orders"."o_orderdate") AS "o_year", - "lineitem"."l_extendedprice" * ( + END) / SUM("lineitem"."l_extendedprice" * ( 1 - "lineitem"."l_discount" - ) AS "volume", - "n2"."n_name" AS "nation" - FROM ( - SELECT - "part"."p_partkey" AS "p_partkey", - "part"."p_type" AS "p_type" - FROM "part" AS "part" - WHERE - "part"."p_type" = 'ECONOMY ANODIZED STEEL' - ) AS "part" - CROSS JOIN ( - SELECT - "region"."r_regionkey" AS "r_regionkey", - "region"."r_name" AS "r_name" - FROM "region" AS "region" - WHERE - "region"."r_name" = 'AMERICA' - ) AS "region" - JOIN ( - SELECT - "nation"."n_nationkey" AS "n_nationkey", - "nation"."n_regionkey" AS "n_regionkey" - FROM "nation" AS "nation" - ) AS "n1" - ON "n1"."n_regionkey" = "region"."r_regionkey" - JOIN ( - SELECT - "customer"."c_custkey" AS "c_custkey", - "customer"."c_nationkey" AS "c_nationkey" - FROM "customer" AS "customer" - ) AS "customer" - ON "customer"."c_nationkey" = "n1"."n_nationkey" - JOIN ( - SELECT - "orders"."o_orderkey" AS "o_orderkey", - "orders"."o_custkey" AS "o_custkey", - "orders"."o_orderdate" AS "o_orderdate" - FROM "orders" AS "orders" - WHERE - "orders"."o_orderdate" BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE) - ) AS "orders" - ON "orders"."o_custkey" = "customer"."c_custkey" - JOIN ( - SELECT - "lineitem"."l_orderkey" AS "l_orderkey", - "lineitem"."l_partkey" AS "l_partkey", - "lineitem"."l_suppkey" AS "l_suppkey", - "lineitem"."l_extendedprice" AS "l_extendedprice", - "lineitem"."l_discount" AS "l_discount" - FROM "lineitem" AS "lineitem" - ) AS "lineitem" - ON "lineitem"."l_orderkey" = "orders"."o_orderkey" - AND "part"."p_partkey" = "lineitem"."l_partkey" - JOIN ( - SELECT - "supplier"."s_suppkey" AS "s_suppkey", - "supplier"."s_nationkey" AS "s_nationkey" - FROM "supplier" AS "supplier" - ) AS "supplier" - ON "supplier"."s_suppkey" = "lineitem"."l_suppkey" - JOIN ( - SELECT - "nation"."n_nationkey" AS "n_nationkey", - "nation"."n_name" AS "n_name" - FROM "nation" AS "nation" - ) AS "n2" - ON "supplier"."s_nationkey" = "n2"."n_nationkey" -) AS "all_nations" + )) AS "mkt_share" +FROM "part" AS "part" +CROSS JOIN "region" AS "region" +JOIN "nation" AS "nation" + ON "nation"."n_regionkey" = "region"."r_regionkey" +JOIN "customer" AS "customer" + ON "customer"."c_nationkey" = "nation"."n_nationkey" +JOIN "orders" AS "orders" + ON "orders"."o_custkey" = "customer"."c_custkey" +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 + "orders"."o_orderdate" BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE) + AND "part"."p_type" = 'ECONOMY ANODIZED STEEL' + AND "region"."r_name" = 'AMERICA' GROUP BY - "all_nations"."o_year" + EXTRACT(year FROM "orders"."o_orderdate") ORDER BY "o_year"; @@ -698,69 +528,28 @@ order by nation, o_year desc; SELECT - "profit"."nation" AS "nation", - "profit"."o_year" AS "o_year", - SUM("profit"."amount") AS "sum_profit" -FROM ( - SELECT - "nation"."n_name" AS "nation", - EXTRACT(year FROM "orders"."o_orderdate") AS "o_year", - "lineitem"."l_extendedprice" * ( + "nation"."n_name" AS "nation", + EXTRACT(year FROM "orders"."o_orderdate") AS "o_year", + SUM("lineitem"."l_extendedprice" * ( 1 - "lineitem"."l_discount" - ) - "partsupp"."ps_supplycost" * "lineitem"."l_quantity" AS "amount" - FROM ( - SELECT - "part"."p_partkey" AS "p_partkey", - "part"."p_name" AS "p_name" - FROM "part" AS "part" - WHERE - "part"."p_name" LIKE '%green%' - ) AS "part" - JOIN ( - SELECT - "lineitem"."l_orderkey" AS "l_orderkey", - "lineitem"."l_partkey" AS "l_partkey", - "lineitem"."l_suppkey" AS "l_suppkey", - "lineitem"."l_quantity" AS "l_quantity", - "lineitem"."l_extendedprice" AS "l_extendedprice", - "lineitem"."l_discount" AS "l_discount" - FROM "lineitem" AS "lineitem" - ) AS "lineitem" - ON "part"."p_partkey" = "lineitem"."l_partkey" - JOIN ( - SELECT - "supplier"."s_suppkey" AS "s_suppkey", - "supplier"."s_nationkey" AS "s_nationkey" - FROM "supplier" AS "supplier" - ) AS "supplier" - ON "supplier"."s_suppkey" = "lineitem"."l_suppkey" - JOIN ( - SELECT - "partsupp"."ps_partkey" AS "ps_partkey", - "partsupp"."ps_suppkey" AS "ps_suppkey", - "partsupp"."ps_supplycost" AS "ps_supplycost" - FROM "partsupp" AS "partsupp" - ) AS "partsupp" - ON "partsupp"."ps_partkey" = "lineitem"."l_partkey" - AND "partsupp"."ps_suppkey" = "lineitem"."l_suppkey" - JOIN ( - SELECT - "orders"."o_orderkey" AS "o_orderkey", - "orders"."o_orderdate" AS "o_orderdate" - FROM "orders" AS "orders" - ) AS "orders" - ON "orders"."o_orderkey" = "lineitem"."l_orderkey" - JOIN ( - SELECT - "nation"."n_nationkey" AS "n_nationkey", - "nation"."n_name" AS "n_name" - FROM "nation" AS "nation" - ) AS "nation" - ON "supplier"."s_nationkey" = "nation"."n_nationkey" -) AS "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" +JOIN "supplier" AS "supplier" + ON "supplier"."s_suppkey" = "lineitem"."l_suppkey" +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 "nation" AS "nation" + ON "supplier"."s_nationkey" = "nation"."n_nationkey" +WHERE + "part"."p_name" LIKE '%green%' GROUP BY - "profit"."nation", - "profit"."o_year" + "nation"."n_name", + EXTRACT(year FROM "orders"."o_orderdate") ORDER BY "nation", "o_year" DESC; @@ -812,46 +601,17 @@ SELECT "customer"."c_address" AS "c_address", "customer"."c_phone" AS "c_phone", "customer"."c_comment" AS "c_comment" -FROM ( - SELECT - "customer"."c_custkey" AS "c_custkey", - "customer"."c_name" AS "c_name", - "customer"."c_address" AS "c_address", - "customer"."c_nationkey" AS "c_nationkey", - "customer"."c_phone" AS "c_phone", - "customer"."c_acctbal" AS "c_acctbal", - "customer"."c_comment" AS "c_comment" - FROM "customer" AS "customer" -) AS "customer" -JOIN ( - SELECT - "orders"."o_orderkey" AS "o_orderkey", - "orders"."o_custkey" AS "o_custkey", - "orders"."o_orderdate" AS "o_orderdate" - FROM "orders" AS "orders" - WHERE - "orders"."o_orderdate" < CAST('1994-01-01' AS DATE) - AND "orders"."o_orderdate" >= CAST('1993-10-01' AS DATE) -) AS "orders" +FROM "customer" AS "customer" +JOIN "orders" AS "orders" ON "customer"."c_custkey" = "orders"."o_custkey" -JOIN ( - SELECT - "lineitem"."l_orderkey" AS "l_orderkey", - "lineitem"."l_extendedprice" AS "l_extendedprice", - "lineitem"."l_discount" AS "l_discount", - "lineitem"."l_returnflag" AS "l_returnflag" - FROM "lineitem" AS "lineitem" - WHERE - "lineitem"."l_returnflag" = 'R' -) AS "lineitem" +JOIN "lineitem" AS "lineitem" ON "lineitem"."l_orderkey" = "orders"."o_orderkey" -JOIN ( - SELECT - "nation"."n_nationkey" AS "n_nationkey", - "nation"."n_name" AS "n_name" - FROM "nation" AS "nation" -) AS "nation" +JOIN "nation" AS "nation" ON "customer"."c_nationkey" = "nation"."n_nationkey" +WHERE + "lineitem"."l_returnflag" = 'R' + AND "orders"."o_orderdate" < CAST('1994-01-01' AS DATE) + AND "orders"."o_orderdate" >= CAST('1993-10-01' AS DATE) GROUP BY "customer"."c_custkey", "customer"."c_name", @@ -910,14 +670,7 @@ WITH "_e_0" AS ( SELECT "partsupp"."ps_partkey" AS "ps_partkey", SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") AS "value" -FROM ( - SELECT - "partsupp"."ps_partkey" AS "ps_partkey", - "partsupp"."ps_suppkey" AS "ps_suppkey", - "partsupp"."ps_availqty" AS "ps_availqty", - "partsupp"."ps_supplycost" AS "ps_supplycost" - FROM "partsupp" AS "partsupp" -) AS "partsupp" +FROM "partsupp" AS "partsupp" JOIN "_e_0" AS "supplier" ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey" JOIN "_e_1" AS "nation" @@ -928,13 +681,7 @@ HAVING SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") > ( SELECT SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") * 0.0001 AS "_col_0" - FROM ( - SELECT - "partsupp"."ps_suppkey" AS "ps_suppkey", - "partsupp"."ps_availqty" AS "ps_availqty", - "partsupp"."ps_supplycost" AS "ps_supplycost" - FROM "partsupp" AS "partsupp" - ) AS "partsupp" + FROM "partsupp" AS "partsupp" JOIN "_e_0" AS "supplier" ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey" JOIN "_e_1" AS "nation" @@ -988,28 +735,15 @@ SELECT THEN 1 ELSE 0 END) AS "low_line_count" -FROM ( - SELECT - "orders"."o_orderkey" AS "o_orderkey", - "orders"."o_orderpriority" AS "o_orderpriority" - FROM "orders" AS "orders" -) AS "orders" -JOIN ( - SELECT - "lineitem"."l_orderkey" AS "l_orderkey", - "lineitem"."l_shipdate" AS "l_shipdate", - "lineitem"."l_commitdate" AS "l_commitdate", - "lineitem"."l_receiptdate" AS "l_receiptdate", - "lineitem"."l_shipmode" AS "l_shipmode" - FROM "lineitem" AS "lineitem" - WHERE - "lineitem"."l_commitdate" < "lineitem"."l_receiptdate" - AND "lineitem"."l_receiptdate" < CAST('1995-01-01' AS DATE) - AND "lineitem"."l_receiptdate" >= CAST('1994-01-01' AS DATE) - AND "lineitem"."l_shipdate" < "lineitem"."l_commitdate" - AND "lineitem"."l_shipmode" IN ('MAIL', 'SHIP') -) AS "lineitem" +FROM "orders" AS "orders" +JOIN "lineitem" AS "lineitem" ON "orders"."o_orderkey" = "lineitem"."l_orderkey" +WHERE + "lineitem"."l_commitdate" < "lineitem"."l_receiptdate" + AND "lineitem"."l_receiptdate" < CAST('1995-01-01' AS DATE) + AND "lineitem"."l_receiptdate" >= CAST('1994-01-01' AS DATE) + AND "lineitem"."l_shipdate" < "lineitem"."l_commitdate" + AND "lineitem"."l_shipmode" IN ('MAIL', 'SHIP') GROUP BY "lineitem"."l_shipmode" ORDER BY @@ -1044,21 +778,10 @@ SELECT FROM ( SELECT COUNT("orders"."o_orderkey") AS "c_count" - FROM ( - SELECT - "customer"."c_custkey" AS "c_custkey" - FROM "customer" AS "customer" - ) AS "customer" - LEFT JOIN ( - SELECT - "orders"."o_orderkey" AS "o_orderkey", - "orders"."o_custkey" AS "o_custkey", - "orders"."o_comment" AS "o_comment" - FROM "orders" AS "orders" - WHERE - NOT "orders"."o_comment" LIKE '%special%requests%' - ) AS "orders" + FROM "customer" AS "customer" + LEFT JOIN "orders" AS "orders" ON "customer"."c_custkey" = "orders"."o_custkey" + AND NOT "orders"."o_comment" LIKE '%special%requests%' GROUP BY "customer"."c_custkey" ) AS "c_orders" @@ -1094,24 +817,12 @@ SELECT END) / SUM("lineitem"."l_extendedprice" * ( 1 - "lineitem"."l_discount" )) AS "promo_revenue" -FROM ( - SELECT - "lineitem"."l_partkey" AS "l_partkey", - "lineitem"."l_extendedprice" AS "l_extendedprice", - "lineitem"."l_discount" AS "l_discount", - "lineitem"."l_shipdate" AS "l_shipdate" - FROM "lineitem" AS "lineitem" - WHERE - "lineitem"."l_shipdate" < CAST('1995-10-01' AS DATE) - AND "lineitem"."l_shipdate" >= CAST('1995-09-01' AS DATE) -) AS "lineitem" -JOIN ( - SELECT - "part"."p_partkey" AS "p_partkey", - "part"."p_type" AS "p_type" - FROM "part" AS "part" -) AS "part" - ON "lineitem"."l_partkey" = "part"."p_partkey"; +FROM "lineitem" AS "lineitem" +JOIN "part" AS "part" + ON "lineitem"."l_partkey" = "part"."p_partkey" +WHERE + "lineitem"."l_shipdate" < CAST('1995-10-01' AS DATE) + AND "lineitem"."l_shipdate" >= CAST('1995-09-01' AS DATE); -------------------------------------- -- TPC-H 15 @@ -1165,14 +876,7 @@ SELECT "supplier"."s_address" AS "s_address", "supplier"."s_phone" AS "s_phone", "revenue"."total_revenue" AS "total_revenue" -FROM ( - SELECT - "supplier"."s_suppkey" AS "s_suppkey", - "supplier"."s_name" AS "s_name", - "supplier"."s_address" AS "s_address", - "supplier"."s_phone" AS "s_phone" - FROM "supplier" AS "supplier" -) AS "supplier" +FROM "supplier" AS "supplier" JOIN "revenue" ON "revenue"."total_revenue" = ( SELECT @@ -1221,12 +925,7 @@ SELECT "part"."p_type" AS "p_type", "part"."p_size" AS "p_size", COUNT(DISTINCT "partsupp"."ps_suppkey") AS "supplier_cnt" -FROM ( - SELECT - "partsupp"."ps_partkey" AS "ps_partkey", - "partsupp"."ps_suppkey" AS "ps_suppkey" - FROM "partsupp" AS "partsupp" -) AS "partsupp" +FROM "partsupp" AS "partsupp" LEFT JOIN ( SELECT "supplier"."s_suppkey" AS "s_suppkey" @@ -1237,21 +936,13 @@ LEFT JOIN ( "supplier"."s_suppkey" ) AS "_u_0" ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey" -JOIN ( - SELECT - "part"."p_partkey" AS "p_partkey", - "part"."p_brand" AS "p_brand", - "part"."p_type" AS "p_type", - "part"."p_size" AS "p_size" - FROM "part" AS "part" - WHERE - "part"."p_brand" <> 'Brand#45' - AND "part"."p_size" IN (49, 14, 23, 45, 19, 3, 36, 9) - AND NOT "part"."p_type" LIKE 'MEDIUM POLISHED%' -) AS "part" +JOIN "part" AS "part" ON "part"."p_partkey" = "partsupp"."ps_partkey" WHERE "_u_0"."s_suppkey" IS NULL + AND "part"."p_brand" <> 'Brand#45' + AND "part"."p_size" IN (49, 14, 23, 45, 19, 3, 36, 9) + AND NOT "part"."p_type" LIKE 'MEDIUM POLISHED%' GROUP BY "part"."p_brand", "part"."p_type", @@ -1284,23 +975,8 @@ where ); SELECT SUM("lineitem"."l_extendedprice") / 7.0 AS "avg_yearly" -FROM ( - SELECT - "lineitem"."l_partkey" AS "l_partkey", - "lineitem"."l_quantity" AS "l_quantity", - "lineitem"."l_extendedprice" AS "l_extendedprice" - FROM "lineitem" AS "lineitem" -) AS "lineitem" -JOIN ( - SELECT - "part"."p_partkey" AS "p_partkey", - "part"."p_brand" AS "p_brand", - "part"."p_container" AS "p_container" - FROM "part" AS "part" - WHERE - "part"."p_brand" = 'Brand#23' - AND "part"."p_container" = 'MED BOX' -) AS "part" +FROM "lineitem" AS "lineitem" +JOIN "part" AS "part" ON "part"."p_partkey" = "lineitem"."l_partkey" LEFT JOIN ( SELECT @@ -1313,6 +989,8 @@ LEFT JOIN ( ON "_u_0"."_u_1" = "part"."p_partkey" WHERE "lineitem"."l_quantity" < "_u_0"."_col_0" + AND "part"."p_brand" = 'Brand#23' + AND "part"."p_container" = 'MED BOX' AND NOT "_u_0"."_u_1" IS NULL; -------------------------------------- @@ -1359,20 +1037,8 @@ SELECT "orders"."o_orderdate" AS "o_orderdate", "orders"."o_totalprice" AS "o_totalprice", SUM("lineitem"."l_quantity") AS "_col_5" -FROM ( - SELECT - "customer"."c_custkey" AS "c_custkey", - "customer"."c_name" AS "c_name" - FROM "customer" AS "customer" -) AS "customer" -JOIN ( - SELECT - "orders"."o_orderkey" AS "o_orderkey", - "orders"."o_custkey" AS "o_custkey", - "orders"."o_totalprice" AS "o_totalprice", - "orders"."o_orderdate" AS "o_orderdate" - FROM "orders" AS "orders" -) AS "orders" +FROM "customer" AS "customer" +JOIN "orders" AS "orders" ON "customer"."c_custkey" = "orders"."o_custkey" LEFT JOIN ( SELECT @@ -1385,12 +1051,7 @@ LEFT JOIN ( SUM("lineitem"."l_quantity") > 300 ) AS "_u_0" ON "orders"."o_orderkey" = "_u_0"."l_orderkey" -JOIN ( - SELECT - "lineitem"."l_orderkey" AS "l_orderkey", - "lineitem"."l_quantity" AS "l_quantity" - FROM "lineitem" AS "lineitem" -) AS "lineitem" +JOIN "lineitem" AS "lineitem" ON "orders"."o_orderkey" = "lineitem"."l_orderkey" WHERE NOT "_u_0"."l_orderkey" IS NULL @@ -1447,24 +1108,8 @@ SELECT SUM("lineitem"."l_extendedprice" * ( 1 - "lineitem"."l_discount" )) AS "revenue" -FROM ( - SELECT - "lineitem"."l_partkey" AS "l_partkey", - "lineitem"."l_quantity" AS "l_quantity", - "lineitem"."l_extendedprice" AS "l_extendedprice", - "lineitem"."l_discount" AS "l_discount", - "lineitem"."l_shipinstruct" AS "l_shipinstruct", - "lineitem"."l_shipmode" AS "l_shipmode" - FROM "lineitem" AS "lineitem" -) AS "lineitem" -JOIN ( - SELECT - "part"."p_partkey" AS "p_partkey", - "part"."p_brand" AS "p_brand", - "part"."p_size" AS "p_size", - "part"."p_container" AS "p_container" - FROM "part" AS "part" -) AS "part" +FROM "lineitem" AS "lineitem" +JOIN "part" AS "part" ON ( "part"."p_brand" = 'Brand#12' AND "part"."p_container" IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') @@ -1558,14 +1203,7 @@ order by SELECT "supplier"."s_name" AS "s_name", "supplier"."s_address" AS "s_address" -FROM ( - SELECT - "supplier"."s_suppkey" AS "s_suppkey", - "supplier"."s_name" AS "s_name", - "supplier"."s_address" AS "s_address", - "supplier"."s_nationkey" AS "s_nationkey" - FROM "supplier" AS "supplier" -) AS "supplier" +FROM "supplier" AS "supplier" LEFT JOIN ( SELECT "partsupp"."ps_suppkey" AS "ps_suppkey" @@ -1604,17 +1242,11 @@ LEFT JOIN ( "partsupp"."ps_suppkey" ) AS "_u_4" ON "supplier"."s_suppkey" = "_u_4"."ps_suppkey" -JOIN ( - SELECT - "nation"."n_nationkey" AS "n_nationkey", - "nation"."n_name" AS "n_name" - FROM "nation" AS "nation" - WHERE - "nation"."n_name" = 'CANADA' -) AS "nation" +JOIN "nation" AS "nation" ON "supplier"."s_nationkey" = "nation"."n_nationkey" WHERE - NOT "_u_4"."ps_suppkey" IS NULL + "nation"."n_name" = 'CANADA' + AND NOT "_u_4"."ps_suppkey" IS NULL ORDER BY "s_name"; @@ -1665,24 +1297,9 @@ limit SELECT "supplier"."s_name" AS "s_name", COUNT(*) AS "numwait" -FROM ( - SELECT - "supplier"."s_suppkey" AS "s_suppkey", - "supplier"."s_name" AS "s_name", - "supplier"."s_nationkey" AS "s_nationkey" - FROM "supplier" AS "supplier" -) AS "supplier" -JOIN ( - SELECT - "lineitem"."l_orderkey" AS "l_orderkey", - "lineitem"."l_suppkey" AS "l_suppkey", - "lineitem"."l_commitdate" AS "l_commitdate", - "lineitem"."l_receiptdate" AS "l_receiptdate" - FROM "lineitem" AS "lineitem" - WHERE - "lineitem"."l_receiptdate" > "lineitem"."l_commitdate" -) AS "l1" - ON "supplier"."s_suppkey" = "l1"."l_suppkey" +FROM "supplier" AS "supplier" +JOIN "lineitem" AS "lineitem" + ON "supplier"."s_suppkey" = "lineitem"."l_suppkey" LEFT JOIN ( SELECT "l2"."l_orderkey" AS "l_orderkey", @@ -1691,7 +1308,7 @@ LEFT JOIN ( GROUP BY "l2"."l_orderkey" ) AS "_u_0" - ON "_u_0"."l_orderkey" = "l1"."l_orderkey" + ON "_u_0"."l_orderkey" = "lineitem"."l_orderkey" LEFT JOIN ( SELECT "l3"."l_orderkey" AS "l_orderkey", @@ -1702,31 +1319,20 @@ LEFT JOIN ( GROUP BY "l3"."l_orderkey" ) AS "_u_2" - ON "_u_2"."l_orderkey" = "l1"."l_orderkey" -JOIN ( - SELECT - "orders"."o_orderkey" AS "o_orderkey", - "orders"."o_orderstatus" AS "o_orderstatus" - FROM "orders" AS "orders" - WHERE - "orders"."o_orderstatus" = 'F' -) AS "orders" - ON "orders"."o_orderkey" = "l1"."l_orderkey" -JOIN ( - SELECT - "nation"."n_nationkey" AS "n_nationkey", - "nation"."n_name" AS "n_name" - FROM "nation" AS "nation" - WHERE - "nation"."n_name" = 'SAUDI ARABIA' -) AS "nation" + ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey" +JOIN "orders" AS "orders" + ON "orders"."o_orderkey" = "lineitem"."l_orderkey" +JOIN "nation" AS "nation" ON "supplier"."s_nationkey" = "nation"."n_nationkey" WHERE ( "_u_2"."l_orderkey" IS NULL - OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "l1"."l_suppkey") + OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "lineitem"."l_suppkey") ) - AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "l1"."l_suppkey") + AND "lineitem"."l_receiptdate" > "lineitem"."l_commitdate" + AND "nation"."n_name" = 'SAUDI ARABIA' + AND "orders"."o_orderstatus" = 'F' + AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "lineitem"."l_suppkey") AND NOT "_u_0"."l_orderkey" IS NULL GROUP BY "supplier"."s_name" @@ -1776,35 +1382,30 @@ group by order by cntrycode; SELECT - "custsale"."cntrycode" AS "cntrycode", + SUBSTRING("customer"."c_phone", 1, 2) AS "cntrycode", COUNT(*) AS "numcust", - SUM("custsale"."c_acctbal") AS "totacctbal" -FROM ( + SUM("customer"."c_acctbal") AS "totacctbal" +FROM "customer" AS "customer" +LEFT JOIN ( SELECT - SUBSTRING("customer"."c_phone", 1, 2) AS "cntrycode", - "customer"."c_acctbal" AS "c_acctbal" - FROM "customer" AS "customer" - LEFT JOIN ( + "orders"."o_custkey" AS "_u_1" + FROM "orders" AS "orders" + GROUP BY + "orders"."o_custkey" +) AS "_u_0" + ON "_u_0"."_u_1" = "customer"."c_custkey" +WHERE + "_u_0"."_u_1" IS NULL + AND "customer"."c_acctbal" > ( SELECT - "orders"."o_custkey" AS "_u_1" - FROM "orders" AS "orders" - GROUP BY - "orders"."o_custkey" - ) AS "_u_0" - ON "_u_0"."_u_1" = "customer"."c_custkey" - WHERE - "_u_0"."_u_1" IS NULL - AND "customer"."c_acctbal" > ( - SELECT - AVG("customer"."c_acctbal") AS "_col_0" - FROM "customer" AS "customer" - WHERE - "customer"."c_acctbal" > 0.00 - AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17') - ) - AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17') -) AS "custsale" + AVG("customer"."c_acctbal") AS "_col_0" + FROM "customer" AS "customer" + WHERE + "customer"."c_acctbal" > 0.00 + AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17') + ) + AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17') GROUP BY - "custsale"."cntrycode" + SUBSTRING("customer"."c_phone", 1, 2) ORDER BY "cntrycode"; -- cgit v1.2.3