File D:\maven-qcr-plugin-testsunitaires\qcr4java\h2-1.3.170-sources\target\site\qc\lecode\expression\Aggregate.java.html

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