1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
pragma config.flags("OptimizerFlags", "EqualityFilterOverJoin");
pragma AnsiOptionalAs;
-- part of tpcds-6
$item = select * from as_table([
<|i_current_price:Just(1.0f), i_category:Just("aaa"), i_item_sk:Just(125l)|>,
<|i_current_price:Just(2.0f), i_category:Just("bbb"), i_item_sk:Just(999l)|>,
]);
$sub2 = (select i_current_price, i_category from $item);
$customer_address = select * from as_table([
<|ca_address_sk:Just(120l)|>,
<|ca_address_sk:Just(150l)|>,
]);
$customer = select * from as_table([
<|c_current_addr_sk:Just(150l), c_customer_sk:Just(4l)|>,
<|c_current_addr_sk:Just(120l), c_customer_sk:Just(2l)|>,
]);
$store_sales = select * from as_table([
<|ss_sold_date_sk:Just(1l), ss_customer_sk:Just(2l), ss_item_sk:Just(3l)|>,
<|ss_sold_date_sk:Just(3l), ss_customer_sk:Just(4l), ss_item_sk:Just(5l)|>,
]);
$date_dim = select * from as_table([
<|d_date_sk:Just(1l)|>,
<|d_date_sk:Just(2l)|>,
]);
$item = select * from as_table([
<|i_category:Just("aaa"), i_item_sk:Just(3l)|>,
<|i_category:Just("bbb"), i_item_sk:Just(5l)|>,
]);
select JoinTableRow() cnt
from $customer_address a
cross join $customer c
cross join $store_sales s
cross join $date_dim d
cross join $item i
left join $sub2 as j on i.i_category = j.i_category
where
s.ss_sold_date_sk = d.d_date_sk
and a.ca_address_sk = c.c_current_addr_sk
and c.c_customer_sk = s.ss_customer_sk
and s.ss_item_sk = i.i_item_sk
|
