Expression


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.index.RowIterator;
71  import org.hsqldb.lib.HashSet;
72  import org.hsqldb.lib.HsqlArrayList;
73  import org.hsqldb.store.ValuePool;
74  
75  // fredt@users 20020215 - patch 1.7.0 by fredt
76  // to preserve column size etc. when SELECT INTO TABLE is used
77  // tony_lai@users 20021020 - patch 1.7.2 - improved aggregates and HAVING
78  // fredt@users 20021112 - patch 1.7.2 by Nitin Chauhan - use of switch
79  // rewrite of the majority of multiple if(){}else{} chains with switch(){}
80  // vorburger@users 20021229 - patch 1.7.2 - null handling
81  // boucherb@users 200307?? - patch 1.7.2 - resolve param nodes
82  // boucherb@users 200307?? - patch 1.7.2 - compress constant expr during resolve
83  // boucherb@users 200307?? - patch 1.7.2 - eager pmd and rsmd
84  // boucherb@users 20031005 - patch 1.7.2 - optimised LIKE
85  // boucherb@users 20031005 - patch 1.7.2 - improved IN value lists
86  // fredt@users 20031012 - patch 1.7.2 - better OUTER JOIN implementation
87  // thomasm@users 20041001 - patch 1.7.3 - BOOLEAN undefined handling
88  // fredt@users 200412xx - patch 1.7.2 - evaluation of time functions
89  // boucherb@users 20050516 - patch 1.8.0 - remove DITypeInfo usage for faster
90  //                                         statement compilation
91  
92  /**
93   * Expression class.
94   *
95   * The core functionality of this class was inherited from HypersonicSQL and
96   * extensively rewritten and extended in successive versions of HSQLDB.
97   *
98   * @author Thomas Mueller (Hypersonic SQL Group)
99   * @version    1.8.0
100  * @since Hypersonic SQL
101  */
102 
103 /** @todo - fredt - constant TRUE and FALSE type expressions have valueData of
104   * type BOOLEAN, while computed expressions have no valueData; this should be
105   * normalised in future
106   */
107 public class Expression {
108 
109     // leaf types
110     static final int VALUE     = 1,
111                      COLUMN    = 2,
112                      QUERY     = 3,
113                      TRUE      = 4,
114                      FALSE     = -4,    // arbitrary
115                      VALUELIST = 5,
116                      ASTERISK  = 6,
117                      FUNCTION  = 7,
118                      LIMIT     = 8,
119                      ROW       = 9;
120 
121 // boucherb@users 20020410 - parametric compiled statements
122     // new leaf type
123     static final int PARAM = 9;
124 
125 // --
126     // operations
127     static final int NEGATE   = 10,
128                      ADD      = 11,
129                      SUBTRACT = 12,
130                      MULTIPLY = 13,
131                      DIVIDE   = 14,
132                      CONCAT   = 15;
133 
134     // logical operations
135     static final int NOT           = 20,
136                      EQUAL         = 21,
137                      BIGGER_EQUAL  = 22,
138                      BIGGER        = 23,
139                      SMALLER       = 24,
140                      SMALLER_EQUAL = 25,
141                      NOT_EQUAL     = 26,
142                      LIKE          = 27,
143                      AND           = 28,
144                      OR            = 29,
145                      IN            = 30,
146                      EXISTS        = 31,
147                      ALL           = 32,
148                      ANY           = 33,
149                      IS_NULL       = 34;
150 
151     // aggregate functions
152     static final int COUNT       = 40,
153                      SUM         = 41,
154                      MIN         = 42,
155                      MAX         = 43,
156                      AVG         = 44,
157                      EVERY       = 45,
158                      SOME        = 46,
159                      STDDEV_POP  = 47,
160                      STDDEV_SAMP = 48,
161                      VAR_POP     = 49,
162                      VAR_SAMP    = 50;
163 
164     // system functions
165     static final int IFNULL      = 60,
166                      CONVERT     = 61,
167                      CASEWHEN    = 62,
168                      EXTRACT     = 63,
169                      POSITION    = 64,
170                      TRIM        = 65,
171                      SUBSTRING   = 66,
172                      NULLIF      = 67,
173                      CASE        = 68,
174                      COALESCE    = 69,
175                      ALTERNATIVE = 70,
176                      SEQUENCE    = 71;
177 
178     // temporary used during parsing
179     static final int PLUS     = 100,
180                      OPEN     = 101,
181                      CLOSE    = 102,
182                      SELECT   = 103,
183                      COMMA    = 104,
184                      BETWEEN  = 106,
185                      CAST     = 107,
186                      END      = 108,
187                      IS       = 109,
188                      WHEN     = 110,
189                      THEN     = 111,
190                      ELSE     = 112,
191                      ENDWHEN  = 113,
192                      DISTINCT = 114,
193                      VIEW     = 115;
194 
195     // used inside brackets for system functions
196     static final int     AS                      = 122,
197                          FOR                     = 123,
198                          FROM                    = 124,
199                          BOTH                    = 125,
200                          LEADING                 = 126,
201                          TRAILING                = 127,
202                          YEAR                    = 128,
203                          MONTH                   = 129,
204                          DAY                     = 130,
205                          HOUR                    = 131,
206                          MINUTE                  = 132,
207                          SECOND                  = 133,
208                          TIMEZONE_HOUR           = 134,
209                          T_TIMEZONE_MINUTE       = 135,
210                          DOW                     = 136;
211     static final HashSet SQL_EXTRACT_FIELD_NAMES = new HashSet();
212     static final HashSet SQL_TRIM_SPECIFICATION  = new HashSet();
213 
214     static {
215         SQL_EXTRACT_FIELD_NAMES.addAll(new Object  [] {
216             Token.T_YEAR, Token.T_MONTH, Token.T_DAY, Token.T_HOUR,
217             Token.T_MINUTE, Token.T_SECOND, Token.T_TIMEZONE_HOUR,
218             Token.T_TIMEZONE_MINUTE, Token.T_DOW
219         });
220         SQL_TRIM_SPECIFICATION.addAll(new Object  [] {
221             Token.T_LEADING, Token.T_TRAILING, Token.T_BOTH
222         });
223     }
224 
225     private static final int AGGREGATE_SELF     = -1;
226     private static final int AGGREGATE_NONE     = 0;
227     private static final int AGGREGATE_LEFT     = 1;
228     private static final int AGGREGATE_RIGHT    = 2;
229     private static final int AGGREGATE_BOTH     = 3;
230     private static final int AGGREGATE_FUNCTION = 4;
231 
232     // type
233     int         exprType;
234     private int aggregateSpec = AGGREGATE_NONE;
235 
236     // nodes
237     Expression eArg, eArg2;
238 
239     // VALUE
240     Object        valueData;
241     private int dataType;
242 
243     // VALUE LIST NEW
244     HashSet         hList;
245     Expression[]    valueList;
246     private boolean isFixedConstantValueList;
247 
248     // QUERY - in single value selects, IN or EXISTS predicates
249     SubQuery subQuery;
250     boolean  isQueryCorrelated;
251 
252     // FUNCTION
253     Function function;
254 
255     // LIKE
256     private Like likeObject;
257 
258     // COLUMN
259     private String        catalog;
260     private String        schema;
261     private String        tableName;
262     private String        columnName;
263     private TableFilter tableFilter;                // null if not yet resolved
264     TableFilter         outerFilter;                // defined if this is part of an OUTER JOIN condition tree
265 
266     // COLUMN
267     private int     columnIndex;
268     private boolean columnQuoted;
269     private int     precision;
270     private int     scale;
271     private String    columnAlias;                    // if it is a column of a select column list
272     private boolean aliasQuoted;
273 
274     //
275     private boolean isDescending;                   // if it is a column in a order by
276     int             joinedTableColumnIndex = -1;    // >= 0 when it is used for order by
277     boolean         isDistinctAggregate;
278 
279     // PARAM
280     private boolean isParam;
281 
282     // does Expression stem from a JOIN <table> ON <expression>
283     boolean isInJoin;
284 
285     //
286     static final Integer   INTEGER_0 = ValuePool.getInt(0);
287     static final Integer   INTEGER_1 = ValuePool.getInt(1);
288 
289     /**
290      * Creates a new boolean expression
291      * @param b boolean constant
292      */
293     Expression(boolean b) {
294         exprType = b ? TRUE
295                      : FALSE;
296     }
297 
298     /**
299      * Creates a new FUNCTION expression
300      * @param f function
301      */
302     Expression(Function f) {
303 
304         exprType = FUNCTION;
305         function = f;
306 
307         if (f.hasAggregate) {
308             aggregateSpec = AGGREGATE_FUNCTION;
309         }
310     }
311 
312     /**
313      * Creates a new SEQUENCE expression
314      * @param sequence number sequence
315      */
316     Expression(NumberSequence sequence) {
317 
318         exprType  = SEQUENCE;
319         valueData = sequence;
320         dataType  = sequence.getType();
321     }
322 
323     /**
324      * Copy Constructor. Used by TableFilter to move a condition to a filter.
325      * @param e source expression
326      */
327     Expression(Expression e) {
328 
329         exprType = e.exprType;
330         dataType = e.dataType;
331         eArg     = e.eArg;
332         eArg2    = e.eArg2;
333         isInJoin = e.isInJoin;
334 
335         //
336         likeObject = e.likeObject;
337         subQuery   = e.subQuery;
338         function   = e.function;
339 
340         checkAggregate();
341     }
342 
343     /**
344      * Creates a new QUERY expression
345      * @param sq subquery
346      */
347     Expression(SubQuery sq) {
348         exprType = QUERY;
349         subQuery = sq;
350     }
351 
352     /**
353      * Creates a new VALUELIST expression
354      * @param valueList array of Expression
355      */
356     Expression(Expression[] valueList) {
357         exprType       = VALUELIST;
358         this.valueList = valueList;
359     }
360 
361     /**
362      * Creates a new binary (or unary) operation expression
363      *
364      * @param type operator type
365      * @param e operand 1
366      * @param e2 operand 2
367      */
368     Expression(int type, Expression e, Expression e2) {
369 
370         exprType = type;
371         eArg     = e;
372         eArg2    = e2;
373 
374         checkAggregate();
375     }
376 
377     /**
378      * creates a CONVERT expression
379      */
380     Expression(Expression e, int dataType, int precision, int scale) {
381 
382         this.exprType    = CONVERT;
383         this.eArg        = e;
384         this.dataType    = dataType;
385         this.precision   = precision;
386         this.scale       = scale;
387         this.columnAlias = e.columnAlias;
388         this.aliasQuoted = e.aliasQuoted;
389 
390         checkAggregate();
391     }
392 
393     /**
394      * Creates a new LIKE expression
395      *
396      * @param e operand 1
397      * @param e2 operand 2
398      * @param escape escape character
399      */
400     Expression(Expression e, Expression e2, Character   escape,
401                boolean hasCollation) {
402 
403         exprType   = LIKE;
404         eArg       = e;
405         eArg2      = e2;
406         likeObject = new Like(escape, hasCollation);
407 
408         checkAggregate();
409     }
410 
411     /**
412      * Creates a new ASTERISK or COLUMN expression
413      * @param table table
414      * @param column column
415      */
416     Expression(String   schema, String   table, String   column) {
417 
418         this.schema = schema;
419         tableName   = table;
420 
421         if (column == null) {
422             exprType = ASTERISK;
423         } else {
424             exprType   = COLUMN;
425             columnName = column;
426         }
427     }
428 
429     /**
430      * Creates a new ASTERIX or possibly quoted COLUMN expression
431      * @param table table
432      * @param column column name
433      * @param isquoted boolean
434      */
435     Expression(String   table, String   column, boolean isquoted) {
436 
437         tableName = table;
438 
439         if (column == null) {
440             exprType = ASTERISK;
441         } else {
442             exprType     = COLUMN;
443             columnName   = column;
444             columnQuoted = isquoted;
445         }
446     }
447 
448     Expression(TableFilter filter, Column column) {
449 
450         schema    = filter.filterTable.tableName.schema.name;
451         tableName = filter.getName();
452 
453         if (column == null) {
454             exprType = ASTERISK;
455         } else {
456             exprType     = COLUMN;
457             columnName   = column.columnName.name;
458             columnQuoted = column.columnName.isNameQuoted;
459             dataType     = column.getType();
460         }
461     }
462 
463     /**
464      * Creates a new VALUE expression
465      *
466      * @param datatype data type
467      * @param o data
468      */
469     Expression(int datatype, Object   o) {
470 
471         exprType  = VALUE;
472         dataType  = datatype;
473         valueData = o;
474     }
475 
476     /**
477      * Creates a new (possibly PARAM) VALUE expression
478      *
479      * @param datatype initial datatype
480      * @param o initial value
481      * @param isParam true if this is to be a PARAM VALUE expression
482      */
483     Expression(int datatype, Object   o, boolean isParam) {
484 
485         this(datatype, o);
486 
487         this.isParam = isParam;
488 
489         if (isParam) {
490             paramMode = PARAM_IN;
491         }
492     }
493 
494     boolean isTypeEqual(Expression other) {
495         return dataType == other.dataType && precision == other.precision
496                && scale == other.scale;
497     }
498 
499     private void checkAggregate() {
500 
501         if (isAggregate(exprType)) {
502             aggregateSpec = AGGREGATE_SELF;
503         } else {
504             aggregateSpec = AGGREGATE_NONE;
505 
506             if ((eArg != null) && eArg.isAggregate()) {
507                 aggregateSpec += AGGREGATE_LEFT;
508             }
509 
510             if ((eArg2 != null) && eArg2.isAggregate()) {
511                 aggregateSpec += AGGREGATE_RIGHT;
512             }
513         }
514     }
515 
516     public String   describe(Session session) {
517         return describe(session, 0);
518     }
519 
520     static String   getContextDDL(Expression expression) throws HsqlException {
521 
522         String   ddl = expression.getDDL();
523 
524         if (expression.exprType != VALUE && expression.exprType != COLUMN
525                 && expression.exprType != FUNCTION
526                 && expression.exprType != ALTERNATIVE
527                 && expression.exprType != CASEWHEN
528                 && expression.exprType != CONVERT) {
529             StringBuffer   temp = new StringBuffer  ();
530 
531             ddl = temp.append('(').append(ddl).append(')').toString();
532         }
533 
534         return ddl;
535     }
536 
537     /**
538      * For use with CHECK constraints. Under development.
539      *
540      * Currently supports a subset of expressions and is suitable for CHECK
541      * search conditions that refer only to the inserted/updated row.
542      *
543      * For full DDL reporting of VIEW select queries and CHECK search
544      * conditions, future improvements here are dependent upon improvements to
545      * SELECT query parsing, so that it is performed in a number of passes.
546      * An early pass should result in the query turned into an Expression tree
547      * that contains the information in the original SQL without any
548      * alterations, and with tables and columns all resolved. This Expression
549      * can then be preserved for future use. Table and column names that
550      * are not user-defined aliases should be kept as the HsqlName structures
551      * so that table or column renaming is reflected in the precompiled
552      * query.
553      *
554      * @return DDL
555      * @throws HsqlException
556      */
557     String   getDDL() throws HsqlException {
558 
559         StringBuffer   buf   = new StringBuffer  (64);
560         String         left  = null;
561         String         right = null;
562 
563         if (eArg != null) {
564             left = Expression.getContextDDL(eArg);
565         }
566 
567         if (eArg2 != null) {
568             right = Expression.getContextDDL(eArg2);
569         }
570 
571         switch (exprType) {
572 
573             case FUNCTION :
574                 return function.getDLL();
575 
576             case VALUE :
577                 try {
578                     return isParam ? Token.T_QUESTION
579                                    : Column.createSQLString(valueData,
580                                    dataType);
581                 } catch (HsqlException e) {}
582 
583                 return buf.toString();
584 
585             case COLUMN :
586 
587                 // this is a limited solution
588                 Table table = tableFilter.getTable();
589 
590                 if (tableName != null) {
591                     buf.append(table.tableName.statementName);
592                     buf.append('.');
593                 }
594 
595                 buf.append(
596                     table.getColumn(columnIndex).columnName.statementName);
597 
598                 return buf.toString();
599 
600             case TRUE :
601                 return Token.T_TRUE;
602 
603             case FALSE :
604                 return Token.T_FALSE;
605 
606             case VALUELIST :
607                 for (int i = 0; i < valueList.length; i++) {
608                     buf.append(valueList[i].getDDL());
609 
610                     if (i < valueList.length - 1) {
611                         buf.append(',');
612                     }
613                 }
614 
615                 return buf.toString();
616 
617             case ASTERISK :
618                 buf.append('*');
619 
620                 return buf.toString();
621 
622             case NEGATE :
623                 buf.append('-').append(left);
624 
625                 return buf.toString();
626 
627             case ADD :
628                 buf.append(left).append('+').append(right);
629 
630                 return buf.toString();
631 
632             case SUBTRACT :
633                 buf.append(left).append('-').append(right);
634 
635                 return buf.toString();
636 
637             case MULTIPLY :
638                 buf.append(left).append('*').append(right);
639 
640                 return buf.toString();
641 
642             case DIVIDE :
643                 buf.append(left).append('/').append(right);
644 
645                 return buf.toString();
646 
647             case CONCAT :
648                 buf.append(left).append("||").append(right);
649 
650                 return buf.toString();
651 
652             case NOT :
653                 if (eArg.exprType == IS_NULL) {
654                     buf.append(getContextDDL(eArg.eArg)).append(' ').append(
655                         Token.T_IS).append(' ').append(Token.T_NOT).append(
656                         ' ').append(Token.T_NULL);
657 
658                     return buf.toString();
659                 }
660 
661                 buf.append(Token.T_NOT).append(' ').append(left);
662 
663                 return buf.toString();
664 
665             case EQUAL :
666                 buf.append(left).append('=').append(right);
667 
668                 return buf.toString();
669 
670             case BIGGER_EQUAL :
671                 buf.append(left).append(">=").append(right);
672 
673                 return buf.toString();
674 
675             case BIGGER :
676                 buf.append(left).append('>').append(right);
677 
678                 return buf.toString();
679 
680             case SMALLER :
681                 buf.append(left).append('<').append(right);
682 
683                 return buf.toString();
684 
685             case SMALLER_EQUAL :
686                 buf.append(left).append("<=").append(right);
687 
688                 return buf.toString();
689 
690             case NOT_EQUAL :
691                 if (Token.T_NULL.equals(right)) {
692                     buf.append(left).append(" IS NOT ").append(right);
693                 } else {
694                     buf.append(left).append("!=").append(right);
695                 }
696 
697                 return buf.toString();
698 
699             case LIKE :
700                 buf.append(left).append(' ').append(Token.T_LIKE).append(' ');
701                 buf.append(right);
702 
703                 /** @todo fredt - scripting of non-ascii escapes needs changes to general script logging */
704                 if (likeObject.escapeChar != null) {
705                     buf.append(' ').append(Token.T_ESCAPE).append(' ').append(
706                         '\'');
707                     buf.append(likeObject.escapeChar.toString()).append('\'');
708                     buf.append(' ');
709                 }
710 
711                 return buf.toString();
712 
713             case AND :
714                 buf.append(left).append(' ').append(Token.T_AND).append(
715                     ' ').append(right);
716 
717                 return buf.toString();
718 
719             case OR :
720                 buf.append(left).append(' ').append(Token.T_OR).append(
721                     ' ').append(right);
722 
723                 return buf.toString();
724 
725             case ALL :
726                 buf.append(left).append(' ').append(Token.T_ALL).append(
727                     ' ').append(right);
728 
729                 return buf.toString();
730 
731             case ANY :
732                 buf.append(left).append(' ').append(Token.T_ANY).append(
733                     ' ').append(right);
734 
735                 return buf.toString();
736 
737             case IN :
738                 buf.append(left).append(' ').append(Token.T_IN).append(
739                     ' ').append(right);
740 
741                 return buf.toString();
742 
743             case CONVERT :
744                 buf.append(' ').append(Token.T_CONVERT).append('(');
745                 buf.append(left).append(',');
746                 buf.append(Types.getTypeString(dataType, precision, scale));
747                 buf.append(')');
748 
749                 return buf.toString();
750 
751             case CASEWHEN :
752                 buf.append(' ').append(Token.T_CASEWHEN).append('(');
753                 buf.append(left).append(',').append(right).append(')');
754 
755                 return buf.toString();
756 
757             case IS_NULL :
758                 buf.append(left).append(' ').append(Token.T_IS).append(
759                     ' ').append(Token.T_NULL);
760 
761                 return buf.toString();
762 
763             case ALTERNATIVE :
764                 buf.append(left).append(',').append(right);
765 
766                 return buf.toString();
767 
768             case QUERY :
769 /*
770                 buf.append('(');
771                 buf.append(subSelect.getDDL());
772                 buf.append(')');
773 */
774                 break;
775 
776             case EXISTS :
777                 buf.append(' ').append(Token.T_EXISTS).append(' ');
778                 break;
779 
780             case COUNT :
781                 buf.append(' ').append(Token.T_COUNT).append('(');
782                 break;
783 
784             case SUM :
785                 buf.append(' ').append(Token.T_SUM).append('(');
786                 buf.append(left).append(')');
787                 break;
788 
789             case MIN :
790                 buf.append(' ').append(Token.T_MIN).append('(');
791                 buf.append(left).append(')');
792                 break;
793 
794             case MAX :
795                 buf.append(' ').append(Token.T_MAX).append('(');
796                 buf.append(left).append(')');
797                 break;
798 
799             case AVG :
800                 buf.append(' ').append(Token.T_AVG).append('(');
801                 buf.append(left).append(')');
802                 break;
803 
804             case EVERY :
805                 buf.append(' ').append(Token.T_EVERY).append('(');
806                 buf.append(left).append(')');
807                 break;
808 
809             case SOME :
810                 buf.append(' ').append(Token.T_SOME).append('(');
811                 buf.append(left).append(')');
812                 break;
813 
814             case STDDEV_POP :
815                 buf.append(' ').append(Token.T_STDDEV_POP).append('(');
816                 buf.append(left).append(')');
817                 break;
818 
819             case STDDEV_SAMP :
820                 buf.append(' ').append(Token.T_STDDEV_SAMP).append('(');
821                 buf.append(left).append(')');
822                 break;
823 
824             case VAR_POP :
825                 buf.append(' ').append(Token.T_VAR_POP).append('(');
826                 buf.append(left).append(')');
827                 break;
828 
829             case VAR_SAMP :
830                 buf.append(' ').append(Token.T_VAR_SAMP).append('(');
831                 buf.append(left).append(')');
832                 break;
833         }
834 
835         throw Trace.error(Trace.EXPRESSION_NOT_SUPPORTED);
836     }
837 
838     private String   describe(Session session, int blanks) {
839 
840         int          lIType;
841         StringBuffer   buf = new StringBuffer  (64);
842 
843         buf.append('\n');
844 
845         for (int i = 0; i < blanks; i++) {
846             buf.append(' ');
847         }
848 
849         if (oldIType != -1) {
850             buf.append("SET TRUE, WAS: ");
851         }
852 
853         lIType = oldIType == -1 ? exprType
854                                 : oldIType;
855 
856         switch (lIType) {
857 
858             case FUNCTION :
859                 buf.append("FUNCTION ");
860                 buf.append(function.describe(session));
861 
862                 return buf.toString();
863 
864             case VALUE :
865                 if (isParam) {
866                     buf.append("PARAM ");
867                 }
868 
869                 buf.append("VALUE = ").append(valueData);
870                 buf.append(", TYPE = ").append(Types.getTypeString(dataType));
871 
872                 return buf.toString();
873 
874             case COLUMN :
875                 buf.append("COLUMN ");
876 
877                 if (tableName != null) {
878                     buf.append(tableName);
879                     buf.append('.');
880                 }
881 
882                 buf.append(columnName);
883 
884                 return buf.toString();
885 
886             case QUERY :
887                 buf.append("QUERY ");
888                 buf.append(subQuery.select.describe(session));
889 
890                 return buf.toString();
891 
892             case TRUE :
893                 buf.append("TRUE ");
894                 break;
895 
896             case FALSE :
897                 buf.append("FALSE ");
898                 break;
899 
900             case VALUELIST :
901                 buf.append("VALUELIST ");
902                 buf.append(" TYPE = ").append(Types.getTypeString(dataType));
903 
904                 if (valueList != null) {
905                     for (int i = 0; i < valueList.length; i++) {
906                         buf.append(valueList[i].describe(session,
907                                                          blanks + blanks));
908                         buf.append(' ');
909                     }
910                 }
911                 break;
912 
913             case ASTERISK :
914                 buf.append("* ");
915                 break;
916 
917             case NEGATE :
918                 buf.append("NEGATE ");
919                 break;
920 
921             case ADD :
922                 buf.append("ADD ");
923                 break;
924 
925             case SUBTRACT :
926                 buf.append("SUBTRACT ");
927                 break;
928 
929             case MULTIPLY :
930                 buf.append("MULTIPLY ");
931                 break;
932 
933             case DIVIDE :
934                 buf.append("DIVIDE ");
935                 break;
936 
937             case CONCAT :
938                 buf.append("CONCAT ");
939                 break;
940 
941             case NOT :
942                 buf.append("NOT ");
943                 break;
944 
945             case EQUAL :
946                 buf.append("EQUAL ");
947                 break;
948 
949             case BIGGER_EQUAL :
950                 buf.append("BIGGER_EQUAL ");
951                 break;
952 
953             case BIGGER :
954                 buf.append("BIGGER ");
955                 break;
956 
957             case SMALLER :
958                 buf.append("SMALLER ");
959                 break;
960 
961             case SMALLER_EQUAL :
962                 buf.append("SMALLER_EQUAL ");
963                 break;
964 
965             case NOT_EQUAL :
966                 buf.append("NOT_EQUAL ");
967                 break;
968 
969             case LIKE :
970                 buf.append("LIKE ");
971                 buf.append(likeObject.describe(session));
972                 break;
973 
974             case AND :
975                 buf.append("AND ");
976                 break;
977 
978             case OR :
979                 buf.append("OR ");
980                 break;
981 
982             case ALL :
983                 buf.append("ALL ");
984                 break;
985 
986             case ANY :
987                 buf.append("ANY ");
988                 break;
989 
990             case IN :
991                 buf.append("IN ");
992                 break;
993 
994             case IS_NULL :
995                 buf.append("IS_NULL ");
996                 break;
997 
998             case EXISTS :
999                 buf.append("EXISTS ");
1000                break;
1001
1002            case COUNT :
1003                buf.append("COUNT ");
1004                break;
1005
1006            case SUM :
1007                buf.append("SUM ");
1008                break;
1009
1010            case MIN :
1011                buf.append("MIN ");
1012                break;
1013
1014            case MAX :
1015                buf.append("MAX ");
1016                break;
1017
1018            case AVG :
1019                buf.append("AVG ");
1020                break;
1021
1022            case EVERY :
1023                buf.append(Token.T_EVERY).append(' ');
1024                break;
1025
1026            case SOME :
1027                buf.append(Token.T_SOME).append(' ');
1028                break;
1029
1030            case STDDEV_POP :
1031                buf.append(Token.T_STDDEV_POP).append(' ');
1032                break;
1033
1034            case STDDEV_SAMP :
1035                buf.append(Token.T_STDDEV_SAMP).append(' ');
1036                break;
1037
1038            case VAR_POP :
1039                buf.append(Token.T_VAR_POP).append(' ');
1040                break;
1041
1042            case VAR_SAMP :
1043                buf.append(Token.T_VAR_SAMP).append(' ');
1044                break;
1045
1046            case CONVERT :
1047                buf.append("CONVERT ");
1048                buf.append(Types.getTypeString(dataType, precision, scale));
1049                buf.append(' ');
1050                break;
1051
1052            case CASEWHEN :
1053                buf.append("CASEWHEN ");
1054                break;
1055        }
1056
1057        if (isInJoin) {
1058            buf.append(" join");
1059        }
1060
1061        if (eArg != null) {
1062            buf.append(" arg1=[");
1063            buf.append(eArg.describe(session, blanks + 1));
1064            buf.append(']');
1065        }
1066
1067        if (eArg2 != null) {
1068            buf.append(" arg2=[");
1069            buf.append(eArg2.describe(session, blanks + 1));
1070            buf.append(']');
1071        }
1072
1073        return buf.toString();
1074    }
1075
1076    /**
1077     * Set the data type
1078     *
1079     *
1080     * @param type data type
1081     */
1082    void setDataType(int type) {
1083        dataType = type;
1084    }
1085
1086    int oldIType = -1;
1087
1088    /**
1089     * When an Expression is assigned to a TableFilter, a copy is made for use
1090     * there and the original is set to Expression.TRUE
1091     *
1092     */
1093    void setTrue() {
1094
1095        if (oldIType == -1) {
1096            oldIType = exprType;
1097        }
1098
1099        exprType = TRUE;
1100    }
1101
1102    void setNull() {
1103
1104        isParam   = false;
1105        exprType  = VALUE;
1106        dataType  = Types.NULL;
1107        valueData = null;
1108        eArg      = null;
1109        eArg2     = null;
1110    }
1111
1112    /**
1113     * Check if the given expression defines similar operation as this
1114     * expression. This method is used for ensuring an expression in
1115     * the ORDER BY clause has a matching column in the SELECT list. This check
1116     * is necessary with a SELECT DISTINCT query.<br>
1117     *
1118     * In the future we may perform the test when evaluating the search
1119     * condition to get a more accurate match.
1120     *
1121     * @param exp expression
1122     * @return boolean
1123     */
1124    public boolean similarTo(Expression exp) {
1125
1126        if (exp == null) {
1127            return false;
1128        }
1129
1130        if (exp == this) {
1131            return true;
1132        }
1133
1134        /** @todo fredt - equals() method for valueList, subSelect and function are needed */
1135        return exprType == exp.exprType && dataType == exp.dataType
1136               && equals(valueData, exp.valueData)
1137               && equals(valueList, exp.valueList)
1138               && equals(subQuery, exp.subQuery)
1139               && equals(function, exp.function)
1140               && equals(tableName, exp.tableName)
1141               && equals(columnName, exp.columnName)
1142               && similarTo(eArg, exp.eArg) && similarTo(eArg2, exp.eArg2);
1143    }
1144
1145    static boolean equals(Object   o1, Object   o2) {
1146        return (o1 == null) ? o2 == null
1147                            : o1.equals(o2);
1148    }
1149
1150    static boolean equals(Expression[] ae1, Expression[] ae2) {
1151
1152        if (ae1 == ae2) {
1153            return true;
1154        }
1155
1156        if (ae1.length != ae2.length) {
1157            return false;
1158        }
1159
1160        int     len    = ae1.length;
1161        boolean equals = true;
1162
1163        for (int i = 0; i < len; i++) {
1164            Expression e1 = ae1[i];
1165            Expression e2 = ae2[i];
1166
1167            equals = (e1 == null) ? e2 == null
1168                                  : e1.equals(e2);
1169        }
1170
1171        return equals;
1172    }
1173
1174    static boolean similarTo(Expression e1, Expression e2) {
1175        return (e1 == null) ? e2 == null
1176                            : e1.similarTo(e2);
1177    }
1178
1179/** @todo fredt - workaround for functions in ORDER BY and GROUP BY needs
1180 *  checking the argument of the function to ensure they are valid. */
1181
1182    /**
1183     * Check if this expression can be included in a group by clause.
1184     * <p>
1185     * It can, if itself is a column expression, and it is not an aggregate
1186     * expression.
1187     *
1188     * @return boolean
1189     */
1190    boolean canBeInGroupBy() {
1191
1192        if (exprType == FUNCTION) {
1193            return true;
1194        }
1195
1196        return isColumn() && (!(isAggregate()));
1197    }
1198
1199    /**
1200     * Check if this expression can be included in an order by clause.
1201     * <p>
1202     * It can, if itself is a column expression.
1203     *
1204     * @return boolean
1205     */
1206    boolean canBeInOrderBy() {
1207        return exprType == FUNCTION || joinedTableColumnIndex != -1
1208               || isColumn() || isAggregate();
1209    }
1210
1211    /**
1212     * Check if this expression defines at least one column.
1213     * <p>
1214     * It is, if itself is a column expression, or any the argument
1215     * expressions is a column expression.
1216     *
1217     * @return boolean
1218     */
1219    private boolean isColumn() {
1220
1221        switch (exprType) {
1222
1223            case COLUMN :
1224                return true;
1225
1226            case NEGATE :
1227                return eArg.isColumn();
1228
1229            case ADD :
1230            case SUBTRACT :
1231            case MULTIPLY :
1232            case DIVIDE :
1233            case CONCAT :
1234                return eArg.isColumn() || eArg2.isColumn();
1235        }
1236
1237        return false;
1238    }
1239
1240    /**
1241     * Collect column name used in this expression.
1242     *
1243     * @param columnNames set to be filled
1244     * @return true if a column name is used in this expression
1245     */
1246    boolean collectColumnName(HashSet columnNames) {
1247
1248        boolean result = exprType == COLUMN;
1249
1250        if (result) {
1251            columnNames.add(columnName);
1252        }
1253
1254        return result;
1255    }
1256
1257    /**
1258     * Collect all column names used in this expression or any of nested
1259     * expression.
1260     *
1261     * @param columnNames set to be filled
1262     */
1263    void collectAllColumnNames(HashSet columnNames) {
1264
1265        if (!collectColumnName(columnNames)) {
1266            if (eArg != null) {
1267                eArg.collectAllColumnNames(columnNames);
1268            }
1269
1270            if (eArg2 != null) {
1271                eArg2.collectAllColumnNames(columnNames);
1272            }
1273        }
1274    }
1275
1276    /**
1277     * Check if this expression defines a constant value.
1278     * <p>
1279     * It does, if it is a constant value expression, or all the argument
1280     * expressions define constant values.
1281     *
1282     * @return boolean
1283     */
1284    boolean isConstant() {
1285
1286        switch (exprType) {
1287
1288            case VALUE :
1289                return true;
1290
1291            case NEGATE :
1292                return eArg.isConstant();
1293
1294            case ADD :
1295            case SUBTRACT :
1296            case MULTIPLY :
1297            case DIVIDE :
1298            case CONCAT :
1299                return eArg.isConstant() && eArg2.isConstant();
1300        }
1301
1302        return false;
1303    }
1304
1305    /**
1306     * Check if this expression can be included as a result column in an
1307     * aggregated select statement.
1308     * <p>
1309     * It can, if itself is an aggregate expression, or it results a constant
1310     * value.
1311     *
1312     * @return boolean
1313     */
1314    boolean canBeInAggregate() {
1315        return isAggregate() || isConstant();
1316    }
1317
1318    /**
1319     *  Is this (indirectly) an aggregate expression
1320     *
1321     *  @return boolean
1322     */
1323    boolean isAggregate() {
1324        return aggregateSpec != AGGREGATE_NONE;
1325    }
1326
1327    /**
1328     *  Is this directly an aggregate expression
1329     *
1330     *
1331     *  @return boolean
1332     */
1333    boolean isSelfAggregate() {
1334        return aggregateSpec == AGGREGATE_SELF;
1335    }
1336
1337    static boolean isAggregate(int type) {
1338
1339        switch (type) {
1340
1341            case COUNT :
1342            case MAX :
1343            case MIN :
1344            case SUM :
1345            case AVG :
1346            case EVERY :
1347            case SOME :
1348            case STDDEV_POP :
1349            case STDDEV_SAMP :
1350            case VAR_POP :
1351            case VAR_SAMP :
1352                return true;
1353        }
1354
1355        return false;
1356    }
1357
1358// tony_lai@users having
1359
1360    /**
1361     *  Checks for conditional expression.
1362     *
1363     *
1364     *  @return boolean
1365     */
1366    boolean isConditional() {
1367
1368        switch (exprType) {
1369
1370            case TRUE :
1371            case FALSE :
1372            case EQUAL :
1373            case BIGGER_EQUAL :
1374            case BIGGER :
1375            case SMALLER :
1376            case SMALLER_EQUAL :
1377            case NOT_EQUAL :
1378            case LIKE :
1379            case IN :
1380            case EXISTS :
1381            case IS_NULL :
1382                return true;
1383
1384            case NOT :
1385                return eArg.isConditional();
1386
1387            case AND :
1388            case OR :
1389                return eArg.isConditional() && eArg2.isConditional();
1390
1391            default :
1392                return false;
1393        }
1394    }
1395
1396    /**
1397     * Collects all expressions that must be in the GROUP BY clause, for a
1398     * grouped select statement.
1399     *
1400     * @param colExps expression list
1401     */
1402    void collectInGroupByExpressions(HsqlArrayList colExps) {
1403
1404        if (!(isConstant() || isSelfAggregate())) {
1405            if (isColumn()) {
1406                colExps.add(this);
1407            } else if (exprType == FUNCTION) {
1408
1409//                function.collectInGroupByExpressions(colExps);
1410            } else if (exprType == CASEWHEN) {
1411                eArg2.collectInGroupByExpressions(colExps);
1412            } else {
1413                if (eArg != null) {
1414                    eArg.collectInGroupByExpressions(colExps);
1415                }
1416
1417                if (eArg2 != null) {
1418                    eArg2.collectInGroupByExpressions(colExps);
1419                }
1420            }
1421        }
1422    }
1423
1424    /**
1425     * Set an ORDER BY column expression DESC
1426     *
1427     */
1428    void setDescending() {
1429        isDescending = true;
1430    }
1431
1432    /**
1433     * Is an ORDER BY column expression DESC
1434     *
1435     *
1436     * @return boolean
1437     */
1438    boolean isDescending() {
1439        return isDescending;
1440    }
1441
1442    /**
1443     * Set the column alias and whether the name is quoted
1444     *
1445     * @param s alias
1446     * @param isquoted boolean
1447     */
1448    void setAlias(String   s, boolean isquoted) {
1449        columnAlias = s;
1450        aliasQuoted = isquoted;
1451    }
1452
1453    /**
1454     * Change the column name
1455     *
1456     * @param newname name
1457     * @param isquoted quoted
1458     */
1459    void setColumnName(String   newname, boolean isquoted) {
1460        columnName   = newname;
1461        columnQuoted = isquoted;
1462    }
1463
1464    /**
1465     * Change the table name
1466     *
1467     * @param newname table name for column expression
1468     */
1469    void setTableName(String   newname) {
1470        tableName = newname;
1471    }
1472
1473    /**
1474     * Return the user defined alias or null if none
1475     *
1476     * @return alias
1477     */
1478    String   getDefinedAlias() {
1479        return columnAlias;
1480    }
1481
1482    /**
1483     * Get the column alias
1484     *
1485     *
1486     * @return alias
1487     */
1488    String   getAlias() {
1489
1490        if (columnAlias != null) {
1491            return columnAlias;
1492        }
1493
1494        if (exprType == COLUMN) {
1495            return columnName;
1496        }
1497
1498        return "";
1499    }
1500
1501    /**
1502     * Is a column alias quoted
1503     *
1504     * @return boolean
1505     */
1506    boolean isAliasQuoted() {
1507
1508        if (columnAlias != null) {
1509            return aliasQuoted;
1510        }
1511
1512        if (exprType == COLUMN) {
1513            return columnQuoted;
1514        }
1515
1516        return false;
1517    }
1518
1519    /**
1520     * Returns the type of expression
1521     *
1522     *
1523     * @return type
1524     */
1525    int getType() {
1526        return exprType;
1527    }
1528
1529    /**
1530     * Returns the left node
1531     *
1532     *
1533     * @return argument
1534     */
1535    Expression getArg() {
1536        return eArg;
1537    }
1538
1539    /**
1540     * Returns the right node
1541     *
1542     *
1543     * @return argument
1544     */
1545    Expression getArg2() {
1546        return eArg2;
1547    }
1548
1549    /**
1550     * Returns the table filter for a COLUMN expression
1551     *
1552     * @return table filter
1553     */
1554    TableFilter getFilter() {
1555        return tableFilter;
1556    }
1557
1558    /**
1559     * Final check for all expressions.
1560     *
1561     * @param check boolean
1562     * @return boolean
1563     * @throws HsqlException
1564     */
1565    boolean checkResolved(boolean check) throws HsqlException {
1566
1567        boolean result = true;
1568
1569        if (eArg != null) {
1570            result = result && eArg.checkResolved(check);
1571        }
1572
1573        if (eArg2 != null) {
1574            result = result && eArg2.checkResolved(check);
1575        }
1576
1577        if (subQuery != null && subQuery.select != null) {
1578            result = result && subQuery.select.checkResolved(check);
1579        }
1580
1581        if (function != null) {
1582            result = result && function.checkResolved(check);
1583        }
1584
1585        if (valueList != null) {
1586            for (int i = 0; i < valueList.length; i++) {
1587                result = result && valueList[i].checkResolved(check);
1588            }
1589        }
1590
1591        if (exprType == COLUMN) {
1592            if (tableFilter == null) {
1593
1594                // if an order by column alias
1595                result = joinedTableColumnIndex != -1;
1596
1597                if (!result && check) {
1598                    String   err = tableName == null ? columnName
1599                                                   : tableName + "."
1600                                                     + columnName;
1601
1602                    throw Trace.error(Trace.COLUMN_NOT_FOUND, err);
1603                }
1604            } else {
1605                tableFilter.usedColumns[this.columnIndex] = true;
1606            }
1607        }
1608
1609        return result;
1610    }
1611
1612    /**
1613     * Resolve the table names for columns and throws if a column remains
1614     * unresolved.
1615     *
1616     * @param filters list of filters
1617     *
1618     * @throws HsqlException
1619     */
1620    void checkTables(HsqlArrayList filters) throws HsqlException {
1621
1622        if (filters == null || exprType == Expression.VALUE) {
1623            return;
1624        }
1625
1626        if (eArg != null) {
1627            eArg.checkTables(filters);
1628        }
1629
1630        if (eArg2 != null) {
1631            eArg2.checkTables(filters);
1632        }
1633
1634        switch (exprType) {
1635
1636            case COLUMN :
1637                boolean found = false;
1638                int     len   = filters.size();
1639
1640                for (int j = 0; j < len; j++) {
1641                    TableFilter filter     = (TableFilter) filters.get(j);
1642                    String        filterName = filter.getName();
1643
1644                    if (tableName == null || filterName.equals(tableName)) {
1645                        Table table = filter.getTable();
1646                        int   i     = table.findColumn(columnName);
1647
1648                        if (i != -1) {
1649                            if (tableName == null) {
1650                                if (found) {
1651                                    throw Trace.error(
1652                                        Trace.AMBIGUOUS_COLUMN_REFERENCE,
1653                                        columnName);
1654                                }
1655
1656                                //
1657                                found = true;
1658                            } else {
1659                                return;
1660                            }
1661                        }
1662                    }
1663                }
1664
1665                if (found) {
1666                    return;
1667                }
1668
1669                throw Trace.error(Trace.COLUMN_NOT_FOUND, columnName);
1670            case QUERY :
1671
1672                // fredt - subquery in join condition !
1673                break;
1674
1675            case FUNCTION :
1676                if (function != null) {
1677                    function.checkTables(filters);
1678                }
1679                break;
1680
1681            case ALL :
1682            case ANY :
1683                break;
1684
1685            case IN :
1686                if (eArg2.exprType != QUERY) {
1687                    Expression[] vl = eArg2.valueList;
1688
1689                    for (int i = 0; i < vl.length; i++) {
1690                        vl[i].checkTables(filters);
1691                    }
1692                }
1693                break;
1694
1695            default :
1696        }
1697    }
1698
1699    /**
1700     * return the expression for an aliases
1701     */
1702    Expression getExpressionForAlias(Expression[] columns, int length) {
1703
1704        for (int i = 0; i < length; i++) {
1705            if (columnName.equals(columns[i].columnAlias)
1706                    && (tableName == null
1707                        || tableName.equals(columns[i].tableName))) {
1708                return columns[i];
1709            }
1710        }
1711
1712        return this;
1713    }
1714
1715    /**
1716     * Replace aliases with expression trees
1717     */
1718    void replaceAliases(Expression[] columns,
1719                        int length) throws HsqlException {
1720
1721        if (eArg != null) {
1722            if (eArg.exprType == Expression.COLUMN) {
1723                eArg = eArg.getExpressionForAlias(columns, length);
1724            } else {
1725                eArg.replaceAliases(columns, length);
1726            }
1727        }
1728
1729        if (eArg2 != null) {
1730            if (eArg2.exprType == Expression.COLUMN) {
1731                eArg2 = eArg2.getExpressionForAlias(columns, length);
1732            } else {
1733                eArg2.replaceAliases(columns, length);
1734            }
1735        }
1736
1737        switch (exprType) {
1738
1739            case QUERY :
1740                break;
1741
1742            case FUNCTION :
1743                if (function != null) {
1744                    function.replaceAliases(columns, length);
1745                }
1746                break;
1747
1748            case ALL :
1749            case ANY :
1750                break;
1751
1752            case IN :
1753                if (eArg2.exprType != QUERY) {
1754                    Expression[] vl = eArg2.valueList;
1755
1756                    for (int i = 0; i < vl.length; i++) {
1757                        if (vl[i].exprType == Expression.COLUMN) {
1758                            vl[i] = vl[i].getExpressionForAlias(columns,
1759                                                                length);
1760                        } else {
1761                            vl[i].replaceAliases(columns, length);
1762                        }
1763                    }
1764                }
1765                break;
1766
1767            default :
1768        }
1769    }
1770
1771    /**
1772     * Workaround for CHECK constraints. We don't want optimisation so we
1773     * flag all LIKE expressions as already optimised.
1774     *
1775     * @throws HsqlException
1776     */
1777    void setLikeOptimised() throws HsqlException {
1778
1779        if (eArg != null) {
1780            eArg.setLikeOptimised();
1781        }
1782
1783        if (eArg2 != null) {
1784            eArg2.setLikeOptimised();
1785        }
1786
1787        if (exprType == LIKE) {
1788            likeObject.optimised = true;
1789        }
1790    }
1791
1792    /**
1793     * Removes table filter resolution from an Expression tree.
1794     */
1795/*
1796    void removeFilters() throws HsqlException {
1797
1798        if (eArg != null) {
1799            eArg.removeFilters();
1800        }
1801
1802        if (eArg2 != null) {
1803            eArg2.removeFilters();
1804        }
1805
1806        switch (exprType) {
1807
1808            case COLUMN :
1809                tableFilter = null;
1810
1811                return;
1812
1813            case QUERY :
1814                if (subSelect != null) {
1815                    subSelect.removeFilters();
1816                }
1817                break;
1818
1819            case FUNCTION :
1820                if (function != null) {
1821                    function.removeFilters();
1822                }
1823                break;
1824
1825            case IN :
1826                if (eArg2.exprType != QUERY) {
1827                    Expression[] vl = eArg2.valueList;
1828
1829                    for (int i = 0; i < vl.length; i++) {
1830                        vl[i].removeFilters();
1831                    }
1832                }
1833                break;
1834
1835            default :
1836        }
1837    }
1838*/
1839
1840    /**
1841     * set boolean flags and expressions for columns in a join
1842     *
1843     * @param filter target table filter
1844     * @param columns boolean array
1845     * @param elist expression list
1846     */
1847    void getEquiJoinColumns(TableFilter filter, boolean[] columns,
1848                            Expression[] elist) {
1849
1850        if (eArg != null) {
1851            eArg.getEquiJoinColumns(filter, columns, elist);
1852        }
1853
1854        if (eArg2 != null) {
1855            eArg2.getEquiJoinColumns(filter, columns, elist);
1856        }
1857
1858        if (exprType == EQUAL) {
1859            if (eArg.tableFilter == eArg2.tableFilter) {
1860                return;
1861            }
1862
1863            // an elist element may be set more than once - OK
1864            if (eArg.tableFilter == filter) {
1865                if (eArg2.exprType == COLUMN || eArg2.exprType == VALUE) {
1866                    columns[eArg.columnIndex] = true;
1867                    elist[eArg.columnIndex]   = eArg2;
1868                }
1869
1870                return;
1871            }
1872
1873            if (eArg2.tableFilter == filter) {
1874                if (eArg.exprType == COLUMN || eArg.exprType == VALUE) {
1875                    columns[eArg2.columnIndex] = true;
1876                    elist[eArg2.columnIndex]   = eArg;
1877                }
1878            }
1879        }
1880    }
1881
1882    /**
1883     * Find a table filter with the given table alias
1884     */
1885    TableFilter findTableFilter(TableFilter[] list) {
1886
1887        for (int t = 0; t < list.length; t++) {
1888            TableFilter f = list[t];
1889
1890            if (schema == null
1891                    || f.filterTable.getSchemaName().equals(schema)) {
1892                if (f.getName().equals(tableName)) {
1893                    return f;
1894                }
1895            }
1896        }
1897
1898        return null;
1899    }
1900
1901    /**
1902     * Resolve the table names for columns
1903     *
1904     * @param f table filter
1905     *
1906     * @throws HsqlException
1907     */
1908    void resolveTables(TableFilter f) throws HsqlException {
1909
1910        if (isParam || f == null || exprType == Expression.VALUE) {
1911            return;
1912        }
1913
1914        if (eArg != null) {
1915            eArg.resolveTables(f);
1916        }
1917
1918        if (eArg2 != null) {
1919            eArg2.resolveTables(f);
1920        }
1921
1922        switch (exprType) {
1923
1924            case COLUMN :
1925                if (tableFilter != null) {
1926                    break;
1927                }
1928
1929                String   filterName = f.getName();
1930
1931                if (tableName == null || tableName.equals(filterName)) {
1932                    Table table = f.getTable();
1933                    int   i     = table.findColumn(columnName);
1934
1935                    if (i != -1) {
1936                        tableFilter = f;
1937                        columnIndex = i;
1938                        tableName   = filterName;
1939
1940                        setTableColumnAttributes(table, i);
1941
1942                        // COLUMN is leaf; we are done
1943                        return;
1944                    }
1945                }
1946                break;
1947
1948            case QUERY :
1949
1950                // we now (1_7_2_ALPHA_R) resolve independently first, then
1951                // resolve in the enclosing context
1952                if (subQuery != null) {
1953                    subQuery.select.resolveTablesUnion(f);
1954                }
1955                break;
1956
1957            case FUNCTION :
1958                if (function != null) {
1959                    function.resolveTables(f);
1960                }
1961                break;
1962
1963            case ALL :
1964            case ANY :
1965                break;
1966
1967            case IN :
1968                if (eArg2.exprType != QUERY) {
1969                    Expression[] vl = eArg2.valueList;
1970
1971                    for (int i = 0; i < vl.length; i++) {
1972                        vl[i].resolveTables(f);
1973                    }
1974                }
1975                break;
1976
1977            default :
1978        }
1979    }
1980
1981    /**
1982     * For CASE WHEN and its special cases section 9.3 of the SQL standard
1983     * on type aggregation should be implemented.
1984     */
1985    int getCaseWhenType(Session session) throws HsqlException {
1986
1987        /*
1988            find data type in condition
1989            int type = eArg.eArg.getDataType();
1990            then recurse on eArg2
1991
1992        */
1993        return eArg2.dataType;
1994    }
1995
1996    void resolveTypes(Session session) throws HsqlException {
1997
1998        if (isParam) {
1999            return;
2000        }
2001
2002        if (eArg != null) {
2003            eArg.resolveTypes(session);
2004        }
2005
2006        if (eArg2 != null) {
2007            eArg2.resolveTypes(session);
2008        }
2009
2010        switch (exprType) {
2011
2012            case VALUE :
2013                if (dataType == Types.BOOLEAN && valueData != null) {
2014                    dataType = 0;
2015                    exprType = ((Boolean  ) valueData).booleanValue() ? TRUE
2016                                                                    : FALSE;
2017                }
2018                break;
2019
2020            case COLUMN :
2021                break;
2022
2023            case FUNCTION :
2024                function.resolveType(session);
2025
2026                dataType = function.getReturnType();
2027                break;
2028
2029            case QUERY : {
2030                subQuery.select.resolveTypes(session);
2031
2032                dataType = subQuery.select.exprColumns[0].dataType;
2033
2034                break;
2035            }
2036            case NEGATE :
2037                if (eArg.isParam) {
2038                    throw Trace.error(Trace.UNRESOLVED_PARAMETER_TYPE,
2039                                      Trace.Expression_resolveTypes1);
2040                }
2041
2042                dataType = eArg.dataType;
2043
2044                if (isFixedConstant()) {
2045                    valueData = getValue(session, dataType);
2046                    eArg      = null;
2047                    exprType  = VALUE;
2048                }
2049                break;
2050
2051            case ADD :
2052
2053                // concat using + operator
2054                // non-standard concat operator to be deprecated
2055                if (Types.isCharacterType(eArg.dataType)
2056                        || Types.isCharacterType(eArg2.dataType)) {
2057                    exprType = Expression.CONCAT;
2058                    dataType = Types.VARCHAR;
2059
2060                    if (isFixedConstant()) {
2061                        valueData = getValue(session, dataType);
2062                        eArg      = null;
2063                        eArg2     = null;
2064                        exprType  = VALUE;
2065                    } else {
2066                        if (eArg.isParam) {
2067                            eArg.dataType = Types.VARCHAR;
2068                        }
2069
2070                        if (eArg2.isParam) {
2071                            eArg2.dataType = Types.VARCHAR;
2072                        }
2073                    }
2074
2075                    break;
2076                }
2077            case SUBTRACT :
2078            case MULTIPLY :
2079            case DIVIDE :
2080                if (eArg.isParam && eArg2.isParam) {
2081                    throw Trace.error(Trace.UNRESOLVED_PARAMETER_TYPE,
2082                                      Trace.Expression_resolveTypes2);
2083                }
2084
2085                if (isFixedConstant()) {
2086                    dataType = Column.getCombinedNumberType(eArg.dataType,
2087                            eArg2.dataType, exprType);
2088                    valueData = getValue(session, dataType);
2089                    eArg      = null;
2090                    eArg2     = null;
2091                    exprType  = VALUE;
2092                } else {
2093                    if (eArg.isParam) {
2094                        eArg.dataType = eArg2.dataType;
2095                    } else if (eArg2.isParam) {
2096                        eArg2.dataType = eArg.dataType;
2097                    }
2098
2099                    // fredt@users 20011010 - patch 442993 by fredt
2100                    dataType = Column.getCombinedNumberType(eArg.dataType,
2101                            eArg2.dataType, exprType);
2102                }
2103                break;
2104
2105            case CONCAT :
2106                dataType = Types.VARCHAR;
2107
2108                if (isFixedConstant()) {
2109                    valueData = getValue(session, dataType);
2110                    eArg      = null;
2111                    eArg2     = null;
2112                    exprType  = VALUE;
2113                } else {
2114                    if (eArg.isParam) {
2115                        eArg.dataType = Types.VARCHAR;
2116                    }
2117
2118                    if (eArg2.isParam) {
2119                        eArg2.dataType = Types.VARCHAR;
2120                    }
2121                }
2122                break;
2123
2124            case EQUAL :
2125            case BIGGER_EQUAL :
2126            case BIGGER :
2127            case SMALLER :
2128            case SMALLER_EQUAL :
2129            case NOT_EQUAL :
2130                if (eArg.isParam && eArg2.isParam) {
2131                    throw Trace.error(Trace.UNRESOLVED_PARAMETER_TYPE,
2132                                      Trace.Expression_resolveTypes3);
2133                }
2134
2135                if (isFixedConditional()) {
2136                    Boolean   result = test(session);
2137
2138                    if (result == null) {
2139                        setNull();
2140                    } else if (result.booleanValue()) {
2141                        exprType = TRUE;
2142                    } else {
2143                        exprType = FALSE;
2144                    }
2145
2146                    eArg  = null;
2147                    eArg2 = null;
2148                } else if (eArg.isParam) {
2149                    eArg.dataType = eArg2.dataType == Types.NULL
2150                                    ? Types.VARCHAR
2151                                    : eArg2.dataType;
2152
2153                    if (eArg2.exprType == COLUMN) {
2154                        eArg.setTableColumnAttributes(eArg2);
2155                    }
2156                } else if (eArg2.isParam) {
2157                    eArg2.dataType = eArg.dataType == Types.NULL
2158                                     ? Types.VARCHAR
2159                                     : eArg.dataType;
2160
2161                    if (eArg.exprType == COLUMN) {
2162                        eArg2.setTableColumnAttributes(eArg);
2163                    }
2164                }
2165
2166                dataType = Types.BOOLEAN;
2167                break;
2168
2169            case LIKE :
2170                resolveTypeForLike(session);
2171
2172                dataType = Types.BOOLEAN;
2173                break;
2174
2175            case AND : {
2176                boolean argFixed  = eArg.isFixedConditional();
2177                boolean arg2Fixed = eArg2.isFixedConditional();
2178                Boolean   arg       = argFixed ? (eArg.test(session))
2179                                             : null;
2180                Boolean   arg2      = arg2Fixed ? eArg2.test(session)
2181                                              : null;
2182
2183                if (argFixed && arg2Fixed) {
2184                    if (arg == null || arg2 == null) {
2185                        setNull();
2186                    } else {
2187                        exprType = arg.booleanValue() && arg2.booleanValue()
2188                                   ? TRUE
2189                                   : FALSE;
2190                        eArg  = null;
2191                        eArg2 = null;
2192                    }
2193                } else if ((argFixed &&!Boolean.TRUE.equals(arg))
2194                           || (arg2Fixed &&!Boolean.TRUE.equals(arg2))) {
2195                    exprType = FALSE;
2196                    eArg     = null;
2197                    eArg2    = null;
2198                } else {
2199                    if (eArg.isParam) {
2200                        eArg.dataType = Types.BOOLEAN;
2201                    }
2202
2203                    if (eArg2.isParam) {
2204                        eArg2.dataType = Types.BOOLEAN;
2205                    }
2206                }
2207
2208                dataType = Types.BOOLEAN;
2209
2210                break;
2211            }
2212            case OR : {
2213                boolean argFixed  = eArg.isFixedConditional();
2214                boolean arg2Fixed = eArg2.isFixedConditional();
2215                Boolean   arg       = argFixed ? (eArg.test(session))
2216                                             : null;
2217                Boolean   arg2      = arg2Fixed ? eArg2.test(session)
2218                                              : null;
2219
2220                if (argFixed && arg2Fixed) {
2221                    if (arg == null || arg2 == null) {
2222                        setNull();
2223                    } else {
2224                        exprType = arg.booleanValue() || arg2.booleanValue()
2225                                   ? TRUE
2226                                   : FALSE;
2227                        eArg  = null;
2228                        eArg2 = null;
2229                    }
2230                } else if ((argFixed && Boolean.TRUE.equals(arg))
2231                           || (arg2Fixed && Boolean.TRUE.equals(arg2))) {
2232                    exprType = TRUE;
2233                    eArg     = null;
2234                    eArg2    = null;
2235                } else {
2236                    if (eArg.isParam) {
2237                        eArg.dataType = Types.BOOLEAN;
2238                    }
2239
2240                    if (eArg2.isParam) {
2241                        eArg2.dataType = Types.BOOLEAN;
2242                    }
2243                }
2244
2245                dataType = Types.BOOLEAN;
2246
2247                break;
2248            }
2249            case IS_NULL :
2250                if (isFixedConditional()) {
2251                    exprType = Boolean.TRUE.equals(test(session)) ? TRUE
2252                                                                  : FALSE;
2253                    eArg     = null;
2254                } else if (eArg.dataType == Types.NULL) {
2255                    eArg.dataType = Types.VARCHAR;
2256                }
2257
2258                dataType = Types.BOOLEAN;
2259                break;
2260
2261            case NOT :
2262                if (isFixedConditional()) {
2263                    Boolean   arg = test(session);
2264
2265                    if (arg == null) {
2266                        setNull();
2267                    } else {
2268                        exprType = arg.booleanValue() ? TRUE
2269                                                      : FALSE;
2270                        eArg     = null;
2271                    }
2272                } else if (eArg.isParam) {
2273                    eArg.dataType = Types.BOOLEAN;
2274                }
2275
2276                dataType = Types.BOOLEAN;
2277                break;
2278
2279            case ALL :
2280            case ANY :
2281                dataType = eArg.dataType;
2282                break;
2283
2284            case IN :
2285                resolveTypeForIn(session);
2286
2287                dataType = Types.BOOLEAN;
2288                break;
2289
2290            case EXISTS :
2291
2292                // NOTE: no such thing as a param arg if expression is EXISTS
2293                // Also, cannot detect if result is fixed value
2294                dataType = Types.BOOLEAN;
2295                break;
2296
2297            /** @todo fredt - set the correct return type */
2298            case COUNT :
2299                if (eArg.isParam) {
2300                    throw Trace.error(Trace.UNRESOLVED_PARAMETER_TYPE,
2301                                      Trace.Expression_resolveTypes4);
2302                }
2303
2304                dataType = Types.INTEGER;
2305                break;
2306
2307            case MAX :
2308            case MIN :
2309            case SUM :
2310            case AVG :
2311            case EVERY :
2312            case SOME :
2313            case STDDEV_POP :
2314            case STDDEV_SAMP :
2315            case VAR_POP :
2316            case VAR_SAMP :
2317                if (eArg.isParam) {
2318                    throw Trace.error(Trace.UNRESOLVED_PARAMETER_TYPE,
2319                                      Trace.Expression_resolveTypes4);
2320                }
2321
2322                dataType = SetFunction.getType(exprType, eArg.dataType);
2323                break;
2324
2325            case CONVERT :
2326
2327                // NOTE: both iDataType for this expr and for eArg (if isParm)
2328                // are already set in Parser during read
2329                if (eArg.isFixedConstant() || eArg.isFixedConditional()) {
2330                    valueData = getValue(session);
2331                    exprType  = VALUE;
2332                    eArg      = null;
2333                }
2334                break;
2335
2336            case CASEWHEN :
2337
2338                // We use CASEWHEN as parent type.
2339                // In the parent, eArg is the condition, and eArg2 is
2340                // the leaf, tagged as type ALTERNATIVE; its eArg is
2341                // case 1 (how to get the value when the condition in
2342                // the parent evaluates to true), while its eArg2 is case 2
2343                // (how to get the value when the condition in
2344                // the parent evaluates to false).
2345                if (eArg.isParam) {
2346
2347                    // condition is a paramter marker,
2348                    // as in casewhen(?, v1, v2)
2349                    eArg.dataType = Types.BOOLEAN;
2350                }
2351
2352                dataType = getCaseWhenType(session);
2353                break;
2354
2355            case ALTERNATIVE : {
2356                Expression case1 = eArg;
2357                Expression case2 = eArg2;
2358
2359                if (case1.isParam && case2.isParam) {
2360                    throw Trace.error(Trace.UNRESOLVED_PARAMETER_TYPE,
2361                                      Trace.Expression_resolveTypes6);
2362                }
2363
2364                if (case1.isParam || case1.dataType == Types.NULL) {
2365                    case1.dataType = case2.dataType;
2366                } else if (case2.isParam || case2.dataType == Types.NULL) {
2367                    case2.dataType = case1.dataType;
2368                }
2369
2370                if (case1.dataType == Types.NULL
2371                        && case2.dataType == Types.NULL) {
2372                    dataType = Types.NULL;
2373                }
2374
2375                if (Types.isNumberType(case1.dataType)
2376                        && Types.isNumberType(case2.dataType)) {
2377                    dataType = Column.getCombinedNumberType(case1.dataType,
2378                            case2.dataType, ALTERNATIVE);
2379                } else if (Types.isCharacterType(case1.dataType)
2380                           && Types.isCharacterType(case2.dataType)) {
2381                    dataType = Types.LONGVARCHAR;
2382                } else if (case1.dataType != case2.dataType) {
2383                    if (case2.exprType == Expression.VALUE) {
2384                        dataType = case2.dataType = case1.dataType;
2385                        case2.valueData =
2386                            Column.convertObject(case2.valueData, dataType);
2387                    } else if (case1.exprType == Expression.VALUE) {
2388                        dataType = case1.dataType = case2.dataType;
2389                        case1.valueData =
2390                            Column.convertObject(case1.valueData, dataType);
2391                    } else {
2392                        throw Trace.error(Trace.UNRESOLVED_PARAMETER_TYPE,
2393                                          Trace.Expression_resolveTypes7,
2394                                          new String  [] {
2395                            Types.getTypeString(case1.dataType),
2396                            Types.getTypeString(case2.dataType)
2397                        });
2398                    }
2399                } else {
2400                    dataType = case1.dataType;
2401                }
2402
2403                break;
2404            }
2405        }
2406    }
2407
2408    void resolveTypeForLike(Session session) throws HsqlException {
2409
2410        if (eArg.isParam && eArg2.isParam) {
2411            throw Trace.error(Trace.UNRESOLVED_PARAMETER_TYPE,
2412                              Trace.Expression_resolveTypeForLike);
2413        }
2414
2415        if (isFixedConditional()) {
2416            Boolean   arg = test(session);
2417
2418            if (arg == null) {
2419                setNull();
2420            } else {
2421                exprType = arg.booleanValue() ? TRUE
2422                                              : FALSE;
2423                eArg     = null;
2424                eArg2    = null;
2425            }
2426        } else if (eArg.isParam) {
2427            eArg.dataType = Types.VARCHAR;
2428        } else if (eArg2.isParam) {
2429            eArg2.dataType = Types.VARCHAR;
2430        }
2431
2432// boucherb@users 2003-09-25 - patch 1.7.2 Alpha P
2433//
2434// Some optimizations for LIKE
2435//
2436// TODO:
2437//
2438// See if the same optimizations can be done dynamically at execute time when
2439// eArg2 is PARAM.  Unfortunately, this currently requires re-resolving from
2440// the root any expression containing at least one parameterized LIKE in the
2441// compiled statement and reseting conditions on any involved table filters,
2442// so the answer is: probably not, at least not under the current code.
2443//
2444// CHECKME:
2445//
2446// Test for correct results under all XXXCHAR types (padding, etc.?)
2447//
2448// NOTE:
2449//
2450// For the old behaviour, simply comment out the block below
2451        if (likeObject.optimised) {
2452            return;
2453        }
2454
2455        boolean isRightArgFixedConstant = eArg2.isFixedConstant();
2456        String   likeStr = isRightArgFixedConstant
2457                         ? (String  ) eArg2.getValue(session, Types.VARCHAR)
2458                         : null;
2459        boolean ignoreCase = eArg.dataType == Types.VARCHAR_IGNORECASE
2460                             || eArg2.dataType == Types.VARCHAR_IGNORECASE;
2461
2462        likeObject.setParams(session, likeStr, ignoreCase);
2463
2464        if (!isRightArgFixedConstant) {
2465
2466            // Then we are done here, since it's impossible
2467            // to determine at this point if the right expression
2468            // will have a fixed prefix that can be used to optimize
2469            // any involved table filters
2470            return;
2471        }
2472
2473        if (likeObject.isEquivalentToFalsePredicate()) {
2474            exprType   = FALSE;
2475            eArg       = null;
2476            eArg2      = null;
2477            likeObject = null;
2478        } else if (likeObject.isEquivalentToEqualsPredicate()) {
2479            exprType   = EQUAL;
2480            eArg2 = new Expression(Types.VARCHAR, likeObject.getRangeLow());
2481            likeObject = null;
2482        } else if (likeObject.isEquivalentToNotNullPredicate()) {}
2483        else {
2484            if (eArg.exprType != Expression.COLUMN) {
2485
2486                // Then we are done here, since range predicates are
2487                // not picked up for use to optimize table filters
2488                // unless the predicate is on the first column of
2489                // an index.
2490                // TODO:
2491                // We might go one step further here and check if the
2492                // column is elligible (is the first column of some
2493                // index on its table).  If it is not, it may be that
2494                // substituting/inserting range predicate below
2495                // can actually lower performance.
2496                // Indeed, we might better consider delaying the
2497                // optimizations below till the TableFilter.setConditions()
2498                // phase.
2499                return;
2500            }
2501
2502            if (!Types.isCharacterType(eArg.dataType)) {
2503
2504                // TODO:
2505                // correct range low / range high generation for
2506                // types other than XXXCHAR
2507                return;
2508            }
2509
2510            boolean between = false;
2511            boolean like    = false;
2512            boolean larger  = false;
2513
2514            if (likeObject.isEquivalentToBetweenPredicate()) {
2515
2516                // X LIKE 'abc%' <=> X >= 'abc' AND X <= 'abc' || max_collation_char
2517                larger  = likeObject.hasCollation;
2518                between = !larger;
2519                like    = larger;
2520            } else if (likeObject
2521                    .isEquivalentToBetweenPredicateAugmentedWithLike()) {
2522
2523                // X LIKE 'abc%...' <=> X >= 'abc' AND X <= 'abc' || max_collation_char AND X LIKE 'abc%...'
2524                larger  = likeObject.hasCollation;
2525                between = !larger;
2526                like    = true;
2527            }
2528
2529            if (between == false && larger == false) {
2530                return;
2531            }
2532
2533            Expression eFirst = new Expression(Types.VARCHAR,
2534                                               likeObject.getRangeLow());
2535            Expression eLast = new Expression(Types.VARCHAR,
2536                                              likeObject.getRangeHigh());
2537
2538            if (between &&!like) {
2539                Expression eArgOld = eArg;
2540
2541                eArg       = new Expression(BIGGER_EQUAL, eArgOld, eFirst);
2542                eArg2      = new Expression(SMALLER_EQUAL, eArgOld, eLast);
2543                exprType   = AND;
2544                likeObject = null;
2545            } else if (between && like) {
2546                Expression gte = new Expression(BIGGER_EQUAL, eArg, eFirst);
2547                Expression lte = new Expression(SMALLER_EQUAL, eArg, eLast);
2548
2549                eArg2 = new Expression(eArg, eArg2, likeObject.escapeChar,
2550                                       likeObject.hasCollation);
2551                eArg2.likeObject = likeObject;
2552                eArg             = new Expression(AND, gte, lte);
2553                exprType         = AND;
2554                likeObject       = null;
2555            } else if (larger) {
2556                Expression gte = new Expression(BIGGER_EQUAL, eArg, eFirst);
2557
2558                eArg2 = new Expression(eArg, eArg2, likeObject.escapeChar,
2559                                       likeObject.hasCollation);
2560                eArg2.likeObject = likeObject;
2561                eArg             = gte;
2562                exprType         = AND;
2563                likeObject       = null;
2564            }
2565        }
2566    }
2567
2568    /**
2569     * Parametric or fixed value lists plus queries are handled.
2570     *
2571     * Empty lists are not allowed.
2572     *
2573     * Parametric predicand is resolved against the value list and vice versa.
2574     */
2575    void resolveTypeForIn(Session session) throws HsqlException {
2576
2577        if (eArg2.exprType == QUERY) {
2578            if (eArg.isParam) {
2579                eArg.dataType = eArg2.dataType;
2580            }
2581
2582            isQueryCorrelated = !eArg2.subQuery.isResolved;
2583        } else {
2584            Expression[] vl  = eArg2.valueList;
2585            int          len = vl.length;
2586
2587            if (eArg.isParam) {
2588                if (vl[0].isParam) {
2589                    throw Trace.error(Trace.UNRESOLVED_PARAMETER_TYPE,
2590                                      Trace.Expression_resolveTypeForIn2);
2591                }
2592
2593                Expression e  = vl[0];
2594                int        dt = e.dataType;
2595
2596                // PARAM datatype same as first value list expression
2597                // should never be Types.NULL when all is said and done
2598                if (dt == Types.NULL) {
2599
2600                    // do nothing...
2601                } else {
2602                    if (eArg.dataType == Types.NULL) {
2603                        eArg.dataType = dt;
2604                    }
2605
2606                    if (eArg2.dataType == Types.NULL) {
2607                        eArg2.dataType = dt;
2608                    }
2609                }
2610
2611                for (int i = 1; i < len; i++) {
2612                    e = vl[i];
2613
2614                    if (e.isParam) {
2615                        if (e.dataType == Types.NULL && dt != Types.NULL) {
2616                            e.dataType = dt;
2617                        }
2618                    } else {
2619                        e.resolveTypes(session);
2620                    }
2621                }
2622            } else {
2623                int dt = eArg.dataType;
2624
2625                if (eArg2.dataType == Types.NULL && dt != Types.NULL) {
2626                    eArg2.dataType = dt;
2627                }
2628
2629                for (int i = 0; i < len; i++) {
2630                    Expression e = vl[i];
2631
2632                    if (e.isParam) {
2633                        if (e.dataType == Types.NULL && dt != Types.NULL) {
2634                            e.dataType = dt;
2635                        }
2636                    } else {
2637                        e.resolveTypes(session);
2638                    }
2639                }
2640            }
2641
2642            eArg2.isFixedConstantValueList = eArg2.dataType
2643                                             != Types.VARCHAR_IGNORECASE;
2644
2645            for (int i = 0; i < len; i++) {
2646                if (!vl[i].isFixedConstant()) {
2647                    eArg2.isFixedConstantValueList = false;
2648                    isQueryCorrelated              = true;
2649
2650                    break;
2651                }
2652            }
2653
2654            if (eArg2.isFixedConstantValueList) {
2655                eArg2.hList = new HashSet();
2656
2657                for (int i = 0; i < len; i++) {
2658                    try {
2659                        Object   value = eArg2.valueList[i].getValue(session);
2660
2661                        value = Column.convertObject(value, eArg2.dataType);
2662
2663                        if (eArg2.dataType == Types.CHAR && value != null) {
2664                            value = Library.rtrim((String  ) value);
2665                        }
2666
2667                        eArg2.hList.add(value);
2668                    } catch (HsqlException e) {}
2669                }
2670            }
2671        }
2672    }
2673
2674    /**
2675     * Has this expression been resolved
2676     *
2677     *
2678     * @return boolean
2679     */
2680    boolean isResolved() {
2681
2682        switch (exprType) {
2683
2684            case VALUE :
2685            case NEGATE :
2686                return true;
2687
2688            case COLUMN :
2689                return tableFilter != null && tableFilter.isAssigned;
2690
2691            case QUERY :
2692                return subQuery.isResolved;
2693        }
2694
2695        // todo: could recurse here, but never miss a 'false'!
2696        return false;
2697    }
2698
2699    /**
2700     * Is the argument expression type a comparison expression
2701     *
2702     * @param i expresion type
2703     *
2704     * @return boolean
2705     */
2706    static boolean isCompare(int i) {
2707
2708        switch (i) {
2709
2710            case EQUAL :
2711            case BIGGER_EQUAL :
2712            case BIGGER :
2713            case SMALLER :
2714            case SMALLER_EQUAL :
2715            case NOT_EQUAL :
2716                return true;
2717        }
2718
2719        return false;
2720    }
2721
2722    /**
2723     * Returns the table name for a column expression as a string
2724     *
2725     * @return table name
2726     */
2727    String   getTableName() {
2728
2729        if (exprType == ASTERISK) {
2730            return tableName;
2731        }
2732
2733        if (exprType == COLUMN) {
2734            if (tableFilter == null) {
2735                return tableName;
2736            } else {
2737                return tableFilter.getTable().getName().name;
2738            }
2739        }
2740
2741        // todo
2742        return "";
2743    }
2744
2745    /**
2746     * Returns the table name for a column expression as a string
2747     *
2748     * @return table name
2749     */
2750    String   getFilterTableName() {
2751
2752        if (tableFilter == null) {
2753            return "";
2754        } else {
2755            return tableFilter.getTable().getName().name;
2756        }
2757    }
2758
2759    /**
2760     * Returns the HsqlName of the table for a column expression
2761     *
2762     * @return table name
2763     */
2764    HsqlName getTableHsqlName() {
2765
2766        if (tableFilter == null) {
2767            return null;
2768        } else {
2769            return tableFilter.getTable().getName();
2770        }
2771    }
2772
2773    String   getTableSchemaName() {
2774
2775        if (tableFilter == null) {
2776            return null;
2777        } else {
2778            return tableFilter.getTable().getName().schema.name;
2779        }
2780    }
2781
2782    /**
2783     * Returns the name of a column as string
2784     *
2785     * @return column name
2786     */
2787    String   getColumnName() {
2788
2789        if (exprType == COLUMN) {
2790            if (tableFilter == null) {
2791                return columnName;
2792            } else {
2793                return tableFilter.getTable().getColumn(
2794                    columnIndex).columnName.name;
2795            }
2796        }
2797
2798        return getAlias();
2799    }
2800
2801    /**
2802     * Returns the name of a column as string
2803     *
2804     * @return column name
2805     */
2806    String   getBaseColumnName() {
2807
2808        if (exprType == COLUMN && tableFilter != null) {
2809            return tableFilter.getTable().getColumn(
2810                columnIndex).columnName.name;
2811        }
2812
2813        return null;
2814    }
2815
2816    /**
2817     * Returns the column index in the table
2818     *
2819     * @return column index
2820     */
2821    int getColumnNr() {
2822        return columnIndex;
2823    }
2824
2825    /**
2826     * Returns the column size
2827     *
2828     * @return size
2829     */
2830    int getColumnSize() {
2831        return precision;
2832    }
2833
2834    /**
2835     * Returns the column scale
2836     *
2837     *
2838     * @return scale
2839     */
2840    int getColumnScale() {
2841        return scale;
2842    }
2843
2844    /**
2845     * Set this as a set function with / without DISTINCT
2846     *
2847     * @param distinct is distinct
2848     */
2849    void setDistinctAggregate(boolean distinct) {
2850
2851        isDistinctAggregate = distinct && (eArg.exprType != ASTERISK);
2852
2853        if (exprType == COUNT) {
2854            dataType = distinct ? dataType
2855                                : Types.INTEGER;
2856        }
2857    }
2858
2859    /**
2860     * Swap the condition with its complement
2861     *
2862     * @throws HsqlException
2863     */
2864    void swapCondition() throws HsqlException {
2865
2866        int i = EQUAL;
2867
2868        switch (exprType) {
2869
2870            case BIGGER_EQUAL :
2871                i = SMALLER_EQUAL;
2872                break;
2873
2874            case SMALLER_EQUAL :
2875                i = BIGGER_EQUAL;
2876                break;
2877
2878            case SMALLER :
2879                i = BIGGER;
2880                break;
2881
2882            case BIGGER :
2883                i = SMALLER;
2884                break;
2885
2886            case EQUAL :
2887                break;
2888
2889            default :
2890                Trace.doAssert(false, "Expression.swapCondition");
2891        }
2892
2893        exprType = i;
2894
2895        Expression e = eArg;
2896
2897        eArg  = eArg2;
2898        eArg2 = e;
2899    }
2900
2901    /**
2902     * Returns the data type
2903     *
2904     *
2905     * @return type
2906     */
2907    int getDataType() {
2908        return dataType;
2909    }
2910
2911    /**
2912     * Get the value in the given type in the given session context
2913     *
2914     *
2915     * @param type returned type
2916     * @param session context
2917     * @return value
2918     *
2919     * @throws HsqlException
2920     */
2921    Object   getValue(Session session, int type) throws HsqlException {
2922
2923        Object   o = getValue(session);
2924
2925        if ((o == null) || (dataType == type)) {
2926            return o;
2927        }
2928
2929        return Column.convertObject(o, type);
2930    }
2931
2932    /**
2933     * Get the result of a SetFunction or an ordinary value
2934     *
2935     * @param currValue instance of set function or value
2936     * @param session context
2937     * @return object
2938     *
2939     * @throws HsqlException
2940     */
2941    Object   getAggregatedValue(Session session,
2942                              Object   currValue) throws HsqlException {
2943
2944        if (!isAggregate()) {
2945            return currValue;
2946        }
2947
2948        // handle expressions
2949        Object   leftValue  = null,
2950               rightValue = null;
2951
2952        switch (aggregateSpec) {
2953
2954            case AGGREGATE_SELF : {
2955
2956                // handles results of aggregates plus NEGATE and CONVERT
2957                switch (exprType) {
2958
2959                    case COUNT :
2960                        if (currValue == null) {
2961                            return INTEGER_0;
2962                        }
2963
2964                        return ((SetFunction) currValue).getValue();
2965
2966                    case MAX :
2967                    case MIN :
2968                    case SUM :
2969                    case AVG :
2970                    case EVERY :
2971                    case SOME :
2972                    case STDDEV_POP :
2973                    case STDDEV_SAMP :
2974                    case VAR_POP :
2975                    case VAR_SAMP :
2976                        if (currValue == null) {
2977                            return null;
2978                        }
2979
2980                        return ((SetFunction) currValue).getValue();
2981                }
2982            }
2983            case AGGREGATE_LEFT :
2984                leftValue = eArg.getAggregatedValue(session,
2985                                                    currValue == null ? null
2986                                                                      : ((Object  []) currValue)[0]);
2987
2988                if (currValue == null) {
2989                    rightValue = eArg2 == null ? null
2990                                               : eArg2.getValue(session);
2991                } else {
2992                    rightValue = ((Object  []) currValue)[1];
2993                }
2994                break;
2995
2996            case AGGREGATE_RIGHT :
2997                if (currValue == null) {
2998                    leftValue = eArg == null ? null
2999                                             : eArg.getValue(session);
3000                } else {
3001                    leftValue = ((Object  []) currValue)[0];
3002                }
3003
3004                rightValue = eArg2.getAggregatedValue(session,
3005                                                      currValue == null ? null
3006                                                                        : ((Object  []) currValue)[1]);
3007                break;
3008
3009            case AGGREGATE_BOTH :
3010                if (currValue == null) {
3011                    currValue = new Object  [2];
3012                }
3013
3014                leftValue =
3015                    eArg.getAggregatedValue(session,
3016                                            ((Object  []) currValue)[0]);
3017                rightValue =
3018                    eArg2.getAggregatedValue(session,
3019                                             ((Object  []) currValue)[1]);
3020                break;
3021        }
3022
3023        // handle other operations
3024        switch (exprType) {
3025
3026            case NEGATE :
3027                return Column.negate(leftValue, dataType);
3028
3029            case CONVERT :
3030                return Column.convertObject(session, leftValue, dataType,
3031                                            precision, scale);
3032
3033            case TRUE :
3034                return Boolean.TRUE;
3035
3036            case FALSE :
3037                return Boolean.FALSE;
3038
3039            case NOT :
3040                if (leftValue == null) {
3041                    return null;
3042                }
3043
3044                return ((Boolean  ) leftValue).booleanValue() ? Boolean.FALSE
3045                                                            : Boolean.TRUE;
3046
3047            case AND :
3048                if (leftValue == null || rightValue == null) {
3049                    return null;
3050                }
3051
3052                return ((Boolean  ) leftValue).booleanValue()
3053                       && ((Boolean  ) rightValue).booleanValue() ? Boolean.TRUE
3054                                                                : Boolean
3055                                                                .FALSE;
3056
3057            case OR :
3058                if (Boolean.TRUE.equals(leftValue)) {
3059                    return Boolean.TRUE;
3060                }
3061
3062                return Boolean.TRUE.equals(rightValue) ? Boolean.TRUE
3063                                                       : Boolean.FALSE;
3064
3065            case IS_NULL :
3066                return leftValue == null ? Boolean.TRUE
3067                                         : Boolean.FALSE;
3068
3069            case LIKE :
3070                String   s = (String  ) Column.convertObject(rightValue,
3071                    Types.VARCHAR);
3072
3073                if (eArg2.isParam || eArg2.exprType != VALUE) {
3074                    likeObject.resetPattern(session, s);
3075                }
3076
3077                String   c = (String  ) Column.convertObject(leftValue,
3078                    Types.VARCHAR);
3079
3080                return likeObject.compare(session, c);
3081
3082            case ALL :
3083            case ANY :
3084                return null;
3085
3086            case IN :
3087                return eArg2.testInCondition(session, leftValue);
3088
3089            case EXISTS :
3090                if (!eArg.subQuery.isResolved) {
3091                    Result r = eArg.subQuery.select.getResult(session, 1);    // 1 is already enough
3092
3093                    return r.rRoot == null ? Boolean.FALSE
3094                                           : Boolean.TRUE;
3095                } else {
3096                    return subQuery.table.isEmpty(session) ? Boolean.FALSE
3097                                                           : Boolean.TRUE;
3098                }
3099            case CASEWHEN :
3100                leftValue = Column.convertObject(leftValue, Types.BOOLEAN);
3101
3102                boolean test   = ((Boolean  ) leftValue).booleanValue();
3103                Object    result = test ? ((Object  []) rightValue)[0]
3104                                      : ((Object  []) rightValue)[1];
3105
3106                return Column.convertObject(result, dataType);
3107
3108            case ALTERNATIVE :
3109                leftValue  = Column.convertObject(leftValue, dataType);
3110                rightValue = Column.convertObject(rightValue, dataType);
3111
3112                Object  [] objectPair = new Object  [2];
3113
3114                objectPair[0] = leftValue;
3115                objectPair[1] = rightValue;
3116
3117                return objectPair;
3118
3119            case FUNCTION :
3120                return function.getAggregatedValue(session, currValue);
3121        }
3122
3123        // handle comparisons
3124        if (isCompare(exprType)) {
3125            if (eArg2.exprType == Expression.ANY
3126                    || eArg2.exprType == Expression.ALL) {
3127                return testAnyAllCondition(session, leftValue);
3128            }
3129
3130            return compareValues(session, leftValue, rightValue);
3131        }
3132
3133        // handle arithmetic and concat operations
3134        if (leftValue != null) {
3135            leftValue = Column.convertObject(leftValue, dataType);
3136        }
3137
3138        if (rightValue != null) {
3139            rightValue = Column.convertObject(rightValue, dataType);
3140        }
3141
3142        switch (exprType) {
3143
3144            case ADD :
3145                return Column.add(leftValue, rightValue, dataType);
3146
3147            case SUBTRACT :
3148                return Column.subtract(leftValue, rightValue, dataType);
3149
3150            case MULTIPLY :
3151                return Column.multiply(leftValue, rightValue, dataType);
3152
3153            case DIVIDE :
3154                return Column.divide(leftValue, rightValue, dataType);
3155
3156            case CONCAT :
3157                return Column.concat(leftValue, rightValue);
3158
3159            default :
3160                throw Trace.error(Trace.NEED_AGGREGATE,
3161                                  this.describe(session));
3162        }
3163    }
3164
3165    /**
3166     * Instantiate the SetFunction or recurse, returning the result
3167     *
3168     * @param currValue setFunction
3169     * @param session context
3170     * @return a normal value or SetFunction instance
3171     *
3172     * @throws HsqlException
3173     */
3174    Object   updateAggregatingValue(Session session,
3175                                  Object   currValue) throws HsqlException {
3176
3177        switch (aggregateSpec) {
3178
3179            case AGGREGATE_SELF : {
3180                if (currValue == null) {
3181                    currValue = new SetFunction(exprType, eArg.dataType,
3182                                                isDistinctAggregate);
3183                }
3184
3185                Object   newValue = eArg.exprType == ASTERISK ? INTEGER_1
3186                                                            : eArg.getValue(
3187                                                                session);
3188
3189                ((SetFunction) currValue).add(session, newValue);
3190
3191                return currValue;
3192            }
3193            case AGGREGATE_BOTH : {
3194                Object  [] valuePair = (Object  []) currValue;
3195
3196                if (valuePair == null) {
3197                    valuePair = new Object  [2];
3198                }
3199
3200                valuePair[0] = eArg.updateAggregatingValue(session,
3201                        valuePair[0]);
3202                valuePair[1] = eArg2.updateAggregatingValue(session,
3203                        valuePair[1]);
3204
3205                return valuePair;
3206            }
3207            case AGGREGATE_LEFT : {
3208                Object  [] valuePair = (Object  []) currValue;
3209
3210                if (valuePair == null) {
3211                    valuePair = new Object  [2];
3212                }
3213
3214                valuePair[0] = eArg.updateAggregatingValue(session,
3215                        valuePair[0]);
3216
3217                if (eArg2 != null) {
3218                    valuePair[1] = eArg2.getValue(session);
3219                }
3220
3221                return valuePair;
3222            }
3223            case AGGREGATE_RIGHT : {
3224                Object  [] valuePair = (Object  []) currValue;
3225
3226                if (valuePair == null) {
3227                    valuePair = new Object  [2];
3228                }
3229
3230                if (eArg != null) {
3231                    valuePair[0] = eArg.getValue(session);
3232                }
3233
3234                valuePair[1] = eArg2.updateAggregatingValue(session,
3235                        valuePair[1]);
3236
3237                return valuePair;
3238            }
3239            case AGGREGATE_FUNCTION :
3240                return function.updateAggregatingValue(session, currValue);
3241
3242            default :
3243
3244                // never gets here
3245                return currValue;
3246        }
3247    }
3248
3249    Object   getValue(Session session) throws HsqlException {
3250
3251        switch (exprType) {
3252
3253            case VALUE :
3254                return valueData;
3255
3256            case COLUMN :
3257                try {
3258                    return tableFilter.currentData[columnIndex];
3259                } catch (NullPointerException   e) {
3260                    String   name = tableName == null ? columnName
3261                                                    : tableName + '.'
3262                                                      + columnName;
3263
3264                    throw Trace.error(Trace.COLUMN_NOT_FOUND, name);
3265                }
3266            case FUNCTION :
3267                return function.getValue(session);
3268
3269            case QUERY :
3270                return subQuery.select.getValue(session, dataType);
3271
3272            case NEGATE :
3273                return Column.negate(eArg.getValue(session, dataType),
3274                                     dataType);
3275
3276            case ALL :
3277            case ANY :
3278                return null;
3279
3280            case AND :
3281            case OR :
3282            case LIKE :
3283            case EXISTS :
3284            case IN :
3285                return test(session);
3286
3287            case CONVERT :
3288                return Column.convertObject(session, eArg.getValue(session),
3289                                            dataType, precision, scale);
3290
3291            case CASEWHEN :
3292                Boolean   result = eArg.test(session);
3293
3294                if (Boolean.TRUE.equals(result)) {
3295                    return eArg2.eArg.getValue(session, dataType);
3296                } else {
3297                    return eArg2.eArg2.getValue(session, dataType);
3298                }
3299
3300            // gets here from getAggregatedValue()
3301            case ALTERNATIVE :
3302                return new Object  [] {
3303                    eArg.getValue(session, dataType),
3304                    eArg2.getValue(session, dataType)
3305                };
3306        }
3307
3308        // todo: simplify this
3309        Object   a = null,
3310               b = null;
3311
3312        if (eArg != null) {
3313            a = eArg.getValue(session, dataType);
3314        }
3315
3316        if (eArg2 != null) {
3317            b = eArg2.getValue(session, dataType);
3318        }
3319
3320        switch (exprType) {
3321
3322            case ADD :
3323                return Column.add(a, b, dataType);
3324
3325            case SUBTRACT :
3326                return Column.subtract(a, b, dataType);
3327
3328            case MULTIPLY :
3329                return Column.multiply(a, b, dataType);
3330
3331            case DIVIDE :
3332                return Column.divide(a, b, dataType);
3333
3334            case CONCAT :
3335                return Column.concat(a, b);
3336
3337            case SEQUENCE :
3338                return ((NumberSequence) valueData).getValueObject();
3339
3340            default :
3341
3342                /** @todo fredt - make sure the expression type is always comparison here */
3343                return test(session);
3344        }
3345    }
3346
3347    boolean testCondition(Session session) throws HsqlException {
3348        return Boolean.TRUE.equals(test(session));
3349    }
3350
3351    /**
3352     * Returns the test result of a conditional expression
3353     *
3354     * @param session session
3355     * @return boolean
3356     * @throws HsqlException
3357     */
3358    Boolean   test(Session session) throws HsqlException {
3359
3360        switch (exprType) {
3361
3362            case TRUE :
3363                return Boolean.TRUE;
3364
3365            case FALSE :
3366                return Boolean.FALSE;
3367
3368            case NOT :
3369                if (eArg2 != null) {
3370                    Trace.doAssert(false, "Expression.test");
3371                }
3372
3373                Boolean   result = eArg.test(session);
3374
3375                return result == null ? null
3376                                      : result.booleanValue() ? Boolean.FALSE
3377                                                              : Boolean.TRUE;
3378
3379            case AND : {
3380                Boolean   r1 = eArg.test(session);
3381
3382                if (r1 == null) {
3383                    return null;
3384                }
3385
3386                Boolean   r2 = eArg2.test(session);
3387
3388                if (r2 == null) {
3389                    return null;
3390                }
3391
3392                return r1.booleanValue() && r2.booleanValue() ? Boolean.TRUE
3393                                                              : Boolean.FALSE;
3394            }
3395            case OR : {
3396                boolean r1 = Boolean.TRUE.equals(eArg.test(session));
3397
3398                if (r1) {
3399                    return Boolean.TRUE;
3400                }
3401
3402                return Boolean.TRUE.equals(eArg2.test(session)) ? Boolean.TRUE
3403                                                                : Boolean
3404                                                                .FALSE;
3405            }
3406            case IS_NULL :
3407                return eArg.getValue(session) == null ? Boolean.TRUE
3408                                                      : Boolean.FALSE;
3409
3410            case LIKE :
3411                String   s = (String  ) eArg2.getValue(session, Types.VARCHAR);
3412
3413                if (eArg2.isParam || eArg2.exprType != VALUE) {
3414                    likeObject.resetPattern(session, s);
3415                }
3416
3417                String   c = (String  ) eArg.getValue(session, Types.VARCHAR);
3418
3419                return likeObject.compare(session, c);
3420
3421            case IN :
3422                return eArg2.testInCondition(session, eArg.getValue(session));
3423
3424            case EXISTS :
3425                return eArg.testExistsCondition(session);
3426
3427            case FUNCTION :
3428                Object   value =
3429                    Column.convertObject(function.getValue(session),
3430                                         Types.BOOLEAN);
3431
3432                return (Boolean  ) value;
3433        }
3434
3435        if (eArg == null || eArg2 == null) {
3436            if (exprType == COLUMN) {
3437                if (dataType == Types.BOOLEAN
3438                        || Types.isNumberType(dataType)) {
3439                    Object   value = Column.convertObject(getValue(session),
3440                                                        Types.BOOLEAN);
3441
3442                    return (Boolean  ) value;
3443                }
3444            }
3445
3446            throw Trace.error(Trace.NOT_A_CONDITION);
3447        }
3448
3449        if (eArg2.exprType == Expression.ANY
3450                || eArg2.exprType == Expression.ALL) {
3451            return testAnyAllCondition(session, eArg.getValue(session));
3452        }
3453
3454        Object   o1 = eArg.getValue(session);
3455        Object   o2 = eArg2.getValue(session);
3456
3457        if (o1 == null || o2 == null) {
3458/*
3459 TableFilter.swapCondition() ensures that with LEFT OUTER, eArg is the
3460 column expression for the table on the right hand side.
3461 We do not join tables on nulls apart from outer joins
3462 Any comparison operator can exist in WHERE or JOIN conditions
3463*/
3464            if (eArg.tableFilter != null && eArg.tableFilter.isOuterJoin) {
3465                if (isInJoin) {
3466                    if (eArg.tableFilter.isCurrentOuter && o1 == null) {
3467                        return Boolean.TRUE;
3468                    }
3469                } else {
3470
3471                    // this is used in WHERE <OUTER JOIN COL> IS [NOT] NULL
3472                    eArg.tableFilter.nonJoinIsNull = o2 == null;
3473                }
3474            }
3475
3476            return null;
3477        }
3478
3479        return compareValues(session, o1, o2);
3480    }
3481
3482    private Boolean   compareValues(Session session, Object   o1,
3483                                  Object   o2) throws HsqlException {
3484
3485        int type = eArg.dataType;
3486
3487        if (eArg.dataType != eArg2.dataType) {
3488            if (Types.isNumberType(eArg.dataType)
3489                    && Types.isNumberType(eArg2.dataType)) {
3490                type = Column.getCombinedNumberType(eArg.dataType,
3491                                                    eArg2.dataType, exprType);
3492            }
3493
3494            o1 = Column.convertObject(o1, type);
3495            o2 = Column.convertObject(o2, type);
3496        }
3497
3498        int result = Column.compare(session.database.collation, o1, o2, type);
3499
3500        switch (exprType) {
3501
3502            case EQUAL :
3503                return result == 0 ? Boolean.TRUE
3504                                   : Boolean.FALSE;
3505
3506            case BIGGER :
3507                return result > 0 ? Boolean.TRUE
3508                                  : Boolean.FALSE;
3509
3510            case BIGGER_EQUAL :
3511                return result >= 0 ? Boolean.TRUE
3512                                   : Boolean.FALSE;
3513
3514            case SMALLER_EQUAL :
3515                return result <= 0 ? Boolean.TRUE
3516                                   : Boolean.FALSE;
3517
3518            case SMALLER :
3519                return result < 0 ? Boolean.TRUE
3520                                  : Boolean.FALSE;
3521
3522            case NOT_EQUAL :
3523                return result != 0 ? Boolean.TRUE
3524                                   : Boolean.FALSE;
3525
3526            default :
3527                throw Trace.error(Trace.GENERAL_ERROR,
3528                                  Trace.Expression_compareValues);
3529        }
3530    }
3531
3532    /**
3533     * Returns the result of testing a VALUE_LIST expression
3534     *
3535     * @param o value to check against
3536     * @param session context
3537     * @return boolean
3538     * @throws HsqlException
3539     */
3540    private Boolean   testInCondition(Session session,
3541                                    Object   o) throws HsqlException {
3542
3543        if (o == null) {
3544            return null;
3545        }
3546
3547        if (exprType == VALUELIST) {
3548            try {
3549                o = Column.convertObject(o, dataType);
3550            } catch (HsqlException e) {
3551                return Boolean.FALSE;
3552            }
3553
3554            if (isFixedConstantValueList) {
3555                if (dataType == Types.CHAR) {
3556                    o = Library.rtrim((String  ) o);
3557                }
3558
3559                return hList.contains(o) ? Boolean.TRUE
3560                                         : Boolean.FALSE;
3561            }
3562
3563            final int len = valueList.length;
3564
3565            for (int i = 0; i < len; i++) {
3566                Object   o2 = valueList[i].getValue(session, dataType);
3567
3568                if (Column.compare(
3569                        session.database.collation, o, o2, dataType) == 0) {
3570                    return Boolean.TRUE;
3571                }
3572            }
3573
3574            return Boolean.FALSE;
3575        } else if (exprType == QUERY) {
3576
3577            /** @todo fredt - convert to join */
3578            try {
3579                o = Column.convertObject(
3580                    o, subQuery.table.getColumnTypes()[0]);
3581            } catch (HsqlException e) {
3582                return Boolean.FALSE;
3583            }
3584
3585            if (!subQuery.isResolved) {
3586                subQuery.populateTable(session);
3587            }
3588
3589            Boolean   result =
3590                subQuery.table.getPrimaryIndex().findFirstRow(
3591                    session, o, Expression.EQUAL).hasNext() ? Boolean.TRUE
3592                                                            : Boolean.FALSE;
3593
3594            if (!subQuery.isResolved) {
3595                subQuery.table.clearAllRows(session);
3596            }
3597
3598            return result;
3599        }
3600
3601        throw Trace.error(Trace.WRONG_DATA_TYPE);
3602    }
3603
3604    private Boolean   testExistsCondition(Session session)
3605    throws HsqlException {
3606
3607        if (subQuery.isResolved) {
3608            return subQuery.table.isEmpty(session) ? Boolean.FALSE
3609                                                   : Boolean.TRUE;
3610        } else {
3611            Result r = subQuery.select.getResult(session, 1);    // 1 is already enough
3612
3613            return r.rRoot == null ? Boolean.FALSE
3614                                   : Boolean.TRUE;
3615        }
3616    }
3617
3618    private Boolean   testAnyAllCondition(Session session,
3619                                        Object   o) throws HsqlException {
3620
3621        if (o == null) {
3622            return null;
3623        }
3624
3625        SubQuery subquery = eArg2.eArg.subQuery;
3626        boolean  populate = !subquery.isResolved;
3627
3628        if (populate) {
3629            subquery.populateTable(session);
3630        }
3631
3632        Boolean   result = getAnyAllValue(session, o, subquery);
3633
3634        if (populate) {
3635            subquery.table.clearAllRows(session);
3636        }
3637
3638        return result;
3639    }
3640
3641    private Boolean   getAnyAllValue(Session session, Object   o,
3642                                   SubQuery subquery) throws HsqlException {
3643
3644        boolean     empty    = subquery.table.isEmpty(session);
3645        Index       index    = subquery.table.getPrimaryIndex();
3646        RowIterator it       = index.findFirstRowNotNull(session);
3647        Row         firstrow = it.next();
3648
3649        switch (eArg2.exprType) {
3650
3651            case ANY : {
3652                if (empty) {
3653                    return Boolean.FALSE;
3654                }
3655
3656                if (firstrow == null) {
3657                    return null;
3658                }
3659
3660                int range =
3661                    Column.compareToTypeRange(o, eArg2.eArg.getDataType());
3662
3663                if (range != 0) {
3664                    switch (exprType) {
3665
3666                        case EQUAL :
3667                            return Boolean.FALSE;
3668
3669                        case NOT_EQUAL :
3670                            return Boolean.TRUE;
3671
3672                        case BIGGER :
3673                        case BIGGER_EQUAL :
3674                            return range > 0 ? Boolean.TRUE
3675                                             : Boolean.FALSE;
3676
3677                        case SMALLER_EQUAL :
3678                        case SMALLER :
3679                            return range < 0 ? Boolean.TRUE
3680                                             : Boolean.FALSE;
3681                    }
3682                }
3683
3684                o = Column.convertObject(o, eArg2.eArg.getDataType());
3685
3686                if (exprType == EQUAL) {
3687                    it = index.findFirstRow(session, o, EQUAL);
3688
3689                    return it.hasNext() ? Boolean.TRUE
3690                                        : Boolean.FALSE;
3691                }
3692
3693                Row    lastrow   = index.lastRow(session);
3694                Object   firstdata = firstrow.getData()[0];
3695                Object   lastdata  = lastrow.getData()[0];
3696                int comparefirst = Column.compare(session.database.collation,
3697                                                  o, firstdata,
3698                                                  eArg.getDataType());
3699                int comparelast = Column.compare(session.database.collation,
3700                                                 o, lastdata,
3701                                                 eArg.getDataType());
3702
3703                switch (exprType) {
3704
3705                    case NOT_EQUAL :
3706                        return (comparefirst == 0 && comparelast == 0)
3707                               ? Boolean.FALSE
3708                               : Boolean.TRUE;
3709
3710                    case BIGGER :
3711                        return comparefirst > 0 ? Boolean.TRUE
3712                                                : Boolean.FALSE;
3713
3714                    case BIGGER_EQUAL :
3715                        return comparefirst >= 0 ? Boolean.TRUE
3716                                                 : Boolean.FALSE;
3717
3718                    case SMALLER :
3719                        return comparelast < 0 ? Boolean.TRUE
3720                                               : Boolean.FALSE;
3721
3722                    case SMALLER_EQUAL :
3723                        return comparelast <= 0 ? Boolean.TRUE
3724                                                : Boolean.FALSE;
3725                }
3726
3727                break;
3728            }
3729            case ALL : {
3730                if (empty) {
3731                    return Boolean.TRUE;
3732                }
3733
3734                if (firstrow == null) {
3735                    return null;
3736                }
3737
3738                int range =
3739                    Column.compareToTypeRange(o, eArg2.eArg.getDataType());
3740
3741                if (range != 0) {
3742                    switch (exprType) {
3743
3744                        case EQUAL :
3745                            return Boolean.FALSE;
3746
3747                        case NOT_EQUAL :
3748                            return Boolean.TRUE;
3749
3750                        case BIGGER :
3751                        case BIGGER_EQUAL :
3752                            return range > 0 ? Boolean.TRUE
3753                                             : Boolean.FALSE;
3754
3755                        case SMALLER_EQUAL :
3756                        case SMALLER :
3757                            return range < 0 ? Boolean.TRUE
3758                                             : Boolean.FALSE;
3759                    }
3760                }
3761
3762                o = Column.convertObject(o, eArg2.eArg.getDataType());
3763
3764                if (exprType == EQUAL || exprType == NOT_EQUAL) {
3765                    it = index.findFirstRow(session, o, EQUAL);
3766
3767                    if (exprType == EQUAL) {
3768                        return (it.hasNext() && subquery.table.getRowCount(session) == 1)
3769                               ? Boolean.TRUE
3770                               : Boolean.FALSE;
3771                    }
3772
3773                    return (it.hasNext()) ? Boolean.FALSE
3774                                          : Boolean.TRUE;
3775                }
3776
3777                Row    lastrow   = index.lastRow(session);
3778                Object   firstdata = firstrow.getData()[0];
3779                Object   lastdata  = lastrow.getData()[0];
3780
3781                o = Column.convertObject(o, eArg2.eArg.getDataType());
3782
3783                int comparefirst = Column.compare(session.database.collation,
3784                                                  o, firstdata,
3785                                                  eArg.getDataType());
3786                int comparelast = Column.compare(session.database.collation,
3787                                                 o, lastdata,
3788                                                 eArg.getDataType());
3789
3790                switch (exprType) {
3791
3792                    case NOT_EQUAL :
3793                        return (comparefirst == 0 || comparelast == 0)
3794                               ? Boolean.FALSE
3795                               : Boolean.TRUE;
3796
3797                    case BIGGER :
3798                        return comparelast > 0 ? Boolean.TRUE
3799                                               : Boolean.FALSE;
3800
3801                    case BIGGER_EQUAL :
3802                        return comparelast >= 0 ? Boolean.TRUE
3803                                                : Boolean.FALSE;
3804
3805                    case SMALLER :
3806                        return comparefirst < 0 ? Boolean.TRUE
3807                                                : Boolean.FALSE;
3808
3809                    case SMALLER_EQUAL :
3810                        return comparefirst <= 0 ? Boolean.TRUE
3811                                                 : Boolean.FALSE;
3812                }
3813
3814                break;
3815            }
3816        }
3817
3818        return null;
3819    }
3820
3821    /**
3822     * Marks all the expressions in the tree for a condition that is part
3823     * of a JOIN .. ON ....<br>
3824     *
3825     * For LEFT OUTER joins, also tests the expression tree for the join
3826     * condition to ensure only permitted expression types are there.
3827     *
3828     * If we want to exapand the expressions to include arithmetic operations
3829     * or functions ...
3830     *
3831     * (fredt@users)
3832     *
3833     * @param tf table filter
3834     * @param outer boolean
3835     * @return boolean
3836     */
3837    boolean setForJoin(TableFilter tf, boolean outer) {
3838
3839        isInJoin = outer;
3840
3841        if (outer) {
3842            outerFilter = tf;
3843        }
3844
3845        if (eArg != null) {
3846            if (eArg.setForJoin(tf, outer) == false) {
3847                return false;
3848            }
3849        }
3850
3851        if (eArg2 != null) {
3852            if (eArg2.setForJoin(tf, outer) == false) {
3853                return false;
3854            }
3855        }
3856
3857        return !outer
3858               || (exprType == Expression.AND || exprType == Expression.OR
3859                   || exprType == Expression.COLUMN
3860                   || exprType == Expression.VALUE
3861                   || exprType == Expression.EQUAL
3862                   || exprType == Expression.NOT_EQUAL
3863                   || exprType == Expression.BIGGER
3864                   || exprType == Expression.BIGGER_EQUAL
3865                   || exprType == Expression.SMALLER
3866                   || exprType == Expression.SMALLER_EQUAL
3867                   || exprType == Expression.IS_NULL);
3868    }
3869
3870    /**
3871     * Returns a Select object that can be used for checking the contents
3872     * of an existing table against the given CHECK search condition.
3873     *
3874     * @param t table
3875     * @param e expression
3876     * @return select object
3877     * @throws HsqlException
3878     */
3879    static Select getCheckSelect(Session session, Table t,
3880                                 Expression e) throws HsqlException {
3881
3882        Select s = new Select();
3883
3884        s.exprColumns    = new Expression[1];
3885        s.exprColumns[0] = new Expression(VALUE, Boolean.TRUE);
3886        s.tFilter        = new TableFilter[1];
3887        s.tFilter[0]     = new TableFilter(t, null, null, false);
3888
3889        Expression condition = new Expression(NOT, e, null);
3890
3891        s.queryCondition = condition;
3892
3893        s.resolveAll(session, true);
3894
3895        return s;
3896    }
3897
3898    /**
3899     * Sets the left leaf.
3900     *
3901     * @param e expression
3902     */
3903    void setLeftExpression(Expression e) {
3904        eArg = e;
3905    }
3906
3907    void setRightExpression(Expression e) {
3908        eArg2 = e;
3909    }
3910
3911    /**
3912     * Gets the right leaf.
3913     *
3914     * @return expression
3915     */
3916    Expression getRightExpression() {
3917        return eArg2;
3918    }
3919
3920// boucherb@users 20030417 - patch 1.7.2 - compiled statement support
3921    void bind(Object   o) {
3922        valueData = o;
3923    }
3924
3925    boolean isParam() {
3926        return isParam;
3927    }
3928
3929    boolean isFixedConstant() {
3930
3931        switch (exprType) {
3932
3933            case VALUE :
3934                return !isParam;
3935
3936            case NEGATE :
3937                return eArg.isFixedConstant();
3938
3939            case ADD :
3940            case SUBTRACT :
3941            case MULTIPLY :
3942            case DIVIDE :
3943            case CONCAT :
3944                return eArg.isFixedConstant() && eArg2.isFixedConstant();
3945        }
3946
3947        return false;
3948    }
3949
3950    boolean isFixedConditional() {
3951
3952        switch (exprType) {
3953
3954            case TRUE :
3955            case FALSE :
3956                return true;
3957
3958            case EQUAL :
3959            case BIGGER_EQUAL :
3960            case BIGGER :
3961            case SMALLER :
3962            case SMALLER_EQUAL :
3963            case NOT_EQUAL :
3964            case LIKE :
3965
3966                //case IN : TODO
3967                return eArg.isFixedConstant() && eArg2.isFixedConstant();
3968
3969            case IS_NULL :
3970                return eArg.isFixedConstant();
3971
3972            case NOT :
3973                return eArg.isFixedConditional();
3974
3975            case AND :
3976            case OR :
3977                return eArg.isFixedConditional()
3978                       && eArg2.isFixedConditional();
3979
3980            default :
3981                return false;
3982        }
3983    }
3984
3985    void setTableColumnAttributes(Expression e) {
3986
3987        precision   = e.precision;
3988        scale       = e.scale;
3989        isIdentity  = e.isIdentity;
3990        nullability = e.nullability;
3991        isWritable  = e.isWritable;
3992        catalog     = e.catalog;
3993        schema      = e.schema;
3994    }
3995
3996    void setTableColumnAttributes(Table t, int i) {
3997
3998        Column c = t.getColumn(i);
3999
4000        dataType   = c.getType();
4001        precision  = c.getSize();
4002        scale      = c.getScale();
4003        isIdentity = c.isIdentity();
4004
4005        // IDENTITY columns are not nullable but NULLs are accepted
4006        // and converted into the next identity value for the table
4007        nullability = c.isNullable() &&!isIdentity ? NULLABLE
4008                                                   : NO_NULLS;
4009        isWritable  = t.isWritable();
4010        catalog     = t.getCatalogName();
4011        schema      = t.getSchemaName();
4012    }
4013
4014    String   getValueClassName() {
4015
4016        // boucherb@users 20050516 - patch 1.8.0 removed DITypeInfo dependency
4017        if (valueClassName == null) {
4018            if (function == null) {
4019                valueClassName = Types.getColStClsName(
4020                    (dataType == Types.VARCHAR_IGNORECASE) ? Types.VARCHAR
4021                                                           : dataType);
4022            } else {
4023                valueClassName = function.getReturnClassName();
4024            }
4025        }
4026
4027        return valueClassName;
4028    }
4029
4030    // parameter modes
4031    static final int        PARAM_UNKNOWN = 0;
4032    public static final int PARAM_IN      = 1;
4033    public static final int PARAM_IN_OUT  = 2;
4034    public static final int PARAM_OUT     = 4;
4035
4036    // result set (output column value) or parameter expression nullability
4037    static final int NO_NULLS         = 0;
4038    static final int NULLABLE         = 1;
4039    static final int NULLABLE_UNKNOWN = 2;
4040
4041    // output column and parameter expression metadata values
4042    boolean isIdentity;        // = false
4043    int     nullability = NULLABLE_UNKNOWN;
4044    boolean isWritable;        // = false; true if column of writable table
4045    int     paramMode = PARAM_UNKNOWN;
4046    String    valueClassName;    // = null
4047
4048// boucherb@users 20040111 - patch 1.7.2 RC1 - metadata xxxusage support
4049//-------------------------------------------------------------------
4050    // TODO:  Maybe provide an interface or abstract class + a default
4051    // implementation instead?  This would allow a more powerful system
4052    // of collectors to be created, for example to assist in the optimization
4053    // of condition expression trees:
4054    //
4055    // HashSet joins = new JoinConditionCollector();
4056    // joins.addAll(select.whereCondition);
4057    // for(Iterator it = joins.iterator(); it.hasNext();) {
4058    //      process((it.next());
4059    // }
4060
4061    /**
4062     * Provides a generic way to collect a set of distinct expressions
4063     * of some type from a tree rooted at a specified Expression.
4064     */
4065    static class Collector extends HashSet {
4066
4067        Collector() {
4068            super();
4069        }
4070
4071        void addAll(Expression e, int type) {
4072
4073            Function     function;
4074            Expression[] list;
4075
4076            if (e == null) {
4077                return;
4078            }
4079
4080            addAll(e.getArg(), type);
4081            addAll(e.getArg2(), type);
4082
4083            // CHECKME: What about setTrue() Expressions?
4084            if (e.exprType == type) {
4085                add(e);
4086            }
4087
4088            if (e.subQuery != null) {
4089                addAll(e.subQuery.select, type);
4090            }
4091
4092            function = e.function;
4093
4094            if (function != null) {
4095                list = function.eArg;
4096
4097                if (list != null) {
4098                    for (int i = 0; i < list.length; i++) {
4099                        addAll(list[i], type);
4100                    }
4101                }
4102            }
4103
4104            list = e.valueList;
4105
4106            if (list != null) {
4107                for (int i = 0; i < list.length; i++) {
4108                    addAll(list[i], type);
4109                }
4110            }
4111        }
4112
4113        void addAll(Select select, int type) {
4114
4115            for (; select != null; select = select.unionSelect) {
4116                Expression[] list = select.exprColumns;
4117
4118                for (int i = 0; i < list.length; i++) {
4119                    addAll(list[i], type);
4120                }
4121
4122                addAll(select.queryCondition, type);
4123                addAll(select.havingCondition, type);
4124
4125                // todo order by columns
4126            }
4127        }
4128    }
4129}
4130
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags