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.expression; |
7 | |
8 | import java.util.ArrayList; |
9 | import java.util.Collections; |
10 | import java.util.Comparator; |
11 | import java.util.HashMap; |
12 | |
13 | import org.h2.api.ErrorCode; |
14 | import org.h2.command.dml.Select; |
15 | import org.h2.command.dml.SelectOrderBy; |
16 | import org.h2.engine.Session; |
17 | import org.h2.index.Cursor; |
18 | import org.h2.index.Index; |
19 | import org.h2.message.DbException; |
20 | import org.h2.result.SearchRow; |
21 | import org.h2.result.SortOrder; |
22 | import org.h2.table.Column; |
23 | import org.h2.table.ColumnResolver; |
24 | import org.h2.table.Table; |
25 | import org.h2.table.TableFilter; |
26 | import org.h2.util.New; |
27 | import org.h2.util.StatementBuilder; |
28 | import org.h2.util.StringUtils; |
29 | import org.h2.value.DataType; |
30 | import org.h2.value.Value; |
31 | import org.h2.value.ValueArray; |
32 | import org.h2.value.ValueBoolean; |
33 | import org.h2.value.ValueDouble; |
34 | import org.h2.value.ValueInt; |
35 | import org.h2.value.ValueLong; |
36 | import org.h2.value.ValueNull; |
37 | import org.h2.value.ValueString; |
38 | |
39 | /** |
40 | * Implements the integrated aggregate functions, such as COUNT, MAX, SUM. |
41 | */ |
42 | public class Aggregate extends Expression { |
43 | |
44 | /** |
45 | * The aggregate type for COUNT(*). |
46 | */ |
47 | public static final int COUNT_ALL = 0; |
48 | |
49 | /** |
50 | * The aggregate type for COUNT(expression). |
51 | */ |
52 | public static final int COUNT = 1; |
53 | |
54 | /** |
55 | * The aggregate type for GROUP_CONCAT(...). |
56 | */ |
57 | public static final int GROUP_CONCAT = 2; |
58 | |
59 | /** |
60 | * The aggregate type for SUM(expression). |
61 | */ |
62 | static final int SUM = 3; |
63 | |
64 | /** |
65 | * The aggregate type for MIN(expression). |
66 | */ |
67 | static final int MIN = 4; |
68 | |
69 | /** |
70 | * The aggregate type for MAX(expression). |
71 | */ |
72 | static final int MAX = 5; |
73 | |
74 | /** |
75 | * The aggregate type for AVG(expression). |
76 | */ |
77 | static final int AVG = 6; |
78 | |
79 | /** |
80 | * The aggregate type for STDDEV_POP(expression). |
81 | */ |
82 | static final int STDDEV_POP = 7; |
83 | |
84 | /** |
85 | * The aggregate type for STDDEV_SAMP(expression). |
86 | */ |
87 | static final int STDDEV_SAMP = 8; |
88 | |
89 | /** |
90 | * The aggregate type for VAR_POP(expression). |
91 | */ |
92 | static final int VAR_POP = 9; |
93 | |
94 | /** |
95 | * The aggregate type for VAR_SAMP(expression). |
96 | */ |
97 | static final int VAR_SAMP = 10; |
98 | |
99 | /** |
100 | * The aggregate type for BOOL_OR(expression). |
101 | */ |
102 | static final int BOOL_OR = 11; |
103 | |
104 | /** |
105 | * The aggregate type for BOOL_AND(expression). |
106 | */ |
107 | static final int BOOL_AND = 12; |
108 | |
109 | /** |
110 | * The aggregate type for BOOL_OR(expression). |
111 | */ |
112 | static final int BIT_OR = 13; |
113 | |
114 | /** |
115 | * The aggregate type for BOOL_AND(expression). |
116 | */ |
117 | static final int BIT_AND = 14; |
118 | |
119 | /** |
120 | * The aggregate type for SELECTIVITY(expression). |
121 | */ |
122 | static final int SELECTIVITY = 15; |
123 | |
124 | /** |
125 | * The aggregate type for HISTOGRAM(expression). |
126 | */ |
127 | static final int HISTOGRAM = 16; |
128 | |
129 | private static final HashMap<String, Integer> AGGREGATES = New.hashMap(); |
130 | |
131 | private final int type; |
132 | private final Select select; |
133 | private final boolean distinct; |
134 | |
135 | private Expression on; |
136 | private Expression groupConcatSeparator; |
137 | private ArrayList<SelectOrderBy> groupConcatOrderList; |
138 | private SortOrder groupConcatSort; |
139 | private int dataType, scale; |
140 | private long precision; |
141 | private int displaySize; |
142 | private int lastGroupRowId; |
143 | |
144 | /** |
145 | * Create a new aggregate object. |
146 | * |
147 | * @param type the aggregate type |
148 | * @param on the aggregated expression |
149 | * @param select the select statement |
150 | * @param distinct if distinct is used |
151 | */ |
152 | public Aggregate(int type, Expression on, Select select, boolean distinct) { |
153 | this.type = type; |
154 | this.on = on; |
155 | this.select = select; |
156 | this.distinct = distinct; |
157 | } |
158 | |
159 | static { |
160 | addAggregate("COUNT", COUNT); |
161 | addAggregate("SUM", SUM); |
162 | addAggregate("MIN", MIN); |
163 | addAggregate("MAX", MAX); |
164 | addAggregate("AVG", AVG); |
165 | addAggregate("GROUP_CONCAT", GROUP_CONCAT); |
166 | addAggregate("STDDEV_SAMP", STDDEV_SAMP); |
167 | addAggregate("STDDEV", STDDEV_SAMP); |
168 | addAggregate("STDDEV_POP", STDDEV_POP); |
169 | addAggregate("STDDEVP", STDDEV_POP); |
170 | addAggregate("VAR_POP", VAR_POP); |
171 | addAggregate("VARP", VAR_POP); |
172 | addAggregate("VAR_SAMP", VAR_SAMP); |
173 | addAggregate("VAR", VAR_SAMP); |
174 | addAggregate("VARIANCE", VAR_SAMP); |
175 | addAggregate("BOOL_OR", BOOL_OR); |
176 | // HSQLDB compatibility, but conflicts with x > EVERY(...) |
177 | addAggregate("SOME", BOOL_OR); |
178 | addAggregate("BOOL_AND", BOOL_AND); |
179 | // HSQLDB compatibility, but conflicts with x > SOME(...) |
180 | addAggregate("EVERY", BOOL_AND); |
181 | addAggregate("SELECTIVITY", SELECTIVITY); |
182 | addAggregate("HISTOGRAM", HISTOGRAM); |
183 | addAggregate("BIT_OR", BIT_OR); |
184 | addAggregate("BIT_AND", BIT_AND); |
185 | } |
186 | |
187 | private static void addAggregate(String name, int type) { |
188 | AGGREGATES.put(name, type); |
189 | } |
190 | |
191 | /** |
192 | * Get the aggregate type for this name, or -1 if no aggregate has been |
193 | * found. |
194 | * |
195 | * @param name the aggregate function name |
196 | * @return -1 if no aggregate function has been found, or the aggregate type |
197 | */ |
198 | public static int getAggregateType(String name) { |
199 | Integer type = AGGREGATES.get(name); |
200 | return type == null ? -1 : type.intValue(); |
201 | } |
202 | |
203 | /** |
204 | * Set the order for GROUP_CONCAT() aggregate. |
205 | * |
206 | * @param orderBy the order by list |
207 | */ |
208 | public void setGroupConcatOrder(ArrayList<SelectOrderBy> orderBy) { |
209 | this.groupConcatOrderList = orderBy; |
210 | } |
211 | |
212 | /** |
213 | * Set the separator for the GROUP_CONCAT() aggregate. |
214 | * |
215 | * @param separator the separator expression |
216 | */ |
217 | public void setGroupConcatSeparator(Expression separator) { |
218 | this.groupConcatSeparator = separator; |
219 | } |
220 | |
221 | private SortOrder initOrder(Session session) { |
222 | int size = groupConcatOrderList.size(); |
223 | int[] index = new int[size]; |
224 | int[] sortType = new int[size]; |
225 | for (int i = 0; i < size; i++) { |
226 | SelectOrderBy o = groupConcatOrderList.get(i); |
227 | index[i] = i + 1; |
228 | int order = o.descending ? SortOrder.DESCENDING : SortOrder.ASCENDING; |
229 | sortType[i] = order; |
230 | } |
231 | return new SortOrder(session.getDatabase(), index, sortType, null); |
232 | } |
233 | |
234 | @Override |
235 | public void updateAggregate(Session session) { |
236 | // TODO aggregates: check nested MIN(MAX(ID)) and so on |
237 | // if (on != null) { |
238 | // on.updateAggregate(); |
239 | // } |
240 | HashMap<Expression, Object> group = select.getCurrentGroup(); |
241 | if (group == null) { |
242 | // this is a different level (the enclosing query) |
243 | return; |
244 | } |
245 | |
246 | int groupRowId = select.getCurrentGroupRowId(); |
247 | if (lastGroupRowId == groupRowId) { |
248 | // already visited |
249 | return; |
250 | } |
251 | lastGroupRowId = groupRowId; |
252 | |
253 | AggregateData data = (AggregateData) group.get(this); |
254 | if (data == null) { |
255 | data = AggregateData.create(type); |
256 | group.put(this, data); |
257 | } |
258 | Value v = on == null ? null : on.getValue(session); |
259 | if (type == GROUP_CONCAT) { |
260 | if (v != ValueNull.INSTANCE) { |
261 | v = v.convertTo(Value.STRING); |
262 | if (groupConcatOrderList != null) { |
263 | int size = groupConcatOrderList.size(); |
264 | Value[] array = new Value[1 + size]; |
265 | array[0] = v; |
266 | for (int i = 0; i < size; i++) { |
267 | SelectOrderBy o = groupConcatOrderList.get(i); |
268 | array[i + 1] = o.expression.getValue(session); |
269 | } |
270 | v = ValueArray.get(array); |
271 | } |
272 | } |
273 | } |
274 | data.add(session.getDatabase(), dataType, distinct, v); |
275 | } |
276 | |
277 | @Override |
278 | public Value getValue(Session session) { |
279 | if (select.isQuickAggregateQuery()) { |
280 | switch (type) { |
281 | case COUNT: |
282 | case COUNT_ALL: |
283 | Table table = select.getTopTableFilter().getTable(); |
284 | return ValueLong.get(table.getRowCount(session)); |
285 | case MIN: |
286 | case MAX: |
287 | boolean first = type == MIN; |
288 | Index index = getColumnIndex(); |
289 | int sortType = index.getIndexColumns()[0].sortType; |
290 | if ((sortType & SortOrder.DESCENDING) != 0) { |
291 | first = !first; |
292 | } |
293 | Cursor cursor = index.findFirstOrLast(session, first); |
294 | SearchRow row = cursor.getSearchRow(); |
295 | Value v; |
296 | if (row == null) { |
297 | v = ValueNull.INSTANCE; |
298 | } else { |
299 | v = row.getValue(index.getColumns()[0].getColumnId()); |
300 | } |
301 | return v; |
302 | default: |
303 | DbException.throwInternalError("type=" + type); |
304 | } |
305 | } |
306 | HashMap<Expression, Object> group = select.getCurrentGroup(); |
307 | if (group == null) { |
308 | throw DbException.get(ErrorCode.INVALID_USE_OF_AGGREGATE_FUNCTION_1, getSQL()); |
309 | } |
310 | AggregateData data = (AggregateData) group.get(this); |
311 | if (data == null) { |
312 | data = AggregateData.create(type); |
313 | } |
314 | Value v = data.getValue(session.getDatabase(), dataType, distinct); |
315 | if (type == GROUP_CONCAT) { |
316 | ArrayList<Value> list = ((AggregateDataGroupConcat) data).getList(); |
317 | if (list == null || list.size() == 0) { |
318 | return ValueNull.INSTANCE; |
319 | } |
320 | if (groupConcatOrderList != null) { |
321 | final SortOrder sortOrder = groupConcatSort; |
322 | Collections.sort(list, new Comparator<Value>() { |
323 | @Override |
324 | public int compare(Value v1, Value v2) { |
325 | Value[] a1 = ((ValueArray) v1).getList(); |
326 | Value[] a2 = ((ValueArray) v2).getList(); |
327 | return sortOrder.compare(a1, a2); |
328 | } |
329 | }); |
330 | } |
331 | StatementBuilder buff = new StatementBuilder(); |
332 | String sep = groupConcatSeparator == null ? |
333 | "," : groupConcatSeparator.getValue(session).getString(); |
334 | for (Value val : list) { |
335 | String s; |
336 | if (val.getType() == Value.ARRAY) { |
337 | s = ((ValueArray) val).getList()[0].getString(); |
338 | } else { |
339 | s = val.getString(); |
340 | } |
341 | if (s == null) { |
342 | continue; |
343 | } |
344 | if (sep != null) { |
345 | buff.appendExceptFirst(sep); |
346 | } |
347 | buff.append(s); |
348 | } |
349 | v = ValueString.get(buff.toString()); |
350 | } |
351 | return v; |
352 | } |
353 | |
354 | @Override |
355 | public int getType() { |
356 | return dataType; |
357 | } |
358 | |
359 | @Override |
360 | public void mapColumns(ColumnResolver resolver, int level) { |
361 | if (on != null) { |
362 | on.mapColumns(resolver, level); |
363 | } |
364 | if (groupConcatOrderList != null) { |
365 | for (SelectOrderBy o : groupConcatOrderList) { |
366 | o.expression.mapColumns(resolver, level); |
367 | } |
368 | } |
369 | if (groupConcatSeparator != null) { |
370 | groupConcatSeparator.mapColumns(resolver, level); |
371 | } |
372 | } |
373 | |
374 | @Override |
375 | public Expression optimize(Session session) { |
376 | if (on != null) { |
377 | on = on.optimize(session); |
378 | dataType = on.getType(); |
379 | scale = on.getScale(); |
380 | precision = on.getPrecision(); |
381 | displaySize = on.getDisplaySize(); |
382 | } |
383 | if (groupConcatOrderList != null) { |
384 | for (SelectOrderBy o : groupConcatOrderList) { |
385 | o.expression = o.expression.optimize(session); |
386 | } |
387 | groupConcatSort = initOrder(session); |
388 | } |
389 | if (groupConcatSeparator != null) { |
390 | groupConcatSeparator = groupConcatSeparator.optimize(session); |
391 | } |
392 | switch (type) { |
393 | case GROUP_CONCAT: |
394 | dataType = Value.STRING; |
395 | scale = 0; |
396 | precision = displaySize = Integer.MAX_VALUE; |
397 | break; |
398 | case COUNT_ALL: |
399 | case COUNT: |
400 | dataType = Value.LONG; |
401 | scale = 0; |
402 | precision = ValueLong.PRECISION; |
403 | displaySize = ValueLong.DISPLAY_SIZE; |
404 | break; |
405 | case SELECTIVITY: |
406 | dataType = Value.INT; |
407 | scale = 0; |
408 | precision = ValueInt.PRECISION; |
409 | displaySize = ValueInt.DISPLAY_SIZE; |
410 | break; |
411 | case HISTOGRAM: |
412 | dataType = Value.ARRAY; |
413 | scale = 0; |
414 | precision = displaySize = Integer.MAX_VALUE; |
415 | break; |
416 | case SUM: |
417 | if (dataType == Value.BOOLEAN) { |
418 | // example: sum(id > 3) (count the rows) |
419 | dataType = Value.LONG; |
420 | } else if (!DataType.supportsAdd(dataType)) { |
421 | throw DbException.get(ErrorCode.SUM_OR_AVG_ON_WRONG_DATATYPE_1, getSQL()); |
422 | } else { |
423 | dataType = DataType.getAddProofType(dataType); |
424 | } |
425 | break; |
426 | case AVG: |
427 | if (!DataType.supportsAdd(dataType)) { |
428 | throw DbException.get(ErrorCode.SUM_OR_AVG_ON_WRONG_DATATYPE_1, getSQL()); |
429 | } |
430 | break; |
431 | case MIN: |
432 | case MAX: |
433 | break; |
434 | case STDDEV_POP: |
435 | case STDDEV_SAMP: |
436 | case VAR_POP: |
437 | case VAR_SAMP: |
438 | dataType = Value.DOUBLE; |
439 | precision = ValueDouble.PRECISION; |
440 | displaySize = ValueDouble.DISPLAY_SIZE; |
441 | scale = 0; |
442 | break; |
443 | case BOOL_AND: |
444 | case BOOL_OR: |
445 | dataType = Value.BOOLEAN; |
446 | precision = ValueBoolean.PRECISION; |
447 | displaySize = ValueBoolean.DISPLAY_SIZE; |
448 | scale = 0; |
449 | break; |
450 | case BIT_AND: |
451 | case BIT_OR: |
452 | if (!DataType.supportsAdd(dataType)) { |
453 | throw DbException.get(ErrorCode.SUM_OR_AVG_ON_WRONG_DATATYPE_1, getSQL()); |
454 | } |
455 | break; |
456 | default: |
457 | DbException.throwInternalError("type=" + type); |
458 | } |
459 | return this; |
460 | } |
461 | |
462 | @Override |
463 | public void setEvaluatable(TableFilter tableFilter, boolean b) { |
464 | if (on != null) { |
465 | on.setEvaluatable(tableFilter, b); |
466 | } |
467 | if (groupConcatOrderList != null) { |
468 | for (SelectOrderBy o : groupConcatOrderList) { |
469 | o.expression.setEvaluatable(tableFilter, b); |
470 | } |
471 | } |
472 | if (groupConcatSeparator != null) { |
473 | groupConcatSeparator.setEvaluatable(tableFilter, b); |
474 | } |
475 | } |
476 | |
477 | @Override |
478 | public int getScale() { |
479 | return scale; |
480 | } |
481 | |
482 | @Override |
483 | public long getPrecision() { |
484 | return precision; |
485 | } |
486 | |
487 | @Override |
488 | public int getDisplaySize() { |
489 | return displaySize; |
490 | } |
491 | |
492 | private String getSQLGroupConcat() { |
493 | StatementBuilder buff = new StatementBuilder("GROUP_CONCAT("); |
494 | if (distinct) { |
495 | buff.append("DISTINCT "); |
496 | } |
497 | buff.append(on.getSQL()); |
498 | if (groupConcatOrderList != null) { |
499 | buff.append(" ORDER BY "); |
500 | for (SelectOrderBy o : groupConcatOrderList) { |
501 | buff.appendExceptFirst(", "); |
502 | buff.append(o.expression.getSQL()); |
503 | if (o.descending) { |
504 | buff.append(" DESC"); |
505 | } |
506 | } |
507 | } |
508 | if (groupConcatSeparator != null) { |
509 | buff.append(" SEPARATOR ").append(groupConcatSeparator.getSQL()); |
510 | } |
511 | return buff.append(')').toString(); |
512 | } |
513 | |
514 | @Override |
515 | public String getSQL() { |
516 | String text; |
517 | switch (type) { |
518 | case GROUP_CONCAT: |
519 | return getSQLGroupConcat(); |
520 | case COUNT_ALL: |
521 | return "COUNT(*)"; |
522 | case COUNT: |
523 | text = "COUNT"; |
524 | break; |
525 | case SELECTIVITY: |
526 | text = "SELECTIVITY"; |
527 | break; |
528 | case HISTOGRAM: |
529 | text = "HISTOGRAM"; |
530 | break; |
531 | case SUM: |
532 | text = "SUM"; |
533 | break; |
534 | case MIN: |
535 | text = "MIN"; |
536 | break; |
537 | case MAX: |
538 | text = "MAX"; |
539 | break; |
540 | case AVG: |
541 | text = "AVG"; |
542 | break; |
543 | case STDDEV_POP: |
544 | text = "STDDEV_POP"; |
545 | break; |
546 | case STDDEV_SAMP: |
547 | text = "STDDEV_SAMP"; |
548 | break; |
549 | case VAR_POP: |
550 | text = "VAR_POP"; |
551 | break; |
552 | case VAR_SAMP: |
553 | text = "VAR_SAMP"; |
554 | break; |
555 | case BOOL_AND: |
556 | text = "BOOL_AND"; |
557 | break; |
558 | case BOOL_OR: |
559 | text = "BOOL_OR"; |
560 | break; |
561 | case BIT_AND: |
562 | text = "BIT_AND"; |
563 | break; |
564 | case BIT_OR: |
565 | text = "BIT_OR"; |
566 | break; |
567 | default: |
568 | throw DbException.throwInternalError("type=" + type); |
569 | } |
570 | if (distinct) { |
571 | return text + "(DISTINCT " + on.getSQL() + ")"; |
572 | } |
573 | return text + StringUtils.enclose(on.getSQL()); |
574 | } |
575 | |
576 | private Index getColumnIndex() { |
577 | if (on instanceof ExpressionColumn) { |
578 | ExpressionColumn col = (ExpressionColumn) on; |
579 | Column column = col.getColumn(); |
580 | TableFilter filter = col.getTableFilter(); |
581 | if (filter != null) { |
582 | Table table = filter.getTable(); |
583 | Index index = table.getIndexForColumn(column); |
584 | return index; |
585 | } |
586 | } |
587 | return null; |
588 | } |
589 | |
590 | @Override |
591 | public boolean isEverything(ExpressionVisitor visitor) { |
592 | if (visitor.getType() == ExpressionVisitor.OPTIMIZABLE_MIN_MAX_COUNT_ALL) { |
593 | switch (type) { |
594 | case COUNT: |
595 | if (!distinct && on.getNullable() == Column.NOT_NULLABLE) { |
596 | return visitor.getTable().canGetRowCount(); |
597 | } |
598 | return false; |
599 | case COUNT_ALL: |
600 | return visitor.getTable().canGetRowCount(); |
601 | case MIN: |
602 | case MAX: |
603 | Index index = getColumnIndex(); |
604 | return index != null; |
605 | default: |
606 | return false; |
607 | } |
608 | } |
609 | if (on != null && !on.isEverything(visitor)) { |
610 | return false; |
611 | } |
612 | if (groupConcatSeparator != null && |
613 | !groupConcatSeparator.isEverything(visitor)) { |
614 | return false; |
615 | } |
616 | if (groupConcatOrderList != null) { |
617 | for (int i = 0, size = groupConcatOrderList.size(); i < size; i++) { |
618 | SelectOrderBy o = groupConcatOrderList.get(i); |
619 | if (!o.expression.isEverything(visitor)) { |
620 | return false; |
621 | } |
622 | } |
623 | } |
624 | return true; |
625 | } |
626 | |
627 | @Override |
628 | public int getCost() { |
629 | return (on == null) ? 1 : on.getCost() + 1; |
630 | } |
631 | |
632 | } |