Refine search
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 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())));
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)); }
@Test public void testCustomRank() { @Language("SQL") String sql = "" + "SELECT orderstatus, clerk, sales\n" + ", custom_rank() OVER (PARTITION BY orderstatus ORDER BY sales DESC) rnk\n" + "FROM (\n" + " SELECT orderstatus, clerk, sum(totalprice) sales\n" + " FROM orders\n" + " GROUP BY orderstatus, clerk\n" + ")\n" + "ORDER BY orderstatus, clerk"; assertEquals(computeActual(sql), computeActual(sql.replace("custom_rank", "rank"))); }
@Test public void testNotEliminateSorts() { @Language("SQL") String sql = "SELECT quantity, row_number() OVER (ORDER BY quantity) FROM lineitem ORDER BY tax"; PlanMatchPattern pattern = anyTree( sort( anyTree( window(windowMatcherBuilder -> windowMatcherBuilder .specification(windowSpec) .addFunction(functionCall("row_number", Optional.empty(), ImmutableList.of())), anyTree(LINEITEM_TABLESCAN_Q))))); assertUnitPlan(sql, pattern); }
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); } }
@Test public void testPartitionedTablesFailWithAvroSchemaUrl() throws Exception { @Language("SQL") String createSql = "CREATE TABLE create_avro (dummy VARCHAR)\n" + "WITH (avro_schema_url = 'dummy_schema',\n" + " partitioned_by=ARRAY['dummy'])"; assertQueryFails(createSql, "Bucketing/Partitioning columns not supported when Avro schema url is set"); }
@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)))); }
protected void assertMinimallyOptimizedPlan(@Language("SQL") String sql, PlanMatchPattern pattern) { List<PlanOptimizer> optimizers = ImmutableList.of( new UnaliasSymbolReferences(), new PruneUnreferencedOutputs(), new IterativeOptimizer( new RuleStatsRecorder(), queryRunner.getStatsCalculator(), queryRunner.getCostCalculator(), ImmutableSet.of(new RemoveRedundantIdentityProjections()))); assertPlan(sql, LogicalPlanner.Stage.OPTIMIZED, pattern, optimizers); }
@Test public void testTooManyStages() { @Language("SQL") String query = "WITH\n" + " t1 AS (SELECT nationkey AS x FROM nation where name='UNITED STATES'),\n" + " t2 AS (SELECT a.x+b.x+c.x+d.x AS x FROM t1 a, t1 b, t1 c, t1 d),\n" + " t3 AS (SELECT a.x+b.x+c.x+d.x AS x FROM t2 a, t2 b, t2 c, t2 d),\n" + " t4 AS (SELECT a.x+b.x+c.x+d.x AS x FROM t3 a, t3 b, t3 c, t3 d),\n" + " t5 AS (SELECT a.x+b.x+c.x+d.x AS x FROM t4 a, t4 b, t4 c, t4 d)\n" + "SELECT x FROM t5\n"; assertQueryFails(query, "Number of stages in the query \\([0-9]+\\) exceeds the allowed maximum \\([0-9]+\\).*"); }
@Test public void testIdenticalWindowSpecificationsDefaultFrame() { ExpectedValueProvider<WindowNode.Specification> specificationC = specification( ImmutableList.of(SUPPKEY_ALIAS), ImmutableList.of(ORDERKEY_ALIAS), ImmutableMap.of(ORDERKEY_ALIAS, SortOrder.ASC_NULLS_LAST)); ExpectedValueProvider<WindowNode.Specification> specificationD = specification( ImmutableList.of(ORDERKEY_ALIAS), ImmutableList.of(SHIPDATE_ALIAS), ImmutableMap.of(SHIPDATE_ALIAS, SortOrder.ASC_NULLS_LAST)); @Language("SQL") String sql = "SELECT " + "SUM(quantity) OVER (PARTITION By suppkey ORDER BY orderkey), " + "SUM(quantity) OVER (PARTITION BY orderkey ORDER BY shipdate), " + "SUM(discount) OVER (PARTITION BY suppkey ORDER BY orderkey) " + "FROM lineitem"; assertUnitPlan(sql, anyTree( window(windowMatcherBuilder -> windowMatcherBuilder .specification(specificationC) .addFunction(functionCall("sum", UNSPECIFIED_FRAME, ImmutableList.of(QUANTITY_ALIAS))) .addFunction(functionCall("sum", UNSPECIFIED_FRAME, ImmutableList.of(DISCOUNT_ALIAS))), window(windowMatcherBuilder -> windowMatcherBuilder .specification(specificationD) .addFunction(functionCall("sum", UNSPECIFIED_FRAME, ImmutableList.of(QUANTITY_ALIAS))), LINEITEM_TABLESCAN_DOQSS)))); }
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)); }
@Test public void testBucketedTablesFailWithAvroSchemaUrl() throws Exception { @Language("SQL") String createSql = "CREATE TABLE create_avro (dummy VARCHAR)\n" + "WITH (avro_schema_url = 'dummy_schema',\n" + " bucket_count = 2, bucketed_by=ARRAY['dummy'])"; assertQueryFails(createSql, "Bucketing/Partitioning columns not supported when Avro schema url is set"); }
@Test public void testNotMergeDifferentOrderBy() { @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 suppkey ORDER BY quantity ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_quantity_C " + "FROM lineitem"; ExpectedValueProvider<WindowNode.Specification> specificationC = specification( ImmutableList.of(SUPPKEY_ALIAS), ImmutableList.of(QUANTITY_ALIAS), ImmutableMap.of(QUANTITY_ALIAS, SortOrder.ASC_NULLS_LAST)); assertUnitPlan(sql, anyTree( window(windowMatcherBuilder -> windowMatcherBuilder .specification(specificationC) .addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(QUANTITY_ALIAS))), window(windowMatcherBuilder -> windowMatcherBuilder .specification(specificationA) .addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(DISCOUNT_ALIAS))), LINEITEM_TABLESCAN_DOQS)))); }
private void testPathHiddenColumn(Session session, HiveStorageFormat storageFormat) @Language("SQL") String createTable = "CREATE TABLE test_path " + "WITH (" + "format = '" + storageFormat + "'," + assertEquals(tableMetadata.getMetadata().getProperties().get(STORAGE_FORMAT_PROPERTY), storageFormat); List<String> columnNames = ImmutableList.of("col0", "col1", PATH_COLUMN_NAME); List<ColumnMetadata> columnMetadatas = tableMetadata.getColumns(); assertEquals(columnMetadatas.size(), columnNames.size());
@Test(expectedExceptions = RuntimeException.class, expectedExceptionsMessageRegExp = "line 1:1: Destination table 'memory.default.nation' already exists") public void testCreateTableWhenTableIsAlreadyCreated() { @Language("SQL") String createTableSql = "CREATE TABLE nation AS SELECT * FROM tpch.tiny.nation"; assertUpdate(createTableSql); }
@Test public void testNotMergeDifferentOrdering() { @Language("SQL") String sql = "SELECT " + "SUM(extendedprice) OVER (PARTITION BY suppkey ORDER BY orderkey ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_extendedprice_A, " + "SUM(quantity) over (PARTITION BY suppkey ORDER BY orderkey DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_quantity_C, " + "SUM(discount) over (PARTITION BY suppkey ORDER BY orderkey ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_discount_A " + "FROM lineitem"; ExpectedValueProvider<WindowNode.Specification> specificationC = specification( ImmutableList.of(SUPPKEY_ALIAS), ImmutableList.of(ORDERKEY_ALIAS), ImmutableMap.of(ORDERKEY_ALIAS, SortOrder.DESC_NULLS_LAST)); assertUnitPlan(sql, anyTree( window(windowMatcherBuilder -> windowMatcherBuilder .specification(specificationC) .addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(QUANTITY_ALIAS))), window(windowMatcherBuilder -> windowMatcherBuilder .specification(specificationA) .addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(EXTENDEDPRICE_ALIAS))) .addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(DISCOUNT_ALIAS))), LINEITEM_TABLESCAN_DEOQS)))); }
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")); }