Canonical request for tpc-h pg syntax (#11018)

22 files changed, 51 insertions, 51 deletions

diff --git a/ydb/library/benchmarks/queries/tpch/pg/q1.sql b/ydb/library/benchmarks/queries/tpch/pg/q1.sql
index ada86e3dc6..9281eabcd4 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q1.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q1.sql
@@ -18,7 +18,7 @@ select
 from
     {{lineitem}}
 where
-    l_shipdate <= date '1998-12-01' - interval '100' day
+    l_shipdate <= date '1998-12-01' - interval '90' day
 group by
     l_returnflag,
     l_linestatus
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q10.sql b/ydb/library/benchmarks/queries/tpch/pg/q10.sql
index a6472b155a..119cf79e32 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q10.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q10.sql
@@ -21,8 +21,8 @@ from
 where
     c_custkey = o_custkey
     and l_orderkey = o_orderkey
-    and o_orderdate >= date '1993-12-01'
-    and o_orderdate < date '1993-12-01' + interval '3' month
+    and o_orderdate >= date '1993-10-01'
+    and o_orderdate < date '1993-10-01' + interval '3' month
     and l_returnflag = 'R'
     and c_nationkey = n_nationkey
 group by
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q11.sql b/ydb/library/benchmarks/queries/tpch/pg/q11.sql
index 25767a88b8..f3e97dff5a 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q11.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q11.sql
@@ -14,7 +14,7 @@ from
 where
     ps_suppkey = s_suppkey
     and s_nationkey = n_nationkey
-    and n_name = 'CANADA'
+    and n_name = 'GERMANY'
 group by
     ps_partkey having
         sum(ps_supplycost * ps_availqty) > (
@@ -27,7 +27,7 @@ group by
             where
                 ps_suppkey = s_suppkey
                 and s_nationkey = n_nationkey
-                and n_name = 'CANADA'
+                and n_name = 'GERMANY'
         )
 order by
     value desc;
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q12.sql b/ydb/library/benchmarks/queries/tpch/pg/q12.sql
index b4c1deb5a8..4c251d291c 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q12.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q12.sql
@@ -23,7 +23,7 @@ from
     {{lineitem}}
 where
     o_orderkey = l_orderkey
-    and l_shipmode in ('MAIL', 'TRUCK')
+    and l_shipmode in ('MAIL', 'SHIP')
     and l_commitdate < l_receiptdate
     and l_shipdate < l_commitdate
     and l_receiptdate >= date '1994-01-01'
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q13.sql b/ydb/library/benchmarks/queries/tpch/pg/q13.sql
index 88d031926b..bb1dd8ad25 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q13.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q13.sql
@@ -15,7 +15,7 @@ from
         from
             {{customer}} left outer join {{orders}} on
                 c_custkey = o_custkey
-                and o_comment not like '%unusual%requests%'
+                and o_comment not like '%special%requests%'
         group by
             c_custkey
     ) as c_orders (c_custkey, c_count)
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q14.sql b/ydb/library/benchmarks/queries/tpch/pg/q14.sql
index d1da7dc9af..13f0217f74 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q14.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q14.sql
@@ -15,7 +15,7 @@ from
     {{part}}
 where
     l_partkey = p_partkey
-    and l_shipdate >= date '1994-08-01'
-    and l_shipdate < date '1994-08-01' + interval '1' month;
+    and l_shipdate >= date '1995-09-01'
+    and l_shipdate < date '1995-09-01' + interval '1' month;
 
 
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q15.sql b/ydb/library/benchmarks/queries/tpch/pg/q15.sql
index a3991cfe6d..3b78d4dcde 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q15.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q15.sql
@@ -11,8 +11,8 @@ create view revenue0 (supplier_no, total_revenue) as
     from
         {{lineitem}}
     where
-        l_shipdate >= date '1997-03-01'
-        and l_shipdate < date '1997-03-01' + interval '3' month
+        l_shipdate >= date '1996-01-01'
+        and l_shipdate < date '1995-01-01' + interval '3' month
     group by
         l_suppkey;
 
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q16.sql b/ydb/library/benchmarks/queries/tpch/pg/q16.sql
index b659cf19cd..2cfc036996 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q16.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q16.sql
@@ -14,9 +14,9 @@ from
     {{part}}
 where
     p_partkey = ps_partkey
-    and p_brand <> 'Brand#33'
-    and p_type not like 'PROMO POLISHED%'
-    and p_size in (20, 27, 11, 45, 40, 41, 34, 36)
+    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
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q17.sql b/ydb/library/benchmarks/queries/tpch/pg/q17.sql
index 3b168169a5..52055c0680 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q17.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q17.sql
@@ -11,8 +11,8 @@ from
     {{part}}
 where
     p_partkey = l_partkey
-    and p_brand = 'Brand#35'
-    and p_container = 'LG DRUM'
+    and p_brand = 'Brand#23'
+    and p_container = 'MED BOX'
     and l_quantity < (
         select
             0.2 * avg(l_quantity)
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q18.sql b/ydb/library/benchmarks/queries/tpch/pg/q18.sql
index 310904ab72..7f9a26c90f 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q18.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q18.sql
@@ -23,7 +23,7 @@ where
             {{lineitem}}
         group by
             l_orderkey having
-                sum(l_quantity) > 315
+                sum(l_quantity) > 300
     )
     and c_custkey = o_custkey
     and o_orderkey = l_orderkey
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q19.sql b/ydb/library/benchmarks/queries/tpch/pg/q19.sql
index 78c15c0cde..457be84ce0 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q19.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q19.sql
@@ -12,9 +12,9 @@ from
 where
     (
         p_partkey = l_partkey
-        and p_brand = 'Brand#23'
+        and p_brand = 'Brand#12'
         and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
-        and l_quantity >= 7 and l_quantity <= 7 + 10
+        and l_quantity >= 1 and l_quantity <= 1 + 10
         and p_size between 1 and 5
         and l_shipmode in ('AIR', 'AIR REG')
         and l_shipinstruct = 'DELIVER IN PERSON'
@@ -22,9 +22,9 @@ where
     or
     (
         p_partkey = l_partkey
-        and p_brand = 'Brand#15'
+        and p_brand = 'Brand#23'
         and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
-        and l_quantity >= 17 and l_quantity <= 17 + 10
+        and l_quantity >= 10 and l_quantity <= 10 + 10
         and p_size between 1 and 10
         and l_shipmode in ('AIR', 'AIR REG')
         and l_shipinstruct = 'DELIVER IN PERSON'
@@ -32,9 +32,9 @@ where
     or
     (
         p_partkey = l_partkey
-        and p_brand = 'Brand#44'
+        and p_brand = 'Brand#34'
         and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
-        and l_quantity >= 25 and l_quantity <= 25 + 10
+        and l_quantity >= 20 and l_quantity <= 20 + 10
         and p_size between 1 and 15
         and l_shipmode in ('AIR', 'AIR REG')
         and l_shipinstruct = 'DELIVER IN PERSON'
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q2.sql b/ydb/library/benchmarks/queries/tpch/pg/q2.sql
index 741f7d13da..0c836cec0d 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q2.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q2.sql
@@ -23,10 +23,10 @@ where
     p_partkey = ps_partkey
     and s_suppkey = ps_suppkey
     and p_size = 10
-    and p_type like '%COPPER'
+    and p_type like '%BRASS'
     and s_nationkey = n_nationkey
     and n_regionkey = r_regionkey
-    and r_name = 'AMERICA'
+    and r_name = 'EUROPE'
     and ps_supplycost = (
         select
             min(ps_supplycost)
@@ -40,7 +40,7 @@ where
             and s_suppkey = ps_suppkey
             and s_nationkey = n_nationkey
             and n_regionkey = r_regionkey
-            and r_name = 'AMERICA'
+            and r_name = 'EUROPE'
     )
 order by
     s_acctbal desc,
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q20.sql b/ydb/library/benchmarks/queries/tpch/pg/q20.sql
index 1c7bc08731..f3da209cbf 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q20.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q20.sql
@@ -23,7 +23,7 @@ where
                 from
                     {{part}}
                 where
-                    p_name like 'maroon%'
+                    p_name like 'forest%'
             )
             and ps_availqty > (
                 select
@@ -33,12 +33,12 @@ where
                 where
                     l_partkey = ps_partkey
                     and l_suppkey = ps_suppkey
-                    and l_shipdate >= date '1993-01-01'
-                    and l_shipdate < date '1993-01-01' + interval '1' year
+                    and l_shipdate >= date '1994-01-01'
+                    and l_shipdate < date '1994-01-01' + interval '1' year
             )
     )
     and s_nationkey = n_nationkey
-    and n_name = 'VIETNAM'
+    and n_name = 'CANADA'
 order by
     s_name;
 
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q21.sql b/ydb/library/benchmarks/queries/tpch/pg/q21.sql
index 6db84602e2..b07be7ffc0 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q21.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q21.sql
@@ -37,7 +37,7 @@ where
             and l3.l_receiptdate > l3.l_commitdate
     )
     and s_nationkey = n_nationkey
-    and n_name = 'EGYPT'
+    and n_name = 'SAUDI ARABIA'
 group by
     s_name
 order by
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q22.sql b/ydb/library/benchmarks/queries/tpch/pg/q22.sql
index 8c652022eb..9d45bacd7b 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q22.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q22.sql
@@ -17,7 +17,7 @@ from
             {{customer}}
         where
             substring(c_phone from 1 for 2) in
-                ('31', '29', '30', '26', '28', '25', '15')
+                ('13', '31', '23', '29', '30', '28', '17')
             and c_acctbal > (
                 select
                     avg(c_acctbal)
@@ -26,7 +26,7 @@ from
                 where
                     c_acctbal > 0.00
                     and substring(c_phone from 1 for 2) in
-                        ('31', '29', '30', '26', '28', '25', '15')
+                        ('13', '31', '23', '29', '30', '28', '17')
             )
             and not exists (
                 select
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q3.sql b/ydb/library/benchmarks/queries/tpch/pg/q3.sql
index c3b2de378e..d30016631b 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q3.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q3.sql
@@ -15,11 +15,11 @@ from
     {{orders}},
     {{lineitem}}
 where
-    c_mktsegment = 'MACHINERY'
+    c_mktsegment = 'BUILDING'
     and c_custkey = o_custkey
     and l_orderkey = o_orderkey
-    and o_orderdate < date '1995-03-08'
-    and l_shipdate > date '1995-03-08'
+    and o_orderdate < date '1995-03-15'
+    and l_shipdate > date '1995-03-15'
 group by
     l_orderkey,
     o_orderdate,
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q4.sql b/ydb/library/benchmarks/queries/tpch/pg/q4.sql
index f41369e693..418c26bba0 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q4.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q4.sql
@@ -10,8 +10,8 @@ select
 from
     {{orders}}
 where
-    o_orderdate >= date '1994-03-01'
-    and o_orderdate < date '1994-03-01' + interval '3' month
+    o_orderdate >= date '1993-07-01'
+    and o_orderdate < date '1993-07-01' + interval '3' month
     and exists (
         select
             *
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q5.sql b/ydb/library/benchmarks/queries/tpch/pg/q5.sql
index 62b7f1f7ad..8b736ccbf6 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q5.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q5.sql
@@ -21,9 +21,9 @@ where
     and c_nationkey = s_nationkey
     and s_nationkey = n_nationkey
     and n_regionkey = r_regionkey
-    and r_name = 'AFRICA'
-    and o_orderdate >= date '1995-01-01'
-    and o_orderdate < date '1995-01-01' + interval '1' year
+    and r_name = 'ASIA'
+    and o_orderdate >= date '1994-01-01'
+    and o_orderdate < date '1994-01-01' + interval '1' year
 group by
     n_name
 order by
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q6.sql b/ydb/library/benchmarks/queries/tpch/pg/q6.sql
index 2145938510..344019a6ef 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q6.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q6.sql
@@ -10,7 +10,7 @@ select
 from
     {{lineitem}}
 where
-    l_shipdate >= date '1995-01-01'
-    and l_shipdate < date '1995-01-01' + interval '1' year
-    and l_discount between 0.07 - 0.0100001 and 0.07 + 0.0100001
-    and l_quantity < 25;
+    l_shipdate >= date '1994-01-01'
+    and l_shipdate < date '1994-01-01' + interval '1' year
+    and l_discount between 0.01 - 0.0100001 and 0.01 + 0.0100001
+    and l_quantity < 24;
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q7.sql b/ydb/library/benchmarks/queries/tpch/pg/q7.sql
index e04fe94249..23425c57d9 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q7.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q7.sql
@@ -30,8 +30,8 @@ from
             and s_nationkey = n1.n_nationkey
             and c_nationkey = n2.n_nationkey
             and (
-                (n1.n_name = 'PERU' and n2.n_name = 'MOZAMBIQUE')
-                or (n1.n_name = 'MOZAMBIQUE' and n2.n_name = 'PERU')
+                (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
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q8.sql b/ydb/library/benchmarks/queries/tpch/pg/q8.sql
index bd8630ae87..9fe526eda4 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q8.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q8.sql
@@ -7,7 +7,7 @@
 select
     o_year,
     sum(case
-        when nation = 'MOZAMBIQUE' then volume
+        when nation = 'BRAZIL' then volume
         else 0
     end) / sum(volume) as mkt_share
 from
@@ -32,10 +32,10 @@ from
             and o_custkey = c_custkey
             and c_nationkey = n1.n_nationkey
             and n1.n_regionkey = r_regionkey
-            and r_name = 'AFRICA'
+            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 PLATED COPPER'
+            and p_type = 'ECONOMY ANODIZED STEEL'
     ) as all_nations
 group by
     o_year
diff --git a/ydb/library/benchmarks/queries/tpch/pg/q9.sql b/ydb/library/benchmarks/queries/tpch/pg/q9.sql
index 882c2093cf..7ec7d52371 100644
--- a/ydb/library/benchmarks/queries/tpch/pg/q9.sql
+++ b/ydb/library/benchmarks/queries/tpch/pg/q9.sql
@@ -28,7 +28,7 @@ from
             and p_partkey = l_partkey
             and o_orderkey = l_orderkey
             and s_nationkey = n_nationkey
-            and p_name like '%rose%'
+            and p_name like '%green%'
     ) as profit
 group by
     nation,