001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (http://h2database.com/html/license.html).
004: * Initial Developer: H2 Group
005: */
006: package org.h2.expression;
007:
008: import java.sql.SQLException;
009:
010: import org.h2.command.dml.Query;
011: import org.h2.command.dml.Select;
012: import org.h2.constant.ErrorCode;
013: import org.h2.engine.Database;
014: import org.h2.engine.Session;
015: import org.h2.message.Message;
016: import org.h2.result.LocalResult;
017: import org.h2.table.ColumnResolver;
018: import org.h2.table.TableFilter;
019: import org.h2.table.TableView;
020: import org.h2.value.Value;
021: import org.h2.value.ValueBoolean;
022: import org.h2.value.ValueNull;
023:
024: /**
025: * An 'in' condition with a subquery, as in WHERE ID IN(SELECT ...)
026: */
027: public class ConditionInSelect extends Condition {
028: private Database database;
029: private Expression left;
030: private Query query;
031: private boolean all;
032: private int compareType;
033: private int queryLevel;
034:
035: public ConditionInSelect(Database database, Expression left,
036: Query query, boolean all, int compareType) {
037: this .database = database;
038: this .left = left;
039: this .query = query;
040: this .all = all;
041: this .compareType = compareType;
042: }
043:
044: public Value getValue(Session session) throws SQLException {
045: Value l = left.getValue(session);
046: if (l == ValueNull.INSTANCE) {
047: return l;
048: }
049: query.setSession(session);
050: LocalResult rows = query.query(0);
051: session.addTemporaryResult(rows);
052: boolean hasNull = false;
053: boolean result = all;
054: while (rows.next()) {
055: boolean value;
056: Value r = rows.currentRow()[0];
057: if (r == ValueNull.INSTANCE) {
058: value = false;
059: hasNull = true;
060: } else {
061: value = Comparison.compareNotNull(database, l, r,
062: compareType);
063: }
064: if (!value && all) {
065: result = false;
066: break;
067: } else if (value && !all) {
068: result = true;
069: break;
070: }
071: }
072: if (!result && hasNull) {
073: return ValueNull.INSTANCE;
074: }
075: return ValueBoolean.get(result);
076: }
077:
078: public void mapColumns(ColumnResolver resolver, int queryLevel)
079: throws SQLException {
080: left.mapColumns(resolver, queryLevel);
081: query.mapColumns(resolver, queryLevel + 1);
082: this .queryLevel = Math.max(queryLevel, this .queryLevel);
083: }
084:
085: public Expression optimize(Session session) throws SQLException {
086: left = left.optimize(session);
087: if (left == ValueExpression.NULL) {
088: return left;
089: }
090: query.prepare();
091: if (query.getColumnCount() != 1) {
092: throw Message
093: .getSQLException(ErrorCode.SUBQUERY_IS_NOT_SINGLE_COLUMN);
094: }
095: // Can not optimize IN(SELECT...): the data may change
096: // However, could transform to an inner join
097: return this ;
098: }
099:
100: public void setEvaluatable(TableFilter tableFilter, boolean b) {
101: left.setEvaluatable(tableFilter, b);
102: query.setEvaluatable(tableFilter, b);
103: }
104:
105: public String getSQL() {
106: StringBuffer buff = new StringBuffer("(");
107: buff.append(left.getSQL());
108: buff.append(" IN(");
109: buff.append(query.getPlanSQL());
110: buff.append("))");
111: return buff.toString();
112: }
113:
114: public void updateAggregate(Session session) {
115: // TODO exists: is it allowed that the subquery contains aggregates?
116: // probably not
117: // select id from test group by id having 1 in (select * from test2
118: // where id=count(test.id))
119: }
120:
121: public boolean isEverything(ExpressionVisitor visitor) {
122: return left.isEverything(visitor)
123: && query.isEverything(visitor);
124: }
125:
126: public int getCost() {
127: return left.getCost() + 10 + (int) (10 * query.getCost());
128: }
129:
130: public Expression optimizeInJoin(Session session, Select select)
131: throws SQLException {
132: if (all || compareType != Comparison.EQUAL) {
133: return this ;
134: }
135: if (!query.isEverything(ExpressionVisitor.EVALUATABLE)) {
136: return this ;
137: }
138: String alias = query.getFirstColumnAlias(session);
139: query.setDistinct(true);
140: if (alias == null) {
141: return this ;
142: }
143: TableView view = TableView.createTempView(session, session
144: .getUser(), query);
145: TableFilter filter = new TableFilter(session, view, view
146: .getName(), false, select);
147: select.addTableFilter(filter, true);
148: ExpressionColumn column = new ExpressionColumn(session
149: .getDatabase(), null, view.getName(), alias);
150: Comparison on = new Comparison(session, Comparison.EQUAL, left,
151: column);
152: on.mapColumns(filter, 0);
153: filter.addFilterCondition(on, true);
154: return ValueExpression.get(ValueBoolean.get(true));
155: }
156:
157: }
|