use of io.trino.sql.planner.planprinter.IoPlanPrinter.IoPlan in project trino by trinodb.
the class BaseHiveConnectorTest method testIoExplainColumnFilters.
@Test
public void testIoExplainColumnFilters() {
// Test IO explain with small number of discrete components.
computeActual("CREATE TABLE test_io_explain_column_filters WITH (partitioned_by = ARRAY['orderkey']) AS SELECT custkey, orderstatus, orderkey FROM orders WHERE orderkey < 3");
EstimatedStatsAndCost estimate = new EstimatedStatsAndCost(2.0, 48.0, 48.0, 0.0, 0.0);
EstimatedStatsAndCost finalEstimate = new EstimatedStatsAndCost(0.0, 0.0, 96.0, 0.0, 0.0);
MaterializedResult result = computeActual("EXPLAIN (TYPE IO, FORMAT JSON) SELECT custkey, orderkey, orderstatus FROM test_io_explain_column_filters WHERE custkey <= 10 and orderstatus='P'");
assertEquals(getIoPlanCodec().fromJson((String) getOnlyElement(result.getOnlyColumnAsSet())), new IoPlan(ImmutableSet.of(new TableColumnInfo(new CatalogSchemaTableName(catalog, "tpch", "test_io_explain_column_filters"), ImmutableSet.of(new ColumnConstraint("orderkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("1"), EXACTLY), new FormattedMarker(Optional.of("1"), EXACTLY)), new FormattedRange(new FormattedMarker(Optional.of("2"), EXACTLY), new FormattedMarker(Optional.of("2"), EXACTLY))))), new ColumnConstraint("custkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.empty(), ABOVE), new FormattedMarker(Optional.of("10"), EXACTLY))))), new ColumnConstraint("orderstatus", VarcharType.createVarcharType(1), new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("P"), EXACTLY), new FormattedMarker(Optional.of("P"), EXACTLY)))))), estimate)), Optional.empty(), finalEstimate));
result = computeActual("EXPLAIN (TYPE IO, FORMAT JSON) SELECT custkey, orderkey, orderstatus FROM test_io_explain_column_filters WHERE custkey <= 10 and (orderstatus='P' or orderstatus='S')");
assertEquals(getIoPlanCodec().fromJson((String) getOnlyElement(result.getOnlyColumnAsSet())), new IoPlan(ImmutableSet.of(new TableColumnInfo(new CatalogSchemaTableName(catalog, "tpch", "test_io_explain_column_filters"), ImmutableSet.of(new ColumnConstraint("orderkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("1"), EXACTLY), new FormattedMarker(Optional.of("1"), EXACTLY)), new FormattedRange(new FormattedMarker(Optional.of("2"), EXACTLY), new FormattedMarker(Optional.of("2"), EXACTLY))))), new ColumnConstraint("orderstatus", VarcharType.createVarcharType(1), new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("P"), EXACTLY), new FormattedMarker(Optional.of("P"), EXACTLY)), new FormattedRange(new FormattedMarker(Optional.of("S"), EXACTLY), new FormattedMarker(Optional.of("S"), EXACTLY))))), new ColumnConstraint("custkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.empty(), ABOVE), new FormattedMarker(Optional.of("10"), EXACTLY)))))), estimate)), Optional.empty(), finalEstimate));
result = computeActual("EXPLAIN (TYPE IO, FORMAT JSON) SELECT custkey, orderkey, orderstatus FROM test_io_explain_column_filters WHERE custkey <= 10 and cast(orderstatus as integer) = 5");
assertEquals(getIoPlanCodec().fromJson((String) getOnlyElement(result.getOnlyColumnAsSet())), new IoPlan(ImmutableSet.of(new TableColumnInfo(new CatalogSchemaTableName(catalog, "tpch", "test_io_explain_column_filters"), ImmutableSet.of(new ColumnConstraint("orderkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("1"), EXACTLY), new FormattedMarker(Optional.of("1"), EXACTLY)), new FormattedRange(new FormattedMarker(Optional.of("2"), EXACTLY), new FormattedMarker(Optional.of("2"), EXACTLY))))), new ColumnConstraint("custkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.empty(), ABOVE), new FormattedMarker(Optional.of("10"), EXACTLY)))))), estimate)), Optional.empty(), finalEstimate));
assertUpdate("DROP TABLE test_io_explain_column_filters");
}
use of io.trino.sql.planner.planprinter.IoPlanPrinter.IoPlan in project trino by trinodb.
the class BaseHiveConnectorTest method testIoExplain.
@Test
public void testIoExplain() {
// Test IO explain with small number of discrete components.
computeActual("CREATE TABLE test_io_explain WITH (partitioned_by = ARRAY['orderkey', 'processing']) AS SELECT custkey, orderkey, orderstatus = 'P' processing FROM orders WHERE orderkey < 3");
EstimatedStatsAndCost estimate = new EstimatedStatsAndCost(2.0, 40.0, 40.0, 0.0, 0.0);
MaterializedResult result = computeActual("EXPLAIN (TYPE IO, FORMAT JSON) INSERT INTO test_io_explain SELECT custkey, orderkey, processing FROM test_io_explain WHERE custkey <= 10");
assertEquals(getIoPlanCodec().fromJson((String) getOnlyElement(result.getOnlyColumnAsSet())), new IoPlan(ImmutableSet.of(new TableColumnInfo(new CatalogSchemaTableName(catalog, "tpch", "test_io_explain"), ImmutableSet.of(new ColumnConstraint("orderkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("1"), EXACTLY), new FormattedMarker(Optional.of("1"), EXACTLY)), new FormattedRange(new FormattedMarker(Optional.of("2"), EXACTLY), new FormattedMarker(Optional.of("2"), EXACTLY))))), new ColumnConstraint("processing", BOOLEAN, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("false"), EXACTLY), new FormattedMarker(Optional.of("false"), EXACTLY))))), new ColumnConstraint("custkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.empty(), ABOVE), new FormattedMarker(Optional.of("10"), EXACTLY)))))), estimate)), Optional.of(new CatalogSchemaTableName(catalog, "tpch", "test_io_explain")), estimate));
assertUpdate("DROP TABLE test_io_explain");
// Test IO explain with large number of discrete components where Domain::simpify comes into play.
computeActual("CREATE TABLE test_io_explain WITH (partitioned_by = ARRAY['orderkey']) AS SELECT custkey, orderkey FROM orders WHERE orderkey < 200");
estimate = new EstimatedStatsAndCost(55.0, 990.0, 990.0, 0.0, 0.0);
result = computeActual("EXPLAIN (TYPE IO, FORMAT JSON) INSERT INTO test_io_explain SELECT custkey, orderkey + 10 FROM test_io_explain WHERE custkey <= 10");
assertEquals(getIoPlanCodec().fromJson((String) getOnlyElement(result.getOnlyColumnAsSet())), new IoPlan(ImmutableSet.of(new TableColumnInfo(new CatalogSchemaTableName(catalog, "tpch", "test_io_explain"), ImmutableSet.of(new ColumnConstraint("orderkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("1"), EXACTLY), new FormattedMarker(Optional.of("199"), EXACTLY))))), new ColumnConstraint("custkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.empty(), ABOVE), new FormattedMarker(Optional.of("10"), EXACTLY)))))), estimate)), Optional.of(new CatalogSchemaTableName(catalog, "tpch", "test_io_explain")), estimate));
EstimatedStatsAndCost finalEstimate = new EstimatedStatsAndCost(Double.NaN, Double.NaN, Double.NaN, Double.NaN, Double.NaN);
estimate = new EstimatedStatsAndCost(1.0, 18.0, 18, 0.0, 0.0);
result = computeActual("EXPLAIN (TYPE IO, FORMAT JSON) INSERT INTO test_io_explain SELECT custkey, orderkey FROM test_io_explain WHERE orderkey = 100");
assertEquals(getIoPlanCodec().fromJson((String) getOnlyElement(result.getOnlyColumnAsSet())), new IoPlan(ImmutableSet.of(new TableColumnInfo(new CatalogSchemaTableName(catalog, "tpch", "test_io_explain"), ImmutableSet.of(new ColumnConstraint("orderkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("100"), EXACTLY), new FormattedMarker(Optional.of("100"), EXACTLY))))), new ColumnConstraint("orderkey", BIGINT, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("100"), EXACTLY), new FormattedMarker(Optional.of("100"), EXACTLY)))))), estimate)), Optional.of(new CatalogSchemaTableName(catalog, "tpch", "test_io_explain")), finalEstimate));
assertUpdate("DROP TABLE test_io_explain");
}
use of io.trino.sql.planner.planprinter.IoPlanPrinter.IoPlan in project trino by trinodb.
the class BaseHiveConnectorTest method testIoExplainWithPrimitiveTypes.
@Test
public void testIoExplainWithPrimitiveTypes() {
// Use LinkedHashMap to maintain insertion order for ease of locating
// map entry if assertion in the loop below fails.
Map<Object, TypeAndEstimate> data = new LinkedHashMap<>();
data.put("foo", new TypeAndEstimate(createUnboundedVarcharType(), new EstimatedStatsAndCost(1.0, 16.0, 16.0, 0.0, 0.0)));
data.put(Byte.toString((byte) (Byte.MAX_VALUE / 2)), new TypeAndEstimate(TINYINT, new EstimatedStatsAndCost(1.0, 10.0, 10.0, 0.0, 0.0)));
data.put(Short.toString((short) (Short.MAX_VALUE / 2)), new TypeAndEstimate(SMALLINT, new EstimatedStatsAndCost(1.0, 11.0, 11.0, 0.0, 0.0)));
data.put(Integer.toString(Integer.MAX_VALUE / 2), new TypeAndEstimate(INTEGER, new EstimatedStatsAndCost(1.0, 13.0, 13.0, 0.0, 0.0)));
data.put(Long.toString(Long.MAX_VALUE / 2), new TypeAndEstimate(BIGINT, new EstimatedStatsAndCost(1.0, 17.0, 17.0, 0.0, 0.0)));
data.put(Boolean.TRUE.toString(), new TypeAndEstimate(BOOLEAN, new EstimatedStatsAndCost(1.0, 10.0, 10.0, 0.0, 0.0)));
data.put("bar", new TypeAndEstimate(createCharType(3), new EstimatedStatsAndCost(1.0, 16.0, 16.0, 0.0, 0.0)));
data.put("1.2345678901234578E14", new TypeAndEstimate(DOUBLE, new EstimatedStatsAndCost(1.0, 17.0, 17.0, 0.0, 0.0)));
data.put("123456789012345678901234.567", new TypeAndEstimate(createDecimalType(30, 3), new EstimatedStatsAndCost(1.0, 25.0, 25.0, 0.0, 0.0)));
data.put("2019-01-01", new TypeAndEstimate(DateType.DATE, new EstimatedStatsAndCost(1.0, 13.0, 13.0, 0.0, 0.0)));
data.put("2019-01-01 23:22:21.123", new TypeAndEstimate(TimestampType.TIMESTAMP_MILLIS, new EstimatedStatsAndCost(1.0, 17.0, 17.0, 0.0, 0.0)));
int index = 0;
for (Map.Entry<Object, TypeAndEstimate> entry : data.entrySet()) {
index++;
Type type = entry.getValue().type;
EstimatedStatsAndCost estimate = entry.getValue().estimate;
@Language("SQL") String query = format("CREATE TABLE test_types_table WITH (partitioned_by = ARRAY['my_col']) AS " + "SELECT 'foo' my_non_partition_col, CAST('%s' AS %s) my_col", entry.getKey(), type.getDisplayName());
assertUpdate(query, 1);
assertEquals(getIoPlanCodec().fromJson((String) getOnlyElement(computeActual("EXPLAIN (TYPE IO, FORMAT JSON) SELECT * FROM test_types_table").getOnlyColumnAsSet())), new IoPlan(ImmutableSet.of(new TableColumnInfo(new CatalogSchemaTableName(catalog, "tpch", "test_types_table"), ImmutableSet.of(new ColumnConstraint("my_col", type, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of(entry.getKey().toString()), EXACTLY), new FormattedMarker(Optional.of(entry.getKey().toString()), EXACTLY)))))), estimate)), Optional.empty(), estimate), format("%d) Type %s ", index, type));
assertUpdate("DROP TABLE test_types_table");
}
}
use of io.trino.sql.planner.planprinter.IoPlanPrinter.IoPlan in project trino by trinodb.
the class BaseHiveConnectorTest method testIoExplainNoFilter.
@Test
public void testIoExplainNoFilter() {
Session admin = Session.builder(getSession()).setIdentity(Identity.forUser("hive").withConnectorRole("hive", new SelectedRole(ROLE, Optional.of("admin"))).build()).build();
assertUpdate(admin, "create table io_explain_test_no_filter(\n" + "id integer,\n" + "a varchar,\n" + "b varchar,\n" + "ds varchar)" + "WITH (format='PARQUET', partitioned_by = ARRAY['ds'])");
assertUpdate(admin, "insert into io_explain_test_no_filter(id,a,ds) values(1, 'a','a')", 1);
EstimatedStatsAndCost estimate = new EstimatedStatsAndCost(1.0, 22.0, 22.0, 0.0, 0.0);
EstimatedStatsAndCost finalEstimate = new EstimatedStatsAndCost(1.0, 22.0, 22.0, 0.0, 22.0);
MaterializedResult result = computeActual("EXPLAIN (TYPE IO, FORMAT JSON) SELECT * FROM io_explain_test_no_filter");
assertEquals(getIoPlanCodec().fromJson((String) getOnlyElement(result.getOnlyColumnAsSet())), new IoPlan(ImmutableSet.of(new TableColumnInfo(new CatalogSchemaTableName(catalog, "tpch", "io_explain_test_no_filter"), ImmutableSet.of(new ColumnConstraint("ds", VARCHAR, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("a"), EXACTLY), new FormattedMarker(Optional.of("a"), EXACTLY)))))), estimate)), Optional.empty(), finalEstimate));
assertUpdate("DROP TABLE io_explain_test_no_filter");
}
use of io.trino.sql.planner.planprinter.IoPlanPrinter.IoPlan in project trino by trinodb.
the class BaseHiveConnectorTest method testIoExplainFilterOnAgg.
@Test
public void testIoExplainFilterOnAgg() {
Session admin = Session.builder(getSession()).setIdentity(Identity.forUser("hive").withConnectorRole("hive", new SelectedRole(ROLE, Optional.of("admin"))).build()).build();
assertUpdate(admin, "create table io_explain_test_filter_on_agg(\n" + "id integer,\n" + "a varchar,\n" + "b varchar,\n" + "ds varchar)" + "WITH (format='PARQUET', partitioned_by = ARRAY['ds'])");
assertUpdate(admin, "insert into io_explain_test_filter_on_agg(id,a,ds) values(1, 'a','a')", 1);
EstimatedStatsAndCost estimate = new EstimatedStatsAndCost(1.0, 5.0, 5.0, 0.0, 0.0);
EstimatedStatsAndCost finalEstimate = new EstimatedStatsAndCost(Double.NaN, Double.NaN, Double.NaN, Double.NaN, Double.NaN);
MaterializedResult result = computeActual("EXPLAIN (TYPE IO, FORMAT JSON) SELECT * FROM (SELECT COUNT(*) cnt FROM io_explain_test_filter_on_agg WHERE b = 'b') WHERE cnt > 0");
assertEquals(getIoPlanCodec().fromJson((String) getOnlyElement(result.getOnlyColumnAsSet())), new IoPlan(ImmutableSet.of(new TableColumnInfo(new CatalogSchemaTableName(catalog, "tpch", "io_explain_test_filter_on_agg"), ImmutableSet.of(new ColumnConstraint("ds", VARCHAR, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("a"), EXACTLY), new FormattedMarker(Optional.of("a"), EXACTLY))))), new ColumnConstraint("b", VARCHAR, new FormattedDomain(false, ImmutableSet.of(new FormattedRange(new FormattedMarker(Optional.of("b"), EXACTLY), new FormattedMarker(Optional.of("b"), EXACTLY)))))), estimate)), Optional.empty(), finalEstimate));
assertUpdate("DROP TABLE io_explain_test_filter_on_agg");
}
Aggregations