@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 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(); }
@Test // Union-Distinct of two character columns of different lengths public void testUnionDistinct8() throws Exception { String query = "select n_name, n_nationkey from cp.\"tpch/nation.parquet\" union \n" + "select r_comment, r_regionkey from cp.\"tpch/region.parquet\""; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q8.tsv") .baselineTypes(MinorType.VARCHAR, MinorType.INT) .baselineColumns("n_name", "n_nationkey") .build() .run(); }
@Test public void testUnionDistinctContainsColumnANumericConstant() throws Exception { String query = "(select n_nationkey, n_regionkey, n_name from cp.\"tpch/nation.parquet\" limit 5) \n" + "union \n" + "(select 1, n_regionkey, 'abc' from cp.\"tpch/nation.parquet\" limit 5)"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q15.tsv") .baselineTypes(MinorType.INT, MinorType.INT, MinorType.VARCHAR) .baselineColumns("n_nationkey", "n_regionkey", "n_name") .build().run(); }
@Test // Union-Distinct where same column is projected twice in left and right child public void testUnionDistinct6_1() throws Exception { String query = "select n_nationkey, n_nationkey from cp.\"tpch/nation.parquet\" union \n" + "select r_regionkey, r_regionkey from cp.\"tpch/region.parquet\""; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q6_1.tsv") .baselineTypes(MinorType.INT, MinorType.INT) .baselineColumns("n_nationkey", "n_nationkey1") .build() .run(); }
@Test // Chain of Unions public void testUnionDistinct4() throws Exception { String query = "select n_regionkey from cp.\"tpch/nation.parquet\" \n" + "union select r_regionkey from cp.\"tpch/region.parquet\" \n" + "union select n_nationkey from cp.\"tpch/nation.parquet\" \n" + "union select c_custkey from cp.\"tpch/customer.parquet\" where c_custkey < 5"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q4.tsv") .baselineTypes(MinorType.INT) .baselineColumns("n_regionkey") .build() .run(); }
@Test // Union over grouped aggregates public void testUnionDistinct3() 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 \n" + "union \n" + "select r1.r_regionkey from cp.\"tpch/region.parquet\" r1 group by r1.r_regionkey"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q3.tsv") .baselineTypes(MinorType.INT) .baselineColumns("n_nationkey") .build() .run(); }
@Test // Union of all columns in the table public void testUnionDistinct5() throws Exception { String query = "select r_name, r_comment, r_regionkey from cp.\"tpch/region.parquet\" r1 \n" + "union \n" + "select r_name, r_comment, r_regionkey from cp.\"tpch/region.parquet\" r2"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q5.tsv") .baselineTypes(MinorType.VARCHAR, MinorType.VARCHAR, MinorType.INT) .baselineColumns("r_name", "r_comment", "r_regionkey") .build() .run(); }
@Test public void testProjectPushDownOverUnionDistinctWithProject() throws Exception { String query = "select n_nationkey, n_name 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/testProjectPushDownOverUnionDistinctWithProject.tsv") .baselineTypes(MinorType.INT, MinorType.VARCHAR) .baselineColumns("n_nationkey", "n_name") .build() .run(); }
@Test public void testProjectPushDownOverUnionDistinctWithoutProject() throws Exception { String query = "select n_nationkey 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/testProjectPushDownOverUnionDistinctWithoutProject.tsv") .baselineTypes(MinorType.INT) .baselineColumns("n_nationkey") .build() .run(); }
@Test // Union over inner joins public void testUnionDistinct2() 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) \n" + "union \n" + "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 (1, 2, 3, 4)"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q2.tsv") .baselineTypes(MinorType.INT) .baselineColumns("n_nationkey") .build() .run(); }
@Test // Union-Distinct where same column is projected twice in right child public void testUnionDistinct6() throws Exception { String query = "select n_nationkey, n_regionkey from cp.\"tpch/nation.parquet\" where n_regionkey = 1 \n" + "union \n" + "select r_regionkey, r_regionkey from cp.\"tpch/region.parquet\" where r_regionkey = 2"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q6.tsv") .baselineTypes(MinorType.INT, MinorType.INT) .baselineColumns("n_nationkey", "n_regionkey") .build() .run(); }
@Test public void testDistinctOverUnionDistinctwithFullyQualifiedColumnNames() throws Exception { String query = "select distinct sq.x1, sq.x2 \n" + "from \n" + "((select n_regionkey as a1, n_name as b1 from cp.\"tpch/nation.parquet\") \n" + "union \n" + "(select r_regionkey as a2, r_name as b2 from cp.\"tpch/region.parquet\")) as sq(x1,x2)"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q14.tsv") .baselineTypes(MinorType.INT, MinorType.VARCHAR) .baselineColumns("x1", "x2") .build() .run(); }
@Test public void testProjectFiltertPushDownOverUnionDistinct() throws Exception { String query = "select n_nationkey 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\") \n" + "where n_nationkey > 0 and n_nationkey < 4"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/testProjectFiltertPushDownOverUnionDistinct.tsv") .baselineTypes(MinorType.INT) .baselineColumns("n_nationkey") .build() .run(); }
@Test public void testProjectWithExpressionPushDownOverUnionDistinct() 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 select r_regionkey, r_name, r_comment from cp.\"tpch/region.parquet\")"; // Validate the result testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/testProjectWithExpressionPushDownOverUnionDistinct.tsv") .baselineTypes(MinorType.INT) .baselineColumns("col") .build() .run(); }
@Test public void testInListPushDownOverUnionDistinct() throws Exception { String query = "select n_nationkey \n" + "from (select n1.n_nationkey from cp.\"tpch/nation.parquet\" n1 inner join cp.\"tpch/region.parquet\" r1 on n1.n_regionkey = r1.r_regionkey \n" + "union \n" + "select n2.n_nationkey from cp.\"tpch/nation.parquet\" n2 inner join cp.\"tpch/region.parquet\" r2 on n2.n_regionkey = r2.r_regionkey) \n" + "where n_nationkey in (1, 2)"; testBuilder() .sqlQuery(query) .unOrdered() .baselineColumns("n_nationkey") .baselineValues(1) .baselineValues(2) .build() .run(); }
@Test // Union-Distinct constant literals @Ignore public void testUnionDistinct10() throws Exception { String query = "(select n_name, 'LEFT' as LiteralConstant, n_nationkey, '1' as NumberConstant from cp.\"tpch/nation.parquet\") \n" + "union \n" + "(select 'RIGHT', r_name, '2', r_regionkey from cp.\"tpch/region.parquet\")"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/unionDistinct/q10.tsv") .baselineTypes(MinorType.VARCHAR, MinorType.VARCHAR, MinorType.INT, MinorType.INT) .baselineColumns("n_name", "LiteralConstant", "n_nationkey", "NumberConstant") .build() .run(); }