001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (http://h2database.com/html/license.html).
004: * Initial Developer: H2 Group
005: */
006: package org.h2.expression;
007:
008: import java.sql.SQLException;
009: import java.util.Comparator;
010: import java.util.HashMap;
011:
012: import org.h2.command.dml.Select;
013: import org.h2.command.dml.SelectOrderBy;
014: import org.h2.constant.ErrorCode;
015: import org.h2.constant.SysProperties;
016: import org.h2.engine.Database;
017: import org.h2.engine.Session;
018: import org.h2.index.Index;
019: import org.h2.message.Message;
020: import org.h2.result.SearchRow;
021: import org.h2.result.SortOrder;
022: import org.h2.table.Column;
023: import org.h2.table.ColumnResolver;
024: import org.h2.table.Table;
025: import org.h2.table.TableFilter;
026: import org.h2.util.ObjectUtils;
027: import org.h2.util.ObjectArray;
028: import org.h2.util.StringUtils;
029: import org.h2.value.DataType;
030: import org.h2.value.Value;
031: import org.h2.value.ValueArray;
032: import org.h2.value.ValueBoolean;
033: import org.h2.value.ValueDouble;
034: import org.h2.value.ValueInt;
035: import org.h2.value.ValueLong;
036: import org.h2.value.ValueNull;
037: import org.h2.value.ValueString;
038:
039: /**
040: * Implements the integrated aggregate functions, such as COUNT, MAX, SUM.
041: */
042: public class Aggregate extends Expression {
043: // TODO incompatibility to hsqldb: aggregates: hsqldb uses automatic data
044: // type for sum if value is too big,
045: // h2 uses the same type as the data
046: public static final int COUNT_ALL = 0, COUNT = 1, SUM = 2, MIN = 3,
047: MAX = 4, AVG = 5;
048: public static final int GROUP_CONCAT = 6, STDDEV_POP = 7,
049: STDDEV_SAMP = 8;
050: public static final int VAR_POP = 9, VAR_SAMP = 10, SOME = 11,
051: EVERY = 12, SELECTIVITY = 13;
052:
053: private final Database database;
054: private final int type;
055: private final Select select;
056: private final boolean distinct;
057:
058: private Expression on;
059: private Expression separator;
060: private ObjectArray orderList;
061: private SortOrder sort;
062: private int dataType, scale;
063: private long precision;
064: private int displaySize;
065:
066: private static final HashMap AGGREGATES = new HashMap();
067:
068: public Aggregate(Database database, int type, Expression on,
069: Select select, boolean distinct) {
070: this .database = database;
071: this .type = type;
072: this .on = on;
073: this .select = select;
074: this .distinct = distinct;
075: }
076:
077: static {
078: addAggregate("COUNT", COUNT);
079: addAggregate("SUM", SUM);
080: addAggregate("MIN", MIN);
081: addAggregate("MAX", MAX);
082: addAggregate("AVG", AVG);
083: addAggregate("GROUP_CONCAT", GROUP_CONCAT);
084: addAggregate("STDDEV_SAMP", STDDEV_SAMP);
085: addAggregate("STDDEV", STDDEV_SAMP);
086: addAggregate("STDDEV_POP", STDDEV_POP);
087: addAggregate("STDDEVP", STDDEV_POP);
088: addAggregate("VAR_POP", VAR_POP);
089: addAggregate("VARP", VAR_POP);
090: addAggregate("VAR_SAMP", VAR_SAMP);
091: addAggregate("VAR", VAR_SAMP);
092: addAggregate("VARIANCE", VAR_SAMP);
093: addAggregate("SOME", SOME);
094: addAggregate("EVERY", EVERY);
095: addAggregate("SELECTIVITY", SELECTIVITY);
096: }
097:
098: private static void addAggregate(String name, int type) {
099: AGGREGATES.put(name, ObjectUtils.getInteger(type));
100: }
101:
102: public static int getAggregateType(String name) {
103: Integer type = (Integer) AGGREGATES.get(name);
104: return type == null ? -1 : type.intValue();
105: }
106:
107: public void setOrder(ObjectArray orderBy) {
108: this .orderList = orderBy;
109: }
110:
111: public void setSeparator(Expression separator) {
112: this .separator = separator;
113: }
114:
115: private SortOrder initOrder(Session session) throws SQLException {
116: int[] index = new int[orderList.size()];
117: int[] sortType = new int[orderList.size()];
118: for (int i = 0; i < orderList.size(); i++) {
119: SelectOrderBy o = (SelectOrderBy) orderList.get(i);
120: index[i] = i + 1;
121: int type = o.descending ? SortOrder.DESCENDING
122: : SortOrder.ASCENDING;
123: sortType[i] = type;
124: }
125: return new SortOrder(session.getDatabase(), index, sortType);
126: }
127:
128: public void updateAggregate(Session session) throws SQLException {
129: // TODO aggregates: check nested MIN(MAX(ID)) and so on
130: // if(on != null) {
131: // on.updateAggregate();
132: // }
133: HashMap group = select.getCurrentGroup();
134: if (group == null) {
135: // this is a different level (the enclosing query)
136: return;
137: }
138: AggregateData data = (AggregateData) group.get(this );
139: if (data == null) {
140: data = new AggregateData(type);
141: group.put(this , data);
142: }
143: Value v = on == null ? null : on.getValue(session);
144: if (type == GROUP_CONCAT) {
145: if (v != ValueNull.INSTANCE) {
146: v = v.convertTo(Value.STRING);
147: if (orderList != null) {
148: Value[] array = new Value[1 + orderList.size()];
149: array[0] = v;
150: for (int i = 0; i < orderList.size(); i++) {
151: SelectOrderBy o = (SelectOrderBy) orderList
152: .get(i);
153: array[i + 1] = o.expression.getValue(session);
154: }
155: v = ValueArray.get(array);
156: }
157: }
158: }
159: data.add(database, distinct, v);
160: }
161:
162: public Value getValue(Session session) throws SQLException {
163: if (select.isQuickQuery()) {
164: switch (type) {
165: case COUNT_ALL:
166: Table table = select.getTopTableFilter().getTable();
167: return ValueLong.get(table.getRowCount(session));
168: case MIN:
169: case MAX:
170: boolean first = type == MIN;
171: Index index = getColumnIndex(first);
172: int sortType = index.getIndexColumns()[0].sortType;
173: if ((sortType & SortOrder.DESCENDING) != 0) {
174: first = !first;
175: }
176: SearchRow row = index.findFirstOrLast(session, first);
177: Value v;
178: if (row == null) {
179: v = ValueNull.INSTANCE;
180: } else {
181: v = row.getValue(index.getColumns()[0]
182: .getColumnId());
183: }
184: return v;
185: default:
186: throw Message.getInternalError("type=" + type);
187: }
188: }
189: HashMap group = select.getCurrentGroup();
190: if (group == null) {
191: throw Message.getSQLException(
192: ErrorCode.INVALID_USE_OF_AGGREGATE_FUNCTION_1,
193: getSQL());
194: }
195: AggregateData data = (AggregateData) group.get(this );
196: if (data == null) {
197: data = new AggregateData(type);
198: }
199: Value v = data.getValue(database, distinct);
200: if (type == GROUP_CONCAT) {
201: ObjectArray list = data.getList();
202: if (list == null || list.size() == 0) {
203: return ValueNull.INSTANCE;
204: }
205: if (orderList != null) {
206: try {
207: // TODO refactor: don't use built in comparator
208: list.sort(new Comparator() {
209: public int compare(Object o1, Object o2) {
210: try {
211: Value[] a1 = ((ValueArray) o1)
212: .getList();
213: Value[] a2 = ((ValueArray) o2)
214: .getList();
215: return sort.compare(a1, a2);
216: } catch (SQLException e) {
217: throw Message.convertToInternal(e);
218: }
219: }
220: });
221: } catch (Error e) {
222: throw Message.convert(e);
223: }
224: }
225: StringBuffer buff = new StringBuffer();
226: String sep = separator == null ? "," : separator.getValue(
227: session).getString();
228: for (int i = 0; i < list.size(); i++) {
229: Value val = (Value) list.get(i);
230: String s;
231: if (val.getType() == Value.ARRAY) {
232: s = ((ValueArray) val).getList()[0].getString();
233: } else {
234: s = val.convertTo(Value.STRING).getString();
235: }
236: if (s == null) {
237: continue;
238: }
239: if (i > 0 && sep != null) {
240: buff.append(sep);
241: }
242: buff.append(s);
243: }
244: v = ValueString.get(buff.toString());
245: }
246: return v;
247: }
248:
249: public int getType() {
250: return dataType;
251: }
252:
253: public void mapColumns(ColumnResolver resolver, int level)
254: throws SQLException {
255: if (on != null) {
256: on.mapColumns(resolver, level);
257: }
258: if (orderList != null) {
259: for (int i = 0; i < orderList.size(); i++) {
260: SelectOrderBy o = (SelectOrderBy) orderList.get(i);
261: o.expression.mapColumns(resolver, level);
262: }
263: }
264: if (separator != null) {
265: separator.mapColumns(resolver, level);
266: }
267: }
268:
269: public Expression optimize(Session session) throws SQLException {
270: if (on != null) {
271: on = on.optimize(session);
272: dataType = on.getType();
273: scale = on.getScale();
274: precision = on.getPrecision();
275: displaySize = on.getDisplaySize();
276: }
277: if (orderList != null) {
278: for (int i = 0; i < orderList.size(); i++) {
279: SelectOrderBy o = (SelectOrderBy) orderList.get(i);
280: o.expression = o.expression.optimize(session);
281: }
282: sort = initOrder(session);
283: }
284: if (separator != null) {
285: separator = separator.optimize(session);
286: }
287: switch (type) {
288: case GROUP_CONCAT:
289: dataType = Value.STRING;
290: scale = 0;
291: precision = Integer.MAX_VALUE;
292: displaySize = Integer.MAX_VALUE;
293: break;
294: case COUNT_ALL:
295: case COUNT:
296: dataType = Value.LONG;
297: scale = 0;
298: precision = ValueLong.PRECISION;
299: displaySize = ValueLong.DISPLAY_SIZE;
300: break;
301: case SELECTIVITY:
302: dataType = Value.INT;
303: scale = 0;
304: precision = ValueInt.PRECISION;
305: displaySize = ValueInt.DISPLAY_SIZE;
306: break;
307: case SUM:
308: case AVG:
309: if (!DataType.supportsAdd(dataType)) {
310: throw Message.getSQLException(
311: ErrorCode.SUM_OR_AVG_ON_WRONG_DATATYPE_1,
312: getSQL());
313: }
314: break;
315: case MIN:
316: case MAX:
317: break;
318: case STDDEV_POP:
319: case STDDEV_SAMP:
320: case VAR_POP:
321: case VAR_SAMP:
322: dataType = Value.DOUBLE;
323: precision = ValueDouble.PRECISION;
324: displaySize = ValueDouble.DISPLAY_SIZE;
325: scale = 0;
326: break;
327: case EVERY:
328: case SOME:
329: dataType = Value.BOOLEAN;
330: precision = ValueBoolean.PRECISION;
331: displaySize = ValueBoolean.DISPLAY_SIZE;
332: scale = 0;
333: break;
334: default:
335: throw Message.getInternalError("type=" + type);
336: }
337: return this ;
338: }
339:
340: public void setEvaluatable(TableFilter tableFilter, boolean b) {
341: if (on != null) {
342: on.setEvaluatable(tableFilter, b);
343: }
344: if (orderList != null) {
345: for (int i = 0; i < orderList.size(); i++) {
346: SelectOrderBy o = (SelectOrderBy) orderList.get(i);
347: o.expression.setEvaluatable(tableFilter, b);
348: }
349: }
350: if (separator != null) {
351: separator.setEvaluatable(tableFilter, b);
352: }
353: }
354:
355: public int getScale() {
356: return scale;
357: }
358:
359: public long getPrecision() {
360: return precision;
361: }
362:
363: public int getDisplaySize() {
364: return displaySize;
365: }
366:
367: public String getSQL() {
368: String text;
369: switch (type) {
370: case GROUP_CONCAT: {
371: StringBuffer buff = new StringBuffer();
372: buff.append("GROUP_CONCAT(");
373: buff.append(on.getSQL());
374: if (orderList != null) {
375: buff.append(" ORDER BY ");
376: for (int i = 0; i < orderList.size(); i++) {
377: SelectOrderBy o = (SelectOrderBy) orderList.get(i);
378: if (i > 0) {
379: buff.append(", ");
380: }
381: buff.append(o.expression.getSQL());
382: if (o.descending) {
383: buff.append(" DESC");
384: }
385: }
386: }
387: if (separator != null) {
388: buff.append(" SEPARATOR ");
389: buff.append(separator.getSQL());
390: }
391: buff.append(")");
392: return buff.toString();
393: }
394: case COUNT_ALL:
395: return "COUNT(*)";
396: case COUNT:
397: text = "COUNT";
398: break;
399: case SELECTIVITY:
400: text = "SELECTIVITY";
401: break;
402: case SUM:
403: text = "SUM";
404: break;
405: case MIN:
406: text = "MIN";
407: break;
408: case MAX:
409: text = "MAX";
410: break;
411: case AVG:
412: text = "AVG";
413: break;
414: case STDDEV_POP:
415: text = "STDDEV_POP";
416: break;
417: case STDDEV_SAMP:
418: text = "STDDEV_SAMP";
419: break;
420: case VAR_POP:
421: text = "VAR_POP";
422: break;
423: case VAR_SAMP:
424: text = "VAR_SAMP";
425: break;
426: case EVERY:
427: text = "EVERY";
428: break;
429: case SOME:
430: text = "SOME";
431: break;
432: default:
433: throw Message.getInternalError("type=" + type);
434: }
435: if (distinct) {
436: return text + "(DISTINCT " + on.getSQL() + ")";
437: } else {
438: return text + StringUtils.enclose(on.getSQL());
439: }
440: }
441:
442: public int getAggregateType() {
443: return type;
444: }
445:
446: private Index getColumnIndex(boolean first) {
447: if (on instanceof ExpressionColumn) {
448: ExpressionColumn col = (ExpressionColumn) on;
449: Column column = col.getColumn();
450: TableFilter filter = col.getTableFilter();
451: if (filter != null) {
452: Table table = filter.getTable();
453: Index index = table.getIndexForColumn(column, first);
454: return index;
455: }
456: }
457: return null;
458: }
459:
460: public boolean isEverything(ExpressionVisitor visitor) {
461: if (visitor.type == ExpressionVisitor.OPTIMIZABLE_MIN_MAX_COUNT_ALL) {
462: switch (type) {
463: case COUNT_ALL:
464: return visitor.table.canGetRowCount();
465: case MIN:
466: case MAX:
467: if (!SysProperties.OPTIMIZE_MIN_MAX) {
468: return false;
469: }
470: boolean first = type == MIN;
471: Index index = getColumnIndex(first);
472: return index != null;
473: default:
474: return false;
475: }
476: }
477: if (on != null && !on.isEverything(visitor)) {
478: return false;
479: }
480: if (separator != null && !separator.isEverything(visitor)) {
481: return false;
482: }
483: for (int i = 0; orderList != null && i < orderList.size(); i++) {
484: SelectOrderBy o = (SelectOrderBy) orderList.get(i);
485: if (!o.expression.isEverything(visitor)) {
486: return false;
487: }
488: }
489: return true;
490: }
491:
492: public int getCost() {
493: return (on == null) ? 1 : on.getCost() + 1;
494: }
495:
496: }
|