Source code: org/hsqldb/Select.java
1 /* Copyrights and Licenses
2 *
3 * This product includes Hypersonic SQL.
4 * Originally developed by Thomas Mueller and the Hypersonic SQL Group.
5 *
6 * Copyright (c) 1995-2000 by the Hypersonic SQL Group. All rights reserved.
7 * Redistribution and use in source and binary forms, with or without modification, are permitted
8 * provided that the following conditions are met:
9 * - Redistributions of source code must retain the above copyright notice, this list of conditions
10 * and the following disclaimer.
11 * - Redistributions in binary form must reproduce the above copyright notice, this list of
12 * conditions and the following disclaimer in the documentation and/or other materials
13 * provided with the distribution.
14 * - All advertising materials mentioning features or use of this software must display the
15 * following acknowledgment: "This product includes Hypersonic SQL."
16 * - Products derived from this software may not be called "Hypersonic SQL" nor may
17 * "Hypersonic SQL" appear in their names without prior written permission of the
18 * Hypersonic SQL Group.
19 * - Redistributions of any form whatsoever must retain the following acknowledgment: "This
20 * product includes Hypersonic SQL."
21 * This software is provided "as is" and any expressed or implied warranties, including, but
22 * not limited to, the implied warranties of merchantability and fitness for a particular purpose are
23 * disclaimed. In no event shall the Hypersonic SQL Group or its contributors be liable for any
24 * direct, indirect, incidental, special, exemplary, or consequential damages (including, but
25 * not limited to, procurement of substitute goods or services; loss of use, data, or profits;
26 * or business interruption). However caused any on any theory of liability, whether in contract,
27 * strict liability, or tort (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 * This software consists of voluntary contributions made by many individuals on behalf of the
30 * Hypersonic SQL Group.
31 *
32 *
33 * For work added by the HSQL Development Group:
34 *
35 * Copyright (c) 2001-2002, The HSQL Development Group
36 * All rights reserved.
37 *
38 * Redistribution and use in source and binary forms, with or without
39 * modification, are permitted provided that the following conditions are met:
40 *
41 * Redistributions of source code must retain the above copyright notice, this
42 * list of conditions and the following disclaimer, including earlier
43 * license statements (above) and comply with all above license conditions.
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, including earlier
48 * license statements (above) and comply with all above license conditions.
49 *
50 * Neither the name of the HSQL Development Group nor the names of its
51 * contributors may be used to endorse or promote products derived from this
52 * software without specific prior written permission.
53 *
54 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
55 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
56 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
57 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
58 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
59 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
60 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
61 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
62 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
63 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
64 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
65 */
66
67
68 package org.hsqldb;
69
70 import java.sql.SQLException;
71 import java.sql.Types;
72
73 // fred@users 20020522 - patch 1.7.0 - aggregate functions with DISTINCT
74 // rougier@users 20020522 - patch 552830 - COUNT(DISTINCT)
75
76 /**
77 * Class declaration
78 *
79 *
80 * @version 1.7.0
81 */
82 class Select {
83
84 boolean isPreProcess;
85 boolean isDistinctSelect;
86 private boolean isDistinctAggregate;
87 private boolean isAggregated;
88 private boolean isGrouped;
89 private Object[] aggregateRow;
90 private int aggregateCount;
91 TableFilter tFilter[];
92 Expression eCondition; // null means no condition
93 Expression havingCondition; // null means none
94 Expression eColumn[]; // 'result', 'group' and 'order' columns
95 int iResultLen; // number of columns that are 'result'
96 int iGroupLen; // number of columns that are 'group'
97 int iOrderLen; // number of columns that are 'order'
98 Select sUnion; // null means no union select
99 HsqlName sIntoTable; // null means not select..into
100
101 // fredt@users 20020221 - patch 513005 by sqlbob@users (RMP)
102 // type and logging attributes of sIntotable
103 int intoType = Table.MEMORY_TABLE;
104
105 // boolean intoTemp;
106 boolean isIntoTableQuoted;
107 int iUnionType;
108 static final int UNION = 1,
109 UNIONALL = 2,
110 INTERSECT = 3,
111 EXCEPT = 4;
112
113 // fredt@users 20010701 - patch 1.6.1 by hybris
114 // basic implementation of LIMIT n m
115 int limitStart = 0; // set only by the LIMIT keyword
116 int limitCount = 0; // set only by the LIMIT keyword
117
118 /**
119 * Set to preprocess mode
120 *
121 */
122 void setPreProcess() {
123 isPreProcess = true;
124 }
125
126 /**
127 * Method declaration
128 *
129 *
130 * @throws SQLException
131 */
132 void resolve() throws SQLException {
133
134 int len = tFilter.length;
135
136 for (int i = 0; i < len; i++) {
137 resolve(tFilter[i], true);
138 }
139 }
140
141 /**
142 * Method declaration
143 *
144 *
145 * @param f
146 * @param ownfilter
147 *
148 * @throws SQLException
149 */
150 void resolve(TableFilter f, boolean ownfilter) throws SQLException {
151
152 if (eCondition != null) {
153
154 // first set the table filter in the condition
155 eCondition.resolve(f);
156
157 if (f != null && ownfilter) {
158
159 // the table filter tries to get as many conditions as
160 // possible but only if it belongs to this query
161 f.setCondition(eCondition);
162 }
163 }
164
165 int len = eColumn.length;
166
167 for (int i = 0; i < len; i++) {
168 eColumn[i].resolve(f);
169 }
170 }
171
172 /**
173 * Method declaration
174 *
175 *
176 * @throws SQLException
177 */
178 void checkResolved() throws SQLException {
179
180 if (eCondition != null) {
181 eCondition.checkResolved();
182 }
183
184 int len = eColumn.length;
185
186 for (int i = 0; i < len; i++) {
187 eColumn[i].checkResolved();
188 }
189 }
190
191 /**
192 * Method declaration
193 *
194 *
195 * @param type
196 *
197 * @return
198 *
199 * @throws SQLException
200 */
201 Object getValue(int type) throws SQLException {
202
203 resolve();
204
205 Result r = getResult(2); // 2 records are (already) too much
206 int size = r.getSize();
207 int len = r.getColumnCount();
208
209 Trace.check(size == 1 && len == 1, Trace.SINGLE_VALUE_EXPECTED);
210
211 Object o = r.rRoot.data[0];
212
213 if (r.colType[0] == type) {
214 return o;
215 }
216
217 return Column.convertObject(o, type);
218 }
219
220 /**
221 * maxrow may be 0 to indicate no limit on the number of rows, or -1
222 * to indicate 0 size result (used for pre-processing the selects in
223 * view statements. positive values limit the size of the result set.
224 *
225 *
226 * @param maxrows
227 *
228 * @return
229 *
230 * @throws SQLException
231 */
232
233 // fredt@users 20020130 - patch 471710 by fredt - LIMIT rewritten
234 // for SELECT LIMIT n m DISTINCT
235 // fredt@users 20020804 - patch 580347 by dkkopp - view speedup
236 Result getResult(int maxrows) throws SQLException {
237
238 resolve();
239 checkResolved();
240
241 if (sUnion != null && sUnion.iResultLen != iResultLen) {
242 throw Trace.error(Trace.COLUMN_COUNT_DOES_NOT_MATCH);
243 }
244
245 if (iGroupLen > 0) { // has been set in Parser
246 isGrouped = true;
247 }
248
249 int len = eColumn.length;
250 Result r = new Result(len);
251
252 for (int i = 0; i < len; i++) {
253 Expression e = eColumn[i];
254
255 r.colType[i] = e.getDataType();
256 r.colSize[i] = e.getColumnSize();
257 r.colScale[i] = e.getColumnScale();
258
259 if (e.isAggregate()) {
260 isAggregated = true;
261
262 if (!isGrouped && e.isDistinctAggregate()) {
263 isDistinctAggregate = true;
264 }
265 }
266 }
267
268 // Object aggregateRow[] = null;
269 if (isAggregated) {
270 aggregateRow = new Object[len];
271 }
272
273 // fredt@users 20020130 - patch 471710 by fredt - LIMIT rewritten
274 // for SELECT LIMIT n m DISTINCT
275 // find cases where the result does not have to be fully built and
276 // set issimplemaxrows and adjust maxrows with LIMIT params
277 // chnages made to apply LIMIT only to the containing SELECT
278 // so they can be used as part of UNION and other set operations
279 if (maxrows == 0) {
280 maxrows = limitCount;
281 } else if (limitCount == 0) {
282 limitCount = maxrows;
283 } else {
284 maxrows = limitCount = (maxrows > limitCount) ? limitCount
285 : maxrows;
286 }
287
288 boolean issimplemaxrows = false;
289
290 if (maxrows != 0 && isDistinctSelect == false
291 && isDistinctAggregate == false && isGrouped == false
292 && sUnion == null && iOrderLen == 0) {
293 issimplemaxrows = true;
294 }
295
296 int limitcount = issimplemaxrows ? limitStart + maxrows
297 : Integer.MAX_VALUE;
298
299 buildResult(r, limitcount);
300
301 if (isAggregated &&!isGrouped &&!isDistinctAggregate) {
302 addAggregateRow(r, aggregateRow, len, aggregateCount);
303 } else if (isGrouped) {
304 groupResult(r);
305 } else if (isDistinctAggregate) {
306 r.removeDuplicates();
307 buildDistinctAggregates(r);
308
309 for (int i = 0; i < len; i++) {
310 Expression e = eColumn[i];
311
312 e.setDistinctAggregate(false);
313
314 r.colType[i] = e.getDataType();
315 r.colSize[i] = e.getColumnSize();
316 r.colScale[i] = e.getColumnScale();
317 }
318 }
319
320 // the result is maybe bigger (due to group and order by)
321 // but don't tell this anybody else
322 if (isDistinctSelect) {
323 int fullColumnCount = r.getColumnCount();
324
325 r.setColumnCount(iResultLen);
326 r.removeDuplicates();
327 r.setColumnCount(fullColumnCount);
328 }
329
330 if (iOrderLen != 0) {
331 int order[] = new int[iOrderLen];
332 int way[] = new int[iOrderLen];
333
334 // fredt@users 20020230 - patch 495938 by johnhobs@users - GROUP BY order
335 for (int i = iResultLen + (isGrouped ? iGroupLen
336 : 0), j = 0; j < iOrderLen;
337 i++, j++) {
338 order[j] = i;
339 way[j] = eColumn[i].isDescending() ? -1
340 : 1;
341 }
342
343 r.sortResult(order, way);
344 }
345
346 // fredt - now there is no need for the sort and group columns
347 r.setColumnCount(iResultLen);
348
349 for (int i = 0; i < iResultLen; i++) {
350 Expression e = eColumn[i];
351
352 r.sLabel[i] = e.getAlias();
353 r.isLabelQuoted[i] = e.isAliasQuoted();
354 r.sTable[i] = e.getTableName();
355 r.sName[i] = e.getColumnName();
356 }
357
358 // fredt@users 20020130 - patch 471710 - LIMIT rewritten
359 r.trimResult(limitStart, limitCount);
360
361 if (sUnion != null) {
362 Result x = sUnion.getResult(0);
363
364 if (iUnionType == UNION) {
365 r.append(x);
366 r.removeDuplicates();
367 } else if (iUnionType == UNIONALL) {
368 r.append(x);
369 } else if (iUnionType == INTERSECT) {
370 r.removeDifferent(x);
371 } else if (iUnionType == EXCEPT) {
372 r.removeSecond(x);
373 }
374 }
375
376 return r;
377 }
378
379 /**
380 * Method declaration
381 *
382 *
383 * @param row
384 * @param n
385 * @param len
386 *
387 * @throws SQLException
388 */
389 private void updateAggregateRow(Object row[], Object n[],
390 int len) throws SQLException {
391
392 for (int i = 0; i < len; i++) {
393 int type = eColumn[i].getDataType();
394
395 switch (eColumn[i].getType()) {
396
397 case Expression.DIST_COUNT :
398 Integer increment = (n[i] == null) ? Expression.INTEGER_0
399 : Expression.INTEGER_1;
400
401 row[i] = Column.sum(row[i], increment, Types.INTEGER);
402 break;
403
404 case Expression.COUNT :
405 case Expression.AVG :
406 case Expression.SUM :
407 row[i] = Column.sum(row[i], n[i], type);
408 break;
409
410 case Expression.MIN :
411 row[i] = Column.min(row[i], n[i], type);
412 break;
413
414 case Expression.MAX :
415 row[i] = Column.max(row[i], n[i], type);
416 break;
417
418 default :
419 row[i] = n[i];
420 break;
421 }
422 }
423 }
424
425 /**
426 * Method declaration
427 *
428 *
429 * @param x
430 * @param row
431 * @param len
432 * @param count
433 *
434 * @throws SQLException
435 */
436 private void addAggregateRow(Result x, Object row[], int len,
437 int count) throws SQLException {
438
439 for (int i = 0; i < len; i++) {
440 int t = eColumn[i].getType();
441
442 if (t == Expression.AVG) {
443 row[i] = Column.avg(row[i], eColumn[i].getDataType(), count);
444 } else if (t == Expression.COUNT) {
445
446 // this fixes the problem with count(*) on a empty table
447 if (row[i] == null) {
448 row[i] = Expression.INTEGER_0;
449 }
450 }
451 }
452
453 x.add(row);
454 }
455
456 private void buildResult(Result r, int limitcount) throws SQLException {
457
458 int len = eColumn.length;
459 int count = 0;
460 int filter = tFilter.length;
461 boolean first[] = new boolean[filter];
462 int level = 0;
463 boolean addtoaggregate = isAggregated &&!isGrouped
464 &&!isDistinctAggregate;
465
466 while (level >= 0 &&!isPreProcess) {
467 TableFilter t = tFilter[level];
468 boolean found;
469
470 if (!first[level]) {
471 found = t.findFirst();
472 first[level] = found;
473 } else {
474 found = t.next();
475 first[level] = found;
476 }
477
478 if (!found) {
479 level--;
480
481 continue;
482 }
483
484 if (level < filter - 1) {
485 level++;
486
487 continue;
488 }
489
490 // apply condition
491 if (eCondition == null || eCondition.test()) {
492 Object row[] = new Object[len];
493
494 for (int i = 0; i < len; i++) {
495 row[i] = eColumn[i].getValue();
496 }
497
498 count++;
499
500 // fredt@users 20010701 - patch for bug 416144 416146 430615 by fredt
501 if (addtoaggregate) {
502 updateAggregateRow(aggregateRow, row, len);
503 } else {
504 r.add(row);
505
506 if (count >= limitcount) {
507 break;
508 }
509 }
510 }
511 }
512
513 if (addtoaggregate) {
514 aggregateCount = count;
515 }
516 }
517
518 private void groupResult(Result r) throws SQLException {
519
520 int len = eColumn.length;
521 int count = 0;
522 int order[] = new int[iGroupLen];
523 int way[] = new int[iGroupLen];
524
525 for (int i = iResultLen, j = 0; j < iGroupLen; i++, j++) {
526 order[j] = i;
527 way[j] = 1;
528 }
529
530 r.sortResult(order, way);
531
532 Record n = r.rRoot;
533 Result x = new Result(len);
534
535 do {
536 Object row[] = new Object[len];
537
538 count = 0;
539
540 boolean newgroup = false;
541
542 while (n != null && newgroup == false) {
543 count++;
544
545 // fredt@users 20020215 - patch 476650 by johnhobs@users - GROUP BY aggregates
546 for (int i = iResultLen; i < iResultLen + iGroupLen; i++) {
547 if (n.next == null) {
548 newgroup = true;
549 } else if (Column.compare(
550 n.data[i], n.next.data[i], r.colType[i]) != 0) {
551
552 // can't use .equals because 'null' is also one group
553 newgroup = true;
554 }
555 }
556
557 updateAggregateRow(row, n.data, len);
558
559 n = n.next;
560 }
561
562 // fredt@users 20020320 - patch 476650 by fredt - empty GROUP BY
563 if (isAggregated || count > 0) {
564 addAggregateRow(x, row, len, count);
565 }
566 } while (n != null);
567
568 r.setRows(x);
569 }
570
571 private void buildDistinctAggregates(Result r) throws SQLException {
572
573 int len = eColumn.length;
574 int count = 0;
575 Record n = r.rRoot;
576 Result x = new Result(len);
577 Object row[] = new Object[len];
578
579 count = 0;
580
581 while (n != null) {
582 count++;
583
584 updateAggregateRow(row, n.data, len);
585
586 n = n.next;
587 }
588
589 if (isAggregated || count > 0) {
590 addAggregateRow(x, row, len, count);
591 }
592
593 r.setRows(x);
594 }
595 }