KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > hsqldb > 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 JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc limit =
618                 (Integer JavaDoc) 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 JavaDoc limit =
639                 (Integer JavaDoc) 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 JavaDoc[] row = new Object JavaDoc[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 JavaDoc[] row = new Object JavaDoc[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 JavaDoc[] row = (Object JavaDoc[]) 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 JavaDoc getDDL() throws HsqlException {
1000
1001        StringBuffer JavaDoc sb = new StringBuffer JavaDoc();
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 JavaDoc describe(Session session) {
1152
1153        StringBuffer JavaDoc sb;
1154        String JavaDoc 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 JavaDoc();
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
Popular Tags