@Test @Ignore("changing schema") public void testDiffDataTypesAndModes() throws Exception { test("use dfs_test.tmp"); test("create view nation_view_testunion as select n_name, n_nationkey from cp.\"tpch/nation.parquet\";"); test("create view region_view_testunion as select r_name, r_regionkey from cp.\"tpch/region.parquet\";"); String t1 = "(select n_comment, n_regionkey from cp.\"tpch/nation.parquet\" limit 5)"; String t2 = "(select * from nation_view_testunion limit 5)"; String t3 = "(select full_name, store_id from cp.\"employee.json\" limit 5)"; String t4 = "(select * from region_view_testunion limit 5)"; String query1 = t1 + " union " + t2 + " union " + t3 + " union " + t4; try { testBuilder() .sqlQuery(query1) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q13.tsv") .baselineTypes(MinorType.VARCHAR, MinorType.BIGINT) .baselineColumns("n_comment", "n_regionkey") .build() .run(); } finally { test("drop view nation_view_testunion"); test("drop view region_view_testunion"); } }
@Test // Union-Distinct of * column from JSON files in different directories public void testUnionDistinct9() 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 \n" + "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/unionDistinct/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(expected = UserException.class) public void testUnionDistinctImplicitCastingFailure() 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\" \n" + "union \n" + "select key from dfs_test.\"%s\" )", rootInt, rootBoolean); test(query); }
@Test @Ignore public void testUnionDistinctLeftEmptyJson() throws Exception { final String rootEmpty = FileUtils.getResourceAsFile("/project/pushdown/empty.json").toURI().toString(); final String rootSimple = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString(); final String queryLeftEmpty = String.format( "select key from dfs_test.\"%s\" " + "union " + "select key from dfs_test.\"%s\"", rootEmpty, rootSimple); testBuilder() .sqlQuery(queryLeftEmpty) .unOrdered() .baselineColumns("key") .baselineValues(true) .baselineValues(false) .build() .run(); }
@Test(expected = UnsupportedRelOperatorException.class) @Ignore public void testUnionDistinctViewUnExpandableStar() throws Exception { test("use dfs_test.tmp"); test("create view nation_view_testunion as select * from cp.\"tpch/nation.parquet\";"); try { String query = "(select * from dfs_test.tmp.\"nation_view_testunion\") \n" + "union (select * from cp.\"tpch/region.parquet\")"; test(query); } catch(UserException ex) { SqlUnsupportedException.errorClassNameToException(ex.getOrCreatePBError(false).getException().getExceptionClass()); throw ex; } finally { test("drop view nation_view_testunion"); } }
@Test @Ignore public void testUnionDistinctRightEmptyJson() throws Exception { String rootEmpty = FileUtils.getResourceAsFile("/project/pushdown/empty.json").toURI().toString(); String rootSimple = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString(); String queryRightEmpty = String.format( "select key from dfs_test.\"%s\" \n" + "union \n" + "select key from dfs_test.\"%s\"", rootSimple, rootEmpty); testBuilder() .sqlQuery(queryRightEmpty) .unOrdered() .baselineColumns("key") .baselineValues(true) .baselineValues(false) .build() .run(); }
@Test // DRILL-4147 // union-distinct 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 distinct SELECT o_custkey FROM dfs_test.\"%s\"", 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 public void testUnionDistinctRightEmptyBatch() throws Exception { String rootSimple = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString(); String queryRightEmptyBatch = String.format( "select key from dfs_test.\"%s\" " + "union " + "select key from dfs_test.\"%s\" where 1 = 0", rootSimple, rootSimple); testBuilder() .sqlQuery(queryRightEmptyBatch) .unOrdered() .baselineColumns("key") .baselineValues(true) .baselineValues(false) .build().run(); }
@Test public void testUnionDistinctViewExpandableStar() throws Exception { test("use dfs_test"); test("create view nation_view_testunion as select n_name, n_nationkey from cp.\"tpch/nation.parquet\";"); test("create view region_view_testunion as select r_name, r_regionkey from cp.\"tpch/region.parquet\";"); testBuilder() .sqlQuery(query1) .unOrdered() .run(); testBuilder() .sqlQuery(query2) .unOrdered() .run(); } finally { test("drop view nation_view_testunion"); test("drop view region_view_testunion");
@Test @Ignore public void testUnionDistinctBothEmptyJson() throws Exception { final String rootEmpty = FileUtils.getResourceAsFile("/project/pushdown/empty.json").toURI().toString(); final String query = String.format( "select key from dfs_test.\"%s\" " + "union " + "select key from dfs_test.\"%s\"", rootEmpty, rootEmpty); final List<Pair<SchemaPath, MajorType>> expectedSchema = Lists.newArrayList(); final MajorType majorType = TypeProtos.MajorType.newBuilder() .setMinorType(TypeProtos.MinorType.INT) .setMode(TypeProtos.DataMode.OPTIONAL) .build(); expectedSchema.add(Pair.of(SchemaPath.getSimplePath("key"), majorType)); testBuilder() .sqlQuery(query) .schemaBaseLine(expectedSchema) .build() .run(); }
@Ignore("DX-4180") @Test public void testUnionDistinctBothEmptyBatch() 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 " + "select key from dfs_test.\"%s\" where 1 = 0", rootSimple, rootSimple); final List<Pair<SchemaPath, MajorType>> expectedSchema = Lists.newArrayList(); final MajorType majorType = TypeProtos.MajorType.newBuilder() .setMinorType(TypeProtos.MinorType.INT) .setMode(TypeProtos.DataMode.OPTIONAL) .build(); expectedSchema.add(Pair.of(SchemaPath.getSimplePath("key"), majorType)); testBuilder() .sqlQuery(query) .schemaBaseLine(expectedSchema) .build() .run(); }
@Test public void testUnionDistinctLeftEmptyBatch() 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 " + "select key from dfs_test.\"%s\"", rootSimple, rootSimple); testBuilder() .sqlQuery(queryLeftBatch) .unOrdered() .baselineColumns("key") .baselineValues(true) .baselineValues(false) .build() .run(); }
@Ignore("DX-4180") @Test public void testProjectDownOverUnionDistinctImplicitCasting() 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 select columns[0], columns[1], columns[2] from dfs.\"%s\") \n" + "order by col limit 10", root); testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/testProjectDownOverUnionDistinctImplicitCasting.tsv") .baselineTypes(MinorType.INT) .baselineColumns("col") .build() .run(); }
@Ignore("DX-4180") @Test public void testFilterPushDownOverUnionDistinctCSV() throws Exception { String root = FileUtils.getResourceAsFile("/multilevel/csv/1994/Q1/orders_94_q1.csv").toURI().toString(); String query = String.format("select ct \n" + "from ((select count(c1) as ct from (select columns[0] c1 from dfs.\"%s\")) \n" + "union \n" + "(select columns[0] c2 from dfs.\"%s\")) \n" + "where ct < 100", root, root); testBuilder() .sqlQuery(query) .ordered() .baselineColumns("ct") .baselineValues((long) 10) .baselineValues((long) 66) .baselineValues((long) 99) .build() .run(); }
"select max(key) as key from dfs_test.\"%s\"", rootDate, rootTimpStmp); testBuilder() .sqlQuery(query1) .unOrdered() .run(); testBuilder() .sqlQuery(query2) .unOrdered() .run(); testBuilder() .sqlQuery(query3) .unOrdered()
"group by calc1 order by calc1)", root, root); testBuilder() .sqlQuery(query1) .unOrdered() .run(); testBuilder() .sqlQuery(query2) .unOrdered()
@Test public void testFilterPushDownOverUnionDistinct() throws Exception { String query = "select n_regionkey from \n" + "(select n_regionkey from cp.\"tpch/nation.parquet\" union select r_regionkey from cp.\"tpch/region.parquet\") \n" + "where n_regionkey > 0 and n_regionkey < 2 \n" + "order by n_regionkey"; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("n_regionkey") .baselineValues(1) .build() .run(); }
@Test // Simple Union over two scans public void testUnionDistinct1() throws Exception { String query = "(select n_regionkey from cp.\"tpch/nation.parquet\") union (select r_regionkey from cp.\"tpch/region.parquet\")"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q1.tsv") .baselineTypes(MinorType.INT) .baselineColumns("n_regionkey") .build() .run(); }
@Test 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 select r_regionkey, r_name, r_comment from cp.\"tpch/region.parquet\")"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/testProjectPushDownProjectColumnReorderingAndAlias.tsv") .baselineTypes(MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR) .baselineColumns("col1", "col2", "col3") .build() .run(); }
@Test // Union-Distinct of two string literals of different lengths public void testUnionDistinct7() throws Exception { String query = "select 'abc' as col from cp.\"tpch/region.parquet\" union \n" + "select 'abcdefgh' from cp.\"tpch/region.parquet\""; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q7.tsv") .baselineTypes(MinorType.VARCHAR) .baselineColumns("col") .build() .run(); }