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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
|
# name: test/sql/join/iejoin/predicate_expressions.test
# description: Predicate expressions should work with multiple chunks
# group: [iejoin]
statement ok
PRAGMA enable_verification
statement ok
SET merge_join_threshold=0
statement ok
PRAGMA explain_output = PHYSICAL_ONLY;
# Create a range of dates
statement ok
create table calendar as SELECT *
FROM range(DATE '2022-01-01', DATE '2024-02-01', INTERVAL '1' MONTH);
# Create an SCD2 dummy table with nullable end dates
statement ok
create table scd2 as
select
range as range_start,
case when date_part('year', range) < 2023 then range + interval 4 month - interval 1 day end as range_end,
n
from calendar
cross join generate_series(1, 85) as n
# Create an SCD2 dummy table with non-nullable end dates
statement ok
create table scd2_non_null as
select
range as range_start,
case when date_part('year', range) < 2023 then range + interval 4 month - interval 1 day else '2099-01-01' end as range_end,
n
from calendar
cross join generate_series(1, 85) as n
# Aggregate each table by using a range join
query II
explain
select
range,
count(*) as n
from scd2_non_null
inner join calendar
on range between range_start and ifnull(range_end,'2099-01-01')
group by range
order by range
----
physical_plan <REGEX>:.*IE_JOIN.*
query II nosort expected
select
range,
count(*) as n
from scd2_non_null
inner join calendar
on range between range_start and ifnull(range_end,'2099-01-01')
group by range
order by range
# First key should work
query II
explain
select
range,
count(*) as n
from scd2
inner join calendar
on range <= ifnull(range_end,'2099-01-01') and range_start <= range
group by range
order by range
----
physical_plan <REGEX>:.*IE_JOIN.*
query II nosort expected
select
range,
count(*) as n
from scd2
inner join calendar
on range <= ifnull(range_end,'2099-01-01') and range_start <= range
group by range
order by range
# Second key should work
query II
explain
select
range,
count(*) as n
from scd2
inner join calendar
on range between range_start and ifnull(range_end,'2099-01-01')
group by range
order by range
----
physical_plan <REGEX>:.*IE_JOIN.*
query II nosort expected
select
range,
count(*) as n
from scd2
inner join calendar
on range between range_start and ifnull(range_end,'2099-01-01')
group by range
order by range
|
