@Test public void testGlobSingleCharacter() throws Exception { testBuilder() .sqlQuery(String.format("select count(*) from dfs_test.\"%s/parquet/199?/*\"", MULTILEVEL)) .unOrdered() .baselineColumns("EXPR$0") .baselineValues(120L) .build().run(); }
@Test public void testSimpleQueryWithCast() throws Exception { String query = String.format("select cast(department_id as bigint) as c from %s where cast(employee_id as decimal) = 170", DATAFILE); testBuilder() .sqlQuery(query) .unOrdered() .baselineColumns("c") .baselineValues(80L) .build() .run(); }
@Test public void testDrill_2013() throws Exception { String query = "select flatten(complex), rownum from cp.\"/store/json/test_flatten_mappify2.json\" where rownum > 5"; testPlanSubstrPatterns(query, new String[] {"columns=[`rownum`, `complex`]"}, null); testBuilder() .sqlQuery(query) .expectsEmptyResultSet() .build().run(); }
private void testWithResult(String query, Object... expectedResult) throws Exception { TestBuilder builder = testBuilder() .sqlQuery(query) .ordered() .baselineColumns("columns"); for (Object o : expectedResult) { builder = builder.baselineValues(o); } builder.build().run(); }
@Test public void testCountOnNullableColumn() throws Exception { testBuilder() .sqlQuery("select count(t.x.y) as cnt1, count(\"integer\") as cnt2 from cp.\"/jsoninput/input2.json\" t") .ordered() .baselineColumns("cnt1", "cnt2") .baselineValues(3l, 4l) .build().run(); }
@Test public void testDRILL2361_JoinColumnAliasWithDots() throws Exception { testBuilder() .sqlQuery("select count(*) as cnt from (select o_custkey as \"x.y\" from cp.\"tpch/orders.parquet\") o inner join cp.\"tpch/customer.parquet\" c on o.\"x.y\" = c.c_custkey") .unOrdered() .baselineColumns("cnt") .baselineValues(15000L) .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(); }
@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 public void test3648Fix() throws Exception { testBuilder() .sqlQuery(getFile("window/3648.sql"), TEST_RES_PATH) .ordered() .csvBaselineFile("window/3648.tsv") .baselineColumns("ntile") .build() .run(); }
@Test public void test3668Fix() throws Exception { //testNoResult("set \"store.parquet.vectorize\" = false"); testBuilder() .sqlQuery(getFile("window/3668.sql"), TEST_RES_PATH) .ordered() .baselineColumns("cnt").baselineValues(2L) .build() .run(); }
@Test public void testSubstr() throws Exception { testBuilder() .sqlQuery("select substr(n_name, 'UN.TE.') a from cp.\"tpch/nation.parquet\" where ilike(n_name, 'united%') = true") .unOrdered() .baselineColumns("a") .baselineValues("UNITED") .baselineValues("UNITED") .build() .run(); }
@Test public void testRefreshSourceStatus() throws Exception { test("USE dfs"); testBuilder() .sqlQuery("ALTER SOURCE dfs_test REFRESH STATUS") .unOrdered() .baselineColumns("ok", "summary") .baselineValues(true, "Successfully refreshed status for source 'dfs_test'. New status is: Healthy") .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 // DRILL-3769 public void testToDateForTimeStamp() throws Exception { final String query = "select to_date(to_timestamp(-1)) as col \n" + "from (values(1))"; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("col") .baselineValues(new LocalDateTime(1969, 12, 31, 0, 0)) .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 caseStruct1() throws Exception { final String query = "SELECT CASE WHEN t.a.b = 1 THEN t.a ELSE null END AS a FROM cp.\"jsoninput/input4.json\" t"; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a") .baselineValues(mapOf("b", 1L, "c", mapOf("d", 2L))) .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 testDRILL2361_SortColumnAliasWithDots() throws Exception { testBuilder() .sqlQuery("select o_custkey as \"x.y.z\" from cp.\"tpch/orders.parquet\" where o_orderkey < 5 order by \"x.y.z\"") .unOrdered() .baselineColumns("`x.y.z`") .baselineValues(370) .baselineValues(781) .baselineValues(1234) .baselineValues(1369) .build().run(); }