Fix OVERLAPS operator Refactor StandardConvertletTable, creating methods to help create calls to common operators.
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/c94a080b Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/c94a080b Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/c94a080b Branch: refs/heads/master Commit: c94a080b3520a568e5452cc33ca3a443cf2f50ab Parents: 39c22f0 Author: Julian Hyde <[email protected]> Authored: Wed Apr 19 14:46:05 2017 -0700 Committer: Julian Hyde <[email protected]> Committed: Wed Apr 26 14:21:11 2017 -0700 ---------------------------------------------------------------------- .../sql2rel/StandardConvertletTable.java | 215 ++++++++----------- .../main/java/org/apache/calcite/util/Bug.java | 7 - .../calcite/sql/test/SqlOperatorBaseTest.java | 134 ++++++++---- 3 files changed, 188 insertions(+), 168 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/c94a080b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java index 5523c81..90f1ffc 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java @@ -361,6 +361,51 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { //~ Methods ---------------------------------------------------------------- + private RexNode or(RexBuilder rexBuilder, RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.OR, a0, a1); + } + + private RexNode ge(RexBuilder rexBuilder, RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, a0, + a1); + } + + private RexNode le(RexBuilder rexBuilder, RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN_OR_EQUAL, a0, a1); + } + + private RexNode and(RexBuilder rexBuilder, RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.AND, a0, a1); + } + + private static RexNode divideInt(RexBuilder rexBuilder, RexNode a0, + RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.DIVIDE_INTEGER, a0, a1); + } + + private RexNode plus(RexBuilder rexBuilder, RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.PLUS, a0, a1); + } + + private RexNode minus(RexBuilder rexBuilder, RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.MINUS, a0, a1); + } + + private static RexNode multiply(RexBuilder rexBuilder, RexNode a0, + RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY, a0, a1); + } + + private RexNode case_(RexBuilder rexBuilder, RexNode... args) { + return rexBuilder.makeCall(SqlStdOperatorTable.CASE, args); + } + + // SqlNode helpers + + private SqlCall plus(SqlParserPos pos, SqlNode a0, SqlNode a1) { + return SqlStdOperatorTable.PLUS.createCall(pos, a0, a1); + } + /** * Converts a CASE expression. */ @@ -545,45 +590,25 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { interval.getIntervalQualifier().getStartUnit().multiplier; RexNode rexInterval = cx.convertExpression(literal); - RexNode res; - final RexBuilder rexBuilder = cx.getRexBuilder(); RexNode zero = rexBuilder.makeExactLiteral(BigDecimal.valueOf(0)); - RexNode cond = - rexBuilder.makeCall( - SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, - rexInterval, - zero); + RexNode cond = ge(rexBuilder, rexInterval, zero); RexNode pad = rexBuilder.makeExactLiteral(val.subtract(BigDecimal.ONE)); RexNode cast = rexBuilder.makeReinterpretCast( rexInterval.getType(), pad, rexBuilder.makeLiteral(false)); - SqlOperator op = - floor ? SqlStdOperatorTable.MINUS - : SqlStdOperatorTable.PLUS; - RexNode sum = rexBuilder.makeCall(op, rexInterval, cast); - - RexNode kase = - floor - ? rexBuilder.makeCall(SqlStdOperatorTable.CASE, - cond, rexInterval, sum) - : rexBuilder.makeCall(SqlStdOperatorTable.CASE, - cond, sum, rexInterval); + RexNode sum = floor + ? minus(rexBuilder, rexInterval, cast) + : plus(rexBuilder, rexInterval, cast); + + RexNode kase = floor + ? case_(rexBuilder, rexInterval, cond, sum) + : case_(rexBuilder, sum, cond, rexInterval); RexNode factor = rexBuilder.makeExactLiteral(val); - RexNode div = - rexBuilder.makeCall( - SqlStdOperatorTable.DIVIDE_INTEGER, - kase, - factor); - RexNode mult = - rexBuilder.makeCall( - SqlStdOperatorTable.MULTIPLY, - div, - factor); - res = mult; - return res; + RexNode div = divideInt(rexBuilder, kase, factor); + return multiply(rexBuilder, div, factor); } // normal floor, ceil function @@ -667,10 +692,10 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { case DATE: res = rexBuilder.makeCall(resType, SqlStdOperatorTable.EXTRACT_DATE, ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.MONTH), res)); - res = rexBuilder.makeCall(SqlStdOperatorTable.MINUS, res, - rexBuilder.makeExactLiteral(BigDecimal.ONE)); + res = + minus(rexBuilder, res, rexBuilder.makeExactLiteral(BigDecimal.ONE)); res = divide(rexBuilder, res, unit.multiplier); - return rexBuilder.makeCall(SqlStdOperatorTable.PLUS, res, + return plus(rexBuilder, res, rexBuilder.makeExactLiteral(BigDecimal.ONE)); } break; @@ -731,13 +756,11 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { res = mod(rexBuilder, resType, res, getFactor(unit)); if (unit == TimeUnit.QUARTER) { - res = rexBuilder.makeCall(SqlStdOperatorTable.MINUS, res, - rexBuilder.makeExactLiteral(BigDecimal.ONE)); + res = minus(rexBuilder, res, rexBuilder.makeExactLiteral(BigDecimal.ONE)); } res = divide(rexBuilder, res, unit.multiplier); if (unit == TimeUnit.QUARTER) { - res = rexBuilder.makeCall(SqlStdOperatorTable.PLUS, res, - rexBuilder.makeExactLiteral(BigDecimal.ONE)); + res = plus(rexBuilder, res, rexBuilder.makeExactLiteral(BigDecimal.ONE)); } return res; } @@ -787,14 +810,13 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { try { final BigDecimal reciprocal = BigDecimal.ONE.divide(val, RoundingMode.UNNECESSARY); - return rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY, res, + return multiply(rexBuilder, res, rexBuilder.makeExactLiteral(reciprocal)); } catch (ArithmeticException e) { // ignore - reciprocal is not an integer } } - return rexBuilder.makeCall(SqlStdOperatorTable.DIVIDE_INTEGER, res, - rexBuilder.makeExactLiteral(val)); + return divideInt(rexBuilder, res, rexBuilder.makeExactLiteral(val)); } public RexNode convertDatetimeMinus( @@ -1091,19 +1113,9 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { final RexNode z = list.get(SqlBetweenOperator.UPPER_OPERAND); final RexBuilder rexBuilder = cx.getRexBuilder(); - RexNode ge1 = - rexBuilder.makeCall( - SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, x, y); - RexNode le1 = - rexBuilder.makeCall( - SqlStdOperatorTable.LESS_THAN_OR_EQUAL, - x, - z); - RexNode and1 = - rexBuilder.makeCall( - SqlStdOperatorTable.AND, - ge1, - le1); + RexNode ge1 = ge(rexBuilder, x, y); + RexNode le1 = le(rexBuilder, x, z); + RexNode and1 = and(rexBuilder, ge1, le1); RexNode res; final SqlBetweenOperator.Flag symmetric = op.flag; @@ -1112,26 +1124,10 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { res = and1; break; case SYMMETRIC: - RexNode ge2 = - rexBuilder.makeCall( - SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, - x, - z); - RexNode le2 = - rexBuilder.makeCall( - SqlStdOperatorTable.LESS_THAN_OR_EQUAL, - x, - y); - RexNode and2 = - rexBuilder.makeCall( - SqlStdOperatorTable.AND, - ge2, - le2); - res = - rexBuilder.makeCall( - SqlStdOperatorTable.OR, - and1, - and2); + RexNode ge2 = ge(rexBuilder, x, z); + RexNode le2 = le(rexBuilder, x, y); + RexNode and2 = and(rexBuilder, ge2, le2); + res = or(rexBuilder, and1, and2); break; default: throw Util.unexpected(symmetric); @@ -1198,60 +1194,33 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { // intervals overlaps by: ~(t1 < t2 or t3 < t0) final SqlNode[] operands = ((SqlBasicCall) call).getOperands(); assert operands.length == 4; - if (operands[1] instanceof SqlIntervalLiteral) { + final SqlParserPos pos = call.getParserPosition(); + final RelDataType t1 = cx.getValidator().getValidatedNodeType(operands[1]); + if (SqlTypeUtil.isInterval(t1)) { // make t1 = t0 + t1 when t1 is an interval. - SqlOperator op1 = SqlStdOperatorTable.PLUS; - SqlNode[] second = new SqlNode[2]; - second[0] = operands[0]; - second[1] = operands[1]; - operands[1] = - op1.createCall( - call.getParserPosition(), - second); + operands[1] = plus(pos, operands[0], operands[1]); } - if (operands[3] instanceof SqlIntervalLiteral) { + final RelDataType t3 = cx.getValidator().getValidatedNodeType(operands[3]); + if (SqlTypeUtil.isInterval(t3)) { // make t3 = t2 + t3 when t3 is an interval. - SqlOperator op1 = SqlStdOperatorTable.PLUS; - SqlNode[] four = new SqlNode[2]; - four[0] = operands[2]; - four[1] = operands[3]; - operands[3] = - op1.createCall( - call.getParserPosition(), - four); + operands[3] = plus(pos, operands[2], operands[3]); } - // This captures t1 >= t2 - SqlOperator op1 = SqlStdOperatorTable.GREATER_THAN_OR_EQUAL; - SqlNode[] left = new SqlNode[2]; - left[0] = operands[1]; - left[1] = operands[2]; - SqlCall call1 = - op1.createCall( - call.getParserPosition(), - left); - - // This captures t3 >= t0 - SqlOperator op2 = SqlStdOperatorTable.GREATER_THAN_OR_EQUAL; - SqlNode[] right = new SqlNode[2]; - right[0] = operands[3]; - right[1] = operands[0]; - SqlCall call2 = - op2.createCall( - call.getParserPosition(), - right); - - // This captures t1 >= t2 and t3 >= t0 - SqlOperator and = SqlStdOperatorTable.AND; - SqlNode[] overlaps = new SqlNode[2]; - overlaps[0] = call1; - overlaps[1] = call2; - SqlCall call3 = - and.createCall( - call.getParserPosition(), - overlaps); - - return cx.convertExpression(call3); + final RexNode r0 = cx.convertExpression(operands[0]); + final RexNode r1 = cx.convertExpression(operands[1]); + final RexNode r2 = cx.convertExpression(operands[2]); + final RexNode r3 = cx.convertExpression(operands[3]); + + // Sort end points into start and end, such that (s0 <= e0) and (s1 <= e1). + final RexBuilder rexBuilder = cx.getRexBuilder(); + final RexNode s0 = case_(rexBuilder, le(rexBuilder, r0, r1), r0, r1); + final RexNode e0 = case_(rexBuilder, le(rexBuilder, r0, r1), r1, r0); + final RexNode s1 = case_(rexBuilder, le(rexBuilder, r2, r3), r2, r3); + final RexNode e1 = case_(rexBuilder, le(rexBuilder, r2, r3), r3, r2); + // (e0 >= s1) AND (e1 >= s0) + return and(rexBuilder, + ge(rexBuilder, e0, s1), + ge(rexBuilder, e1, s0)); } /** @@ -1484,7 +1453,7 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { final TimeUnit unit = unitLiteral.symbolValue(TimeUnit.class); return rexBuilder.makeCall(SqlStdOperatorTable.DATETIME_PLUS, cx.convertExpression(call.operand(2)), - rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY, + multiply(rexBuilder, rexBuilder.makeIntervalLiteral(unit.multiplier, new SqlIntervalQualifier(unit, null, unitLiteral.getParserPosition())), http://git-wip-us.apache.org/repos/asf/calcite/blob/c94a080b/core/src/main/java/org/apache/calcite/util/Bug.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/util/Bug.java b/core/src/main/java/org/apache/calcite/util/Bug.java index 1adc2fe..e5d7096 100644 --- a/core/src/main/java/org/apache/calcite/util/Bug.java +++ b/core/src/main/java/org/apache/calcite/util/Bug.java @@ -108,13 +108,6 @@ public abstract class Bug { public static final boolean FRG78_FIXED = false; /** - * Whether <a href="http://issues.eigenbase.org/browse/FRG-187";>issue - * FRG-187: FarragoAutoVmOperatorTest.testOverlapsOperator fails</a> is - * fixed. - */ - public static final boolean FRG187_FIXED = false; - - /** * Whether <a href="http://issues.eigenbase.org/browse/FRG-189";>issue * FRG-189: FarragoAutoVmOperatorTest.testSelect fails</a> is fixed. */ http://git-wip-us.apache.org/repos/asf/calcite/blob/c94a080b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index 2ba92ac..ff3c0e2 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -2313,46 +2313,80 @@ public abstract class SqlOperatorBaseTest { @Test public void testOverlapsOperator() { tester.setFor(SqlStdOperatorTable.OVERLAPS, VM_EXPAND); - if (Bug.FRG187_FIXED) { - tester.checkBoolean( - "(date '1-2-3', date '1-2-3') overlaps (date '1-2-3', interval '1' year)", - Boolean.TRUE); - tester.checkBoolean( - "(date '1-2-3', date '1-2-3') overlaps (date '4-5-6', interval '1' year)", - Boolean.FALSE); - tester.checkBoolean( - "(date '1-2-3', date '4-5-6') overlaps (date '2-2-3', date '3-4-5')", - Boolean.TRUE); - tester.checkNull( - "(cast(null as date), date '1-2-3') overlaps (date '1-2-3', interval '1' year)"); - tester.checkNull( - "(date '1-2-3', date '1-2-3') overlaps (date '1-2-3', cast(null as date))"); + tester.checkBoolean( + "(date '1-2-3', date '1-2-3') overlaps (date '1-2-3', interval '1' year)", + Boolean.TRUE); + tester.checkBoolean( + "(date '1-2-3', date '1-2-3') overlaps (date '4-5-6', interval '1' year)", + Boolean.FALSE); + tester.checkBoolean( + "(date '1-2-3', date '4-5-6') overlaps (date '2-2-3', date '3-4-5')", + Boolean.TRUE); + tester.checkNull( + "(cast(null as date), date '1-2-3') overlaps (date '1-2-3', interval '1' year)"); + tester.checkNull( + "(date '1-2-3', date '1-2-3') overlaps (date '1-2-3', cast(null as date))"); - tester.checkBoolean( - "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', time '1:2:3')", - Boolean.TRUE); - tester.checkBoolean( - "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', time '1:2:2')", - Boolean.FALSE); - tester.checkBoolean( - "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', interval '2' hour)", - Boolean.TRUE); - tester.checkNull( - "(time '1:2:3', cast(null as time)) overlaps (time '23:59:59', time '1:2:3')"); - tester.checkNull( - "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', cast(null as interval hour))"); + tester.checkBoolean( + "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', time '1:2:3')", + Boolean.TRUE); + tester.checkBoolean( + "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', time '1:2:2')", + Boolean.TRUE); + tester.checkBoolean( + "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', interval '2' hour)", + Boolean.FALSE); + tester.checkNull( + "(time '1:2:3', cast(null as time)) overlaps (time '23:59:59', time '1:2:3')"); + tester.checkNull( + "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', cast(null as interval hour))"); - tester.checkBoolean( - "(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (timestamp '1-2-3 4:5:6', interval '1 2:3:4.5' day to second)", - Boolean.TRUE); - tester.checkBoolean( - "(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (timestamp '2-2-3 4:5:6', interval '1 2:3:4.5' day to second)", - Boolean.FALSE); - tester.checkNull( - "(timestamp '1-2-3 4:5:6', cast(null as interval day) ) overlaps (timestamp '1-2-3 4:5:6', interval '1 2:3:4.5' day to second)"); - tester.checkNull( - "(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (cast(null as timestamp), interval '1 2:3:4.5' day to second)"); - } + tester.checkBoolean( + "(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (timestamp '1-2-3 4:5:6', interval '1 2:3:4.5' day to second)", + Boolean.TRUE); + tester.checkBoolean( + "(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (timestamp '2-2-3 4:5:6', interval '1 2:3:4.5' day to second)", + Boolean.FALSE); + tester.checkNull( + "(timestamp '1-2-3 4:5:6', cast(null as interval day) ) overlaps (timestamp '1-2-3 4:5:6', interval '1 2:3:4.5' day to second)"); + tester.checkNull( + "(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (cast(null as timestamp), interval '1 2:3:4.5' day to second)"); + } + + @Test public void testOverlapsEtc() { + String[] times = { + "TIME '01:00:00'", + "TIME '02:00:00'", + "TIME '03:00:00'", + "TIME '04:00:00'", + }; + String[] dates = { + "DATE '1970-01-01'", + "DATE '1970-02-01'", + "DATE '1970-03-01'", + "DATE '1970-04-01'", + }; + String[] timestamps = { + "TIMESTAMP '1970-01-01 00:00:00'", + "TIMESTAMP '1970-02-01 00:00:00'", + "TIMESTAMP '1970-03-01 00:00:00'", + "TIMESTAMP '1970-04-01 00:00:00'", + }; + checkOverlaps(new OverlapChecker(times)); + checkOverlaps(new OverlapChecker(dates)); + checkOverlaps(new OverlapChecker(timestamps)); + } + + private void checkOverlaps(OverlapChecker c) { + c.isFalse("($0,$1) OVERLAPS ($2,$3)"); + c.isTrue("($0,$1) OVERLAPS ($1,$2)"); + c.isTrue("($0,$2) OVERLAPS ($1,$3)"); + c.isTrue("($0,$2) OVERLAPS ($3,$1)"); + c.isTrue("($2,$0) OVERLAPS ($3,$1)"); + c.isFalse("($3,$2) OVERLAPS ($1,$0)"); + c.isTrue("($2,$3) OVERLAPS ($0,$2)"); + c.isTrue("($2,$3) OVERLAPS ($2,$0)"); + c.isTrue("($3,$2) OVERLAPS ($2,$0)"); } @Test public void testLessThanOperator() { @@ -6743,6 +6777,30 @@ public abstract class SqlOperatorBaseTest { this.values.add(new ValueType(type, null)); } } + + /** Runs an OVERLAPS test with a given set of literal values. */ + class OverlapChecker { + final String[] values; + + OverlapChecker(String... values) { + this.values = values; + } + + public void isTrue(String s) { + tester.checkBoolean(sub(s), Boolean.TRUE); + } + + public void isFalse(String s) { + tester.checkBoolean(sub(s), Boolean.FALSE); + } + + private String sub(String s) { + return s.replace("$0", values[0]) + .replace("$1", values[1]) + .replace("$2", values[2]) + .replace("$3", values[3]); + } + } } // End SqlOperatorBaseTest.java
