@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-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 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 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 // 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 // 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-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 // 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 // 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 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 // 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 public void testImpalaParquetBinaryAsTimeStamp_DictChange() throws Exception { final String WORKING_PATH = TestTools.getWorkingPath(); final String TEST_RES_PATH = WORKING_PATH + "/src/test/resources"; testBuilder() .sqlQuery("select int96_ts from dfs.\"%s/parquet/int96_dict_change\" order by int96_ts", TEST_RES_PATH) .ordered() .csvBaselineFile("testframework/testParquetReader/testInt96DictChange/q1.tsv") .baselineTypes(TypeProtos.MinorType.TIMESTAMP) .baselineColumns("int96_ts") .build().run(); }
@Test public void testCSVVerification() throws Exception { testBuilder() .sqlQuery("select employee_id, first_name, last_name from cp.\"testframework/small_test_data.json\"") .ordered() .csvBaselineFile("testframework/small_test_data.tsv") .baselineTypes(MinorType.BIGINT, MinorType.VARCHAR, MinorType.VARCHAR) .baselineColumns("employee_id", "first_name", "last_name") .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 // 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 // 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 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 // see DRILL-1825 @Ignore public void testSelStarWithAdditionalColumnLimit() throws Exception { testBuilder() .sqlQuery("select *, n_nationkey, *, n_name from cp.\"tpch/nation.parquet\" limit 2") .ordered() .csvBaselineFile("testframework/testStarQueries/testSelStarWithAdditionalColumnLimit/q1.tsv") .baselineTypes(MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.VARCHAR) .baselineColumns("n_nationkey", "n_name", "n_regionkey", "n_comment", "n_nationkey0", "n_nationkey1", "n_name0", "n_regionkey0", "n_comment0", "n_name1") .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 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(); }