Example 16 with ComparisonExpression
use of io.trino.sql.tree.ComparisonExpression in project trino by trinodb.
the class TestSqlParser method testShowStatsForQuery.
@Test
public void testShowStatsForQuery() {
String[] tableNames = { "t", "s.t", "c.s.t" };
for (String fullName : tableNames) {
QualifiedName qualifiedName = makeQualifiedName(fullName);
// Simple SELECT
assertStatement(format("SHOW STATS FOR (SELECT * FROM %s)", qualifiedName), createShowStats(qualifiedName, ImmutableList.of(new AllColumns()), Optional.empty()));
// SELECT with predicate
assertStatement(format("SHOW STATS FOR (SELECT * FROM %s WHERE field > 0)", qualifiedName), createShowStats(qualifiedName, ImmutableList.of(new AllColumns()), Optional.of(new ComparisonExpression(ComparisonExpression.Operator.GREATER_THAN, new Identifier("field"), new LongLiteral("0")))));
// SELECT with more complex predicate
assertStatement(format("SHOW STATS FOR (SELECT * FROM %s WHERE field > 0 or field < 0)", qualifiedName), createShowStats(qualifiedName, ImmutableList.of(new AllColumns()), Optional.of(LogicalExpression.or(new ComparisonExpression(ComparisonExpression.Operator.GREATER_THAN, new Identifier("field"), new LongLiteral("0")), new ComparisonExpression(ComparisonExpression.Operator.LESS_THAN, new Identifier("field"), new LongLiteral("0"))))));
}
// SELECT with LIMIT
assertThat(statement("SHOW STATS FOR (SELECT * FROM t LIMIT 10)")).isEqualTo(new ShowStats(Optional.of(location(1, 1)), new TableSubquery(new Query(location(1, 17), Optional.empty(), new QuerySpecification(location(1, 17), new Select(location(1, 17), false, ImmutableList.of(new AllColumns(location(1, 24), Optional.empty(), ImmutableList.of()))), Optional.of(new Table(location(1, 31), QualifiedName.of(ImmutableList.of(new Identifier(location(1, 31), "t", false))))), Optional.empty(), Optional.empty(), Optional.empty(), ImmutableList.of(), Optional.empty(), Optional.empty(), Optional.of(new Limit(location(1, 33), new LongLiteral(location(1, 39), "10")))), Optional.empty(), Optional.empty(), Optional.empty()))));
// SELECT with ORDER BY ... LIMIT
assertThat(statement("SHOW STATS FOR (SELECT * FROM t ORDER BY field LIMIT 10)")).isEqualTo(new ShowStats(Optional.of(location(1, 1)), new TableSubquery(new Query(location(1, 17), Optional.empty(), new QuerySpecification(location(1, 17), new Select(location(1, 17), false, ImmutableList.of(new AllColumns(location(1, 24), Optional.empty(), ImmutableList.of()))), Optional.of(new Table(location(1, 31), QualifiedName.of(ImmutableList.of(new Identifier(location(1, 31), "t", false))))), Optional.empty(), Optional.empty(), Optional.empty(), ImmutableList.of(), Optional.of(new OrderBy(location(1, 33), ImmutableList.of(new SortItem(location(1, 42), new Identifier(location(1, 42), "field", false), ASCENDING, UNDEFINED)))), Optional.empty(), Optional.of(new Limit(location(1, 48), new LongLiteral(location(1, 54), "10")))), Optional.empty(), Optional.empty(), Optional.empty()))));
// SELECT with WITH
assertThat(statement("SHOW STATS FOR (\n" + " WITH t AS (SELECT 1 )\n" + " SELECT * FROM t)")).isEqualTo(new ShowStats(Optional.of(location(1, 1)), new TableSubquery(new Query(location(2, 4), Optional.of(new With(location(2, 4), false, ImmutableList.of(new WithQuery(location(2, 9), new Identifier(location(2, 9), "t", false), new Query(location(2, 15), Optional.empty(), new QuerySpecification(location(2, 15), new Select(location(2, 15), false, ImmutableList.of(new SingleColumn(location(2, 22), new LongLiteral(location(2, 22), "1"), Optional.empty()))), Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty(), ImmutableList.of(), Optional.empty(), Optional.empty(), Optional.empty()), Optional.empty(), Optional.empty(), Optional.empty()), Optional.empty())))), new QuerySpecification(location(3, 4), new Select(location(3, 4), false, ImmutableList.of(new AllColumns(location(3, 11), Optional.empty(), ImmutableList.of()))), Optional.of(new Table(location(3, 18), QualifiedName.of(ImmutableList.of(new Identifier(location(3, 18), "t", false))))), Optional.empty(), Optional.empty(), Optional.empty(), ImmutableList.of(), Optional.empty(), Optional.empty(), Optional.empty()), Optional.empty(), Optional.empty(), Optional.empty()))));
}
Also used :
OrderBy(io.trino.sql.tree.OrderBy)
CreateTable(io.trino.sql.tree.CreateTable)
DropTable(io.trino.sql.tree.DropTable)
Table(io.trino.sql.tree.Table)
TruncateTable(io.trino.sql.tree.TruncateTable)
RenameTable(io.trino.sql.tree.RenameTable)
QueryUtil.simpleQuery(io.trino.sql.QueryUtil.simpleQuery)
Query(io.trino.sql.tree.Query)
WithQuery(io.trino.sql.tree.WithQuery)
LongLiteral(io.trino.sql.tree.LongLiteral)
QualifiedName(io.trino.sql.tree.QualifiedName)
AllColumns(io.trino.sql.tree.AllColumns)
SingleColumn(io.trino.sql.tree.SingleColumn)
TableSubquery(io.trino.sql.tree.TableSubquery)
With(io.trino.sql.tree.With)
QuantifiedComparisonExpression(io.trino.sql.tree.QuantifiedComparisonExpression)
ComparisonExpression(io.trino.sql.tree.ComparisonExpression)
QuerySpecification(io.trino.sql.tree.QuerySpecification)
SortItem(io.trino.sql.tree.SortItem)
QueryUtil.quotedIdentifier(io.trino.sql.QueryUtil.quotedIdentifier)
Identifier(io.trino.sql.tree.Identifier)
ShowStats(io.trino.sql.tree.ShowStats)
WithQuery(io.trino.sql.tree.WithQuery)
CreateTableAsSelect(io.trino.sql.tree.CreateTableAsSelect)
Select(io.trino.sql.tree.Select)
Limit(io.trino.sql.tree.Limit)
Test(org.junit.jupiter.api.Test)
Example 17 with ComparisonExpression
use of io.trino.sql.tree.ComparisonExpression in project trino by trinodb.
the class TestComparisonStatsCalculator method symbolToLiteralGreaterThanStats.
@Test
public void symbolToLiteralGreaterThanStats() {
// Simple case
assertCalculate(new ComparisonExpression(GREATER_THAN, new SymbolReference("y"), new DoubleLiteral("2.5"))).outputRowsCount(// all rows minus nulls times range coverage (50%)
250.0).symbolStats("y", symbolAssert -> {
symbolAssert.averageRowSize(4.0).distinctValuesCount(10.0).lowValue(2.5).highValue(5.0).nullsFraction(0.0);
});
// Literal on the edge of symbol range (whole range included)
assertCalculate(new ComparisonExpression(GREATER_THAN, new SymbolReference("x"), new DoubleLiteral("-10.0"))).outputRowsCount(// all rows minus nulls times range coverage (100%)
750.0).symbolStats("x", 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(GREATER_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).symbolStats("x", 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(GREATER_THAN, new SymbolReference("y"), new DoubleLiteral("10.0"))).outputRowsCount(// all rows minus nulls times range coverage (0%)
0.0).symbolStats("y", symbolAssert -> {
symbolAssert.averageRowSize(0.0).distinctValuesCount(0.0).emptyRange().nullsFraction(1.0);
});
// Literal in left open range
assertCalculate(new ComparisonExpression(GREATER_THAN, new SymbolReference("leftOpen"), new DoubleLiteral("0.0"))).outputRowsCount(// all rows minus nulls times range coverage (25% - heuristic)
225.0).symbolStats("leftOpen", symbolAssert -> {
symbolAssert.averageRowSize(4.0).distinctValuesCount(// (25% heuristic)
12.5).lowValue(0.0).highValue(15.0).nullsFraction(0.0);
});
// Literal in right open range
assertCalculate(new ComparisonExpression(GREATER_THAN, new SymbolReference("rightOpen"), new DoubleLiteral("0.0"))).outputRowsCount(// all rows minus nulls times range coverage (50% - heuristic)
450.0).symbolStats("rightOpen", symbolAssert -> {
symbolAssert.averageRowSize(4.0).distinctValuesCount(// (50% heuristic)
25.0).lowValue(0.0).highValueUnknown().nullsFraction(0.0);
});
// Literal in unknown range
assertCalculate(new ComparisonExpression(GREATER_THAN, new SymbolReference("unknownRange"), new DoubleLiteral("0.0"))).outputRowsCount(// all rows minus nulls times range coverage (50% - heuristic)
450.0).symbolStats("unknownRange", symbolAssert -> {
symbolAssert.averageRowSize(4.0).distinctValuesCount(// (50% heuristic)
25.0).lowValue(0.0).highValueUnknown().nullsFraction(0.0);
});
// Literal in empty range
assertCalculate(new ComparisonExpression(GREATER_THAN, new SymbolReference("emptyRange"), new DoubleLiteral("0.0"))).outputRowsCount(0.0).symbolStats("emptyRange", equalTo(emptyRangeStats));
}
Also used :
ComparisonExpression(io.trino.sql.tree.ComparisonExpression)
SymbolReference(io.trino.sql.tree.SymbolReference)
DoubleLiteral(io.trino.sql.tree.DoubleLiteral)
Test(org.testng.annotations.Test)
Example 18 with ComparisonExpression
use of io.trino.sql.tree.ComparisonExpression in project trino by trinodb.
the class TestComparisonStatsCalculator method symbolToSymbolNotEqual.
@Test
public void symbolToSymbolNotEqual() {
// Equal ranges
double rowCount = 807.3;
assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("u"), new SymbolReference("w"))).outputRowsCount(rowCount).symbolStats("u", equalTo(capNDV(zeroNullsFraction(uStats), rowCount))).symbolStats("w", equalTo(capNDV(zeroNullsFraction(wStats), rowCount))).symbolStats("z", equalTo(capNDV(zStats, rowCount)));
// One symbol's range is within the other's
rowCount = 365.625;
assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("x"), new SymbolReference("y"))).outputRowsCount(rowCount).symbolStats("x", equalTo(capNDV(zeroNullsFraction(xStats), rowCount))).symbolStats("y", equalTo(capNDV(zeroNullsFraction(yStats), rowCount))).symbolStats("z", equalTo(capNDV(zStats, rowCount)));
// Partially overlapping ranges
rowCount = 658.125;
assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("x"), new SymbolReference("w"))).outputRowsCount(rowCount).symbolStats("x", equalTo(capNDV(zeroNullsFraction(xStats), rowCount))).symbolStats("w", equalTo(capNDV(zeroNullsFraction(wStats), rowCount))).symbolStats("z", equalTo(capNDV(zStats, rowCount)));
// None of the ranges is included in the other, and one symbol has much higher cardinality, so that it has bigger NDV in intersect than the other in total
rowCount = 672.75;
assertCalculate(new ComparisonExpression(NOT_EQUAL, new SymbolReference("x"), new SymbolReference("u"))).outputRowsCount(rowCount).symbolStats("x", equalTo(capNDV(zeroNullsFraction(xStats), rowCount))).symbolStats("u", equalTo(capNDV(zeroNullsFraction(uStats), rowCount))).symbolStats("z", equalTo(capNDV(zStats, rowCount)));
}
Also used :
ComparisonExpression(io.trino.sql.tree.ComparisonExpression)
SymbolReference(io.trino.sql.tree.SymbolReference)
Test(org.testng.annotations.Test)
Example 19 with ComparisonExpression
use of io.trino.sql.tree.ComparisonExpression in project trino by trinodb.
the class TestComparisonStatsCalculator method symbolToLiteralEqualStats.
@Test
public void symbolToLiteralEqualStats() {
// Simple case
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("y"), new DoubleLiteral("2.5"))).outputRowsCount(// all rows minus nulls divided by distinct values count
25.0).symbolStats("y", symbolAssert -> {
symbolAssert.averageRowSize(4.0).distinctValuesCount(1.0).lowValue(2.5).highValue(2.5).nullsFraction(0.0);
});
// Literal on the edge of symbol range
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("x"), new DoubleLiteral("10.0"))).outputRowsCount(// all rows minus nulls divided by distinct values count
18.75).symbolStats("x", symbolAssert -> {
symbolAssert.averageRowSize(4.0).distinctValuesCount(1.0).lowValue(10.0).highValue(10.0).nullsFraction(0.0);
});
// Literal out of symbol range
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("y"), new DoubleLiteral("10.0"))).outputRowsCount(// all rows minus nulls divided by distinct values count
0.0).symbolStats("y", symbolAssert -> {
symbolAssert.averageRowSize(0.0).distinctValuesCount(0.0).emptyRange().nullsFraction(1.0);
});
// Literal in left open range
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("leftOpen"), new DoubleLiteral("2.5"))).outputRowsCount(// all rows minus nulls divided by distinct values count
18.0).symbolStats("leftOpen", symbolAssert -> {
symbolAssert.averageRowSize(4.0).distinctValuesCount(1.0).lowValue(2.5).highValue(2.5).nullsFraction(0.0);
});
// Literal in right open range
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("rightOpen"), new DoubleLiteral("-2.5"))).outputRowsCount(// all rows minus nulls divided by distinct values count
18.0).symbolStats("rightOpen", symbolAssert -> {
symbolAssert.averageRowSize(4.0).distinctValuesCount(1.0).lowValue(-2.5).highValue(-2.5).nullsFraction(0.0);
});
// Literal in unknown range
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("unknownRange"), new DoubleLiteral("0.0"))).outputRowsCount(// all rows minus nulls divided by distinct values count
18.0).symbolStats("unknownRange", symbolAssert -> {
symbolAssert.averageRowSize(4.0).distinctValuesCount(1.0).lowValue(0.0).highValue(0.0).nullsFraction(0.0);
});
// Literal in empty range
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("emptyRange"), new DoubleLiteral("0.0"))).outputRowsCount(0.0).symbolStats("emptyRange", equalTo(emptyRangeStats));
// Column with values not representable as double (unknown range)
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("varchar"), new StringLiteral("blah"))).outputRowsCount(// all rows minus nulls divided by distinct values count
18.0).symbolStats("varchar", symbolAssert -> {
symbolAssert.averageRowSize(4.0).distinctValuesCount(1.0).lowValue(NEGATIVE_INFINITY).highValue(POSITIVE_INFINITY).nullsFraction(0.0);
});
}
Also used :
ComparisonExpression(io.trino.sql.tree.ComparisonExpression)
StringLiteral(io.trino.sql.tree.StringLiteral)
SymbolReference(io.trino.sql.tree.SymbolReference)
DoubleLiteral(io.trino.sql.tree.DoubleLiteral)
Test(org.testng.annotations.Test)
Example 20 with ComparisonExpression
use of io.trino.sql.tree.ComparisonExpression in project trino by trinodb.
the class TestComparisonStatsCalculator method symbolToSymbolEqualStats.
@Test
public void symbolToSymbolEqualStats() {
// z's stats should be unchanged when not involved, except NDV capping to row count
// Equal ranges
double rowCount = 2.7;
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("u"), new SymbolReference("w"))).outputRowsCount(rowCount).symbolStats("u", equalTo(capNDV(zeroNullsFraction(uStats), rowCount))).symbolStats("w", equalTo(capNDV(zeroNullsFraction(wStats), rowCount))).symbolStats("z", equalTo(capNDV(zStats, rowCount)));
// One symbol's range is within the other's
rowCount = 9.375;
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("x"), new SymbolReference("y"))).outputRowsCount(rowCount).symbolStats("x", symbolAssert -> {
symbolAssert.averageRowSize(4).lowValue(0).highValue(5).distinctValuesCount(9.375).nullsFraction(0);
}).symbolStats("y", symbolAssert -> {
symbolAssert.averageRowSize(4).lowValue(0).highValue(5).distinctValuesCount(9.375).nullsFraction(0);
}).symbolStats("z", equalTo(capNDV(zStats, rowCount)));
// Partially overlapping ranges
rowCount = 16.875;
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("x"), new SymbolReference("w"))).outputRowsCount(rowCount).symbolStats("x", symbolAssert -> {
symbolAssert.averageRowSize(6).lowValue(0).highValue(10).distinctValuesCount(16.875).nullsFraction(0);
}).symbolStats("w", symbolAssert -> {
symbolAssert.averageRowSize(6).lowValue(0).highValue(10).distinctValuesCount(16.875).nullsFraction(0);
}).symbolStats("z", equalTo(capNDV(zStats, rowCount)));
// None of the ranges is included in the other, and one symbol has much higher cardinality, so that it has bigger NDV in intersect than the other in total
rowCount = 2.25;
assertCalculate(new ComparisonExpression(EQUAL, new SymbolReference("x"), new SymbolReference("u"))).outputRowsCount(rowCount).symbolStats("x", symbolAssert -> {
symbolAssert.averageRowSize(6).lowValue(0).highValue(10).distinctValuesCount(2.25).nullsFraction(0);
}).symbolStats("u", symbolAssert -> {
symbolAssert.averageRowSize(6).lowValue(0).highValue(10).distinctValuesCount(2.25).nullsFraction(0);
}).symbolStats("z", equalTo(capNDV(zStats, rowCount)));
}
Also used :
TypeAnalyzer.createTestingTypeAnalyzer(io.trino.sql.planner.TypeAnalyzer.createTestingTypeAnalyzer)
POSITIVE_INFINITY(java.lang.Double.POSITIVE_INFINITY)
DoubleType(io.trino.spi.type.DoubleType)
Type(io.trino.spi.type.Type)
Test(org.testng.annotations.Test)
NEGATIVE_INFINITY(java.lang.Double.NEGATIVE_INFINITY)
ArrayList(java.util.ArrayList)
Cast(io.trino.sql.tree.Cast)
VarcharType(io.trino.spi.type.VarcharType)
NaN(java.lang.Double.NaN)
LongLiteral(io.trino.sql.tree.LongLiteral)
GREATER_THAN(io.trino.sql.tree.ComparisonExpression.Operator.GREATER_THAN)
Symbol(io.trino.sql.planner.Symbol)
StringLiteral(io.trino.sql.tree.StringLiteral)
ImmutableMap(com.google.common.collect.ImmutableMap)
BeforeClass(org.testng.annotations.BeforeClass)
Collection(java.util.Collection)
TypeSignatureTranslator.toSqlType(io.trino.sql.analyzer.TypeSignatureTranslator.toSqlType)
LESS_THAN(io.trino.sql.tree.ComparisonExpression.Operator.LESS_THAN)
Math.min(java.lang.Math.min)
ComparisonExpression(io.trino.sql.tree.ComparisonExpression)
DoubleLiteral(io.trino.sql.tree.DoubleLiteral)
String.format(java.lang.String.format)
Collectors.joining(java.util.stream.Collectors.joining)
Objects(java.util.Objects)
Consumer(java.util.function.Consumer)
DOUBLE(io.trino.spi.type.DoubleType.DOUBLE)
EQUAL(io.trino.sql.tree.ComparisonExpression.Operator.EQUAL)
NOT_EQUAL(io.trino.sql.tree.ComparisonExpression.Operator.NOT_EQUAL)
List(java.util.List)
TestingSession.testSessionBuilder(io.trino.testing.TestingSession.testSessionBuilder)
BIGINT(io.trino.spi.type.BigintType.BIGINT)
PLANNER_CONTEXT(io.trino.sql.planner.TestingPlannerContext.PLANNER_CONTEXT)
SymbolReference(io.trino.sql.tree.SymbolReference)
Double.isNaN(java.lang.Double.isNaN)
TypeProvider(io.trino.sql.planner.TypeProvider)
Expression(io.trino.sql.tree.Expression)
Session(io.trino.Session)
ComparisonExpression(io.trino.sql.tree.ComparisonExpression)
SymbolReference(io.trino.sql.tree.SymbolReference)
Test(org.testng.annotations.Test)
Aggregations
ComparisonExpression (io.trino.sql.tree.ComparisonExpression)58
Test (org.testng.annotations.Test)30
ImmutableList (com.google.common.collect.ImmutableList)24
Expression (io.trino.sql.tree.Expression)23
Symbol (io.trino.sql.planner.Symbol)22
SymbolReference (io.trino.sql.tree.SymbolReference)21
ImmutableMap (com.google.common.collect.ImmutableMap)18
LongLiteral (io.trino.sql.tree.LongLiteral)17
BaseRuleTest (io.trino.sql.planner.iterative.rule.test.BaseRuleTest)15
FunctionCall (io.trino.sql.tree.FunctionCall)15
GenericLiteral (io.trino.sql.tree.GenericLiteral)15
QualifiedName (io.trino.sql.tree.QualifiedName)15
Optional (java.util.Optional)15
StringLiteral (io.trino.sql.tree.StringLiteral)14
PlanMatchPattern.values (io.trino.sql.planner.assertions.PlanMatchPattern.values)13
Type (io.trino.spi.type.Type)12
BIGINT (io.trino.spi.type.BigintType.BIGINT)11
FilterNode (io.trino.sql.planner.plan.FilterNode)11
ResolvedFunction (io.trino.metadata.ResolvedFunction)10
GREATER_THAN (io.trino.sql.tree.ComparisonExpression.Operator.GREATER_THAN)10
