0001: /* Copyright (c) 1995-2000, The Hypersonic SQL Group.
0002: * All rights reserved.
0003: *
0004: * Redistribution and use in source and binary forms, with or without
0005: * modification, are permitted provided that the following conditions are met:
0006: *
0007: * Redistributions of source code must retain the above copyright notice, this
0008: * list of conditions and the following disclaimer.
0009: *
0010: * Redistributions in binary form must reproduce the above copyright notice,
0011: * this list of conditions and the following disclaimer in the documentation
0012: * and/or other materials provided with the distribution.
0013: *
0014: * Neither the name of the Hypersonic SQL Group nor the names of its
0015: * contributors may be used to endorse or promote products derived from this
0016: * software without specific prior written permission.
0017: *
0018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
0019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
0020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
0021: * ARE DISCLAIMED. IN NO EVENT SHALL THE HYPERSONIC SQL GROUP,
0022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
0023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
0024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
0025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
0026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
0027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
0028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
0029: *
0030: * This software consists of voluntary contributions made by many individuals
0031: * on behalf of the Hypersonic SQL Group.
0032: *
0033: *
0034: * For work added by the HSQL Development Group:
0035: *
0036: * Copyright (c) 2001-2005, The HSQL Development Group
0037: * All rights reserved.
0038: *
0039: * Redistribution and use in source and binary forms, with or without
0040: * modification, are permitted provided that the following conditions are met:
0041: *
0042: * Redistributions of source code must retain the above copyright notice, this
0043: * list of conditions and the following disclaimer.
0044: *
0045: * Redistributions in binary form must reproduce the above copyright notice,
0046: * this list of conditions and the following disclaimer in the documentation
0047: * and/or other materials provided with the distribution.
0048: *
0049: * Neither the name of the HSQL Development Group nor the names of its
0050: * contributors may be used to endorse or promote products derived from this
0051: * software without specific prior written permission.
0052: *
0053: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
0054: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
0055: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
0056: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
0057: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
0058: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
0059: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
0060: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
0061: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
0062: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
0063: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
0064: */
0065:
0066: package org.hsqldb;
0067:
0068: import org.hsqldb.HsqlNameManager.HsqlName;
0069: import org.hsqldb.lib.HashMap;
0070: import org.hsqldb.lib.HashSet;
0071: import org.hsqldb.lib.HsqlArrayList;
0072: import org.hsqldb.lib.IntKeyHashMap;
0073: import org.hsqldb.lib.Iterator;
0074:
0075: // fredt@users 20010701 - patch 1.6.1 by hybris
0076: // basic implementation of LIMIT n m
0077: // fredt@users 20020130 - patch 471710 by fredt - LIMIT rewritten
0078: // for SELECT LIMIT n m DISTINCT
0079: // fredt@users 20020221 - patch 513005 by sqlbob@users (RMP)
0080: // type and logging attributes of sIntotable
0081: // fredt@users 20020230 - patch 495938 by johnhobs@users - GROUP BY order
0082: // fred@users 20020522 - patch 1.7.0 - aggregate functions with DISTINCT
0083: // rougier@users 20020522 - patch 552830 - COUNT(DISTINCT)
0084: // fredt@users 20020804 - patch 580347 by dkkopp - view speedup
0085: // tony_lai@users 20021020 - patch 1.7.2 - improved aggregates and HAVING
0086: // boucherb@users 20030811 - patch 1.7.2 - prepared statement support
0087: // fredt@users 20031012 - patch 1.7.2 - better OUTER JOIN implementation
0088: // fredt@users 20031012 - patch 1.7.2 - SQL standard ORDER BY with UNION and other set queries
0089: // fredt@users 200408xx - patch 1.7.2 - correct evaluation of the precedence of nested UNION and other set query
0090:
0091: /**
0092: * The compiled representation of an SQL SELECT.
0093: *
0094: * Extended in successive versions of HSQLDB.
0095: *
0096: * @author Thomas Mueller (Hypersonic SQL Group)
0097: * @version 1.8.0
0098: * @since Hypersonic SQL
0099: */
0100: class Select {
0101:
0102: boolean isDistinctSelect;
0103: boolean isAggregated;
0104: private boolean isGrouped;
0105: private HashSet groupColumnNames;
0106: TableFilter[] tFilter;
0107: Expression limitCondition;
0108: Expression queryCondition; // null means no condition
0109: Expression havingCondition; // null means none
0110: Expression[] exprColumns; // 'result', 'group' and 'order' columns
0111: int iResultLen; // number of columns that are 'result'
0112: int iGroupLen; // number of columns that are 'group'
0113: int iHavingLen; // number of columns that are 'group'
0114: int iOrderLen; // number of columns that are 'order'
0115: int[] sortOrder;
0116: int[] sortDirection;
0117: boolean sortUnion; // if true, sort the result of the full union
0118: HsqlName sIntoTable; // null means not select..into
0119: int intoType;
0120: Select[] unionArray; // only set in the first Select in a union chain
0121: int unionMaxDepth; // max unionDepth in chain
0122: Select unionSelect; // null means no union select
0123: int unionType;
0124: int unionDepth;
0125: static final int NOUNION = 0, UNION = 1, UNIONALL = 2,
0126: INTERSECT = 3, EXCEPT = 4;
0127: private boolean simpleLimit; // true if maxrows can be uses as is
0128: Result.ResultMetaData resultMetaData;
0129: IntKeyHashMap asteriskPositions;
0130:
0131: /**
0132: * Experimental.
0133: *
0134: * Map the column aliases to expressions in order to resolve alias names
0135: * in WHERE clauses
0136: *
0137: */
0138: HashMap getColumnAliases() {
0139:
0140: HashMap aliasMap = new HashMap();
0141:
0142: for (int i = 0; i < iResultLen; i++) {
0143: String alias = exprColumns[i].getAlias();
0144:
0145: if (alias != null) {
0146: aliasMap.put(alias, exprColumns[i]);
0147: }
0148: }
0149:
0150: return aliasMap;
0151: }
0152:
0153: /**
0154: * Method declaration
0155: *
0156: *
0157: * @throws HsqlException
0158: */
0159: void resolve(Session session) throws HsqlException {
0160:
0161: resolveTables();
0162: resolveTypes(session);
0163: setFilterConditions(session);
0164: }
0165:
0166: /**
0167: * Method declaration
0168: *
0169: *
0170: * @throws HsqlException
0171: */
0172: private void resolveTables() throws HsqlException {
0173:
0174: // replace the aliases with expressions
0175: for (int i = iResultLen; i < exprColumns.length; i++) {
0176: if (exprColumns[i].getType() == Expression.COLUMN) {
0177: if (exprColumns[i].joinedTableColumnIndex == -1) {
0178: boolean descending = exprColumns[i].isDescending();
0179:
0180: exprColumns[i] = exprColumns[i]
0181: .getExpressionForAlias(exprColumns,
0182: iResultLen);
0183:
0184: if (descending) {
0185: exprColumns[i].setDescending();
0186: }
0187: }
0188: } else {
0189: exprColumns[i].replaceAliases(exprColumns, iResultLen);
0190: }
0191: }
0192:
0193: if (queryCondition != null) {
0194: queryCondition.replaceAliases(exprColumns, iResultLen);
0195: }
0196:
0197: int len = tFilter.length;
0198:
0199: for (int i = 0; i < len; i++) {
0200: resolveTables(tFilter[i]);
0201: }
0202: }
0203:
0204: /**
0205: * Converts the types of the columns in set operations to those in the first
0206: * Select.
0207: */
0208: void resolveUnionColumnTypes() throws HsqlException {
0209:
0210: if (unionSelect != null) {
0211: if (unionSelect.iResultLen != iResultLen) {
0212: throw Trace.error(Trace.COLUMN_COUNT_DOES_NOT_MATCH);
0213: }
0214:
0215: for (int i = 0; i < iResultLen; i++) {
0216: Expression e = exprColumns[i];
0217:
0218: if (!e.isTypeEqual(unionSelect.exprColumns[i])) {
0219: unionSelect.exprColumns[i] = new Expression(
0220: unionSelect.exprColumns[i],
0221: e.getDataType(), e.getColumnSize(), e
0222: .getColumnScale());
0223: }
0224: }
0225: }
0226: }
0227:
0228: /**
0229: * Sets the types of all the expressions that have so far resolved.
0230: *
0231: * @throws HsqlException
0232: */
0233: void resolveTypes(Session session) throws HsqlException {
0234:
0235: int len = exprColumns.length;
0236:
0237: for (int i = 0; i < len; i++) {
0238: exprColumns[i].resolveTypes(session);
0239: }
0240:
0241: if (queryCondition != null) {
0242: queryCondition.resolveTypes(session);
0243: }
0244: }
0245:
0246: void resolveTablesUnion(TableFilter f) throws HsqlException {
0247:
0248: if (unionArray == null) {
0249: resolveTables(f);
0250: } else {
0251: for (int i = 0; i < unionArray.length; i++) {
0252: unionArray[i].resolveTables(f);
0253: }
0254: }
0255: }
0256:
0257: /**
0258: * Resolves the tables for all the Expression in the Select object
0259: * if it is possible to do so with the given TableFilter.
0260: *
0261: * @param f
0262: *
0263: * @throws HsqlException
0264: */
0265: void resolveTables(TableFilter f) throws HsqlException {
0266:
0267: int len = exprColumns.length;
0268:
0269: for (int i = 0; i < len; i++) {
0270: exprColumns[i].resolveTables(f);
0271: }
0272:
0273: if (queryCondition != null) {
0274: queryCondition.resolveTables(f);
0275: }
0276: }
0277:
0278: private void setFilterConditions(Session session)
0279: throws HsqlException {
0280:
0281: if (queryCondition == null) {
0282: return;
0283: }
0284:
0285: for (int i = 0; i < tFilter.length; i++) {
0286: tFilter[i].setConditions(session, queryCondition);
0287: }
0288: }
0289:
0290: /**
0291: * Check all Expression have resolved. Return true or false as a result.
0292: * Throw if false and check parameter is true.
0293: *
0294: * @throws HsqlException
0295: */
0296: boolean checkResolved(boolean check) throws HsqlException {
0297:
0298: boolean result = true;
0299: int len = exprColumns.length;
0300:
0301: for (int i = 0; i < len; i++) {
0302: result = result && exprColumns[i].checkResolved(check);
0303: }
0304:
0305: if (queryCondition != null) {
0306: result = result && queryCondition.checkResolved(check);
0307: }
0308:
0309: if (havingCondition != null) {
0310: result = result && havingCondition.checkResolved(check);
0311: }
0312:
0313: for (int i = 0; i < tFilter.length; i++) {
0314: if (tFilter[i].filterIndex == null) {
0315: tFilter[i].filterIndex = tFilter[i].filterTable
0316: .getPrimaryIndex();
0317: }
0318: }
0319:
0320: return result;
0321: }
0322:
0323: /**
0324: * Removes all the TableFilters from the Expressions.
0325: *
0326: * @throws HsqlException
0327: */
0328: /*
0329: void removeFilters() throws HsqlException {
0330:
0331: int len = eColumn.length;
0332:
0333: for (int i = 0; i < len; i++) {
0334: eColumn[i].removeFilters();
0335: }
0336:
0337: if (eCondition != null) {
0338: eCondition.removeFilters();
0339: }
0340: }
0341: */
0342:
0343: /**
0344: * Returns a single value result or throws if the result has more than
0345: * one row with one value.
0346: *
0347: * @param type data type
0348: * @param session context
0349: * @return the single valued result
0350: * @throws HsqlException
0351: */
0352: Object getValue(Session session, int type) throws HsqlException {
0353:
0354: resolve(session);
0355:
0356: Result r = getResult(session, 2); // 2 records are required for test
0357: int size = r.getSize();
0358: int len = r.getColumnCount();
0359:
0360: if (len == 1) {
0361: if (size == 0) {
0362: return null;
0363: } else if (size == 1) {
0364: Object o = r.rRoot.data[0];
0365:
0366: return r.metaData.colTypes[0] == type ? o : Column
0367: .convertObject(o, type);
0368: } else {
0369: throw Trace
0370: .error(Trace.CARDINALITY_VIOLATION_NO_SUBCLASS);
0371: }
0372: }
0373:
0374: HsqlException e = Trace
0375: .error(Trace.CARDINALITY_VIOLATION_NO_SUBCLASS);
0376:
0377: throw new HsqlInternalException(e);
0378: }
0379:
0380: /**
0381: * Resolves expressions and pepares thre metadata for the result.
0382: */
0383: void prepareResult(Session session) throws HsqlException {
0384:
0385: resolveAll(session, true);
0386:
0387: if (iGroupLen > 0) { // has been set in Parser
0388: isGrouped = true;
0389: groupColumnNames = new HashSet();
0390:
0391: for (int i = iResultLen; i < iResultLen + iGroupLen; i++) {
0392:
0393: // MarcH: this is wrong for a CASE WHEN statement in a SELECT CASE WHEN ...,<something aggregate> statement
0394: // collectColumnName collects no columns if exprColumns[i]'s expressiontype is Expression.CASEWHEN
0395: // collectAllColumnNames collects all columns used in the CASE WHEN statement
0396: // exprColumns[i].collectColumnName(groupColumnNames);
0397: exprColumns[i].collectAllColumnNames(groupColumnNames);
0398: }
0399: }
0400:
0401: int len = exprColumns.length;
0402:
0403: resultMetaData = new Result.ResultMetaData(len);
0404:
0405: Result.ResultMetaData rmd = resultMetaData;
0406:
0407: // tony_lai@users having
0408: int groupByStart = iResultLen;
0409: int groupByEnd = groupByStart + iGroupLen;
0410: int orderByStart = groupByEnd + iHavingLen;
0411: int orderByEnd = orderByStart + iOrderLen;
0412:
0413: for (int i = 0; i < len; i++) {
0414: Expression e = exprColumns[i];
0415:
0416: rmd.colTypes[i] = e.getDataType();
0417: rmd.colSizes[i] = e.getColumnSize();
0418: rmd.colScales[i] = e.getColumnScale();
0419:
0420: if (e.isAggregate()) {
0421: isAggregated = true;
0422: }
0423:
0424: if (i >= groupByStart && i < groupByEnd
0425: && !exprColumns[i].canBeInGroupBy()) {
0426: Trace.error(Trace.INVALID_GROUP_BY, exprColumns[i]);
0427: }
0428:
0429: if (i >= groupByEnd && i < groupByEnd + iHavingLen
0430: && !exprColumns[i].isConditional()) {
0431: Trace.error(Trace.INVALID_HAVING, exprColumns[i]);
0432: }
0433:
0434: if (i >= orderByStart && i < orderByEnd
0435: && !exprColumns[i].canBeInOrderBy()) {
0436: Trace.error(Trace.INVALID_ORDER_BY, exprColumns[i]);
0437: }
0438:
0439: if (i < iResultLen) {
0440: rmd.colLabels[i] = e.getAlias();
0441: rmd.isLabelQuoted[i] = e.isAliasQuoted();
0442: rmd.schemaNames[i] = e.getTableSchemaName();
0443: rmd.tableNames[i] = e.getTableName();
0444: rmd.colNames[i] = e.getColumnName();
0445:
0446: if (rmd.isTableColumn(i)) {
0447: rmd.colNullable[i] = e.nullability;
0448: rmd.isIdentity[i] = e.isIdentity;
0449: rmd.isWritable[i] = e.isWritable;
0450: }
0451:
0452: rmd.classNames[i] = e.getValueClassName();
0453: }
0454: }
0455:
0456: // selected columns
0457: checkAggregateOrGroupByColumns(0, iResultLen);
0458:
0459: // having columns
0460: checkAggregateOrGroupByColumns(groupByEnd, orderByStart);
0461:
0462: // order by columns
0463: checkAggregateOrGroupByOrderColumns(orderByStart, orderByEnd);
0464: prepareSort();
0465:
0466: simpleLimit = (isDistinctSelect == false && isGrouped == false
0467: && unionSelect == null && iOrderLen == 0);
0468: }
0469:
0470: /**
0471: * This is called externally only on the first Select in a UNION chain.
0472: */
0473: void prepareUnions() throws HsqlException {
0474:
0475: int count = 0;
0476:
0477: for (Select current = this ; current != null; current = current.unionSelect, count++) {
0478: }
0479:
0480: if (count == 1) {
0481: if (unionDepth != 0) {
0482: throw Trace.error(Trace.MISSING_CLOSEBRACKET);
0483: }
0484:
0485: return;
0486: }
0487:
0488: unionArray = new Select[count];
0489: count = 0;
0490:
0491: for (Select current = this ; current != null; current = current.unionSelect, count++) {
0492: unionArray[count] = current;
0493: unionMaxDepth = current.unionDepth > unionMaxDepth ? current.unionDepth
0494: : unionMaxDepth;
0495: }
0496:
0497: if (unionArray[unionArray.length - 1].unionDepth != 0) {
0498: throw Trace.error(Trace.MISSING_CLOSEBRACKET);
0499: }
0500: }
0501:
0502: /**
0503: * Returns the result of executing this Select.
0504: *
0505: * @param maxrows may be 0 to indicate no limit on the number of rows.
0506: * Positive values limit the size of the result set.
0507: * @return the result of executing this Select
0508: * @throws HsqlException if a database access error occurs
0509: */
0510: Result getResult(Session session, int maxrows) throws HsqlException {
0511:
0512: Result r;
0513:
0514: if (unionArray == null) {
0515: r = getSingleResult(session, maxrows);
0516: } else {
0517: r = getResultMain(session);
0518:
0519: if (sortUnion) {
0520: sortResult(session, r);
0521: r.trimResult(getLimitStart(session), getLimitCount(
0522: session, maxrows));
0523: }
0524: }
0525:
0526: // fredt - now there is no need for the sort and group columns
0527: r.setColumnCount(iResultLen);
0528:
0529: return r;
0530: }
0531:
0532: private Result getResultMain(Session session) throws HsqlException {
0533:
0534: Result[] unionResults = new Result[unionArray.length];
0535:
0536: for (int i = 0; i < unionArray.length; i++) {
0537: unionResults[i] = unionArray[i].getSingleResult(session,
0538: Integer.MAX_VALUE);
0539: }
0540:
0541: for (int depth = unionMaxDepth; depth >= 0; depth--) {
0542: for (int pass = 0; pass < 2; pass++) {
0543: for (int i = 0; i < unionArray.length - 1; i++) {
0544: if (unionResults[i] != null
0545: && unionArray[i].unionDepth >= depth) {
0546: if (pass == 0
0547: && unionArray[i].unionType != Select.INTERSECT) {
0548: continue;
0549: }
0550:
0551: if (pass == 1
0552: && unionArray[i].unionType == Select.INTERSECT) {
0553: continue;
0554: }
0555:
0556: int nextIndex = i + 1;
0557:
0558: for (; nextIndex < unionArray.length; nextIndex++) {
0559: if (unionResults[nextIndex] != null) {
0560: break;
0561: }
0562: }
0563:
0564: if (nextIndex == unionArray.length) {
0565: break;
0566: }
0567:
0568: unionArray[i].mergeResults(session,
0569: unionResults[i],
0570: unionResults[nextIndex]);
0571:
0572: unionResults[nextIndex] = unionResults[i];
0573: unionResults[i] = null;
0574: }
0575: }
0576: }
0577: }
0578:
0579: return unionResults[unionResults.length - 1];
0580: }
0581:
0582: /**
0583: * Merges the second result into the first using the unionMode
0584: * set operation.
0585: */
0586: private void mergeResults(Session session, Result first,
0587: Result second) throws HsqlException {
0588:
0589: switch (unionType) {
0590:
0591: case UNION:
0592: first.append(second);
0593: first.removeDuplicates(session, iResultLen);
0594: break;
0595:
0596: case UNIONALL:
0597: first.append(second);
0598: break;
0599:
0600: case INTERSECT:
0601: first.removeDifferent(session, second, iResultLen);
0602: break;
0603:
0604: case EXCEPT:
0605: first.removeSecond(session, second, iResultLen);
0606: break;
0607: }
0608: }
0609:
0610: int getLimitStart(Session session) throws HsqlException {
0611:
0612: if (limitCondition != null) {
0613: Integer limit = (Integer) limitCondition.getArg().getValue(
0614: session);
0615:
0616: if (limit != null) {
0617: return limit.intValue();
0618: }
0619: }
0620:
0621: return 0;
0622: }
0623:
0624: /**
0625: * For SELECT LIMIT n m ....
0626: * finds cases where the result does not have to be fully built and
0627: * returns an adjusted rowCount with LIMIT params.
0628: */
0629: int getLimitCount(Session session, int rowCount)
0630: throws HsqlException {
0631:
0632: int limitCount = 0;
0633:
0634: if (limitCondition != null) {
0635: Integer limit = (Integer) limitCondition.getArg2()
0636: .getValue(session);
0637:
0638: if (limit != null) {
0639: limitCount = limit.intValue();
0640: }
0641: }
0642:
0643: if (rowCount != 0 && (limitCount == 0 || rowCount < limitCount)) {
0644: limitCount = rowCount;
0645: }
0646:
0647: return limitCount;
0648: }
0649:
0650: /**
0651: * translate the rowCount into total number of rows needed from query,
0652: * including any rows skipped at the beginning
0653: */
0654: int getMaxRowCount(Session session, int rowCount)
0655: throws HsqlException {
0656:
0657: int limitStart = getLimitStart(session);
0658: int limitCount = getLimitCount(session, rowCount);
0659:
0660: if (!simpleLimit) {
0661: rowCount = Integer.MAX_VALUE;
0662: } else {
0663: if (rowCount == 0) {
0664: rowCount = limitCount;
0665: }
0666:
0667: if (rowCount == 0
0668: || rowCount > Integer.MAX_VALUE - limitStart) {
0669: rowCount = Integer.MAX_VALUE;
0670: } else {
0671: rowCount += limitStart;
0672: }
0673: }
0674:
0675: return rowCount;
0676: }
0677:
0678: private Result getSingleResult(Session session, int rowCount)
0679: throws HsqlException {
0680:
0681: if (resultMetaData == null) {
0682: prepareResult(session);
0683: }
0684:
0685: Result r = buildResult(session, getMaxRowCount(session,
0686: rowCount));
0687:
0688: // the result is perhaps wider (due to group and order by)
0689: // so use the visible columns to remove duplicates
0690: if (isDistinctSelect) {
0691: r.removeDuplicates(session, iResultLen);
0692: }
0693:
0694: if (!sortUnion) {
0695: sortResult(session, r);
0696: r.trimResult(getLimitStart(session), getLimitCount(session,
0697: rowCount));
0698: }
0699:
0700: return r;
0701: }
0702:
0703: private void prepareSort() {
0704:
0705: if (iOrderLen == 0) {
0706: return;
0707: }
0708:
0709: sortOrder = new int[iOrderLen];
0710: sortDirection = new int[iOrderLen];
0711:
0712: int startCol = iResultLen + iGroupLen + iHavingLen;
0713:
0714: for (int i = startCol, j = 0; j < iOrderLen; i++, j++) {
0715: int colindex = i;
0716:
0717: // fredt - when a union, use the visible select columns for sort comparison
0718: // also whenever a column alias is used
0719: if (exprColumns[i].joinedTableColumnIndex != -1) {
0720: colindex = exprColumns[i].joinedTableColumnIndex;
0721: }
0722:
0723: sortOrder[j] = colindex;
0724: sortDirection[j] = exprColumns[i].isDescending() ? -1 : 1;
0725: }
0726: }
0727:
0728: private void sortResult(Session session, Result r)
0729: throws HsqlException {
0730:
0731: if (iOrderLen == 0) {
0732: return;
0733: }
0734:
0735: r.sortResult(session, sortOrder, sortDirection);
0736: }
0737:
0738: /**
0739: * Check result columns for aggregate or group by violation.
0740: * If any result column is aggregated, then all result columns need to be
0741: * aggregated, unless it is included in the group by clause.
0742: */
0743: private void checkAggregateOrGroupByColumns(int start, int end)
0744: throws HsqlException {
0745:
0746: if (start < end) {
0747: HsqlArrayList colExps = new HsqlArrayList();
0748:
0749: for (int i = start; i < end; i++) {
0750: exprColumns[i].collectInGroupByExpressions(colExps);
0751: }
0752:
0753: for (int i = 0, size = colExps.size(); i < size; i++) {
0754: Expression exp = (Expression) colExps.get(i);
0755:
0756: if (inAggregateOrGroupByClause(exp)) {
0757: continue;
0758: }
0759:
0760: throw Trace.error(Trace.NOT_IN_AGGREGATE_OR_GROUP_BY,
0761: exp);
0762: }
0763: }
0764: }
0765:
0766: private void checkAggregateOrGroupByOrderColumns(int start, int end)
0767: throws HsqlException {
0768:
0769: checkAggregateOrGroupByColumns(start, end);
0770:
0771: if (start < end && isDistinctSelect) {
0772: HsqlArrayList colExps = new HsqlArrayList();
0773:
0774: for (int i = start; i < end; i++) {
0775: exprColumns[i].collectInGroupByExpressions(colExps);
0776: }
0777:
0778: for (int i = 0, size = colExps.size(); i < size; i++) {
0779: Expression exp = (Expression) colExps.get(i);
0780:
0781: if (isSimilarIn(exp, 0, iResultLen)) {
0782: continue;
0783: }
0784:
0785: throw Trace.error(
0786: Trace.INVALID_ORDER_BY_IN_DISTINCT_SELECT, exp);
0787: }
0788: }
0789: }
0790:
0791: /**
0792: * Check if the given expression is acceptable in a select that may
0793: * include aggregate function and/or group by clause.
0794: * <p>
0795: * The expression is acceptable if:
0796: * <UL>
0797: * <LI>The select does not containt any aggregate function;
0798: * <LI>The expression itself can be included in an aggregate select;
0799: * <LI>The expression is defined in the group by clause;
0800: * <LI>All the columns in the expression are defined in the group by clause;
0801: * </UL)
0802: */
0803: private boolean inAggregateOrGroupByClause(Expression exp) {
0804:
0805: if (isGrouped) {
0806: return isSimilarIn(exp, iResultLen, iResultLen + iGroupLen)
0807: || allColumnsAreDefinedIn(exp, groupColumnNames);
0808: } else if (isAggregated) {
0809: return exp.canBeInAggregate();
0810: } else {
0811: return true;
0812: }
0813: }
0814:
0815: /**
0816: * Check if the given expression is similar to any of the eColumn
0817: * expressions within the given range.
0818: */
0819: private boolean isSimilarIn(Expression exp, int start, int end) {
0820:
0821: for (int i = start; i < end; i++) {
0822: if (exp.similarTo(exprColumns[i])) {
0823: return true;
0824: }
0825: }
0826:
0827: return false;
0828: }
0829:
0830: /**
0831: * Check if all the column names used in the given expression are defined
0832: * in the given defined column names.
0833: */
0834: static boolean allColumnsAreDefinedIn(Expression exp,
0835: HashSet definedColumns) {
0836:
0837: HashSet colNames = new HashSet();
0838:
0839: exp.collectAllColumnNames(colNames);
0840:
0841: if ((colNames.size() > 0) && (definedColumns == null)) {
0842: return false;
0843: }
0844:
0845: Iterator i = colNames.iterator();
0846:
0847: while (i.hasNext()) {
0848: if (!definedColumns.contains(i.next())) {
0849: return false;
0850: }
0851: }
0852:
0853: return true;
0854: }
0855:
0856: // fredt@users 20030810 - patch 1.7.2 - OUTER JOIN rewrite
0857: private Result buildResult(Session session, int limitcount)
0858: throws HsqlException {
0859:
0860: GroupedResult gResult = new GroupedResult(this , resultMetaData);
0861: final int len = exprColumns.length;
0862: final int filter = tFilter.length;
0863: boolean[] first = new boolean[filter];
0864: boolean[] outerused = new boolean[filter];
0865: int level = 0;
0866:
0867: // fredt - shortcut needed by OpenOffice to speed up empty query processing for metadata
0868: boolean notempty = !(queryCondition != null
0869: && queryCondition.isFixedConditional() && !queryCondition
0870: .testCondition(session));
0871:
0872: while (notempty && level >= 0) {
0873:
0874: // perform a join
0875: TableFilter t = tFilter[level];
0876: boolean found;
0877: boolean outerfound;
0878:
0879: if (!first[level]) {
0880: found = t.findFirst(session);
0881:
0882: // if outer join, and no inner result, get next outer row
0883: // nonJoinIsNull disallows getting the next outer row in some circumstances
0884: outerused[level] = outerfound = t.isOuterJoin && !found
0885: && !outerused[level] && !t.nonJoinIsNull
0886: && t.nextOuter(session);
0887: first[level] = found;
0888: } else {
0889: found = t.next(session);
0890: outerused[level] = outerfound = t.isOuterJoin && !found
0891: && !first[level] && !outerused[level]
0892: && !t.nonJoinIsNull && t.nextOuter(session);
0893: first[level] = found;
0894: }
0895:
0896: if (!found && !outerfound) {
0897: level--;
0898:
0899: continue;
0900: }
0901:
0902: if (level < filter - 1) {
0903: level++;
0904:
0905: continue;
0906: } else {
0907: while (outerused[level]) {
0908: outerused[level--] = false;
0909: }
0910: }
0911:
0912: // apply condition
0913: if (queryCondition == null
0914: || queryCondition.testCondition(session)) {
0915: try {
0916: Object[] row = new Object[len];
0917:
0918: // gets the group by column values first.
0919: for (int i = gResult.groupBegin; i < gResult.groupEnd; i++) {
0920: row[i] = exprColumns[i].getValue(session);
0921: }
0922:
0923: row = gResult.getRow(row);
0924:
0925: // Get all other values
0926: for (int i = 0; i < gResult.groupBegin; i++) {
0927: row[i] = isAggregated
0928: && exprColumns[i].isAggregate() ? exprColumns[i]
0929: .updateAggregatingValue(session, row[i])
0930: : exprColumns[i].getValue(session);
0931: }
0932:
0933: for (int i = gResult.groupEnd; i < len; i++) {
0934: row[i] = isAggregated
0935: && exprColumns[i].isAggregate() ? exprColumns[i]
0936: .updateAggregatingValue(session, row[i])
0937: : exprColumns[i].getValue(session);
0938: }
0939:
0940: gResult.addRow(row);
0941:
0942: if (gResult.size() >= limitcount) {
0943: break;
0944: }
0945: } catch (HsqlInternalException e) {
0946: continue;
0947: }
0948: }
0949: }
0950:
0951: if (isAggregated && !isGrouped && gResult.size() == 0) {
0952: Object[] row = new Object[len];
0953:
0954: for (int i = 0; i < len; i++) {
0955: row[i] = exprColumns[i].isAggregate() ? null
0956: : exprColumns[i].getValue(session);
0957: }
0958:
0959: gResult.addRow(row);
0960: }
0961:
0962: Iterator it = gResult.iterator();
0963:
0964: while (it.hasNext()) {
0965: Object[] row = (Object[]) it.next();
0966:
0967: if (isAggregated) {
0968: for (int i = 0; i < len; i++) {
0969: if (exprColumns[i].isAggregate()) {
0970: row[i] = exprColumns[i].getAggregatedValue(
0971: session, row[i]);
0972: }
0973: }
0974: }
0975:
0976: if (iHavingLen > 0) {
0977:
0978: // The test value, either aggregate or not, is set already.
0979: // Removes the row that does not satisfy the HAVING
0980: // condition.
0981: if (!Boolean.TRUE.equals(row[iResultLen + iGroupLen])) {
0982: it.remove();
0983: }
0984: }
0985: }
0986:
0987: return gResult.getResult();
0988: }
0989:
0990: /**
0991: * Skeleton under development. Needs a lot of work.
0992: */
0993: public StringBuffer getDDL() throws HsqlException {
0994:
0995: StringBuffer sb = new StringBuffer();
0996:
0997: sb.append(Token.T_SELECT).append(' ');
0998:
0999: //limitStart;
1000: //limitCount;
1001: for (int i = 0; i < iResultLen; i++) {
1002: sb.append(exprColumns[i].getDDL());
1003:
1004: if (i < iResultLen - 1) {
1005: sb.append(',');
1006: }
1007: }
1008:
1009: sb.append(Token.T_FROM);
1010:
1011: for (int i = 0; i < tFilter.length; i++) {
1012:
1013: // find out if any expression in any of the filters isInJoin then use this form
1014: TableFilter filter = tFilter[i];
1015:
1016: // if any expression isInJoin
1017: if (i != 0) {
1018: if (filter.isOuterJoin) {
1019: sb.append(Token.T_FROM).append(' ');
1020: sb.append(Token.T_JOIN).append(' ');
1021: }
1022:
1023: // eStart and eEnd expressions
1024: }
1025:
1026: // otherwise use a comma delimited table list
1027: sb.append(',');
1028: }
1029:
1030: // if there are any expressions that are not isInJoin
1031: sb.append(' ').append(Token.T_WHERE).append(' ');
1032:
1033: for (int i = 0; i < tFilter.length; i++) {
1034: TableFilter filter = tFilter[i];
1035:
1036: // eStart and eEnd expressions that are not isInJoin
1037: }
1038:
1039: // if has GROUP BY
1040: sb.append(' ').append(Token.T_GROUP).append(' ');
1041:
1042: for (int i = iResultLen; i < iResultLen + iGroupLen; i++) {
1043: sb.append(exprColumns[i].getDDL());
1044:
1045: if (i < iResultLen + iGroupLen - 1) {
1046: sb.append(',');
1047: }
1048: }
1049:
1050: // if has HAVING
1051: sb.append(' ').append(Token.T_HAVING).append(' ');
1052:
1053: for (int i = iResultLen + iGroupLen; i < iResultLen + iGroupLen
1054: + iHavingLen; i++) {
1055: sb.append(exprColumns[i].getDDL());
1056:
1057: if (i < iResultLen + iGroupLen - 1) {
1058: sb.append(',');
1059: }
1060: }
1061:
1062: if (unionSelect != null) {
1063: switch (unionType) {
1064:
1065: case EXCEPT:
1066: sb.append(' ').append(Token.T_EXCEPT).append(' ');
1067: break;
1068:
1069: case INTERSECT:
1070: sb.append(' ').append(Token.T_INTERSECT).append(' ');
1071: break;
1072:
1073: case UNION:
1074: sb.append(' ').append(Token.T_UNION).append(' ');
1075: break;
1076:
1077: case UNIONALL:
1078: sb.append(' ').append(Token.T_UNION).append(' ')
1079: .append(Token.T_ALL).append(' ');
1080: break;
1081: }
1082: }
1083:
1084: // if has ORDER BY
1085: int groupByEnd = iResultLen + iGroupLen;
1086: int orderByStart = groupByEnd + iHavingLen;
1087: int orderByEnd = orderByStart + iOrderLen;
1088:
1089: sb.append(' ').append(Token.T_ORDER).append(Token.T_BY).append(
1090: ' ');
1091:
1092: for (int i = orderByStart; i < orderByEnd; i++) {
1093: sb.append(exprColumns[i].getDDL());
1094:
1095: if (i < iResultLen + iGroupLen - 1) {
1096: sb.append(',');
1097: }
1098: }
1099:
1100: return sb;
1101: }
1102:
1103: boolean isResolved = false;
1104:
1105: /**
1106: * @todo - post 1.8.0 - review resolve and check resolve -
1107: * determine if isResolved is specific to main query or the full set including UNION
1108: *
1109: */
1110: boolean resolveAll(Session session, boolean check)
1111: throws HsqlException {
1112:
1113: if (isResolved) {
1114: return true;
1115: }
1116:
1117: resolve(session);
1118:
1119: isResolved = checkResolved(check);
1120:
1121: if (unionSelect != null) {
1122: if (unionSelect.iResultLen != iResultLen) {
1123: throw Trace.error(Trace.COLUMN_COUNT_DOES_NOT_MATCH);
1124: }
1125:
1126: for (int i = 0; i < iResultLen; i++) {
1127: Expression e = exprColumns[i];
1128:
1129: if (!e.isTypeEqual(unionSelect.exprColumns[i])) {
1130: unionSelect.exprColumns[i] = new Expression(
1131: unionSelect.exprColumns[i],
1132: e.getDataType(), e.getColumnSize(), e
1133: .getColumnScale());
1134: }
1135: }
1136:
1137: isResolved &= unionSelect.resolveAll(session, check);
1138: }
1139:
1140: return isResolved;
1141: }
1142:
1143: boolean isResolved() {
1144: return isResolved;
1145: }
1146:
1147: public String describe(Session session) {
1148:
1149: StringBuffer sb;
1150: String temp;
1151:
1152: // temporary : it is currently unclear whether this may affect
1153: // later attempts to retrieve an actual result (calls getResult(1)
1154: // in preProcess mode). Thus, toString() probably should not be called
1155: // on Select objects that will actually be used to retrieve results,
1156: // only on Select objects used by EXPLAIN PLAN FOR
1157: try {
1158: getResult(session, 1);
1159: } catch (HsqlException e) {
1160: }
1161:
1162: sb = new StringBuffer();
1163:
1164: sb.append(super .toString()).append("[\n");
1165:
1166: if (sIntoTable != null) {
1167: sb.append("into table=[").append(sIntoTable.name).append(
1168: "]\n");
1169: }
1170:
1171: if (limitCondition != null) {
1172: sb.append("offset=[").append(
1173: limitCondition.getArg().describe(session)).append(
1174: "]\n");
1175: sb.append("limit=[").append(
1176: limitCondition.getArg2().describe(session)).append(
1177: "]\n");
1178: }
1179:
1180: sb.append("isDistinctSelect=[").append(isDistinctSelect)
1181: .append("]\n");
1182: sb.append("isGrouped=[").append(isGrouped).append("]\n");
1183: sb.append("isAggregated=[").append(isAggregated).append("]\n");
1184: sb.append("columns=[");
1185:
1186: int columns = exprColumns.length - iOrderLen;
1187:
1188: for (int i = 0; i < columns; i++) {
1189: sb.append(exprColumns[i].describe(session));
1190: }
1191:
1192: sb.append("\n]\n");
1193: sb.append("tableFilters=[\n");
1194:
1195: for (int i = 0; i < tFilter.length; i++) {
1196: sb.append("[\n");
1197: sb.append(tFilter[i].describe(session));
1198: sb.append("\n]");
1199: }
1200:
1201: sb.append("]\n");
1202:
1203: temp = queryCondition == null ? "null" : queryCondition
1204: .describe(session);
1205:
1206: sb.append("eCondition=[").append(temp).append("]\n");
1207:
1208: temp = havingCondition == null ? "null" : havingCondition
1209: .describe(session);
1210:
1211: sb.append("havingCondition=[").append(temp).append("]\n");
1212: sb.append("groupColumns=[").append(groupColumnNames).append(
1213: "]\n");
1214:
1215: if (unionSelect != null) {
1216: switch (unionType) {
1217:
1218: case EXCEPT:
1219: sb.append(" EXCEPT ");
1220: break;
1221:
1222: case INTERSECT:
1223: sb.append(" INTERSECT ");
1224: break;
1225:
1226: case UNION:
1227: sb.append(" UNION ");
1228: break;
1229:
1230: case UNIONALL:
1231: sb.append(" UNION ALL ");
1232: break;
1233:
1234: default:
1235: sb.append(" UNKNOWN SET OPERATION ");
1236: }
1237:
1238: sb.append("[\n").append(unionSelect.describe(session))
1239: .append("]\n");
1240: }
1241:
1242: return sb.toString();
1243: }
1244:
1245: Result describeResult() {
1246:
1247: Result r;
1248: Result.ResultMetaData rmd;
1249: Expression e;
1250:
1251: r = new Result(ResultConstants.DATA, iResultLen);
1252: rmd = r.metaData;
1253:
1254: for (int i = 0; i < iResultLen; i++) {
1255: e = exprColumns[i];
1256: rmd.colTypes[i] = e.getDataType();
1257: rmd.colSizes[i] = e.getColumnSize();
1258: rmd.colScales[i] = e.getColumnScale();
1259: rmd.colLabels[i] = e.getAlias();
1260: rmd.isLabelQuoted[i] = e.isAliasQuoted();
1261: rmd.tableNames[i] = e.getTableName();
1262: rmd.colNames[i] = e.getColumnName();
1263:
1264: if (rmd.isTableColumn(i)) {
1265: rmd.colNullable[i] = e.nullability;
1266: rmd.isIdentity[i] = e.isIdentity;
1267: rmd.isWritable[i] = e.isWritable;
1268: }
1269: }
1270:
1271: return r;
1272: }
1273: }
|