test(sliceTargetSmall); test(enableDistribute); testBuilder() .optionSettingQueriesForTestQuery(sliceTargetSmall) .optionSettingQueriesForBaseline(sliceTargetDefault) .go(); test(sliceTargetSmall); testBuilder() .optionSettingQueriesForTestQuery(enableRoundRobinUnionAll) .optionSettingQueriesForBaseline(disableRoundRobinUnionAll) test(sliceTargetDefault); test(defaultDistribute); test(enableRoundRobinUnionAll);
@Ignore("DX-4180") @Test public void testUnionAllBothEmptyBatch() throws Exception { String rootSimple = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString(); final String query = String.format( "select key from dfs_test.\"%s\" where 1 = 0 " + "union all " + "select key from dfs_test.\"%s\" where 1 = 0", rootSimple, rootSimple); final List<Pair<SchemaPath, MajorType>> expectedSchema = Lists.newArrayList(); final MajorType majorType = Types.optional(MinorType.INT); expectedSchema.add(Pair.of(SchemaPath.getSimplePath("key"), majorType)); testBuilder() .sqlQuery(query) .schemaBaseLine(expectedSchema) .build() .run(); }
@Test(expected = UserException.class) // see DRILL-2590 public void testUnionAllImplicitCastingFailure() throws Exception { String rootInt = FileUtils.getResourceAsFile("/store/json/intData.json").toURI().toString(); String rootBoolean = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString(); String query = String.format( "(select key from dfs_test.\"%s\" " + "union all " + "select key from dfs_test.\"%s\" )", rootInt, rootBoolean); test(query); }
@Test // DRILL-1905: Union-all of * column from JSON files in different directories public void testUnionAll9() throws Exception { String file0 = FileUtils.getResourceAsFile("/multilevel/json/1994/Q1/orders_94_q1.json").toURI().toString(); String file1 = FileUtils.getResourceAsFile("/multilevel/json/1995/Q1/orders_95_q1.json").toURI().toString(); String query = String.format("select o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment, o_orderkey from dfs_test.\"%s\" union all " + "select o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment, o_orderkey from dfs_test.\"%s\"", file0, file1); testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q9.tsv") .baselineTypes(MinorType.BIGINT, MinorType.VARCHAR, MinorType.FLOAT8, MinorType.VARCHAR, MinorType.VARCHAR, MinorType.VARCHAR, MinorType.BIGINT,MinorType.VARCHAR, MinorType.BIGINT) .baselineColumns("o_custkey", "o_orderstatus", "o_totalprice", "o_orderdate", "o_orderpriority", "o_clerk", "o_shippriority", "o_comment", "o_orderkey") .build().run(); }
@Test public void testUnionAllRightEmptyBatch() throws Exception { String rootSimple = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString(); String queryRightEmptyBatch = String.format( "select key from dfs_test.\"%s\" " + "union all " + "select key from dfs_test.\"%s\" where 1 = 0", rootSimple, rootSimple); testBuilder() .sqlQuery(queryRightEmptyBatch) .unOrdered() .baselineColumns("key") .baselineValues(true) .baselineValues(false) .build().run(); }
test(sliceTargetSmall); test(enableDistribute); testBuilder() .optionSettingQueriesForTestQuery(sliceTargetSmall) .optionSettingQueriesForBaseline(sliceTargetDefault) .go(); test(sliceTargetSmall); testBuilder() .optionSettingQueriesForTestQuery(enableRoundRobinUnionAll) .optionSettingQueriesForBaseline(disableRoundRobinUnionAll) test(sliceTargetDefault); test(defaultDistribute); test(enableRoundRobinUnionAll);
@Test public void testUnionAllLeftEmptyBatch() throws Exception { String rootSimple = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString(); final String queryLeftBatch = String.format( "select key from dfs_test.\"%s\" where 1 = 0 " + "union all " + "select key from dfs_test.\"%s\"", rootSimple, rootSimple); testBuilder() .sqlQuery(queryLeftBatch) .unOrdered() .baselineColumns("key") .baselineValues(true) .baselineValues(false) .build() .run(); }
@Test // DRILL-4147 // union-all above a hash join public void testDrill4147_3() throws Exception { final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString(); final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString(); final String query = String.format("SELECT o_custkey FROM \n" + " (select o1.o_custkey from dfs_test.\"%s\" o1 inner join dfs_test.\"%s\" o2 on o1.o_orderkey = o2.o_custkey) \n" + " Union All SELECT o_custkey FROM dfs_test.\"%s\" where o_custkey < 10", l, r, l); // Validate the plan final String[] expectedPlan = {"(?s)UnionExchange.*UnionAll.*HashJoin.*"}; final String[] excludedPlan = {}; try { test(sliceTargetSmall); PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan); testBuilder() .optionSettingQueriesForTestQuery(sliceTargetSmall) .optionSettingQueriesForBaseline(sliceTargetDefault) .unOrdered() .sqlQuery(query) .sqlBaselineQuery(query) .build() .run(); } finally { test(sliceTargetDefault); } }
@Test // see DRILL-1977, DRILL-2376, DRILL-2377, DRILL-2378, DRILL-2379 public void testAggregationOnUnionAllOperator() throws Exception { String root = FileUtils.getResourceAsFile("/store/text/data/t.json").toURI().toString(); String query1 = String.format( "(select calc1, max(b1) as \"max\", min(b1) as \"min\", count(c1) as \"count\" " + "from (select a1 + 10 as calc1, b1, c1 from dfs_test.\"%s\" " + "union all " + "select a1 + 100 as diff1, b1 as diff2, c1 as diff3 from dfs_test.\"%s\") " + "group by calc1 order by calc1)", root, root); String query2 = String.format( "(select calc1, min(b1) as \"min\", max(b1) as \"max\", count(c1) as \"count\" " + "from (select a1 + 10 as calc1, b1, c1 from dfs_test.\"%s\" " + "union all " + "select a1 + 100 as diff1, b1 as diff2, c1 as diff3 from dfs_test.\"%s\") " + "group by calc1 order by calc1)", root, root); testBuilder() .sqlQuery(query1) .ordered() .csvBaselineFile("testframework/testExampleQueries/testAggregationOnUnionAllOperator/q1.tsv") .baselineTypes(MinorType.BIGINT, MinorType.BIGINT, MinorType.BIGINT, MinorType.BIGINT) .baselineColumns("calc1", "max", "min", "count") .build().run(); testBuilder() .sqlQuery(query2) .ordered() .csvBaselineFile("testframework/testExampleQueries/testAggregationOnUnionAllOperator/q2.tsv") .baselineTypes(MinorType.BIGINT, MinorType.BIGINT, MinorType.BIGINT, MinorType.BIGINT) .baselineColumns("calc1", "min", "max", "count") .build().run(); }
@Test public void testUnionAllViewExpandableStar() throws Exception { test("use dfs_test"); test("create view nation_view_testunionall as select n_name, n_nationkey from cp.\"tpch/nation.parquet\";"); test("create view region_view_testunionall as select r_name, r_regionkey from cp.\"tpch/region.parquet\";"); testBuilder() .sqlQuery(query1) .unOrdered() .build().run(); testBuilder() .sqlQuery(query2) .unOrdered() .build().run(); } finally { test("drop view nation_view_testunionall"); test("drop view region_view_testunionall");
"select max(key) as key from dfs_test.\"%s\"", rootDate, rootTimpStmp); testBuilder() .sqlQuery(query1) .unOrdered() .build().run(); testBuilder() .sqlQuery(query2) .unOrdered() .build().run(); testBuilder() .sqlQuery(query3) .unOrdered()
@Test // DRILL-4147 // base case public void testDrill4147_1() throws Exception { final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString(); final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString(); final String query = String.format("SELECT o_custkey FROM dfs_test.\"%s\" \n" + "Union All SELECT o_custkey FROM dfs_test.\"%s\"", l, r); // Validate the plan final String[] expectedPlan = {"UnionExchange.*\n", ".*Project.*\n" + ".*UnionAll"}; final String[] excludedPlan = {}; try { test(sliceTargetSmall); PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan); testBuilder() .optionSettingQueriesForTestQuery(sliceTargetSmall) .optionSettingQueriesForBaseline(sliceTargetDefault) .unOrdered() .sqlQuery(query) .sqlBaselineQuery(query) .build() .run(); } finally { test(sliceTargetDefault); } }
@Ignore("DX-4180") @Test // see DRILL-3130 public void testProjectDownOverUnionAllImplicitCasting() throws Exception { String root = FileUtils.getResourceAsFile("/store/text/data/nations.csv").toURI().toString(); String query = String.format("select 2 * n_nationkey as col from \n" + "(select n_nationkey, n_name, n_comment from cp.\"tpch/nation.parquet\" \n" + "union all select columns[0], columns[1], columns[2] from dfs.\"%s\") \n" + "order by col limit 10", root); // Validate the plan final String[] expectedPlan = {"UnionAll.*\n." + ".*Project.*\n" + ".*Scan.*columns=\\[`n_nationkey`, `n_name`, `n_comment`\\].*\n" + ".*Project.*\n" + ".*Scan.*columns=\\[`columns`\\[0\\], `columns`\\[1\\], `columns`\\[2\\]\\]"}; final String[] excludedPlan = {}; PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan); // Validate the result testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/testProjectDownOverUnionAllImplicitCasting.tsv") .baselineTypes(MinorType.INT) .baselineColumns("col") .build() .run(); }
@Test // DRILL-4147 // group-by on top of union-all public void testDrill4147_2() throws Exception { final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString(); final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString(); final String query = String.format("Select o_custkey, count(*) as cnt from \n" + " (SELECT o_custkey FROM dfs_test.\"%s\" \n" + "Union All SELECT o_custkey FROM dfs_test.\"%s\") \n" + "group by o_custkey", l, r); // Validate the plan final String[] expectedPlan = {"(?s)UnionExchange.*HashAgg.*HashToRandomExchange.*UnionAll.*"}; final String[] excludedPlan = {}; try { test(sliceTargetSmall); PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan); testBuilder() .optionSettingQueriesForTestQuery(sliceTargetSmall) .optionSettingQueriesForBaseline(sliceTargetDefault) .unOrdered() .sqlQuery(query) .sqlBaselineQuery(query) .build() .run(); } finally { test(sliceTargetDefault); } }
@Test // Union-All over grouped aggregates public void testUnionAll3() throws Exception { String query = "select n1.n_nationkey from cp.\"tpch/nation.parquet\" n1 where n1.n_nationkey in (1, 2) group by n1.n_nationkey union all select r1.r_regionkey from cp.\"tpch/region.parquet\" r1 group by r1.r_regionkey"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q3.tsv") .baselineTypes(MinorType.INT) .baselineColumns("n_nationkey") .build().run(); }
" AND t_w_firstyear.year_total > 0 and t_w_secyear.year_total > 0"; testBuilder() .sqlQuery(query1) .ordered() test("alter session set planner.enable_reduce_filter=false"); testBuilder() .sqlQuery(query2) .ordered() .run(); testBuilder() .sqlQuery(query3) .ordered() .run(); testBuilder() .sqlQuery(query4) .ordered() .build() .run(); test("alter session set planner.enable_reduce_filter=true");
@Test // Chain of Union-Alls public void testUnionAll4() throws Exception { String query = "select n_regionkey from cp.\"tpch/nation.parquet\" union all select r_regionkey from cp.\"tpch/region.parquet\" union all select n_nationkey from cp.\"tpch/nation.parquet\" union all select c_custkey from cp.\"tpch/customer.parquet\" where c_custkey < 5"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q4.tsv") .baselineTypes(MinorType.INT) .baselineColumns("n_regionkey") .build().run(); }
@Test // Union-all of two string literals of different lengths public void testUnionAll7() throws Exception { String query = "select 'abc' from cp.\"tpch/region.parquet\" union all select 'abcdefgh' from cp.\"tpch/region.parquet\""; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q7.tsv") .baselineTypes(MinorType.VARCHAR) .baselineColumns("EXPR$0") .build().run(); }
@Test // Simple Union-All over two scans public void testUnionAll1() throws Exception { String query = "(select n_regionkey from cp.\"tpch/nation.parquet\") union all (select r_regionkey from cp.\"tpch/region.parquet\")"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q1.tsv") .baselineTypes(MinorType.INT) .baselineColumns("n_regionkey") .build().run(); }
@Test // Union-all of two character columns of different lengths public void testUnionAll8() throws Exception { String query = "select n_name, n_nationkey from cp.\"tpch/nation.parquet\" union all select r_comment, r_regionkey from cp.\"tpch/region.parquet\""; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q8.tsv") .baselineTypes(MinorType.VARCHAR, MinorType.INT) .baselineColumns("n_name", "n_nationkey") .build().run(); }