Examples with ComparisonExpression com.facebook.presto.sql.tree.ComparisonExpression used on opensource projects

Example 26 with ComparisonExpression

use of com.facebook.presto.sql.tree.ComparisonExpression in project presto by prestodb.

the class TestComparisonStatsCalculator method symbolToLiteralLessThanStats.

@Test
public void symbolToLiteralLessThanStats() {
    // Simple case
    assertCalculate(new ComparisonExpression(LESS_THAN, new SymbolReference("y"), new DoubleLiteral("2.5"))).outputRowsCount(// all rows minus nulls times range coverage (50%)
    250.0).variableStats(new VariableReferenceExpression(Optional.empty(), "y", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(10.0).lowValue(0.0).highValue(2.5).nullsFraction(0.0);
    });
    // Literal on the edge of symbol range (whole range included)
    assertCalculate(new ComparisonExpression(LESS_THAN, new SymbolReference("x"), new DoubleLiteral("10.0"))).outputRowsCount(// all rows minus nulls times range coverage (100%)
    750.0).variableStats(new VariableReferenceExpression(Optional.empty(), "x", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(40.0).lowValue(-10.0).highValue(10.0).nullsFraction(0.0);
    });
    // Literal on the edge of symbol range (whole range excluded)
    assertCalculate(new ComparisonExpression(LESS_THAN, new SymbolReference("x"), new DoubleLiteral("-10.0"))).outputRowsCount(// all rows minus nulls divided by NDV (one value from edge is included as approximation)
    18.75).variableStats(new VariableReferenceExpression(Optional.empty(), "x", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(1.0).lowValue(-10.0).highValue(-10.0).nullsFraction(0.0);
    });
    // Literal range out of symbol range
    assertCalculate(new ComparisonExpression(LESS_THAN, new SymbolReference("y"), new DoubleLiteral("-10.0"))).outputRowsCount(// all rows minus nulls times range coverage (0%)
    0.0).variableStats(new VariableReferenceExpression(Optional.empty(), "y", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(0.0).distinctValuesCount(0.0).emptyRange().nullsFraction(1.0);
    });
    // Literal in left open range
    assertCalculate(new ComparisonExpression(LESS_THAN, new SymbolReference("leftOpen"), new DoubleLiteral("0.0"))).outputRowsCount(// all rows minus nulls times range coverage (50% - heuristic)
    450.0).variableStats(new VariableReferenceExpression(Optional.empty(), "leftOpen", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(// (50% heuristic)
        25.0).lowValueUnknown().highValue(0.0).nullsFraction(0.0);
    });
    // Literal in right open range
    assertCalculate(new ComparisonExpression(LESS_THAN, new SymbolReference("rightOpen"), new DoubleLiteral("0.0"))).outputRowsCount(// all rows minus nulls times range coverage (25% - heuristic)
    225.0).variableStats(new VariableReferenceExpression(Optional.empty(), "rightOpen", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(// (25% heuristic)
        12.5).lowValue(-15.0).highValue(0.0).nullsFraction(0.0);
    });
    // Literal in unknown range
    assertCalculate(new ComparisonExpression(LESS_THAN, new SymbolReference("unknownRange"), new DoubleLiteral("0.0"))).outputRowsCount(// all rows minus nulls times range coverage (50% - heuristic)
    450.0).variableStats(new VariableReferenceExpression(Optional.empty(), "unknownRange", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(// (50% heuristic)
        25.0).lowValueUnknown().highValue(0.0).nullsFraction(0.0);
    });
    // Literal in empty range
    assertCalculate(new ComparisonExpression(LESS_THAN, new SymbolReference("emptyRange"), new DoubleLiteral("0.0"))).outputRowsCount(0.0).variableStats(new VariableReferenceExpression(Optional.empty(), "emptyRange", DOUBLE), equalTo(emptyRangeStats));
}

Example 27 with ComparisonExpression

use of com.facebook.presto.sql.tree.ComparisonExpression in project presto by prestodb.

the class TestComparisonStatsCalculator method symbolToLiteralNotEqualStats.

@Test
public void symbolToLiteralNotEqualStats() {
    // Simple case
    assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("y"), new DoubleLiteral("2.5"))).outputRowsCount(// all rows minus nulls multiplied by ((distinct values - 1) / distinct values)
    475.0).variableStats(new VariableReferenceExpression(Optional.empty(), "y", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(19.0).lowValue(0.0).highValue(5.0).nullsFraction(0.0);
    });
    // Literal on the edge of symbol range
    assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("x"), new DoubleLiteral("10.0"))).outputRowsCount(// all rows minus nulls multiplied by ((distinct values - 1) / distinct values)
    731.25).variableStats(new VariableReferenceExpression(Optional.empty(), "x", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(39.0).lowValue(-10.0).highValue(10.0).nullsFraction(0.0);
    });
    // Literal out of symbol range
    assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("y"), new DoubleLiteral("10.0"))).outputRowsCount(// all rows minus nulls
    500.0).variableStats(new VariableReferenceExpression(Optional.empty(), "y", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(19.0).lowValue(0.0).highValue(5.0).nullsFraction(0.0);
    });
    // Literal in left open range
    assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("leftOpen"), new DoubleLiteral("2.5"))).outputRowsCount(// all rows minus nulls multiplied by ((distinct values - 1) / distinct values)
    882.0).variableStats(new VariableReferenceExpression(Optional.empty(), "leftOpen", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(49.0).lowValueUnknown().highValue(15.0).nullsFraction(0.0);
    });
    // Literal in right open range
    assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("rightOpen"), new DoubleLiteral("-2.5"))).outputRowsCount(// all rows minus nulls divided by distinct values count
    882.0).variableStats(new VariableReferenceExpression(Optional.empty(), "rightOpen", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(49.0).lowValue(-15.0).highValueUnknown().nullsFraction(0.0);
    });
    // Literal in unknown range
    assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("unknownRange"), new DoubleLiteral("0.0"))).outputRowsCount(// all rows minus nulls divided by distinct values count
    882.0).variableStats(new VariableReferenceExpression(Optional.empty(), "unknownRange", DOUBLE), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(49.0).lowValueUnknown().highValueUnknown().nullsFraction(0.0);
    });
    // Literal in empty range
    assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("emptyRange"), new DoubleLiteral("0.0"))).outputRowsCount(0.0).variableStats(new VariableReferenceExpression(Optional.empty(), "emptyRange", DOUBLE), equalTo(emptyRangeStats));
    // Column with values not representable as double (unknown range)
    assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("varchar"), new StringLiteral("blah"))).outputRowsCount(// all rows minus nulls divided by distinct values count
    882.0).variableStats(new VariableReferenceExpression(Optional.empty(), "varchar", VarcharType.createVarcharType(10)), symbolAssert -> {
        symbolAssert.averageRowSize(4.0).distinctValuesCount(49.0).lowValueUnknown().highValueUnknown().nullsFraction(0.0);
    });
}

Example 28 with ComparisonExpression

use of com.facebook.presto.sql.tree.ComparisonExpression in project presto by prestodb.

the class TestComparisonStatsCalculator method symbolToCastExpressionNotEqual.

@Test
public void symbolToCastExpressionNotEqual() {
    double rowCount = 807.3;
    assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("u"), new Cast(new SymbolReference("w"), StandardTypes.BIGINT))).outputRowsCount(rowCount).variableStats(new VariableReferenceExpression(Optional.empty(), "u", DOUBLE), equalTo(capNDV(zeroNullsFraction(uStats), rowCount))).variableStats(new VariableReferenceExpression(Optional.empty(), "w", DOUBLE), equalTo(capNDV(wStats, rowCount))).variableStats(new VariableReferenceExpression(Optional.empty(), "z", DOUBLE), equalTo(capNDV(zStats, rowCount)));
    rowCount = 897.0;
    assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("u"), new Cast(new LongLiteral("10"), StandardTypes.BIGINT))).outputRowsCount(rowCount).variableStats(new VariableReferenceExpression(Optional.empty(), "u", DOUBLE), equalTo(capNDV(updateNDV(zeroNullsFraction(uStats), -1), rowCount))).variableStats(new VariableReferenceExpression(Optional.empty(), "z", DOUBLE), equalTo(capNDV(zStats, rowCount)));
}

Example 29 with ComparisonExpression

use of com.facebook.presto.sql.tree.ComparisonExpression in project presto by prestodb.

the class TestJoinStatsRule method testStatsForInnerJoinWithTwoEquiClausesAndNonEqualityFunction.

@Test
public void testStatsForInnerJoinWithTwoEquiClausesAndNonEqualityFunction() {
    double innerJoinRowCount = // driver join clause
    LEFT_ROWS_COUNT * RIGHT_ROWS_COUNT / LEFT_JOIN_COLUMN_2_NDV * LEFT_JOIN_COLUMN_2_NON_NULLS * RIGHT_JOIN_COLUMN_2_NON_NULLS * // auxiliary join clause
    UNKNOWN_FILTER_COEFFICIENT * // LEFT_JOIN_COLUMN < 10 non equality filter
    0.3333333333;
    PlanNodeStatsEstimate innerJoinStats = planNodeStats(innerJoinRowCount, variableStatistics(LEFT_JOIN_COLUMN, 5.0, 10.0, 0.0, RIGHT_JOIN_COLUMN_NDV * 0.3333333333), variableStatistics(RIGHT_JOIN_COLUMN, 5.0, 20.0, 0.0, RIGHT_JOIN_COLUMN_NDV), variableStatistics(LEFT_JOIN_COLUMN_2, 100.0, 200.0, 0.0, RIGHT_JOIN_COLUMN_2_NDV), variableStatistics(RIGHT_JOIN_COLUMN_2, 100.0, 200.0, 0.0, RIGHT_JOIN_COLUMN_2_NDV));
    tester().assertStatsFor(pb -> {
        VariableReferenceExpression leftJoinColumn = pb.variable(LEFT_JOIN_COLUMN);
        VariableReferenceExpression rightJoinColumn = pb.variable(RIGHT_JOIN_COLUMN);
        VariableReferenceExpression leftJoinColumn2 = pb.variable(LEFT_JOIN_COLUMN_2);
        VariableReferenceExpression rightJoinColumn2 = pb.variable(RIGHT_JOIN_COLUMN_2);
        ComparisonExpression leftJoinColumnLessThanTen = new ComparisonExpression(ComparisonExpression.Operator.LESS_THAN, new SymbolReference(leftJoinColumn.getName()), new LongLiteral("10"));
        return pb.join(INNER, pb.values(leftJoinColumn, leftJoinColumn2), pb.values(rightJoinColumn, rightJoinColumn2), ImmutableList.of(new EquiJoinClause(leftJoinColumn2, rightJoinColumn2), new EquiJoinClause(leftJoinColumn, rightJoinColumn)), ImmutableList.of(leftJoinColumn, leftJoinColumn2, rightJoinColumn, rightJoinColumn2), Optional.of(castToRowExpression(leftJoinColumnLessThanTen)));
    }).withSourceStats(0, planNodeStats(LEFT_ROWS_COUNT, LEFT_JOIN_COLUMN_STATS, LEFT_JOIN_COLUMN_2_STATS)).withSourceStats(1, planNodeStats(RIGHT_ROWS_COUNT, RIGHT_JOIN_COLUMN_STATS, RIGHT_JOIN_COLUMN_2_STATS)).check(stats -> stats.equalTo(innerJoinStats));
}

Example 30 with ComparisonExpression

use of com.facebook.presto.sql.tree.ComparisonExpression in project presto by prestodb.

the class TestSqlParser method testDelete.

@Test
public void testDelete() {
    assertStatement("DELETE FROM t", new Delete(table(QualifiedName.of("t")), Optional.empty()));
    assertStatement("DELETE FROM \"awesome table\"", new Delete(table(QualifiedName.of("awesome table")), Optional.empty()));
    assertStatement("DELETE FROM t WHERE a = b", new Delete(table(QualifiedName.of("t")), Optional.of(new ComparisonExpression(ComparisonExpression.Operator.EQUAL, new Identifier("a"), new Identifier("b")))));
}