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
|
# name: test/optimizer/using_optimizer.test
# description: Test optimization of USING columns
# group: [optimizer]
statement ok
PRAGMA explain_output='optimized_only';
statement ok
create table a as select 42 as i, 80 as j;
statement ok
create table b as select 43 as i, 84 as k;
statement ok
create table c as select 44 as i, 84 as l;
# inner join
query II nosort inner
explain select i from a inner join b using (i);
----
query II nosort inner
explain select a.i from a, b where a.i=b.i;
----
query II nosort inner
explain select a.i from a natural join b;
----
# left join
query II nosort left
explain select i from a left outer join b using (i);
----
query II nosort left
explain select a.i from a left outer join b using (i);
----
query II nosort left
explain select a.i from a left outer join b on (a.i=b.i);
----
# right join
query II nosort right
explain select i from a right outer join b using (i);
----
query II nosort right
explain select b.i from a right outer join b using (i);
----
query II nosort right
explain select b.i from a right outer join b on (a.i=b.i);
----
# left join followed by inner join
query I
select i from a left outer join b using (i) inner join c using (i);
----
query I
select a.i from a left outer join b on (a.i=b.i) inner join c on (a.i=c.i);
----
query II nosort leftinner
explain select i from a left outer join b using (i) inner join c using (i);
----
query II nosort leftinner
explain select a.i from a left outer join b on (a.i=b.i) inner join c on (a.i=c.i);
----
# left join followed by left join
query I
select i from a left outer join b using (i) left outer join c using (i);
----
42
query I
select a.i from a left outer join b on (a.i=b.i) left outer join c on (a.i=c.i);
----
42
query II nosort leftleft
explain select i from a left outer join b using (i) left outer join c using (i);
----
query II nosort leftleft
explain select a.i from a left outer join b on (a.i=b.i) left outer join c on (a.i=c.i);
----
# left join followed by right join
query I
select i from a left outer join b using (i) right join c using (i);
----
44
query I
select c.i from a left outer join b on (a.i=b.i) right join c on (a.i=c.i);
----
44
query II nosort leftright
explain select i from a left outer join b using (i) right join c using (i);
----
query II nosort leftright
explain select c.i from a left outer join b on (a.i=b.i) right join c on (a.i=c.i);
----
# full outer join
# need to use a case expression here
query I
select i from a full outer join b using (i) order by all;
----
42
43
query I
select coalesce(a.i, b.i) from a full outer join b on (a.i=b.i) order by all;
----
42
43
query II nosort fullouter
explain select i from a full outer join b using (i);
----
query II nosort fullouter
explain select coalesce(b.i, a.i) from a full outer join b on (a.i=b.i);
----
|
