@Test // DRILL-811 public void testDRILL_811ViewJoin() throws Exception { test("use dfs_test"); test("create view nation_view_testexamplequeries as select * from cp.\"tpch/nation.parquet\";"); test("create view region_view_testexamplequeries as select * from cp.\"tpch/region.parquet\";"); test("select n.n_nationkey, n.n_regionkey, r.r_name from region_view_testexamplequeries r , nation_view_testexamplequeries n where r.r_regionkey = n.n_regionkey "); test("select n.n_regionkey, count(*) as cnt from region_view_testexamplequeries r , nation_view_testexamplequeries n where r.r_regionkey = n.n_regionkey and n.n_nationkey > 8 group by n.n_regionkey order by n.n_regionkey"); test("select n.n_regionkey, count(*) as cnt from region_view_testexamplequeries r join nation_view_testexamplequeries n on r.r_regionkey = n.n_regionkey and n.n_nationkey > 8 group by n.n_regionkey order by n.n_regionkey"); test("drop view region_view_testexamplequeries "); test("drop view nation_view_testexamplequeries "); }
@Test public void testMixedTypeGroupBy() throws Exception { test("alter session set \"planner.enable_streamagg\" = false"); test("alter session set \"planner.enable_hashagg\" = true"); test("select count(*) from cp.\"vector/complex/mixed.json\" group by a, typeof(a)"); test("alter session set \"planner.enable_streamagg\" = true"); }
@Test // DRILL-811 public void testDRILL_811Json() throws Exception { test("use dfs_test"); test("create view region_view_testexamplequeries as select * from cp.\"region.json\";"); test("select sales_city, sales_region from region_view_testexamplequeries where region_id > 50 order by sales_country; "); test("drop view region_view_testexamplequeries "); }
@Test // Bugs DRILL-727, DRILL-940 public void testOrderByDiffColumn() throws Exception { test("select r_name from cp.\"tpch/region.parquet\" order by r_regionkey"); test("select r_name from cp.\"tpch/region.parquet\" order by r_name, r_regionkey"); test("select cast(r_name as varchar(20)) from cp.\"tpch/region.parquet\" order by r_name"); }
@Test // DRILL-2221 public void createJsonWithEmptyList() throws Exception { final String file = FileUtils.getResourceAsFile("/store/json/record_with_empty_list.json").toURI().getPath().toString(); final String tableName = "jsonWithEmptyList"; test("USE dfs_test"); test("ALTER SESSION SET \"store.format\"='json'"); test(String.format("CREATE TABLE %s AS SELECT * FROM dfs.\"%s\"", tableName, file)); test(String.format("SELECT COUNT(*) FROM %s", tableName)); test("ALTER SESSION SET \"store.format\"='parquet'"); }
@Test public void subQueryNotInWhereNotNull() throws Exception { test("SELECT l_returnflag, l_linestatus, sum(l_extendedprice)\n" + "FROM cp.\"tpch/lineitem.parquet\" as lineitem\n" + "where l_quantity not in\n" + "(select l_linenumber from cp.\"tpch/lineitem.parquet\" as lineitem where (l_linenumber is not null) group by l_linenumber)\n" + "group by l_returnflag, l_linestatus"); }
@Test public void testPushExpInJoinConditionRightJoin() throws Exception { test("select a.n_nationkey, b.r_regionkey from cp.\"tpch/nation.parquet\" a right join cp.\"tpch/region.parquet\" b " + "" + " on a.n_regionkey +100 = b.r_regionkey +200 " + // expressions in both sides of equal join filter " and (substr(a.n_name,1,3)= 'L1' or substr(a.n_name,2,2) = 'L2') "); // left filter // " and (substr(b.r_name,1,3)= 'R1' or substr(b.r_name,2,2) = 'R2') " + // right filter // " and (substr(a.n_name,2,3)= 'L3' or substr(b.r_name,3,2) = 'R3');"); // non-equal join filter }
@Test public void testSVRV4Join() throws Exception { test("select count(*) from cp.\"tpch/lineitem.parquet\" l, cp.\"tpch/partsupp.parquet\" ps \n" + " where l.l_partkey = ps.ps_partkey and l.l_suppkey = ps.ps_suppkey ;"); }
@Test public void testPushExpInJoinConditionWhere() throws Exception { test("select a.n_nationkey from cp.\"tpch/nation.parquet\" a , cp.\"tpch/region.parquet\" b " + "" + " where a.n_regionkey + 100 = b.r_regionkey + 200" + // expressions in both sides of equal join filter " and (substr(a.n_name,1,3)= 'L1' or substr(a.n_name,2,2) = 'L2') " + // left filter " and (substr(b.r_name,1,3)= 'R1' or substr(b.r_name,2,2) = 'R2') " + // right filter " and (substr(a.n_name,2,3)= 'L3' or substr(b.r_name,3,2) = 'R3');"); // non-equal join filter }
@Test public void testPushExpInJoinConditionLeftJoin() throws Exception { test("select a.n_nationkey, b.r_regionkey from cp.\"tpch/nation.parquet\" a left join cp.\"tpch/region.parquet\" b " + "" + " on a.n_regionkey +100 = b.r_regionkey +200 " + // expressions in both sides of equal join filter // " and (substr(a.n_name,1,3)= 'L1' or substr(a.n_name,2,2) = 'L2') " + // left filter " and (substr(b.r_name,1,3)= 'R1' or substr(b.r_name,2,2) = 'R2') "); // right filter // " and (substr(a.n_name,2,3)= 'L3' or substr(b.r_name,3,2) = 'R3');"); // non-equal join filter }
@Test // see DRILL-3557 public void testEmptyCSVinDirectory() throws Exception { final String root = FileUtils.getResourceAsFile("/store/text/directoryWithEmpyCSV").toURI().getPath().toString(); final String toFile = FileUtils.getResourceAsFile("/store/text/directoryWithEmpyCSV/empty.csv").toURI().getPath().toString(); String query1 = String.format("explain plan for select * from dfs.\"%s\"", root); String query2 = String.format("explain plan for select * from dfs.\"%s\"", toFile); test(query1); test(query2); }
@Test public void testText() throws Exception { String root = FileUtils.getResourceAsFile("/store/text/data/regions.csv").toURI().getPath().toString(); String query = String.format("select * from dfs.\"%s\"", root); test(query); }
@Test @Ignore("DRILL-3774") public void testTextPartitions() throws Exception { String root = FileUtils.getResourceAsFile("/store/text/data/").toURI().getPath().toString(); String query = String.format("select * from dfs.\"%s\"", root); test(query); }
@Test // DRILL-1561 public void test2PhaseAggAfterOrderBy() throws Exception { String query = "select count(*) from (select o_custkey from cp.\"tpch/orders.parquet\" order by o_custkey)"; // set slice_target = 1 to force exchanges and 2-phase aggregation try(AutoCloseable ac = withOption(ExecConstants.SLICE_TARGET_OPTION, 1)){ test(query); } }
@Test // DRILL-1846 (this tests issue with SimpleMergeExchange) public void testOrderByDiffColumnsInSubqAndOuter() throws Exception { String query = "select n.n_nationkey from (select n_nationkey, n_regionkey from cp.\"tpch/nation.parquet\" order by n_regionkey) n order by n.n_nationkey"; // set slice_target = 1 to force exchanges try(AutoCloseable ac = withOption(ExecConstants.SLICE_TARGET_OPTION, 1)){ test(query); } }
@Test // DRILL-1846 (this tests issue with UnionExchange) @Ignore("DRILL-1866") public void testLimitInSubqAndOrderByOuter() throws Exception { String query = "select t2.n_nationkey from (select n_nationkey, n_regionkey from cp.\"tpch/nation.parquet\" t1 group by n_nationkey, n_regionkey limit 10) t2 order by t2.n_nationkey"; // set slice_target = 1 to force exchanges try(AutoCloseable ac = withOption(ExecConstants.SLICE_TARGET_OPTION, 1)){ test(query); } }