| # Test HDFS scan node. |
| select 1 |
| from tpch_nested_parquet.customer c, c.c_orders o, o.o_lineitems |
| where 5 + 5 < c_custkey and o_orderkey = (2 + 2) |
| and (coalesce(2, 3, 4) * 10) + l_linenumber < (0 * 1) |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=176.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=52B cardinality=1500000 |
| | |
| |--08:NESTED LOOP JOIN [CROSS JOIN] |
| | | mem-estimate=24B mem-reservation=0B |
| | | tuple-ids=2,1,0 row-size=52B cardinality=100 |
| | | |
| | |--02:SINGULAR ROW SRC |
| | | parent-subplan=01 |
| | | mem-estimate=0B mem-reservation=0B |
| | | tuple-ids=0 row-size=24B cardinality=1 |
| | | |
| | 04:SUBPLAN |
| | | mem-estimate=0B mem-reservation=0B |
| | | tuple-ids=2,1 row-size=28B cardinality=100 |
| | | |
| | |--07:NESTED LOOP JOIN [CROSS JOIN] |
| | | | mem-estimate=24B mem-reservation=0B |
| | | | tuple-ids=2,1 row-size=28B cardinality=10 |
| | | | |
| | | |--05:SINGULAR ROW SRC |
| | | | parent-subplan=04 |
| | | | mem-estimate=0B mem-reservation=0B |
| | | | tuple-ids=1 row-size=24B 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 = 4 |
| predicates on o_lineitems: 20 + l_linenumber < 0 |
| 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=176.00MB mem-reservation=0B |
| tuple-ids=0 row-size=24B cardinality=15000 |
| ==== |
| # Test HBase scan node. |
| select * from functional_hbase.stringids |
| where string_col = cast(4 as string) and 2 + 3 = tinyint_col |
| and id between concat('1', '0') and upper('20') |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=1.00GB mem-reservation=0B |
| PLAN-ROOT SINK |
| | mem-estimate=0B mem-reservation=0B |
| | |
| 00:SCAN HBASE [functional_hbase.stringids] |
| start key: 10 |
| stop key: 20\0 |
| hbase filters: d:string_col EQUAL '4' |
| predicates: tinyint_col = 5, string_col = '4' |
| table stats: rows=10000 |
| column stats: all |
| mem-estimate=1.00GB mem-reservation=0B |
| tuple-ids=0 row-size=119B cardinality=1 |
| ==== |
| # Test datasource scan node. |
| select * from functional.alltypes_datasource |
| where tinyint_col < (pow(2, 8)) and float_col != 0 and 1 + 1 > int_col |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=1.00GB mem-reservation=0B |
| PLAN-ROOT SINK |
| | mem-estimate=0B mem-reservation=0B |
| | |
| 00:SCAN DATA SOURCE [functional.alltypes_datasource] |
| data source predicates: tinyint_col < 256, int_col < 2 |
| predicates: float_col != 0 |
| mem-estimate=1.00GB mem-reservation=0B |
| tuple-ids=0 row-size=116B cardinality=500 |
| ==== |
| # Test aggregation. |
| select sum(1 + 1 + id) sm |
| from functional.alltypes |
| group by timestamp_col = cast('2015-11-15' as timestamp) + interval 1 year |
| having 1024 * 1024 * count(*) % 2 = 0 |
| and (sm > 1 or sm > 1) |
| and (sm between 5 and 10) |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=138.00MB mem-reservation=1.94MB |
| PLAN-ROOT SINK |
| | mem-estimate=0B mem-reservation=0B |
| | |
| 01:AGGREGATE [FINALIZE] |
| | output: sum(2 + id), count(*) |
| | group by: timestamp_col = TIMESTAMP '2016-11-15 00:00:00' |
| | having: sum(2 + id) <= 10, sum(2 + id) > 1, sum(2 + id) >= 5, 1048576 * count(*) % 2 = 0 |
| | mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB |
| | tuple-ids=1 row-size=17B cardinality=0 |
| | |
| 00:SCAN HDFS [functional.alltypes] |
| partitions=24/24 files=24 size=478.45KB |
| stats-rows=7300 extrapolated-rows=disabled |
| table stats: rows=7300 size=478.45KB |
| column stats: all |
| mem-estimate=128.00MB mem-reservation=0B |
| tuple-ids=0 row-size=20B cardinality=7300 |
| ==== |
| # Test hash join. |
| select 1 from functional.alltypes a |
| left outer join functional.alltypes b |
| on (1 + 1 + a.id = b.id - (1 + 1) and |
| a.int_col between 0 + 0 + 0 + b.bigint_col and b.bigint_col + ascii('a')) |
| where round(1.11 + 2.22 + 3.33 + 4.44, 1) < cast(b.double_col as decimal(3, 2)) |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=257.94MB mem-reservation=1.94MB |
| PLAN-ROOT SINK |
| | mem-estimate=0B mem-reservation=0B |
| | |
| 02:HASH JOIN [LEFT OUTER JOIN] |
| | hash predicates: 2 + a.id = b.id - 2 |
| | fk/pk conjuncts: assumed fk/pk |
| | other join predicates: a.int_col <= b.bigint_col + 97, a.int_col >= 0 + b.bigint_col |
| | other predicates: CAST(b.double_col AS DECIMAL(3,2)) > 11.1 |
| | mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB |
| | tuple-ids=0,1N row-size=28B cardinality=7300 |
| | |
| |--01:SCAN HDFS [functional.alltypes b] |
| | partitions=24/24 files=24 size=478.45KB |
| | predicates: CAST(b.double_col AS DECIMAL(3,2)) > 11.1 |
| | stats-rows=7300 extrapolated-rows=disabled |
| | table stats: rows=7300 size=478.45KB |
| | column stats: all |
| | parquet dictionary predicates: CAST(b.double_col AS DECIMAL(3,2)) > 11.1 |
| | mem-estimate=128.00MB mem-reservation=0B |
| | tuple-ids=1 row-size=20B cardinality=730 |
| | |
| 00:SCAN HDFS [functional.alltypes a] |
| partitions=24/24 files=24 size=478.45KB |
| stats-rows=7300 extrapolated-rows=disabled |
| table stats: rows=7300 size=478.45KB |
| column stats: all |
| mem-estimate=128.00MB mem-reservation=0B |
| tuple-ids=0 row-size=8B cardinality=7300 |
| ==== |
| # Test nested-loop join. Same as above but and with a disjunction in the On clause. |
| # The Where-clause predicate has the lhs/rhs flipped. |
| select 1 from functional.alltypes a |
| left outer join functional.alltypes b |
| on (1 + 1 + a.id = b.id - (1 + 1) or |
| a.int_col between 0 + 0 + 0 + b.bigint_col and b.bigint_col + ascii('a')) |
| where cast(b.double_col as decimal(3, 2)) > round(1.11 + 2.22 + 3.33 + 4.44, 1) |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=256.01MB mem-reservation=0B |
| PLAN-ROOT SINK |
| | mem-estimate=0B mem-reservation=0B |
| | |
| 02:NESTED LOOP JOIN [LEFT OUTER JOIN] |
| | join predicates: (2 + a.id = b.id - 2 OR a.int_col >= 0 + b.bigint_col AND a.int_col <= b.bigint_col + 97) |
| | predicates: CAST(b.double_col AS DECIMAL(3,2)) > 11.1 |
| | mem-estimate=14.26KB mem-reservation=0B |
| | tuple-ids=0,1N row-size=28B cardinality=7300 |
| | |
| |--01:SCAN HDFS [functional.alltypes b] |
| | partitions=24/24 files=24 size=478.45KB |
| | predicates: CAST(b.double_col AS DECIMAL(3,2)) > 11.1 |
| | stats-rows=7300 extrapolated-rows=disabled |
| | table stats: rows=7300 size=478.45KB |
| | column stats: all |
| | parquet dictionary predicates: CAST(b.double_col AS DECIMAL(3,2)) > 11.1 |
| | mem-estimate=128.00MB mem-reservation=0B |
| | tuple-ids=1 row-size=20B cardinality=730 |
| | |
| 00:SCAN HDFS [functional.alltypes a] |
| partitions=24/24 files=24 size=478.45KB |
| stats-rows=7300 extrapolated-rows=disabled |
| table stats: rows=7300 size=478.45KB |
| column stats: all |
| mem-estimate=128.00MB mem-reservation=0B |
| tuple-ids=0 row-size=8B cardinality=7300 |
| ==== |
| # Test distinct aggregation with grouping. |
| select sum(distinct 1 + 1 + id) |
| from functional.alltypes |
| group by timestamp_col = cast('2015-11-15' as timestamp) + interval 1 year |
| having 1024 * 1024 * count(*) % 2 = 0 |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=138.00MB mem-reservation=3.88MB |
| PLAN-ROOT SINK |
| | mem-estimate=0B mem-reservation=0B |
| | |
| 02:AGGREGATE [FINALIZE] |
| | output: sum(2 + id), count:merge(*) |
| | group by: timestamp_col = TIMESTAMP '2016-11-15 00:00:00' |
| | having: 1048576 * count(*) % 2 = 0 |
| | mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB |
| | tuple-ids=2 row-size=17B cardinality=0 |
| | |
| 01:AGGREGATE |
| | output: count(*) |
| | group by: timestamp_col = TIMESTAMP '2016-11-15 00:00:00', 2 + id |
| | mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB |
| | tuple-ids=1 row-size=17B cardinality=7300 |
| | |
| 00:SCAN HDFS [functional.alltypes] |
| partitions=24/24 files=24 size=478.45KB |
| stats-rows=7300 extrapolated-rows=disabled |
| table stats: rows=7300 size=478.45KB |
| column stats: all |
| mem-estimate=128.00MB mem-reservation=0B |
| tuple-ids=0 row-size=20B cardinality=7300 |
| ==== |
| # Test non-grouping distinct aggregation. |
| select sum(distinct 1 + 1 + id) |
| from functional.alltypes |
| having 1024 * 1024 * count(*) % 2 = 0 |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=138.00MB mem-reservation=1.94MB |
| PLAN-ROOT SINK |
| | mem-estimate=0B mem-reservation=0B |
| | |
| 02:AGGREGATE [FINALIZE] |
| | output: sum(2 + id), count:merge(*) |
| | having: 1048576 * zeroifnull(count(*)) % 2 = 0 |
| | mem-estimate=10.00MB mem-reservation=0B spill-buffer=2.00MB |
| | tuple-ids=2 row-size=16B cardinality=0 |
| | |
| 01:AGGREGATE |
| | output: count(*) |
| | group by: 2 + id |
| | mem-estimate=10.00MB mem-reservation=1.94MB spill-buffer=64.00KB |
| | tuple-ids=1 row-size=16B cardinality=7300 |
| | |
| 00:SCAN HDFS [functional.alltypes] |
| partitions=24/24 files=24 size=478.45KB |
| stats-rows=7300 extrapolated-rows=disabled |
| table stats: rows=7300 size=478.45KB |
| column stats: all |
| mem-estimate=128.00MB mem-reservation=0B |
| tuple-ids=0 row-size=4B cardinality=7300 |
| ==== |
| # Test analytic eval node. |
| select first_value(1 + 1 + int_col - (1 - 1)) over |
| (partition by concat(concat('a', 'b'), string_col) |
| order by greatest(greatest(10, 20), bigint_col)) |
| from functional.alltypes |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=144.00MB mem-reservation=16.00MB |
| PLAN-ROOT SINK |
| | mem-estimate=0B mem-reservation=0B |
| | |
| 02:ANALYTIC |
| | functions: first_value(2 + int_col - 0) |
| | partition by: concat('ab', string_col) |
| | order by: greatest(20, bigint_col) ASC |
| | window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
| | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB |
| | tuple-ids=3,2 row-size=61B cardinality=7300 |
| | |
| 01:SORT |
| | order by: concat('ab', string_col) ASC NULLS FIRST, greatest(20, bigint_col) ASC |
| | materialized: concat('ab', string_col), greatest(20, bigint_col) |
| | mem-estimate=12.00MB mem-reservation=12.00MB spill-buffer=2.00MB |
| | tuple-ids=3 row-size=53B cardinality=7300 |
| | |
| 00:SCAN HDFS [functional.alltypes] |
| partitions=24/24 files=24 size=478.45KB |
| stats-rows=7300 extrapolated-rows=disabled |
| table stats: rows=7300 size=478.45KB |
| column stats: all |
| mem-estimate=128.00MB mem-reservation=0B |
| tuple-ids=0 row-size=29B cardinality=7300 |
| ==== |
| # Test sort node. |
| select int_col from functional.alltypes |
| order by id * abs((factorial(5) / power(2, 4))) |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=134.00MB mem-reservation=6.00MB |
| PLAN-ROOT SINK |
| | mem-estimate=0B mem-reservation=0B |
| | |
| 01:SORT |
| | order by: id * 7.5 ASC |
| | mem-estimate=6.00MB mem-reservation=6.00MB spill-buffer=2.00MB |
| | tuple-ids=1 row-size=8B cardinality=7300 |
| | |
| 00:SCAN HDFS [functional.alltypes] |
| partitions=24/24 files=24 size=478.45KB |
| stats-rows=7300 extrapolated-rows=disabled |
| table stats: rows=7300 size=478.45KB |
| column stats: all |
| mem-estimate=128.00MB mem-reservation=0B |
| tuple-ids=0 row-size=8B cardinality=7300 |
| ==== |
| # Test HDFS table sink. |
| insert into functional.alltypes (id, int_col) partition(year,month) |
| select id, int_col, cast(1 + 1 + 1 + year as int), cast(month - (1 - 1 - 1) as int) |
| from functional.alltypessmall |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=32.00MB mem-reservation=0B |
| WRITE TO HDFS [functional.alltypes, OVERWRITE=false, PARTITION-KEYS=(CAST(3 + year AS INT),CAST(month - -1 AS INT))] |
| | partitions=4 |
| | mem-estimate=1.56KB mem-reservation=0B |
| | |
| 00:SCAN HDFS [functional.alltypessmall] |
| partitions=4/4 files=4 size=6.32KB |
| stats-rows=100 extrapolated-rows=disabled |
| table stats: rows=100 size=6.32KB |
| column stats: all |
| mem-estimate=32.00MB mem-reservation=0B |
| tuple-ids=0 row-size=16B cardinality=100 |
| ==== |
| # Constant folding does not work across query blocks. |
| select sum(id + c3) from |
| (select id, 10 + c2 as c3 from |
| (select id, 20 + c1 as c2 from |
| (select id, 30 as c1 from functional.alltypes limit 2) v1 |
| ) v2 |
| ) v3 |
| ---- PLAN |
| F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 |
| | Per-Host Resources: mem-estimate=138.00MB mem-reservation=0B |
| PLAN-ROOT SINK |
| | mem-estimate=0B mem-reservation=0B |
| | |
| 01:AGGREGATE [FINALIZE] |
| | output: sum(id + 10 + 20 + 30) |
| | mem-estimate=10.00MB mem-reservation=0B spill-buffer=2.00MB |
| | tuple-ids=4 row-size=8B cardinality=1 |
| | |
| 00:SCAN HDFS [functional.alltypes] |
| partitions=24/24 files=24 size=478.45KB |
| stats-rows=7300 extrapolated-rows=disabled |
| table stats: rows=7300 size=478.45KB |
| column stats: all |
| limit: 2 |
| mem-estimate=128.00MB mem-reservation=0B |
| tuple-ids=0 row-size=4B cardinality=2 |
| ==== |
