# Distributed nested-loop join not allowed.
select count(*) from
functional_parquet.alltypestiny a,
functional_parquet.alltypestiny b
---- PLAN
not implemented: MT_DOP not supported for plans with base table joins or table sinks.
---- PARALLELPLANS
not implemented: MT_DOP not supported for plans with base table joins or table sinks.
====
# Distributed hash-join not allowed.
select count(*) from
functional_parquet.alltypestiny a,
functional_parquet.alltypestiny b
where a.id = b.id
---- PLAN
not implemented: MT_DOP not supported for plans with base table joins or table sinks.
---- PARALLELPLANS
not implemented: MT_DOP not supported for plans with base table joins or table sinks.
====
# Insert not allowed.
insert into functional_parquet.alltypes partition(year,month)
select * from functional_parquet.alltypessmall
---- PLAN
not implemented: MT_DOP not supported for plans with base table joins or table sinks.
---- PARALLELPLANS
not implemented: MT_DOP not supported for plans with base table joins or table sinks.
====
# CTAS not allowed.
create table ctas_mt_dop_test as select * from functional_parquet.alltypes
---- PLAN
not implemented: MT_DOP not supported for plans with base table joins or table sinks.
---- PARALLELPLANS
not implemented: MT_DOP not supported for plans with base table joins or table sinks.
====
# Single-table scan/filter/agg/topn should work.
select count(int_col) cnt from functional_parquet.alltypes
where id < 10
group by bigint_col
order by cnt, bigint_col
limit 10
---- PLAN
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=144.00MB mem-reservation=34.00MB
PLAN-ROOT SINK
|  mem-estimate=0B mem-reservation=0B
|
02:TOP-N [LIMIT=10]
|  order by: count(int_col) ASC, bigint_col ASC
|  mem-estimate=160B mem-reservation=0B
|  tuple-ids=2 row-size=16B cardinality=10
|
01:AGGREGATE [FINALIZE]
|  output: count(int_col)
|  group by: bigint_col
|  mem-estimate=128.00MB mem-reservation=34.00MB spill-buffer=2.00MB
|  tuple-ids=1 row-size=16B cardinality=unavailable
|
00:SCAN HDFS [functional_parquet.alltypes]
   partitions=24/24 files=24 size=178.13KB
   predicates: id < 10
   stats-rows=unavailable extrapolated-rows=disabled
   table stats: rows=unavailable size=unavailable
   column stats: unavailable
   parquet statistics predicates: id < 10
   parquet dictionary predicates: id < 10
   mem-estimate=16.00MB mem-reservation=0B
   tuple-ids=0 row-size=16B cardinality=unavailable
---- PARALLELPLANS
F02:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=0B mem-reservation=0B
PLAN-ROOT SINK
|  mem-estimate=0B mem-reservation=0B
|
05:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: count(int_col) ASC, bigint_col ASC
|  limit: 10
|  mem-estimate=0B mem-reservation=0B
|  tuple-ids=2 row-size=16B cardinality=10
|
F01:PLAN FRAGMENT [HASH(bigint_col)] hosts=3 instances=9
Per-Host Resources: mem-estimate=384.00MB mem-reservation=102.00MB
02:TOP-N [LIMIT=10]
|  order by: count(int_col) ASC, bigint_col ASC
|  mem-estimate=160B mem-reservation=0B
|  tuple-ids=2 row-size=16B cardinality=10
|
04:AGGREGATE [FINALIZE]
|  output: count:merge(int_col)
|  group by: bigint_col
|  mem-estimate=128.00MB mem-reservation=34.00MB spill-buffer=2.00MB
|  tuple-ids=1 row-size=16B cardinality=unavailable
|
03:EXCHANGE [HASH(bigint_col)]
|  mem-estimate=0B mem-reservation=0B
|  tuple-ids=1 row-size=16B cardinality=unavailable
|
F00:PLAN FRAGMENT [RANDOM] hosts=3 instances=9
Per-Host Resources: mem-estimate=432.00MB mem-reservation=99.00MB
01:AGGREGATE [STREAMING]
|  output: count(int_col)
|  group by: bigint_col
|  mem-estimate=128.00MB mem-reservation=33.00MB spill-buffer=2.00MB
|  tuple-ids=1 row-size=16B cardinality=unavailable
|
00:SCAN HDFS [functional_parquet.alltypes, RANDOM]
   partitions=24/24 files=24 size=178.13KB
   predicates: id < 10
   stats-rows=unavailable extrapolated-rows=disabled
   table stats: rows=unavailable size=unavailable
   column stats: unavailable
   parquet statistics predicates: id < 10
   parquet dictionary predicates: id < 10
   mem-estimate=16.00MB mem-reservation=0B
   tuple-ids=0 row-size=16B cardinality=unavailable
====
# Single-table scan/filter/analytic should work.
select row_number() over(partition by int_col order by id)
from functional_parquet.alltypes
where id < 10
---- PLAN
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=26.00MB mem-reservation=10.00MB
PLAN-ROOT SINK
|  mem-estimate=0B mem-reservation=0B
|
02:ANALYTIC
|  functions: row_number()
|  partition by: int_col
|  order by: id ASC
|  window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB
|  tuple-ids=4,3 row-size=16B cardinality=unavailable
|
01:SORT
|  order by: int_col ASC NULLS FIRST, id ASC
|  mem-estimate=6.00MB mem-reservation=6.00MB spill-buffer=2.00MB
|  tuple-ids=4 row-size=8B cardinality=unavailable
|
00:SCAN HDFS [functional_parquet.alltypes]
   partitions=24/24 files=24 size=178.13KB
   predicates: id < 10
   stats-rows=unavailable extrapolated-rows=disabled
   table stats: rows=unavailable size=unavailable
   column stats: unavailable
   parquet statistics predicates: id < 10
   parquet dictionary predicates: id < 10
   mem-estimate=16.00MB mem-reservation=0B
   tuple-ids=0 row-size=8B cardinality=unavailable
---- PARALLELPLANS
F02:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=0B mem-reservation=0B
PLAN-ROOT SINK
|  mem-estimate=0B mem-reservation=0B
|
04:EXCHANGE [UNPARTITIONED]
|  mem-estimate=0B mem-reservation=0B
|  tuple-ids=4,3 row-size=16B cardinality=unavailable
|
F01:PLAN FRAGMENT [HASH(int_col)] hosts=3 instances=9
Per-Host Resources: mem-estimate=30.00MB mem-reservation=30.00MB
02:ANALYTIC
|  functions: row_number()
|  partition by: int_col
|  order by: id ASC
|  window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB
|  tuple-ids=4,3 row-size=16B cardinality=unavailable
|
01:SORT
|  order by: int_col ASC NULLS FIRST, id ASC
|  mem-estimate=6.00MB mem-reservation=6.00MB spill-buffer=2.00MB
|  tuple-ids=4 row-size=8B cardinality=unavailable
|
03:EXCHANGE [HASH(int_col)]
|  mem-estimate=0B mem-reservation=0B
|  tuple-ids=0 row-size=8B cardinality=unavailable
|
F00:PLAN FRAGMENT [RANDOM] hosts=3 instances=9
Per-Host Resources: mem-estimate=48.00MB mem-reservation=0B
00:SCAN HDFS [functional_parquet.alltypes, RANDOM]
   partitions=24/24 files=24 size=178.13KB
   predicates: id < 10
   stats-rows=unavailable extrapolated-rows=disabled
   table stats: rows=unavailable size=unavailable
   column stats: unavailable
   parquet statistics predicates: id < 10
   parquet dictionary predicates: id < 10
   mem-estimate=16.00MB mem-reservation=0B
   tuple-ids=0 row-size=8B cardinality=unavailable
====
# Nested-loop join in a subplan should work.
select *
from tpch_nested_parquet.customer c, c.c_orders o, o.o_lineitems
where c_custkey < 10 and o_orderkey < 5 and l_linenumber < 3
---- PLAN
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=88.00MB mem-reservation=0B
PLAN-ROOT SINK
|  mem-estimate=0B mem-reservation=0B
|
01:SUBPLAN
|  mem-estimate=0B mem-reservation=0B
|  tuple-ids=2,1,0 row-size=562B cardinality=1500000
|
|--08:NESTED LOOP JOIN [CROSS JOIN]
|  |  mem-estimate=254B mem-reservation=0B
|  |  tuple-ids=2,1,0 row-size=562B cardinality=100
|  |
|  |--02:SINGULAR ROW SRC
|  |     parent-subplan=01
|  |     mem-estimate=0B mem-reservation=0B
|  |     tuple-ids=0 row-size=254B cardinality=1
|  |
|  04:SUBPLAN
|  |  mem-estimate=0B mem-reservation=0B
|  |  tuple-ids=2,1 row-size=308B cardinality=100
|  |
|  |--07:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  mem-estimate=124B mem-reservation=0B
|  |  |  tuple-ids=2,1 row-size=308B cardinality=10
|  |  |
|  |  |--05:SINGULAR ROW SRC
|  |  |     parent-subplan=04
|  |  |     mem-estimate=0B mem-reservation=0B
|  |  |     tuple-ids=1 row-size=124B cardinality=1
|  |  |
|  |  06:UNNEST [o.o_lineitems]
|  |     parent-subplan=04
|  |     mem-estimate=0B mem-reservation=0B
|  |     tuple-ids=2 row-size=0B cardinality=10
|  |
|  03:UNNEST [c.c_orders o]
|     parent-subplan=01
|     mem-estimate=0B mem-reservation=0B
|     tuple-ids=1 row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   partitions=1/1 files=4 size=292.35MB
   predicates: c_custkey < 10, !empty(c.c_orders)
   predicates on o: !empty(o.o_lineitems), o_orderkey < 5
   predicates on o_lineitems: l_linenumber < 3
   stats-rows=150000 extrapolated-rows=disabled
   table stats: rows=150000 size=292.35MB
   columns missing stats: c_orders
   parquet statistics predicates: c_custkey < 10
   parquet dictionary predicates: c_custkey < 10
   mem-estimate=88.00MB mem-reservation=0B
   tuple-ids=0 row-size=254B cardinality=15000
---- PARALLELPLANS
F01:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=0B mem-reservation=0B
PLAN-ROOT SINK
|  mem-estimate=0B mem-reservation=0B
|
09:EXCHANGE [UNPARTITIONED]
|  mem-estimate=0B mem-reservation=0B
|  tuple-ids=2,1,0 row-size=562B cardinality=1500000
|
F00:PLAN FRAGMENT [RANDOM] hosts=3 instances=9
Per-Host Resources: mem-estimate=264.00MB mem-reservation=0B
01:SUBPLAN
|  mem-estimate=0B mem-reservation=0B
|  tuple-ids=2,1,0 row-size=562B cardinality=1500000
|
|--08:NESTED LOOP JOIN [CROSS JOIN]
|  |  mem-estimate=254B mem-reservation=0B
|  |  tuple-ids=2,1,0 row-size=562B cardinality=100
|  |
|  |--02:SINGULAR ROW SRC
|  |     parent-subplan=01
|  |     mem-estimate=0B mem-reservation=0B
|  |     tuple-ids=0 row-size=254B cardinality=1
|  |
|  04:SUBPLAN
|  |  mem-estimate=0B mem-reservation=0B
|  |  tuple-ids=2,1 row-size=308B cardinality=100
|  |
|  |--07:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  mem-estimate=124B mem-reservation=0B
|  |  |  tuple-ids=2,1 row-size=308B cardinality=10
|  |  |
|  |  |--05:SINGULAR ROW SRC
|  |  |     parent-subplan=04
|  |  |     mem-estimate=0B mem-reservation=0B
|  |  |     tuple-ids=1 row-size=124B cardinality=1
|  |  |
|  |  06:UNNEST [o.o_lineitems]
|  |     parent-subplan=04
|  |     mem-estimate=0B mem-reservation=0B
|  |     tuple-ids=2 row-size=0B cardinality=10
|  |
|  03:UNNEST [c.c_orders o]
|     parent-subplan=01
|     mem-estimate=0B mem-reservation=0B
|     tuple-ids=1 row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c, RANDOM]
   partitions=1/1 files=4 size=292.35MB
   predicates: c_custkey < 10, !empty(c.c_orders)
   predicates on o: !empty(o.o_lineitems), o_orderkey < 5
   predicates on o_lineitems: l_linenumber < 3
   stats-rows=150000 extrapolated-rows=disabled
   table stats: rows=150000 size=292.35MB
   columns missing stats: c_orders
   parquet statistics predicates: c_custkey < 10
   parquet dictionary predicates: c_custkey < 10
   mem-estimate=88.00MB mem-reservation=0B
   tuple-ids=0 row-size=254B cardinality=15000
====
# Hash-join in a subplan should work.
select c.*
from tpch_nested_parquet.customer c, c.c_orders o1, c.c_orders o2
where o1.o_orderkey = o2.o_orderkey + 2 and o1.o_orderkey < 5
---- PLAN
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=89.94MB mem-reservation=1.94MB
PLAN-ROOT SINK
|  mem-estimate=0B mem-reservation=0B
|
01:SUBPLAN
|  mem-estimate=0B mem-reservation=0B
|  tuple-ids=1,0,2 row-size=286B cardinality=1500000
|
|--06:HASH JOIN [INNER JOIN]
|  |  hash predicates: o1.o_orderkey = o2.o_orderkey + 2
|  |  fk/pk conjuncts: assumed fk/pk
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB
|  |  tuple-ids=1,0,2 row-size=286B cardinality=10
|  |
|  |--04:UNNEST [c.c_orders o2]
|  |     parent-subplan=01
|  |     mem-estimate=0B mem-reservation=0B
|  |     tuple-ids=2 row-size=0B cardinality=10
|  |
|  05:NESTED LOOP JOIN [CROSS JOIN]
|  |  mem-estimate=270B mem-reservation=0B
|  |  tuple-ids=1,0 row-size=278B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     parent-subplan=01
|  |     mem-estimate=0B mem-reservation=0B
|  |     tuple-ids=0 row-size=270B cardinality=1
|  |
|  03:UNNEST [c.c_orders o1]
|     parent-subplan=01
|     mem-estimate=0B mem-reservation=0B
|     tuple-ids=1 row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   partitions=1/1 files=4 size=292.35MB
   predicates: !empty(c.c_orders), !empty(c.c_orders)
   predicates on o1: o1.o_orderkey < 5
   stats-rows=150000 extrapolated-rows=disabled
   table stats: rows=150000 size=292.35MB
   columns missing stats: c_orders, c_orders
   mem-estimate=88.00MB mem-reservation=0B
   tuple-ids=0 row-size=270B cardinality=150000
---- PARALLELPLANS
F01:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=0B mem-reservation=0B
PLAN-ROOT SINK
|  mem-estimate=0B mem-reservation=0B
|
07:EXCHANGE [UNPARTITIONED]
|  mem-estimate=0B mem-reservation=0B
|  tuple-ids=1,0,2 row-size=286B cardinality=1500000
|
F00:PLAN FRAGMENT [RANDOM] hosts=3 instances=9
Per-Host Resources: mem-estimate=269.81MB mem-reservation=5.81MB
01:SUBPLAN
|  mem-estimate=0B mem-reservation=0B
|  tuple-ids=1,0,2 row-size=286B cardinality=1500000
|
|--06:HASH JOIN [INNER JOIN]
|  |  hash predicates: o1.o_orderkey = o2.o_orderkey + 2
|  |  fk/pk conjuncts: assumed fk/pk
|  |  mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB
|  |  tuple-ids=1,0,2 row-size=286B cardinality=10
|  |
|  |--04:UNNEST [c.c_orders o2]
|  |     parent-subplan=01
|  |     mem-estimate=0B mem-reservation=0B
|  |     tuple-ids=2 row-size=0B cardinality=10
|  |
|  05:NESTED LOOP JOIN [CROSS JOIN]
|  |  mem-estimate=270B mem-reservation=0B
|  |  tuple-ids=1,0 row-size=278B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     parent-subplan=01
|  |     mem-estimate=0B mem-reservation=0B
|  |     tuple-ids=0 row-size=270B cardinality=1
|  |
|  03:UNNEST [c.c_orders o1]
|     parent-subplan=01
|     mem-estimate=0B mem-reservation=0B
|     tuple-ids=1 row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c, RANDOM]
   partitions=1/1 files=4 size=292.35MB
   predicates: !empty(c.c_orders), !empty(c.c_orders)
   predicates on o1: o1.o_orderkey < 5
   stats-rows=150000 extrapolated-rows=disabled
   table stats: rows=150000 size=292.35MB
   columns missing stats: c_orders, c_orders
   mem-estimate=88.00MB mem-reservation=0B
   tuple-ids=0 row-size=270B cardinality=150000
====