@Test public void testExchangeRemoveForJoinPlan() throws Exception { final String WORKING_PATH = TestTools.getWorkingPath(); final String TEST_RES_PATH = WORKING_PATH + "/src/test/resources"; String sql = String.format("select t2.n_nationkey from dfs.\"%s/tpchmulti/region\" t1 join dfs.\"%s/tpchmulti/nation\" t2 on t2.n_regionkey = t1.r_regionkey", TEST_RES_PATH, TEST_RES_PATH); testBuilder() .unOrdered() .optionSettingQueriesForTestQuery("alter session set \"planner.slice_target\" = 10; alter session set \"planner.join.row_count_estimate_factor\" = 0.1") // Enforce exchange will be inserted. .sqlQuery(sql) .optionSettingQueriesForBaseline("alter session set \"planner.slice_target\" = 100000; alter session set \"planner.join.row_count_estimate_factor\" = 1.0") // Use default option setting. .sqlBaselineQuery(sql) .build().run(); }
@Test public void testLtrimOneArgConvergeToLtrim() throws Exception { final String query_1 = "SELECT ltrim(concat(' ', r_name, ' ')) \n" + "FROM cp.\"tpch/region.parquet\""; final String query_2 = "SELECT ltrim(concat(' ', r_name, ' '), ' ') \n" + "FROM cp.\"tpch/region.parquet\""; testBuilder() .sqlQuery(query_1) .unOrdered() .sqlBaselineQuery(query_2) .build() .run(); }
@Test public void testRpadTwoArgConvergeToRpad() throws Exception { final String query_1 = "SELECT rpad(r_name, 25) \n" + "FROM cp.\"tpch/region.parquet\""; final String query_2 = "SELECT rpad(r_name, 25, ' ') \n" + "FROM cp.\"tpch/region.parquet\""; testBuilder() .sqlQuery(query_1) .unOrdered() .sqlBaselineQuery(query_2) .build() .run(); }
@Test public void testLpadTwoArgConvergeToLpad() throws Exception { final String query_1 = "SELECT lpad(r_name, 25) \n" + "FROM cp.\"tpch/region.parquet\""; final String query_2 = "SELECT lpad(r_name, 25, ' ') \n" + "FROM cp.\"tpch/region.parquet\""; testBuilder() .sqlQuery(query_1) .unOrdered() .sqlBaselineQuery(query_2) .build() .run(); }
@Test public void testSelStarJoinSameColName() throws Exception { testBuilder() .unOrdered() .sqlQuery("select * from cp.\"tpch/nation.parquet\" n1, cp.\"tpch/nation.parquet\" n2 where n1.n_nationkey = n2.n_nationkey") .sqlBaselineQuery("select n1.n_nationkey, n1.n_name, n1.n_regionkey, n1.n_comment, " + "n2.n_nationkey as n_nationkey0, n2.n_name as n_name0, n2.n_regionkey as n_regionkey0, n2.n_comment as n_comment0 " + "from cp.\"tpch/nation.parquet\" n1, cp.\"tpch/nation.parquet\" n2 where n1.n_nationkey = n2.n_nationkey") .build().run(); }
@Test public void testSelStarRegColConstJoin() throws Exception { testBuilder() .ordered() .sqlQuery("select *, n.n_nationkey as n_nationkey0, 1 + 2 as constant from cp.\"tpch/nation.parquet\" n, cp.\"tpch/region.parquet\" r where n.n_regionkey = r.r_regionkey order by n.n_name") .sqlBaselineQuery(" select n.n_nationkey, n.n_name, n.n_regionkey, n.n_comment, r.r_regionkey, r.r_name, r.r_comment, " + " n.n_nationkey as n_nationkey0, 1 + 2 as constant " + " from cp.\"tpch/nation.parquet\" n, cp.\"tpch/region.parquet\" r " + " where n.n_regionkey = r.r_regionkey " + " order by n.n_name") .build().run(); }
@Test public void testSelStarOrderBy() throws Exception{ testBuilder() .ordered() .sqlQuery(" select * from cp.\"employee.json\" order by last_name") .sqlBaselineQuery(" select employee_id, full_name,first_name,last_name,position_id,position_title,store_id," + " department_id,birth_date,hire_date,salary,supervisor_id,education_level,marital_status,gender,management_role " + " from cp.\"employee.json\" " + " order by last_name ") .build().run(); }
@Test public void testAggregateRangeCurrentAndCurrent() throws Exception { final String table = "dfs.\""+TEST_RES_PATH+"/window/b4.p4\""; testBuilder() .sqlQuery(getFile("window/aggregate_range_current_current.sql"), table) .unOrdered() .sqlBaselineQuery(getFile("window/aggregate_range_current_current_baseline.sql"), table) .build() .run(); }
@Test public void testSelStarBothSideJoin() throws Exception { testBuilder() .unOrdered() .sqlQuery("select n.*, r.* from cp.\"tpch/nation.parquet\" n, cp.\"tpch/region.parquet\" r where n.n_regionkey = r.r_regionkey") .sqlBaselineQuery("select n.n_nationkey,n.n_name,n.n_regionkey,n.n_comment,r.r_regionkey,r.r_name,r.r_comment from cp.\"tpch/nation.parquet\" n, cp.\"tpch/region.parquet\" r where n.n_regionkey = r.r_regionkey order by n.n_name") .build().run(); }
@Test public void testSelStarOrderByLimit() throws Exception{ testBuilder() .ordered() .sqlQuery(" select * from cp.\"employee.json\" order by last_name limit 2") .sqlBaselineQuery(" select employee_id, full_name,first_name,last_name,position_id,position_title,store_id," + " department_id,birth_date,hire_date,salary,supervisor_id,education_level,marital_status,gender,management_role " + " from cp.\"employee.json\" " + " order by last_name limit 2") .build().run(); }
@Test public void testLastValueRowsUnboundedAndCurrentRow() throws Exception { final String table = "dfs.\""+TEST_RES_PATH+"/window/b4.p4\""; testBuilder() .sqlQuery(getFile("window/last_value_rows_unbounded_current.sql"), table) .unOrdered() .sqlBaselineQuery(getFile("window/last_value_rows_unbounded_current_baseline.sql"), table) .build() .run(); }
@Test public void testAggregateRowsUnboundedAndCurrentRow() throws Exception { final String table = "dfs.\""+TEST_RES_PATH+"/window/b4.p4\""; testBuilder() .sqlQuery(getFile("window/aggregate_rows_unbounded_current.sql"), table) .ordered() .sqlBaselineQuery(getFile("window/aggregate_rows_unbounded_current_baseline.sql"), table) .build() .run(); }
@Test public void testUnboundedFollowing() throws Exception { testBuilder() .sqlQuery(getFile("window/q3.sql"), TEST_RES_PATH) .ordered() .sqlBaselineQuery(getFile("window/q4.sql"), TEST_RES_PATH) .build() .run(); }
@Test public void testSelLeftStarJoin() throws Exception { testBuilder() .ordered() .sqlQuery("select n.* from cp.\"tpch/nation.parquet\" n, cp.\"tpch/region.parquet\" r where n.n_regionkey = r.r_regionkey order by n.n_name") .sqlBaselineQuery("select n.n_nationkey, n.n_name, n.n_regionkey, n.n_comment from cp.\"tpch/nation.parquet\" n, cp.\"tpch/region.parquet\" r where n.n_regionkey = r.r_regionkey order by n.n_name") .build().run(); }
@Test public void testSelRightStarJoin() throws Exception { testBuilder() .ordered() .sqlQuery("select r.* from cp.\"tpch/nation.parquet\" n, cp.\"tpch/region.parquet\" r where n.n_regionkey = r.r_regionkey order by n.n_name") .sqlBaselineQuery("select r.r_regionkey, r.r_name, r.r_comment from cp.\"tpch/nation.parquet\" n, cp.\"tpch/region.parquet\" r where n.n_regionkey = r.r_regionkey order by n.n_name") .build().run(); }
@Test public void testSelStarJoin() throws Exception { testBuilder() .ordered() .sqlQuery("select * from cp.\"tpch/nation.parquet\" n, cp.\"tpch/region.parquet\" r where n.n_regionkey = r.r_regionkey order by n.n_name") .sqlBaselineQuery("select n.n_nationkey, n.n_name,n.n_regionkey,n.n_comment,r.r_regionkey,r.r_name, r.r_comment from cp.\"tpch/nation.parquet\" n, cp.\"tpch/region.parquet\" r where n.n_regionkey = r.r_regionkey order by n.n_name") .build().run(); }
@Test public void testSelStarWhereOrderBy() throws Exception{ testBuilder() .ordered() .sqlQuery("select * from cp.\"employee.json\" where first_name = 'James' order by last_name") .sqlBaselineQuery("select employee_id, full_name,first_name,last_name,position_id,position_title,store_id," + " department_id,birth_date,hire_date,salary,supervisor_id,education_level,marital_status,gender,management_role " + " from cp.\"employee.json\" " + " where first_name = 'James' order by last_name") .build().run(); }
@Test public void testFirstValueRangeCurrentAndCurrent() throws Exception { final String table = "dfs.\""+TEST_RES_PATH+"/window/b4.p4\""; testBuilder() .sqlQuery(getFile("window/first_value_range_current_current.sql"), table) .unOrdered() .sqlBaselineQuery(getFile("window/first_value_range_current_current_baseline.sql"), table) .build() .run(); }
@Test //DRILL-3018 public void testNestLoopJoinScalarSubQ() throws Exception { testBuilder() .sqlQuery("select n_nationkey from cp.\"tpch/nation.parquet\" where n_nationkey >= (select min(c_nationkey) from cp.\"tpch/customer.parquet\")") .unOrdered() .sqlBaselineQuery("select n_nationkey from cp.\"tpch/nation.parquet\"") .build() .run(); }
@Test public void testSelStarPlusRegCol() throws Exception{ testBuilder() .unOrdered() .sqlQuery("select *, n_nationkey as key2 from cp.\"tpch/nation.parquet\" order by n_name limit 2") .sqlBaselineQuery("select n_comment, n_name, n_nationkey, n_regionkey, n_nationkey as key2 from cp.\"tpch/nation.parquet\" order by n_name limit 2") .build().run(); }