Merge branch 'rightlib' into mergelibs-241120-1113

39 files changed, 1686 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/tpch/customer.txt b/yql/essentials/tests/sql/suites/tpch/customer.txt
new file mode 100644
index 0000000000..51461f4d12
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/customer.txt
@@ -0,0 +1 @@
+{"c_custkey"=1;"c_acctbal"=711.56;"c_phone"="25-989-741-2988";"c_mktsegment"="BUILDING";"c_nationkey"=15;"c_comment"="to the even, regular platelets. regular, ironic epitaphs nag e";"c_name"="Customer#000000001";"c_address"="IVhzIApeRb ot,c,E";};
diff --git a/yql/essentials/tests/sql/suites/tpch/customer.txt.attr b/yql/essentials/tests/sql/suites/tpch/customer.txt.attr
new file mode 100644
index 0000000000..db4ca5853e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/customer.txt.attr
@@ -0,0 +1,56 @@
+{
+    "schema" = <
+        "strict" = %true;
+        "unique_keys" = %false;
+    > [
+        {
+            "name" = "c_custkey";
+            "required" = %true;
+            "sort_order" = "ascending";
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "c_acctbal";
+            "required" = %true;
+            "type" = "double";
+            "type_v3" = "double";
+        };
+        {
+            "name" = "c_phone";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "c_mktsegment";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "c_nationkey";
+            "required" = %true;
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "c_comment";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "c_name";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "c_address";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+    ];
+}
diff --git a/yql/essentials/tests/sql/suites/tpch/default.cfg b/yql/essentials/tests/sql/suites/tpch/default.cfg
new file mode 100644
index 0000000000..f3c9624348
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/default.cfg
@@ -0,0 +1,8 @@
+in customer customer.txt
+in lineitem lineitem.txt
+in nation nation.txt
+in orders orders.txt
+in part part.txt
+in partsupp partsupp.txt
+in region region.txt
+in supplier supplier.txt
diff --git a/yql/essentials/tests/sql/suites/tpch/lineitem.txt b/yql/essentials/tests/sql/suites/tpch/lineitem.txt
new file mode 100644
index 0000000000..dedb4b8f12
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/lineitem.txt
@@ -0,0 +1 @@
+{"l_orderkey"=1;"l_linenumber"=1;"l_discount"=0.04;"l_extendedprice"=21168.23;"l_comment"="egular courts above the";"l_shipdate"=9568u;"l_returnflag"="N";"l_partkey"=155190;"l_tax"=0.02;"l_shipinstruct"="DELIVER IN PERSON";"l_commitdate"=9538u;"l_receiptdate"=9577u;"l_quantity"=17.;"l_suppkey"=7706;"l_shipmode"="TRUCK";"l_linestatus"="O";};
diff --git a/yql/essentials/tests/sql/suites/tpch/lineitem.txt.attr b/yql/essentials/tests/sql/suites/tpch/lineitem.txt.attr
new file mode 100644
index 0000000000..7abd7775de
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/lineitem.txt.attr
@@ -0,0 +1,114 @@
+{
+    "schema" = <
+        "strict" = %true;
+        "unique_keys" = %false;
+    > [
+        {
+            "name" = "l_orderkey";
+            "required" = %true;
+            "sort_order" = "ascending";
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "l_linenumber";
+            "required" = %true;
+            "sort_order" = "ascending";
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "l_discount";
+            "required" = %true;
+            "type" = "double";
+            "type_v3" = "double";
+        };
+        {
+            "name" = "l_extendedprice";
+            "required" = %true;
+            "type" = "double";
+            "type_v3" = "double";
+        };
+        {
+            "name" = "l_comment";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "l_shipdate";
+            "required" = %false;
+            "type" = "date";
+            "type_v3" = {
+                "type_name" = "optional";
+                "item" = "date";
+            };
+        };
+        {
+            "name" = "l_returnflag";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "l_partkey";
+            "required" = %true;
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "l_tax";
+            "required" = %true;
+            "type" = "double";
+            "type_v3" = "double";
+        };
+        {
+            "name" = "l_shipinstruct";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "l_commitdate";
+            "required" = %false;
+            "type" = "date";
+            "type_v3" = {
+                "type_name" = "optional";
+                "item" = "date";
+            };
+        };
+        {
+            "name" = "l_receiptdate";
+            "required" = %false;
+            "type" = "date";
+            "type_v3" = {
+                "type_name" = "optional";
+                "item" = "date";
+            };
+        };
+        {
+            "name" = "l_quantity";
+            "required" = %true;
+            "type" = "double";
+            "type_v3" = "double";
+        };
+        {
+            "name" = "l_suppkey";
+            "required" = %true;
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "l_shipmode";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "l_linestatus";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+    ];
+}
diff --git a/yql/essentials/tests/sql/suites/tpch/nation.txt b/yql/essentials/tests/sql/suites/tpch/nation.txt
new file mode 100644
index 0000000000..69cbb20e37
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/nation.txt
@@ -0,0 +1 @@
+{"n_nationkey"=0;"n_regionkey"=0;"n_name"="ALGERIA";"n_comment"=" haggle. carefully final deposits detect slyly agai";};
diff --git a/yql/essentials/tests/sql/suites/tpch/nation.txt.attr b/yql/essentials/tests/sql/suites/tpch/nation.txt.attr
new file mode 100644
index 0000000000..1db996b861
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/nation.txt.attr
@@ -0,0 +1,32 @@
+{
+    "schema" = <
+        "strict" = %true;
+        "unique_keys" = %false;
+    > [
+        {
+            "name" = "n_nationkey";
+            "required" = %true;
+            "sort_order" = "ascending";
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "n_regionkey";
+            "required" = %true;
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "n_name";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "n_comment";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+    ];
+}
diff --git a/yql/essentials/tests/sql/suites/tpch/orders.txt b/yql/essentials/tests/sql/suites/tpch/orders.txt
new file mode 100644
index 0000000000..0fb3aa5340
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/orders.txt
@@ -0,0 +1 @@
+{"o_orderkey"=1;"o_orderstatus"="O";"o_comment"="nstructions sleep furiously among ";"o_totalprice"=173665.47;"o_clerk"="Clerk#000000951";"o_custkey"=36901;"o_orderpriority"="5-LOW";"o_orderdate"=9497u;"o_shippriority"=0;};
diff --git a/yql/essentials/tests/sql/suites/tpch/orders.txt.attr b/yql/essentials/tests/sql/suites/tpch/orders.txt.attr
new file mode 100644
index 0000000000..fb5e422f56
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/orders.txt.attr
@@ -0,0 +1,65 @@
+{
+    "schema" = <
+        "strict" = %true;
+        "unique_keys" = %false;
+    > [
+        {
+            "name" = "o_orderkey";
+            "required" = %true;
+            "sort_order" = "ascending";
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "o_orderstatus";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "o_comment";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "o_totalprice";
+            "required" = %true;
+            "type" = "double";
+            "type_v3" = "double";
+        };
+        {
+            "name" = "o_clerk";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "o_custkey";
+            "required" = %true;
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "o_orderpriority";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "o_orderdate";
+            "required" = %false;
+            "type" = "date";
+            "type_v3" = {
+                "type_name" = "optional";
+                "item" = "date";
+            };
+        };
+        {
+            "name" = "o_shippriority";
+            "required" = %true;
+            "type" = "int64";
+            "type_v3" = "int64";
+        };
+    ];
+}
diff --git a/yql/essentials/tests/sql/suites/tpch/part.txt b/yql/essentials/tests/sql/suites/tpch/part.txt
new file mode 100644
index 0000000000..62ebe18f41
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/part.txt
@@ -0,0 +1 @@
+{"p_partkey"=1;"p_name"="goldenrod lavender spring chocolate lace";"p_container"="JUMBO PKG";"p_size"=7;"p_type"="PROMO BURNISHED COPPER";"p_comment"="ly. slyly ironi";"p_retailprice"=901.;"p_mfgr"="Manufacturer#1";"p_brand"="Brand#13";};
diff --git a/yql/essentials/tests/sql/suites/tpch/part.txt.attr b/yql/essentials/tests/sql/suites/tpch/part.txt.attr
new file mode 100644
index 0000000000..973fedb9e4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/part.txt.attr
@@ -0,0 +1,62 @@
+{
+    "schema" = <
+        "strict" = %true;
+        "unique_keys" = %false;
+    > [
+        {
+            "name" = "p_partkey";
+            "required" = %true;
+            "sort_order" = "ascending";
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "p_name";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "p_container";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "p_size";
+            "required" = %true;
+            "type" = "int64";
+            "type_v3" = "int64";
+        };
+        {
+            "name" = "p_type";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "p_comment";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "p_retailprice";
+            "required" = %true;
+            "type" = "double";
+            "type_v3" = "double";
+        };
+        {
+            "name" = "p_mfgr";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "p_brand";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+    ];
+}
diff --git a/yql/essentials/tests/sql/suites/tpch/partsupp.txt b/yql/essentials/tests/sql/suites/tpch/partsupp.txt
new file mode 100644
index 0000000000..c06ac1b884
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/partsupp.txt
@@ -0,0 +1 @@
+{"ps_partkey"=1;"ps_suppkey"=2;"ps_comment"=", even theodolites. regular, final theodolites eat after the carefully pending foxes. furiously regular deposits sleep slyly. carefully bold realms above the ironic dependencies haggle careful";"ps_supplycost"=771.64;"ps_availqty"=3325;};
diff --git a/yql/essentials/tests/sql/suites/tpch/partsupp.txt.attr b/yql/essentials/tests/sql/suites/tpch/partsupp.txt.attr
new file mode 100644
index 0000000000..b941075b07
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/partsupp.txt.attr
@@ -0,0 +1,39 @@
+{
+    "schema" = <
+        "strict" = %true;
+        "unique_keys" = %false;
+    > [
+        {
+            "name" = "ps_partkey";
+            "required" = %true;
+            "sort_order" = "ascending";
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "ps_suppkey";
+            "required" = %true;
+            "sort_order" = "ascending";
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "ps_comment";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "ps_supplycost";
+            "required" = %true;
+            "type" = "double";
+            "type_v3" = "double";
+        };
+        {
+            "name" = "ps_availqty";
+            "required" = %true;
+            "type" = "int64";
+            "type_v3" = "int64";
+        };
+    ];
+}
diff --git a/yql/essentials/tests/sql/suites/tpch/q1.sql b/yql/essentials/tests/sql/suites/tpch/q1.sql
new file mode 100644
index 0000000000..3f8ccfe964
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q1.sql
@@ -0,0 +1,26 @@
+
+-- TPC-H/TPC-R Pricing Summary Report Query (Q1)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+select
+    l_returnflag,
+    l_linestatus,
+    sum(l_quantity) as sum_qty,
+    sum(l_extendedprice) as sum_base_price,
+    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
+    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
+    avg(l_quantity) as avg_qty,
+    avg(l_extendedprice) as avg_price,
+    avg(l_discount) as avg_disc,
+    count(*) as count_order
+from
+    plato.lineitem
+where
+    CAST(l_shipdate AS Timestamp) <= (CAST('1998-12-01' AS Date) - Interval("P100D"))
+group by
+    l_returnflag,
+    l_linestatus
+order by
+    l_returnflag,
+    l_linestatus;
diff --git a/yql/essentials/tests/sql/suites/tpch/q10.sql b/yql/essentials/tests/sql/suites/tpch/q10.sql
new file mode 100644
index 0000000000..1d3e4a75bb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q10.sql
@@ -0,0 +1,87 @@
+
+-- TPC-H/TPC-R Returned Item Reporting Query (Q10)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$border = Date("1993-12-01");
+$join1 = (
+select
+    c.c_custkey as c_custkey,
+    c.c_name as c_name,
+    c.c_acctbal as c_acctbal,
+    c.c_address as c_address,
+    c.c_phone as c_phone,
+    c.c_comment as c_comment,
+    c.c_nationkey as c_nationkey,
+    o.o_orderkey as o_orderkey
+from
+    plato.customer as c
+join
+    plato.orders as o
+on
+    c.c_custkey = o.o_custkey
+where
+    cast(o.o_orderdate as timestamp) >= $border and
+    cast(o.o_orderdate as timestamp) < ($border + Interval("P90D"))
+);
+$join2 = (
+select
+    j.c_custkey as c_custkey,
+    j.c_name as c_name,
+    j.c_acctbal as c_acctbal,
+    j.c_address as c_address,
+    j.c_phone as c_phone,
+    j.c_comment as c_comment,
+    j.c_nationkey as c_nationkey,
+    l.l_extendedprice as l_extendedprice,
+    l.l_discount as l_discount
+from
+    $join1 as j 
+join
+    plato.lineitem as l
+on
+    l.l_orderkey = j.o_orderkey
+where
+    l.l_returnflag = 'R'
+);
+$join3 = (
+select
+    j.c_custkey as c_custkey,
+    j.c_name as c_name,
+    j.c_acctbal as c_acctbal,
+    j.c_address as c_address,
+    j.c_phone as c_phone,
+    j.c_comment as c_comment,
+    j.c_nationkey as c_nationkey,
+    j.l_extendedprice as l_extendedprice,
+    j.l_discount as l_discount,
+    n.n_name as n_name
+from
+    $join2 as j 
+join
+    plato.nation as n
+on
+    n.n_nationkey = j.c_nationkey
+);
+select
+    c_custkey,
+    c_name,
+    sum(l_extendedprice * (1 - l_discount)) as revenue,
+    c_acctbal,
+    n_name,
+    c_address,
+    c_phone,
+    c_comment
+from
+    $join3
+group by
+    c_custkey,
+    c_name,
+    c_acctbal,
+    c_phone,
+    n_name,
+    c_address,
+    c_comment
+order by
+    revenue desc
+limit 20;
diff --git a/yql/essentials/tests/sql/suites/tpch/q11.sql b/yql/essentials/tests/sql/suites/tpch/q11.sql
new file mode 100644
index 0000000000..2a80fb6128
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q11.sql
@@ -0,0 +1,62 @@
+
+-- TPC-H/TPC-R Important Stock Identification Query (Q11)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+PRAGMA DisableSimpleColumns; 
+
+$join1 = (
+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
+from
+    plato.partsupp as ps
+join
+    plato.supplier as s
+on
+    ps.ps_suppkey = s.s_suppkey
+);
+$join2 = (
+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
+from
+    $join1 as j
+join
+    plato.nation as n
+on
+    n.n_nationkey = j.s_nationkey
+where
+    n.n_name = 'CANADA'
+);
+$threshold = (
+select
+    sum(ps_supplycost * ps_availqty) * 0.0001000000 as threshold
+from
+    $join2
+);
+$values = (
+select
+    ps_partkey,
+    sum(ps_supplycost * ps_availqty) as value
+from
+    $join2
+group by
+    ps_partkey
+);
+
+select
+    v.ps_partkey as ps_partkey,
+    v.value as value
+from
+    $values as v
+cross join
+    $threshold as t
+where
+    v.value > t.threshold
+order by
+    value desc;
diff --git a/yql/essentials/tests/sql/suites/tpch/q12.sql b/yql/essentials/tests/sql/suites/tpch/q12.sql
new file mode 100644
index 0000000000..d8425b0b4a
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q12.sql
@@ -0,0 +1,45 @@
+
+-- TPC-H/TPC-R Shipping Modes and Order Priority Query (Q12)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$join = (
+    select
+        l.l_shipmode as l_shipmode,
+        o.o_orderpriority as o_orderpriority,
+        l.l_commitdate as l_commitdate,
+        l.l_shipdate as l_shipdate,
+        l.l_receiptdate as l_receiptdate
+    from
+        plato.orders as o
+        join plato.lineitem as l
+        on o.o_orderkey == l.l_orderkey
+);
+
+$border = Date("1994-01-01");
+
+select
+    l_shipmode,
+    sum(case
+        when o_orderpriority = '1-URGENT'
+            or o_orderpriority = '2-HIGH'
+            then 1
+        else 0
+    end) as high_line_count,
+    sum(case
+        when o_orderpriority <> '1-URGENT'
+            and o_orderpriority <> '2-HIGH'
+            then 1
+        else 0
+    end) as low_line_count
+from $join
+where
+    (l_shipmode = 'MAIL' or l_shipmode = 'TRUCK')
+    and l_commitdate < l_receiptdate
+    and l_shipdate < l_commitdate
+    and cast(l_receiptdate as timestamp) >= $border
+    and cast(l_receiptdate as timestamp) < ($border + Interval("P365D"))
+group by
+    l_shipmode
+order by
+    l_shipmode;
diff --git a/yql/essentials/tests/sql/suites/tpch/q13.sql b/yql/essentials/tests/sql/suites/tpch/q13.sql
new file mode 100644
index 0000000000..e94f36a545
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q13.sql
@@ -0,0 +1,33 @@
+
+-- TPC-H/TPC-R Customer Distribution Query (Q13)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$orders = (
+    select
+        o_orderkey,
+        o_custkey
+    from
+        plato.orders
+    where
+        o_comment NOT LIKE "%unusual%requests%"
+);
+select
+    c_count as c_count,
+    count(*) as custdist
+from
+    (
+        select
+            c.c_custkey as c_custkey,
+            count(o.o_orderkey) as c_count
+        from
+            plato.customer as c left outer join $orders as o on
+                c.c_custkey = o.o_custkey
+        group by
+            c.c_custkey
+    ) as c_orders
+group by
+    c_count
+order by
+    custdist desc,
+    c_count desc;
diff --git a/yql/essentials/tests/sql/suites/tpch/q14.sql b/yql/essentials/tests/sql/suites/tpch/q14.sql
new file mode 100644
index 0000000000..99ad7f0c4e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q14.sql
@@ -0,0 +1,21 @@
+
+-- TPC-H/TPC-R Promotion Effect Query (Q14)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$border = Date("1994-08-01");
+select
+    100.00 * sum(case
+        when StartsWith(p.p_type, 'PROMO')
+            then l.l_extendedprice * (1 - l.l_discount)
+        else 0
+    end) / sum(l.l_extendedprice * (1 - l.l_discount)) as promo_revenue
+from
+    plato.lineitem as l
+join
+    plato.part as p
+on
+    l.l_partkey = p.p_partkey
+where
+    cast(l.l_shipdate as timestamp) >= $border
+    and cast(l.l_shipdate as timestamp) < ($border + Interval("P31D"));
diff --git a/yql/essentials/tests/sql/suites/tpch/q15.sql b/yql/essentials/tests/sql/suites/tpch/q15.sql
new file mode 100644
index 0000000000..e8a141e83f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q15.sql
@@ -0,0 +1,56 @@
+-- ignore runonopt plan diff
+-- TPC-H/TPC-R Top Supplier Query (Q15)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$border = Date("1997-03-01");
+$revenue0 = (
+    select
+        l_suppkey as supplier_no,
+        sum(l_extendedprice * (1 - l_discount)) as total_revenue,
+        cast(sum(l_extendedprice * (1 - l_discount)) as Uint64) as total_revenue_approx
+    from
+        plato.lineitem
+    where
+        cast(l_shipdate as timestamp) >= $border
+        and cast(l_shipdate as timestamp) < ($border + Interval("P92D"))
+    group by
+        l_suppkey
+);
+$max_revenue = (
+select
+    max(total_revenue_approx) as max_revenue
+from
+    $revenue0
+);
+$join1 = (
+select
+    s.s_suppkey as s_suppkey,
+    s.s_name as s_name,
+    s.s_address as s_address,
+    s.s_phone as s_phone,
+    r.total_revenue as total_revenue,
+    r.total_revenue_approx as total_revenue_approx
+from
+    plato.supplier as s
+join
+    $revenue0 as r
+on
+    s.s_suppkey = r.supplier_no
+);
+
+select
+    j.s_suppkey as s_suppkey,
+    j.s_name as s_name,
+    j.s_address as s_address,
+    j.s_phone as s_phone,
+    j.total_revenue as total_revenue
+from
+    $join1 as j
+join
+    $max_revenue as m
+on
+    j.total_revenue_approx = m.max_revenue
+order by
+    s_suppkey;
+
diff --git a/yql/essentials/tests/sql/suites/tpch/q16.sql b/yql/essentials/tests/sql/suites/tpch/q16.sql
new file mode 100644
index 0000000000..153b72797b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q16.sql
@@ -0,0 +1,44 @@
+
+-- TPC-H/TPC-R Parts/Supplier Relationship Query (Q16)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$join = (
+select
+    ps.ps_suppkey as ps_suppkey,
+    ps.ps_partkey as ps_partkey
+from
+    plato.partsupp as ps
+left join
+    plato.supplier as w
+on
+    w.s_suppkey = ps.ps_suppkey
+where not (s_comment like "%Customer%Complaints%")
+);
+
+select
+    p.p_brand as p_brand,
+    p.p_type as p_type,
+    p.p_size as p_size,
+    count(distinct j.ps_suppkey) as supplier_cnt
+from
+    $join as j
+join
+    plato.part as p
+on
+    p.p_partkey = j.ps_partkey
+where
+    p.p_brand <> 'Brand#33'
+    and (not StartsWith(p.p_type, 'PROMO POLISHED'))
+    and (p.p_size = 20 or p.p_size = 27 or p.p_size = 11 or p.p_size = 45 or p.p_size = 40 or p.p_size = 41 or p.p_size = 34 or p.p_size = 36)
+group by
+    p.p_brand,
+    p.p_type,
+    p.p_size
+order by
+    supplier_cnt desc,
+    p_brand,
+    p_type,
+    p_size
+;
+
diff --git a/yql/essentials/tests/sql/suites/tpch/q17.sql b/yql/essentials/tests/sql/suites/tpch/q17.sql
new file mode 100644
index 0000000000..c775d585bb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q17.sql
@@ -0,0 +1,41 @@
+
+-- TPC-H/TPC-R Small-Quantity-Order Revenue Query (Q17)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$threshold = (
+select
+    0.2 * avg(l_quantity) as threshold,
+    l_partkey
+from
+    plato.lineitem
+group by
+    l_partkey
+);
+
+$join1 = (
+select
+    p.p_partkey as p_partkey,
+    l.l_extendedprice as l_extendedprice,
+    l.l_quantity as l_quantity
+from
+    plato.lineitem as l
+join
+    plato.part as p
+on
+    p.p_partkey = l.l_partkey
+where
+    p.p_brand = 'Brand#35'
+    and p.p_container = 'LG DRUM'
+);
+
+select
+    sum(j.l_extendedprice) / 7.0 as avg_yearly
+from
+    $join1 as j
+join
+    $threshold as t
+on
+    j.p_partkey = t.l_partkey
+where
+    j.l_quantity < t.threshold;
diff --git a/yql/essentials/tests/sql/suites/tpch/q18.sql b/yql/essentials/tests/sql/suites/tpch/q18.sql
new file mode 100644
index 0000000000..dfc0e858ea
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q18.sql
@@ -0,0 +1,53 @@
+
+-- TPC-H/TPC-R Large Volume Customer Query (Q18)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$in = (
+select
+    l_orderkey,
+    sum(l_quantity) as sum_l_quantity
+from
+    plato.lineitem
+group by
+    l_orderkey having
+        sum(l_quantity) > 315
+);
+
+$join1 = (
+select
+    c.c_name as c_name,
+    c.c_custkey as c_custkey,
+    o.o_orderkey as o_orderkey,
+    o.o_orderdate as o_orderdate,
+    o.o_totalprice as o_totalprice
+from
+    plato.customer as c
+join
+    plato.orders as o
+on
+    c.c_custkey = o.o_custkey
+);
+select
+    j.c_name as c_name,
+    j.c_custkey as c_custkey,
+    j.o_orderkey as o_orderkey,
+    j.o_orderdate as o_orderdate,
+    j.o_totalprice as o_totalprice,
+    sum(i.sum_l_quantity) as sum_l_quantity
+from
+    $join1 as j
+join
+    $in as i
+on
+    i.l_orderkey = j.o_orderkey
+group by
+    j.c_name,
+    j.c_custkey,
+    j.o_orderkey,
+    j.o_orderdate,
+    j.o_totalprice
+order by
+    o_totalprice desc,
+    o_orderdate
+limit 100;
diff --git a/yql/essentials/tests/sql/suites/tpch/q19.sql b/yql/essentials/tests/sql/suites/tpch/q19.sql
new file mode 100644
index 0000000000..e9b3a7d7bc
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q19.sql
@@ -0,0 +1,40 @@
+
+-- TPC-H/TPC-R Discounted Revenue Query (Q19)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+select
+    sum(l.l_extendedprice* (1 - l.l_discount)) as revenue
+from
+    plato.lineitem as l
+join
+    plato.part as p
+on
+    p.p_partkey = l.l_partkey
+where
+    (
+        p.p_brand = 'Brand#23'
+        and (p.p_container = 'SM CASE' or p.p_container = 'SM BOX' or p.p_container = 'SM PACK' or p.p_container = 'SM PKG')
+        and l.l_quantity >= 7 and l.l_quantity <= 7 + 10
+        and p.p_size between 1 and 5
+        and (l.l_shipmode = 'AIR' or l.l_shipmode = 'AIR REG')
+        and l.l_shipinstruct = 'DELIVER IN PERSON'
+    )
+    or
+    (
+        p.p_brand = 'Brand#15'
+        and (p.p_container = 'MED BAG' or p.p_container = 'MED BOX' or p.p_container = 'MED PKG' or p.p_container = 'MED PACK')
+        and l.l_quantity >= 17 and l.l_quantity <= 17 + 10
+        and p.p_size between 1 and 10
+        and (l.l_shipmode = 'AIR' or l.l_shipmode = 'AIR REG')
+        and l.l_shipinstruct = 'DELIVER IN PERSON'
+    )
+    or
+    (
+        p.p_brand = 'Brand#44'
+        and (p.p_container = 'LG CASE' or p.p_container = 'LG BOX' or p.p_container = 'LG PACK' or p.p_container = 'LG PKG')
+        and l.l_quantity >= 25 and l.l_quantity <= 25 + 10
+        and p.p_size between 1 and 15
+        and (l.l_shipmode = 'AIR' or l.l_shipmode = 'AIR REG')
+        and l.l_shipinstruct = 'DELIVER IN PERSON'
+    );
diff --git a/yql/essentials/tests/sql/suites/tpch/q2.sql b/yql/essentials/tests/sql/suites/tpch/q2.sql
new file mode 100644
index 0000000000..f8e11a8c5d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q2.sql
@@ -0,0 +1,68 @@
+
+-- TPC-H/TPC-R Minimum Cost Supplier Query (Q2)
+-- using 1680793381 as a seed to the RNG
+
+$r = (select r_regionkey from 
+    plato.region
+where r_name='AMERICA');
+
+$j1 = (select n_name,n_nationkey
+    from plato.nation as n 
+    join $r as r on 
+    n.n_regionkey = r.r_regionkey);
+
+$j2 = (select s_acctbal,s_name,s_address,s_phone,s_comment,n_name,s_suppkey
+    from plato.supplier as s
+    join $j1 as j on
+    s.s_nationkey = j.n_nationkey
+);
+
+$j3 = (select ps_partkey,ps_supplycost,s_acctbal,s_name,s_address,s_phone,s_comment,n_name
+    from plato.partsupp as ps
+    join $j2 as j on
+    ps.ps_suppkey = j.s_suppkey
+);
+
+$min_ps_supplycost = (select min(ps_supplycost) as min_ps_supplycost,ps_partkey
+    from $j3
+    group by ps_partkey
+);
+
+$p = (select p_partkey,p_mfgr
+    from plato.part
+    where
+    p_size = 10
+    and p_type like '%COPPER'
+);
+
+$j4 = (select s_acctbal,
+    s_name,
+    n_name,
+    p_partkey,
+    p_mfgr,
+    s_address,
+    s_phone,
+    s_comment
+    from $p as p
+    join $j3 as j on p.p_partkey = j.ps_partkey
+    join $min_ps_supplycost as m on p.p_partkey = m.ps_partkey
+    where min_ps_supplycost=ps_supplycost
+);
+
+select 
+    s_acctbal,
+    s_name,
+    n_name,
+    p_partkey,
+    p_mfgr,
+    s_address,
+    s_phone,
+    s_comment
+from $j4 
+order by
+    s_acctbal desc,
+    n_name,
+    s_name,
+    p_partkey
+limit 100;
+
diff --git a/yql/essentials/tests/sql/suites/tpch/q20.sql b/yql/essentials/tests/sql/suites/tpch/q20.sql
new file mode 100644
index 0000000000..3272f866d6
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q20.sql
@@ -0,0 +1,83 @@
+
+-- TPC-H/TPC-R Potential Part Promotion Query (Q20)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$border = Date("1993-01-01");
+$threshold = (
+select
+    0.5 * sum(l_quantity) as threshold,
+    l_partkey as l_partkey,
+    l_suppkey as l_suppkey
+from
+    plato.lineitem
+where
+    cast(l_shipdate as timestamp) >= $border
+    and cast(l_shipdate as timestamp) < ($border + Interval("P365D"))
+group by
+    l_partkey, l_suppkey
+);
+
+$parts = (
+select
+    p_partkey
+from
+    plato.part
+where
+    StartsWith(p_name, 'maroon')
+);
+
+$join1 = (
+select
+    ps.ps_suppkey as ps_suppkey,
+    ps.ps_availqty as ps_availqty,
+    ps.ps_partkey as ps_partkey
+from
+    plato.partsupp as ps
+join any
+    $parts as p
+on
+    ps.ps_partkey = p.p_partkey
+);
+
+$join2 = (
+select
+    distinct(j.ps_suppkey) as ps_suppkey
+from
+    $join1 as j
+join any
+    $threshold as t
+on
+    j.ps_partkey = t.l_partkey and j.ps_suppkey = t.l_suppkey
+where
+    j.ps_availqty > t.threshold
+);
+
+$join3 = (
+select
+    j.ps_suppkey as ps_suppkey,
+    s.s_name as s_name,
+    s.s_address as s_address,
+    s.s_nationkey as s_nationkey
+from
+    $join2 as j
+join any
+    plato.supplier as s
+on
+    j.ps_suppkey = s.s_suppkey
+);
+
+select
+    j.s_name as s_name,
+    j.s_address as s_address
+from
+    $join3 as j
+join
+    plato.nation as n
+on
+    j.s_nationkey = n.n_nationkey
+where
+    n.n_name = 'VIETNAM'
+order by
+    s_name;
+
diff --git a/yql/essentials/tests/sql/suites/tpch/q21.sql b/yql/essentials/tests/sql/suites/tpch/q21.sql
new file mode 100644
index 0000000000..8b116c809c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q21.sql
@@ -0,0 +1,51 @@
+
+-- TPC-H/TPC-R Suppliers Who Kept Orders Waiting Query (Q21)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$n = select n_nationkey from plato.nation
+where n_name = 'EGYPT';
+
+$s = select s_name, s_suppkey from plato.supplier as supplier
+join $n as nation
+on supplier.s_nationkey = nation.n_nationkey;
+
+$l = select l_suppkey, l_orderkey from plato.lineitem
+where l_receiptdate > l_commitdate;
+
+$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
+join plato.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
+on l1.l_orderkey = l2.l_orderkey;
+
+$j4 = select s_name from $j3 as l1
+join plato.orders as orders
+on orders.o_orderkey = l1.l_orderkey
+where o_orderstatus = 'F';
+
+select s_name,
+    count(*) as numwait from $j4
+group by
+    s_name
+order by
+    numwait desc,
+    s_name
+limit 100;
+
diff --git a/yql/essentials/tests/sql/suites/tpch/q22.sql b/yql/essentials/tests/sql/suites/tpch/q22.sql
new file mode 100644
index 0000000000..4b12ec51a7
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q22.sql
@@ -0,0 +1,58 @@
+
+-- TPC-H/TPC-R Global Sales Opportunity Query (Q22)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$customers = (
+select
+    c_acctbal,
+    c_custkey,
+    Substring(c_phone, 0u, 2u) as cntrycode
+from
+    plato.customer
+where (Substring(c_phone, 0u, 2u) = '31' or Substring(c_phone, 0u, 2u) = '29' or Substring(c_phone, 0u, 2u) = '30' or Substring(c_phone, 0u, 2u) = '26' or Substring(c_phone, 0u, 2u) = '28' or Substring(c_phone, 0u, 2u) = '25' or Substring(c_phone, 0u, 2u) = '15')
+);
+$avg = (
+select
+    avg(c_acctbal) as a
+from
+    $customers
+where
+    c_acctbal > 0.00
+);
+$join1 = (
+select
+    c.c_acctbal as c_acctbal,
+    c.c_custkey as c_custkey,
+    c.cntrycode as cntrycode
+from
+    $customers as c
+cross join
+    $avg as a
+where
+    c.c_acctbal > a.a
+);
+$join2 = (
+select
+    j.cntrycode as cntrycode,
+    c_custkey,
+    j.c_acctbal as c_acctbal
+from
+    $join1 as j
+left only join 
+    plato.orders as o
+on
+    o.o_custkey = j.c_custkey
+);
+
+select
+    cntrycode,
+    count(*) as numcust,
+    sum(c_acctbal) as totacctbal
+from
+    $join2 as custsale
+group by
+    cntrycode
+order by
+    cntrycode;
+
diff --git a/yql/essentials/tests/sql/suites/tpch/q3.sql b/yql/essentials/tests/sql/suites/tpch/q3.sql
new file mode 100644
index 0000000000..668cb9441f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q3.sql
@@ -0,0 +1,55 @@
+
+-- TPC-H/TPC-R Shipping Priority Query (Q3)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$join1 = (
+select
+    c.c_mktsegment as c_mktsegment,
+    o.o_orderdate as o_orderdate,
+    o.o_shippriority as o_shippriority,
+    o.o_orderkey as o_orderkey
+from
+    plato.customer as c
+join
+    plato.orders as o
+on
+    c.c_custkey = o.o_custkey
+);
+
+$join2 = (
+select
+    j1.c_mktsegment as c_mktsegment,
+    j1.o_orderdate as o_orderdate,
+    j1.o_shippriority as o_shippriority,
+    l.l_orderkey as l_orderkey,
+    l.l_discount as l_discount,
+    l.l_shipdate as l_shipdate,
+    l.l_extendedprice as l_extendedprice
+from
+    $join1 as j1
+join
+    plato.lineitem as l
+on
+    l.l_orderkey = j1.o_orderkey
+);
+
+select
+    l_orderkey,
+    sum(l_extendedprice * (1 - l_discount)) as revenue,
+    o_orderdate,
+    o_shippriority
+from
+    $join2
+where
+    c_mktsegment = 'MACHINERY'
+    and CAST(o_orderdate AS Timestamp) < Date('1995-03-08')
+    and CAST(l_shipdate AS Timestamp) > Date('1995-03-08')
+group by
+    l_orderkey,
+    o_orderdate,
+    o_shippriority
+order by
+    revenue desc,
+    o_orderdate
+limit 10;
diff --git a/yql/essentials/tests/sql/suites/tpch/q4.sql b/yql/essentials/tests/sql/suites/tpch/q4.sql
new file mode 100644
index 0000000000..9b02c049cd
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q4.sql
@@ -0,0 +1,29 @@
+
+-- TPC-H/TPC-R Order Priority Checking Query (Q4)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$border = Date("1994-03-01");
+
+$join = (select
+        o.o_orderpriority as o_orderpriority,
+        o.o_orderdate as o_orderdate,
+        l.l_commitdate as l_commitdate,
+        l.l_receiptdate as l_receiptdate
+    from
+        plato.orders as o
+        join any plato.lineitem as l
+        on o.o_orderkey = l.l_orderkey);
+
+select
+    o_orderpriority,
+    count(*) as order_count
+from $join
+where
+    CAST(o_orderdate AS Timestamp) >= $border
+    and CAST(o_orderdate AS Timestamp) < DateTime::MakeDate(DateTime::ShiftMonths($border, 3))
+    and l_commitdate < l_receiptdate
+group by
+    o_orderpriority
+order by
+    o_orderpriority;
diff --git a/yql/essentials/tests/sql/suites/tpch/q5.sql b/yql/essentials/tests/sql/suites/tpch/q5.sql
new file mode 100644
index 0000000000..62e7acc7d1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q5.sql
@@ -0,0 +1,102 @@
+
+-- TPC-H/TPC-R Local Supplier Volume Query (Q5)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$join1 = (
+select
+    o.o_orderkey as o_orderkey,
+    o.o_orderdate as o_orderdate,
+    c.c_nationkey as c_nationkey
+from
+    plato.customer as c
+join
+    plato.orders as o
+on
+    c.c_custkey = o.o_custkey
+);
+
+$join2 = (
+select
+    j.o_orderkey as o_orderkey,
+    j.o_orderdate as o_orderdate,
+    j.c_nationkey as c_nationkey,
+    l.l_extendedprice as l_extendedprice,
+    l.l_discount as l_discount,
+    l.l_suppkey as l_suppkey
+from
+    $join1 as j
+join
+    plato.lineitem as l
+on
+    l.l_orderkey = j.o_orderkey
+);
+
+$join3 = (
+select
+    j.o_orderkey as o_orderkey,
+    j.o_orderdate as o_orderdate,
+    j.c_nationkey as c_nationkey,
+    j.l_extendedprice as l_extendedprice,
+    j.l_discount as l_discount,
+    j.l_suppkey as l_suppkey,
+    s.s_nationkey as s_nationkey
+from
+    $join2 as j
+join
+    plato.supplier as s
+on
+    j.l_suppkey = s.s_suppkey
+);
+$join4 = (
+select
+    j.o_orderkey as o_orderkey,
+    j.o_orderdate as o_orderdate,
+    j.c_nationkey as c_nationkey,
+    j.l_extendedprice as l_extendedprice,
+    j.l_discount as l_discount,
+    j.l_suppkey as l_suppkey,
+    j.s_nationkey as s_nationkey,
+    n.n_regionkey as n_regionkey,
+    n.n_name as n_name
+from
+    $join3 as j
+join
+    plato.nation as n
+on
+    j.s_nationkey = n.n_nationkey
+    and j.c_nationkey = n.n_nationkey
+);
+$join5 = (
+select
+    j.o_orderkey as o_orderkey,
+    j.o_orderdate as o_orderdate,
+    j.c_nationkey as c_nationkey,
+    j.l_extendedprice as l_extendedprice,
+    j.l_discount as l_discount,
+    j.l_suppkey as l_suppkey,
+    j.s_nationkey as s_nationkey,
+    j.n_regionkey as n_regionkey,
+    j.n_name as n_name,
+    r.r_name as r_name
+from
+    $join4 as j
+join
+    plato.region as r
+on
+    j.n_regionkey = r.r_regionkey
+);
+$border = Date("1995-01-01");
+select
+    n_name,
+    sum(l_extendedprice * (1 - l_discount)) as revenue
+from
+    $join5
+where
+    r_name = 'AFRICA'
+    and CAST(o_orderdate AS Timestamp) >= $border
+    and CAST(o_orderdate AS Timestamp) < ($border + Interval("P365D"))
+group by
+    n_name
+order by
+    revenue desc;
diff --git a/yql/essentials/tests/sql/suites/tpch/q6.sql b/yql/essentials/tests/sql/suites/tpch/q6.sql
new file mode 100644
index 0000000000..c96b8ac098
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q6.sql
@@ -0,0 +1,16 @@
+
+-- TPC-H/TPC-R Forecasting Revenue Change Query (Q6)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$border = Date("1995-01-01");
+
+select
+    sum(l_extendedprice * l_discount) as revenue
+from
+    plato.lineitem
+where
+    CAST(l_shipdate AS Timestamp) >= $border 
+    and cast(l_shipdate as Timestamp) < ($border + Interval("P365D"))
+    and l_discount between 0.07 - 0.01 and 0.07 + 0.01
+    and l_quantity < 25;
diff --git a/yql/essentials/tests/sql/suites/tpch/q7.sql b/yql/essentials/tests/sql/suites/tpch/q7.sql
new file mode 100644
index 0000000000..6ff0eec4db
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q7.sql
@@ -0,0 +1,87 @@
+
+-- TPC-H/TPC-R Volume Shipping Query (Q7)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$n = select n_name, n_nationkey from plato.nation as n
+    where n_name = 'PERU' or n_name = 'MOZAMBIQUE';
+
+$l = select 
+    l_orderkey, l_suppkey,
+    DateTime::GetYear(cast(l_shipdate as timestamp)) as l_year,
+    l_extendedprice * (1 - l_discount) as volume
+from 
+    plato.lineitem as l
+where 
+    cast(cast(l.l_shipdate as Timestamp) as Date) 
+    between Date('1995-01-01') and Date('1996-12-31');
+
+$j1 = select 
+    n_name as supp_nation,
+    s_suppkey
+from 
+    plato.supplier as supplier
+join 
+    $n as n1
+on 
+    supplier.s_nationkey = n1.n_nationkey;
+
+$j2 = select
+    n_name as cust_nation,
+    c_custkey
+from 
+    plato.customer as customer
+join 
+    $n as n2
+on 
+    customer.c_nationkey = n2.n_nationkey;
+
+$j3 = select
+    cust_nation, o_orderkey
+from 
+    plato.orders as orders
+join 
+    $j2 as customer
+on 
+    orders.o_custkey = customer.c_custkey;
+
+$j4 = select
+    cust_nation,
+    l_orderkey, l_suppkey,
+    l_year,
+    volume
+from 
+    $l as lineitem
+join 
+    $j3 as orders
+on 
+    lineitem.l_orderkey = orders.o_orderkey;
+
+$j5 = select
+    supp_nation, cust_nation,
+    l_year, volume
+from 
+    $j4 as lineitem
+join 
+    $j1 as supplier
+on 
+    lineitem.l_suppkey = supplier.s_suppkey
+where (supp_nation = 'PERU' and cust_nation = 'MOZAMBIQUE')
+    OR (supp_nation = 'MOZAMBIQUE' and cust_nation = 'PERU');
+
+select
+    supp_nation,
+    cust_nation,
+    l_year, 
+    sum(volume) as revenue
+from
+    $j5 as shipping
+group by
+    supp_nation,
+    cust_nation,
+    l_year
+order by
+    supp_nation,
+    cust_nation,
+    l_year;
+
diff --git a/yql/essentials/tests/sql/suites/tpch/q8.sql b/yql/essentials/tests/sql/suites/tpch/q8.sql
new file mode 100644
index 0000000000..855813a06e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q8.sql
@@ -0,0 +1,110 @@
+
+-- TPC-H/TPC-R National Market Share Query (Q8)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$join1 = (
+select
+    l.l_extendedprice * (1 - l.l_discount) as volume,
+    l.l_suppkey as l_suppkey,
+    l.l_orderkey as l_orderkey
+from
+    plato.part as p
+join
+    plato.lineitem as l
+on
+    p.p_partkey = l.l_partkey
+where
+    p.p_type = 'ECONOMY PLATED COPPER'
+);
+$join2 = (
+select
+    j.volume as volume,
+    j.l_orderkey as l_orderkey,
+    s.s_nationkey as s_nationkey
+from
+    $join1 as j
+join
+    plato.supplier as s
+on
+    s.s_suppkey = j.l_suppkey
+);
+$join3 = (
+select
+    j.volume as volume,
+    j.l_orderkey as l_orderkey,
+    n.n_name as nation
+from
+    $join2 as j
+join
+    plato.nation as n
+on
+    n.n_nationkey = j.s_nationkey
+);
+$join4 = (
+select
+    j.volume as volume,
+    j.nation as nation,
+    DateTime::GetYear(cast(o.o_orderdate as Timestamp)) as o_year,
+    o.o_custkey as o_custkey
+from
+    $join3 as j
+join
+    plato.orders as o
+on
+    o.o_orderkey = j.l_orderkey
+where cast(cast(o_orderdate as Timestamp) as Date) between Date('1995-01-01') and Date('1996-12-31')
+);
+$join5 = (
+select
+    j.volume as volume,
+    j.nation as nation,
+    j.o_year as o_year,
+    c.c_nationkey as c_nationkey
+from
+    $join4 as j
+join
+    plato.customer as c
+on
+    c.c_custkey = j.o_custkey
+);
+$join6 = (
+select
+    j.volume as volume,
+    j.nation as nation,
+    j.o_year as o_year,
+    n.n_regionkey as n_regionkey
+from
+    $join5 as j
+join
+    plato.nation as n
+on
+    n.n_nationkey = j.c_nationkey
+);
+$join7 = (
+select
+    j.volume as volume,
+    j.nation as nation,
+    j.o_year as o_year
+from
+    $join6 as j
+join
+    plato.region as r
+on
+    r.r_regionkey = j.n_regionkey
+where
+    r.r_name = 'AFRICA'
+);
+
+select
+    o_year,
+    sum(case
+        when nation = 'MOZAMBIQUE' then volume
+        else 0
+    end) / sum(volume) as mkt_share
+from
+    $join7 as all_nations
+group by
+    o_year
+order by
+    o_year;
diff --git a/yql/essentials/tests/sql/suites/tpch/q9.sql b/yql/essentials/tests/sql/suites/tpch/q9.sql
new file mode 100644
index 0000000000..f6c9f914ed
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q9.sql
@@ -0,0 +1,59 @@
+
+-- TPC-H/TPC-R Product Type Profit Measure Query (Q9)
+-- Approved February 1998
+-- using 1680793381 as a seed to the RNG
+
+$p = (select p_partkey, p_name
+from
+    plato.part
+where FIND(p_name, 'rose') IS NOT NULL);
+
+$j1 = (select ps_partkey, ps_suppkey, ps_supplycost
+from
+    plato.partsupp as ps
+join $p as p
+on ps.ps_partkey = p.p_partkey);
+
+$j2 = (select l_suppkey, l_partkey, l_orderkey, l_extendedprice, l_discount, ps_supplycost, l_quantity
+from
+    plato.lineitem as l
+join $j1 as j
+on l.l_suppkey = j.ps_suppkey AND l.l_partkey = j.ps_partkey);
+
+$j3 = (select l_orderkey, s_nationkey, l_extendedprice, l_discount, ps_supplycost, l_quantity
+from
+    plato.supplier as s
+join $j2 as j
+on j.l_suppkey = s.s_suppkey);
+
+$j4 = (select o_orderdate, l_extendedprice, l_discount, ps_supplycost, l_quantity, s_nationkey
+from
+    plato.orders as o
+join $j3 as j
+on o.o_orderkey = j.l_orderkey);
+
+$j5 = (select n_name, o_orderdate, l_extendedprice, l_discount, ps_supplycost, l_quantity
+from
+    plato.nation as n
+join $j4 as j
+on j.s_nationkey = n.n_nationkey
+);
+
+$profit = (select 
+    n_name as nation,
+    DateTime::GetYear(cast(o_orderdate as timestamp)) as o_year,
+    l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
+from $j5);
+
+select
+    nation,
+    o_year,
+    sum(amount) as sum_profit
+from $profit
+group by
+    nation,
+    o_year
+order by
+    nation,
+    o_year desc;
+
diff --git a/yql/essentials/tests/sql/suites/tpch/region.txt b/yql/essentials/tests/sql/suites/tpch/region.txt
new file mode 100644
index 0000000000..14ddd4ea7b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/region.txt
@@ -0,0 +1 @@
+{"r_regionkey"=0;"r_comment"="lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to ";"r_name"="AFRICA";};
diff --git a/yql/essentials/tests/sql/suites/tpch/region.txt.attr b/yql/essentials/tests/sql/suites/tpch/region.txt.attr
new file mode 100644
index 0000000000..daadd6b05b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/region.txt.attr
@@ -0,0 +1,26 @@
+{
+    "schema" = <
+        "strict" = %true;
+        "unique_keys" = %false;
+    > [
+        {
+            "name" = "r_regionkey";
+            "required" = %true;
+            "sort_order" = "ascending";
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "r_comment";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "r_name";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+    ];
+}
diff --git a/yql/essentials/tests/sql/suites/tpch/supplier.txt b/yql/essentials/tests/sql/suites/tpch/supplier.txt
new file mode 100644
index 0000000000..52c12d3c7f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/supplier.txt
@@ -0,0 +1 @@
+{"s_suppkey"=1;"s_comment"="each slyly above the careful";"s_address"=" N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ";"s_acctbal"=5755.94;"s_nationkey"=17;"s_name"="Supplier#000000001";"s_phone"="27-918-335-1736";};
diff --git a/yql/essentials/tests/sql/suites/tpch/supplier.txt.attr b/yql/essentials/tests/sql/suites/tpch/supplier.txt.attr
new file mode 100644
index 0000000000..cc250b2226
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/supplier.txt.attr
@@ -0,0 +1,50 @@
+{
+    "schema" = <
+        "strict" = %true;
+        "unique_keys" = %false;
+    > [
+        {
+            "name" = "s_suppkey";
+            "required" = %true;
+            "sort_order" = "ascending";
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "s_comment";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "s_address";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "s_acctbal";
+            "required" = %true;
+            "type" = "double";
+            "type_v3" = "double";
+        };
+        {
+            "name" = "s_nationkey";
+            "required" = %true;
+            "type" = "int32";
+            "type_v3" = "int32";
+        };
+        {
+            "name" = "s_name";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+        {
+            "name" = "s_phone";
+            "required" = %true;
+            "type" = "string";
+            "type_v3" = "string";
+        };
+    ];
+}