Select


1   /* Copyright (c) 1995-2000, The Hypersonic SQL Group.
2    * All rights reserved.
3    *
4    * Redistribution and use in source and binary forms, with or without
5    * modification, are permitted provided that the following conditions are met:
6    *
7    * Redistributions of source code must retain the above copyright notice, this
8    * list of conditions and the following disclaimer.
9    *
10   * Redistributions in binary form must reproduce the above copyright notice,
11   * this list of conditions and the following disclaimer in the documentation
12   * and/or other materials provided with the distribution.
13   *
14   * Neither the name of the Hypersonic SQL Group nor the names of its
15   * contributors may be used to endorse or promote products derived from this
16   * software without specific prior written permission.
17   *
18   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21   * ARE DISCLAIMED. IN NO EVENT SHALL THE HYPERSONIC SQL GROUP,
22   * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23   * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24   * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25   * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26   * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28   * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29   *
30   * This software consists of voluntary contributions made by many individuals 
31   * on behalf of the Hypersonic SQL Group.
32   *
33   *
34   * For work added by the HSQL Development Group:
35   *
36   * Copyright (c) 2001-2005, The HSQL Development Group
37   * All rights reserved.
38   *
39   * Redistribution and use in source and binary forms, with or without
40   * modification, are permitted provided that the following conditions are met:
41   *
42   * Redistributions of source code must retain the above copyright notice, this
43   * list of conditions and the following disclaimer.
44   *
45   * Redistributions in binary form must reproduce the above copyright notice,
46   * this list of conditions and the following disclaimer in the documentation
47   * and/or other materials provided with the distribution.
48   *
49   * Neither the name of the HSQL Development Group nor the names of its
50   * contributors may be used to endorse or promote products derived from this
51   * software without specific prior written permission.
52   *
53   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
54   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
55   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
56   * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
57   * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
58   * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
59   * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
60   * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
61   * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
62   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
63   * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
64   */
65  
66  
67  package org.hsqldb;
68  
69  import org.hsqldb.HsqlNameManager.HsqlName;
70  import org.hsqldb.lib.HashMap;
71  import org.hsqldb.lib.HashSet;
72  import org.hsqldb.lib.HsqlArrayList;
73  import org.hsqldb.lib.Iterator;
74  
75  // fredt@users 20010701 - patch 1.6.1 by hybris
76  // basic implementation of LIMIT n m
77  // fredt@users 20020130 - patch 471710 by fredt - LIMIT rewritten
78  // for SELECT LIMIT n m DISTINCT
79  // fredt@users 20020221 - patch 513005 by sqlbob@users (RMP)
80  // type and logging attributes of sIntotable
81  // fredt@users 20020230 - patch 495938 by johnhobs@users - GROUP BY order
82  // fred@users 20020522 - patch 1.7.0 - aggregate functions with DISTINCT
83  // rougier@users 20020522 - patch 552830 - COUNT(DISTINCT)
84  // fredt@users 20020804 - patch 580347 by dkkopp - view speedup
85  // tony_lai@users 20021020 - patch 1.7.2 - improved aggregates and HAVING
86  // boucherb@users 20030811 - patch 1.7.2 - prepared statement support
87  // fredt@users 20031012 - patch 1.7.2 - better OUTER JOIN implementation
88  // fredt@users 20031012 - patch 1.7.2 - SQL standard ORDER BY with UNION and other set queries
89  // fredt@users 200408xx - patch 1.7.2 - correct evaluation of the precedence of nested UNION and other set query
90  
91  /**
92   * The compiled representation of an SQL SELECT.
93   *
94   * Extended in successive versions of HSQLDB.
95   *
96   * @author Thomas Mueller (Hypersonic SQL Group)
97   * @version 1.8.0
98   * @since Hypersonic SQL
99   */
100 class Select {
101 
102     boolean               isDistinctSelect;
103     boolean               isAggregated;
104     private boolean       isGrouped;
105     private HashSet       groupColumnNames;
106     TableFilter[]         tFilter;
107     Expression            limitCondition;
108     Expression            queryCondition;     // null means no condition
109     Expression            havingCondition;    // null means none
110     Expression[]          exprColumns;        // 'result', 'group' and 'order' columns
111     int                   iResultLen;         // number of columns that are 'result'
112     int                   iGroupLen;          // number of columns that are 'group'
113     int                   iHavingLen;         // number of columns that are 'group'
114     int                   iOrderLen;          // number of columns that are 'order'
115     int[]                 sortOrder;
116     int[]                 sortDirection;
117     boolean               sortUnion;          // if true, sort the result of the full union
118     HsqlName              sIntoTable;         // null means not select..into
119     int                   intoType;
120     Select[]              unionArray;         // only set in the first Select in a union chain
121     int                   unionMaxDepth;      // max unionDepth in chain
122     Select                unionSelect;        // null means no union select
123     int                   unionType;
124     int                   unionDepth;
125     static final int      NOUNION   = 0,
126                           UNION     = 1,
127                           UNIONALL  = 2,
128                           INTERSECT = 3,
129                           EXCEPT    = 4;
130     private boolean       simpleLimit;        // true if maxrows can be uses as is
131     Result.ResultMetaData resultMetaData;
132 
133     /**
134      * Experimental.
135      *
136      * Map the column aliases to expressions in order to resolve alias names
137      * in WHERE clauses
138      *
139      */
140     HashMap getColumnAliases() {
141 
142         HashMap aliasMap = new HashMap();
143 
144         for (int i = 0; i < iResultLen; i++) {
145             String   alias = exprColumns[i].getAlias();
146 
147             if (alias != null) {
148                 aliasMap.put(alias, exprColumns[i]);
149             }
150         }
151 
152         return aliasMap;
153     }
154 
155     /**
156      * Method declaration
157      *
158      *
159      * @throws HsqlException
160      */
161     void resolve(Session session) throws HsqlException {
162 
163         resolveTables();
164         resolveTypes(session);
165         setFilterConditions(session);
166     }
167 
168     /**
169      * Method declaration
170      *
171      *
172      * @throws HsqlException
173      */
174     private void resolveTables() throws HsqlException {
175 
176         // replace the aliases with expressions
177         for (int i = iResultLen; i < exprColumns.length; i++) {
178             if (exprColumns[i].getType() == Expression.COLUMN) {
179                 if (exprColumns[i].joinedTableColumnIndex == -1) {
180                     boolean descending = exprColumns[i].isDescending();
181 
182                     exprColumns[i] =
183                         exprColumns[i].getExpressionForAlias(exprColumns,
184                             iResultLen);
185 
186                     if (descending) {
187                         exprColumns[i].setDescending();
188                     }
189                 }
190             } else {
191                 exprColumns[i].replaceAliases(exprColumns, iResultLen);
192             }
193         }
194 
195         if (queryCondition != null) {
196             queryCondition.replaceAliases(exprColumns, iResultLen);
197         }
198 
199         int len = tFilter.length;
200 
201         for (int i = 0; i < len; i++) {
202             resolveTables(tFilter[i]);
203         }
204     }
205 
206     /**
207      * Converts the types of the columns in set operations to those in the first
208      * Select.
209      */
210     void resolveUnionColumnTypes() throws HsqlException {
211 
212         if (unionSelect != null) {
213             if (unionSelect.iResultLen != iResultLen) {
214                 throw Trace.error(Trace.COLUMN_COUNT_DOES_NOT_MATCH);
215             }
216 
217             for (int i = 0; i < iResultLen; i++) {
218                 Expression e = exprColumns[i];
219 
220                 if (!e.isTypeEqual(unionSelect.exprColumns[i])) {
221                     unionSelect.exprColumns[i] =
222                         new Expression(unionSelect.exprColumns[i],
223                                        e.getDataType(), e.getColumnSize(),
224                                        e.getColumnScale());
225                 }
226             }
227         }
228     }
229 
230     /**
231      * Sets the types of all the expressions that have so far resolved.
232      *
233      * @throws HsqlException
234      */
235     void resolveTypes(Session session) throws HsqlException {
236 
237         int len = exprColumns.length;
238 
239         for (int i = 0; i < len; i++) {
240             exprColumns[i].resolveTypes(session);
241         }
242 
243         if (queryCondition != null) {
244             queryCondition.resolveTypes(session);
245         }
246     }
247 
248     void resolveTablesUnion(TableFilter f) throws HsqlException {
249 
250         if (unionArray == null) {
251             resolveTables(f);
252         } else {
253             for (int i = 0; i < unionArray.length; i++) {
254                 unionArray[i].resolveTables(f);
255             }
256         }
257     }
258 
259     /**
260      * Resolves the tables for all the Expression in the Select object
261      * if it is possible to do so with the given TableFilter.
262      *
263      * @param f
264      *
265      * @throws HsqlException
266      */
267     void resolveTables(TableFilter f) throws HsqlException {
268 
269         int len = exprColumns.length;
270 
271         for (int i = 0; i < len; i++) {
272             exprColumns[i].resolveTables(f);
273         }
274 
275         if (queryCondition != null) {
276             queryCondition.resolveTables(f);
277         }
278     }
279 
280     private void setFilterConditions(Session session) throws HsqlException {
281 
282         if (queryCondition == null) {
283             return;
284         }
285 
286         for (int i = 0; i < tFilter.length; i++) {
287             tFilter[i].setConditions(session, queryCondition);
288         }
289     }
290 
291     /**
292      * Check all Expression have resolved. Return true or false as a result.
293      * Throw if false and check parameter is true.
294      *
295      * @throws HsqlException
296      */
297     boolean checkResolved(boolean check) throws HsqlException {
298 
299         boolean result = true;
300         int     len    = exprColumns.length;
301 
302         for (int i = 0; i < len; i++) {
303             result = result && exprColumns[i].checkResolved(check);
304         }
305 
306         if (queryCondition != null) {
307             result = result && queryCondition.checkResolved(check);
308         }
309 
310         if (havingCondition != null) {
311             result = result && havingCondition.checkResolved(check);
312         }
313 
314         for (int i = 0; i < tFilter.length; i++) {
315             if (tFilter[i].filterIndex == null) {
316                 tFilter[i].filterIndex =
317                     tFilter[i].filterTable.getPrimaryIndex();
318             }
319         }
320 
321         return result;
322     }
323 
324     /**
325      * Removes all the TableFilters from the Expressions.
326      *
327      * @throws HsqlException
328      */
329 /*
330     void removeFilters() throws HsqlException {
331 
332         int len = eColumn.length;
333 
334         for (int i = 0; i < len; i++) {
335             eColumn[i].removeFilters();
336         }
337 
338         if (eCondition != null) {
339             eCondition.removeFilters();
340         }
341     }
342 */
343 
344     /**
345      * Returns a single value result or throws if the result has more than
346      * one row with one value.
347      *
348      * @param type data type
349      * @param session context
350      * @return the single valued result
351      * @throws HsqlException
352      */
353     Object   getValue(Session session, int type) throws HsqlException {
354 
355         resolve(session);
356 
357         Result r    = getResult(session, 2);    // 2 records are required for test
358         int    size = r.getSize();
359         int    len  = r.getColumnCount();
360 
361         if (len == 1) {
362             if (size == 0) {
363                 return null;
364             } else if (size == 1) {
365                 Object   o = r.rRoot.data[0];
366 
367                 return r.metaData.colTypes[0] == type ? o
368                                                       : Column.convertObject(
369                                                       o, type);
370             } else {
371                 throw Trace.error(Trace.CARDINALITY_VIOLATION_NO_SUBCLASS);
372             }
373         }
374 
375         HsqlException e =
376             Trace.error(Trace.CARDINALITY_VIOLATION_NO_SUBCLASS);
377 
378         throw new HsqlInternalException(e);
379     }
380 
381     /**
382      * Resolves expressions and pepares thre metadata for the result.
383      */
384     void prepareResult(Session session) throws HsqlException {
385 
386         resolveAll(session, true);
387 
388         if (iGroupLen > 0) {    // has been set in Parser
389             isGrouped        = true;
390             groupColumnNames = new HashSet();
391 
392             for (int i = iResultLen; i < iResultLen + iGroupLen; i++) {
393 
394 //              MarcH: this is wrong for a CASE WHEN statement in a SELECT CASE WHEN ...,<something aggregate> statement
395 //              collectColumnName collects no columns if exprColumns[i]'s expressiontype is Expression.CASEWHEN
396 //              collectAllColumnNames collects all columns used in the CASE WHEN statement
397 //              exprColumns[i].collectColumnName(groupColumnNames);
398                 exprColumns[i].collectAllColumnNames(groupColumnNames);
399             }
400         }
401 
402         int len = exprColumns.length;
403 
404         resultMetaData = new Result.ResultMetaData(len);
405 
406         Result.ResultMetaData rmd = resultMetaData;
407 
408         // tony_lai@users having
409         int groupByStart = iResultLen;
410         int groupByEnd   = groupByStart + iGroupLen;
411         int orderByStart = groupByEnd + iHavingLen;
412         int orderByEnd   = orderByStart + iOrderLen;
413 
414         for (int i = 0; i < len; i++) {
415             Expression e = exprColumns[i];
416 
417             rmd.colTypes[i]  = e.getDataType();
418             rmd.colSizes[i]  = e.getColumnSize();
419             rmd.colScales[i] = e.getColumnScale();
420 
421             if (e.isAggregate()) {
422                 isAggregated = true;
423             }
424 
425             if (i >= groupByStart && i < groupByEnd
426                     &&!exprColumns[i].canBeInGroupBy()) {
427                 Trace.error(Trace.INVALID_GROUP_BY, exprColumns[i]);
428             }
429 
430             if (i >= groupByEnd && i < groupByEnd + iHavingLen
431                     &&!exprColumns[i].isConditional()) {
432                 Trace.error(Trace.INVALID_HAVING, exprColumns[i]);
433             }
434 
435             if (i >= orderByStart && i < orderByEnd
436                     &&!exprColumns[i].canBeInOrderBy()) {
437                 Trace.error(Trace.INVALID_ORDER_BY, exprColumns[i]);
438             }
439 
440             if (i < iResultLen) {
441                 rmd.colLabels[i]     = e.getAlias();
442                 rmd.isLabelQuoted[i] = e.isAliasQuoted();
443                 rmd.schemaNames[i]   = e.getTableSchemaName();
444                 rmd.tableNames[i]    = e.getTableName();
445                 rmd.colNames[i]      = e.getColumnName();
446 
447                 if (rmd.isTableColumn(i)) {
448                     rmd.colNullable[i] = e.nullability;
449                     rmd.isIdentity[i]  = e.isIdentity;
450                     rmd.isWritable[i]  = e.isWritable;
451                 }
452 
453                 rmd.classNames[i] = e.getValueClassName();
454             }
455         }
456 
457         // selected columns
458         checkAggregateOrGroupByColumns(0, iResultLen);
459 
460         // having columns
461         checkAggregateOrGroupByColumns(groupByEnd, orderByStart);
462 
463         // order by columns
464         checkAggregateOrGroupByOrderColumns(orderByStart, orderByEnd);
465         prepareSort();
466 
467         simpleLimit = (isDistinctSelect == false && isGrouped == false
468                        && unionSelect == null && iOrderLen == 0);
469     }
470 
471     /**
472      * This is called externally only on the first Select in a UNION chain.
473      */
474     void prepareUnions() throws HsqlException {
475 
476         int count = 0;
477 
478         for (Select current = this; current != null;
479                 current = current.unionSelect, count++) {}
480 
481         if (count == 1) {
482             if (unionDepth != 0) {
483                 throw Trace.error(Trace.MISSING_CLOSEBRACKET);
484             }
485 
486             return;
487         }
488 
489         unionArray = new Select[count];
490         count      = 0;
491 
492         for (Select current = this; current != null;
493                 current = current.unionSelect, count++) {
494             unionArray[count] = current;
495             unionMaxDepth = current.unionDepth > unionMaxDepth
496                             ? current.unionDepth
497                             : unionMaxDepth;
498         }
499 
500         if (unionArray[unionArray.length - 1].unionDepth != 0) {
501             throw Trace.error(Trace.MISSING_CLOSEBRACKET);
502         }
503     }
504 
505     /**
506      * Returns the result of executing this Select.
507      *
508      * @param maxrows may be 0 to indicate no limit on the number of rows.
509      * Positive values limit the size of the result set.
510      * @return the result of executing this Select
511      * @throws HsqlException if a database access error occurs
512      */
513     Result getResult(Session session, int maxrows) throws HsqlException {
514 
515         Result r;
516 
517         if (unionArray == null) {
518             r = getSingleResult(session, maxrows);
519         } else {
520             r = getResultMain(session);
521 
522             if (sortUnion) {
523                 sortResult(session, r);
524                 r.trimResult(getLimitStart(session),
525                              getLimitCount(session, maxrows));
526             }
527         }
528 
529         // fredt - now there is no need for the sort and group columns
530         r.setColumnCount(iResultLen);
531 
532         return r;
533     }
534 
535     private Result getResultMain(Session session) throws HsqlException {
536 
537         Result[] unionResults = new Result[unionArray.length];
538 
539         for (int i = 0; i < unionArray.length; i++) {
540             unionResults[i] = unionArray[i].getSingleResult(session,
541                     Integer.MAX_VALUE);
542         }
543 
544         for (int depth = unionMaxDepth; depth >= 0; depth--) {
545             for (int pass = 0; pass < 2; pass++) {
546                 for (int i = 0; i < unionArray.length - 1; i++) {
547                     if (unionResults[i] != null
548                             && unionArray[i].unionDepth >= depth) {
549                         if (pass == 0
550                                 && unionArray[i].unionType
551                                    != Select.INTERSECT) {
552                             continue;
553                         }
554 
555                         if (pass == 1
556                                 && unionArray[i].unionType
557                                    == Select.INTERSECT) {
558                             continue;
559                         }
560 
561                         int nextIndex = i + 1;
562 
563                         for (; nextIndex < unionArray.length; nextIndex++) {
564                             if (unionResults[nextIndex] != null) {
565                                 break;
566                             }
567                         }
568 
569                         if (nextIndex == unionArray.length) {
570                             break;
571                         }
572 
573                         unionArray[i].mergeResults(session, unionResults[i],
574                                                    unionResults[nextIndex]);
575 
576                         unionResults[nextIndex] = unionResults[i];
577                         unionResults[i]         = null;
578                     }
579                 }
580             }
581         }
582 
583         return unionResults[unionResults.length - 1];
584     }
585 
586     /**
587      * Merges the second result into the first using the unionMode
588      * set operation.
589      */
590     private void mergeResults(Session session, Result first,
591                               Result second) throws HsqlException {
592 
593         switch (unionType) {
594 
595             case UNION :
596                 first.append(second);
597                 first.removeDuplicates(session, iResultLen);
598                 break;
599 
600             case UNIONALL :
601                 first.append(second);
602                 break;
603 
604             case INTERSECT :
605                 first.removeDifferent(session, second, iResultLen);
606                 break;
607 
608             case EXCEPT :
609                 first.removeSecond(session, second, iResultLen);
610                 break;
611         }
612     }
613 
614     int getLimitStart(Session session) throws HsqlException {
615 
616         if (limitCondition != null) {
617             Integer   limit =
618                 (Integer  ) limitCondition.getArg().getValue(session);
619 
620             if (limit != null) {
621                 return limit.intValue();
622             }
623         }
624 
625         return 0;
626     }
627 
628     /**
629      * For SELECT LIMIT n m ....
630      * finds cases where the result does not have to be fully built and
631      * returns an adjusted rowCount with LIMIT params.
632      */
633     int getLimitCount(Session session, int rowCount) throws HsqlException {
634 
635         int limitCount = 0;
636 
637         if (limitCondition != null) {
638             Integer   limit =
639                 (Integer  ) limitCondition.getArg2().getValue(session);
640 
641             if (limit != null) {
642                 limitCount = limit.intValue();
643             }
644         }
645 
646         if (rowCount != 0 && (limitCount == 0 || rowCount < limitCount)) {
647             limitCount = rowCount;
648         }
649 
650         return limitCount;
651     }
652 
653     /**
654      * translate the rowCount into total number of rows needed from query,
655      * including any rows skipped at the beginning
656      */
657     int getMaxRowCount(Session session, int rowCount) throws HsqlException {
658 
659         int limitStart = getLimitStart(session);
660         int limitCount = getLimitCount(session, rowCount);
661 
662         if (!simpleLimit) {
663             rowCount = Integer.MAX_VALUE;
664         } else {
665             if (rowCount == 0) {
666                 rowCount = limitCount;
667             }
668 
669             if (rowCount == 0 || rowCount > Integer.MAX_VALUE - limitStart) {
670                 rowCount = Integer.MAX_VALUE;
671             } else {
672                 rowCount += limitStart;
673             }
674         }
675 
676         return rowCount;
677     }
678 
679     private Result getSingleResult(Session session,
680                                    int rowCount) throws HsqlException {
681 
682         if (resultMetaData == null) {
683             prepareResult(session);
684         }
685 
686         Result r = buildResult(session, getMaxRowCount(session, rowCount));
687 
688         // the result is perhaps wider (due to group and order by)
689         // so use the visible columns to remove duplicates
690         if (isDistinctSelect) {
691             r.removeDuplicates(session, iResultLen);
692         }
693 
694         if (!sortUnion) {
695             sortResult(session, r);
696             r.trimResult(getLimitStart(session),
697                          getLimitCount(session, rowCount));
698         }
699 
700         return r;
701     }
702 
703     private void prepareSort() {
704 
705         if (iOrderLen == 0) {
706             return;
707         }
708 
709         sortOrder     = new int[iOrderLen];
710         sortDirection = new int[iOrderLen];
711 
712         int startCol = iResultLen + iGroupLen + iHavingLen;
713 
714         for (int i = startCol, j = 0; j < iOrderLen; i++, j++) {
715             int colindex = i;
716 
717             // fredt - when a union, use the visible select columns for sort comparison
718             // also whenever a column alias is used
719             if (exprColumns[i].joinedTableColumnIndex != -1) {
720                 colindex = exprColumns[i].joinedTableColumnIndex;
721             }
722 
723             sortOrder[j]     = colindex;
724             sortDirection[j] = exprColumns[i].isDescending() ? -1
725                                                              : 1;
726         }
727     }
728 
729     private void sortResult(Session session, Result r) throws HsqlException {
730 
731         if (iOrderLen == 0) {
732             return;
733         }
734 
735         r.sortResult(session, sortOrder, sortDirection);
736     }
737 
738     /**
739      * Check result columns for aggregate or group by violation.
740      * If any result column is aggregated, then all result columns need to be
741      * aggregated, unless it is included in the group by clause.
742      */
743     private void checkAggregateOrGroupByColumns(int start,
744             int end) throws HsqlException {
745 
746         if (start < end) {
747             HsqlArrayList colExps = new HsqlArrayList();
748 
749             for (int i = start; i < end; i++) {
750                 exprColumns[i].collectInGroupByExpressions(colExps);
751             }
752 
753             for (int i = 0, size = colExps.size(); i < size; i++) {
754                 Expression exp = (Expression) colExps.get(i);
755 
756                 if (inAggregateOrGroupByClause(exp)) {
757                     continue;
758                 }
759 
760                 throw Trace.error(Trace.NOT_IN_AGGREGATE_OR_GROUP_BY, exp);
761             }
762         }
763     }
764 
765     private void checkAggregateOrGroupByOrderColumns(int start,
766             int end) throws HsqlException {
767 
768         checkAggregateOrGroupByColumns(start, end);
769 
770         if (start < end && isDistinctSelect) {
771             HsqlArrayList colExps = new HsqlArrayList();
772 
773             for (int i = start; i < end; i++) {
774                 exprColumns[i].collectInGroupByExpressions(colExps);
775             }
776 
777             for (int i = 0, size = colExps.size(); i < size; i++) {
778                 Expression exp = (Expression) colExps.get(i);
779 
780                 if (isSimilarIn(exp, 0, iResultLen)) {
781                     continue;
782                 }
783 
784                 throw Trace.error(Trace.INVALID_ORDER_BY_IN_DISTINCT_SELECT,
785                                   exp);
786             }
787         }
788     }
789 
790     /**
791      * Check if the given expression is acceptable in a select that may
792      * include aggregate function and/or group by clause.
793      * <p>
794      * The expression is acceptable if:
795      * <UL>
796      * <LI>The select does not containt any aggregate function;
797      * <LI>The expression itself can be included in an aggregate select;
798      * <LI>The expression is defined in the group by clause;
799      * <LI>All the columns in the expression are defined in the group by clause;
800      * </UL)
801      */
802     private boolean inAggregateOrGroupByClause(Expression exp) {
803 
804         if (isGrouped) {
805             return isSimilarIn(exp, iResultLen, iResultLen + iGroupLen)
806                    || allColumnsAreDefinedIn(exp, groupColumnNames);
807         } else if (isAggregated) {
808             return exp.canBeInAggregate();
809         } else {
810             return true;
811         }
812     }
813 
814     /**
815      * Check if the given expression is similar to any of the eColumn
816      * expressions within the given range.
817      */
818     private boolean isSimilarIn(Expression exp, int start, int end) {
819 
820         for (int i = start; i < end; i++) {
821             if (exp.similarTo(exprColumns[i])) {
822                 return true;
823             }
824         }
825 
826         return false;
827     }
828 
829     /**
830      * Check if all the column names used in the given expression are defined
831      * in the given defined column names.
832      */
833     static boolean allColumnsAreDefinedIn(Expression exp,
834                                           HashSet definedColumns) {
835 
836         HashSet colNames = new HashSet();
837 
838         exp.collectAllColumnNames(colNames);
839 
840         if ((colNames.size() > 0) && (definedColumns == null)) {
841             return false;
842         }
843 
844         Iterator i = colNames.iterator();
845 
846         while (i.hasNext()) {
847             if (!definedColumns.contains(i.next())) {
848                 return false;
849             }
850         }
851 
852         return true;
853     }
854 
855 // fredt@users 20030810 - patch 1.7.2 - OUTER JOIN rewrite
856     private Result buildResult(Session session,
857                                int limitcount) throws HsqlException {
858 
859         GroupedResult gResult   = new GroupedResult(this, resultMetaData);
860         final int     len       = exprColumns.length;
861         final int     filter    = tFilter.length;
862         boolean[]     first     = new boolean[filter];
863         boolean[]     outerused = new boolean[filter];
864         int           level     = 0;
865 
866         // fredt - shortcut needed by OpenOffice to speed up empty query processing for metadata
867         boolean notempty = !(queryCondition != null
868                              && queryCondition.isFixedConditional()
869                              &&!queryCondition.testCondition(session));
870 
871         while (notempty && level >= 0) {
872 
873             // perform a join
874             TableFilter t = tFilter[level];
875             boolean     found;
876             boolean     outerfound;
877 
878             if (!first[level]) {
879                 found = t.findFirst(session);
880 
881                 // if outer join, and no inner result, get next outer row
882                 // nonJoinIsNull disallows getting the next outer row in some circumstances
883                 outerused[level] = outerfound = t.isOuterJoin &&!found
884                                                 &&!outerused[level]
885                                                 &&!t.nonJoinIsNull
886                                                 && t.nextOuter(session);
887                 first[level] = found;
888             } else {
889                 found = t.next(session);
890                 outerused[level] = outerfound = t.isOuterJoin &&!found
891                                                 &&!first[level]
892                                                 &&!outerused[level]
893                                                 &&!t.nonJoinIsNull
894                                                 && t.nextOuter(session);
895                 first[level] = found;
896             }
897 
898             if (!found &&!outerfound) {
899                 level--;
900 
901                 continue;
902             }
903 
904             if (level < filter - 1) {
905                 level++;
906 
907                 continue;
908             } else {
909                 while (outerused[level]) {
910                     outerused[level--] = false;
911                 }
912             }
913 
914             // apply condition
915             if (queryCondition == null
916                     || queryCondition.testCondition(session)) {
917                 try {
918                     Object  [] row = new Object  [len];
919 
920                     // gets the group by column values first.
921                     for (int i = gResult.groupBegin; i < gResult.groupEnd;
922                             i++) {
923                         row[i] = exprColumns[i].getValue(session);
924                     }
925 
926                     row = gResult.getRow(row);
927 
928                     // Get all other values
929                     for (int i = 0; i < gResult.groupBegin; i++) {
930                         row[i] =
931                             isAggregated && exprColumns[i].isAggregate()
932                             ? exprColumns[i].updateAggregatingValue(session,
933                                 row[i])
934                             : exprColumns[i].getValue(session);
935                     }
936 
937                     for (int i = gResult.groupEnd; i < len; i++) {
938                         row[i] =
939                             isAggregated && exprColumns[i].isAggregate()
940                             ? exprColumns[i].updateAggregatingValue(session,
941                                 row[i])
942                             : exprColumns[i].getValue(session);
943                     }
944 
945                     gResult.addRow(row);
946 
947                     if (gResult.size() >= limitcount) {
948                         break;
949                     }
950                 } catch (HsqlInternalException e) {
951                     continue;
952                 }
953             }
954         }
955 
956         if (isAggregated &&!isGrouped && gResult.size() == 0) {
957             Object  [] row = new Object  [len];
958 
959             for (int i = 0; i < len; i++) {
960                 row[i] = exprColumns[i].isAggregate() ? null
961                                                       : exprColumns[i]
962                                                       .getValue(session);
963             }
964 
965             gResult.addRow(row);
966         }
967 
968         Iterator it = gResult.iterator();
969 
970         while (it.hasNext()) {
971             Object  [] row = (Object  []) it.next();
972 
973             if (isAggregated) {
974                 for (int i = 0; i < len; i++) {
975                     if (exprColumns[i].isAggregate()) {
976                         row[i] = exprColumns[i].getAggregatedValue(session,
977                                 row[i]);
978                     }
979                 }
980             }
981 
982             if (iHavingLen > 0) {
983 
984                 // The test value, either aggregate or not, is set already.
985                 // Removes the row that does not satisfy the HAVING
986                 // condition.
987                 if (!Boolean.TRUE.equals(row[iResultLen + iGroupLen])) {
988                     it.remove();
989                 }
990             }
991         }
992 
993         return gResult.getResult();
994     }
995 
996     /**
997      * Skeleton under development. Needs a lot of work.
998      */
999     public StringBuffer   getDDL() throws HsqlException {
1000
1001        StringBuffer   sb = new StringBuffer  ();
1002
1003        sb.append(Token.T_SELECT).append(' ');
1004
1005        //limitStart;
1006        //limitCount;
1007        for (int i = 0; i < iResultLen; i++) {
1008            sb.append(exprColumns[i].getDDL());
1009
1010            if (i < iResultLen - 1) {
1011                sb.append(',');
1012            }
1013        }
1014
1015        sb.append(Token.T_FROM);
1016
1017        for (int i = 0; i < tFilter.length; i++) {
1018
1019            // find out if any expression in any of the filters isInJoin then use this form
1020            TableFilter filter = tFilter[i];
1021
1022            // if any expression isInJoin
1023            if (i != 0) {
1024                if (filter.isOuterJoin) {
1025                    sb.append(Token.T_FROM).append(' ');
1026                    sb.append(Token.T_JOIN).append(' ');
1027                }
1028
1029                // eStart and eEnd expressions
1030            }
1031
1032            // otherwise use a comma delimited table list
1033            sb.append(',');
1034        }
1035
1036        // if there are any expressions that are not isInJoin
1037        sb.append(' ').append(Token.T_WHERE).append(' ');
1038
1039        for (int i = 0; i < tFilter.length; i++) {
1040            TableFilter filter = tFilter[i];
1041
1042            // eStart and eEnd expressions that are not isInJoin
1043        }
1044
1045        // if has GROUP BY
1046        sb.append(' ').append(Token.T_GROUP).append(' ');
1047
1048        for (int i = iResultLen; i < iResultLen + iGroupLen; i++) {
1049            sb.append(exprColumns[i].getDDL());
1050
1051            if (i < iResultLen + iGroupLen - 1) {
1052                sb.append(',');
1053            }
1054        }
1055
1056        // if has HAVING
1057        sb.append(' ').append(Token.T_HAVING).append(' ');
1058
1059        for (int i = iResultLen + iGroupLen;
1060                i < iResultLen + iGroupLen + iHavingLen; i++) {
1061            sb.append(exprColumns[i].getDDL());
1062
1063            if (i < iResultLen + iGroupLen - 1) {
1064                sb.append(',');
1065            }
1066        }
1067
1068        if (unionSelect != null) {
1069            switch (unionType) {
1070
1071                case EXCEPT :
1072                    sb.append(' ').append(Token.T_EXCEPT).append(' ');
1073                    break;
1074
1075                case INTERSECT :
1076                    sb.append(' ').append(Token.T_INTERSECT).append(' ');
1077                    break;
1078
1079                case UNION :
1080                    sb.append(' ').append(Token.T_UNION).append(' ');
1081                    break;
1082
1083                case UNIONALL :
1084                    sb.append(' ').append(Token.T_UNION).append(' ').append(
1085                        Token.T_ALL).append(' ');
1086                    break;
1087            }
1088        }
1089
1090        // if has ORDER BY
1091        int groupByEnd   = iResultLen + iGroupLen;
1092        int orderByStart = groupByEnd + iHavingLen;
1093        int orderByEnd   = orderByStart + iOrderLen;
1094
1095        sb.append(' ').append(Token.T_ORDER).append(Token.T_BY).append(' ');
1096
1097        for (int i = orderByStart; i < orderByEnd; i++) {
1098            sb.append(exprColumns[i].getDDL());
1099
1100            if (i < iResultLen + iGroupLen - 1) {
1101                sb.append(',');
1102            }
1103        }
1104
1105        return sb;
1106    }
1107
1108    boolean isResolved = false;
1109
1110    /**
1111     * @todo - post 1.8.0 - review resolve and check resolve -
1112     * determine if isResolved is specific to main query or the full set including UNION
1113     *
1114     */
1115    boolean resolveAll(Session session, boolean check) throws HsqlException {
1116
1117        if (isResolved) {
1118            return true;
1119        }
1120
1121        resolve(session);
1122
1123        isResolved = checkResolved(check);
1124
1125        if (unionSelect != null) {
1126            if (unionSelect.iResultLen != iResultLen) {
1127                throw Trace.error(Trace.COLUMN_COUNT_DOES_NOT_MATCH);
1128            }
1129
1130            for (int i = 0; i < iResultLen; i++) {
1131                Expression e = exprColumns[i];
1132
1133                if (!e.isTypeEqual(unionSelect.exprColumns[i])) {
1134                    unionSelect.exprColumns[i] =
1135                        new Expression(unionSelect.exprColumns[i],
1136                                       e.getDataType(), e.getColumnSize(),
1137                                       e.getColumnScale());
1138                }
1139            }
1140
1141            isResolved &= unionSelect.resolveAll(session, check);
1142        }
1143
1144        return isResolved;
1145    }
1146
1147    boolean isResolved() {
1148        return isResolved;
1149    }
1150
1151    public String   describe(Session session) {
1152
1153        StringBuffer   sb;
1154        String         temp;
1155
1156        // temporary :  it is currently unclear whether this may affect
1157        // later attempts to retrieve an actual result (calls getResult(1)
1158        // in preProcess mode).  Thus, toString() probably should not be called
1159        // on Select objects that will actually be used to retrieve results,
1160        // only on Select objects used by EXPLAIN PLAN FOR
1161        try {
1162            getResult(session, 1);
1163        } catch (HsqlException e) {}
1164
1165        sb = new StringBuffer  ();
1166
1167        sb.append(super.toString()).append("[\n");
1168
1169        if (sIntoTable != null) {
1170            sb.append("into table=[").append(sIntoTable.name).append("]\n");
1171        }
1172
1173        if (limitCondition != null) {
1174            sb.append("offset=[").append(
1175                limitCondition.getArg().describe(session)).append("]\n");
1176            sb.append("limit=[").append(
1177                limitCondition.getArg2().describe(session)).append("]\n");
1178        }
1179
1180        sb.append("isDistinctSelect=[").append(isDistinctSelect).append(
1181            "]\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"
1204                                      : queryCondition.describe(session);
1205
1206        sb.append("eCondition=[").append(temp).append("]\n");
1207
1208        temp = havingCondition == null ? "null"
1209                                       : havingCondition.describe(session);
1210
1211        sb.append("havingCondition=[").append(temp).append("]\n");
1212        sb.append("groupColumns=[").append(groupColumnNames).append("]\n");
1213
1214        if (unionSelect != null) {
1215            switch (unionType) {
1216
1217                case EXCEPT :
1218                    sb.append(" EXCEPT ");
1219                    break;
1220
1221                case INTERSECT :
1222                    sb.append(" INTERSECT ");
1223                    break;
1224
1225                case UNION :
1226                    sb.append(" UNION ");
1227                    break;
1228
1229                case UNIONALL :
1230                    sb.append(" UNION ALL ");
1231                    break;
1232
1233                default :
1234                    sb.append(" UNKNOWN SET OPERATION ");
1235            }
1236
1237            sb.append("[\n").append(unionSelect.describe(session)).append(
1238                "]\n");
1239        }
1240
1241        return sb.toString();
1242    }
1243
1244    Result describeResult() {
1245
1246        Result                r;
1247        Result.ResultMetaData rmd;
1248        Expression            e;
1249
1250        r   = new Result(ResultConstants.DATA, iResultLen);
1251        rmd = r.metaData;
1252
1253        for (int i = 0; i < iResultLen; i++) {
1254            e                    = exprColumns[i];
1255            rmd.colTypes[i]      = e.getDataType();
1256            rmd.colSizes[i]      = e.getColumnSize();
1257            rmd.colScales[i]     = e.getColumnScale();
1258            rmd.colLabels[i]     = e.getAlias();
1259            rmd.isLabelQuoted[i] = e.isAliasQuoted();
1260            rmd.tableNames[i]    = e.getTableName();
1261            rmd.colNames[i]      = e.getColumnName();
1262
1263            if (rmd.isTableColumn(i)) {
1264                rmd.colNullable[i] = e.nullability;
1265                rmd.isIdentity[i]  = e.isIdentity;
1266                rmd.isWritable[i]  = e.isWritable;
1267            }
1268        }
1269
1270        return r;
1271    }
1272}
1273
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags