0001: /*
0002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
0003: * (http://h2database.com/html/license.html).
0004: * Initial Developer: H2 Group
0005: */
0006: package org.h2.command.dml;
0007:
0008: import java.sql.SQLException;
0009: import java.util.Arrays;
0010: import java.util.HashMap;
0011: import java.util.HashSet;
0012: import org.h2.constant.ErrorCode;
0013: import org.h2.constant.SysProperties;
0014: import org.h2.engine.Constants;
0015: import org.h2.engine.Session;
0016: import org.h2.expression.Alias;
0017: import org.h2.expression.Comparison;
0018: import org.h2.expression.ConditionAndOr;
0019: import org.h2.expression.Expression;
0020: import org.h2.expression.ExpressionColumn;
0021: import org.h2.expression.ExpressionVisitor;
0022: import org.h2.expression.Parameter;
0023: import org.h2.expression.Wildcard;
0024: import org.h2.index.Cursor;
0025: import org.h2.index.Index;
0026: import org.h2.index.IndexType;
0027: import org.h2.message.Message;
0028: import org.h2.result.LocalResult;
0029: import org.h2.result.SearchRow;
0030: import org.h2.result.SortOrder;
0031: import org.h2.table.Column;
0032: import org.h2.table.ColumnResolver;
0033: import org.h2.table.IndexColumn;
0034: import org.h2.table.Table;
0035: import org.h2.table.TableFilter;
0036: import org.h2.util.ObjectArray;
0037: import org.h2.util.StringUtils;
0038: import org.h2.util.ValueHashMap;
0039: import org.h2.value.Value;
0040: import org.h2.value.ValueArray;
0041: import org.h2.value.ValueNull;
0042:
0043: /**
0044: * This class represents a simple SELECT statement.
0045: *
0046: * For each select statement,
0047: * visibleColumnCount <= distinctColumnCount <= expressionCount.
0048: * The expression list count could include ORDER BY and GROUP BY expressions
0049: * that are not in the select list.
0050: *
0051: * The call sequence is init(), mapColumns() if it's a subquery, prepare().
0052: *
0053: * @author Thomas Mueller
0054: * @author Joel Turkel (Group sorted query)
0055: */
0056: public class Select extends Query {
0057: private TableFilter topTableFilter;
0058: private ObjectArray filters = new ObjectArray();
0059: private ObjectArray topFilters = new ObjectArray();
0060: private ObjectArray expressions;
0061: private Expression having;
0062: private Expression condition;
0063: private int visibleColumnCount, distinctColumnCount;
0064: private ObjectArray orderList;
0065: private ObjectArray group;
0066: private int[] groupIndex;
0067: private boolean[] groupByExpression;
0068: private boolean distinct;
0069: private HashMap currentGroup;
0070: private int havingIndex;
0071: private boolean isGroupQuery, isGroupSortedQuery;
0072: private boolean isForUpdate;
0073: private double cost;
0074: private boolean isQuickQuery, isDistinctQuery;
0075: private boolean isPrepared, checkInit;
0076: private boolean sortUsingIndex;
0077: private SortOrder sort;
0078:
0079: public Select(Session session) {
0080: super (session);
0081: }
0082:
0083: /**
0084: * Add a table to the query.
0085: *
0086: * @param filter the table to add
0087: * @param isTop if the table can be the first table in the query plan
0088: */
0089: public void addTableFilter(TableFilter filter, boolean isTop) {
0090: // TODO compatibility: it seems oracle doesn't check on
0091: // duplicate aliases; do other databases check it?
0092: // String alias = filter.getAlias();
0093: // if(filterNames.contains(alias)) {
0094: // throw Message.getSQLException(
0095: // ErrorCode.DUPLICATE_TABLE_ALIAS, alias);
0096: // }
0097: // filterNames.add(alias);
0098: filters.add(filter);
0099: if (isTop) {
0100: topFilters.add(filter);
0101: }
0102: }
0103:
0104: public ObjectArray getTopFilters() {
0105: return topFilters;
0106: }
0107:
0108: public void setExpressions(ObjectArray expressions) {
0109: this .expressions = expressions;
0110: }
0111:
0112: public void setGroupQuery() {
0113: isGroupQuery = true;
0114: }
0115:
0116: public void setGroupBy(ObjectArray group) {
0117: this .group = group;
0118: }
0119:
0120: public HashMap getCurrentGroup() {
0121: return currentGroup;
0122: }
0123:
0124: public void setOrder(ObjectArray order) {
0125: orderList = order;
0126: }
0127:
0128: /**
0129: * Add a condition to the list of conditions.
0130: *
0131: * @param cond the condition to add
0132: */
0133: public void addCondition(Expression cond) {
0134: if (condition == null) {
0135: condition = cond;
0136: } else {
0137: condition = new ConditionAndOr(ConditionAndOr.AND, cond,
0138: condition);
0139: }
0140: }
0141:
0142: private void queryGroupSorted(int columnCount, LocalResult result)
0143: throws SQLException {
0144: int rowNumber = 0;
0145: setCurrentRowNumber(0);
0146: Value[] previousKeyValues = null;
0147: while (topTableFilter.next()) {
0148: checkCancelled();
0149: setCurrentRowNumber(rowNumber + 1);
0150: if (condition == null
0151: || Boolean.TRUE.equals(condition
0152: .getBooleanValue(session))) {
0153: rowNumber++;
0154: Value[] keyValues = new Value[groupIndex.length];
0155: // update group
0156: for (int i = 0; i < groupIndex.length; i++) {
0157: int idx = groupIndex[i];
0158: Expression expr = (Expression) expressions.get(idx);
0159: keyValues[i] = expr.getValue(session);
0160: }
0161:
0162: if (previousKeyValues == null) {
0163: previousKeyValues = keyValues;
0164: currentGroup = new HashMap();
0165: } else if (!Arrays.equals(previousKeyValues, keyValues)) {
0166: addGroupSortedRow(previousKeyValues, columnCount,
0167: result);
0168: previousKeyValues = keyValues;
0169: currentGroup = new HashMap();
0170: }
0171:
0172: for (int i = 0; i < columnCount; i++) {
0173: if (groupByExpression == null
0174: || !groupByExpression[i]) {
0175: Expression expr = (Expression) expressions
0176: .get(i);
0177: expr.updateAggregate(session);
0178: }
0179: }
0180: }
0181: }
0182: if (previousKeyValues != null) {
0183: addGroupSortedRow(previousKeyValues, columnCount, result);
0184: }
0185: }
0186:
0187: private void addGroupSortedRow(Value[] keyValues, int columnCount,
0188: LocalResult result) throws SQLException {
0189: Value[] row = new Value[columnCount];
0190: for (int j = 0; groupIndex != null && j < groupIndex.length; j++) {
0191: row[groupIndex[j]] = keyValues[j];
0192: }
0193: for (int j = 0; j < columnCount; j++) {
0194: if (groupByExpression != null && groupByExpression[j]) {
0195: continue;
0196: }
0197: Expression expr = (Expression) expressions.get(j);
0198: row[j] = expr.getValue(session);
0199: }
0200: if (havingIndex > 0) {
0201: Value v = row[havingIndex];
0202: if (v == ValueNull.INSTANCE) {
0203: return;
0204: }
0205: if (!Boolean.TRUE.equals(v.getBoolean())) {
0206: return;
0207: }
0208: }
0209: if (columnCount != distinctColumnCount) {
0210: // remove columns so that 'distinct' can filter duplicate rows
0211: Value[] r2 = new Value[distinctColumnCount];
0212: System.arraycopy(row, 0, r2, 0, distinctColumnCount);
0213: row = r2;
0214: }
0215: result.addRow(row);
0216: }
0217:
0218: private Index getGroupSortedIndex() {
0219: if (groupIndex == null || groupByExpression == null) {
0220: return null;
0221: }
0222: ObjectArray indexes = topTableFilter.getTable().getIndexes();
0223: for (int i = 0; indexes != null && i < indexes.size(); i++) {
0224: Index index = (Index) indexes.get(i);
0225: if (index.getIndexType().isScan()) {
0226: continue;
0227: }
0228: if (isGroupSortedIndex(index)) {
0229: return index;
0230: }
0231: }
0232: return null;
0233: }
0234:
0235: private boolean isGroupSortedIndex(Index index) {
0236: Column[] indexColumns = index.getColumns();
0237: outerLoop: for (int i = 0; i < expressions.size(); i++) {
0238: if (!groupByExpression[i]) {
0239: continue;
0240: }
0241: Expression expr = (Expression) expressions.get(i);
0242: if (!(expr instanceof ExpressionColumn)) {
0243: return false;
0244: }
0245: ExpressionColumn exprCol = (ExpressionColumn) expr;
0246: for (int j = 0; j < indexColumns.length; ++j) {
0247: if (indexColumns[j].equals(exprCol.getColumn())) {
0248: continue outerLoop;
0249: }
0250: }
0251: // We didn't find a matching index column for the group by
0252: // expression
0253: return false;
0254: }
0255: return true;
0256: }
0257:
0258: private int getGroupByExpressionCount() {
0259: if (groupByExpression == null) {
0260: return 0;
0261: }
0262: int count = 0;
0263: for (int i = 0; i < groupByExpression.length; i++) {
0264: if (groupByExpression[i]) {
0265: ++count;
0266: }
0267: }
0268: return count;
0269: }
0270:
0271: private void queryGroup(int columnCount, LocalResult result)
0272: throws SQLException {
0273: ValueHashMap groups = new ValueHashMap(session.getDatabase());
0274: int rowNumber = 0;
0275: setCurrentRowNumber(0);
0276: ValueArray defaultGroup = ValueArray.get(new Value[0]);
0277: while (topTableFilter.next()) {
0278: checkCancelled();
0279: setCurrentRowNumber(rowNumber + 1);
0280: if (condition == null
0281: || Boolean.TRUE.equals(condition
0282: .getBooleanValue(session))) {
0283: Value key;
0284: rowNumber++;
0285: if (groupIndex == null) {
0286: key = defaultGroup;
0287: } else {
0288: Value[] keyValues = new Value[groupIndex.length];
0289: // update group
0290: for (int i = 0; i < groupIndex.length; i++) {
0291: int idx = groupIndex[i];
0292: Expression expr = (Expression) expressions
0293: .get(idx);
0294: keyValues[i] = expr.getValue(session);
0295: }
0296: key = ValueArray.get(keyValues);
0297: }
0298: HashMap values = (HashMap) groups.get(key);
0299: if (values == null) {
0300: values = new HashMap();
0301: groups.put(key, values);
0302: }
0303: currentGroup = values;
0304: int len = columnCount;
0305: for (int i = 0; i < len; i++) {
0306: if (groupByExpression == null
0307: || !groupByExpression[i]) {
0308: Expression expr = (Expression) expressions
0309: .get(i);
0310: expr.updateAggregate(session);
0311: }
0312: }
0313: if (sampleSize > 0 && rowNumber >= sampleSize) {
0314: break;
0315: }
0316: }
0317: }
0318: if (groupIndex == null && groups.size() == 0) {
0319: groups.put(defaultGroup, new HashMap());
0320: }
0321: ObjectArray keys = groups.keys();
0322: for (int i = 0; i < keys.size(); i++) {
0323: ValueArray key = (ValueArray) keys.get(i);
0324: currentGroup = (HashMap) groups.get(key);
0325: Value[] keyValues = key.getList();
0326: Value[] row = new Value[columnCount];
0327: for (int j = 0; groupIndex != null && j < groupIndex.length; j++) {
0328: row[groupIndex[j]] = keyValues[j];
0329: }
0330: for (int j = 0; j < columnCount; j++) {
0331: if (groupByExpression != null && groupByExpression[j]) {
0332: continue;
0333: }
0334: Expression expr = (Expression) expressions.get(j);
0335: row[j] = expr.getValue(session);
0336: }
0337: if (havingIndex > 0) {
0338: Value v = row[havingIndex];
0339: if (v == ValueNull.INSTANCE) {
0340: continue;
0341: }
0342: if (!Boolean.TRUE.equals(v.getBoolean())) {
0343: continue;
0344: }
0345: }
0346: if (columnCount != distinctColumnCount) {
0347: // remove columns so that 'distinct' can filter duplicate rows
0348: Value[] r2 = new Value[distinctColumnCount];
0349: System.arraycopy(row, 0, r2, 0, distinctColumnCount);
0350: row = r2;
0351: }
0352: result.addRow(row);
0353: }
0354: }
0355:
0356: /**
0357: * Get the index that matches the ORDER BY list, if one exists. This is to
0358: * avoid running a separate ORDER BY if an index can be used. This is
0359: * specially important for large result sets, if only the first few rows are
0360: * important (LIMIT is used)
0361: *
0362: * @return the index if one is found
0363: */
0364: private Index getSortIndex() throws SQLException {
0365: if (sort == null) {
0366: return null;
0367: }
0368: int[] indexes = sort.getIndexes();
0369: ObjectArray sortColumns = new ObjectArray();
0370: for (int i = 0; i < indexes.length; i++) {
0371: int idx = indexes[i];
0372: if (idx < 0 || idx >= expressions.size()) {
0373: throw Message.getInvalidValueException("" + (idx + 1),
0374: "ORDER BY");
0375: }
0376: Expression expr = (Expression) expressions.get(idx);
0377: expr = expr.getNonAliasExpression();
0378: if (expr.isConstant()) {
0379: continue;
0380: }
0381: if (!(expr instanceof ExpressionColumn)) {
0382: return null;
0383: }
0384: Column col = ((ExpressionColumn) expr).getColumn();
0385: if (col.getTable() != topTableFilter.getTable()) {
0386: return null;
0387: }
0388: sortColumns.add(col);
0389: }
0390: Column[] sortCols = new Column[sortColumns.size()];
0391: sortColumns.toArray(sortCols);
0392: int[] sortTypes = sort.getSortTypes();
0393: if (sortCols.length == 0) {
0394: // sort just on constants - can use scan index
0395: return topTableFilter.getTable().getScanIndex(session);
0396: }
0397: ObjectArray list = topTableFilter.getTable().getIndexes();
0398: for (int i = 0; list != null && i < list.size(); i++) {
0399: Index index = (Index) list.get(i);
0400: if (index.getCreateSQL() == null) {
0401: // can't use the scan index
0402: continue;
0403: }
0404: if (index.getIndexType().isHash()) {
0405: continue;
0406: }
0407: IndexColumn[] indexCols = index.getIndexColumns();
0408: if (indexCols.length < sortCols.length) {
0409: continue;
0410: }
0411: boolean ok = true;
0412: for (int j = 0; j < sortCols.length; j++) {
0413: // the index and the sort order must start
0414: // with the exact same columns
0415: IndexColumn idxCol = indexCols[j];
0416: Column sortCol = sortCols[j];
0417: if (idxCol.column != sortCol) {
0418: ok = false;
0419: break;
0420: }
0421: if (idxCol.sortType != sortTypes[j]) {
0422: // TODO NULL FIRST for ascending and NULLS LAST
0423: // for descending would actually match the default
0424: ok = false;
0425: break;
0426: }
0427: }
0428: if (ok) {
0429: return index;
0430: }
0431: }
0432: return null;
0433: }
0434:
0435: private void queryDistinct(int columnCount, LocalResult result,
0436: long limitRows) throws SQLException {
0437: if (limitRows != 0 && offset != null) {
0438: // limitRows must be long, otherwise we get an int overflow
0439: // if limitRows is at or near Integer.MAX_VALUE
0440: limitRows += offset.getValue(session).getInt();
0441: }
0442: int rowNumber = 0;
0443: setCurrentRowNumber(0);
0444: Index index = topTableFilter.getIndex();
0445: SearchRow first = null;
0446: int columnIndex = index.getColumns()[0].getColumnId();
0447: while (true) {
0448: checkCancelled();
0449: setCurrentRowNumber(rowNumber + 1);
0450: Cursor cursor = index.findNext(session, first, null);
0451: if (!cursor.next()) {
0452: break;
0453: }
0454: SearchRow found = cursor.getSearchRow();
0455: Value value = found.getValue(columnIndex);
0456: if (first == null) {
0457: first = topTableFilter.getTable().getTemplateSimpleRow(
0458: true);
0459: }
0460: first.setValue(columnIndex, value);
0461: Value[] row = new Value[1];
0462: row[0] = value;
0463: result.addRow(row);
0464: rowNumber++;
0465: if ((sort == null || sortUsingIndex) && limitRows != 0
0466: && result.getRowCount() >= limitRows) {
0467: break;
0468: }
0469: if (sampleSize > 0 && rowNumber >= sampleSize) {
0470: break;
0471: }
0472: }
0473: }
0474:
0475: private void queryFlat(int columnCount, LocalResult result,
0476: long limitRows) throws SQLException {
0477: if (limitRows != 0 && offset != null) {
0478: // limitRows must be long, otherwise we get an int overflow
0479: // if limitRows is at or near Integer.MAX_VALUE
0480: limitRows += offset.getValue(session).getInt();
0481: }
0482: int rowNumber = 0;
0483: setCurrentRowNumber(0);
0484: while (topTableFilter.next()) {
0485: checkCancelled();
0486: setCurrentRowNumber(rowNumber + 1);
0487: if (condition == null
0488: || Boolean.TRUE.equals(condition
0489: .getBooleanValue(session))) {
0490: Value[] row = new Value[columnCount];
0491: for (int i = 0; i < columnCount; i++) {
0492: Expression expr = (Expression) expressions.get(i);
0493: row[i] = expr.getValue(session);
0494: }
0495: result.addRow(row);
0496: rowNumber++;
0497: if ((sort == null || sortUsingIndex) && limitRows != 0
0498: && result.getRowCount() >= limitRows) {
0499: break;
0500: }
0501: if (sampleSize > 0 && rowNumber >= sampleSize) {
0502: break;
0503: }
0504: }
0505: }
0506: }
0507:
0508: private void queryQuick(int columnCount, LocalResult result)
0509: throws SQLException {
0510: Value[] row = new Value[columnCount];
0511: for (int i = 0; i < columnCount; i++) {
0512: Expression expr = (Expression) expressions.get(i);
0513: row[i] = expr.getValue(session);
0514: }
0515: result.addRow(row);
0516: }
0517:
0518: public LocalResult queryMeta() throws SQLException {
0519: LocalResult result = new LocalResult(session, expressions,
0520: visibleColumnCount);
0521: result.done();
0522: return result;
0523: }
0524:
0525: public LocalResult queryWithoutCache(int maxRows)
0526: throws SQLException {
0527: int limitRows = maxRows;
0528: if (limit != null) {
0529: int l = limit.getValue(session).getInt();
0530: if (limitRows == 0) {
0531: limitRows = l;
0532: } else {
0533: limitRows = Math.min(l, limitRows);
0534: }
0535: }
0536: int columnCount = expressions.size();
0537: LocalResult result = new LocalResult(session, expressions,
0538: visibleColumnCount);
0539: if (!sortUsingIndex) {
0540: result.setSortOrder(sort);
0541: }
0542: if (distinct && !isDistinctQuery) {
0543: result.setDistinct();
0544: }
0545: topTableFilter.startQuery(session);
0546: topTableFilter.reset();
0547: topTableFilter.lock(session, isForUpdate, isForUpdate);
0548: if (isQuickQuery) {
0549: queryQuick(columnCount, result);
0550: } else if (isGroupQuery) {
0551: if (isGroupSortedQuery) {
0552: queryGroupSorted(columnCount, result);
0553: } else {
0554: queryGroup(columnCount, result);
0555: }
0556: } else if (isDistinctQuery) {
0557: queryDistinct(columnCount, result, limitRows);
0558: } else {
0559: queryFlat(columnCount, result, limitRows);
0560: }
0561: if (offset != null) {
0562: result.setOffset(offset.getValue(session).getInt());
0563: }
0564: if (limitRows != 0) {
0565: result.setLimit(limitRows);
0566: }
0567: result.done();
0568: return result;
0569: }
0570:
0571: private void expandColumnList() throws SQLException {
0572: // TODO this works: select distinct count(*) from system_columns group
0573: // by table
0574: for (int i = 0; i < expressions.size(); i++) {
0575: Expression expr = (Expression) expressions.get(i);
0576: if (!expr.isWildcard()) {
0577: continue;
0578: }
0579: String tableAlias = expr.getTableAlias();
0580: if (tableAlias == null) {
0581: int temp = i;
0582: expressions.remove(i);
0583: for (int j = 0; j < filters.size(); j++) {
0584: TableFilter filter = (TableFilter) filters.get(j);
0585: Wildcard c2 = new Wildcard(filter.getTable()
0586: .getSchema().getName(), filter
0587: .getTableAlias());
0588: expressions.add(i++, c2);
0589: }
0590: i = temp - 1;
0591: } else {
0592: TableFilter filter = null;
0593: for (int j = 0; j < filters.size(); j++) {
0594: TableFilter f = (TableFilter) filters.get(j);
0595: if (tableAlias.equals(f.getTableAlias())) {
0596: filter = f;
0597: break;
0598: }
0599: }
0600: if (filter == null) {
0601: throw Message.getSQLException(
0602: ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1,
0603: tableAlias);
0604: }
0605: Table t = filter.getTable();
0606: String alias = filter.getTableAlias();
0607: expressions.remove(i);
0608: Column[] columns = t.getColumns();
0609: for (int j = 0; j < columns.length; j++) {
0610: Column c = columns[j];
0611: ExpressionColumn ec = new ExpressionColumn(session
0612: .getDatabase(), null, alias, c.getName());
0613: expressions.add(i++, ec);
0614: }
0615: i--;
0616: }
0617: }
0618: }
0619:
0620: public void init() throws SQLException {
0621: if (SysProperties.CHECK && checkInit) {
0622: throw Message.getInternalError();
0623: }
0624: expandColumnList();
0625: visibleColumnCount = expressions.size();
0626: ObjectArray expressionSQL;
0627: if (orderList != null || group != null) {
0628: expressionSQL = new ObjectArray();
0629: for (int i = 0; i < expressions.size(); i++) {
0630: Expression expr = (Expression) expressions.get(i);
0631: expr = expr.getNonAliasExpression();
0632: String sql = expr.getSQL();
0633: expressionSQL.add(sql);
0634: }
0635: } else {
0636: expressionSQL = null;
0637: }
0638: if (orderList != null) {
0639: initOrder(expressions, expressionSQL, orderList,
0640: visibleColumnCount, distinct);
0641: }
0642: distinctColumnCount = expressions.size();
0643: if (having != null) {
0644: expressions.add(having);
0645: havingIndex = expressions.size() - 1;
0646: having = null;
0647: } else {
0648: havingIndex = -1;
0649: }
0650:
0651: // first visible columns, then order by, then having,
0652: // and group by at the end
0653: if (group != null) {
0654: groupIndex = new int[group.size()];
0655: for (int i = 0; i < group.size(); i++) {
0656: Expression expr = (Expression) group.get(i);
0657: String sql = expr.getSQL();
0658: int found = -1;
0659: for (int j = 0; j < expressionSQL.size(); j++) {
0660: String s2 = (String) expressionSQL.get(j);
0661: if (s2.equals(sql)) {
0662: found = j;
0663: break;
0664: }
0665: }
0666: if (found < 0) {
0667: int index = expressions.size();
0668: groupIndex[i] = index;
0669: expressions.add(expr);
0670: } else {
0671: groupIndex[i] = found;
0672: }
0673: }
0674: groupByExpression = new boolean[expressions.size()];
0675: for (int i = 0; i < groupIndex.length; i++) {
0676: groupByExpression[groupIndex[i]] = true;
0677: }
0678: group = null;
0679: }
0680: // map columns in select list and condition
0681: for (int i = 0; i < filters.size(); i++) {
0682: TableFilter f = (TableFilter) filters.get(i);
0683: for (int j = 0; j < expressions.size(); j++) {
0684: Expression expr = (Expression) expressions.get(j);
0685: expr.mapColumns(f, 0);
0686: }
0687: if (condition != null) {
0688: condition.mapColumns(f, 0);
0689: }
0690: }
0691: checkInit = true;
0692: }
0693:
0694: public void prepare() throws SQLException {
0695: if (isPrepared) {
0696: // sometimes a subquery is prepared twice (CREATE TABLE AS SELECT)
0697: return;
0698: }
0699: if (SysProperties.CHECK && !checkInit) {
0700: throw Message.getInternalError("not initialized");
0701: }
0702: if (orderList != null) {
0703: sort = prepareOrder(orderList, expressions.size());
0704: orderList = null;
0705: }
0706: for (int i = 0; i < expressions.size(); i++) {
0707: Expression e = (Expression) expressions.get(i);
0708: expressions.set(i, e.optimize(session));
0709: }
0710: if (condition != null) {
0711: condition = condition.optimize(session);
0712: if (SysProperties.OPTIMIZE_IN_JOIN) {
0713: condition = condition.optimizeInJoin(session, this );
0714: }
0715: for (int j = 0; j < filters.size(); j++) {
0716: TableFilter f = (TableFilter) filters.get(j);
0717: condition.createIndexConditions(session, f);
0718: }
0719: }
0720: if (isGroupQuery && groupIndex == null && havingIndex < 0
0721: && filters.size() == 1) {
0722: if (condition == null) {
0723: ExpressionVisitor optimizable = ExpressionVisitor
0724: .get(ExpressionVisitor.OPTIMIZABLE_MIN_MAX_COUNT_ALL);
0725: optimizable.table = ((TableFilter) filters.get(0))
0726: .getTable();
0727: isQuickQuery = isEverything(optimizable);
0728: }
0729: }
0730: cost = preparePlan();
0731: if (SysProperties.OPTIMIZE_DISTINCT && distinct
0732: && !isGroupQuery && filters.size() == 1
0733: && expressions.size() == 1 && condition == null) {
0734: Expression expr = (Expression) expressions.get(0);
0735: expr = expr.getNonAliasExpression();
0736: if (expr instanceof ExpressionColumn) {
0737: Column column = ((ExpressionColumn) expr).getColumn();
0738: int selectivity = column.getSelectivity();
0739: Index columnIndex = topTableFilter.getTable()
0740: .getIndexForColumn(column, true);
0741: if (columnIndex != null
0742: && selectivity != Constants.SELECTIVITY_DEFAULT
0743: && selectivity < 20) {
0744: // the first column must be ascending
0745: boolean ascending = columnIndex.getIndexColumns()[0].sortType == SortOrder.ASCENDING;
0746: Index current = topTableFilter.getIndex();
0747: // if another index is faster
0748: if (columnIndex.canFindNext()
0749: && ascending
0750: && (current == null
0751: || current.getIndexType().isScan() || columnIndex == current)) {
0752: IndexType type = columnIndex.getIndexType();
0753: // hash indexes don't work, and unique single column indexes don't work
0754: if (!type.isHash()
0755: && (!type.isUnique() || columnIndex
0756: .getColumns().length > 1)) {
0757: topTableFilter.setIndex(columnIndex);
0758: isDistinctQuery = true;
0759: }
0760: }
0761: }
0762: }
0763: }
0764: if (sort != null && !isQuickQuery && !isGroupQuery) {
0765: Index index = getSortIndex();
0766: Index current = topTableFilter.getIndex();
0767: if (index != null
0768: && (current.getIndexType().isScan() || current == index)) {
0769: topTableFilter.setIndex(index);
0770: if (!distinct || isDistinctQuery) {
0771: // sort using index would not work correctly for distinct result sets
0772: // because it would break too early when limit is used
0773: sortUsingIndex = true;
0774: }
0775: }
0776: }
0777: if (SysProperties.OPTIMIZE_GROUP_SORTED && !isQuickQuery
0778: && isGroupQuery && getGroupByExpressionCount() > 0) {
0779: Index index = getGroupSortedIndex();
0780: Index current = topTableFilter.getIndex();
0781: if (index != null
0782: && (current.getIndexType().isScan() || current == index)) {
0783: topTableFilter.setIndex(index);
0784: isGroupSortedQuery = true;
0785: }
0786: }
0787: isPrepared = true;
0788: }
0789:
0790: public double getCost() {
0791: return cost;
0792: }
0793:
0794: public HashSet getTables() {
0795: HashSet set = new HashSet();
0796: for (int i = 0; i < filters.size(); i++) {
0797: TableFilter filter = (TableFilter) filters.get(i);
0798: set.add(filter.getTable());
0799: }
0800: return set;
0801: }
0802:
0803: private double preparePlan() throws SQLException {
0804:
0805: TableFilter[] topArray = new TableFilter[topFilters.size()];
0806: topFilters.toArray(topArray);
0807: for (int i = 0; i < topArray.length; i++) {
0808: topArray[i].setFullCondition(condition);
0809: }
0810:
0811: Optimizer optimizer = new Optimizer(topArray, condition,
0812: session);
0813: optimizer.optimize();
0814: topTableFilter = optimizer.getTopFilter();
0815: double cost = optimizer.getCost();
0816:
0817: TableFilter f = topTableFilter;
0818: while (f != null) {
0819: f.setEvaluatable(f, true);
0820: if (condition != null) {
0821: condition.setEvaluatable(f, true);
0822: }
0823: Expression on = f.getJoinCondition();
0824: if (on != null) {
0825: if (!on.isEverything(ExpressionVisitor.EVALUATABLE)) {
0826: if (f.isJoinOuter()) {
0827: throw Message
0828: .getSQLException(
0829: ErrorCode.UNSUPPORTED_OUTER_JOIN_CONDITION_1,
0830: on.getSQL());
0831: }
0832: f.removeJoinCondition();
0833: // need to check that all added are bound to a table
0834: on = on.optimize(session);
0835: addCondition(on);
0836: }
0837: }
0838: on = f.getFilterCondition();
0839: if (on != null) {
0840: if (!on.isEverything(ExpressionVisitor.EVALUATABLE)) {
0841: f.removeFilterCondition();
0842: addCondition(on);
0843: }
0844: }
0845: // this is only important for subqueries, so they know
0846: // the result columns are evaluatable
0847: for (int i = 0; i < expressions.size(); i++) {
0848: Expression e = (Expression) expressions.get(i);
0849: e.setEvaluatable(f, true);
0850: }
0851: f = f.getJoin();
0852: }
0853: topTableFilter.prepare();
0854: return cost;
0855: }
0856:
0857: public String getPlanSQL() {
0858: if (topTableFilter == null) {
0859: return sql;
0860: }
0861: StringBuffer buff = new StringBuffer();
0862: Expression[] exprList = new Expression[expressions.size()];
0863: expressions.toArray(exprList);
0864: buff.append("SELECT ");
0865: if (distinct) {
0866: buff.append("DISTINCT ");
0867: }
0868: for (int i = 0; i < visibleColumnCount; i++) {
0869: if (i > 0) {
0870: buff.append(", ");
0871: }
0872: Expression expr = exprList[i];
0873: buff.append(expr.getSQL());
0874: }
0875: buff.append("\nFROM ");
0876: TableFilter filter = topTableFilter;
0877: boolean join = false;
0878: int id = 0;
0879: do {
0880: if (id > 0) {
0881: buff.append("\n");
0882: }
0883: buff.append(filter.getPlanSQL(join));
0884: id++;
0885: join = true;
0886: filter = filter.getJoin();
0887: } while (filter != null);
0888: if (condition != null) {
0889: buff.append("\nWHERE "
0890: + StringUtils.unEnclose(condition.getSQL()));
0891: }
0892: if (groupIndex != null) {
0893: buff.append("\nGROUP BY ");
0894: for (int i = 0; i < groupIndex.length; i++) {
0895: Expression g = exprList[groupIndex[i]];
0896: g = g.getNonAliasExpression();
0897: if (i > 0) {
0898: buff.append(", ");
0899: }
0900: buff.append(StringUtils.unEnclose(g.getSQL()));
0901: }
0902: }
0903: if (having != null) {
0904: // could be set in addGlobalCondition
0905: // in this case the query is not run directly, just getPlanSQL is
0906: // called
0907: Expression h = having;
0908: buff
0909: .append("\nHAVING "
0910: + StringUtils.unEnclose(h.getSQL()));
0911: } else if (havingIndex >= 0) {
0912: Expression h = exprList[havingIndex];
0913: buff
0914: .append("\nHAVING "
0915: + StringUtils.unEnclose(h.getSQL()));
0916: }
0917: if (sort != null) {
0918: buff.append("\nORDER BY ");
0919: buff.append(sort.getSQL(exprList, visibleColumnCount));
0920: }
0921: if (limit != null) {
0922: buff.append("\nLIMIT ");
0923: buff.append(StringUtils.unEnclose(limit.getSQL()));
0924: if (offset != null) {
0925: buff.append(" OFFSET ");
0926: buff.append(StringUtils.unEnclose(offset.getSQL()));
0927: }
0928: }
0929: if (isForUpdate) {
0930: buff.append("\nFOR UPDATE");
0931: }
0932: if (isQuickQuery) {
0933: buff.append("\n/* direct lookup */");
0934: }
0935: if (isDistinctQuery) {
0936: buff.append("\n/* distinct */");
0937: }
0938: if (isGroupQuery) {
0939: if (isGroupSortedQuery) {
0940: buff.append("\n/* group sorted */");
0941: }
0942: }
0943: return buff.toString();
0944: }
0945:
0946: public void setDistinct(boolean b) {
0947: distinct = b;
0948: }
0949:
0950: public void setHaving(Expression having) {
0951: this .having = having;
0952: }
0953:
0954: public int getColumnCount() {
0955: return visibleColumnCount;
0956: }
0957:
0958: public TableFilter getTopTableFilter() {
0959: return topTableFilter;
0960: }
0961:
0962: public ObjectArray getExpressions() {
0963: return expressions;
0964: }
0965:
0966: public void setForUpdate(boolean b) {
0967: this .isForUpdate = b;
0968: }
0969:
0970: public void mapColumns(ColumnResolver resolver, int level)
0971: throws SQLException {
0972: for (int i = 0; i < expressions.size(); i++) {
0973: Expression e = (Expression) expressions.get(i);
0974: e.mapColumns(resolver, level);
0975: }
0976: if (condition != null) {
0977: condition.mapColumns(resolver, level);
0978: }
0979: }
0980:
0981: public void setEvaluatable(TableFilter tableFilter, boolean b) {
0982: for (int i = 0; i < expressions.size(); i++) {
0983: Expression e = (Expression) expressions.get(i);
0984: e.setEvaluatable(tableFilter, b);
0985: }
0986: if (condition != null) {
0987: condition.setEvaluatable(tableFilter, b);
0988: }
0989: }
0990:
0991: public boolean isQuickQuery() {
0992: return isQuickQuery;
0993: }
0994:
0995: public void addGlobalCondition(Parameter param, int columnId,
0996: int comparisonType) throws SQLException {
0997: addParameter(param);
0998: Expression col = (Expression) expressions.get(columnId);
0999: col = col.getNonAliasExpression();
1000: Expression comp = new Comparison(session, comparisonType, col,
1001: param);
1002: comp = comp.optimize(session);
1003: boolean addToCondition = true;
1004: if (isGroupQuery) {
1005: addToCondition = false;
1006: for (int i = 0; groupIndex != null && i < groupIndex.length; i++) {
1007: if (groupIndex[i] == columnId) {
1008: addToCondition = true;
1009: break;
1010: }
1011: }
1012: if (!addToCondition) {
1013: if (havingIndex >= 0) {
1014: having = (Expression) expressions.get(havingIndex);
1015: }
1016: if (having == null) {
1017: having = comp;
1018: } else {
1019: having = new ConditionAndOr(ConditionAndOr.AND,
1020: having, comp);
1021: }
1022: }
1023: }
1024: if (addToCondition) {
1025: if (condition == null) {
1026: condition = comp;
1027: } else {
1028: condition = new ConditionAndOr(ConditionAndOr.AND,
1029: condition, comp);
1030: }
1031: }
1032: }
1033:
1034: public void updateAggregate(Session session) throws SQLException {
1035: for (int i = 0; i < expressions.size(); i++) {
1036: Expression e = (Expression) expressions.get(i);
1037: e.updateAggregate(session);
1038: }
1039: if (condition != null) {
1040: condition.updateAggregate(session);
1041: }
1042: if (having != null) {
1043: having.updateAggregate(session);
1044: }
1045: }
1046:
1047: public boolean isEverything(ExpressionVisitor visitor) {
1048: switch (visitor.type) {
1049: case ExpressionVisitor.SET_MAX_DATA_MODIFICATION_ID: {
1050: for (int i = 0; i < filters.size(); i++) {
1051: TableFilter f = (TableFilter) filters.get(i);
1052: long m = f.getTable().getMaxDataModificationId();
1053: visitor.addDataModificationId(m);
1054: }
1055: break;
1056: }
1057: case ExpressionVisitor.EVALUATABLE: {
1058: if (!SysProperties.OPTIMIZE_EVALUATABLE_SUBQUERIES) {
1059: return false;
1060: }
1061: break;
1062: }
1063: case ExpressionVisitor.GET_DEPENDENCIES: {
1064: for (int i = 0; i < filters.size(); i++) {
1065: TableFilter filter = (TableFilter) filters.get(i);
1066: Table table = filter.getTable();
1067: visitor.addDependency(table);
1068: table.addDependencies(visitor.getDependencies());
1069: }
1070: break;
1071: }
1072: }
1073: visitor.queryLevel(1);
1074: boolean result = true;
1075: for (int i = 0; i < expressions.size(); i++) {
1076: Expression e = (Expression) expressions.get(i);
1077: if (!e.isEverything(visitor)) {
1078: result = false;
1079: break;
1080: }
1081: }
1082: if (result && condition != null
1083: && !condition.isEverything(visitor)) {
1084: result = false;
1085: }
1086: if (result && having != null && !having.isEverything(visitor)) {
1087: result = false;
1088: }
1089: visitor.queryLevel(-1);
1090: return result;
1091: }
1092:
1093: public boolean isReadOnly() {
1094: return isEverything(ExpressionVisitor.READONLY);
1095: }
1096:
1097: public String getFirstColumnAlias(Session session) {
1098: if (SysProperties.CHECK) {
1099: if (visibleColumnCount > 1) {
1100: throw Message.getInternalError("" + visibleColumnCount);
1101: }
1102: }
1103: Expression expr = (Expression) expressions.get(0);
1104: if (expr instanceof Alias) {
1105: return expr.getAlias();
1106: } else {
1107: expr = new Alias(expr, session.getNextTempViewName() + "_X");
1108: expressions.set(0, expr);
1109: }
1110: return expr.getAlias();
1111: }
1112:
1113: }
|