Optimization: remove excess join

1 files changed, 11 insertions, 12 deletions

diff --git a/ydb/library/benchmarks/queries/tpch/yql/q21.sql b/ydb/library/benchmarks/queries/tpch/yql/q21.sql
index 93c89311540..5056e6203f9 100644
--- a/ydb/library/benchmarks/queries/tpch/yql/q21.sql
+++ b/ydb/library/benchmarks/queries/tpch/yql/q21.sql
@@ -18,24 +18,23 @@ $j1 = select s_name, l_suppkey, l_orderkey from $l as l1
 join $s as supplier
 on l1.l_suppkey = supplier.s_suppkey;
 
-$j1_1 = select l1.l_orderkey as l_orderkey from $j1 as l1
-join $l as l3
-on l1.l_orderkey = l3.l_orderkey
-where l3.l_suppkey <> l1.l_suppkey;
-
-$j2 = select s_name, l_suppkey, l_orderkey from $j1 as l1
-left only join $j1_1 as l3
-on l1.l_orderkey = l3.l_orderkey;
-
-$j2_1 = select l1.l_orderkey as l_orderkey from $j2 as l1
+-- exists
+$j2 = select l1.l_orderkey as l_orderkey, l1.l_suppkey as l_suppkey, l1.s_name as s_name, l2.l_receiptdate as l_receiptdate, l2.l_commitdate as l_commitdate from $j1 as l1
 join {{lineitem}} as l2
 on l1.l_orderkey = l2.l_orderkey
 where l2.l_suppkey <> l1.l_suppkey;
 
-$j3 = select s_name, l1.l_suppkey as l_suppkey, l1.l_orderkey as l_orderkey from $j2 as l1
-left semi join $j2_1 as l2
+$j2_1 = select s_name, l1.l_suppkey as l_suppkey, l1.l_orderkey as l_orderkey from $j1 as l1
+left semi join $j2 as l2
 on l1.l_orderkey = l2.l_orderkey;
 
+-- not exists
+$j2_2 = select l_orderkey from $j2 where l_receiptdate > l_commitdate;
+
+$j3 = select s_name, l_suppkey, l_orderkey from $j2_1 as l1
+left only join $j2_2 as l3
+on l1.l_orderkey = l3.l_orderkey;
+
 $j4 = select s_name from $j3 as l1
 join {{orders}} as orders
 on orders.o_orderkey = l1.l_orderkey