Reorder Q11 like CBO (#3592)

2 files changed, 38 insertions, 40 deletions

diff --git a/ydb/library/benchmarks/queries/tpch/ydb/q11.sql b/ydb/library/benchmarks/queries/tpch/ydb/q11.sql
index 85d88789d64..3e8c4195c4f 100644
--- a/ydb/library/benchmarks/queries/tpch/ydb/q11.sql
+++ b/ydb/library/benchmarks/queries/tpch/ydb/q11.sql
@@ -2,46 +2,45 @@
 -- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
 -- using 1680793381 as a seed to the RNG
 
-$join1 = (
+$j1 = (
 select
-    ps.ps_partkey as ps_partkey,
-    ps.ps_supplycost as ps_supplycost,
-    ps.ps_availqty as ps_availqty,
-    s.s_nationkey as s_nationkey
+    s.s_suppkey as s_suppkey
 from
-    `{path}partsupp` as ps
-join
     `{path}supplier` as s
+join
+    `{path}nation` as n
 on
-    ps.ps_suppkey = s.s_suppkey
+    n.n_nationkey = s.s_nationkey
+where
+    n.n_name = 'CANADA'
 );
-$join2 = (
+
+$j2 = (
 select
-    j.ps_partkey as ps_partkey,
-    j.ps_supplycost as ps_supplycost,
-    j.ps_availqty as ps_availqty,
-    j.s_nationkey as s_nationkey
+    ps.ps_partkey as ps_partkey,
+    ps.ps_supplycost as ps_supplycost,
+    ps.ps_availqty as ps_availqty
 from
-    $join1 as j
+    `{path}partsupp` as ps
 join
-    `{path}nation` as n
+    $j1 as j
 on
-    n.n_nationkey = j.s_nationkey
-where
-    n.n_name = 'CANADA'
+    ps.ps_suppkey = j.s_suppkey
 );
+
 $threshold = (
 select
     sum(ps_supplycost * ps_availqty) * 0.0001000000 as threshold
 from
-    $join2
+    $j2
 );
+
 $values = (
 select
     ps_partkey,
     sum(ps_supplycost * ps_availqty) as value
 from
-    $join2
+    $j2
 group by
     ps_partkey
 );
diff --git a/ydb/library/benchmarks/queries/tpch/yql/q11.sql b/ydb/library/benchmarks/queries/tpch/yql/q11.sql
index 561df5a6bf9..b0e209fdd86 100644
--- a/ydb/library/benchmarks/queries/tpch/yql/q11.sql
+++ b/ydb/library/benchmarks/queries/tpch/yql/q11.sql
@@ -4,46 +4,45 @@
 -- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
 -- using 1680793381 as a seed to the RNG
 
-$join1 = (
+$j1 = (
 select
-    ps.ps_partkey as ps_partkey,
-    ps.ps_supplycost as ps_supplycost,
-    ps.ps_availqty as ps_availqty,
-    s.s_nationkey as s_nationkey
+    s.s_suppkey as s_suppkey
 from
-    {{partsupp}} as ps
-join
     {{supplier}} as s
+join
+    {{nation}} as n
 on
-    ps.ps_suppkey = s.s_suppkey
+    n.n_nationkey = s.s_nationkey
+where
+    n.n_name = 'CANADA'
 );
-$join2 = (
+
+$j2 = (
 select
-    j.ps_partkey as ps_partkey,
-    j.ps_supplycost as ps_supplycost,
-    j.ps_availqty as ps_availqty,
-    j.s_nationkey as s_nationkey
+    ps.ps_partkey as ps_partkey,
+    ps.ps_supplycost as ps_supplycost,
+    ps.ps_availqty as ps_availqty
 from
-    $join1 as j
+    {{partsupp}} as ps
 join
-    {{nation}} as n
+    $j1 as j
 on
-    n.n_nationkey = j.s_nationkey
-where
-    n.n_name = 'CANADA'
+    ps.ps_suppkey = j.s_suppkey
 );
+
 $threshold = (
 select
     sum(ps_supplycost * ps_availqty) * 0.0001000000 as threshold
 from
-    $join2
+    $j2
 );
+
 $values = (
 select
     ps_partkey,
     sum(ps_supplycost * ps_availqty) as value
 from
-    $join2
+    $j2
 group by
     ps_partkey
 );