blob: 9b837186edc533f030c862bb7526afe3222dc56c [file] [log] [blame]
/*
* Description : Equi joins two datasets, Customers and Orders, based on the customer id.
* Given the 'indexnl' hint we expect the join to be transformed
* into an indexed nested-loop join using Customers' primary index.
* We expect the additional predicates to be put into a select above the
* primary index search.
* Success : Yes
*/
drop dataverse test if exists;
create dataverse test;
use dataverse test;
create type AddressType as closed {
number: int32,
street: string,
city: string
}
create type CustomerType as closed {
cid: int32,
name: string,
age: int32?,
address: AddressType?,
lastorder: {
oid: int32,
total: float
}
}
create type OrderType as closed {
oid: int32,
cid: int32,
orderstatus: string,
orderpriority: string,
clerk: string,
total: float
}
create dataset Customers(CustomerType) partitioned by key cid;
create dataset Orders(OrderType) partitioned by key oid;
write output to nc1:"rttest/btree-index-join_primary-equi-join-multipred.adm";
for $c in dataset('Customers')
for $o in dataset('Orders')
where $c.cid /*+ indexnl */ = $o.cid and $c.name < $o.orderstatus and $c.age < $o.cid
return {"customer":$c, "order": $o}