[
https://issues.apache.org/jira/browse/ASTERIXDB-1332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15180624#comment-15180624
]
Yingyi Bu commented on ASTERIXDB-1332:
--------------------------------------
There is one potentially optimization that can be done in
InlineSubplanInputForNestedTupleSourceRule (for the general case):
to directly change NTS to ETS if the out variables are not referred in the path
from the subplan root to the NTS to be inlined.
The following plans show how the current rule rewrites subplans.
Before plan:
{noformat}
distribute result [%0->$$94] -- |UNPARTITIONED|
project ([$$94]) -- |UNPARTITIONED|
assign [$$94] <- [function-call: asterix:open-record-constructor,
Args:[AString: {nation}, %0->$$13, AString: {o_year}, %0->$$14, AString:
{sum_profit}, %0->$$109]] -- |UNPARTITIONED|
order (ASC, %0->$$13) (DESC, %0->$$14) -- |UNPARTITIONED|
group by ([$$13 := %0->$$98; $$14 := %0->$$99]) decor ([]) {
aggregate [$$109] <- [function-call: asterix:agg-sum,
Args:[%0->$$96]] -- |UNPARTITIONED|
assign [$$96] <- [function-call:
asterix:field-access-by-name, Args:[%0->$$88, AString: {amount}]] --
|UNPARTITIONED|
nested tuple source -- |UNPARTITIONED|
} -- |UNPARTITIONED|
assign [$$99] <- [function-call: asterix:field-access-by-name,
Args:[%0->$$88, AString: {o_year}]] -- |UNPARTITIONED|
assign [$$98] <- [function-call: asterix:field-access-by-name,
Args:[%0->$$88, AString: {nation}]] -- |UNPARTITIONED|
assign [$$88] <- [function-call: asterix:open-record-constructor,
Args:[AString: {nation}, function-call: asterix:field-access-by-name,
Args:[%0->$$9, AString: {n_name}], AString: {o_year}, function-call:
asterix:get-year, Args:[function-call: asterix:field-access-by-name,
Args:[%0->$$0, AString: {o_orderdate}]], AString: {amount}, function-call:
asterix:numeric-subtract, Args:[function-call: asterix:numeric-multiply,
Args:[function-call: asterix:field-access-by-name, Args:[%0->$$9, AString:
{l_extendedprice}], function-call: asterix:numeric-subtract, Args:[AInt64: {1},
function-call: asterix:field-access-by-name, Args:[%0->$$9, AString:
{l_discount}]]], function-call: asterix:numeric-multiply, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$9, AString: {ps_supplycost}],
function-call: asterix:field-access-by-name, Args:[%0->$$9, AString:
{l_quantity}]]]]] -- |UNPARTITIONED|
select (function-call: algebricks:eq, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$0, AString: {o_orderkey}],
function-call: asterix:field-access-by-name, Args:[%0->$$9, AString:
{l_orderkey}]]) -- |UNPARTITIONED|
unnest $$9 <- function-call: asterix:scan-collection,
Args:[%0->$$74] -- |UNPARTITIONED|
subplan {
aggregate [$$74] <- [function-call:
asterix:listify, Args:[%0->$$67]] -- |UNPARTITIONED|
assign [$$67] <- [function-call:
asterix:open-record-constructor, Args:[AString: {l_extendedprice},
function-call: asterix:field-access-by-name, Args:[%0->$$8, AString:
{l_extendedprice}], AString: {l_discount}, function-call:
asterix:field-access-by-name, Args:[%0->$$8, AString: {l_discount}], AString:
{l_quantity}, function-call: asterix:field-access-by-name, Args:[%0->$$8,
AString: {l_quantity}], AString: {l_orderkey}, %0->$$100, AString: {n_name},
function-call: asterix:field-access-by-name, Args:[%0->$$8, AString: {n_name}],
AString: {ps_supplycost}, function-call: asterix:field-access-by-name,
Args:[%0->$$8, AString: {ps_supplycost}]]] -- |UNPARTITIONED|
select (function-call: algebricks:eq,
Args:[function-call: asterix:field-access-by-name, Args:[%0->$$1, AString:
{p_partkey}], function-call: asterix:field-access-by-name, Args:[%0->$$8,
AString: {l_partkey}]]) -- |UNPARTITIONED|
select (function-call: algebricks:eq,
Args:[%0->$$100, function-call: asterix:field-access-by-name, Args:[%0->$$0,
AString: {l_orderkey}]]) -- |UNPARTITIONED|
assign [$$100] <- [function-call:
asterix:field-access-by-name, Args:[%0->$$8, AString: {l_orderkey}]] --
|UNPARTITIONED|
unnest $$8 <- function-call:
asterix:scan-collection, Args:[%0->$$57] -- |UNPARTITIONED|
subplan {
aggregate [$$57] <-
[function-call: asterix:listify, Args:[%0->$$49]] -- |UNPARTITIONED|
assign [$$49] <-
[function-call: asterix:open-record-constructor, Args:[AString:
{l_extendedprice}, function-call: asterix:field-access-by-name, Args:[%0->$$7,
AString: {l_extendedprice}], AString: {l_discount}, function-call:
asterix:field-access-by-name, Args:[%0->$$7, AString: {l_discount}], AString:
{l_quantity}, function-call: asterix:field-access-by-name, Args:[%0->$$7,
AString: {l_quantity}], AString: {l_partkey}, %0->$$107, AString: {l_orderkey},
function-call: asterix:field-access-by-name, Args:[%0->$$7, AString:
{l_orderkey}], AString: {n_name}, function-call: asterix:field-access-by-name,
Args:[%0->$$7, AString: {n_name}], AString: {ps_supplycost}, function-call:
asterix:field-access-by-name, Args:[%0->$$2, AString: {ps_supplycost}]]] --
|UNPARTITIONED|
select (function-call:
algebricks:eq, Args:[function-call: asterix:field-access-by-name,
Args:[%0->$$2, AString: {ps_suppkey}], function-call:
asterix:field-access-by-name, Args:[%0->$$7, AString: {l_suppkey}]]) --
|UNPARTITIONED|
select
(function-call: algebricks:eq, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$2, AString: {ps_partkey}],
%0->$$107]) -- |UNPARTITIONED|
assign [$$107] <-
[function-call: asterix:field-access-by-name, Args:[%0->$$7, AString:
{l_partkey}]] -- |UNPARTITIONED|
unnest $$7 <-
function-call: asterix:scan-collection, Args:[%0->$$41] -- |UNPARTITIONED|
subplan {
aggregate [$$41] <- [function-call: asterix:listify, Args:[%0->$$33]] --
|UNPARTITIONED|
assign [$$33] <- [function-call: asterix:open-record-constructor,
Args:[AString: {l_suppkey}, %0->$$108, AString: {l_extendedprice},
function-call: asterix:field-access-by-name, Args:[%0->$$6, AString:
{l_extendedprice}], AString: {l_discount}, function-call:
asterix:field-access-by-name, Args:[%0->$$6, AString: {l_discount}], AString:
{l_quantity}, function-call: asterix:field-access-by-name, Args:[%0->$$6,
AString: {l_quantity}], AString: {l_partkey}, function-call:
asterix:field-access-by-name, Args:[%0->$$6, AString: {l_partkey}], AString:
{l_orderkey}, function-call: asterix:field-access-by-name, Args:[%0->$$0,
AString: {o_orderkey}], AString: {n_name}, function-call:
asterix:field-access-by-name, Args:[%0->$$5, AString: {n_name}]]] --
|UNPARTITIONED|
select (function-call: algebricks:eq, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$5, AString: {s_suppkey}], %0->$$108])
-- |UNPARTITIONED|
assign [$$108] <- [function-call: asterix:field-access-by-name, Args:[%0->$$6,
AString: {l_suppkey}]] -- |UNPARTITIONED|
unnest $$6 <- function-call: asterix:scan-collection, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$0, AString: {o_lineitems}]] --
|UNPARTITIONED|
unnest $$5 <- function-call: asterix:scan-collection, Args:[%0->$$28] --
|UNPARTITIONED|
join (TRUE) -- |UNPARTITIONED|
nested tuple source -- |UNPARTITIONED|
aggregate [$$28] <- [function-call: asterix:listify, Args:[%0->$$25]]
-- |UNPARTITIONED|
join (TRUE) -- |UNPARTITIONED|
empty-tuple-source -- |UNPARTITIONED|
assign [$$25] <- [function-call: asterix:open-record-constructor,
Args:[AString: {s_suppkey}, function-call: asterix:field-access-by-name,
Args:[%0->$$3, AString: {s_suppkey}], AString: {n_name}, function-call:
asterix:field-access-by-name, Args:[%0->$$4, AString: {n_name}]]] --
|UNPARTITIONED|
select (function-call: algebricks:eq, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$4, AString: {n_nationkey}],
function-call: asterix:field-access-by-name, Args:[%0->$$3, AString:
{s_nationkey}]]) -- |UNPARTITIONED|
join (TRUE) -- |UNPARTITIONED|
data-scan []<-[$$105, $$3] <- test:Supplier -- |UNPARTITIONED|
empty-tuple-source -- |UNPARTITIONED|
data-scan []<-[$$106, $$4] <- test:Nation -- |UNPARTITIONED|
empty-tuple-source -- |UNPARTITIONED|
} --
|UNPARTITIONED|
data-scan
[]<-[$$103, $$104, $$2] <- test:Partsupp -- |UNPARTITIONED|
nested
tuple source -- |UNPARTITIONED|
} -- |UNPARTITIONED|
select (function-call:
asterix:contains, Args:[function-call: asterix:field-access-by-name,
Args:[%0->$$1, AString: {p_name}], AString: {green}]) -- |UNPARTITIONED|
data-scan []<-[$$102, $$1] <-
test:Part -- |UNPARTITIONED|
nested tuple source --
|UNPARTITIONED|
} -- |UNPARTITIONED|
data-scan []<-[$$101, $$0] <- test:NestedOrders --
|UNPARTITIONED|
empty-tuple-source -- |UNPARTITIONED|
{noformat}
Mar 04, 2016 1:57:25 PM
org.apache.hyracks.algebricks.core.rewriter.base.AbstractRuleController
printRuleApplication
FINE: >>>> After plan
{noformat}
distribute result [%0->$$94] -- |UNPARTITIONED|
project ([$$94]) -- |UNPARTITIONED|
assign [$$94] <- [function-call: asterix:open-record-constructor,
Args:[AString: {nation}, %0->$$13, AString: {o_year}, %0->$$14, AString:
{sum_profit}, %0->$$109]] -- |UNPARTITIONED|
order (ASC, %0->$$13) (DESC, %0->$$14) -- |UNPARTITIONED|
group by ([$$13 := %0->$$98; $$14 := %0->$$99]) decor ([]) {
aggregate [$$109] <- [function-call: asterix:agg-sum,
Args:[%0->$$96]] -- |UNPARTITIONED|
assign [$$96] <- [function-call:
asterix:field-access-by-name, Args:[%0->$$88, AString: {amount}]] --
|UNPARTITIONED|
nested tuple source -- |UNPARTITIONED|
} -- |UNPARTITIONED|
assign [$$99] <- [function-call: asterix:field-access-by-name,
Args:[%0->$$88, AString: {o_year}]] -- |UNPARTITIONED|
assign [$$98] <- [function-call: asterix:field-access-by-name,
Args:[%0->$$88, AString: {nation}]] -- |UNPARTITIONED|
assign [$$88] <- [function-call: asterix:open-record-constructor,
Args:[AString: {nation}, function-call: asterix:field-access-by-name,
Args:[%0->$$9, AString: {n_name}], AString: {o_year}, function-call:
asterix:get-year, Args:[function-call: asterix:field-access-by-name,
Args:[%0->$$0, AString: {o_orderdate}]], AString: {amount}, function-call:
asterix:numeric-subtract, Args:[function-call: asterix:numeric-multiply,
Args:[function-call: asterix:field-access-by-name, Args:[%0->$$9, AString:
{l_extendedprice}], function-call: asterix:numeric-subtract, Args:[AInt64: {1},
function-call: asterix:field-access-by-name, Args:[%0->$$9, AString:
{l_discount}]]], function-call: asterix:numeric-multiply, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$9, AString: {ps_supplycost}],
function-call: asterix:field-access-by-name, Args:[%0->$$9, AString:
{l_quantity}]]]]] -- |UNPARTITIONED|
select (function-call: algebricks:eq, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$0, AString: {o_orderkey}],
function-call: asterix:field-access-by-name, Args:[%0->$$9, AString:
{l_orderkey}]]) -- |UNPARTITIONED|
unnest $$9 <- function-call: asterix:scan-collection,
Args:[%0->$$74] -- |UNPARTITIONED|
group by ([$$116 := %0->$$101]) decor ([%0->$$0]) {
aggregate [$$74] <- [function-call:
asterix:listify, Args:[%0->$$67]] -- |UNPARTITIONED|
select (function-call: algebricks:not,
Args:[function-call: algebricks:is-null, Args:[%0->$$115]]) -- |UNPARTITIONED|
nested tuple source -- |UNPARTITIONED|
} -- |UNPARTITIONED|
left outer join (function-call: algebricks:eq,
Args:[%0->$$101, %0->$$124]) -- |UNPARTITIONED|
data-scan []<-[$$101, $$0] <- test:NestedOrders --
|UNPARTITIONED|
empty-tuple-source -- |UNPARTITIONED|
assign [$$115] <- [TRUE] -- |UNPARTITIONED|
assign [$$67] <- [function-call:
asterix:open-record-constructor, Args:[AString: {l_extendedprice},
function-call: asterix:field-access-by-name, Args:[%0->$$8, AString:
{l_extendedprice}], AString: {l_discount}, function-call:
asterix:field-access-by-name, Args:[%0->$$8, AString: {l_discount}], AString:
{l_quantity}, function-call: asterix:field-access-by-name, Args:[%0->$$8,
AString: {l_quantity}], AString: {l_orderkey}, %0->$$100, AString: {n_name},
function-call: asterix:field-access-by-name, Args:[%0->$$8, AString: {n_name}],
AString: {ps_supplycost}, function-call: asterix:field-access-by-name,
Args:[%0->$$8, AString: {ps_supplycost}]]] -- |UNPARTITIONED|
select (function-call: algebricks:eq,
Args:[function-call: asterix:field-access-by-name, Args:[%0->$$1, AString:
{p_partkey}], function-call: asterix:field-access-by-name, Args:[%0->$$8,
AString: {l_partkey}]]) -- |UNPARTITIONED|
select (function-call: algebricks:eq,
Args:[%0->$$100, function-call: asterix:field-access-by-name, Args:[%0->$$114,
AString: {l_orderkey}]]) -- |UNPARTITIONED|
assign [$$100] <- [function-call:
asterix:field-access-by-name, Args:[%0->$$8, AString: {l_orderkey}]] --
|UNPARTITIONED|
unnest $$8 <- function-call:
asterix:scan-collection, Args:[%0->$$57] -- |UNPARTITIONED|
group by ([$$124 := %0->$$113; $$125 :=
%0->$$102]) decor ([%0->$$1; %0->$$114]) {
aggregate [$$57] <-
[function-call: asterix:listify, Args:[%0->$$49]] -- |UNPARTITIONED|
select (function-call:
algebricks:not, Args:[function-call: algebricks:is-null, Args:[%0->$$123]]) --
|UNPARTITIONED|
nested tuple source --
|UNPARTITIONED|
} -- |UNPARTITIONED|
left outer join (function-call:
algebricks:and, Args:[function-call: algebricks:eq, Args:[%0->$$113,
%0->$$122], function-call: algebricks:eq, Args:[%0->$$102, %0->$$121]]) --
|UNPARTITIONED|
select (function-call:
asterix:contains, Args:[function-call: asterix:field-access-by-name,
Args:[%0->$$1, AString: {p_name}], AString: {green}]) -- |UNPARTITIONED|
data-scan []<-[$$102, $$1] <-
test:Part -- |UNPARTITIONED|
data-scan []<-[$$113, $$114] <-
test:NestedOrders -- |UNPARTITIONED|
empty-tuple-source --
|UNPARTITIONED|
assign [$$123] <- [TRUE] --
|UNPARTITIONED|
assign [$$49] <- [function-call:
asterix:open-record-constructor, Args:[AString: {l_extendedprice},
function-call: asterix:field-access-by-name, Args:[%0->$$7, AString:
{l_extendedprice}], AString: {l_discount}, function-call:
asterix:field-access-by-name, Args:[%0->$$7, AString: {l_discount}], AString:
{l_quantity}, function-call: asterix:field-access-by-name, Args:[%0->$$7,
AString: {l_quantity}], AString: {l_partkey}, %0->$$107, AString: {l_orderkey},
function-call: asterix:field-access-by-name, Args:[%0->$$7, AString:
{l_orderkey}], AString: {n_name}, function-call: asterix:field-access-by-name,
Args:[%0->$$7, AString: {n_name}], AString: {ps_supplycost}, function-call:
asterix:field-access-by-name, Args:[%0->$$2, AString: {ps_supplycost}]]] --
|UNPARTITIONED|
select (function-call:
algebricks:eq, Args:[function-call: asterix:field-access-by-name,
Args:[%0->$$2, AString: {ps_suppkey}], function-call:
asterix:field-access-by-name, Args:[%0->$$7, AString: {l_suppkey}]]) --
|UNPARTITIONED|
select (function-call:
algebricks:eq, Args:[function-call: asterix:field-access-by-name,
Args:[%0->$$2, AString: {ps_partkey}], %0->$$107]) -- |UNPARTITIONED|
assign [$$107] <-
[function-call: asterix:field-access-by-name, Args:[%0->$$7, AString:
{l_partkey}]] -- |UNPARTITIONED|
unnest $$7 <- function-call:
asterix:scan-collection, Args:[%0->$$41] -- |UNPARTITIONED|
group by ([$$111 :=
%0->$$103; $$112 := %0->$$104; $$121 := %0->$$118; $$122 := %0->$$119]) decor
([%0->$$120; %0->$$117; %0->$$2]) {
aggregate [$$41]
<- [function-call: asterix:listify, Args:[%0->$$33]] -- |UNPARTITIONED|
assign [$$33]
<- [function-call: asterix:open-record-constructor, Args:[AString: {l_suppkey},
%0->$$108, AString: {l_extendedprice}, function-call:
asterix:field-access-by-name, Args:[%0->$$6, AString: {l_extendedprice}],
AString: {l_discount}, function-call: asterix:field-access-by-name,
Args:[%0->$$6, AString: {l_discount}], AString: {l_quantity}, function-call:
asterix:field-access-by-name, Args:[%0->$$6, AString: {l_quantity}], AString:
{l_partkey}, function-call: asterix:field-access-by-name, Args:[%0->$$6,
AString: {l_partkey}], AString: {l_orderkey}, function-call:
asterix:field-access-by-name, Args:[%0->$$120, AString: {o_orderkey}], AString:
{n_name}, function-call: asterix:field-access-by-name, Args:[%0->$$5, AString:
{n_name}]]] -- |UNPARTITIONED|
select
(function-call: algebricks:eq, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$5, AString: {s_suppkey}], %0->$$108])
-- |UNPARTITIONED|
assign
[$$108] <- [function-call: asterix:field-access-by-name, Args:[%0->$$6,
AString: {l_suppkey}]] -- |UNPARTITIONED|
unnest
$$6 <- function-call: asterix:scan-collection, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$120, AString: {o_lineitems}]] --
|UNPARTITIONED|
unnest
$$5 <- function-call: asterix:scan-collection, Args:[%0->$$28] --
|UNPARTITIONED|
select (function-call: algebricks:not, Args:[function-call: algebricks:is-null,
Args:[%0->$$110]]) -- |UNPARTITIONED|
nested tuple source -- |UNPARTITIONED|
} -- |UNPARTITIONED|
left outer join (TRUE) --
|UNPARTITIONED|
data-scan []<-[$$103,
$$104, $$2] <- test:Partsupp -- |UNPARTITIONED|
select
(function-call: asterix:contains, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$117, AString: {p_name}], AString:
{green}]) -- |UNPARTITIONED|
data-scan
[]<-[$$118, $$117] <- test:Part -- |UNPARTITIONED|
data-scan
[]<-[$$119, $$120] <- test:NestedOrders -- |UNPARTITIONED|
empty-tuple-source -- |UNPARTITIONED|
assign [$$110] <-
[TRUE] -- |UNPARTITIONED|
aggregate [$$28] <-
[function-call: asterix:listify, Args:[%0->$$25]] -- |UNPARTITIONED|
join (TRUE) --
|UNPARTITIONED|
empty-tuple-source -- |UNPARTITIONED|
assign [$$25] <-
[function-call: asterix:open-record-constructor, Args:[AString: {s_suppkey},
function-call: asterix:field-access-by-name, Args:[%0->$$3, AString:
{s_suppkey}], AString: {n_name}, function-call: asterix:field-access-by-name,
Args:[%0->$$4, AString: {n_name}]]] -- |UNPARTITIONED|
select
(function-call: algebricks:eq, Args:[function-call:
asterix:field-access-by-name, Args:[%0->$$4, AString: {n_nationkey}],
function-call: asterix:field-access-by-name, Args:[%0->$$3, AString:
{s_nationkey}]]) -- |UNPARTITIONED|
join (TRUE)
-- |UNPARTITIONED|
data-scan
[]<-[$$105, $$3] <- test:Supplier -- |UNPARTITIONED|
empty-tuple-source -- |UNPARTITIONED|
data-scan
[]<-[$$106, $$4] <- test:Nation -- |UNPARTITIONED|
empty-tuple-source -- |UNPARTITIONED|
{noformat}
> Query fails in rewrite phase with stack overflow
> ------------------------------------------------
>
> Key: ASTERIXDB-1332
> URL: https://issues.apache.org/jira/browse/ASTERIXDB-1332
> Project: Apache AsterixDB
> Issue Type: Bug
> Components: Optimizer
> Reporter: Pouria
> Assignee: Yingyi Bu
>
> A rewritten version of TPCH Q9 (for nested schema) breaks in re-write step:
> //The error on cc.log
> {noformat}
> 2016-03-04 10:30:04,642 WARN [qtp85748029-40] util.log
> (JCLLoggerAdapter.java:warn(437)) - Error for /
> java.lang.StackOverflowError
> at
> org.apache.hyracks.algebricks.core.algebra.typing.PropagatingTypeEnvironment.getVarTypeFullList(PropagatingTypeEnvironment.java:84)
> at
> org.apache.hyracks.algebricks.core.algebra.typing.PropagatingTypeEnvironment.getVarType(PropagatingTypeEnvironment.java:73)
> at
> org.apache.hyracks.algebricks.core.algebra.properties.TypePropagationPolicy$1.getVarType(TypePropagationPolicy.java:42)
> at
> org.apache.hyracks.algebricks.core.algebra.typing.PropagatingTypeEnvironment.getVarTypeFullList(PropagatingTypeEnvironment.java:88)
> at
> org.apache.hyracks.algebricks.core.algebra.typing.PropagatingTypeEnvironment.getVarType(PropagatingTypeEnvironment.java:73)
> at
> org.apache.hyracks.algebricks.core.algebra.properties.TypePropagationPolicy$1.getVarType(TypePropagationPolicy.java:42)
> at org.
> [… It repeats for tens of lines]
> {noformat}
> //Here is the Query:
> {noformat}
> for $profit in (
> for $o in dataset NestedOrders
> for $l3 in (
> for $p in dataset('Part')
> for $l2 in (
> for $ps in dataset('Partsupp')
> for $l1 in (
> for $s1 in (
> for $s in dataset('Supplier')
> for $n in dataset('Nation')
> where $n.n_nationkey = $s.s_nationkey
> return {
> "s_suppkey": $s.s_suppkey,
> "n_name": $n.n_name
> }
> ) //s1
> //for $l in dataset('LineItem')
> for $l in $o.o_lineitems
> //where $s1.s_suppkey = $l.l_suppkey
> return {
> "l_suppkey": $l.l_suppkey,
> "l_extendedprice": $l.l_extendedprice,
> "l_discount": $l.l_discount,
> "l_quantity": $l.l_quantity,
> "l_partkey": $l.l_partkey,
> "l_orderkey": $o.o_orderkey, //$l.l_orderkey,
> "n_name": $s1.n_name
> }
> ) //l1
> where $ps.ps_suppkey = $l1.l_suppkey and $ps.ps_partkey = $l1.l_partkey
>
> return {
> "l_extendedprice": $l1.l_extendedprice,
> "l_discount": $l1.l_discount,
> "l_quantity": $l1.l_quantity,
> "l_partkey": $l1.l_partkey,
> "l_orderkey": $l1.l_orderkey,
> "n_name": $l1.n_name,
> "ps_supplycost": $ps.ps_supplycost
> }
> ) //l2
> where contains($p.p_name, 'green') and $p.p_partkey = $l2.l_partkey
> return {
> "l_extendedprice": $l2.l_extendedprice,
> "l_discount": $l2.l_discount,
> "l_quantity": $l2.l_quantity,
> "l_orderkey": $l2.l_orderkey,
> "n_name": $l2.n_name,
> "ps_supplycost": $l2.ps_supplycost
> }
> ) //l3
> where $o.o_orderkey = $l3.l_orderkey
> let $amount := $l3.l_extendedprice * (1 - $l3.l_discount) -
> $l3.ps_supplycost * $l3.l_quantity
> let $o_year := get-year($o.o_orderdate)
> return {
> "nation": $l3.n_name,
> "o_year": $o_year,
> "amount": $amount
> }
> ) //profit
> group by $nation := $profit.nation, $o_year := $profit.o_year with $profit
> order by $nation, $o_year desc
> return {
> "nation": $nation,
> "o_year": $o_year,
> "sum_profit": sum( for $pr in $profit return $pr.amount )
> }
> //Here is the Schema
> create type NestedLineItemType as {
> l_partkey: int64,
> l_suppkey: int64,
> l_linenumber: int32,
> l_quantity: int32,
> l_extendedprice: double,
> l_discount: double,
> l_tax: double,
> l_returnflag: string,
> l_linestatus: string,
> l_shipdate: string,
> l_commitdate: string,
> l_receiptdate: string,
> l_shipinstruct: string,
> l_shipmode: string,
> l_comment: string
> }
> create type NestedOrderType as {
> o_orderkey: int64,
> o_custkey: int64,
> o_orderstatus: string,
> o_totalprice: double,
> o_orderdate: string,
> o_orderpriority: string,
> o_clerk: string,
> o_shippriority: int32,
> o_comment: string,
> o_lineitems: [ NestedLineItemType ]
> }
> create type CustomerType as {
> c_custkey: int64,
> c_name: string,
> c_address: string,
> c_nationkey: int32,
> c_phone: string,
> c_acctbal: double,
> c_mktsegment: string,
> c_comment: string
> }
> create type PartType as {
> p_partkey: int64,
> p_name: string,
> p_mfgr: string,
> p_brand: string,
> p_type: string,
> p_size: int32,
> p_container: string,
> p_retailprice: double,
> p_comment: string
> }
> create type PartSuppType as {
> ps_partkey: int64,
> ps_suppkey: int64,
> ps_availqty: int32,
> ps_supplycost: double,
> ps_comment: string
> }
> create type SupplierType as {
> s_suppkey: int64,
> s_name: string,
> s_address: string,
> s_nationkey: int32,
> s_phone: string,
> s_acctbal: double,
> s_comment: string
> }
> create type NationType as {
> n_nationkey: int32,
> n_name: string,
> n_regionkey: int32,
> n_comment: string
> }
> create type RegionType as {
> r_regionkey: int32,
> r_name: string,
> r_comment: string
> }
> create dataset NestedOrders(NestedOrderType) primary key o_orderkey;
> create dataset Customer(CustomerType) primary key c_custkey;
> create dataset Part(PartType) primary key p_partkey;
> create dataset Partsupp(PartSuppType) primary key ps_partkey, ps_suppkey;
> create dataset Supplier(SupplierType) primary key s_suppkey;
> create dataset Region(RegionType) primary key r_regionkey;
> create dataset Nation(NationType) primary key n_nationkey;
> create index nation_fk_region on Nation(n_regionkey);
> create index supplier_fk_nation on Supplier (s_nationkey);
> create index partsupp_fk_part on Partsupp (ps_partkey);
> create index partsupp_fk_supplier on Partsupp (ps_suppkey);
> create index customer_fk_nation on Customer (c_nationkey);
> create index nestedOrders_fk_customer on NestedOrders (o_custkey);
> create index nestedOrders_orderdateIx on NestedOrders (o_orderdate);
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
