-
Notifications
You must be signed in to change notification settings - Fork 593
/
join.slt
172 lines (116 loc) · 2.9 KB
/
join.slt
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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
statement ok
create table t1 (v1 int not null, v2 int not null, v3 int not null);
statement ok
create table t2 (v1 int not null, v2 int not null, v3 int not null);
statement ok
create materialized view mv1 as select * from t1 INNER JOIN t2 ON t1.v1=t2.v1;
statement ok
insert into t1 values (1,4,2), (2,3,3);
statement ok
insert into t2 values (1,3,5), (2,4,6);
statement ok
flush;
query IIIIIII
select v1, v2, v3, v10, v20, v30 from mv1;
----
1 4 2 1 3 5
2 3 3 2 4 6
statement ok
create table t3 (v1 int not null, v2 int not null, v3 int not null);
statement ok
create table t4 (v1 int not null, v2 int not null, v3 int not null);
statement ok
create materialized view mv2 as select * from t3 LEFT JOIN t4 ON t3.v1=t4.v1;
statement ok
insert into t3 values (1,4,2), (2,3,3);
statement ok
flush;
query IIIIII
select v1, v2, v3, v10, v20, v30 from mv2;
----
1 4 2 NULL NULL NULL
2 3 3 NULL NULL NULL
statement ok
create table t5 (v1 int not null, v2 int not null, v3 int not null);
statement ok
create table t6 (v1 int not null, v2 int not null, v3 int not null);
statement ok
create materialized view mv3 as select * from t5 RIGHT JOIN t6 ON t5.v1=t6.v1;
statement ok
insert into t6 values (1,3,5), (2,4,6);
statement ok
flush;
query IIIIII
select v1, v2, v3, v10, v20, v30 from mv3;
----
NULL NULL NULL 1 3 5
NULL NULL NULL 2 4 6
statement ok
create table t7 (v1 int not null, v2 int not null, v3 int not null);
statement ok
create table t8 (v1 int not null, v2 int not null, v3 int not null);
statement ok
create materialized view mv4 as select * from t7 FULL JOIN t8 ON t7.v1=t8.v1;
statement ok
insert into t7 values (1,4,2), (2,3,3);
statement ok
flush;
statement ok
insert into t8 values (3,3,5), (4,4,6);
statement ok
flush;
query IIIIII
select v1, v2, v3, v10, v20, v30 from mv4;
----
NULL NULL NULL 3 3 5
NULL NULL NULL 4 4 6
1 4 2 NULL NULL NULL
2 3 3 NULL NULL NULL
statement ok
create table t9 (v1 int not null, v2 int not null, v3 int not null);
statement ok
create table t10 (v1 int not null, v2 int not null, v3 int not null);
statement ok
create materialized view mv_full_outer_cond as select * from t9 FULL JOIN t10 ON t9.v1=t10.v1 and t9.v2 > t10.v2;
statement ok
insert into t9 values (1,4,2), (2,3,3);
statement ok
insert into t10 values (1,3,5), (2,4,6);
statement ok
flush;
query IIIIII
select v1, v2, v3, v10, v20, v30 from mv_full_outer_cond;
----
NULL NULL NULL 2 4 6
1 4 2 1 3 5
2 3 3 NULL NULL NULL
statement ok
drop materialized view mv1
statement ok
drop materialized view mv2
statement ok
drop materialized view mv3
statement ok
drop materialized view mv4
statement ok
drop materialized view mv_full_outer_cond
statement ok
drop table t1
statement ok
drop table t2
statement ok
drop table t3
statement ok
drop table t4
statement ok
drop table t5
statement ok
drop table t6
statement ok
drop table t7
statement ok
drop table t8
statement ok
drop table t9
statement ok
drop table t10