1 | /* |
2 | * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0, |
3 | * and the EPL 1.0 (http://h2database.com/html/license.html). |
4 | * Initial Developer: H2 Group |
5 | */ |
6 | package org.h2.expression; |
7 | |
8 | import java.util.Arrays; |
9 | import org.h2.engine.Database; |
10 | import org.h2.engine.Session; |
11 | import org.h2.engine.SysProperties; |
12 | import org.h2.index.IndexCondition; |
13 | import org.h2.message.DbException; |
14 | import org.h2.table.ColumnResolver; |
15 | import org.h2.table.TableFilter; |
16 | import org.h2.util.New; |
17 | import org.h2.value.Value; |
18 | import org.h2.value.ValueBoolean; |
19 | import org.h2.value.ValueGeometry; |
20 | import org.h2.value.ValueNull; |
21 | |
22 | /** |
23 | * Example comparison expressions are ID=1, NAME=NAME, NAME IS NULL. |
24 | * |
25 | * @author Thomas Mueller |
26 | * @author Noel Grandin |
27 | * @author Nicolas Fortin, Atelier SIG, IRSTV FR CNRS 24888 |
28 | */ |
29 | public class Comparison extends Condition { |
30 | |
31 | /** |
32 | * This is a flag meaning the comparison is null safe (meaning never returns |
33 | * NULL even if one operand is NULL). Only EQUAL and NOT_EQUAL are supported |
34 | * currently. |
35 | */ |
36 | public static final int NULL_SAFE = 16; |
37 | |
38 | /** |
39 | * The comparison type meaning = as in ID=1. |
40 | */ |
41 | public static final int EQUAL = 0; |
42 | |
43 | /** |
44 | * The comparison type meaning ID IS 1 (ID IS NOT DISTINCT FROM 1). |
45 | */ |
46 | public static final int EQUAL_NULL_SAFE = EQUAL | NULL_SAFE; |
47 | |
48 | /** |
49 | * The comparison type meaning >= as in ID>=1. |
50 | */ |
51 | public static final int BIGGER_EQUAL = 1; |
52 | |
53 | /** |
54 | * The comparison type meaning > as in ID>1. |
55 | */ |
56 | public static final int BIGGER = 2; |
57 | |
58 | /** |
59 | * The comparison type meaning <= as in ID<=1. |
60 | */ |
61 | public static final int SMALLER_EQUAL = 3; |
62 | |
63 | /** |
64 | * The comparison type meaning < as in ID<1. |
65 | */ |
66 | public static final int SMALLER = 4; |
67 | |
68 | /** |
69 | * The comparison type meaning <> as in ID<>1. |
70 | */ |
71 | public static final int NOT_EQUAL = 5; |
72 | |
73 | /** |
74 | * The comparison type meaning ID IS NOT 1 (ID IS DISTINCT FROM 1). |
75 | */ |
76 | public static final int NOT_EQUAL_NULL_SAFE = NOT_EQUAL | NULL_SAFE; |
77 | |
78 | /** |
79 | * The comparison type meaning IS NULL as in NAME IS NULL. |
80 | */ |
81 | public static final int IS_NULL = 6; |
82 | |
83 | /** |
84 | * The comparison type meaning IS NOT NULL as in NAME IS NOT NULL. |
85 | */ |
86 | public static final int IS_NOT_NULL = 7; |
87 | |
88 | /** |
89 | * This is a pseudo comparison type that is only used for index conditions. |
90 | * It means the comparison will always yield FALSE. Example: 1=0. |
91 | */ |
92 | public static final int FALSE = 8; |
93 | |
94 | /** |
95 | * This is a pseudo comparison type that is only used for index conditions. |
96 | * It means equals any value of a list. Example: IN(1, 2, 3). |
97 | */ |
98 | public static final int IN_LIST = 9; |
99 | |
100 | /** |
101 | * This is a pseudo comparison type that is only used for index conditions. |
102 | * It means equals any value of a list. Example: IN(SELECT ...). |
103 | */ |
104 | public static final int IN_QUERY = 10; |
105 | |
106 | /** |
107 | * This is a comparison type that is only used for spatial index |
108 | * conditions (operator "&&"). |
109 | */ |
110 | public static final int SPATIAL_INTERSECTS = 11; |
111 | |
112 | private final Database database; |
113 | private int compareType; |
114 | private Expression left; |
115 | private Expression right; |
116 | |
117 | public Comparison(Session session, int compareType, Expression left, |
118 | Expression right) { |
119 | this.database = session.getDatabase(); |
120 | this.left = left; |
121 | this.right = right; |
122 | this.compareType = compareType; |
123 | } |
124 | |
125 | @Override |
126 | public String getSQL() { |
127 | String sql; |
128 | switch (compareType) { |
129 | case IS_NULL: |
130 | sql = left.getSQL() + " IS NULL"; |
131 | break; |
132 | case IS_NOT_NULL: |
133 | sql = left.getSQL() + " IS NOT NULL"; |
134 | break; |
135 | case SPATIAL_INTERSECTS: |
136 | sql = "INTERSECTS(" + left.getSQL() + ", " + right.getSQL() + ")"; |
137 | break; |
138 | default: |
139 | sql = left.getSQL() + " " + getCompareOperator(compareType) + |
140 | " " + right.getSQL(); |
141 | } |
142 | return "(" + sql + ")"; |
143 | } |
144 | |
145 | /** |
146 | * Get the comparison operator string ("=", ">",...). |
147 | * |
148 | * @param compareType the compare type |
149 | * @return the string |
150 | */ |
151 | static String getCompareOperator(int compareType) { |
152 | switch (compareType) { |
153 | case EQUAL: |
154 | return "="; |
155 | case EQUAL_NULL_SAFE: |
156 | return "IS"; |
157 | case BIGGER_EQUAL: |
158 | return ">="; |
159 | case BIGGER: |
160 | return ">"; |
161 | case SMALLER_EQUAL: |
162 | return "<="; |
163 | case SMALLER: |
164 | return "<"; |
165 | case NOT_EQUAL: |
166 | return "<>"; |
167 | case NOT_EQUAL_NULL_SAFE: |
168 | return "IS NOT"; |
169 | case SPATIAL_INTERSECTS: |
170 | return "&&"; |
171 | default: |
172 | throw DbException.throwInternalError("compareType=" + compareType); |
173 | } |
174 | } |
175 | |
176 | @Override |
177 | public Expression optimize(Session session) { |
178 | left = left.optimize(session); |
179 | if (right != null) { |
180 | right = right.optimize(session); |
181 | if (right instanceof ExpressionColumn) { |
182 | if (left.isConstant() || left instanceof Parameter) { |
183 | Expression temp = left; |
184 | left = right; |
185 | right = temp; |
186 | compareType = getReversedCompareType(compareType); |
187 | } |
188 | } |
189 | if (left instanceof ExpressionColumn) { |
190 | if (right.isConstant()) { |
191 | Value r = right.getValue(session); |
192 | if (r == ValueNull.INSTANCE) { |
193 | if ((compareType & NULL_SAFE) == 0) { |
194 | return ValueExpression.getNull(); |
195 | } |
196 | } |
197 | } else if (right instanceof Parameter) { |
198 | ((Parameter) right).setColumn( |
199 | ((ExpressionColumn) left).getColumn()); |
200 | } |
201 | } |
202 | } |
203 | if (compareType == IS_NULL || compareType == IS_NOT_NULL) { |
204 | if (left.isConstant()) { |
205 | return ValueExpression.get(getValue(session)); |
206 | } |
207 | } else { |
208 | if (SysProperties.CHECK && (left == null || right == null)) { |
209 | DbException.throwInternalError(); |
210 | } |
211 | if (left == ValueExpression.getNull() || |
212 | right == ValueExpression.getNull()) { |
213 | // TODO NULL handling: maybe issue a warning when comparing with |
214 | // a NULL constants |
215 | if ((compareType & NULL_SAFE) == 0) { |
216 | return ValueExpression.getNull(); |
217 | } |
218 | } |
219 | if (left.isConstant() && right.isConstant()) { |
220 | return ValueExpression.get(getValue(session)); |
221 | } |
222 | } |
223 | return this; |
224 | } |
225 | |
226 | @Override |
227 | public Value getValue(Session session) { |
228 | Value l = left.getValue(session); |
229 | if (right == null) { |
230 | boolean result; |
231 | switch (compareType) { |
232 | case IS_NULL: |
233 | result = l == ValueNull.INSTANCE; |
234 | break; |
235 | case IS_NOT_NULL: |
236 | result = !(l == ValueNull.INSTANCE); |
237 | break; |
238 | default: |
239 | throw DbException.throwInternalError("type=" + compareType); |
240 | } |
241 | return ValueBoolean.get(result); |
242 | } |
243 | if (l == ValueNull.INSTANCE) { |
244 | if ((compareType & NULL_SAFE) == 0) { |
245 | return ValueNull.INSTANCE; |
246 | } |
247 | } |
248 | Value r = right.getValue(session); |
249 | if (r == ValueNull.INSTANCE) { |
250 | if ((compareType & NULL_SAFE) == 0) { |
251 | return ValueNull.INSTANCE; |
252 | } |
253 | } |
254 | int dataType = Value.getHigherOrder(left.getType(), right.getType()); |
255 | l = l.convertTo(dataType); |
256 | r = r.convertTo(dataType); |
257 | boolean result = compareNotNull(database, l, r, compareType); |
258 | return ValueBoolean.get(result); |
259 | } |
260 | |
261 | /** |
262 | * Compare two values, given the values are not NULL. |
263 | * |
264 | * @param database the database |
265 | * @param l the first value |
266 | * @param r the second value |
267 | * @param compareType the compare type |
268 | * @return true if the comparison indicated by the comparison type evaluates |
269 | * to true |
270 | */ |
271 | static boolean compareNotNull(Database database, Value l, Value r, |
272 | int compareType) { |
273 | boolean result; |
274 | switch (compareType) { |
275 | case EQUAL: |
276 | case EQUAL_NULL_SAFE: |
277 | result = database.areEqual(l, r); |
278 | break; |
279 | case NOT_EQUAL: |
280 | case NOT_EQUAL_NULL_SAFE: |
281 | result = !database.areEqual(l, r); |
282 | break; |
283 | case BIGGER_EQUAL: |
284 | result = database.compare(l, r) >= 0; |
285 | break; |
286 | case BIGGER: |
287 | result = database.compare(l, r) > 0; |
288 | break; |
289 | case SMALLER_EQUAL: |
290 | result = database.compare(l, r) <= 0; |
291 | break; |
292 | case SMALLER: |
293 | result = database.compare(l, r) < 0; |
294 | break; |
295 | case SPATIAL_INTERSECTS: { |
296 | ValueGeometry lg = (ValueGeometry) l.convertTo(Value.GEOMETRY); |
297 | ValueGeometry rg = (ValueGeometry) r.convertTo(Value.GEOMETRY); |
298 | result = lg.intersectsBoundingBox(rg); |
299 | break; |
300 | } |
301 | default: |
302 | throw DbException.throwInternalError("type=" + compareType); |
303 | } |
304 | return result; |
305 | } |
306 | |
307 | private int getReversedCompareType(int type) { |
308 | switch (compareType) { |
309 | case EQUAL: |
310 | case EQUAL_NULL_SAFE: |
311 | case NOT_EQUAL: |
312 | case NOT_EQUAL_NULL_SAFE: |
313 | case SPATIAL_INTERSECTS: |
314 | return type; |
315 | case BIGGER_EQUAL: |
316 | return SMALLER_EQUAL; |
317 | case BIGGER: |
318 | return SMALLER; |
319 | case SMALLER_EQUAL: |
320 | return BIGGER_EQUAL; |
321 | case SMALLER: |
322 | return BIGGER; |
323 | default: |
324 | throw DbException.throwInternalError("type=" + compareType); |
325 | } |
326 | } |
327 | |
328 | @Override |
329 | public Expression getNotIfPossible(Session session) { |
330 | if (compareType == SPATIAL_INTERSECTS) { |
331 | return null; |
332 | } |
333 | int type = getNotCompareType(); |
334 | return new Comparison(session, type, left, right); |
335 | } |
336 | |
337 | private int getNotCompareType() { |
338 | switch (compareType) { |
339 | case EQUAL: |
340 | return NOT_EQUAL; |
341 | case EQUAL_NULL_SAFE: |
342 | return NOT_EQUAL_NULL_SAFE; |
343 | case NOT_EQUAL: |
344 | return EQUAL; |
345 | case NOT_EQUAL_NULL_SAFE: |
346 | return EQUAL_NULL_SAFE; |
347 | case BIGGER_EQUAL: |
348 | return SMALLER; |
349 | case BIGGER: |
350 | return SMALLER_EQUAL; |
351 | case SMALLER_EQUAL: |
352 | return BIGGER; |
353 | case SMALLER: |
354 | return BIGGER_EQUAL; |
355 | case IS_NULL: |
356 | return IS_NOT_NULL; |
357 | case IS_NOT_NULL: |
358 | return IS_NULL; |
359 | default: |
360 | throw DbException.throwInternalError("type=" + compareType); |
361 | } |
362 | } |
363 | |
364 | @Override |
365 | public void createIndexConditions(Session session, TableFilter filter) { |
366 | if (!filter.getTable().isQueryComparable()) { |
367 | return; |
368 | } |
369 | ExpressionColumn l = null; |
370 | if (left instanceof ExpressionColumn) { |
371 | l = (ExpressionColumn) left; |
372 | if (filter != l.getTableFilter()) { |
373 | l = null; |
374 | } |
375 | } |
376 | if (right == null) { |
377 | if (l != null) { |
378 | switch (compareType) { |
379 | case IS_NULL: |
380 | if (session.getDatabase().getSettings().optimizeIsNull) { |
381 | filter.addIndexCondition( |
382 | IndexCondition.get( |
383 | Comparison.EQUAL_NULL_SAFE, l, |
384 | ValueExpression.getNull())); |
385 | } |
386 | } |
387 | } |
388 | return; |
389 | } |
390 | ExpressionColumn r = null; |
391 | if (right instanceof ExpressionColumn) { |
392 | r = (ExpressionColumn) right; |
393 | if (filter != r.getTableFilter()) { |
394 | r = null; |
395 | } |
396 | } |
397 | // one side must be from the current filter |
398 | if (l == null && r == null) { |
399 | return; |
400 | } |
401 | if (l != null && r != null) { |
402 | return; |
403 | } |
404 | if (l == null) { |
405 | ExpressionVisitor visitor = |
406 | ExpressionVisitor.getNotFromResolverVisitor(filter); |
407 | if (!left.isEverything(visitor)) { |
408 | return; |
409 | } |
410 | } else if (r == null) { |
411 | ExpressionVisitor visitor = |
412 | ExpressionVisitor.getNotFromResolverVisitor(filter); |
413 | if (!right.isEverything(visitor)) { |
414 | return; |
415 | } |
416 | } else { |
417 | // if both sides are part of the same filter, it can't be used for |
418 | // index lookup |
419 | return; |
420 | } |
421 | boolean addIndex; |
422 | switch (compareType) { |
423 | case NOT_EQUAL: |
424 | case NOT_EQUAL_NULL_SAFE: |
425 | addIndex = false; |
426 | break; |
427 | case EQUAL: |
428 | case EQUAL_NULL_SAFE: |
429 | case BIGGER: |
430 | case BIGGER_EQUAL: |
431 | case SMALLER_EQUAL: |
432 | case SMALLER: |
433 | case SPATIAL_INTERSECTS: |
434 | addIndex = true; |
435 | break; |
436 | default: |
437 | throw DbException.throwInternalError("type=" + compareType); |
438 | } |
439 | if (addIndex) { |
440 | if (l != null) { |
441 | filter.addIndexCondition( |
442 | IndexCondition.get(compareType, l, right)); |
443 | } else if (r != null) { |
444 | int compareRev = getReversedCompareType(compareType); |
445 | filter.addIndexCondition( |
446 | IndexCondition.get(compareRev, r, left)); |
447 | } |
448 | } |
449 | } |
450 | |
451 | @Override |
452 | public void setEvaluatable(TableFilter tableFilter, boolean b) { |
453 | left.setEvaluatable(tableFilter, b); |
454 | if (right != null) { |
455 | right.setEvaluatable(tableFilter, b); |
456 | } |
457 | } |
458 | |
459 | @Override |
460 | public void updateAggregate(Session session) { |
461 | left.updateAggregate(session); |
462 | if (right != null) { |
463 | right.updateAggregate(session); |
464 | } |
465 | } |
466 | |
467 | @Override |
468 | public void addFilterConditions(TableFilter filter, boolean outerJoin) { |
469 | if (compareType == IS_NULL && outerJoin) { |
470 | // can not optimize: |
471 | // select * from test t1 left join test t2 on t1.id = t2.id |
472 | // where t2.id is null |
473 | // to |
474 | // select * from test t1 left join test t2 |
475 | // on t1.id = t2.id and t2.id is null |
476 | return; |
477 | } |
478 | super.addFilterConditions(filter, outerJoin); |
479 | } |
480 | |
481 | @Override |
482 | public void mapColumns(ColumnResolver resolver, int level) { |
483 | left.mapColumns(resolver, level); |
484 | if (right != null) { |
485 | right.mapColumns(resolver, level); |
486 | } |
487 | } |
488 | |
489 | @Override |
490 | public boolean isEverything(ExpressionVisitor visitor) { |
491 | return left.isEverything(visitor) && |
492 | (right == null || right.isEverything(visitor)); |
493 | } |
494 | |
495 | @Override |
496 | public int getCost() { |
497 | return left.getCost() + (right == null ? 0 : right.getCost()) + 1; |
498 | } |
499 | |
500 | /** |
501 | * Get the other expression if this is an equals comparison and the other |
502 | * expression matches. |
503 | * |
504 | * @param match the expression that should match |
505 | * @return null if no match, the other expression if there is a match |
506 | */ |
507 | Expression getIfEquals(Expression match) { |
508 | if (compareType == EQUAL) { |
509 | String sql = match.getSQL(); |
510 | if (left.getSQL().equals(sql)) { |
511 | return right; |
512 | } else if (right.getSQL().equals(sql)) { |
513 | return left; |
514 | } |
515 | } |
516 | return null; |
517 | } |
518 | |
519 | /** |
520 | * Get an additional condition if possible. Example: given two conditions |
521 | * A=B AND B=C, the new condition A=C is returned. Given the two conditions |
522 | * A=1 OR A=2, the new condition A IN(1, 2) is returned. |
523 | * |
524 | * @param session the session |
525 | * @param other the second condition |
526 | * @param and true for AND, false for OR |
527 | * @return null or the third condition |
528 | */ |
529 | Expression getAdditional(Session session, Comparison other, boolean and) { |
530 | if (compareType == other.compareType && compareType == EQUAL) { |
531 | boolean lc = left.isConstant(); |
532 | boolean rc = right.isConstant(); |
533 | boolean l2c = other.left.isConstant(); |
534 | boolean r2c = other.right.isConstant(); |
535 | String l = left.getSQL(); |
536 | String l2 = other.left.getSQL(); |
537 | String r = right.getSQL(); |
538 | String r2 = other.right.getSQL(); |
539 | if (and) { |
540 | // a=b AND a=c |
541 | // must not compare constants. example: NOT(B=2 AND B=3) |
542 | if (!(rc && r2c) && l.equals(l2)) { |
543 | return new Comparison(session, EQUAL, right, other.right); |
544 | } else if (!(rc && l2c) && l.equals(r2)) { |
545 | return new Comparison(session, EQUAL, right, other.left); |
546 | } else if (!(lc && r2c) && r.equals(l2)) { |
547 | return new Comparison(session, EQUAL, left, other.right); |
548 | } else if (!(lc && l2c) && r.equals(r2)) { |
549 | return new Comparison(session, EQUAL, left, other.left); |
550 | } |
551 | } else { |
552 | // a=b OR a=c |
553 | Database db = session.getDatabase(); |
554 | if (rc && r2c && l.equals(l2)) { |
555 | return new ConditionIn(db, left, |
556 | New.arrayList(Arrays.asList(right, other.right))); |
557 | } else if (rc && l2c && l.equals(r2)) { |
558 | return new ConditionIn(db, left, |
559 | New.arrayList(Arrays.asList(right, other.left))); |
560 | } else if (lc && r2c && r.equals(l2)) { |
561 | return new ConditionIn(db, right, |
562 | New.arrayList(Arrays.asList(left, other.right))); |
563 | } else if (lc && l2c && r.equals(r2)) { |
564 | return new ConditionIn(db, right, |
565 | New.arrayList(Arrays.asList(left, other.left))); |
566 | } |
567 | } |
568 | } |
569 | return null; |
570 | } |
571 | |
572 | /** |
573 | * Get the left or the right sub-expression of this condition. |
574 | * |
575 | * @param getLeft true to get the left sub-expression, false to get the |
576 | * right sub-expression. |
577 | * @return the sub-expression |
578 | */ |
579 | public Expression getExpression(boolean getLeft) { |
580 | return getLeft ? this.left : right; |
581 | } |
582 | |
583 | } |