YQL-19206 Move contrib/ydb/library/yql/tests/sql/suites -> yql/essentials/tests/sql/suites

commit_hash:d0ef1f92b09c94db7c2408f946d2a4c62b603f00

39 files changed, 846 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/pg-tpch/customer.txt b/yql/essentials/tests/sql/suites/pg-tpch/customer.txt
new file mode 100644
index 0000000000..28e42f042d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/customer.txt
@@ -0,0 +1 @@
+{"c_acctbal"="\0\2\0\0\0\0\0\2\x11\xAA\x1D\xB0";"c_address"="ocIz 2S9MsEyfkL";"c_comment"="dolites alongside of the furiously pending theodolites affix closely idly bold instruction";"c_custkey"=750001;"c_mktsegment"="FURNITURE";"c_name"="Customer#000750001";"c_nationkey"=6;"c_phone"="16-182-876-9496";};
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/customer.txt.attr b/yql/essentials/tests/sql/suites/pg-tpch/customer.txt.attr
new file mode 100644
index 0000000000..858a12536d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/customer.txt.attr
@@ -0,0 +1,14 @@
+{
+    "_yql_row_spec"={
+       "Type"=["StructType";[
+           ["c_custkey";["PgType";"int4";];];
+           ["c_name";["PgType";"text";];];
+           ["c_address";["PgType";"text";];];
+           ["c_nationkey";["PgType";"int4";];];
+           ["c_phone";["PgType";"text";];];
+           ["c_acctbal";["PgType";"numeric";];];
+           ["c_mktsegment";["PgType";"text";];];
+           ["c_comment";["PgType";"text";];];
+       ];];
+    }
+}
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/default.cfg b/yql/essentials/tests/sql/suites/pg-tpch/default.cfg
new file mode 100644
index 0000000000..f3c9624348
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-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/pg-tpch/lineitem.txt b/yql/essentials/tests/sql/suites/pg-tpch/lineitem.txt
new file mode 100644
index 0000000000..c730fcaafb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/lineitem.txt
@@ -0,0 +1 @@
+{"l_comment"="ckages boost throug";"l_commitdate"="\xFF\xFF\xF9V";"l_discount"="\0\1\xFF\xFF\0\0\0\2\2X";"l_extendedprice"="\0\3\0\1\0\0\0\2\0\2\6\xEF\x1B\xBC";"l_linenumber"=1;"l_linestatus"="F";"l_orderkey"=5997378;"l_partkey"=1292154;"l_quantity"="\0\1\0\0\0\0\0\0\0\x13";"l_receiptdate"="\xFF\xFF\xF9?";"l_returnflag"="A";"l_shipdate"="\xFF\xFF\xF9<";"l_shipinstruct"="COLLECT COD";"l_shipmode"="RAIL";"l_suppkey"=67191;"l_tax"="\0\1\xFF\xFF\0\0\0\2\0\xC8";};
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/lineitem.txt.attr b/yql/essentials/tests/sql/suites/pg-tpch/lineitem.txt.attr
new file mode 100644
index 0000000000..f6de3aba92
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/lineitem.txt.attr
@@ -0,0 +1,22 @@
+{
+    "_yql_row_spec"={
+       "Type"=["StructType";[
+           ["l_orderkey";["PgType";"int4";];];
+           ["l_partkey";["PgType";"int4";];];
+           ["l_suppkey";["PgType";"int4";];];
+           ["l_linenumber";["PgType";"int4";];];
+           ["l_quantity";["PgType";"numeric";];];
+           ["l_extendedprice";["PgType";"numeric";];];
+           ["l_discount";["PgType";"numeric";];];
+           ["l_tax";["PgType";"numeric";];];
+           ["l_returnflag";["PgType";"text";];];
+           ["l_linestatus";["PgType";"text";];];
+           ["l_shipdate";["PgType";"date";];];
+           ["l_commitdate";["PgType";"date";];];
+           ["l_receiptdate";["PgType";"date";];];
+           ["l_shipinstruct";["PgType";"text";];];
+           ["l_shipmode";["PgType";"text";];];
+           ["l_comment";["PgType";"text";];];
+       ];];
+    }
+}
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/nation.txt b/yql/essentials/tests/sql/suites/pg-tpch/nation.txt
new file mode 100644
index 0000000000..b3784e0a7b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/nation.txt
@@ -0,0 +1 @@
+{"n_comment"=" haggle. carefully final deposits detect slyly agai";"n_name"="ALGERIA";"n_nationkey"=0;"n_regionkey"=0;};
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/nation.txt.attr b/yql/essentials/tests/sql/suites/pg-tpch/nation.txt.attr
new file mode 100644
index 0000000000..984875af2b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/nation.txt.attr
@@ -0,0 +1,10 @@
+{
+    "_yql_row_spec"={
+       "Type"=["StructType";[
+           ["n_nationkey";["PgType";"int4";];];
+           ["n_name";["PgType";"text";];];
+           ["n_regionkey";["PgType";"int4";];];
+           ["n_comment";["PgType";"text";];];
+       ];];
+    }
+}
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/orders.txt b/yql/essentials/tests/sql/suites/pg-tpch/orders.txt
new file mode 100644
index 0000000000..59a7a09401
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/orders.txt
@@ -0,0 +1 @@
+{"o_clerk"="Clerk#000006222";"o_comment"=" ironic platelets. final instructions haggle ";"o_custkey"=1110145;"o_orderdate"="\xFF\xFF\xF6k";"o_orderkey"=55714306;"o_orderpriority"="4-NOT SPECIFIED";"o_orderstatus"="F";"o_shippriority"=0;"o_totalprice"="\0\3\0\1\0\0\0\1\0\x10\x1A\xB2\x1BX";};
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/orders.txt.attr b/yql/essentials/tests/sql/suites/pg-tpch/orders.txt.attr
new file mode 100644
index 0000000000..dc3829bfe4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/orders.txt.attr
@@ -0,0 +1,15 @@
+{
+    "_yql_row_spec"={
+       "Type"=["StructType";[
+           ["o_orderkey";["PgType";"int4";];];
+           ["o_custkey";["PgType";"int4";];];
+           ["o_orderstatus";["PgType";"text";];];
+           ["o_totalprice";["PgType";"numeric";];];
+           ["o_orderdate";["PgType";"date";];];
+           ["o_orderpriority";["PgType";"text";];];
+           ["o_clerk";["PgType";"text";];];
+           ["o_shippriority";["PgType";"int4";];];
+           ["o_comment";["PgType";"text";];];
+       ];];
+    }
+}
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/part.txt b/yql/essentials/tests/sql/suites/pg-tpch/part.txt
new file mode 100644
index 0000000000..34b4faf5b2
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/part.txt
@@ -0,0 +1 @@
+{"p_brand"="Brand#13";"p_comment"="ly. slyly ironi";"p_container"="JUMBO PKG";"p_mfgr"="Manufacturer#1";"p_name"="goldenrod lavender spring chocolate lace";"p_partkey"=1;"p_retailprice"="\0\1\0\0\0\0\0\0\3\x85";"p_size"=7;"p_type"="PROMO BURNISHED COPPER";};
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/part.txt.attr b/yql/essentials/tests/sql/suites/pg-tpch/part.txt.attr
new file mode 100644
index 0000000000..c4d0a973fc
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/part.txt.attr
@@ -0,0 +1,15 @@
+{
+    "_yql_row_spec"={
+       "Type"=["StructType";[
+           ["p_partkey";["PgType";"int4";];];
+           ["p_name";["PgType";"text";];];
+           ["p_mfgr";["PgType";"text";];];
+           ["p_brand";["PgType";"text";];];
+           ["p_type";["PgType";"text";];];
+           ["p_size";["PgType";"int4";];];
+           ["p_container";["PgType";"text";];];
+           ["p_retailprice";["PgType";"numeric";];];
+           ["p_comment";["PgType";"text";];];
+       ];];
+    }
+}
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/partsupp.txt b/yql/essentials/tests/sql/suites/pg-tpch/partsupp.txt
new file mode 100644
index 0000000000..7ccfca7134
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/partsupp.txt
@@ -0,0 +1 @@
+{"ps_availqty"=3903;"ps_comment"="olites. blithely ironic accounts sublate furiously slow instructions. blithely ironic deposits pr";"ps_partkey"=1400001;"ps_suppkey"=2;"ps_supplycost"="\0\2\0\0\0\0\0\2\3Y\x0E\xD8";};
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/partsupp.txt.attr b/yql/essentials/tests/sql/suites/pg-tpch/partsupp.txt.attr
new file mode 100644
index 0000000000..7e6a2bd73c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/partsupp.txt.attr
@@ -0,0 +1,11 @@
+{
+    "_yql_row_spec"={
+       "Type"=["StructType";[
+           ["ps_partkey";["PgType";"int4";];];
+           ["ps_suppkey";["PgType";"int4";];];
+           ["ps_availqty";["PgType";"int4";];];
+           ["ps_supplycost";["PgType";"numeric";];];
+           ["ps_comment";["PgType";"text";];];
+       ];];
+    }
+}
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q01.sql b/yql/essentials/tests/sql/suites/pg-tpch/q01.sql
new file mode 100644
index 0000000000..7198f496bc
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q01.sql
@@ -0,0 +1,25 @@
+--!syntax_pg
+--TPC-H Q1
+
+
+select
+l_returnflag, 
+l_linestatus, 
+sum(l_quantity) as sum_qty,
+sum(l_extendedprice) as sum_base_price,
+sum(l_extendedprice*(1::numeric-l_discount)) as sum_disc_price,
+sum(l_extendedprice*(1::numeric-l_discount)*(1::numeric+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 
+l_shipdate <= date '1998-12-01' - interval '90' day
+group by 
+l_returnflag, 
+l_linestatus
+order by 
+l_returnflag, 
+l_linestatus;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q02.sql b/yql/essentials/tests/sql/suites/pg-tpch/q02.sql
new file mode 100644
index 0000000000..91349256c7
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q02.sql
@@ -0,0 +1,49 @@
+--!syntax_pg
+--ignore runonopt plan diff
+--TPC-H Q2
+
+
+select
+s_acctbal, 
+s_name, 
+n_name, 
+p_partkey, 
+p_mfgr, 
+s_address, 
+s_phone, 
+s_comment
+from 
+plato."part", 
+plato."supplier", 
+plato."partsupp", 
+plato."nation", 
+plato."region"
+where 
+p_partkey = ps_partkey
+and s_suppkey = ps_suppkey
+and p_size = 15
+and p_type like '%BRASS'
+and s_nationkey = n_nationkey
+and n_regionkey = r_regionkey
+and r_name = 'EUROPE'
+and ps_supplycost = (
+select 
+min(ps_supplycost)
+from 
+plato."partsupp",
+plato."supplier", 
+plato."nation",
+plato."region"
+where 
+p_partkey = ps_partkey
+and s_suppkey = ps_suppkey
+and s_nationkey = n_nationkey
+and n_regionkey = r_regionkey
+and r_name = 'EUROPE'
+)
+order by 
+s_acctbal desc, 
+n_name, 
+s_name, 
+p_partkey
+limit 100;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q03.sql b/yql/essentials/tests/sql/suites/pg-tpch/q03.sql
new file mode 100644
index 0000000000..fd34b86d2b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q03.sql
@@ -0,0 +1,27 @@
+--!syntax_pg
+--TPC-H Q3
+
+
+select
+l_orderkey, 
+sum(l_extendedprice*(1::numeric-l_discount)) as revenue,
+o_orderdate, 
+o_shippriority
+from 
+plato."customer", 
+plato."orders", 
+plato."lineitem"
+where 
+c_mktsegment = 'BUILDING'
+and c_custkey = o_custkey
+and l_orderkey = o_orderkey
+and o_orderdate < date '1995-03-15'
+and l_shipdate > date '1995-03-15'
+group by 
+l_orderkey, 
+o_orderdate, 
+o_shippriority
+order by 
+revenue desc, 
+o_orderdate
+limit 10;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q04.sql b/yql/essentials/tests/sql/suites/pg-tpch/q04.sql
new file mode 100644
index 0000000000..40718918ee
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q04.sql
@@ -0,0 +1,26 @@
+--!syntax_pg
+--ignore runonopt plan diff
+--TPC-H Q4
+
+
+select
+o_orderpriority, 
+count(*) as order_count
+from 
+plato."orders"
+where 
+o_orderdate >= date '1993-07-01'
+and o_orderdate < date '1993-07-01' + interval '3' month
+and exists (
+select 
+*
+from 
+plato."lineitem"
+where 
+l_orderkey = o_orderkey
+and l_commitdate < l_receiptdate
+)
+group by 
+o_orderpriority
+order by 
+o_orderpriority;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q05.sql b/yql/essentials/tests/sql/suites/pg-tpch/q05.sql
new file mode 100644
index 0000000000..089c838380
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q05.sql
@@ -0,0 +1,28 @@
+--!syntax_pg
+--TPC-H Q5
+
+
+select
+n_name, 
+sum(l_extendedprice * (1::numeric - l_discount)) as revenue
+from 
+plato."customer", 
+plato."orders", 
+plato."lineitem", 
+plato."supplier", 
+plato."nation", 
+plato."region"
+where 
+c_custkey = o_custkey
+and l_orderkey = o_orderkey
+and l_suppkey = s_suppkey
+and c_nationkey = s_nationkey
+and s_nationkey = n_nationkey
+and n_regionkey = r_regionkey
+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 
+revenue desc;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q06.sql b/yql/essentials/tests/sql/suites/pg-tpch/q06.sql
new file mode 100644
index 0000000000..4f6c0b039b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q06.sql
@@ -0,0 +1,13 @@
+--!syntax_pg
+--TPC-H Q6
+
+
+select
+sum(l_extendedprice*l_discount) as revenue
+from 
+plato."lineitem"
+where 
+l_shipdate >= date '1994-01-01'
+and l_shipdate < date '1994-01-01' + interval '1' year
+and l_discount between (0.06 - 0.01)::numeric and (0.06 + 0.01)::numeric
+and l_quantity < 24::numeric;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q07.sql b/yql/essentials/tests/sql/suites/pg-tpch/q07.sql
new file mode 100644
index 0000000000..8f1c30e994
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q07.sql
@@ -0,0 +1,41 @@
+--!syntax_pg
+--TPC-H Q7
+
+
+select
+supp_nation, 
+cust_nation, 
+l_year, sum(volume) as revenue
+from (
+select 
+n1.n_name as supp_nation, 
+n2.n_name as cust_nation, 
+extract(year from l_shipdate) as l_year,
+l_extendedprice * (1::numeric - l_discount) as volume
+from 
+plato."supplier", 
+plato."lineitem", 
+plato."orders", 
+plato."customer", 
+plato."nation" n1, 
+plato."nation" n2
+where 
+s_suppkey = l_suppkey
+and o_orderkey = l_orderkey
+and c_custkey = o_custkey
+and s_nationkey = n1.n_nationkey
+and c_nationkey = n2.n_nationkey
+and (
+(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
+group by 
+supp_nation, 
+cust_nation, 
+l_year
+order by 
+supp_nation, 
+cust_nation, 
+l_year;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q08.sql b/yql/essentials/tests/sql/suites/pg-tpch/q08.sql
new file mode 100644
index 0000000000..bd31d2dde4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q08.sql
@@ -0,0 +1,41 @@
+--!syntax_pg
+--TPC-H Q8
+
+
+select
+o_year, 
+sum(case 
+when nation = 'BRAZIL' 
+then volume
+else 0::numeric
+end) / (sum(volume) + 1e-12::numeric) as mkt_share
+from (
+select 
+extract(year from o_orderdate) as o_year,
+l_extendedprice * (1::numeric-l_discount) as volume, 
+n2.n_name as nation
+from 
+plato."part", 
+plato."supplier", 
+plato."lineitem", 
+plato."orders", 
+plato."customer", 
+plato."nation" n1, 
+plato."nation" n2, 
+plato."region"
+where 
+p_partkey = l_partkey
+and s_suppkey = l_suppkey
+and l_orderkey = o_orderkey
+and o_custkey = c_custkey
+and c_nationkey = n1.n_nationkey
+and n1.n_regionkey = r_regionkey
+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 ANODIZED STEEL' 
+) as all_nations
+group by 
+o_year
+order by 
+o_year;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q09.sql b/yql/essentials/tests/sql/suites/pg-tpch/q09.sql
new file mode 100644
index 0000000000..7db204c44f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q09.sql
@@ -0,0 +1,35 @@
+--!syntax_pg
+--TPC-H Q9
+
+
+select 
+nation, 
+o_year, 
+sum(amount) as sum_profit
+from (
+select 
+n_name as nation, 
+extract(year from o_orderdate) as o_year,
+l_extendedprice * (1::numeric - l_discount) - ps_supplycost * l_quantity as amount
+from 
+plato."part", 
+plato."supplier", 
+plato."lineitem", 
+plato."partsupp", 
+plato."orders", 
+plato."nation"
+where 
+s_suppkey = l_suppkey
+and ps_suppkey = l_suppkey
+and ps_partkey = l_partkey
+and p_partkey = l_partkey
+and o_orderkey = l_orderkey
+and s_nationkey = n_nationkey
+and p_name like '%green%'
+) as profit
+group by 
+nation, 
+o_year
+order by 
+nation, 
+o_year desc;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q10.sql b/yql/essentials/tests/sql/suites/pg-tpch/q10.sql
new file mode 100644
index 0000000000..e331135e3b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q10.sql
@@ -0,0 +1,36 @@
+--!syntax_pg
+--TPC-H Q10
+
+
+select
+c_custkey, 
+c_name, 
+sum(l_extendedprice * (1::numeric - l_discount)) as revenue,
+c_acctbal, 
+n_name, 
+c_address, 
+c_phone, 
+c_comment
+from 
+plato."customer", 
+plato."orders", 
+plato."lineitem", 
+plato."nation"
+where 
+c_custkey = o_custkey
+and l_orderkey = o_orderkey
+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 
+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/pg-tpch/q11.sql b/yql/essentials/tests/sql/suites/pg-tpch/q11.sql
new file mode 100644
index 0000000000..f61ddbb957
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q11.sql
@@ -0,0 +1,32 @@
+--!syntax_pg
+--TPC-H Q11
+-- ignore runonopt plan diff
+
+
+select
+ps_partkey, 
+sum(ps_supplycost * ps_availqty::numeric) as value
+from 
+plato."partsupp", 
+plato."supplier",
+plato."nation"
+where 
+ps_suppkey = s_suppkey
+and s_nationkey = n_nationkey
+and n_name = 'GERMANY'
+group by 
+ps_partkey having 
+sum(ps_supplycost * ps_availqty::numeric) > (
+select 
+sum(ps_supplycost * ps_availqty::numeric) * 0.0001::numeric
+from 
+plato."partsupp", 
+plato."supplier", 
+plato."nation"
+where 
+ps_suppkey = s_suppkey
+and s_nationkey = n_nationkey
+and n_name = 'GERMANY'
+)
+order by
+value desc;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q12.sql b/yql/essentials/tests/sql/suites/pg-tpch/q12.sql
new file mode 100644
index 0000000000..bf38bc5e83
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q12.sql
@@ -0,0 +1,32 @@
+--!syntax_pg
+--TPC-H Q12
+
+
+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 
+plato."orders", 
+plato."lineitem"
+where 
+o_orderkey = l_orderkey
+and l_shipmode in ('MAIL', 'SHIP')
+and l_commitdate < l_receiptdate
+and l_shipdate < l_commitdate
+and l_receiptdate >= date '1994-01-01'
+and l_receiptdate < date '1994-01-01' + interval '1' year
+group by 
+l_shipmode
+order by 
+l_shipmode;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q13.sql b/yql/essentials/tests/sql/suites/pg-tpch/q13.sql
new file mode 100644
index 0000000000..97848f0b9b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q13.sql
@@ -0,0 +1,25 @@
+--!syntax_pg
+--TPC-H Q13
+
+
+select 
+c_count, count(*) as custdist 
+from (
+select 
+c_custkey,
+count(o_orderkey) 
+from 
+plato."customer"
+left outer join 
+plato."orders"
+on 
+c_custkey = o_custkey
+and o_comment not like '%special%requests%'
+group by 
+c_custkey
+)as c_orders (c_custkey, c_count)
+group by 
+c_count
+order by 
+custdist desc, 
+c_count desc;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q14.sql b/yql/essentials/tests/sql/suites/pg-tpch/q14.sql
new file mode 100644
index 0000000000..e515a5d480
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q14.sql
@@ -0,0 +1,17 @@
+--!syntax_pg
+--TPC-H Q14
+
+
+select
+100.00::numeric * sum(case 
+when p_type like 'PROMO%'
+then l_extendedprice*(1::numeric-l_discount)
+else 0::numeric
+end) / (sum(l_extendedprice * (1::numeric - l_discount)) + 1e-12::numeric) as promo_revenue
+from 
+plato."lineitem", 
+plato."part"
+where
+l_partkey = p_partkey
+and l_shipdate >= date '1995-09-01'
+and l_shipdate < date '1995-09-01' + interval '1' month;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q15.sql b/yql/essentials/tests/sql/suites/pg-tpch/q15.sql
new file mode 100644
index 0000000000..729fd88641
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q15.sql
@@ -0,0 +1,36 @@
+--!syntax_pg
+--ignore runonopt plan diff
+--TPC-H Q15
+
+
+create view revenue_STREAM_ID (supplier_no, total_revenue) as
+select 
+l_suppkey, 
+sum(l_extendedprice * (1::numeric - l_discount))
+from 
+plato."lineitem"
+where 
+l_shipdate >= date '1996-01-01'
+and l_shipdate < date '1996-01-01' + interval '3' month
+group by 
+l_suppkey;
+select
+s_suppkey, 
+s_name, 
+s_address, 
+s_phone, 
+total_revenue
+from 
+plato."supplier", 
+revenue_STREAM_ID
+where 
+s_suppkey = supplier_no
+and total_revenue = (
+select 
+max(total_revenue)
+from 
+revenue_STREAM_ID
+)
+order by 
+s_suppkey;
+drop view revenue_STREAM_ID;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q16.sql b/yql/essentials/tests/sql/suites/pg-tpch/q16.sql
new file mode 100644
index 0000000000..7df4f54439
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q16.sql
@@ -0,0 +1,34 @@
+--!syntax_pg
+--TPC-H Q16
+
+
+select
+p_brand, 
+p_type, 
+p_size, 
+count(distinct ps_suppkey) as supplier_cnt
+from 
+plato."partsupp", 
+plato."part"
+where 
+p_partkey = ps_partkey
+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
+from 
+plato."supplier"
+where 
+s_comment like '%Customer%Complaints%'
+)
+group by 
+p_brand, 
+p_type, 
+p_size
+order by 
+supplier_cnt desc, 
+p_brand, 
+p_type, 
+p_size;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q17.sql b/yql/essentials/tests/sql/suites/pg-tpch/q17.sql
new file mode 100644
index 0000000000..e893138f09
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q17.sql
@@ -0,0 +1,22 @@
+--!syntax_pg
+--ignore runonopt plan diff
+--TPC-H Q17
+
+
+select
+sum(l_extendedprice) / 7.0::numeric as avg_yearly
+from 
+plato."lineitem", 
+plato."part"
+where 
+p_partkey = l_partkey
+and p_brand = 'Brand#23'
+and p_container = 'MED BOX'
+and l_quantity < (
+select
+0.2::numeric * avg(l_quantity)
+from 
+plato."lineitem"
+where 
+l_partkey = p_partkey
+);
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q18.sql b/yql/essentials/tests/sql/suites/pg-tpch/q18.sql
new file mode 100644
index 0000000000..0c32891237
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q18.sql
@@ -0,0 +1,38 @@
+--!syntax_pg
+--ignore runonopt plan diff
+--TPC-H Q18
+
+
+select 
+c_name,
+c_custkey, 
+o_orderkey,
+o_orderdate,
+o_totalprice,
+sum(l_quantity)
+from 
+plato."customer",
+plato."orders",
+plato."lineitem"
+where 
+o_orderkey in (
+select
+l_orderkey
+from
+plato."lineitem"
+group by 
+l_orderkey having 
+sum(l_quantity) > 300::numeric
+)
+and c_custkey = o_custkey
+and o_orderkey = l_orderkey
+group by 
+c_name, 
+c_custkey, 
+o_orderkey, 
+o_orderdate, 
+o_totalprice
+order by 
+o_totalprice desc,
+o_orderdate
+limit 100;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q19.sql b/yql/essentials/tests/sql/suites/pg-tpch/q19.sql
new file mode 100644
index 0000000000..5eb0799774
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q19.sql
@@ -0,0 +1,39 @@
+--!syntax_pg
+--TPC-H Q19
+
+
+select
+sum(l_extendedprice * (1::numeric - l_discount) ) as revenue
+from 
+plato."lineitem", 
+plato."part"
+where 
+(
+p_partkey = l_partkey
+and p_brand = 'Brand#12'
+and p_container in ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') 
+and l_quantity >= 1::numeric and l_quantity <= (1 + 10)::numeric
+and p_size between 1 and 5 
+and l_shipmode in ('AIR', 'AIR REG')
+and l_shipinstruct = 'DELIVER IN PERSON' 
+)
+or 
+(
+p_partkey = l_partkey
+and p_brand = 'Brand#23'
+and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
+and l_quantity >= 10::numeric and l_quantity <= (10 + 10)::numeric
+and p_size between 1 and 10
+and l_shipmode in ('AIR', 'AIR REG')
+and l_shipinstruct = 'DELIVER IN PERSON'
+)
+or 
+(
+p_partkey = l_partkey
+and p_brand = 'Brand#34'
+and p_container in ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
+and l_quantity >= 20::numeric and l_quantity <= (20 + 10)::numeric
+and p_size between 1 and 15
+and l_shipmode in ('AIR', 'AIR REG')
+and l_shipinstruct = 'DELIVER IN PERSON'
+);
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q20.sql b/yql/essentials/tests/sql/suites/pg-tpch/q20.sql
new file mode 100644
index 0000000000..fdb34b62f1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q20.sql
@@ -0,0 +1,41 @@
+--!syntax_pg
+--TPC-H Q20
+
+
+select 
+s_name, 
+s_address
+from 
+plato."supplier",
+plato."nation"
+where 
+s_suppkey in (
+select 
+ps_suppkey
+from 
+plato."partsupp"
+where 
+ps_partkey in (
+select 
+p_partkey
+from 
+plato."part"
+where 
+p_name like 'forest%'
+)
+and ps_availqty::numeric > (
+select 
+0.5::numeric * sum(l_quantity)
+from 
+plato."lineitem"
+where 
+l_partkey = ps_partkey
+and l_suppkey = ps_suppkey
+and l_shipdate >= '1994-01-01'::date
+and l_shipdate < '1994-01-01'::date + interval '1' year 
+)
+)
+and s_nationkey = n_nationkey
+and n_name = 'CANADA'
+order by 
+s_name;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q21.sql b/yql/essentials/tests/sql/suites/pg-tpch/q21.sql
new file mode 100644
index 0000000000..acd5bf4bb1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q21.sql
@@ -0,0 +1,44 @@
+--!syntax_pg
+--TPC-H Q21
+
+
+select 
+s_name, 
+count(*) as numwait
+from 
+plato."supplier", 
+plato."lineitem" l1, 
+plato."orders", 
+plato."nation"
+where 
+s_suppkey = l1.l_suppkey
+and o_orderkey = l1.l_orderkey
+and o_orderstatus = 'F'
+and l1.l_receiptdate > l1.l_commitdate
+and exists ( 
+select 
+*
+from 
+plato."lineitem" l2
+where 
+l2.l_orderkey = l1.l_orderkey
+and l2.l_suppkey <> l1.l_suppkey
+)
+and not exists ( 
+select 
+*
+from 
+plato."lineitem" l3
+where 
+l3.l_orderkey = l1.l_orderkey
+and l3.l_suppkey <> l1.l_suppkey
+and l3.l_receiptdate > l3.l_commitdate
+)
+and s_nationkey = n_nationkey
+and n_name = 'SAUDI ARABIA'
+group by 
+s_name
+order by 
+numwait desc, 
+s_name
+limit 100;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q22.sql b/yql/essentials/tests/sql/suites/pg-tpch/q22.sql
new file mode 100644
index 0000000000..80a89442ef
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q22.sql
@@ -0,0 +1,40 @@
+--!syntax_pg
+--TPC-H Q22
+
+
+select 
+cntrycode, 
+count(*) as numcust, 
+sum(c_acctbal) as totacctbal
+from (
+select 
+substring(c_phone from 1 for 2) as cntrycode, 
+c_acctbal
+from 
+plato."customer"
+where 
+substring(c_phone from 1 for 2) in 
+('13','31','23','29','30','18','17')
+and c_acctbal > (
+select 
+avg(c_acctbal)
+from 
+plato."customer"
+where 
+c_acctbal > 0.00::numeric
+and substring (c_phone from 1 for 2) in
+('13','31','23','29','30','18','17')
+)
+and not exists (
+select 
+* 
+from 
+plato."orders"
+where 
+o_custkey = c_custkey
+)
+) as custsale
+group by 
+cntrycode 
+order by 
+cntrycode;
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/region.txt b/yql/essentials/tests/sql/suites/pg-tpch/region.txt
new file mode 100644
index 0000000000..35b2e04fad
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/region.txt
@@ -0,0 +1 @@
+{"r_comment"="lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to ";"r_name"="AFRICA";"r_regionkey"=0;};
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/region.txt.attr b/yql/essentials/tests/sql/suites/pg-tpch/region.txt.attr
new file mode 100644
index 0000000000..3c92a439ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/region.txt.attr
@@ -0,0 +1,9 @@
+{
+    "_yql_row_spec"={
+       "Type"=["StructType";[
+           ["r_regionkey";["PgType";"int4";];];
+           ["r_name";["PgType";"text";];];
+           ["r_comment";["PgType";"text";];];
+       ];];
+    }
+}
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/supplier.txt b/yql/essentials/tests/sql/suites/pg-tpch/supplier.txt
new file mode 100644
index 0000000000..c5f0cbc1ad
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/supplier.txt
@@ -0,0 +1 @@
+{"s_acctbal"="\0\2\0\0\0\0\0\2\x16{$\xB8";"s_address"=" N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ";"s_comment"="each slyly above the careful";"s_name"="Supplier#000000001";"s_nationkey"=17;"s_phone"="27-918-335-1736";"s_suppkey"=1;};
diff --git a/yql/essentials/tests/sql/suites/pg-tpch/supplier.txt.attr b/yql/essentials/tests/sql/suites/pg-tpch/supplier.txt.attr
new file mode 100644
index 0000000000..610ac95a35
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/supplier.txt.attr
@@ -0,0 +1,13 @@
+{
+    "_yql_row_spec"={
+       "Type"=["StructType";[
+           ["s_suppkey";["PgType";"int4";];];
+           ["s_name";["PgType";"text";];];
+           ["s_address";["PgType";"text";];];
+           ["s_nationkey";["PgType";"int4";];];
+           ["s_phone";["PgType";"text";];];
+           ["s_acctbal";["PgType";"numeric";];];
+           ["s_comment";["PgType";"text";];];
+       ];];
+    }
+}