@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 // 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 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 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 // 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(); }
@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(); }
@Test // Union-All where same column is projected twice in right child public void testUnionAll6() throws Exception { String query = "select n_nationkey, n_regionkey from cp.\"tpch/nation.parquet\" where n_regionkey = 1 union all select r_regionkey, r_regionkey from cp.\"tpch/region.parquet\" where r_regionkey = 2"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q6.tsv") .baselineTypes(MinorType.INT, MinorType.INT) .baselineColumns("n_nationkey", "n_regionkey") .build().run(); }
@Test // Union-All where same column is projected twice in left and right child public void testUnionAll6_1() throws Exception { String query = "select n_nationkey, n_nationkey from cp.\"tpch/nation.parquet\" union all select r_regionkey, r_regionkey from cp.\"tpch/region.parquet\""; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q6_1.tsv") .baselineTypes(MinorType.INT, MinorType.INT) .baselineColumns("n_nationkey", "n_nationkey1") .build().run(); }
@Test // Union-All of all columns in the table public void testUnionAll5() throws Exception { String query = "select r_name, r_comment, r_regionkey from cp.\"tpch/region.parquet\" r1 " + "union all " + "select r_name, r_comment, r_regionkey from cp.\"tpch/region.parquet\" r2"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q5.tsv") .baselineTypes(MinorType.VARCHAR, MinorType.VARCHAR, MinorType.INT) .baselineColumns("r_name", "r_comment", "r_regionkey") .build().run(); }
@Test // see DRILL-1923 public void testUnionAllContainsColumnANumericConstant() throws Exception { String query = "(select n_nationkey, n_regionkey, n_name from cp.\"tpch/nation.parquet\" limit 5) " + "union all " + "(select 1, n_regionkey, 'abc' from cp.\"tpch/nation.parquet\" limit 5)"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q15.tsv") .baselineTypes(MinorType.INT, MinorType.INT, MinorType.VARCHAR) .baselineColumns("n_nationkey", "n_regionkey", "n_name") .build().run(); }
@Test // Union All constant literals public void testUnionAll10() throws Exception { String query = "(select n_name, 'LEFT' as LiteralConstant, n_nationkey, 1 as NumberConstant from cp.\"tpch/nation.parquet\") " + "union all " + "(select 'RIGHT', r_name, 2, r_regionkey from cp.\"tpch/region.parquet\")"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q10.tsv") .baselineTypes(MinorType.VARCHAR, MinorType.VARCHAR, MinorType.INT, MinorType.INT) .baselineColumns("n_name", "LiteralConstant", "n_nationkey", "NumberConstant") .build().run(); }
@Test // Union-All over inner joins public void testUnionAll2() throws Exception { String query = "select n1.n_nationkey from cp.\"tpch/nation.parquet\" n1 inner join cp.\"tpch/region.parquet\" r1 on n1.n_regionkey = r1.r_regionkey where n1.n_nationkey in (1, 2) " + "union all " + "select n2.n_nationkey from cp.\"tpch/nation.parquet\" n2 inner join cp.\"tpch/region.parquet\" r2 on n2.n_regionkey = r2.r_regionkey where n2.n_nationkey in (3, 4)"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q2.tsv") .baselineTypes(MinorType.INT) .baselineColumns("n_nationkey") .build().run(); }
@Test // see DRILL-2203 public void testDistinctOverUnionAllwithFullyQualifiedColumnNames() throws Exception { String query = "select distinct sq.x1, sq.x2 " + "from " + "((select n_regionkey as a1, n_name as b1 from cp.\"tpch/nation.parquet\") " + "union all " + "(select r_regionkey as a2, r_name as b2 from cp.\"tpch/region.parquet\")) as sq(x1,x2)"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q14.tsv") .baselineTypes(MinorType.INT, MinorType.VARCHAR) .baselineColumns("x1", "x2") .build().run(); }
@Test // see DRILL-2207 public void testUnionAllEmptySides() throws Exception { String query1 = "(select n_nationkey, n_regionkey, n_name from cp.\"tpch/nation.parquet\" limit 0) " + "union all " + "(select 1, n_regionkey, 'abc' from cp.\"tpch/nation.parquet\" limit 5)"; String query2 = "(select n_nationkey, n_regionkey, n_name from cp.\"tpch/nation.parquet\" limit 5) " + "union all " + "(select 1, n_regionkey, 'abc' from cp.\"tpch/nation.parquet\" limit 0)"; testBuilder() .sqlQuery(query1) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q16.tsv") .baselineTypes(MinorType.INT, MinorType.INT, MinorType.VARCHAR) .baselineColumns("n_nationkey", "n_regionkey", "n_name") .build().run(); testBuilder() .sqlQuery(query2) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q17.tsv") .baselineTypes(MinorType.INT, MinorType.INT, MinorType.VARCHAR) .baselineColumns("n_nationkey", "n_regionkey", "n_name") .build().run(); }
@Test // see DRILL-3130 public void testProjectPushDownOverUnionAllWithoutProject() throws Exception { String query = "select n_nationkey from \n" + "(select n_nationkey, n_comment from cp.\"tpch/nation.parquet\" \n" + "union all select r_regionkey, r_comment from cp.\"tpch/region.parquet\")"; // Validate the plan final String[] expectedPlan = {"Project\\(n_nationkey=\\[\\$0\\]\\).*\n" + ".*UnionAll.*\n" + ".*Scan.*columns=\\[`n_nationkey`\\].*\n" + ".*Scan.*columns=\\[`r_regionkey`\\].*"}; final String[] excludedPlan = {}; PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan); // Validate the result testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/testProjectPushDownOverUnionAllWithoutProject.tsv") .baselineTypes(MinorType.INT) .baselineColumns("n_nationkey") .build() .run(); }
@Test // see DRILL-3130 public void testProjectPushDownProjectColumnReorderingAndAlias() throws Exception { String query = "select n_comment as col1, n_nationkey as col2, n_name as col3 from \n" + "(select n_nationkey, n_name, n_comment from cp.\"tpch/nation.parquet\" \n" + "union all select r_regionkey, r_name, r_comment from cp.\"tpch/region.parquet\")"; // Validate the plan final String[] expectedPlan = { "Scan.*columns=\\[`n_nationkey`, `n_name`, `n_comment`\\]", "Scan.*columns=\\[`r_regionkey`, `r_name`, `r_comment`\\]"}; final String[] excludedPlan = {}; PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan); // Validate the result testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/testProjectPushDownProjectColumnReorderingAndAlias.tsv") .baselineTypes(MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR) .baselineColumns("col1", "col2", "col3") .build() .run(); }
@Test // see DRILL-3130 public void testProjectWithExpressionPushDownOverUnionAll() throws Exception { String query = "select 2 * n_nationkey as col from \n" + "(select n_nationkey, n_name, n_comment from cp.\"tpch/nation.parquet\" \n" + "union all select r_regionkey, r_name, r_comment from cp.\"tpch/region.parquet\")"; // Validate the plan final String[] expectedPlan = { "Scan.*columns=\\[`n_nationkey`\\]", "Scan.*columns=\\[`r_regionkey`\\]" }; final String[] excludedPlan = {}; PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan); // Validate the result testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/testProjectWithExpressionPushDownOverUnionAll.tsv") .baselineTypes(MinorType.INT) .baselineColumns("col") .build() .run(); }