/
TestWithClauseProcessing.java
276 lines (218 loc) · 14 KB
/
TestWithClauseProcessing.java
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
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
package org.teiid.query.processor;
import static org.teiid.query.processor.TestProcessor.*;
import java.util.Arrays;
import java.util.List;
import org.junit.Test;
import org.teiid.common.buffer.BlockedException;
import org.teiid.common.buffer.TupleSource;
import org.teiid.core.TeiidComponentException;
import org.teiid.core.TeiidException;
import org.teiid.core.TeiidProcessingException;
import org.teiid.query.metadata.TransformationMetadata;
import org.teiid.query.optimizer.TestOptimizer;
import org.teiid.query.optimizer.TestOptimizer.ComparisonMode;
import org.teiid.query.optimizer.capabilities.BasicSourceCapabilities;
import org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder;
import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder;
import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability;
import org.teiid.query.sql.lang.Command;
import org.teiid.query.unittest.RealMetadataFactory;
import org.teiid.query.util.CommandContext;
@SuppressWarnings({"nls", "unchecked"})
public class TestWithClauseProcessing {
@Test public void testSingleItem() {
String sql = "with a (x, y, z) as (select e1, e2, e3 from pm1.g1) SELECT pm1.g2.e2, a.x from pm1.g2, a where e1 = x and z = 1 order by x"; //$NON-NLS-1$
List<?>[] expected = new List[] {
Arrays.asList(0, "a"),
Arrays.asList(3, "a"),
Arrays.asList(0, "a"),
Arrays.asList(1, "c"),
};
FakeDataManager dataManager = new FakeDataManager();
dataManager.setBlockOnce();
sampleData1(dataManager);
ProcessorPlan plan = helpGetPlan(helpParse(sql), RealMetadataFactory.example1Cached());
helpProcess(plan, dataManager, expected);
}
@Test public void testMultipleItems() {
String sql = "with a (x, y, z) as (select e1, e2, e3 from pm1.g1), b as (SELECT * from pm1.g2, a where e1 = x and z = 1 order by e2 limit 2) SELECT a.x, b.e1 from a, b where a.x = b.e1"; //$NON-NLS-1$
List<?>[] expected = new List[] {
Arrays.asList("a", "a"),
Arrays.asList("a", "a"),
Arrays.asList("a", "a"),
Arrays.asList("a", "a"),
Arrays.asList("a", "a"),
Arrays.asList("a", "a"),
};
FakeDataManager dataManager = new FakeDataManager();
dataManager.setBlockOnce();
sampleData1(dataManager);
ProcessorPlan plan = helpGetPlan(helpParse(sql), RealMetadataFactory.example1Cached());
helpProcess(plan, dataManager, expected);
}
@Test public void testWithPushdown() throws TeiidException {
FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
caps.setCapabilitySupport(Capability.COMMON_TABLE_EXPRESSIONS, true);
caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
String sql = "with a (x, y, z) as (select e1, e2, e3 from pm1.g1) SELECT a.x from a, a z"; //$NON-NLS-1$
HardcodedDataManager dataManager = new HardcodedDataManager();
List<?>[] expected = new List[] {
Arrays.asList("a", 1, Boolean.FALSE),
};
dataManager.addData("WITH a (x, y, z) AS (SELECT g_0.e1, g_0.e2, g_0.e3 FROM pm1.g1 AS g_0) SELECT g_0.x FROM a AS g_0, a AS g_1", expected);
ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, capFinder, new String[] {"WITH a (x, y, z) AS (SELECT g_0.e1, g_0.e2, g_0.e3 FROM pm1.g1 AS g_0) SELECT g_0.x FROM a AS g_0, a AS g_1"}, ComparisonMode.EXACT_COMMAND_STRING);
helpProcess(plan, dataManager, expected);
}
@Test public void testWithPushdown1() throws TeiidException {
FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
caps.setCapabilitySupport(Capability.COMMON_TABLE_EXPRESSIONS, true);
caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, true);
capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
String sql = "with a as (select x, y, z from (select e1 as x, e2 as y, e3 as z from pm1.g1) v) SELECT count(a.x) from a, a z"; //$NON-NLS-1$
HardcodedDataManager dataManager = new HardcodedDataManager(RealMetadataFactory.example1Cached());
List<?>[] expected = new List[] {
Arrays.asList("a", 1, Boolean.FALSE),
};
dataManager.addData("WITH a (x, y, z) AS (SELECT g_0.e1, g_0.e2, g_0.e3 FROM g1 AS g_0) SELECT COUNT(g_0.x) FROM a AS g_0, a AS g_1", expected);
ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, capFinder, new String[] {"WITH a (x, y, z) AS (SELECT g_0.e1, g_0.e2, g_0.e3 FROM pm1.g1 AS g_0) SELECT COUNT(g_0.x) FROM a AS g_0, a AS g_1"}, ComparisonMode.EXACT_COMMAND_STRING);
helpProcess(plan, dataManager, expected);
}
/**
* This tests both an intervening parent plan construct (count) and a reference to a parent with in a subquery
*/
@Test public void testWithPushdownNotFullyPushed() throws TeiidException {
FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
caps.setCapabilitySupport(Capability.COMMON_TABLE_EXPRESSIONS, true);
caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, false);
capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
String sql = "with a as (select x, y, z from (select e1 as x, e2 as y, e3 as z from pm1.g1) v), b as (select e4 from pm1.g3) SELECT count(a.x), max(a.y) from a, a z group by z.x having max(a.y) < (with b as (select e1 from pm1.g1) select a.y from a, b where a.x = z.x)"; //$NON-NLS-1$
HardcodedDataManager dataManager = new HardcodedDataManager(RealMetadataFactory.example1Cached());
List<?>[] expected = new List[] {
Arrays.asList("a", 1, "a"),
};
dataManager.addData("WITH a (x, y, z) AS (SELECT g_0.e1, g_0.e2, g_0.e3 FROM g1 AS g_0) SELECT g_1.x, g_0.y, g_0.x FROM a AS g_0, a AS g_1", expected);
dataManager.addData("WITH b (e1) AS (SELECT g_0.e1 FROM g1 AS g_0), a (x, y, z) AS (SELECT g_0.e1, g_0.e2, g_0.e3 FROM g1 AS g_0) SELECT g_0.y FROM a AS g_0, b AS g_1 WHERE g_0.x = 'a'",
new List[] {Arrays.asList(2)});
ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, capFinder, new String[] {"WITH a (x, y, z) AS (SELECT g_0.e1, g_0.e2, g_0.e3 FROM pm1.g1 AS g_0) SELECT g_1.x, g_0.y, g_0.x FROM a AS g_0, a AS g_1"}, ComparisonMode.EXACT_COMMAND_STRING);
helpProcess(plan, dataManager, new List[] {
Arrays.asList(1, 1),
});
}
/**
* Tests source affinity
*/
@Test public void testWithPushdownNotFullyPushed1() throws TeiidException {
FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
caps.setCapabilitySupport(Capability.COMMON_TABLE_EXPRESSIONS, true);
caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, false);
capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
capFinder.addCapabilities("pm2", caps); //$NON-NLS-1$
String sql = "with a as (select e1 from pm1.g1), b as (select e1 from pm2.g2), c as (select count(*) as x from pm1.g1) SELECT a.e1, (select max(x) from c), pm1.g1.e2 from pm1.g1, a, b"; //$NON-NLS-1$
HardcodedDataManager dataManager = new HardcodedDataManager(RealMetadataFactory.example1Cached());
dataManager.addData("WITH a (e1) AS (SELECT g_0.e1 FROM g1 AS g_0) SELECT g_0.e1 FROM a AS g_0", new List[] {
Arrays.asList("a"),
});
dataManager.addData("WITH b (e1) AS (SELECT g_0.e1 FROM g2 AS g_0) SELECT 1 FROM b AS g_0", new List[] {
Arrays.asList("b"),
});
dataManager.addData("SELECT g_0.e2 FROM g1 AS g_0", new List[] {
Arrays.asList(1), Arrays.asList(2)
});
dataManager.addData("SELECT 1 FROM g1 AS g_0", new List[] {
Arrays.asList(1), Arrays.asList(1), Arrays.asList(1)
});
ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, capFinder, new String[] {
"SELECT g_0.e2 FROM pm1.g1 AS g_0",
"WITH b (e1) AS (SELECT g_0.e1 FROM pm2.g2 AS g_0) SELECT 1 FROM b AS g_0",
"WITH a (e1) AS (SELECT g_0.e1 FROM pm1.g1 AS g_0) SELECT g_0.e1 FROM a AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING);
helpProcess(plan, dataManager, new List[] {
Arrays.asList("a", 3, 1),
Arrays.asList("a", 3, 2),
});
}
@Test public void testWithPushdownWithConstants() throws TeiidException {
FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
caps.setCapabilitySupport(Capability.COMMON_TABLE_EXPRESSIONS, true);
caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
String sql = "with a (x, y) as (select 1, 2 from pm1.g1) SELECT a.x from a, a z"; //$NON-NLS-1$
TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, capFinder, new String[] {"WITH a (x, y) AS (SELECT 1, 2 FROM pm1.g1 AS g_0) SELECT g_0.x FROM a AS g_0, a AS g_1"}, ComparisonMode.EXACT_COMMAND_STRING);
}
@Test public void testWithOrderBy() throws TeiidException {
FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
caps.setCapabilitySupport(Capability.COMMON_TABLE_EXPRESSIONS, true);
caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
String sql = "with a (x, y) as (select 1, 2 from pm1.g1) SELECT a.x from a, a z order by x"; //$NON-NLS-1$
HardcodedDataManager dataManager = new HardcodedDataManager(RealMetadataFactory.example1Cached());
dataManager.addData("WITH a (x, y) AS (SELECT 1, 2 FROM g1 AS g_0) SELECT g_0.x AS c_0 FROM a AS g_0, a AS g_1 ORDER BY c_0", new List[0]);
ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, capFinder, new String[] {"WITH a (x, y) AS (SELECT 1, 2 FROM pm1.g1 AS g_0) SELECT g_0.x AS c_0 FROM a AS g_0, a AS g_1 ORDER BY c_0"}, ComparisonMode.EXACT_COMMAND_STRING);
helpProcess(plan, dataManager, new List[0]);
}
@Test public void testWithJoinPlanning() throws TeiidException {
TransformationMetadata metadata = RealMetadataFactory.example1();
RealMetadataFactory.setCardinality("pm1.g2", 100000, metadata);
String sql = "with a (x) as (select e1 from pm1.g1) SELECT a.x from pm1.g2, a where (pm1.g2.e1 = a.x)"; //$NON-NLS-1$
TestOptimizer.helpPlan(sql, metadata, null, TestOptimizer.getGenericFinder(false), new String[] {"SELECT g_0.e1 FROM pm1.g2 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT a.x FROM a ORDER BY a.x"}, ComparisonMode.EXACT_COMMAND_STRING);
}
@Test public void testWithJoinPlanning1() throws TeiidException {
TransformationMetadata metadata = RealMetadataFactory.example1Cached();
String sql = "with a (x) as (select e1 from pm1.g1) SELECT a.x from pm1.g2, a where (pm1.g2.e1 = a.x)"; //$NON-NLS-1$
TestOptimizer.helpPlan(sql, metadata, null, TestOptimizer.getGenericFinder(false), new String[] {"SELECT g_0.e1 FROM pm1.g2 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT a.x FROM a ORDER BY a.x"}, ComparisonMode.EXACT_COMMAND_STRING);
}
@Test public void testWithBlockingJoin() throws TeiidException {
String sql = "with a (x, y) as (select e1, e2 from pm1.g1) SELECT a.x, a.y, pm1.g2.e1 from a left outer join pm1.g2 makenotdep on (rtrim(a.x) = pm1.g2.e1) order by a.y"; //$NON-NLS-1$
HardcodedDataManager dataManager = new HardcodedDataManager() {
@Override
public TupleSource registerRequest(CommandContext context,
Command command, String modelName,
RegisterRequestParameter parameterObject)
throws TeiidComponentException {
final TupleSource ts = super.registerRequest(context, command, modelName, parameterObject);
return new TupleSource() {
int i = 0;
@Override
public List<?> nextTuple() throws TeiidComponentException,
TeiidProcessingException {
if ((i++ % 100)<3) {
throw BlockedException.INSTANCE;
}
return ts.nextTuple();
}
@Override
public void closeSource() {
ts.closeSource();
}
};
}
};
List<?>[] rows = new List[10];
for (int i = 0; i < rows.length; i++) {
rows[i] = Arrays.asList(String.valueOf(i));
}
dataManager.addData("SELECT g_0.e1 AS c_0 FROM pm1.g2 AS g_0 ORDER BY c_0", rows);
rows = new List[100];
for (int i = 0; i < rows.length; i++) {
rows[i] = Arrays.asList(String.valueOf(i), i);
}
dataManager.addData("SELECT g_0.e1, g_0.e2 FROM pm1.g1 AS g_0", rows);
dataManager.addData("WITH a (x, y) AS (SELECT 1, 2 FROM g1 AS g_0) SELECT g_0.x AS c_0 FROM a AS g_0, a AS g_1 ORDER BY c_0", new List[0]);
ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, new DefaultCapabilitiesFinder(TestOptimizer.getTypicalCapabilities()), new String[] {"SELECT a.x, a.y FROM a", "SELECT g_0.e1 AS c_0 FROM pm1.g2 AS g_0 ORDER BY c_0"}, ComparisonMode.EXACT_COMMAND_STRING);
//check the full pushdown command
List<?>[] result = new List[100];
for (int i = 0; i < result.length; i++) {
result[i] = Arrays.asList(String.valueOf(i), i, i < 10?String.valueOf(i):null);
}
helpProcess(plan, dataManager, result);
}
}