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
110
111
112
113
114
|
# name: test/sql/join/asof/test_asof_join_predicates.test
# description: Test As-Of join NLJ rewrite for non-comparison predicates
# group: [asof]
statement ok
PRAGMA enable_verification;
# Issue 18309
statement ok
CREATE TABLE tt1 (i INTEGER, j VARCHAR);
statement ok
INSERT INTO tt1 VALUES
(2, 'A'),
(4, 'B'),
(5, 'A');
statement ok
CREATE TABLE tt2 (i INTEGER, j VARCHAR, k VARCHAR);
statement ok
INSERT INTO tt2 VALUES
(1, 'A', 'I'),
(3, 'B', 'II');
query II
explain
SELECT tt1.i, tt2.k
FROM tt1
ASOF JOIN tt2 ON
tt1.j = tt2.j AND tt1.i >= tt2.i
ORDER BY tt1.i;
----
physical_plan <REGEX>:.*NESTED_LOOP_JOIN.*
query II
SELECT tt1.i, tt2.k
FROM tt1
ASOF JOIN tt2 ON
(tt1.j = tt2.j OR tt1.j = tt2.j) AND tt1.i >= tt2.i
ORDER BY tt1.i;
----
2 I
4 II
5 I
# Issue 19027
statement ok
create table l (id integer, date timestamp, item varchar);
statement ok
insert into l values
(0, '2025-01-01', 'A');
statement ok
create table r (id integer, date timestamp, item varchar, valuei double);
statement ok
insert into r values
(0, '2025-01-01', 'A', 8.0),
(0, '2025-01-01', 'B', 12.0);
query II
explain
select
l.id,
l.date,
l.item as litem,
r.item as ritem,
valuei
from l
asof left join r
on l.id = r.id and l.date >= r.date
and (l.item = r.item or l.item = '*');
----
physical_plan <REGEX>:.*NESTED_LOOP_JOIN.*
query IIIII
select
l.id,
l.date,
l.item as litem,
r.item as ritem,
valuei
from l
asof left join r
on l.id = r.id and l.date >= r.date
and (l.item = r.item or l.item = '*');
----
0 2025-01-01 00:00:00 A A 8.0
# Issue 19251
statement ok
create temp table tbl1 as
select unnest(range(1000)) % 10 as x, '2022-01-01'::timestamp + to_days(unnest(range(1000))) as ts;
statement ok
create temp table tbl2 as
select unnest(range(1000)) % 10 as x, '2022-01-01'::timestamp + to_hours(unnest(range(1000))) as ts;
query II
explain
from tbl1 asof join
tbl2 on tbl1.x = tbl2.x
and tbl1.ts >= tbl2.ts
and (tbl1.ts - tbl2.ts) < interval '1' hours;
----
physical_plan <REGEX>:.*NESTED_LOOP_JOIN.*
statement ok
from tbl1 asof join
tbl2 on tbl1.x = tbl2.x
and tbl1.ts >= tbl2.ts
and (tbl1.ts - tbl2.ts) < interval '1' hours;
|
