public static void assertUpdate(QueryRunner queryRunner, Session session, @Language("SQL") String sql, OptionalLong count, Optional<Consumer<Plan>> planAssertion) if (planAssertion.isPresent()) { MaterializedResultWithPlan resultWithPlan = queryRunner.executeWithPlan(session, sql, WarningCollector.NOOP); queryPlan = resultWithPlan.getQueryPlan(); results = resultWithPlan.getMaterializedResult().toTestTypes(); results = queryRunner.execute(session, sql); if (planAssertion.isPresent()) { planAssertion.get().accept(queryPlan); if (!results.getUpdateType().isPresent()) { fail("update type is not set"); if (results.getUpdateCount().isPresent()) { if (!count.isPresent()) { fail("update count should not be present"); assertEquals(results.getUpdateCount().getAsLong(), count.getAsLong(), "update count");
@Test public void testNotMergeDifferentPartition() { @Language("SQL") String sql = "SELECT " + "SUM(discount) OVER (PARTITION BY suppkey ORDER BY orderkey ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_extendedprice_A, " + "SUM(quantity) over (PARTITION BY quantity ORDER BY orderkey ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_quantity_C " + "FROM lineitem"; ExpectedValueProvider<WindowNode.Specification> specificationC = specification( ImmutableList.of(QUANTITY_ALIAS), ImmutableList.of(ORDERKEY_ALIAS), ImmutableMap.of(ORDERKEY_ALIAS, SortOrder.ASC_NULLS_LAST)); assertUnitPlan(sql, anyTree( window(windowMatcherBuilder -> windowMatcherBuilder .specification(specificationA) .addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(DISCOUNT_ALIAS))), window(windowMatcherBuilder -> windowMatcherBuilder .specification(specificationC) .addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(QUANTITY_ALIAS))), LINEITEM_TABLESCAN_DOQS)))); }
@Test public void testEliminateSorts() { @Language("SQL") String sql = "SELECT quantity, row_number() OVER (ORDER BY quantity) FROM lineitem ORDER BY quantity"; PlanMatchPattern pattern = output( window(windowMatcherBuilder -> windowMatcherBuilder .specification(windowSpec) .addFunction(functionCall("row_number", Optional.empty(), ImmutableList.of())), anyTree(LINEITEM_TABLESCAN_Q))); assertUnitPlan(sql, pattern); }
@Test public void testMixedDistinctAggregationOptimizer() @Language("SQL") String sql = "SELECT custkey, max(totalprice) AS s, count(DISTINCT orderdate) AS d FROM orders GROUP BY custkey"; List<String> groupByKeysSecond = ImmutableList.of(groupBy); Map<Optional<String>, ExpectedValueProvider<FunctionCall>> aggregationsSecond = ImmutableMap.of( Optional.of("arbitrary"), PlanMatchPattern.functionCall("arbitrary", false, ImmutableList.of(anySymbol())), Optional.of("count"), PlanMatchPattern.functionCall("count", false, ImmutableList.of(anySymbol()))); List<String> groupByKeysFirst = ImmutableList.of(groupBy, distinctAggregation, group); Map<Optional<String>, ExpectedValueProvider<FunctionCall>> aggregationsFirst = ImmutableMap.of( Optional.of("MAX"), functionCall("max", ImmutableList.of("TOTALPRICE"))); PlanMatchPattern tableScan = tableScan("orders", ImmutableMap.of("TOTALPRICE", "totalprice", "CUSTKEY", "custkey", "ORDERDATE", "orderdate"));
@Test public void testView() { skipTestUnless(supportsViews()); @Language("SQL") String query = "SELECT orderkey, orderstatus, totalprice / 2 half FROM orders"; assertUpdate("CREATE VIEW test_view AS SELECT 123 x"); assertUpdate("CREATE OR REPLACE VIEW test_view AS " + query); assertQuery("SELECT * FROM test_view", query); assertQuery( "SELECT * FROM test_view a JOIN test_view b on a.orderkey = b.orderkey", format("SELECT * FROM (%s) a JOIN (%s) b ON a.orderkey = b.orderkey", query, query)); assertQuery("WITH orders AS (SELECT * FROM orders LIMIT 0) SELECT * FROM test_view", query); String name = format("%s.%s.test_view", getSession().getCatalog().get(), getSession().getSchema().get()); assertQuery("SELECT * FROM " + name, query); assertUpdate("DROP VIEW test_view"); }
@Test public void testIdenticalWindowSpecificationsABA() { @Language("SQL") String sql = "SELECT " + "SUM(quantity) OVER (PARTITION BY suppkey ORDER BY orderkey ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_quantity_A, " + "SUM(quantity) OVER (PARTITION BY orderkey ORDER BY shipdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_quantity_B, " + "SUM(discount) OVER (PARTITION BY suppkey ORDER BY orderkey ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_discount_A " + "FROM lineitem"; assertUnitPlan(sql, anyTree( window(windowMatcherBuilder -> windowMatcherBuilder .specification(specificationA) .addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(QUANTITY_ALIAS))) .addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(DISCOUNT_ALIAS))), window(windowMatcherBuilder -> windowMatcherBuilder .specification(specificationB) .addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(QUANTITY_ALIAS))), LINEITEM_TABLESCAN_DOQSS)))); }
@Test public void testCreateExternalTable() throws Exception { File tempDir = createTempDir(); File dataFile = new File(tempDir, "test.txt"); Files.write("hello\nworld\n", dataFile, UTF_8); @Language("SQL") String createTableSql = format("" + "CREATE TABLE %s.%s.test_create_external (\n" + " name varchar\n" + ")\n" + "WITH (\n" + " external_location = '%s',\n" + " format = 'TEXTFILE'\n" + ")", getSession().getCatalog().get(), getSession().getSchema().get(), new Path(tempDir.toURI().toASCIIString()).toString()); assertUpdate(createTableSql); MaterializedResult actual = computeActual("SHOW CREATE TABLE test_create_external"); assertEquals(actual.getOnlyValue(), createTableSql); actual = computeActual("SELECT name FROM test_create_external"); assertEquals(actual.getOnlyColumnAsSet(), ImmutableSet.of("hello", "world")); assertUpdate("DROP TABLE test_create_external"); // file should still exist after drop assertFile(dataFile); deleteRecursively(tempDir.toPath(), ALLOW_INSECURE); }
@Test public void testViewMetadata() @Language("SQL") String query = "SELECT BIGINT '123' x, 'foo' y"; assertUpdate("CREATE VIEW meta_test_view AS " + query); getSession().getSchema().get())); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row("customer", "BASE TABLE") .row("lineitem", "BASE TABLE") MaterializedResult.Builder builder = resultBuilder(getSession(), actual.getTypes()); for (MaterializedRow row : expected.getMaterializedRows()) { builder.row(row.getField(0)); getSession().getSchema().get())); expected = resultBuilder(getSession(), actual.getTypes()) getSession().getCatalog().get(), getSession().getSchema().get(), "meta_test_view",
private void testPathHiddenColumn(Session session, HiveStorageFormat storageFormat) @Language("SQL") String createTable = "CREATE TABLE test_path " + "WITH (" + "format = '" + storageFormat + "'," + " ) t(col0, col1) "; assertUpdate(session, createTable, 8); assertTrue(getQueryRunner().tableExists(getSession(), "test_path")); List<String> columnNames = ImmutableList.of("col0", "col1", PATH_COLUMN_NAME); List<ColumnMetadata> columnMetadatas = tableMetadata.getColumns(); assertEquals(columnMetadatas.size(), columnNames.size()); if (columnMetadata.getName().equals(PATH_COLUMN_NAME)) { assertTrue(columnMetadata.isHidden()); String parentDirectory = new Path(pathName).getParent().toString(); assertTrue(pathName.length() > 0); assertEquals((int) (col0 % 3), col1); if (partitionPathMap.containsKey(col1)) { assertFalse(getQueryRunner().tableExists(session, "test_path"));
private void assertExistsRewrittenToAggregationAboveJoin(@Language("SQL") String actual, @Language("SQL") String expected, boolean extraAggregation) { Consumer<Plan> singleStreamingAggregationValidator = plan -> assertEquals(countSingleStreamingAggregations(plan), 1); Consumer<Plan> finalAggregationValidator = plan -> assertEquals(countFinalAggregationNodes(plan), extraAggregation ? 1 : 0); assertions.assertQueryAndPlan(actual, expected, anyTree( aggregation( ImmutableMap.of("COUNT", functionCall("count", ImmutableList.of("NON_NULL"))), SINGLE, node(JoinNode.class, anyTree( node(ValuesNode.class)), anyTree( node(ProjectNode.class, anyTree( node(ValuesNode.class))) .withAlias("NON_NULL", expression("true")))))), singleStreamingAggregationValidator.andThen(finalAggregationValidator)); }
private void assertUnitPlan(@Language("SQL") String sql, PlanMatchPattern pattern) { List<PlanOptimizer> optimizers = ImmutableList.of( new UnaliasSymbolReferences(), new IterativeOptimizer( new RuleStatsRecorder(), getQueryRunner().getStatsCalculator(), getQueryRunner().getEstimatedExchangesCostCalculator(), ImmutableSet.<Rule<?>>builder() .add(new RemoveRedundantIdentityProjections()) .addAll(GatherAndMergeWindows.rules()) .build()), new PruneUnreferencedOutputs()); assertPlan(sql, pattern, optimizers); } }
private void testCreatePartitionedTableAs(Session session, HiveStorageFormat storageFormat) { @Language("SQL") String createTable = "" + "CREATE TABLE test_create_partitioned_table_as " + "WITH (" + "format = '" + storageFormat + "', " + "partitioned_by = ARRAY[ 'SHIP_PRIORITY', 'ORDER_STATUS' ]" + ") " + "AS " + "SELECT orderkey AS order_key, shippriority AS ship_priority, orderstatus AS order_status " + "FROM tpch.tiny.orders"; assertUpdate(session, createTable, "SELECT count(*) from orders"); TableMetadata tableMetadata = getTableMetadata(catalog, TPCH_SCHEMA, "test_create_partitioned_table_as"); assertEquals(tableMetadata.getMetadata().getProperties().get(STORAGE_FORMAT_PROPERTY), storageFormat); assertEquals(tableMetadata.getMetadata().getProperties().get(PARTITIONED_BY_PROPERTY), ImmutableList.of("ship_priority", "order_status")); List<?> partitions = getPartitions("test_create_partitioned_table_as"); assertEquals(partitions.size(), 3); assertQuery(session, "SELECT * from test_create_partitioned_table_as", "SELECT orderkey, shippriority, orderstatus FROM orders"); assertUpdate(session, "DROP TABLE test_create_partitioned_table_as"); assertFalse(getQueryRunner().tableExists(session, "test_create_partitioned_table_as")); }
private static void assertEquivalent(@Language("SQL") String left, @Language("SQL") String right) { ParsingOptions parsingOptions = new ParsingOptions(AS_DOUBLE /* anything */); Expression leftExpression = rewriteIdentifiersToSymbolReferences(SQL_PARSER.createExpression(left, parsingOptions)); Expression rightExpression = rewriteIdentifiersToSymbolReferences(SQL_PARSER.createExpression(right, parsingOptions)); Set<Symbol> symbols = extractUnique(ImmutableList.of(leftExpression, rightExpression)); TypeProvider types = TypeProvider.copyOf(symbols.stream() .collect(toMap(identity(), TestExpressionEquivalence::generateType))); assertTrue( EQUIVALENCE.areExpressionsEquivalent(TEST_SESSION, leftExpression, rightExpression, types), String.format("Expected (%s) and (%s) to be equivalent", left, right)); assertTrue( EQUIVALENCE.areExpressionsEquivalent(TEST_SESSION, rightExpression, leftExpression, types), String.format("Expected (%s) and (%s) to be equivalent", right, left)); }
private static void assertNotEquivalent(@Language("SQL") String left, @Language("SQL") String right) { ParsingOptions parsingOptions = new ParsingOptions(AS_DOUBLE /* anything */); Expression leftExpression = rewriteIdentifiersToSymbolReferences(SQL_PARSER.createExpression(left, parsingOptions)); Expression rightExpression = rewriteIdentifiersToSymbolReferences(SQL_PARSER.createExpression(right, parsingOptions)); Set<Symbol> symbols = extractUnique(ImmutableList.of(leftExpression, rightExpression)); TypeProvider types = TypeProvider.copyOf(symbols.stream() .collect(toMap(identity(), TestExpressionEquivalence::generateType))); assertFalse( EQUIVALENCE.areExpressionsEquivalent(TEST_SESSION, leftExpression, rightExpression, types), String.format("Expected (%s) and (%s) to not be equivalent", left, right)); assertFalse( EQUIVALENCE.areExpressionsEquivalent(TEST_SESSION, rightExpression, leftExpression, types), String.format("Expected (%s) and (%s) to not be equivalent", right, left)); }
@Test public void testMetadataIsClearedAfterQueryFailed() { @Language("SQL") String sql = "SELECT nationkey/0 FROM nation"; // will raise division by zero exception try { queryRunner.execute(sql); fail("expected exception"); } catch (Throwable t) { // query should fail } assertEquals(metadataManager.getCatalogsByQueryId().size(), 0); }
protected static void assertQueryReturnsEmptyResult(QueryRunner queryRunner, Session session, @Language("SQL") String sql) { try { MaterializedResult results = queryRunner.execute(session, sql).toTestTypes(); assertNotNull(results); assertEquals(results.getRowCount(), 0); } catch (RuntimeException ex) { fail("Execution of query failed: " + sql, ex); } }
@Test public void testMetadataIsClearedAfterQueryFinished() { @Language("SQL") String sql = "SELECT * FROM nation"; queryRunner.execute(sql); assertEquals(metadataManager.getCatalogsByQueryId().size(), 0); }
@Test public void testMetadataDelete() @Language("SQL") String createTable = "" + "CREATE TABLE test_metadata_delete " + "(" + getQueryRunner().execute("DELETE FROM test_metadata_delete WHERE ORDER_KEY=1"); fail("expected exception"); assertFalse(getQueryRunner().tableExists(getSession(), "test_metadata_delete"));
private void assertCallFails(@Language("SQL") String sql, String message) { tester.reset(); try { assertUpdate(sql); fail("expected exception"); } catch (RuntimeException e) { assertFalse(tester.wasCalled()); assertEquals(e.getMessage(), message); } }