1 | /* |
2 | * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0, |
3 | * and the EPL 1.0 (http://h2database.com/html/license.html). |
4 | * Initial Developer: H2 Group |
5 | */ |
6 | package org.h2.command.dml; |
7 | |
8 | import java.util.ArrayList; |
9 | import java.util.HashSet; |
10 | |
11 | import org.h2.api.ErrorCode; |
12 | import org.h2.command.CommandInterface; |
13 | import org.h2.engine.Session; |
14 | import org.h2.engine.SysProperties; |
15 | import org.h2.expression.Expression; |
16 | import org.h2.expression.ExpressionColumn; |
17 | import org.h2.expression.ExpressionVisitor; |
18 | import org.h2.expression.Parameter; |
19 | import org.h2.expression.ValueExpression; |
20 | import org.h2.message.DbException; |
21 | import org.h2.result.LocalResult; |
22 | import org.h2.result.ResultInterface; |
23 | import org.h2.result.ResultTarget; |
24 | import org.h2.result.SortOrder; |
25 | import org.h2.table.Column; |
26 | import org.h2.table.ColumnResolver; |
27 | import org.h2.table.Table; |
28 | import org.h2.table.TableFilter; |
29 | import org.h2.util.New; |
30 | import org.h2.util.StringUtils; |
31 | import org.h2.value.Value; |
32 | import org.h2.value.ValueInt; |
33 | import org.h2.value.ValueNull; |
34 | |
35 | /** |
36 | * Represents a union SELECT statement. |
37 | */ |
38 | public class SelectUnion extends Query { |
39 | |
40 | /** |
41 | * The type of a UNION statement. |
42 | */ |
43 | public static final int UNION = 0; |
44 | |
45 | /** |
46 | * The type of a UNION ALL statement. |
47 | */ |
48 | public static final int UNION_ALL = 1; |
49 | |
50 | /** |
51 | * The type of an EXCEPT statement. |
52 | */ |
53 | public static final int EXCEPT = 2; |
54 | |
55 | /** |
56 | * The type of an INTERSECT statement. |
57 | */ |
58 | public static final int INTERSECT = 3; |
59 | |
60 | private int unionType; |
61 | private final Query left; |
62 | private Query right; |
63 | private ArrayList<Expression> expressions; |
64 | private Expression[] expressionArray; |
65 | private ArrayList<SelectOrderBy> orderList; |
66 | private SortOrder sort; |
67 | private boolean isPrepared, checkInit; |
68 | private boolean isForUpdate; |
69 | |
70 | public SelectUnion(Session session, Query query) { |
71 | super(session); |
72 | this.left = query; |
73 | } |
74 | |
75 | public void setUnionType(int type) { |
76 | this.unionType = type; |
77 | } |
78 | |
79 | public int getUnionType() { |
80 | return unionType; |
81 | } |
82 | |
83 | public void setRight(Query select) { |
84 | right = select; |
85 | } |
86 | |
87 | public Query getLeft() { |
88 | return left; |
89 | } |
90 | |
91 | public Query getRight() { |
92 | return right; |
93 | } |
94 | |
95 | @Override |
96 | public void setSQL(String sql) { |
97 | this.sqlStatement = sql; |
98 | } |
99 | |
100 | @Override |
101 | public void setOrder(ArrayList<SelectOrderBy> order) { |
102 | orderList = order; |
103 | } |
104 | |
105 | private Value[] convert(Value[] values, int columnCount) { |
106 | Value[] newValues; |
107 | if (columnCount == values.length) { |
108 | // re-use the array if possible |
109 | newValues = values; |
110 | } else { |
111 | // create a new array if needed, |
112 | // for the value hash set |
113 | newValues = new Value[columnCount]; |
114 | } |
115 | for (int i = 0; i < columnCount; i++) { |
116 | Expression e = expressions.get(i); |
117 | newValues[i] = values[i].convertTo(e.getType()); |
118 | } |
119 | return newValues; |
120 | } |
121 | |
122 | @Override |
123 | public ResultInterface queryMeta() { |
124 | int columnCount = left.getColumnCount(); |
125 | LocalResult result = new LocalResult(session, expressionArray, columnCount); |
126 | result.done(); |
127 | return result; |
128 | } |
129 | |
130 | public LocalResult getEmptyResult() { |
131 | int columnCount = left.getColumnCount(); |
132 | return new LocalResult(session, expressionArray, columnCount); |
133 | } |
134 | |
135 | @Override |
136 | protected LocalResult queryWithoutCache(int maxRows, ResultTarget target) { |
137 | if (maxRows != 0) { |
138 | // maxRows is set (maxRows 0 means no limit) |
139 | int l; |
140 | if (limitExpr == null) { |
141 | l = -1; |
142 | } else { |
143 | Value v = limitExpr.getValue(session); |
144 | l = v == ValueNull.INSTANCE ? -1 : v.getInt(); |
145 | } |
146 | if (l < 0) { |
147 | // for limitExpr, 0 means no rows, and -1 means no limit |
148 | l = maxRows; |
149 | } else { |
150 | l = Math.min(l, maxRows); |
151 | } |
152 | limitExpr = ValueExpression.get(ValueInt.get(l)); |
153 | } |
154 | if (session.getDatabase().getSettings().optimizeInsertFromSelect) { |
155 | if (unionType == UNION_ALL && target != null) { |
156 | if (sort == null && !distinct && maxRows == 0 && |
157 | offsetExpr == null && limitExpr == null) { |
158 | left.query(0, target); |
159 | right.query(0, target); |
160 | return null; |
161 | } |
162 | } |
163 | } |
164 | int columnCount = left.getColumnCount(); |
165 | LocalResult result = new LocalResult(session, expressionArray, columnCount); |
166 | if (sort != null) { |
167 | result.setSortOrder(sort); |
168 | } |
169 | if (distinct) { |
170 | left.setDistinct(true); |
171 | right.setDistinct(true); |
172 | result.setDistinct(); |
173 | } |
174 | if (randomAccessResult) { |
175 | result.setRandomAccess(); |
176 | } |
177 | switch (unionType) { |
178 | case UNION: |
179 | case EXCEPT: |
180 | left.setDistinct(true); |
181 | right.setDistinct(true); |
182 | result.setDistinct(); |
183 | break; |
184 | case UNION_ALL: |
185 | break; |
186 | case INTERSECT: |
187 | left.setDistinct(true); |
188 | right.setDistinct(true); |
189 | break; |
190 | default: |
191 | DbException.throwInternalError("type=" + unionType); |
192 | } |
193 | LocalResult l = left.query(0); |
194 | LocalResult r = right.query(0); |
195 | l.reset(); |
196 | r.reset(); |
197 | switch (unionType) { |
198 | case UNION_ALL: |
199 | case UNION: { |
200 | while (l.next()) { |
201 | result.addRow(convert(l.currentRow(), columnCount)); |
202 | } |
203 | while (r.next()) { |
204 | result.addRow(convert(r.currentRow(), columnCount)); |
205 | } |
206 | break; |
207 | } |
208 | case EXCEPT: { |
209 | while (l.next()) { |
210 | result.addRow(convert(l.currentRow(), columnCount)); |
211 | } |
212 | while (r.next()) { |
213 | result.removeDistinct(convert(r.currentRow(), columnCount)); |
214 | } |
215 | break; |
216 | } |
217 | case INTERSECT: { |
218 | LocalResult temp = new LocalResult(session, expressionArray, columnCount); |
219 | temp.setDistinct(); |
220 | temp.setRandomAccess(); |
221 | while (l.next()) { |
222 | temp.addRow(convert(l.currentRow(), columnCount)); |
223 | } |
224 | while (r.next()) { |
225 | Value[] values = convert(r.currentRow(), columnCount); |
226 | if (temp.containsDistinct(values)) { |
227 | result.addRow(values); |
228 | } |
229 | } |
230 | break; |
231 | } |
232 | default: |
233 | DbException.throwInternalError("type=" + unionType); |
234 | } |
235 | if (offsetExpr != null) { |
236 | result.setOffset(offsetExpr.getValue(session).getInt()); |
237 | } |
238 | if (limitExpr != null) { |
239 | Value v = limitExpr.getValue(session); |
240 | if (v != ValueNull.INSTANCE) { |
241 | result.setLimit(v.getInt()); |
242 | } |
243 | } |
244 | l.close(); |
245 | r.close(); |
246 | result.done(); |
247 | if (target != null) { |
248 | while (result.next()) { |
249 | target.addRow(result.currentRow()); |
250 | } |
251 | result.close(); |
252 | return null; |
253 | } |
254 | return result; |
255 | } |
256 | |
257 | @Override |
258 | public void init() { |
259 | if (SysProperties.CHECK && checkInit) { |
260 | DbException.throwInternalError(); |
261 | } |
262 | checkInit = true; |
263 | left.init(); |
264 | right.init(); |
265 | int len = left.getColumnCount(); |
266 | if (len != right.getColumnCount()) { |
267 | throw DbException.get(ErrorCode.COLUMN_COUNT_DOES_NOT_MATCH); |
268 | } |
269 | ArrayList<Expression> le = left.getExpressions(); |
270 | // set the expressions to get the right column count and names, |
271 | // but can't validate at this time |
272 | expressions = New.arrayList(); |
273 | for (int i = 0; i < len; i++) { |
274 | Expression l = le.get(i); |
275 | expressions.add(l); |
276 | } |
277 | } |
278 | |
279 | @Override |
280 | public void prepare() { |
281 | if (isPrepared) { |
282 | // sometimes a subquery is prepared twice (CREATE TABLE AS SELECT) |
283 | return; |
284 | } |
285 | if (SysProperties.CHECK && !checkInit) { |
286 | DbException.throwInternalError("not initialized"); |
287 | } |
288 | isPrepared = true; |
289 | left.prepare(); |
290 | right.prepare(); |
291 | int len = left.getColumnCount(); |
292 | // set the correct expressions now |
293 | expressions = New.arrayList(); |
294 | ArrayList<Expression> le = left.getExpressions(); |
295 | ArrayList<Expression> re = right.getExpressions(); |
296 | for (int i = 0; i < len; i++) { |
297 | Expression l = le.get(i); |
298 | Expression r = re.get(i); |
299 | int type = Value.getHigherOrder(l.getType(), r.getType()); |
300 | long prec = Math.max(l.getPrecision(), r.getPrecision()); |
301 | int scale = Math.max(l.getScale(), r.getScale()); |
302 | int displaySize = Math.max(l.getDisplaySize(), r.getDisplaySize()); |
303 | Column col = new Column(l.getAlias(), type, prec, scale, displaySize); |
304 | Expression e = new ExpressionColumn(session.getDatabase(), col); |
305 | expressions.add(e); |
306 | } |
307 | if (orderList != null) { |
308 | initOrder(session, expressions, null, orderList, getColumnCount(), true, null); |
309 | sort = prepareOrder(orderList, expressions.size()); |
310 | orderList = null; |
311 | } |
312 | expressionArray = new Expression[expressions.size()]; |
313 | expressions.toArray(expressionArray); |
314 | } |
315 | |
316 | @Override |
317 | public double getCost() { |
318 | return left.getCost() + right.getCost(); |
319 | } |
320 | |
321 | @Override |
322 | public HashSet<Table> getTables() { |
323 | HashSet<Table> set = left.getTables(); |
324 | set.addAll(right.getTables()); |
325 | return set; |
326 | } |
327 | |
328 | @Override |
329 | public ArrayList<Expression> getExpressions() { |
330 | return expressions; |
331 | } |
332 | |
333 | @Override |
334 | public void setForUpdate(boolean forUpdate) { |
335 | left.setForUpdate(forUpdate); |
336 | right.setForUpdate(forUpdate); |
337 | isForUpdate = forUpdate; |
338 | } |
339 | |
340 | @Override |
341 | public int getColumnCount() { |
342 | return left.getColumnCount(); |
343 | } |
344 | |
345 | @Override |
346 | public void mapColumns(ColumnResolver resolver, int level) { |
347 | left.mapColumns(resolver, level); |
348 | right.mapColumns(resolver, level); |
349 | } |
350 | |
351 | @Override |
352 | public void setEvaluatable(TableFilter tableFilter, boolean b) { |
353 | left.setEvaluatable(tableFilter, b); |
354 | right.setEvaluatable(tableFilter, b); |
355 | } |
356 | |
357 | @Override |
358 | public void addGlobalCondition(Parameter param, int columnId, |
359 | int comparisonType) { |
360 | addParameter(param); |
361 | switch (unionType) { |
362 | case UNION_ALL: |
363 | case UNION: |
364 | case INTERSECT: { |
365 | left.addGlobalCondition(param, columnId, comparisonType); |
366 | right.addGlobalCondition(param, columnId, comparisonType); |
367 | break; |
368 | } |
369 | case EXCEPT: { |
370 | left.addGlobalCondition(param, columnId, comparisonType); |
371 | break; |
372 | } |
373 | default: |
374 | DbException.throwInternalError("type=" + unionType); |
375 | } |
376 | } |
377 | |
378 | @Override |
379 | public String getPlanSQL() { |
380 | StringBuilder buff = new StringBuilder(); |
381 | buff.append('(').append(left.getPlanSQL()).append(')'); |
382 | switch (unionType) { |
383 | case UNION_ALL: |
384 | buff.append("\nUNION ALL\n"); |
385 | break; |
386 | case UNION: |
387 | buff.append("\nUNION\n"); |
388 | break; |
389 | case INTERSECT: |
390 | buff.append("\nINTERSECT\n"); |
391 | break; |
392 | case EXCEPT: |
393 | buff.append("\nEXCEPT\n"); |
394 | break; |
395 | default: |
396 | DbException.throwInternalError("type=" + unionType); |
397 | } |
398 | buff.append('(').append(right.getPlanSQL()).append(')'); |
399 | Expression[] exprList = expressions.toArray(new Expression[expressions.size()]); |
400 | if (sort != null) { |
401 | buff.append("\nORDER BY ").append(sort.getSQL(exprList, exprList.length)); |
402 | } |
403 | if (limitExpr != null) { |
404 | buff.append("\nLIMIT ").append( |
405 | StringUtils.unEnclose(limitExpr.getSQL())); |
406 | if (offsetExpr != null) { |
407 | buff.append("\nOFFSET ").append( |
408 | StringUtils.unEnclose(offsetExpr.getSQL())); |
409 | } |
410 | } |
411 | if (sampleSizeExpr != null) { |
412 | buff.append("\nSAMPLE_SIZE ").append( |
413 | StringUtils.unEnclose(sampleSizeExpr.getSQL())); |
414 | } |
415 | if (isForUpdate) { |
416 | buff.append("\nFOR UPDATE"); |
417 | } |
418 | return buff.toString(); |
419 | } |
420 | |
421 | @Override |
422 | public LocalResult query(int limit, ResultTarget target) { |
423 | // union doesn't always know the parameter list of the left and right |
424 | // queries |
425 | return queryWithoutCache(limit, target); |
426 | } |
427 | |
428 | @Override |
429 | public boolean isEverything(ExpressionVisitor visitor) { |
430 | return left.isEverything(visitor) && right.isEverything(visitor); |
431 | } |
432 | |
433 | @Override |
434 | public boolean isReadOnly() { |
435 | return left.isReadOnly() && right.isReadOnly(); |
436 | } |
437 | |
438 | @Override |
439 | public void updateAggregate(Session s) { |
440 | left.updateAggregate(s); |
441 | right.updateAggregate(s); |
442 | } |
443 | |
444 | @Override |
445 | public void fireBeforeSelectTriggers() { |
446 | left.fireBeforeSelectTriggers(); |
447 | right.fireBeforeSelectTriggers(); |
448 | } |
449 | |
450 | @Override |
451 | public int getType() { |
452 | return CommandInterface.SELECT; |
453 | } |
454 | |
455 | @Override |
456 | public boolean allowGlobalConditions() { |
457 | return left.allowGlobalConditions() && right.allowGlobalConditions(); |
458 | } |
459 | |
460 | } |