# 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