Materialize/test/sqllogictest/tpch_create_index.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

# Plans for the TPC-H workload modeled as CREATE INDEX statements
# on views for each workload query.

# PRIMARY KEY annotations (which are in the spec) are currently
# removed from this slt, because we don't support them at the moment.
# (Note that _in slts_ they are actually supported, but it's better
# to match the plans of real runs more closely.)

statement ok
CREATE TABLE nation (
    n_nationkey  integer,
    n_name       char(25) NOT NULL,
    n_regionkey  integer NOT NULL,
    n_comment    varchar(152)
);

statement ok
CREATE INDEX pk_nation_nationkey ON nation (n_nationkey ASC);

statement ok
CREATE INDEX fk_nation_regionkey ON nation (n_regionkey ASC);

statement ok
CREATE TABLE region  (
    r_regionkey  integer,
    r_name       char(25) NOT NULL,
    r_comment    varchar(152)
);

statement ok
CREATE INDEX pk_region_regionkey ON region (r_regionkey ASC);

statement ok
CREATE TABLE part (
    p_partkey     integer,
    p_name        varchar(55) NOT NULL,
    p_mfgr        char(25) NOT NULL,
    p_brand       char(10) NOT NULL,
    p_type        varchar(25) NOT NULL,
    p_size        integer NOT NULL,
    p_container   char(10) NOT NULL,
    p_retailprice decimal(15, 2) NOT NULL,
    p_comment     varchar(23) NOT NULL
);

statement ok
CREATE INDEX pk_part_partkey ON part (p_partkey ASC);

statement ok
CREATE TABLE supplier (
    s_suppkey     integer,
    s_name        char(25) NOT NULL,
    s_address     varchar(40) NOT NULL,
    s_nationkey   integer NOT NULL,
    s_phone       char(15) NOT NULL,
    s_acctbal     decimal(15, 2) NOT NULL,
    s_comment     varchar(101) NOT NULL
);

statement ok
CREATE INDEX pk_supplier_suppkey ON supplier (s_suppkey ASC);

statement ok
CREATE INDEX fk_supplier_nationkey ON supplier (s_nationkey ASC);

statement ok
CREATE TABLE partsupp (
    ps_partkey     integer NOT NULL,
    ps_suppkey     integer NOT NULL,
    ps_availqty    integer NOT NULL,
    ps_supplycost  decimal(15, 2) NOT NULL,
    ps_comment     varchar(199) NOT NULL
);

statement ok
CREATE INDEX pk_partsupp_partkey_suppkey ON partsupp (ps_partkey ASC, ps_suppkey ASC);

statement ok
CREATE INDEX fk_partsupp_partkey ON partsupp (ps_partkey ASC);

statement ok
CREATE INDEX fk_partsupp_suppkey ON partsupp (ps_suppkey ASC);

statement ok
CREATE TABLE customer (
    c_custkey     integer,
    c_name        varchar(25) NOT NULL,
    c_address     varchar(40) NOT NULL,
    c_nationkey   integer NOT NULL,
    c_phone       char(15) NOT NULL,
    c_acctbal     decimal(15, 2) NOT NULL,
    c_mktsegment  char(10) NOT NULL,
    c_comment     varchar(117) NOT NULL
);

statement ok
CREATE INDEX pk_customer_custkey ON customer (c_custkey ASC);

statement ok
CREATE INDEX fk_customer_nationkey ON customer (c_nationkey ASC);

statement ok
CREATE TABLE orders (
    o_orderkey       integer,
    o_custkey        integer NOT NULL,
    o_orderstatus    char(1) NOT NULL,
    o_totalprice     decimal(15, 2) NOT NULL,
    o_orderdate      DATE NOT NULL,
    o_orderpriority  char(15) NOT NULL,
    o_clerk          char(15) NOT NULL,
    o_shippriority   integer NOT NULL,
    o_comment        varchar(79) NOT NULL
);

statement ok
CREATE INDEX pk_orders_orderkey ON orders (o_orderkey ASC);

statement ok
CREATE INDEX fk_orders_custkey ON orders (o_custkey ASC);

statement ok
CREATE TABLE lineitem (
    l_orderkey       integer NOT NULL,
    l_partkey        integer NOT NULL,
    l_suppkey        integer NOT NULL,
    l_linenumber     integer NOT NULL,
    l_quantity       decimal(15, 2) NOT NULL,
    l_extendedprice  decimal(15, 2) NOT NULL,
    l_discount       decimal(15, 2) NOT NULL,
    l_tax            decimal(15, 2) NOT NULL,
    l_returnflag     char(1) NOT NULL,
    l_linestatus     char(1) NOT NULL,
    l_shipdate       date NOT NULL,
    l_commitdate     date NOT NULL,
    l_receiptdate    date NOT NULL,
    l_shipinstruct   char(25) NOT NULL,
    l_shipmode       char(10) NOT NULL,
    l_comment        varchar(44) NOT NULL
);

statement ok
CREATE INDEX pk_lineitem_orderkey_linenumber ON lineitem (l_orderkey ASC, l_linenumber ASC);

statement ok
CREATE INDEX fk_lineitem_orderkey ON lineitem (l_orderkey ASC);

statement ok
CREATE INDEX fk_lineitem_partkey ON lineitem (l_partkey ASC);

statement ok
CREATE INDEX fk_lineitem_suppkey ON lineitem (l_suppkey ASC);

statement ok
CREATE INDEX fk_lineitem_partsuppkey ON lineitem (l_partkey ASC, l_suppkey ASC);

statement ok
-- Query 01
CREATE VIEW Q01 AS
SELECT
	l_returnflag,
	l_linestatus,
	sum(l_quantity) AS sum_qty,
	sum(l_extendedprice) AS sum_base_price,
	sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
	sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
	avg(l_quantity) AS avg_qty,
	avg(l_extendedprice) AS avg_price,
	avg(l_discount) AS avg_disc,
	count(*) AS count_order
FROM
	lineitem
WHERE
	l_shipdate <= DATE '1998-12-01' - INTERVAL '60' day
GROUP BY
	l_returnflag,
	l_linestatus
ORDER BY
	l_returnflag,
	l_linestatus;

query T multiline
-- Explain index on Q01
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q01;
----
materialize.public.q01_primary_idx:
  ArrangeBy keys=[[#0{l_returnflag}, #1{l_linestatus}]] // { arity: 10 }
    ReadGlobalFromSameDataflow materialize.public.q01 // { arity: 10 }

materialize.public.q01:
  Project (#0{l_returnflag}..=#5{sum}, #9..=#11, #6{count}) // { arity: 10 }
    Map (bigint_to_numeric(case when (#6{count} = 0) then null else #6{count} end), (#2{sum_l_quantity} / #8), (#3{sum_l_extendedprice} / #8), (#7{sum_l_discount} / #8)) // { arity: 12 }
      Reduce group_by=[#4{l_returnflag}, #5{l_linestatus}] aggregates=[sum(#0{l_quantity}), sum(#1{l_extendedprice}), sum((#1{l_extendedprice} * (1 - #2{l_discount}))), sum(((#1{l_extendedprice} * (1 - #2{l_discount})) * (1 + #3{l_tax}))), count(*), sum(#2{l_discount})] // { arity: 8 }
        Project (#4{l_quantity}..=#9{l_linestatus}) // { arity: 6 }
          Filter (date_to_timestamp(#10{l_shipdate}) <= 1998-10-02 00:00:00) // { arity: 16 }
            ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 16 }

Used Indexes:
  - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***)

Target cluster: quickstart

EOF

# Create a default index on Q01 in order to ensure that the EXPLAIN CREATE INDEX
# output does not produce a plan that just reads from the explained index if it
# already exists.
statement ok
CREATE DEFAULT INDEX ON Q01;


statement ok
-- Query 02
CREATE VIEW Q02 AS
SELECT
    s_acctbal,
    s_name,
    n_name,
    p_partkey,
    p_mfgr,
    s_address,
    s_phone,
    s_comment
FROM
    part, supplier, partsupp, nation, region
WHERE
    p_partkey = ps_partkey
    AND s_suppkey = ps_suppkey
    AND p_size = CAST (15 AS smallint)
    AND p_type LIKE '%BRASS'
    AND s_nationkey = n_nationkey
    AND n_regionkey = r_regionkey
    AND r_name = 'EUROPE'
    AND ps_supplycost
        = (
                SELECT
                    min(ps_supplycost)
                FROM
                    partsupp, supplier, nation, region
                WHERE
                    p_partkey = ps_partkey
                    AND s_suppkey = ps_suppkey
                    AND s_nationkey = n_nationkey
                    AND n_regionkey = r_regionkey
                    AND r_name = 'EUROPE'
            )
ORDER BY
    s_acctbal DESC, n_name, s_name, p_partkey;


query T multiline
-- Explain index on Q02
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q02;
----
materialize.public.q02_primary_idx:
  ArrangeBy keys=[[#0{s_acctbal}..=#7{s_comment}]] // { arity: 8 }
    ReadGlobalFromSameDataflow materialize.public.q02 // { arity: 8 }

materialize.public.q02:
  With
    cte l0 =
      ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 }
        ReadIndex on=supplier pk_supplier_suppkey=[delta join lookup] fk_supplier_nationkey=[delta join lookup] // { arity: 7 }
    cte l1 =
      ArrangeBy keys=[[#0{ps_partkey}], [#1{ps_suppkey}]] // { arity: 5 }
        ReadIndex on=partsupp fk_partsupp_partkey=[delta join lookup] fk_partsupp_suppkey=[delta join lookup] // { arity: 5 }
    cte l2 =
      ArrangeBy keys=[[#0{n_nationkey}], [#2{n_regionkey}]] // { arity: 4 }
        ReadIndex on=nation pk_nation_nationkey=[delta join lookup] fk_nation_regionkey=[delta join lookup] // { arity: 4 }
    cte l3 =
      ArrangeBy keys=[[#0{r_regionkey}]] // { arity: 3 }
        ReadIndex on=region pk_region_regionkey=[delta join lookup] // { arity: 3 }
    cte l4 =
      Project (#0{p_partkey}, #2{p_mfgr}, #10{s_name}, #11{s_address}, #13{s_phone}..=#15{s_comment}, #19{ps_supplycost}, #22{n_name}) // { arity: 9 }
        Filter (#5{p_size} = 15) AND (#26{r_name} = "EUROPE") AND like["%BRASS"](varchar_to_text(#4{p_type})) // { arity: 28 }
          Join on=(#0{p_partkey} = #16{ps_partkey} AND #9{s_suppkey} = #17{ps_suppkey} AND #12{s_nationkey} = #21{n_nationkey} AND #23{n_regionkey} = #25{r_regionkey}) type=delta // { arity: 28 }
            implementation
              %0:part » %2:l1[#0{ps_partkey}]KA » %1:l0[#0{s_suppkey}]KA » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef
              %1:l0 » %2:l1[#1{ps_suppkey}]KA » %0:part[#0{p_partkey}]KAelf » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef
              %2:l1 » %0:part[#0{p_partkey}]KAelf » %1:l0[#0{s_suppkey}]KA » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef
              %3:l2 » %4:l3[#0{r_regionkey}]KAef » %1:l0[#3{s_nationkey}]KA » %2:l1[#1{ps_suppkey}]KA » %0:part[#0{p_partkey}]KAelf
              %4:l3 » %3:l2[#2{n_regionkey}]KA » %1:l0[#3{s_nationkey}]KA » %2:l1[#1{ps_suppkey}]KA » %0:part[#0{p_partkey}]KAelf
            ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 }
              ReadIndex on=part pk_part_partkey=[delta join 1st input (full scan)] // { arity: 9 }
            Get l0 // { arity: 7 }
            Get l1 // { arity: 5 }
            Get l2 // { arity: 4 }
            Get l3 // { arity: 3 }
  Return // { arity: 8 }
    Project (#5{s_acctbal}, #2{s_name}, #8{n_name}, #0{p_partkey}, #1{p_mfgr}, #3{s_address}, #4{s_phone}, #6{s_comment}) // { arity: 8 }
      Join on=(#0{p_partkey} = #9{p_partkey} AND #7{ps_supplycost} = #10{min_ps_supplycost}) type=differential // { arity: 11 }
        implementation
          %1[#0, #1]UKK » %0:l4[#0, #7{ps_supplycost}]KK
        ArrangeBy keys=[[#0{p_partkey}, #7{ps_supplycost}]] // { arity: 9 }
          Get l4 // { arity: 9 }
        ArrangeBy keys=[[#0{p_partkey}, #1{min_ps_supplycost}]] // { arity: 2 }
          Reduce group_by=[#0{p_partkey}] aggregates=[min(#1{ps_supplycost})] // { arity: 2 }
            Project (#0{p_partkey}, #4{ps_supplycost}) // { arity: 2 }
              Filter (#18{r_name} = "EUROPE") // { arity: 20 }
                Join on=(#0{p_partkey} = #1{ps_partkey} AND #2{ps_suppkey} = #6{s_suppkey} AND #9{s_nationkey} = #13{n_nationkey} AND #15{n_regionkey} = #17{r_regionkey}) type=delta // { arity: 20 }
                  implementation
                    %0 » %1:l1[#0{ps_partkey}]KA » %2:l0[#0{s_suppkey}]KA » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef
                    %1:l1 » %0[#0]UKA » %2:l0[#0{s_suppkey}]KA » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef
                    %2:l0 » %1:l1[#1{ps_suppkey}]KA » %0[#0]UKA » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef
                    %3:l2 » %4:l3[#0{r_regionkey}]KAef » %2:l0[#3{s_nationkey}]KA » %1:l1[#1{ps_suppkey}]KA » %0[#0]UKA
                    %4:l3 » %3:l2[#2{n_regionkey}]KA » %2:l0[#3{s_nationkey}]KA » %1:l1[#1{ps_suppkey}]KA » %0[#0]UKA
                  ArrangeBy keys=[[#0{p_partkey}]] // { arity: 1 }
                    Distinct project=[#0{p_partkey}] // { arity: 1 }
                      Project (#0{p_partkey}) // { arity: 1 }
                        Get l4 // { arity: 9 }
                  Get l1 // { arity: 5 }
                  Get l0 // { arity: 7 }
                  Get l2 // { arity: 4 }
                  Get l3 // { arity: 3 }

Used Indexes:
  - materialize.public.pk_nation_nationkey (delta join lookup)
  - materialize.public.fk_nation_regionkey (delta join lookup)
  - materialize.public.pk_region_regionkey (delta join lookup)
  - materialize.public.pk_part_partkey (delta join 1st input (full scan))
  - materialize.public.pk_supplier_suppkey (delta join lookup)
  - materialize.public.fk_supplier_nationkey (delta join lookup)
  - materialize.public.fk_partsupp_partkey (delta join lookup)
  - materialize.public.fk_partsupp_suppkey (delta join lookup)

Target cluster: quickstart

EOF


statement ok
-- Query 03
CREATE VIEW Q03 AS
SELECT
    l_orderkey,
    sum(l_extendedprice * (1 - l_discount)) AS revenue,
    o_orderdate,
    o_shippriority
FROM
    customer,
    orders,
    lineitem
WHERE
    c_mktsegment = 'BUILDING'
    AND c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND o_orderdate < DATE '1995-03-15'
    AND l_shipdate > DATE '1995-03-15'
GROUP BY
    l_orderkey,
    o_orderdate,
    o_shippriority
ORDER BY
    revenue DESC,
    o_orderdate;


query T multiline
-- Explain index on Q03
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q03;
----
materialize.public.q03_primary_idx:
  ArrangeBy keys=[[#0{l_orderkey}, #2{o_orderdate}, #3{o_shippriority}]] // { arity: 4 }
    ReadGlobalFromSameDataflow materialize.public.q03 // { arity: 4 }

materialize.public.q03:
  Project (#0{o_orderkey}, #3{sum}, #1{o_orderdate}, #2{o_shippriority}) // { arity: 4 }
    Reduce group_by=[#0{o_orderkey}..=#2{o_shippriority}] aggregates=[sum((#3{l_extendedprice} * (1 - #4{l_discount})))] // { arity: 4 }
      Project (#8{o_orderkey}, #12{o_orderdate}, #15{o_shippriority}, #22{l_extendedprice}, #23{l_discount}) // { arity: 5 }
        Filter (#6{c_mktsegment} = "BUILDING") AND (#12{o_orderdate} < 1995-03-15) AND (#27{l_shipdate} > 1995-03-15) // { arity: 33 }
          Join on=(#0{c_custkey} = #9{o_custkey} AND #8{o_orderkey} = #17{l_orderkey}) type=delta // { arity: 33 }
            implementation
              %0:customer » %1:orders[#1{o_custkey}]KAif » %2:lineitem[#0{l_orderkey}]KAif
              %1:orders » %0:customer[#0{c_custkey}]KAef » %2:lineitem[#0{l_orderkey}]KAif
              %2:lineitem » %1:orders[#0{o_orderkey}]KAif » %0:customer[#0{c_custkey}]KAef
            ArrangeBy keys=[[#0{c_custkey}]] // { arity: 8 }
              ReadIndex on=customer pk_customer_custkey=[delta join 1st input (full scan)] // { arity: 8 }
            ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 }
              ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 }
            ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 16 }
              ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] // { arity: 16 }

Used Indexes:
  - materialize.public.pk_customer_custkey (delta join 1st input (full scan))
  - materialize.public.pk_orders_orderkey (delta join lookup)
  - materialize.public.fk_orders_custkey (delta join lookup)
  - materialize.public.fk_lineitem_orderkey (delta join lookup)

Target cluster: quickstart

EOF


statement ok
-- Query 04
CREATE VIEW Q04 AS
SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' month
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;


query T multiline
-- Explain index on Q04
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q04;
----
materialize.public.q04_primary_idx:
  ArrangeBy keys=[[#0{o_orderpriority}]] // { arity: 2 }
    ReadGlobalFromSameDataflow materialize.public.q04 // { arity: 2 }

materialize.public.q04:
  Reduce group_by=[#0{o_orderpriority}] aggregates=[count(*)] // { arity: 2 }
    Project (#5{o_orderpriority}) // { arity: 1 }
      Filter (#4{o_orderdate} >= 1993-07-01) AND (date_to_timestamp(#4{o_orderdate}) < 1993-10-01 00:00:00) // { arity: 10 }
        Join on=(#0{o_orderkey} = #9{l_orderkey}) type=differential // { arity: 10 }
          implementation
            %1[#0]UKA » %0:orders[#0{o_orderkey}]KAiif
          ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 9 }
            ReadIndex on=orders pk_orders_orderkey=[differential join] // { arity: 9 }
          ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 1 }
            Distinct project=[#0{l_orderkey}] // { arity: 1 }
              Project (#0{l_orderkey}) // { arity: 1 }
                Filter (#11{l_commitdate} < #12{l_receiptdate}) // { arity: 16 }
                  ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 16 }

Used Indexes:
  - materialize.public.pk_orders_orderkey (differential join)
  - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***)

Target cluster: quickstart

EOF


statement ok
-- Query 05
CREATE VIEW Q05 AS
SELECT
    n_name,
    sum(l_extendedprice * (1 - l_discount)) AS revenue
FROM
    customer,
    orders,
    lineitem,
    supplier,
    nation,
    region
WHERE
    c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND l_suppkey = s_suppkey
    AND c_nationkey = s_nationkey
    AND s_nationkey = n_nationkey
    AND n_regionkey = r_regionkey
    AND r_name = 'ASIA'
    AND o_orderdate >= DATE '1994-01-01'
    AND o_orderdate < DATE '1995-01-01'
GROUP BY
    n_name
ORDER BY
    revenue DESC;


query T multiline
-- Explain index on Q05
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q05;
----
materialize.public.q05_primary_idx:
  ArrangeBy keys=[[#0{n_name}]] // { arity: 2 }
    ReadGlobalFromSameDataflow materialize.public.q05 // { arity: 2 }

materialize.public.q05:
  Reduce group_by=[#2{n_name}] aggregates=[sum((#0{l_extendedprice} * (1 - #1{l_discount})))] // { arity: 2 }
    Project (#19{l_extendedprice}, #20{l_discount}, #24{n_name}) // { arity: 3 }
      Filter (#28{r_name} = "ASIA") AND (#12{o_orderdate} < 1995-01-01) AND (#12{o_orderdate} >= 1994-01-01) // { arity: 30 }
        Join on=(#0{c_custkey} = #9{o_custkey} AND #3{c_nationkey} = #22{s_nationkey} = #23{n_nationkey} AND #8{o_orderkey} = #17{l_orderkey} AND #18{l_suppkey} = #21{s_suppkey} AND #25{n_regionkey} = #27{r_regionkey}) type=delta // { arity: 30 }
          implementation
            %0:customer » %1:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %3:supplier[#0{s_suppkey}, #1{s_nationkey}]KK » %4:nation[#0{n_nationkey}]KA » %5:region[#0{r_regionkey}]KAef
            %1:orders » %0:customer[#0{c_custkey}]KA » %2:lineitem[#0{l_orderkey}]KA » %3:supplier[#0{s_suppkey}, #1{s_nationkey}]KK » %4:nation[#0{n_nationkey}]KA » %5:region[#0{r_regionkey}]KAef
            %2:lineitem » %1:orders[#0{o_orderkey}]KAiif » %0:customer[#0{c_custkey}]KA » %3:supplier[#0{s_suppkey}, #1{s_nationkey}]KK » %4:nation[#0{n_nationkey}]KA » %5:region[#0{r_regionkey}]KAef
            %3:supplier » %0:customer[#3{c_nationkey}]KA » %1:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}, #1{l_suppkey}]KK » %4:nation[#0{n_nationkey}]KA » %5:region[#0{r_regionkey}]KAef
            %4:nation » %5:region[#0{r_regionkey}]KAef » %0:customer[#3{c_nationkey}]KA » %1:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %3:supplier[#0{s_suppkey}, #1{s_nationkey}]KK
            %5:region » %4:nation[#2{n_regionkey}]KA » %0:customer[#3{c_nationkey}]KA » %1:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %3:supplier[#0{s_suppkey}, #1{s_nationkey}]KK
          ArrangeBy keys=[[#0{c_custkey}], [#3{c_nationkey}]] // { arity: 8 }
            ReadIndex on=customer pk_customer_custkey=[delta join 1st input (full scan)] fk_customer_nationkey=[delta join lookup] // { arity: 8 }
          ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 }
            ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 }
          ArrangeBy keys=[[#0{l_orderkey}], [#0{l_orderkey}, #1{l_suppkey}]] // { arity: 4 }
            Project (#0{l_orderkey}, #2{l_suppkey}, #5{l_extendedprice}, #6{l_discount}) // { arity: 4 }
              ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 16 }
          ArrangeBy keys=[[#0{s_suppkey}, #1{s_nationkey}]] // { arity: 2 }
            Project (#0{s_suppkey}, #3{s_nationkey}) // { arity: 2 }
              Filter (#0{s_suppkey}) IS NOT NULL // { arity: 7 }
                ReadIndex on=supplier pk_supplier_suppkey=[*** full scan ***] // { arity: 7 }
          ArrangeBy keys=[[#0{n_nationkey}], [#2{n_regionkey}]] // { arity: 4 }
            ReadIndex on=nation pk_nation_nationkey=[delta join lookup] fk_nation_regionkey=[delta join lookup] // { arity: 4 }
          ArrangeBy keys=[[#0{r_regionkey}]] // { arity: 3 }
            ReadIndex on=region pk_region_regionkey=[delta join lookup] // { arity: 3 }

Used Indexes:
  - materialize.public.pk_nation_nationkey (delta join lookup)
  - materialize.public.fk_nation_regionkey (delta join lookup)
  - materialize.public.pk_region_regionkey (delta join lookup)
  - materialize.public.pk_supplier_suppkey (*** full scan ***)
  - materialize.public.pk_customer_custkey (delta join 1st input (full scan))
  - materialize.public.fk_customer_nationkey (delta join lookup)
  - materialize.public.pk_orders_orderkey (delta join lookup)
  - materialize.public.fk_orders_custkey (delta join lookup)
  - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***)

Target cluster: quickstart

EOF


statement ok
-- Query 06
CREATE VIEW Q06 AS
SELECT
    sum(l_extendedprice * l_discount) AS revenue
FROM
    lineitem
WHERE
    l_quantity < 24
    AND l_shipdate >= DATE '1994-01-01'
    AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' year
    AND l_discount BETWEEN 0.06 - 0.01 AND 0.07;


query T multiline
-- Explain index on Q06
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q06;
----
materialize.public.q06_primary_idx:
  ArrangeBy keys=[[#0{revenue}]] // { arity: 1 }
    ReadGlobalFromSameDataflow materialize.public.q06 // { arity: 1 }

materialize.public.q06:
  With
    cte l0 =
      Reduce aggregates=[sum((#0{l_extendedprice} * #1{l_discount}))] // { arity: 1 }
        Project (#5{l_extendedprice}, #6{l_discount}) // { arity: 2 }
          Filter (#4{l_quantity} < 24) AND (#6{l_discount} <= 0.07) AND (#6{l_discount} >= 0.05) AND (#10{l_shipdate} >= 1994-01-01) AND (date_to_timestamp(#10{l_shipdate}) < 1995-01-01 00:00:00) // { arity: 16 }
            ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 16 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l0 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

Used Indexes:
  - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***)

Target cluster: quickstart

EOF


statement ok
-- Query 07
CREATE VIEW Q07 AS
SELECT
    supp_nation,
    cust_nation,
    l_year,
    sum(volume) AS revenue
FROM
    (
        SELECT
            n1.n_name AS supp_nation,
            n2.n_name AS cust_nation,
            extract(year FROM l_shipdate) AS l_year,
            l_extendedprice * (1 - l_discount) AS volume
        FROM
            supplier,
            lineitem,
            orders,
            customer,
            nation n1,
            nation n2
        WHERE
            s_suppkey = l_suppkey
            AND o_orderkey = l_orderkey
            AND c_custkey = o_custkey
            AND s_nationkey = n1.n_nationkey
            AND c_nationkey = n2.n_nationkey
            AND (
                (n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY')
                or (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE')
            )
            AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
    ) AS shipping
GROUP BY
    supp_nation,
    cust_nation,
    l_year
ORDER BY
    supp_nation,
    cust_nation,
    l_year;


query T multiline
-- Explain index on Q07
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q07;
----
materialize.public.q07_primary_idx:
  ArrangeBy keys=[[#0{supp_nation}..=#2{l_year}]] // { arity: 4 }
    ReadGlobalFromSameDataflow materialize.public.q07 // { arity: 4 }

materialize.public.q07:
  With
    cte l0 =
      ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 }
        ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 }
  Return // { arity: 4 }
    Reduce group_by=[#3{n_name}, #4{n_name}, extract_year_d(#2{l_shipdate})] aggregates=[sum((#0{l_extendedprice} * (1 - #1{l_discount})))] // { arity: 4 }
      Project (#12{l_extendedprice}, #13{l_discount}, #17{l_shipdate}, #41{n_name}, #45{n_name}) // { arity: 5 }
        Filter (#17{l_shipdate} <= 1996-12-31) AND (#17{l_shipdate} >= 1995-01-01) AND (#48 OR #49) AND (#50 OR #51) AND ((#48 AND #51) OR (#49 AND #50)) // { arity: 52 }
          Map ((#41{n_name} = "FRANCE"), (#41{n_name} = "GERMANY"), (#45{n_name} = "FRANCE"), (#45{n_name} = "GERMANY")) // { arity: 52 }
            Join on=(#0{s_suppkey} = #9{l_suppkey} AND #3{s_nationkey} = #40{n_nationkey} AND #7{l_orderkey} = #23{o_orderkey} AND #24{o_custkey} = #32{c_custkey} AND #35{c_nationkey} = #44{n_nationkey}) type=delta // { arity: 48 }
              implementation
                %0:supplier » %4:l0[#0{n_nationkey}]KAef » %1:lineitem[#2{l_suppkey}]KAiif » %2:orders[#0{o_orderkey}]KA » %3:customer[#0{c_custkey}]KA » %5:l0[#0{n_nationkey}]KAef
                %1:lineitem » %0:supplier[#0{s_suppkey}]KA » %4:l0[#0{n_nationkey}]KAef » %2:orders[#0{o_orderkey}]KA » %3:customer[#0{c_custkey}]KA » %5:l0[#0{n_nationkey}]KAef
                %2:orders » %1:lineitem[#0{l_orderkey}]KAiif » %0:supplier[#0{s_suppkey}]KA » %4:l0[#0{n_nationkey}]KAef » %3:customer[#0{c_custkey}]KA » %5:l0[#0{n_nationkey}]KAef
                %3:customer » %5:l0[#0{n_nationkey}]KAef » %2:orders[#1{o_custkey}]KA » %1:lineitem[#0{l_orderkey}]KAiif » %0:supplier[#0{s_suppkey}]KA » %4:l0[#0{n_nationkey}]KAef
                %4:l0 » %0:supplier[#3{s_nationkey}]KA » %1:lineitem[#2{l_suppkey}]KAiif » %2:orders[#0{o_orderkey}]KA » %3:customer[#0{c_custkey}]KA » %5:l0[#0{n_nationkey}]KAef
                %5:l0 » %3:customer[#3{c_nationkey}]KA » %2:orders[#1{o_custkey}]KA » %1:lineitem[#0{l_orderkey}]KAiif » %0:supplier[#0{s_suppkey}]KA » %4:l0[#0{n_nationkey}]KAef
              ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 }
                ReadIndex on=supplier pk_supplier_suppkey=[delta join 1st input (full scan)] fk_supplier_nationkey=[delta join lookup] // { arity: 7 }
              ArrangeBy keys=[[#0{l_orderkey}], [#2{l_suppkey}]] // { arity: 16 }
                ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] fk_lineitem_suppkey=[delta join lookup] // { arity: 16 }
              ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 }
                ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 }
              ArrangeBy keys=[[#0{c_custkey}], [#3{c_nationkey}]] // { arity: 8 }
                ReadIndex on=customer pk_customer_custkey=[delta join lookup] fk_customer_nationkey=[delta join lookup] // { arity: 8 }
              Get l0 // { arity: 4 }
              Get l0 // { arity: 4 }

Used Indexes:
  - materialize.public.pk_nation_nationkey (delta join lookup)
  - materialize.public.pk_supplier_suppkey (delta join 1st input (full scan))
  - materialize.public.fk_supplier_nationkey (delta join lookup)
  - materialize.public.pk_customer_custkey (delta join lookup)
  - materialize.public.fk_customer_nationkey (delta join lookup)
  - materialize.public.pk_orders_orderkey (delta join lookup)
  - materialize.public.fk_orders_custkey (delta join lookup)
  - materialize.public.fk_lineitem_orderkey (delta join lookup)
  - materialize.public.fk_lineitem_suppkey (delta join lookup)

Target cluster: quickstart

EOF


statement ok
-- Query 08
CREATE VIEW Q08 AS
SELECT
    o_year,
    sum(case
        when nation = 'BRAZIL' then volume
        else 0
    end) / sum(volume) AS mkt_share
FROM
    (
        SELECT
            extract(year FROM o_orderdate) AS o_year,
            l_extendedprice * (1 - l_discount) AS volume,
            n2.n_name AS nation
        FROM
            part,
            supplier,
            lineitem,
            orders,
            customer,
            nation n1,
            nation n2,
            region
        WHERE
            p_partkey = l_partkey
            AND s_suppkey = l_suppkey
            AND l_orderkey = o_orderkey
            AND o_custkey = c_custkey
            AND c_nationkey = n1.n_nationkey
            AND n1.n_regionkey = r_regionkey
            AND r_name = 'AMERICA'
            AND s_nationkey = n2.n_nationkey
            AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
            AND p_type = 'ECONOMY ANODIZED STEEL'
    ) AS all_nations
GROUP BY
    o_year
ORDER BY
    o_year;


query T multiline
-- Explain index on Q08
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q08;
----
materialize.public.q08_primary_idx:
  ArrangeBy keys=[[#0{o_year}]] // { arity: 2 }
    ReadGlobalFromSameDataflow materialize.public.q08 // { arity: 2 }

materialize.public.q08:
  Project (#0, #3) // { arity: 2 }
    Map ((#1{sum} / #2{sum})) // { arity: 4 }
      Reduce group_by=[extract_year_d(#2{o_orderdate})] aggregates=[sum(case when (#3{n_name} = "BRAZIL") then (#0{l_extendedprice} * (1 - #1{l_discount})) else 0 end), sum((#0{l_extendedprice} * (1 - #1{l_discount})))] // { arity: 3 }
        Project (#21{l_extendedprice}, #22{l_discount}, #36{o_orderdate}, #54{n_name}) // { arity: 4 }
          Filter (#58{r_name} = "AMERICA") AND (#36{o_orderdate} <= 1996-12-31) AND (#36{o_orderdate} >= 1995-01-01) AND ("ECONOMY ANODIZED STEEL" = varchar_to_text(#4{p_type})) // { arity: 60 }
            Join on=(#0{p_partkey} = #17{l_partkey} AND #9{s_suppkey} = #18{l_suppkey} AND #12{s_nationkey} = #53{n_nationkey} AND #16{l_orderkey} = #32{o_orderkey} AND #33{o_custkey} = #41{c_custkey} AND #44{c_nationkey} = #49{n_nationkey} AND #51{n_regionkey} = #57{r_regionkey}) type=delta // { arity: 60 }
              implementation
                %0:part » %2:lineitem[#1{l_partkey}]KA » %3:orders[#0{o_orderkey}]KAiif » %1:supplier[#0{s_suppkey}]KA » %4:customer[#0{c_custkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef » %6:nation[#0{n_nationkey}]KA
                %1:supplier » %2:lineitem[#2{l_suppkey}]KA » %0:part[#0{p_partkey}]KAef » %3:orders[#0{o_orderkey}]KAiif » %4:customer[#0{c_custkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef » %6:nation[#0{n_nationkey}]KA
                %2:lineitem » %0:part[#0{p_partkey}]KAef » %3:orders[#0{o_orderkey}]KAiif » %1:supplier[#0{s_suppkey}]KA » %4:customer[#0{c_custkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef » %6:nation[#0{n_nationkey}]KA
                %3:orders » %2:lineitem[#0{l_orderkey}]KA » %0:part[#0{p_partkey}]KAef » %1:supplier[#0{s_suppkey}]KA » %4:customer[#0{c_custkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef » %6:nation[#0{n_nationkey}]KA
                %4:customer » %3:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %0:part[#0{p_partkey}]KAef » %1:supplier[#0{s_suppkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef » %6:nation[#0{n_nationkey}]KA
                %5:nation » %7:region[#0{r_regionkey}]KAef » %4:customer[#3{c_nationkey}]KA » %3:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %0:part[#0{p_partkey}]KAef » %1:supplier[#0{s_suppkey}]KA » %6:nation[#0{n_nationkey}]KA
                %6:nation » %1:supplier[#3{s_nationkey}]KA » %2:lineitem[#2{l_suppkey}]KA » %0:part[#0{p_partkey}]KAef » %3:orders[#0{o_orderkey}]KAiif » %4:customer[#0{c_custkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef
                %7:region » %5:nation[#2{n_regionkey}]KA » %4:customer[#3{c_nationkey}]KA » %3:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %0:part[#0{p_partkey}]KAef » %1:supplier[#0{s_suppkey}]KA » %6:nation[#0{n_nationkey}]KA
              ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 }
                ReadIndex on=part pk_part_partkey=[delta join 1st input (full scan)] // { arity: 9 }
              ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 }
                ReadIndex on=supplier pk_supplier_suppkey=[delta join lookup] fk_supplier_nationkey=[delta join lookup] // { arity: 7 }
              ArrangeBy keys=[[#0{l_orderkey}], [#1{l_partkey}], [#2{l_suppkey}]] // { arity: 16 }
                ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] fk_lineitem_partkey=[delta join lookup] fk_lineitem_suppkey=[delta join lookup] // { arity: 16 }
              ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 }
                ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 }
              ArrangeBy keys=[[#0{c_custkey}], [#3{c_nationkey}]] // { arity: 8 }
                ReadIndex on=customer pk_customer_custkey=[delta join lookup] fk_customer_nationkey=[delta join lookup] // { arity: 8 }
              ArrangeBy keys=[[#0{n_nationkey}], [#2{n_regionkey}]] // { arity: 4 }
                ReadIndex on=nation pk_nation_nationkey=[delta join lookup] fk_nation_regionkey=[delta join lookup] // { arity: 4 }
              ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 }
                ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 }
              ArrangeBy keys=[[#0{r_regionkey}]] // { arity: 3 }
                ReadIndex on=region pk_region_regionkey=[delta join lookup] // { arity: 3 }

Used Indexes:
  - materialize.public.pk_nation_nationkey (delta join lookup)
  - materialize.public.fk_nation_regionkey (delta join lookup)
  - materialize.public.pk_region_regionkey (delta join lookup)
  - materialize.public.pk_part_partkey (delta join 1st input (full scan))
  - materialize.public.pk_supplier_suppkey (delta join lookup)
  - materialize.public.fk_supplier_nationkey (delta join lookup)
  - materialize.public.pk_customer_custkey (delta join lookup)
  - materialize.public.fk_customer_nationkey (delta join lookup)
  - materialize.public.pk_orders_orderkey (delta join lookup)
  - materialize.public.fk_orders_custkey (delta join lookup)
  - materialize.public.fk_lineitem_orderkey (delta join lookup)
  - materialize.public.fk_lineitem_partkey (delta join lookup)
  - materialize.public.fk_lineitem_suppkey (delta join lookup)

Target cluster: quickstart

EOF


statement ok
-- Query 09
CREATE VIEW Q09 AS
SELECT
    nation,
    o_year,
    sum(amount) AS sum_profit
FROM
    (
        SELECT
            n_name AS nation,
            extract(year FROM o_orderdate) AS o_year,
            l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
        FROM
            part,
            supplier,
            lineitem,
            partsupp,
            orders,
            nation
        WHERE
            s_suppkey = l_suppkey
            AND ps_suppkey = l_suppkey
            AND ps_partkey = l_partkey
            AND p_partkey = l_partkey
            AND o_orderkey = l_orderkey
            AND s_nationkey = n_nationkey
            AND p_name like '%green%'
    ) AS profit
GROUP BY
    nation,
    o_year
ORDER BY
    nation,
    o_year DESC;


query T multiline
-- Explain index on Q09
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q09;
----
materialize.public.q09_primary_idx:
  ArrangeBy keys=[[#0{nation}, #1{o_year}]] // { arity: 3 }
    ReadGlobalFromSameDataflow materialize.public.q09 // { arity: 3 }

materialize.public.q09:
  Reduce group_by=[#5{n_name}, extract_year_d(#4{o_orderdate})] aggregates=[sum(((#1{l_extendedprice} * (1 - #2{l_discount})) - (#3{ps_supplycost} * #0{l_quantity})))] // { arity: 3 }
    Project (#20{l_quantity}..=#22{l_discount}, #35{ps_supplycost}, #41{o_orderdate}, #47{n_name}) // { arity: 6 }
      Filter like["%green%"](varchar_to_text(#1{p_name})) // { arity: 50 }
        Join on=(#0{p_partkey} = #17{l_partkey} = #32{ps_partkey} AND #9{s_suppkey} = #18{l_suppkey} = #33{ps_suppkey} AND #12{s_nationkey} = #46{n_nationkey} AND #16{l_orderkey} = #37{o_orderkey}) type=delta // { arity: 50 }
          implementation
            %0:part » %2:lineitem[#1{l_partkey}]KA » %3:partsupp[#0{ps_partkey}, #1{ps_suppkey}]KKA » %1:supplier[#0{s_suppkey}]KA » %4:orders[#0{o_orderkey}]KA » %5:nation[#0{n_nationkey}]KA
            %1:supplier » %2:lineitem[#2{l_suppkey}]KA » %3:partsupp[#0{ps_partkey}, #1{ps_suppkey}]KKA » %0:part[#0{p_partkey}]KAlf » %4:orders[#0{o_orderkey}]KA » %5:nation[#0{n_nationkey}]KA
            %2:lineitem » %3:partsupp[#0{ps_partkey}, #1{ps_suppkey}]KKA » %0:part[#0{p_partkey}]KAlf » %1:supplier[#0{s_suppkey}]KA » %4:orders[#0{o_orderkey}]KA » %5:nation[#0{n_nationkey}]KA
            %3:partsupp » %2:lineitem[#1{l_partkey}, #2{l_suppkey}]KKA » %0:part[#0{p_partkey}]KAlf » %1:supplier[#0{s_suppkey}]KA » %4:orders[#0{o_orderkey}]KA » %5:nation[#0{n_nationkey}]KA
            %4:orders » %2:lineitem[#0{l_orderkey}]KA » %3:partsupp[#0{ps_partkey}, #1{ps_suppkey}]KKA » %0:part[#0{p_partkey}]KAlf » %1:supplier[#0{s_suppkey}]KA » %5:nation[#0{n_nationkey}]KA
            %5:nation » %1:supplier[#3{s_nationkey}]KA » %2:lineitem[#2{l_suppkey}]KA » %3:partsupp[#0{ps_partkey}, #1{ps_suppkey}]KKA » %0:part[#0{p_partkey}]KAlf » %4:orders[#0{o_orderkey}]KA
          ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 }
            ReadIndex on=part pk_part_partkey=[delta join 1st input (full scan)] // { arity: 9 }
          ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 }
            ReadIndex on=supplier pk_supplier_suppkey=[delta join lookup] fk_supplier_nationkey=[delta join lookup] // { arity: 7 }
          ArrangeBy keys=[[#0{l_orderkey}], [#1{l_partkey}], [#1{l_partkey}, #2{l_suppkey}], [#2{l_suppkey}]] // { arity: 16 }
            ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] fk_lineitem_partkey=[delta join lookup] fk_lineitem_suppkey=[delta join lookup] fk_lineitem_partsuppkey=[delta join lookup] // { arity: 16 }
          ArrangeBy keys=[[#0{ps_partkey}, #1{ps_suppkey}]] // { arity: 5 }
            ReadIndex on=partsupp pk_partsupp_partkey_suppkey=[delta join lookup] // { arity: 5 }
          ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 9 }
            ReadIndex on=orders pk_orders_orderkey=[delta join lookup] // { arity: 9 }
          ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 }
            ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 }

Used Indexes:
  - materialize.public.pk_nation_nationkey (delta join lookup)
  - materialize.public.pk_part_partkey (delta join 1st input (full scan))
  - materialize.public.pk_supplier_suppkey (delta join lookup)
  - materialize.public.fk_supplier_nationkey (delta join lookup)
  - materialize.public.pk_partsupp_partkey_suppkey (delta join lookup)
  - materialize.public.pk_orders_orderkey (delta join lookup)
  - materialize.public.fk_lineitem_orderkey (delta join lookup)
  - materialize.public.fk_lineitem_partkey (delta join lookup)
  - materialize.public.fk_lineitem_suppkey (delta join lookup)
  - materialize.public.fk_lineitem_partsuppkey (delta join lookup)

Target cluster: quickstart

EOF


statement ok
-- Query 10
CREATE VIEW Q10 AS
SELECT
    c_custkey,
    c_name,
    sum(l_extendedprice * (1 - l_discount)) AS revenue,
    c_acctbal,
    n_name,
    c_address,
    c_phone,
    c_comment
FROM
    customer,
    orders,
    lineitem,
    nation
WHERE
    c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND o_orderdate >= DATE '1993-10-01'
    AND o_orderdate < DATE '1994-01-01'
    AND o_orderdate < DATE '1993-10-01' + INTERVAL '3' month
    AND l_returnflag = 'R'
    AND c_nationkey = n_nationkey
GROUP BY
    c_custkey,
    c_name,
    c_acctbal,
    c_phone,
    n_name,
    c_address,
    c_comment
ORDER BY
    revenue DESC;


query T multiline
-- Explain index on Q10
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q10;
----
materialize.public.q10_primary_idx:
  ArrangeBy keys=[[#0{c_custkey}, #1{c_name}, #3{c_acctbal}..=#7{c_comment}]] // { arity: 8 }
    ReadGlobalFromSameDataflow materialize.public.q10 // { arity: 8 }

materialize.public.q10:
  Project (#0{c_custkey}, #1{c_name}, #7{sum}, #2{c_acctbal}, #4{n_name}, #5{c_address}, #3{c_phone}, #6{c_comment}) // { arity: 8 }
    Reduce group_by=[#0{c_custkey}, #1{c_name}, #4{c_acctbal}, #3{c_phone}, #8{n_name}, #2{c_address}, #5{c_comment}] aggregates=[sum((#6{l_extendedprice} * (1 - #7{l_discount})))] // { arity: 8 }
      Project (#0{c_custkey}..=#2{c_address}, #4{c_phone}, #5{c_acctbal}, #7{c_comment}, #22{l_extendedprice}, #23{l_discount}, #34{n_name}) // { arity: 9 }
        Filter (#25{l_returnflag} = "R") AND (#12{o_orderdate} < 1994-01-01) AND (#12{o_orderdate} >= 1993-10-01) AND (date_to_timestamp(#12{o_orderdate}) < 1994-01-01 00:00:00) // { arity: 37 }
          Join on=(#0{c_custkey} = #9{o_custkey} AND #3{c_nationkey} = #33{n_nationkey} AND #8{o_orderkey} = #17{l_orderkey}) type=delta // { arity: 37 }
            implementation
              %0:customer » %1:orders[#1{o_custkey}]KAiiif » %2:lineitem[#0{l_orderkey}]KAef » %3:nation[#0{n_nationkey}]KA
              %1:orders » %2:lineitem[#0{l_orderkey}]KAef » %0:customer[#0{c_custkey}]KA » %3:nation[#0{n_nationkey}]KA
              %2:lineitem » %1:orders[#0{o_orderkey}]KAiiif » %0:customer[#0{c_custkey}]KA » %3:nation[#0{n_nationkey}]KA
              %3:nation » %0:customer[#3{c_nationkey}]KA » %1:orders[#1{o_custkey}]KAiiif » %2:lineitem[#0{l_orderkey}]KAef
            ArrangeBy keys=[[#0{c_custkey}], [#3{c_nationkey}]] // { arity: 8 }
              ReadIndex on=customer pk_customer_custkey=[delta join 1st input (full scan)] fk_customer_nationkey=[delta join lookup] // { arity: 8 }
            ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 }
              ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 }
            ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 16 }
              ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] // { arity: 16 }
            ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 }
              ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 }

Used Indexes:
  - materialize.public.pk_nation_nationkey (delta join lookup)
  - materialize.public.pk_customer_custkey (delta join 1st input (full scan))
  - materialize.public.fk_customer_nationkey (delta join lookup)
  - materialize.public.pk_orders_orderkey (delta join lookup)
  - materialize.public.fk_orders_custkey (delta join lookup)
  - materialize.public.fk_lineitem_orderkey (delta join lookup)

Target cluster: quickstart

EOF


statement ok
-- Query 11
CREATE VIEW Q11 AS
SELECT
    ps_partkey,
    sum(ps_supplycost * ps_availqty) AS value
FROM
    partsupp,
    supplier,
    nation
WHERE
    ps_suppkey = s_suppkey
    AND s_nationkey = n_nationkey
    AND n_name = 'GERMANY'
GROUP BY
    ps_partkey having
        sum(ps_supplycost * ps_availqty) > (
            SELECT
                sum(ps_supplycost * ps_availqty) * 0.0001
            FROM
                partsupp,
                supplier,
                nation
            WHERE
                ps_suppkey = s_suppkey
                AND s_nationkey = n_nationkey
                AND n_name = 'GERMANY'
        )
ORDER BY
    value DESC;


query T multiline
-- Explain index on Q11
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q11;
----
materialize.public.q11_primary_idx:
  ArrangeBy keys=[[#0{ps_partkey}]] // { arity: 2 }
    ReadGlobalFromSameDataflow materialize.public.q11 // { arity: 2 }

materialize.public.q11:
  With
    cte l0 =
      Project (#0{ps_partkey}, #2{ps_availqty}, #3{ps_supplycost}) // { arity: 3 }
        Filter (#13{n_name} = "GERMANY") // { arity: 16 }
          Join on=(#1{ps_suppkey} = #5{s_suppkey} AND #8{s_nationkey} = #12{n_nationkey}) type=delta // { arity: 16 }
            implementation
              %0:partsupp » %1:supplier[#0{s_suppkey}]KA » %2:nation[#0{n_nationkey}]KAef
              %1:supplier » %2:nation[#0{n_nationkey}]KAef » %0:partsupp[#1{ps_suppkey}]KA
              %2:nation » %1:supplier[#3{s_nationkey}]KA » %0:partsupp[#1{ps_suppkey}]KA
            ArrangeBy keys=[[#1{ps_suppkey}]] // { arity: 5 }
              ReadIndex on=partsupp fk_partsupp_suppkey=[delta join 1st input (full scan)] // { arity: 5 }
            ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 }
              ReadIndex on=supplier pk_supplier_suppkey=[delta join lookup] fk_supplier_nationkey=[delta join lookup] // { arity: 7 }
            ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 }
              ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 }
  Return // { arity: 2 }
    Project (#0{ps_partkey}, #1{sum}) // { arity: 2 }
      Filter (#1{sum} > (#2{sum} * 0.0001)) // { arity: 3 }
        CrossJoin type=differential // { arity: 3 }
          implementation
            %1[×]UA » %0[×]
          ArrangeBy keys=[[]] // { arity: 2 }
            Reduce group_by=[#0{ps_partkey}] aggregates=[sum((#2{ps_supplycost} * integer_to_numeric(#1{ps_availqty})))] // { arity: 2 }
              Get l0 // { arity: 3 }
          ArrangeBy keys=[[]] // { arity: 1 }
            Reduce aggregates=[sum((#1{ps_supplycost} * integer_to_numeric(#0{ps_availqty})))] // { arity: 1 }
              Project (#1{ps_availqty}, #2{ps_supplycost}) // { arity: 2 }
                Get l0 // { arity: 3 }

Used Indexes:
  - materialize.public.pk_nation_nationkey (delta join lookup)
  - materialize.public.pk_supplier_suppkey (delta join lookup)
  - materialize.public.fk_supplier_nationkey (delta join lookup)
  - materialize.public.fk_partsupp_suppkey (delta join 1st input (full scan))

Target cluster: quickstart

EOF


statement ok
-- Query 12
CREATE VIEW Q12 AS
SELECT
    l_shipmode,
    sum(case
        when o_orderpriority = '1-URGENT'
            or o_orderpriority = '2-HIGH'
            then 1
        else 0
    end) AS high_line_count,
    sum(case
        when o_orderpriority <> '1-URGENT'
            AND o_orderpriority <> '2-HIGH'
            then 1
        else 0
    end) AS low_line_count
FROM
    orders,
    lineitem
WHERE
    o_orderkey = l_orderkey
    AND l_shipmode IN ('MAIL', 'SHIP')
    AND l_commitdate < l_receiptdate
    AND l_shipdate < l_commitdate
    AND l_receiptdate >= DATE '1994-01-01'
    AND l_receiptdate < DATE '1994-01-01' + INTERVAL '1' year
GROUP BY
    l_shipmode
ORDER BY
    l_shipmode;


query T multiline
-- Explain index on Q12
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q12;
----
materialize.public.q12_primary_idx:
  ArrangeBy keys=[[#0{l_shipmode}]] // { arity: 3 }
    ReadGlobalFromSameDataflow materialize.public.q12 // { arity: 3 }

materialize.public.q12:
  Reduce group_by=[#1{l_shipmode}] aggregates=[sum(case when ((#0{o_orderpriority} = "2-HIGH") OR (#0{o_orderpriority} = "1-URGENT")) then 1 else 0 end), sum(case when ((#0{o_orderpriority} != "2-HIGH") AND (#0{o_orderpriority} != "1-URGENT")) then 1 else 0 end)] // { arity: 3 }
    Project (#5{o_orderpriority}, #23{l_shipmode}) // { arity: 2 }
      Filter (#21{l_receiptdate} >= 1994-01-01) AND (#19{l_shipdate} < #20{l_commitdate}) AND (#20{l_commitdate} < #21{l_receiptdate}) AND (date_to_timestamp(#21{l_receiptdate}) < 1995-01-01 00:00:00) AND ((#23{l_shipmode} = "MAIL") OR (#23{l_shipmode} = "SHIP")) // { arity: 25 }
        Join on=(#0{o_orderkey} = #9{l_orderkey}) type=differential // { arity: 25 }
          implementation
            %1:lineitem[#0{l_orderkey}]KAeiif » %0:orders[#0{o_orderkey}]KAeiif
          ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 9 }
            ReadIndex on=orders pk_orders_orderkey=[differential join] // { arity: 9 }
          ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 16 }
            ReadIndex on=lineitem fk_lineitem_orderkey=[differential join] // { arity: 16 }

Used Indexes:
  - materialize.public.pk_orders_orderkey (differential join)
  - materialize.public.fk_lineitem_orderkey (differential join)

Target cluster: quickstart

EOF


statement ok
-- Query 13
CREATE VIEW Q13 AS
SELECT
    c_count,
    count(*) AS custdist
FROM
    (
        SELECT
            c_custkey,
            count(o_orderkey) c_count -- workaround for no column aliases
        FROM
            customer LEFT OUTER JOIN orders ON
                c_custkey = o_custkey
                AND o_comment NOT LIKE '%special%requests%'
        GROUP BY
            c_custkey
    ) AS c_orders -- (c_custkey, c_count) -- no column aliases yet
GROUP BY
    c_count
ORDER BY
    custdist DESC,
    c_count DESC;


query T multiline
-- Explain index on Q13
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q13;
----
materialize.public.q13_primary_idx:
  ArrangeBy keys=[[#0{c_count}]] // { arity: 2 }
    ReadGlobalFromSameDataflow materialize.public.q13 // { arity: 2 }

materialize.public.q13:
  With
    cte l0 =
      ArrangeBy keys=[[#0{c_custkey}]] // { arity: 8 }
        ReadIndex on=customer pk_customer_custkey=[differential join] // { arity: 8 }
    cte l1 =
      Project (#0{c_custkey}, #8{o_orderkey}) // { arity: 2 }
        Filter NOT(like["%special%requests%"](varchar_to_text(#16{o_comment}))) // { arity: 17 }
          Join on=(#0{c_custkey} = #9{o_custkey}) type=differential // { arity: 17 }
            implementation
              %1:orders[#1{o_custkey}]KAf » %0:l0[#0{c_custkey}]KAf
            Get l0 // { arity: 8 }
            ArrangeBy keys=[[#1{o_custkey}]] // { arity: 9 }
              ReadIndex on=orders fk_orders_custkey=[differential join] // { arity: 9 }
  Return // { arity: 2 }
    Reduce group_by=[#0{count_o_orderkey}] aggregates=[count(*)] // { arity: 2 }
      Project (#1{count_o_orderkey}) // { arity: 1 }
        Reduce group_by=[#0{c_custkey}] aggregates=[count(#1{o_orderkey})] // { arity: 2 }
          Union // { arity: 2 }
            Map (null) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Project (#0{c_custkey}) // { arity: 1 }
                    Join on=(#0{c_custkey} = #8{c_custkey}) type=differential // { arity: 9 }
                      implementation
                        %1[#0]UKA » %0:l0[#0{c_custkey}]KA
                      Get l0 // { arity: 8 }
                      ArrangeBy keys=[[#0{c_custkey}]] // { arity: 1 }
                        Distinct project=[#0{c_custkey}] // { arity: 1 }
                          Project (#0{c_custkey}) // { arity: 1 }
                            Get l1 // { arity: 2 }
                Project (#0{c_custkey}) // { arity: 1 }
                  ReadIndex on=customer pk_customer_custkey=[*** full scan ***] // { arity: 8 }
            Get l1 // { arity: 2 }

Used Indexes:
  - materialize.public.pk_customer_custkey (*** full scan ***, differential join)
  - materialize.public.fk_orders_custkey (differential join)

Target cluster: quickstart

EOF


statement ok
-- Query 14
CREATE VIEW Q14 AS
SELECT
    100.00 * sum(case
        when p_type like 'PROMO%'
            then l_extendedprice * (1 - l_discount)
        else 0
    end) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue
FROM
    lineitem,
    part
WHERE
    l_partkey = p_partkey
    AND l_shipdate >= DATE '1995-09-01'
    AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' month;


query T multiline
-- Explain index on Q14
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q14;
----
materialize.public.q14_primary_idx:
  ArrangeBy keys=[[#0{promo_revenue}]] // { arity: 1 }
    ReadGlobalFromSameDataflow materialize.public.q14 // { arity: 1 }

materialize.public.q14:
  With
    cte l0 =
      Reduce aggregates=[sum(case when like["PROMO%"](varchar_to_text(#2{p_type})) then (#0{l_extendedprice} * (1 - #1{l_discount})) else 0 end), sum((#0{l_extendedprice} * (1 - #1{l_discount})))] // { arity: 2 }
        Project (#5{l_extendedprice}, #6{l_discount}, #20{p_type}) // { arity: 3 }
          Filter (#10{l_shipdate} >= 1995-09-01) AND (date_to_timestamp(#10{l_shipdate}) < 1995-10-01 00:00:00) // { arity: 25 }
            Join on=(#1{l_partkey} = #16{p_partkey}) type=differential // { arity: 25 }
              implementation
                %0:lineitem[#1{l_partkey}]KAiif » %1:part[#0{p_partkey}]KAiif
              ArrangeBy keys=[[#1{l_partkey}]] // { arity: 16 }
                ReadIndex on=lineitem fk_lineitem_partkey=[differential join] // { arity: 16 }
              ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 }
                ReadIndex on=part pk_part_partkey=[differential join] // { arity: 9 }
  Return // { arity: 1 }
    Project (#2) // { arity: 1 }
      Map (((100 * #0{sum}) / #1{sum})) // { arity: 3 }
        Union // { arity: 2 }
          Get l0 // { arity: 2 }
          Map (null, null) // { arity: 2 }
            Union // { arity: 0 }
              Negate // { arity: 0 }
                Project () // { arity: 0 }
                  Get l0 // { arity: 2 }
              Constant // { arity: 0 }
                - ()

Used Indexes:
  - materialize.public.pk_part_partkey (differential join)
  - materialize.public.fk_lineitem_partkey (differential join)

Target cluster: quickstart

EOF


statement ok
create view revenue (supplier_no, total_revenue) as
    SELECT
        l_suppkey,
        sum(l_extendedprice * (1 - l_discount))
    FROM
        lineitem
    WHERE
        l_shipdate >= DATE '1996-01-01'
        AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' month
    GROUP BY
        l_suppkey

statement ok
-- Query 15
CREATE VIEW Q15 AS
SELECT
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
FROM
    supplier,
    revenue
WHERE
    s_suppkey = supplier_no
    AND total_revenue = (
        SELECT
            max(total_revenue)
        FROM
            revenue
    )
ORDER BY
    s_suppkey;


query T multiline
-- Explain index on Q15
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q15;
----
materialize.public.q15_primary_idx:
  ArrangeBy keys=[[#0{s_suppkey}..=#4{total_revenue}]] // { arity: 5 }
    ReadGlobalFromSameDataflow materialize.public.q15 // { arity: 5 }

materialize.public.q15:
  With
    cte l0 =
      Reduce group_by=[#0{l_suppkey}] aggregates=[sum((#1{l_extendedprice} * (1 - #2{l_discount})))] // { arity: 2 }
        Project (#2{l_suppkey}, #5{l_extendedprice}, #6{l_discount}) // { arity: 3 }
          Filter (#10{l_shipdate} >= 1996-01-01) AND (date_to_timestamp(#10{l_shipdate}) < 1996-04-01 00:00:00) // { arity: 16 }
            ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 16 }
  Return // { arity: 5 }
    Project (#0{s_suppkey}..=#2{s_address}, #4{s_phone}, #8{sum}) // { arity: 5 }
      Join on=(#0{s_suppkey} = #7{l_suppkey} AND #8{sum} = #9{max_sum}) type=delta // { arity: 10 }
        implementation
          %0:supplier » %1:l0[#0]UKA » %2[#0]UK
          %1:l0 » %2[#0]UK » %0:supplier[#0{s_suppkey}]KA
          %2 » %1:l0[#1{total_revenue}]K » %0:supplier[#0{s_suppkey}]KA
        ArrangeBy keys=[[#0{s_suppkey}]] // { arity: 7 }
          ReadIndex on=supplier pk_supplier_suppkey=[delta join 1st input (full scan)] // { arity: 7 }
        ArrangeBy keys=[[#0{l_suppkey}], [#1{sum}]] // { arity: 2 }
          Get l0 // { arity: 2 }
        ArrangeBy keys=[[#0{max_sum}]] // { arity: 1 }
          Reduce aggregates=[max(#0{sum})] // { arity: 1 }
            Project (#1{sum}) // { arity: 1 }
              Get l0 // { arity: 2 }

Used Indexes:
  - materialize.public.pk_supplier_suppkey (delta join 1st input (full scan))
  - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***)

Target cluster: quickstart

EOF


statement ok
-- Query 16
CREATE VIEW Q16 AS
SELECT
    p_brand,
    p_type,
    p_size,
    count(DISTINCT ps_suppkey) AS supplier_cnt
FROM
    partsupp,
    part
WHERE
    p_partkey = ps_partkey
    AND p_brand <> 'Brand#45'
    AND p_type NOT LIKE 'MEDIUM POLISHED%'
    AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
    AND ps_suppkey NOT IN (
        SELECT
            s_suppkey
        FROM
            supplier
        WHERE
            s_comment like '%Customer%Complaints%'
    )
GROUP BY
    p_brand,
    p_type,
    p_size
ORDER BY
    supplier_cnt DESC,
    p_brand,
    p_type,
    p_size;


query T multiline
-- Explain index on Q16
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q16;
----
materialize.public.q16_primary_idx:
  ArrangeBy keys=[[#0{p_brand}..=#2{p_size}]] // { arity: 4 }
    ReadGlobalFromSameDataflow materialize.public.q16 // { arity: 4 }

materialize.public.q16:
  With
    cte l0 =
      Project (#1{ps_suppkey}, #8{p_brand}..=#10{p_size}) // { arity: 4 }
        Filter (#8{p_brand} != "Brand#45") AND NOT(like["MEDIUM POLISHED%"](varchar_to_text(#9{p_type}))) AND ((#10{p_size} = 3) OR (#10{p_size} = 9) OR (#10{p_size} = 14) OR (#10{p_size} = 19) OR (#10{p_size} = 23) OR (#10{p_size} = 36) OR (#10{p_size} = 45) OR (#10{p_size} = 49)) // { arity: 14 }
          Join on=(#0{ps_partkey} = #5{p_partkey}) type=differential // { arity: 14 }
            implementation
              %1:part[#0{p_partkey}]KAef » %0:partsupp[#0{ps_partkey}]KAef
            ArrangeBy keys=[[#0{ps_partkey}]] // { arity: 5 }
              ReadIndex on=partsupp fk_partsupp_partkey=[differential join] // { arity: 5 }
            ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 }
              ReadIndex on=part pk_part_partkey=[differential join] // { arity: 9 }
    cte l1 =
      Distinct project=[#0{ps_suppkey}] // { arity: 1 }
        Project (#0{ps_suppkey}) // { arity: 1 }
          Get l0 // { arity: 4 }
  Return // { arity: 4 }
    Reduce group_by=[#1{p_brand}..=#3{p_size}] aggregates=[count(distinct #0{ps_suppkey})] // { arity: 4 }
      Project (#0{ps_suppkey}..=#3{p_size}) // { arity: 4 }
        Join on=(#0{ps_suppkey} = #4{ps_suppkey}) type=differential // { arity: 5 }
          implementation
            %0:l0[#0]K » %1[#0]K
          ArrangeBy keys=[[#0{ps_suppkey}]] // { arity: 4 }
            Get l0 // { arity: 4 }
          ArrangeBy keys=[[#0{ps_suppkey}]] // { arity: 1 }
            Union // { arity: 1 }
              Negate // { arity: 1 }
                Distinct project=[#0{ps_suppkey}] // { arity: 1 }
                  Project (#0{ps_suppkey}) // { arity: 1 }
                    Filter ((#1{s_suppkey}) IS NULL OR (#0{ps_suppkey} = #1{s_suppkey})) // { arity: 2 }
                      CrossJoin type=differential // { arity: 2 }
                        implementation
                          %1:supplier[×]lf » %0:l1[×]lf
                        ArrangeBy keys=[[]] // { arity: 1 }
                          Get l1 // { arity: 1 }
                        ArrangeBy keys=[[]] // { arity: 1 }
                          Project (#0{s_suppkey}) // { arity: 1 }
                            Filter like["%Customer%Complaints%"](varchar_to_text(#6{s_comment})) // { arity: 7 }
                              ReadIndex on=supplier pk_supplier_suppkey=[*** full scan ***] // { arity: 7 }
              Get l1 // { arity: 1 }

Used Indexes:
  - materialize.public.pk_part_partkey (differential join)
  - materialize.public.pk_supplier_suppkey (*** full scan ***)
  - materialize.public.fk_partsupp_partkey (differential join)

Target cluster: quickstart

EOF


statement ok
-- Query 17
CREATE VIEW Q17 AS
SELECT
  sum(l_extendedprice) / 7.0 AS avg_yearly
FROM
  lineitem,
  part
WHERE
  p_partkey = l_partkey
  AND p_brand = 'Brand#23'
  AND p_container = 'MED BOX'
  AND l_quantity < (
    SELECT
      0.2 * avg(l_quantity)
    FROM
      lineitem
    WHERE
      l_partkey = p_partkey
  );


query T multiline
-- Explain index on Q17
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q17;
----
materialize.public.q17_primary_idx:
  ArrangeBy keys=[[#0{avg_yearly}]] // { arity: 1 }
    ReadGlobalFromSameDataflow materialize.public.q17 // { arity: 1 }

materialize.public.q17:
  With
    cte l0 =
      ArrangeBy keys=[[#1{l_partkey}]] // { arity: 16 }
        ReadIndex on=lineitem fk_lineitem_partkey=[differential join] // { arity: 16 }
    cte l1 =
      Project (#1{l_partkey}, #4{l_quantity}, #5{l_extendedprice}) // { arity: 3 }
        Filter (#19{p_brand} = "Brand#23") AND (#22{p_container} = "MED BOX") // { arity: 25 }
          Join on=(#1{l_partkey} = #16{p_partkey}) type=differential // { arity: 25 }
            implementation
              %1:part[#0{p_partkey}]KAef » %0:l0[#1{l_partkey}]KAef
            Get l0 // { arity: 16 }
            ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 }
              ReadIndex on=part pk_part_partkey=[differential join] // { arity: 9 }
    cte l2 =
      Reduce aggregates=[sum(#0{l_extendedprice})] // { arity: 1 }
        Project (#2{l_extendedprice}) // { arity: 1 }
          Filter (#1{l_quantity} < (0.2 * (#4{sum_l_quantity} / bigint_to_numeric(case when (#5{count} = 0) then null else #5{count} end)))) // { arity: 6 }
            Join on=(#0{l_partkey} = #3{l_partkey}) type=differential // { arity: 6 }
              implementation
                %1[#0]UKA » %0:l1[#0]K
              ArrangeBy keys=[[#0{l_partkey}]] // { arity: 3 }
                Get l1 // { arity: 3 }
              ArrangeBy keys=[[#0{l_partkey}]] // { arity: 3 }
                Reduce group_by=[#0{l_partkey}] aggregates=[sum(#1{l_quantity}), count(*)] // { arity: 3 }
                  Project (#0{l_partkey}, #5{l_quantity}) // { arity: 2 }
                    Join on=(#0{l_partkey} = #2{l_partkey}) type=differential // { arity: 17 }
                      implementation
                        %0[#0{p_partkey}]UKA » %1:l0[#1{l_partkey}]KA
                      ArrangeBy keys=[[#0{l_partkey}]] // { arity: 1 }
                        Distinct project=[#0{l_partkey}] // { arity: 1 }
                          Project (#0{l_partkey}) // { arity: 1 }
                            Get l1 // { arity: 3 }
                      Get l0 // { arity: 16 }
  Return // { arity: 1 }
    Project (#1) // { arity: 1 }
      Map ((#0{sum_l_extendedprice} / 7)) // { arity: 2 }
        Union // { arity: 1 }
          Get l2 // { arity: 1 }
          Map (null) // { arity: 1 }
            Union // { arity: 0 }
              Negate // { arity: 0 }
                Project () // { arity: 0 }
                  Get l2 // { arity: 1 }
              Constant // { arity: 0 }
                - ()

Used Indexes:
  - materialize.public.pk_part_partkey (differential join)
  - materialize.public.fk_lineitem_partkey (differential join)

Target cluster: quickstart

EOF


statement ok
-- Query 18
CREATE VIEW Q18 AS
SELECT
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        GROUP BY
            l_orderkey having
                sum(l_quantity) > 300
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice
ORDER BY
    o_totalprice DESC,
    o_orderdate;


query T multiline
-- Explain index on Q18
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q18;
----
materialize.public.q18_primary_idx:
  ArrangeBy keys=[[#0{c_name}..=#4{o_totalprice}]] // { arity: 6 }
    ReadGlobalFromSameDataflow materialize.public.q18 // { arity: 6 }

materialize.public.q18:
  With
    cte l0 =
      ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 16 }
        ReadIndex on=lineitem fk_lineitem_orderkey=[differential join, delta join lookup] // { arity: 16 }
    cte l1 =
      Project (#0{c_custkey}, #1{c_name}, #8{o_orderkey}, #11{o_totalprice}, #12{o_orderdate}, #21{l_quantity}) // { arity: 6 }
        Join on=(#0{c_custkey} = #9{o_custkey} AND #8{o_orderkey} = #17{l_orderkey}) type=delta // { arity: 33 }
          implementation
            %0:customer » %1:orders[#1{o_custkey}]KA » %2:l0[#0{l_orderkey}]KA
            %1:orders » %0:customer[#0{c_custkey}]KA » %2:l0[#0{l_orderkey}]KA
            %2:l0 » %1:orders[#0{o_orderkey}]KA » %0:customer[#0{c_custkey}]KA
          ArrangeBy keys=[[#0{c_custkey}]] // { arity: 8 }
            ReadIndex on=customer pk_customer_custkey=[delta join 1st input (full scan)] // { arity: 8 }
          ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 }
            ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 }
          Get l0 // { arity: 16 }
  Return // { arity: 6 }
    Reduce group_by=[#1{c_name}, #0{c_custkey}, #2{o_orderkey}, #4{o_orderdate}, #3{o_totalprice}] aggregates=[sum(#5{l_quantity})] // { arity: 6 }
      Project (#0{c_custkey}..=#5{l_quantity}) // { arity: 6 }
        Filter (#7{sum_l_quantity} > 300) // { arity: 8 }
          Join on=(#2{o_orderkey} = #6{o_orderkey}) type=differential // { arity: 8 }
            implementation
              %1[#0]UKAif » %0:l1[#2]Kif
            ArrangeBy keys=[[#2{o_orderkey}]] // { arity: 6 }
              Get l1 // { arity: 6 }
            ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 2 }
              Reduce group_by=[#0{o_orderkey}] aggregates=[sum(#1{l_quantity})] // { arity: 2 }
                Project (#0{o_orderkey}, #5{l_quantity}) // { arity: 2 }
                  Join on=(#0{o_orderkey} = #1{l_orderkey}) type=differential // { arity: 17 }
                    implementation
                      %0[#0]UKA » %1:l0[#0{l_orderkey}]KA
                    ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 1 }
                      Distinct project=[#0{o_orderkey}] // { arity: 1 }
                        Project (#2{o_orderkey}) // { arity: 1 }
                          Get l1 // { arity: 6 }
                    Get l0 // { arity: 16 }

Used Indexes:
  - materialize.public.pk_customer_custkey (delta join 1st input (full scan))
  - materialize.public.pk_orders_orderkey (delta join lookup)
  - materialize.public.fk_orders_custkey (delta join lookup)
  - materialize.public.fk_lineitem_orderkey (differential join, delta join lookup)

Target cluster: quickstart

EOF


statement ok
-- Query 19
CREATE VIEW Q19 AS
SELECT
    sum(l_extendedprice* (1 - l_discount)) AS revenue
FROM
    lineitem,
    part
WHERE
    (
        p_partkey = l_partkey
        AND p_brand = 'Brand#12'
        AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
        AND l_quantity >= CAST (1 AS smallint) AND l_quantity <= CAST (1 + 10 AS smallint)
        AND p_size BETWEEN CAST (1 AS smallint) AND CAST (5 AS smallint)
        AND l_shipmode IN ('AIR', 'AIR REG')
        AND l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        AND p_brand = 'Brand#23'
        AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
        AND l_quantity >= CAST (10 AS smallint) AND l_quantity <= CAST (10 + 10 AS smallint)
        AND p_size BETWEEN CAST (1 AS smallint) AND CAST (10 AS smallint)
        AND l_shipmode IN ('AIR', 'AIR REG')
        AND l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        AND p_brand = 'Brand#34'
        AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
        AND l_quantity >= CAST (20 AS smallint) AND l_quantity <= CAST (20 + 10 AS smallint)
        AND p_size BETWEEN CAST (1 AS smallint) AND CAST (15 AS smallint)
        AND l_shipmode IN ('AIR', 'AIR REG')
        AND l_shipinstruct = 'DELIVER IN PERSON'
    );


query T multiline
-- Explain index on Q19
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q19;
----
materialize.public.q19_primary_idx:
  ArrangeBy keys=[[#0{revenue}]] // { arity: 1 }
    ReadGlobalFromSameDataflow materialize.public.q19 // { arity: 1 }

materialize.public.q19:
  With
    cte l0 =
      Reduce aggregates=[sum((#0{l_extendedprice} * (1 - #1{l_discount})))] // { arity: 1 }
        Project (#5{l_extendedprice}, #6{l_discount}) // { arity: 2 }
          Filter (#13{l_shipinstruct} = "DELIVER IN PERSON") AND (#21{p_size} >= 1) AND ((#14{l_shipmode} = "AIR") OR (#14{l_shipmode} = "AIR REG")) AND ((#25 AND #26) OR (#27 AND #28) OR (#29 AND #30)) AND ((#31 AND #32 AND #33) OR (#34 AND #35 AND #36) OR (#37 AND #38 AND #39)) AND ((#25 AND #26 AND #34 AND #35 AND #36) OR (#27 AND #28 AND #37 AND #38 AND #39) OR (#29 AND #30 AND #31 AND #32 AND #33)) // { arity: 40 }
            Map ((#4{l_quantity} <= 20), (#4{l_quantity} >= 10), (#4{l_quantity} <= 30), (#4{l_quantity} >= 20), (#4{l_quantity} <= 11), (#4{l_quantity} >= 1), (#19{p_brand} = "Brand#12"), (#21{p_size} <= 5), ((#22{p_container} = "SM BOX") OR (#22{p_container} = "SM PKG") OR (#22{p_container} = "SM CASE") OR (#22{p_container} = "SM PACK")), (#19{p_brand} = "Brand#23"), (#21{p_size} <= 10), ((#22{p_container} = "MED BAG") OR (#22{p_container} = "MED BOX") OR (#22{p_container} = "MED PKG") OR (#22{p_container} = "MED PACK")), (#19{p_brand} = "Brand#34"), (#21{p_size} <= 15), ((#22{p_container} = "LG BOX") OR (#22{p_container} = "LG PKG") OR (#22{p_container} = "LG CASE") OR (#22{p_container} = "LG PACK"))) // { arity: 40 }
              Join on=(#1{l_partkey} = #16{p_partkey}) type=differential // { arity: 25 }
                implementation
                  %1:part[#0{p_partkey}]KAeiiif » %0:lineitem[#1{l_partkey}]KAeiiiiif
                ArrangeBy keys=[[#1{l_partkey}]] // { arity: 16 }
                  ReadIndex on=lineitem fk_lineitem_partkey=[differential join] // { arity: 16 }
                ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 }
                  ReadIndex on=part pk_part_partkey=[differential join] // { arity: 9 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l0 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

Used Indexes:
  - materialize.public.pk_part_partkey (differential join)
  - materialize.public.fk_lineitem_partkey (differential join)

Target cluster: quickstart

EOF


statement ok
-- Query 20
CREATE VIEW Q20 AS
SELECT
    s_name,
    s_address
FROM
    supplier,
    nation
WHERE
    s_suppkey IN (
        SELECT
            ps_suppkey
        FROM
            partsupp
        WHERE
            ps_partkey IN (
                SELECT
                    p_partkey
                FROM
                    part
                WHERE
                    p_name like 'forest%'
            )
            AND ps_availqty > (
                SELECT
                    0.5 * sum(l_quantity)
                FROM
                    lineitem
                WHERE
                    l_partkey = ps_partkey
                    AND l_suppkey = ps_suppkey
                    AND l_shipdate >= DATE '1995-01-01'
                    AND l_shipdate < DATE '1995-01-01' + INTERVAL '1' year
            )
    )
    AND s_nationkey = n_nationkey
    AND n_name = 'CANADA'
ORDER BY
    s_name;


query T multiline
-- Explain index on Q20
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q20;
----
materialize.public.q20_primary_idx:
  ArrangeBy keys=[[#0{s_name}, #1{s_address}]] // { arity: 2 }
    ReadGlobalFromSameDataflow materialize.public.q20 // { arity: 2 }

materialize.public.q20:
  With
    cte l0 =
      Project (#0{s_suppkey}..=#2{s_address}) // { arity: 3 }
        Filter (#8{n_name} = "CANADA") // { arity: 11 }
          Join on=(#3{s_nationkey} = #7{n_nationkey}) type=differential // { arity: 11 }
            implementation
              %1:nation[#0{n_nationkey}]KAef » %0:supplier[#3{s_nationkey}]KAef
            ArrangeBy keys=[[#3{s_nationkey}]] // { arity: 7 }
              ReadIndex on=supplier fk_supplier_nationkey=[differential join] // { arity: 7 }
            ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 }
              ReadIndex on=nation pk_nation_nationkey=[differential join] // { arity: 4 }
    cte l1 =
      Project (#0{s_suppkey}..=#3{ps_availqty}) // { arity: 4 }
        Join on=(#1{ps_partkey} = #4{p_partkey}) type=delta // { arity: 5 }
          implementation
            %0 » %1:partsupp[×] » %2[#0]UKA
            %1:partsupp » %2[#0]UKA » %0[×]
            %2 » %1:partsupp[#0{ps_partkey}]KA » %0[×]
          ArrangeBy keys=[[]] // { arity: 1 }
            Distinct project=[#0{s_suppkey}] // { arity: 1 }
              Project (#0{s_suppkey}) // { arity: 1 }
                Get l0 // { arity: 3 }
          ArrangeBy keys=[[], [#0{ps_partkey}]] // { arity: 3 }
            Project (#0{ps_partkey}..=#2{ps_availqty}) // { arity: 3 }
              ReadIndex on=partsupp pk_partsupp_partkey_suppkey=[*** full scan ***] // { arity: 5 }
          ArrangeBy keys=[[#0{p_partkey}]] // { arity: 1 }
            Distinct project=[#0{p_partkey}] // { arity: 1 }
              Project (#0{p_partkey}) // { arity: 1 }
                Filter (#0{p_partkey}) IS NOT NULL AND like["forest%"](varchar_to_text(#1{p_name})) // { arity: 9 }
                  ReadIndex on=part pk_part_partkey=[*** full scan ***] // { arity: 9 }
  Return // { arity: 2 }
    Project (#1{s_name}, #2{s_address}) // { arity: 2 }
      Join on=(#0{s_suppkey} = #3{s_suppkey}) type=differential // { arity: 4 }
        implementation
          %1[#0]UKA » %0:l0[#0]K
        ArrangeBy keys=[[#0{s_suppkey}]] // { arity: 3 }
          Get l0 // { arity: 3 }
        ArrangeBy keys=[[#0{s_suppkey}]] // { arity: 1 }
          Distinct project=[#0{s_suppkey}] // { arity: 1 }
            Project (#0{s_suppkey}) // { arity: 1 }
              Filter (integer_to_numeric(#2{ps_availqty}) > #5) // { arity: 6 }
                Join on=(#0{s_suppkey} = #4{ps_suppkey} AND #1{ps_partkey} = #3{ps_partkey}) type=differential // { arity: 6 }
                  implementation
                    %1[#1, #0]UKK » %0:l1[#0{s_suppkey}, #1]KKf
                  ArrangeBy keys=[[#0{s_suppkey}, #1{ps_partkey}]] // { arity: 3 }
                    Project (#0{s_suppkey}, #1{ps_partkey}, #3{ps_availqty}) // { arity: 3 }
                      Filter (#0{s_suppkey} = #2{ps_suppkey}) // { arity: 4 }
                        Get l1 // { arity: 4 }
                  ArrangeBy keys=[[#1{ps_suppkey}, #0{ps_partkey}]] // { arity: 3 }
                    Project (#0{ps_partkey}, #1{ps_suppkey}, #3) // { arity: 3 }
                      Map ((0.5 * #2{sum_l_quantity})) // { arity: 4 }
                        Reduce group_by=[#0{ps_partkey}, #1{ps_suppkey}] aggregates=[sum(#2{l_quantity})] // { arity: 3 }
                          Project (#0{ps_partkey}, #1{ps_suppkey}, #6{l_quantity}) // { arity: 3 }
                            Filter (#12{l_shipdate} >= 1995-01-01) AND (date_to_timestamp(#12{l_shipdate}) < 1996-01-01 00:00:00) // { arity: 18 }
                              Join on=(#0{ps_partkey} = #3{l_partkey} AND #1{ps_suppkey} = #4{l_suppkey}) type=differential // { arity: 18 }
                                implementation
                                  %0[#0{ps_partkey}, #1{ps_suppkey}]UKKA » %1:lineitem[#1{l_partkey}, #2{l_suppkey}]KKAiif
                                ArrangeBy keys=[[#0{ps_partkey}, #1{ps_suppkey}]] // { arity: 2 }
                                  Distinct project=[#0{ps_partkey}, #1{ps_suppkey}] // { arity: 2 }
                                    Project (#1{ps_partkey}, #2{ps_suppkey}) // { arity: 2 }
                                      Get l1 // { arity: 4 }
                                ArrangeBy keys=[[#1{l_partkey}, #2{l_suppkey}]] // { arity: 16 }
                                  ReadIndex on=lineitem fk_lineitem_partsuppkey=[differential join] // { arity: 16 }

Used Indexes:
  - materialize.public.pk_nation_nationkey (differential join)
  - materialize.public.pk_part_partkey (*** full scan ***)
  - materialize.public.fk_supplier_nationkey (differential join)
  - materialize.public.pk_partsupp_partkey_suppkey (*** full scan ***)
  - materialize.public.fk_lineitem_partsuppkey (differential join)

Target cluster: quickstart

EOF


statement ok
-- Query 21
CREATE VIEW Q21 AS
SELECT
    s_name,
    count(*) AS numwait
FROM
    supplier,
    lineitem l1,
    orders,
    nation
WHERE
    s_suppkey = l1.l_suppkey
    AND o_orderkey = l1.l_orderkey
    AND o_orderstatus = 'F'
    AND l1.l_receiptdate > l1.l_commitdate
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem l2
        WHERE
            l2.l_orderkey = l1.l_orderkey
            AND l2.l_suppkey <> l1.l_suppkey
    )
    AND not EXISTS (
        SELECT
            *
        FROM
            lineitem l3
        WHERE
            l3.l_orderkey = l1.l_orderkey
            AND l3.l_suppkey <> l1.l_suppkey
            AND l3.l_receiptdate > l3.l_commitdate
    )
    AND s_nationkey = n_nationkey
    AND n_name = 'SAUDI ARABIA'
GROUP BY
    s_name
ORDER BY
    numwait DESC,
    s_name;


query T multiline
-- Explain index on Q21
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q21;
----
materialize.public.q21_primary_idx:
  ArrangeBy keys=[[#0{s_name}]] // { arity: 2 }
    ReadGlobalFromSameDataflow materialize.public.q21 // { arity: 2 }

materialize.public.q21:
  With
    cte l0 =
      Project (#0{s_suppkey}, #1{s_name}, #7{l_orderkey}) // { arity: 3 }
        Filter (#25{o_orderstatus} = "F") AND (#33{n_name} = "SAUDI ARABIA") AND (#19{l_receiptdate} > #18{l_commitdate}) // { arity: 36 }
          Join on=(#0{s_suppkey} = #9{l_suppkey} AND #3{s_nationkey} = #32{n_nationkey} AND #7{l_orderkey} = #23{o_orderkey}) type=delta // { arity: 36 }
            implementation
              %0:supplier » %3:nation[#0{n_nationkey}]KAef » %1:lineitem[#2{l_suppkey}]KAf » %2:orders[#0{o_orderkey}]KAef
              %1:lineitem » %2:orders[#0{o_orderkey}]KAef » %0:supplier[#0{s_suppkey}]KA » %3:nation[#0{n_nationkey}]KAef
              %2:orders » %1:lineitem[#0{l_orderkey}]KAf » %0:supplier[#0{s_suppkey}]KA » %3:nation[#0{n_nationkey}]KAef
              %3:nation » %0:supplier[#3{s_nationkey}]KA » %1:lineitem[#2{l_suppkey}]KAf » %2:orders[#0{o_orderkey}]KAef
            ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 }
              ReadIndex on=supplier pk_supplier_suppkey=[delta join 1st input (full scan)] fk_supplier_nationkey=[delta join lookup] // { arity: 7 }
            ArrangeBy keys=[[#0{l_orderkey}], [#2{l_suppkey}]] // { arity: 16 }
              ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] fk_lineitem_suppkey=[delta join lookup] // { arity: 16 }
            ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 9 }
              ReadIndex on=orders pk_orders_orderkey=[delta join lookup] // { arity: 9 }
            ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 }
              ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 }
    cte l1 =
      ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 16 }
        ReadIndex on=lineitem fk_lineitem_orderkey=[differential join] // { arity: 16 }
    cte l2 =
      Project (#0{s_suppkey}..=#2{l_orderkey}) // { arity: 3 }
        Join on=(#0{s_suppkey} = #4{s_suppkey} AND #2{l_orderkey} = #3{l_orderkey}) type=differential // { arity: 5 }
          implementation
            %1[#1, #0]UKK » %0:l0[#0, #2]KK
          ArrangeBy keys=[[#0{s_suppkey}, #2{l_orderkey}]] // { arity: 3 }
            Get l0 // { arity: 3 }
          ArrangeBy keys=[[#1{s_suppkey}, #0{l_orderkey}]] // { arity: 2 }
            Distinct project=[#0{l_orderkey}, #1{s_suppkey}] // { arity: 2 }
              Project (#0{l_orderkey}, #1{s_suppkey}) // { arity: 2 }
                Filter (#1{s_suppkey} != #4{l_suppkey}) // { arity: 18 }
                  Join on=(#0{l_orderkey} = #2{l_orderkey}) type=differential // { arity: 18 }
                    implementation
                      %1:l1[#0{l_orderkey}]KA » %0[#0{l_orderkey}]K
                    ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 2 }
                      Distinct project=[#1{l_orderkey}, #0{s_suppkey}] // { arity: 2 }
                        Project (#0{s_suppkey}, #2{l_orderkey}) // { arity: 2 }
                          Get l0 // { arity: 3 }
                    Get l1 // { arity: 16 }
    cte l3 =
      Distinct project=[#1{l_orderkey}, #0{s_suppkey}] // { arity: 2 }
        Project (#0{s_suppkey}, #2{l_orderkey}) // { arity: 2 }
          Get l2 // { arity: 3 }
  Return // { arity: 2 }
    Reduce group_by=[#0{s_name}] aggregates=[count(*)] // { arity: 2 }
      Project (#1{s_name}) // { arity: 1 }
        Join on=(#0{s_suppkey} = #4{s_suppkey} AND #2{l_orderkey} = #3{l_orderkey}) type=differential // { arity: 5 }
          implementation
            %0:l2[#2, #0]KK » %1[#0, #1]KK
          ArrangeBy keys=[[#2{l_orderkey}, #0{s_suppkey}]] // { arity: 3 }
            Get l2 // { arity: 3 }
          ArrangeBy keys=[[#0{l_orderkey}, #1{s_suppkey}]] // { arity: 2 }
            Union // { arity: 2 }
              Negate // { arity: 2 }
                Distinct project=[#0{l_orderkey}, #1{s_suppkey}] // { arity: 2 }
                  Project (#0{l_orderkey}, #1{s_suppkey}) // { arity: 2 }
                    Filter (#1{s_suppkey} != #4{l_suppkey}) AND (#14{l_receiptdate} > #13{l_commitdate}) // { arity: 18 }
                      Join on=(#0{l_orderkey} = #2{l_orderkey}) type=differential // { arity: 18 }
                        implementation
                          %1:l1[#0{l_orderkey}]KAf » %0:l3[#0{l_orderkey}]Kf
                        ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 2 }
                          Get l3 // { arity: 2 }
                        Get l1 // { arity: 16 }
              Get l3 // { arity: 2 }

Used Indexes:
  - materialize.public.pk_nation_nationkey (delta join lookup)
  - materialize.public.pk_supplier_suppkey (delta join 1st input (full scan))
  - materialize.public.fk_supplier_nationkey (delta join lookup)
  - materialize.public.pk_orders_orderkey (delta join lookup)
  - materialize.public.fk_lineitem_orderkey (differential join, delta join lookup)
  - materialize.public.fk_lineitem_suppkey (delta join lookup)

Target cluster: quickstart

EOF


statement ok
-- Query 22
CREATE VIEW Q22 AS
SELECT
    cntrycode,
    count(*) AS numcust,
    sum(c_acctbal) AS totacctbal
FROM
    (
        SELECT
            substring(c_phone, 1, 2) AS cntrycode, c_acctbal
        FROM
            customer
        WHERE
            substring(c_phone, 1, 2)
            IN ('13', '31', '23', '29', '30', '18', '17')
            AND c_acctbal
                > (
                        SELECT
                            avg(c_acctbal)
                        FROM
                            customer
                        WHERE
                            c_acctbal > 0.00
                            AND substring(c_phone, 1, 2)
                                IN (
                                        '13',
                                        '31',
                                        '23',
                                        '29',
                                        '30',
                                        '18',
                                        '17'
                                    )
                    )
            AND NOT
                    EXISTS(
                        SELECT
                            *
                        FROM
                            orders
                        WHERE
                            o_custkey = c_custkey
                    )
    )
        AS custsale
GROUP BY
    cntrycode
ORDER BY
    cntrycode;


query T multiline
-- Explain index on Q22
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
CREATE DEFAULT INDEX ON Q22;
----
materialize.public.q22_primary_idx:
  ArrangeBy keys=[[#0{cntrycode}]] // { arity: 3 }
    ReadGlobalFromSameDataflow materialize.public.q22 // { arity: 3 }

materialize.public.q22:
  With
    cte l0 =
      Project (#0{c_custkey}, #4{c_phone}, #5{c_acctbal}, #8) // { arity: 4 }
        Map (substr(char_to_text(#4{c_phone}), 1, 2)) // { arity: 9 }
          ReadIndex on=customer pk_customer_custkey=[*** full scan ***] // { arity: 8 }
    cte l1 =
      Project (#0{c_custkey}..=#2{c_acctbal}) // { arity: 3 }
        Filter (#2{c_acctbal} > (#3{sum_c_acctbal} / bigint_to_numeric(case when (#4{count} = 0) then null else #4{count} end))) // { arity: 5 }
          CrossJoin type=differential // { arity: 5 }
            implementation
              %1[×]UA » %0:l0[×]ef
            ArrangeBy keys=[[]] // { arity: 3 }
              Project (#0{c_custkey}..=#2{c_acctbal}) // { arity: 3 }
                Filter ((#3 = "13") OR (#3 = "17") OR (#3 = "18") OR (#3 = "23") OR (#3 = "29") OR (#3 = "30") OR (#3 = "31")) // { arity: 4 }
                  Get l0 // { arity: 4 }
            ArrangeBy keys=[[]] // { arity: 2 }
              Reduce aggregates=[sum(#0{c_acctbal}), count(*)] // { arity: 2 }
                Project (#2{c_acctbal}) // { arity: 1 }
                  Filter (#2{c_acctbal} > 0) AND ((#3 = "13") OR (#3 = "17") OR (#3 = "18") OR (#3 = "23") OR (#3 = "29") OR (#3 = "30") OR (#3 = "31")) // { arity: 4 }
                    Get l0 // { arity: 4 }
    cte l2 =
      Distinct project=[#0{c_custkey}] // { arity: 1 }
        Project (#0{c_custkey}) // { arity: 1 }
          Get l1 // { arity: 3 }
  Return // { arity: 3 }
    Reduce group_by=[substr(char_to_text(#0{c_phone}), 1, 2)] aggregates=[count(*), sum(#1{c_acctbal})] // { arity: 3 }
      Project (#1{c_phone}, #2{c_acctbal}) // { arity: 2 }
        Join on=(#0{c_custkey} = #3{c_custkey}) type=differential // { arity: 4 }
          implementation
            %0:l1[#0]K » %1[#0]K
          ArrangeBy keys=[[#0{c_custkey}]] // { arity: 3 }
            Get l1 // { arity: 3 }
          ArrangeBy keys=[[#0{c_custkey}]] // { arity: 1 }
            Union // { arity: 1 }
              Negate // { arity: 1 }
                Project (#0{c_custkey}) // { arity: 1 }
                  Join on=(#0{c_custkey} = #1{o_custkey}) type=differential // { arity: 2 }
                    implementation
                      %0:l2[#0]UKA » %1[#0]UKA
                    ArrangeBy keys=[[#0{c_custkey}]] // { arity: 1 }
                      Get l2 // { arity: 1 }
                    ArrangeBy keys=[[#0{o_custkey}]] // { arity: 1 }
                      Distinct project=[#0{o_custkey}] // { arity: 1 }
                        Project (#1{o_custkey}) // { arity: 1 }
                          ReadIndex on=orders pk_orders_orderkey=[*** full scan ***] // { arity: 9 }
              Get l2 // { arity: 1 }

Used Indexes:
  - materialize.public.pk_customer_custkey (*** full scan ***)
  - materialize.public.pk_orders_orderkey (*** full scan ***)

Target cluster: quickstart

EOF