rust
/
Materialize


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298
							# 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.

# Regression test for https://github.com/MaterializeInc/database-issues/issues/2746
mode cockroach

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

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 text NOT NULL, l_linestatus text NOT NULL, l_shipdate date NOT NULL, l_commitdate date NOT NULL, l_receiptdate date NOT NULL, l_shipinstruct text NOT NULL, l_shipmode text NOT NULL, l_comment text NOT NULL
);

statement ok
INSERT INTO "lineitem" VALUES (581,7,1,1,41,37187,0.09,0.07,'N','O','1997-05-26','1997-04-06','1997-06-10','TAKE BACK RETURN','MAIL','even, bold excuses about the'),(581,10,1,2,14,12740.14,0.06,0.08,'N','O','1997-05-17','1997-04-14','1997-06-08','NONE','MAIL','furiously silent depths'),(581,11,1,3,49,44639.49,0.1,0.02,'N','O','1997-02-27','1997-04-24','1997-03-10','TAKE BACK RETURN','MAIL','furiously regular theodolites wake qu'),(581,8,1,4,30,27240,0.1,0.08,'N','O','1997-06-19','1997-05-21','1997-06-22','TAKE BACK RETURN','TRUCK','even deposits wa'),(582,6,1,1,7,6342,0.07,0,'N','O','1997-11-16','1997-11-29','1997-12-10','TAKE BACK RETURN','FOB','carefully final requests sleep slyly. even,'),(582,6,1,2,49,44394,0.05,0.03,'N','O','1997-12-17','1998-01-12','1997-12-31','COLLECT COD','REG AIR','carefully regular'),(582,15,1,3,42,38430.42,0.07,0,'N','O','1997-11-15','1997-12-21','1997-12-03','COLLECT COD','SHIP','pending, spe'),(582,17,1,4,36,33012.36,0.06,0.01,'N','O','1997-12-09','1997-11-27','1997-12-26','TAKE BACK RETURN','SHIP','slyly final foxes nag permanen');

statement ok
INSERT INTO "orders" VALUES (547,10,'O',87538.95,'1996-06-22','3-MEDIUM','Clerk#000000976',0,'bold instructions print fluffily carefully id'),(548,13,'F',91796.96,'1994-09-21','1-URGENT','Clerk#000000435',0,'quickly regular accounts daz'),(549,11,'F',129199.61,'1992-07-13','1-URGENT','Clerk#000000196',0,'carefully regular foxes integrate ironic, fina'),(550,4,'O',27927.38,'1995-08-02','1-URGENT','Clerk#000000204',0,'carefully even asymptotes sleep furiously sp'),(551,10,'O',40845.41,'1995-05-30','1-URGENT','Clerk#000000179',0,'unusual, final accounts use above the special excuses. final depo'),(576,4,'O',17143.74,'1997-05-13','3-MEDIUM','Clerk#000000955',0,'pending theodolites about the carefu'),(577,7,'F',33465.32,'1994-12-19','5-LOW','Clerk#000000154',0,'blithely unusual packages sl'),(578,10,'O',60466.97,'1997-01-10','5-LOW','Clerk#000000281',0,'blithely pending asymptotes wake quickly across the carefully final'),(579,7,'O',116780.04,'1998-03-11','2-HIGH','Clerk#000000862',0,'slyly even requests cajole slyly. sil'),(580,7,'O',77490.74,'1997-07-05','2-HIGH','Clerk#000000314',0,'final ideas must have to are carefully quickly furious requests'),(581,7,'O',116599.09,'1997-02-23','4-NOT SPECIFIED','Clerk#000000239',0,'carefully regular dolphins cajole ruthlessl'),(582,5,'O',116419.79,'1997-10-21','1-URGENT','Clerk#000000378',0,'quietly ironic pinto beans wake carefully. ironic accounts across the dol');

query I
SELECT
 1
FROM
 lineitem JOIN orders ON true
WHERE
 l_orderkey
 = ANY (
 SELECT
 o_orderkey
 FROM
 lineitem RIGHT JOIN orders ON true
 WHERE
 l_receiptdate IN ('1997-07-06')
 OR l_commitdate = o_orderdate - ' 7 DAY '
 );
----


query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT
 1
FROM
 lineitem JOIN orders ON true
WHERE
 l_orderkey
 = ANY (
 SELECT
 o_orderkey
 FROM
 lineitem RIGHT JOIN orders ON true
 WHERE
 l_receiptdate IN ('1997-07-06')
 OR l_commitdate = o_orderdate - ' 7 DAY '
 );
----
Explained Query:
  With
    cte l0 =
      Join::Linear
        linear_stage[0]
          lookup={ relation=1, key=[] }
          stream={ key=[], thinning=(#0) }
        source={ relation=0, key=[] }
        ArrangeBy
          raw=true
          arrangements[0]={ key=[], permutation=id, thinning=(#0) }
          types=[integer]
          Get::Collection materialize.public.lineitem
            project=(#0)
            raw=true
        ArrangeBy
          raw=true
          arrangements[0]={ key=[], permutation=id, thinning=() }
          Get::Collection materialize.public.orders
            project=()
            raw=true
  Return
    Join::Linear
      final_closure
        project=(#0)
        map=(1)
      linear_stage[0]
        closure
          project=()
        lookup={ relation=0, key=[#0] }
        stream={ key=[#0], thinning=() }
      source={ relation=1, key=[#0] }
      ArrangeBy
        raw=true
        arrangements[0]={ key=[#0], permutation=id, thinning=() }
        types=[integer]
        Get::PassArrangements l0
          raw=true
      Reduce::Distinct
        val_plan
          project=()
        key_plan=id
        Join::Delta
          plan_path[0]
            delta_stage[1]
              closure
                project=(#0)
                filter=(((#2{l_receiptdate} = 1997-07-06) OR (date_to_timestamp(#1{l_commitdate}) = (#3{o_orderdate} - 7 days))))
              lookup={ relation=2, key=[#0] }
              stream={ key=[#2], thinning=(#0, #1) }
            delta_stage[0]
              lookup={ relation=1, key=[] }
              stream={ key=[], thinning=(#0, #1) }
            source={ relation=0, key=[] }
          plan_path[1]
            delta_stage[1]
              closure
                project=(#0)
                filter=(((#3{l_receiptdate} = 1997-07-06) OR (date_to_timestamp(#2{l_commitdate}) = (#1{o_orderdate} - 7 days))))
              lookup={ relation=0, key=[] }
              stream={ key=[], thinning=(#0, #1) }
            delta_stage[0]
              lookup={ relation=2, key=[#0] }
              stream={ key=[#0], thinning=() }
            source={ relation=1, key=[] }
          plan_path[2]
            delta_stage[1]
              closure
                project=(#0)
                filter=(((#3{l_receiptdate} = 1997-07-06) OR (date_to_timestamp(#2{l_commitdate}) = (#1{o_orderdate} - 7 days))))
              lookup={ relation=0, key=[] }
              stream={ key=[], thinning=(#0, #1) }
            delta_stage[0]
              lookup={ relation=1, key=[#0] }
              stream={ key=[#0], thinning=(#1) }
            source={ relation=2, key=[#0] }
          ArrangeBy
            raw=true
            arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) }
            types=[date, date]
            Get::Collection materialize.public.lineitem
              project=(#1, #2)
              raw=true
          ArrangeBy
            input_key=[#0]
            raw=false
            arrangements[0]={ key=[], permutation=id, thinning=(#0) }
            types=[integer]
            Reduce::Distinct
              val_plan
                project=()
              key_plan=id
              Get::PassArrangements l0
                raw=true
          ArrangeBy
            raw=true
            arrangements[0]={ key=[#0], permutation=id, thinning=(#1) }
            types=[integer, date]
            Get::Collection materialize.public.orders
              filter=((#0) IS NOT NULL)
              raw=true

Source materialize.public.orders
  project=(#0, #4)
Source materialize.public.lineitem
  project=(#0, #11, #12)

Target cluster: quickstart

EOF

query I
SELECT
	CASE o_orderkey
	WHEN 1 THEN l_orderkey
	ELSE (
		SELECT
			l_orderkey
		WHERE
			l_shipdate = o_orderdate + ' 3 MONTH '
			OR o_orderdate = l_shipdate - ' 9 DAY '
				AND EXISTS(
						SELECT
							1
						WHERE
							true
							AND l_orderkey
								= ANY (
										SELECT
											o_orderkey
										FROM
											lineitem RIGHT JOIN orders ON true
										WHERE
											l_receiptdate IN ('1997-07-06') OR l_commitdate = o_orderdate - ' 7 DAY '
									)
					)
	)
	END
FROM
	lineitem JOIN orders ON true;
----
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL