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.Prepared; |
13 | import org.h2.engine.Database; |
14 | import org.h2.engine.Session; |
15 | import org.h2.expression.Alias; |
16 | import org.h2.expression.Expression; |
17 | import org.h2.expression.ExpressionColumn; |
18 | import org.h2.expression.ExpressionVisitor; |
19 | import org.h2.expression.Parameter; |
20 | import org.h2.expression.ValueExpression; |
21 | import org.h2.message.DbException; |
22 | import org.h2.result.LocalResult; |
23 | import org.h2.result.ResultTarget; |
24 | import org.h2.result.SortOrder; |
25 | import org.h2.table.ColumnResolver; |
26 | import org.h2.table.Table; |
27 | import org.h2.table.TableFilter; |
28 | import org.h2.util.New; |
29 | import org.h2.value.Value; |
30 | import org.h2.value.ValueInt; |
31 | import org.h2.value.ValueNull; |
32 | |
33 | /** |
34 | * Represents a SELECT statement (simple, or union). |
35 | */ |
36 | public abstract class Query extends Prepared { |
37 | |
38 | /** |
39 | * The limit expression as specified in the LIMIT or TOP clause. |
40 | */ |
41 | protected Expression limitExpr; |
42 | |
43 | /** |
44 | * The offset expression as specified in the LIMIT .. OFFSET clause. |
45 | */ |
46 | protected Expression offsetExpr; |
47 | |
48 | /** |
49 | * The sample size expression as specified in the SAMPLE_SIZE clause. |
50 | */ |
51 | protected Expression sampleSizeExpr; |
52 | |
53 | /** |
54 | * Whether the result must only contain distinct rows. |
55 | */ |
56 | protected boolean distinct; |
57 | |
58 | /** |
59 | * Whether the result needs to support random access. |
60 | */ |
61 | protected boolean randomAccessResult; |
62 | |
63 | private boolean noCache; |
64 | private int lastLimit; |
65 | private long lastEvaluated; |
66 | private LocalResult lastResult; |
67 | private Value[] lastParameters; |
68 | private boolean cacheableChecked; |
69 | |
70 | Query(Session session) { |
71 | super(session); |
72 | } |
73 | |
74 | /** |
75 | * Execute the query without checking the cache. If a target is specified, |
76 | * the results are written to it, and the method returns null. If no target |
77 | * is specified, a new LocalResult is created and returned. |
78 | * |
79 | * @param limit the limit as specified in the JDBC method call |
80 | * @param target the target to write results to |
81 | * @return the result |
82 | */ |
83 | protected abstract LocalResult queryWithoutCache(int limit, |
84 | ResultTarget target); |
85 | |
86 | /** |
87 | * Initialize the query. |
88 | */ |
89 | public abstract void init(); |
90 | |
91 | /** |
92 | * The the list of select expressions. |
93 | * This may include invisible expressions such as order by expressions. |
94 | * |
95 | * @return the list of expressions |
96 | */ |
97 | public abstract ArrayList<Expression> getExpressions(); |
98 | |
99 | /** |
100 | * Calculate the cost to execute this query. |
101 | * |
102 | * @return the cost |
103 | */ |
104 | public abstract double getCost(); |
105 | |
106 | /** |
107 | * Calculate the cost when used as a subquery. |
108 | * This method returns a value between 10 and 1000000, |
109 | * to ensure adding other values can't result in an integer overflow. |
110 | * |
111 | * @return the estimated cost as an integer |
112 | */ |
113 | public int getCostAsExpression() { |
114 | // ensure the cost is not larger than 1 million, |
115 | // so that adding other values can't overflow |
116 | return (int) Math.min(1000000.0, 10.0 + 10.0 * getCost()); |
117 | } |
118 | |
119 | /** |
120 | * Get all tables that are involved in this query. |
121 | * |
122 | * @return the set of tables |
123 | */ |
124 | public abstract HashSet<Table> getTables(); |
125 | |
126 | /** |
127 | * Set the order by list. |
128 | * |
129 | * @param order the order by list |
130 | */ |
131 | public abstract void setOrder(ArrayList<SelectOrderBy> order); |
132 | |
133 | /** |
134 | * Set the 'for update' flag. |
135 | * |
136 | * @param forUpdate the new setting |
137 | */ |
138 | public abstract void setForUpdate(boolean forUpdate); |
139 | |
140 | /** |
141 | * Get the column count of this query. |
142 | * |
143 | * @return the column count |
144 | */ |
145 | public abstract int getColumnCount(); |
146 | |
147 | /** |
148 | * Map the columns to the given column resolver. |
149 | * |
150 | * @param resolver |
151 | * the resolver |
152 | * @param level |
153 | * the subquery level (0 is the top level query, 1 is the first |
154 | * subquery level) |
155 | */ |
156 | public abstract void mapColumns(ColumnResolver resolver, int level); |
157 | |
158 | /** |
159 | * Change the evaluatable flag. This is used when building the execution |
160 | * plan. |
161 | * |
162 | * @param tableFilter the table filter |
163 | * @param b the new value |
164 | */ |
165 | public abstract void setEvaluatable(TableFilter tableFilter, boolean b); |
166 | |
167 | /** |
168 | * Add a condition to the query. This is used for views. |
169 | * |
170 | * @param param the parameter |
171 | * @param columnId the column index (0 meaning the first column) |
172 | * @param comparisonType the comparison type |
173 | */ |
174 | public abstract void addGlobalCondition(Parameter param, int columnId, |
175 | int comparisonType); |
176 | |
177 | /** |
178 | * Check whether adding condition to the query is allowed. This is not |
179 | * allowed for views that have an order by and a limit, as it would affect |
180 | * the returned results. |
181 | * |
182 | * @return true if adding global conditions is allowed |
183 | */ |
184 | public abstract boolean allowGlobalConditions(); |
185 | |
186 | /** |
187 | * Check if this expression and all sub-expressions can fulfill a criteria. |
188 | * If any part returns false, the result is false. |
189 | * |
190 | * @param visitor the visitor |
191 | * @return if the criteria can be fulfilled |
192 | */ |
193 | public abstract boolean isEverything(ExpressionVisitor visitor); |
194 | |
195 | /** |
196 | * Update all aggregate function values. |
197 | * |
198 | * @param s the session |
199 | */ |
200 | public abstract void updateAggregate(Session s); |
201 | |
202 | /** |
203 | * Call the before triggers on all tables. |
204 | */ |
205 | public abstract void fireBeforeSelectTriggers(); |
206 | |
207 | /** |
208 | * Set the distinct flag. |
209 | * |
210 | * @param b the new value |
211 | */ |
212 | public void setDistinct(boolean b) { |
213 | distinct = b; |
214 | } |
215 | |
216 | public boolean isDistinct() { |
217 | return distinct; |
218 | } |
219 | |
220 | /** |
221 | * Whether results need to support random access. |
222 | * |
223 | * @param b the new value |
224 | */ |
225 | public void setRandomAccessResult(boolean b) { |
226 | randomAccessResult = b; |
227 | } |
228 | |
229 | @Override |
230 | public boolean isQuery() { |
231 | return true; |
232 | } |
233 | |
234 | @Override |
235 | public boolean isTransactional() { |
236 | return true; |
237 | } |
238 | |
239 | /** |
240 | * Disable caching of result sets. |
241 | */ |
242 | public void disableCache() { |
243 | this.noCache = true; |
244 | } |
245 | |
246 | private boolean sameResultAsLast(Session s, Value[] params, |
247 | Value[] lastParams, long lastEval) { |
248 | if (!cacheableChecked) { |
249 | long max = getMaxDataModificationId(); |
250 | noCache = max == Long.MAX_VALUE; |
251 | cacheableChecked = true; |
252 | } |
253 | if (noCache) { |
254 | return false; |
255 | } |
256 | Database db = s.getDatabase(); |
257 | for (int i = 0; i < params.length; i++) { |
258 | Value a = lastParams[i], b = params[i]; |
259 | if (a.getType() != b.getType() || !db.areEqual(a, b)) { |
260 | return false; |
261 | } |
262 | } |
263 | if (!isEverything(ExpressionVisitor.DETERMINISTIC_VISITOR) || |
264 | !isEverything(ExpressionVisitor.INDEPENDENT_VISITOR)) { |
265 | return false; |
266 | } |
267 | if (db.getModificationDataId() > lastEval && |
268 | getMaxDataModificationId() > lastEval) { |
269 | return false; |
270 | } |
271 | return true; |
272 | } |
273 | |
274 | public final Value[] getParameterValues() { |
275 | ArrayList<Parameter> list = getParameters(); |
276 | if (list == null) { |
277 | list = New.arrayList(); |
278 | } |
279 | int size = list.size(); |
280 | Value[] params = new Value[size]; |
281 | for (int i = 0; i < size; i++) { |
282 | Value v = list.get(i).getParamValue(); |
283 | params[i] = v; |
284 | } |
285 | return params; |
286 | } |
287 | |
288 | @Override |
289 | public LocalResult query(int maxrows) { |
290 | return query(maxrows, null); |
291 | } |
292 | |
293 | /** |
294 | * Execute the query, writing the result to the target result. |
295 | * |
296 | * @param limit the maximum number of rows to return |
297 | * @param target the target result (null will return the result) |
298 | * @return the result set (if the target is not set). |
299 | */ |
300 | LocalResult query(int limit, ResultTarget target) { |
301 | fireBeforeSelectTriggers(); |
302 | if (noCache || !session.getDatabase().getOptimizeReuseResults()) { |
303 | return queryWithoutCache(limit, target); |
304 | } |
305 | Value[] params = getParameterValues(); |
306 | long now = session.getDatabase().getModificationDataId(); |
307 | if (isEverything(ExpressionVisitor.DETERMINISTIC_VISITOR)) { |
308 | if (lastResult != null && !lastResult.isClosed() && |
309 | limit == lastLimit) { |
310 | if (sameResultAsLast(session, params, lastParameters, |
311 | lastEvaluated)) { |
312 | lastResult = lastResult.createShallowCopy(session); |
313 | if (lastResult != null) { |
314 | lastResult.reset(); |
315 | return lastResult; |
316 | } |
317 | } |
318 | } |
319 | } |
320 | lastParameters = params; |
321 | closeLastResult(); |
322 | LocalResult r = queryWithoutCache(limit, target); |
323 | lastResult = r; |
324 | this.lastEvaluated = now; |
325 | lastLimit = limit; |
326 | return r; |
327 | } |
328 | |
329 | private void closeLastResult() { |
330 | if (lastResult != null) { |
331 | lastResult.close(); |
332 | } |
333 | } |
334 | |
335 | /** |
336 | * Initialize the order by list. This call may extend the expressions list. |
337 | * |
338 | * @param session the session |
339 | * @param expressions the select list expressions |
340 | * @param expressionSQL the select list SQL snippets |
341 | * @param orderList the order by list |
342 | * @param visible the number of visible columns in the select list |
343 | * @param mustBeInResult all order by expressions must be in the select list |
344 | * @param filters the table filters |
345 | */ |
346 | static void initOrder(Session session, |
347 | ArrayList<Expression> expressions, |
348 | ArrayList<String> expressionSQL, |
349 | ArrayList<SelectOrderBy> orderList, |
350 | int visible, |
351 | boolean mustBeInResult, |
352 | ArrayList<TableFilter> filters) { |
353 | Database db = session.getDatabase(); |
354 | for (SelectOrderBy o : orderList) { |
355 | Expression e = o.expression; |
356 | if (e == null) { |
357 | continue; |
358 | } |
359 | // special case: SELECT 1 AS A FROM DUAL ORDER BY A |
360 | // (oracle supports it, but only in order by, not in group by and |
361 | // not in having): |
362 | // SELECT 1 AS A FROM DUAL ORDER BY -A |
363 | boolean isAlias = false; |
364 | int idx = expressions.size(); |
365 | if (e instanceof ExpressionColumn) { |
366 | // order by expression |
367 | ExpressionColumn exprCol = (ExpressionColumn) e; |
368 | String tableAlias = exprCol.getOriginalTableAliasName(); |
369 | String col = exprCol.getOriginalColumnName(); |
370 | for (int j = 0; j < visible; j++) { |
371 | boolean found = false; |
372 | Expression ec = expressions.get(j); |
373 | if (ec instanceof ExpressionColumn) { |
374 | // select expression |
375 | ExpressionColumn c = (ExpressionColumn) ec; |
376 | found = db.equalsIdentifiers(col, c.getColumnName()); |
377 | if (found && tableAlias != null) { |
378 | String ca = c.getOriginalTableAliasName(); |
379 | if (ca == null) { |
380 | found = false; |
381 | if (filters != null) { |
382 | // select id from test order by test.id |
383 | for (int i = 0, size = filters.size(); i < size; i++) { |
384 | TableFilter f = filters.get(i); |
385 | if (db.equalsIdentifiers(f.getTableAlias(), tableAlias)) { |
386 | found = true; |
387 | break; |
388 | } |
389 | } |
390 | } |
391 | } else { |
392 | found = db.equalsIdentifiers(ca, tableAlias); |
393 | } |
394 | } |
395 | } else if (!(ec instanceof Alias)) { |
396 | continue; |
397 | } else if (tableAlias == null && db.equalsIdentifiers(col, ec.getAlias())) { |
398 | found = true; |
399 | } else { |
400 | Expression ec2 = ec.getNonAliasExpression(); |
401 | if (ec2 instanceof ExpressionColumn) { |
402 | ExpressionColumn c2 = (ExpressionColumn) ec2; |
403 | String ta = exprCol.getSQL(); |
404 | String tb = c2.getSQL(); |
405 | String s2 = c2.getColumnName(); |
406 | found = db.equalsIdentifiers(col, s2); |
407 | if (!db.equalsIdentifiers(ta, tb)) { |
408 | found = false; |
409 | } |
410 | } |
411 | } |
412 | if (found) { |
413 | idx = j; |
414 | isAlias = true; |
415 | break; |
416 | } |
417 | } |
418 | } else { |
419 | String s = e.getSQL(); |
420 | if (expressionSQL != null) { |
421 | for (int j = 0, size = expressionSQL.size(); j < size; j++) { |
422 | String s2 = expressionSQL.get(j); |
423 | if (db.equalsIdentifiers(s2, s)) { |
424 | idx = j; |
425 | isAlias = true; |
426 | break; |
427 | } |
428 | } |
429 | } |
430 | } |
431 | if (!isAlias) { |
432 | if (mustBeInResult) { |
433 | throw DbException.get(ErrorCode.ORDER_BY_NOT_IN_RESULT, |
434 | e.getSQL()); |
435 | } |
436 | expressions.add(e); |
437 | String sql = e.getSQL(); |
438 | expressionSQL.add(sql); |
439 | } |
440 | o.columnIndexExpr = ValueExpression.get(ValueInt.get(idx + 1)); |
441 | Expression expr = expressions.get(idx).getNonAliasExpression(); |
442 | o.expression = expr; |
443 | } |
444 | } |
445 | |
446 | /** |
447 | * Create a {@link SortOrder} object given the list of {@link SelectOrderBy} |
448 | * objects. The expression list is extended if necessary. |
449 | * |
450 | * @param orderList a list of {@link SelectOrderBy} elements |
451 | * @param expressionCount the number of columns in the query |
452 | * @return the {@link SortOrder} object |
453 | */ |
454 | public SortOrder prepareOrder(ArrayList<SelectOrderBy> orderList, |
455 | int expressionCount) { |
456 | int size = orderList.size(); |
457 | int[] index = new int[size]; |
458 | int[] sortType = new int[size]; |
459 | for (int i = 0; i < size; i++) { |
460 | SelectOrderBy o = orderList.get(i); |
461 | int idx; |
462 | boolean reverse = false; |
463 | Expression expr = o.columnIndexExpr; |
464 | Value v = expr.getValue(null); |
465 | if (v == ValueNull.INSTANCE) { |
466 | // parameter not yet set - order by first column |
467 | idx = 0; |
468 | } else { |
469 | idx = v.getInt(); |
470 | if (idx < 0) { |
471 | reverse = true; |
472 | idx = -idx; |
473 | } |
474 | idx -= 1; |
475 | if (idx < 0 || idx >= expressionCount) { |
476 | throw DbException.get(ErrorCode.ORDER_BY_NOT_IN_RESULT, "" + (idx + 1)); |
477 | } |
478 | } |
479 | index[i] = idx; |
480 | boolean desc = o.descending; |
481 | if (reverse) { |
482 | desc = !desc; |
483 | } |
484 | int type = desc ? SortOrder.DESCENDING : SortOrder.ASCENDING; |
485 | if (o.nullsFirst) { |
486 | type += SortOrder.NULLS_FIRST; |
487 | } else if (o.nullsLast) { |
488 | type += SortOrder.NULLS_LAST; |
489 | } |
490 | sortType[i] = type; |
491 | } |
492 | return new SortOrder(session.getDatabase(), index, sortType, orderList); |
493 | } |
494 | |
495 | public void setOffset(Expression offset) { |
496 | this.offsetExpr = offset; |
497 | } |
498 | |
499 | public Expression getOffset() { |
500 | return offsetExpr; |
501 | } |
502 | |
503 | public void setLimit(Expression limit) { |
504 | this.limitExpr = limit; |
505 | } |
506 | |
507 | public Expression getLimit() { |
508 | return limitExpr; |
509 | } |
510 | |
511 | /** |
512 | * Add a parameter to the parameter list. |
513 | * |
514 | * @param param the parameter to add |
515 | */ |
516 | void addParameter(Parameter param) { |
517 | if (parameters == null) { |
518 | parameters = New.arrayList(); |
519 | } |
520 | parameters.add(param); |
521 | } |
522 | |
523 | public void setSampleSize(Expression sampleSize) { |
524 | this.sampleSizeExpr = sampleSize; |
525 | } |
526 | |
527 | /** |
528 | * Get the sample size, if set. |
529 | * |
530 | * @param session the session |
531 | * @return the sample size |
532 | */ |
533 | int getSampleSizeValue(Session session) { |
534 | if (sampleSizeExpr == null) { |
535 | return 0; |
536 | } |
537 | Value v = sampleSizeExpr.optimize(session).getValue(session); |
538 | if (v == ValueNull.INSTANCE) { |
539 | return 0; |
540 | } |
541 | return v.getInt(); |
542 | } |
543 | |
544 | public final long getMaxDataModificationId() { |
545 | ExpressionVisitor visitor = ExpressionVisitor.getMaxModificationIdVisitor(); |
546 | isEverything(visitor); |
547 | return visitor.getMaxDataModificationId(); |
548 | } |
549 | |
550 | } |