@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 testLagWithPby() throws Exception { testBuilder() .sqlQuery(getFile("window/lag.pby.oby.sql"), TEST_RES_PATH) .ordered() .csvBaselineFile("window/b4.p4.lag.pby.oby.tsv") .baselineColumns("lag") .build() .run(); }
@Test // DRILL-3605 public void testFix3605() throws Exception { testBuilder() .sqlQuery(getFile("window/3605.sql"), TEST_RES_PATH) .ordered() .csvBaselineFile("window/3605.tsv") .baselineColumns("col2", "lead_col2") .build() .run(); }
@Test public void testCSVVerificationOfUnorderedComparison() throws Throwable { testBuilder() .sqlQuery("select columns[0] as employee_id, columns[1] as first_name, columns[2] as last_name from cp.\"testframework/small_test_data_reordered.tsv\"") .unOrdered() .csvBaselineFile("testframework/small_test_data.tsv") .baselineColumns("employee_id", "first_name", "last_name") .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 public void testNtile() throws Exception { testBuilder() .sqlQuery(getFile("window/ntile.sql"), TEST_RES_PATH) .ordered() .csvBaselineFile("window/b2.p4.ntile.tsv") .baselineColumns("ntile") .build() .run(); }
@Test public void testLag() throws Exception { testBuilder() .sqlQuery(getFile("window/lag.oby.sql"), TEST_RES_PATH) .ordered() .csvBaselineFile("window/b4.p4.lag.oby.tsv") .baselineColumns("lag") .build() .run(); }
@Test // DRILL-3606 public void testFix3606() throws Exception { testBuilder() .sqlQuery(getFile("window/3606.sql"), TEST_RES_PATH) .ordered() .csvBaselineFile("window/3606.tsv") .baselineColumns("col2", "lead_col2") .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 public void testSelectWithLimitOffset() throws Exception{ testBuilder() .sqlQuery("select employee_id, first_name, last_name from cp.\"employee.json\" order by employee_id limit 5 offset 10 ") .ordered() .csvBaselineFile("sort/testSelectWithLimitOffset.tsv") .baselineColumns("employee_id", "first_name", "last_name") .build().run(); }
@Test public void testFirstValue() throws Exception { testBuilder() .sqlQuery(getFile("window/fval.pby.sql"), TEST_RES_PATH) .ordered() .csvBaselineFile("window/b4.p4.fval.pby.tsv") .baselineColumns("first_value") .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 public void test3648Fix() throws Exception { testBuilder() .sqlQuery(getFile("window/3648.sql"), TEST_RES_PATH) .ordered() .csvBaselineFile("window/3648.tsv") .baselineColumns("ntile") .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(); }
@Test public void testLeadWithPby() throws Exception { testBuilder() .sqlQuery(getFile("window/lead.pby.oby.sql"), TEST_RES_PATH) .ordered() .csvBaselineFile("window/b4.p4.lead.pby.oby.tsv") .baselineColumns("lead") .build() .run(); }