@Test public void testBinaryPrefixNumericFunctions() throws Exception { final String exprs = " cast(%s(stars, review_count) as double), cast(%s(stars, stars) as double), cast(%s(review_count, review_count) as double) "; String s = "power"; testBuilder() .sqlQuery(String.format("select " + exprs + " from %s", s, s, s, ELASTIC_TABLE)) .unOrdered() .sqlBaselineQuery(String.format("select " + exprs + " from %s", s, s, s, PARQUET_TABLE)) .go(); }
@Test public void join() throws Exception { final String joinArrow = "SELECT * FROM TABLE(dfs_test.lineitem(type => 'arrow')) l JOIN " + "TABLE(dfs_test.orders(type => 'arrow')) o ON l.l_orderkey = o.o_orderkey"; final String joinParquet = "SELECT * FROM cp.\"tpch/lineitem.parquet\" l JOIN " + "cp.\"tpch/orders.parquet\" o ON l.l_orderkey = o.o_orderkey"; testBuilder() .unOrdered() .sqlQuery(joinArrow) .sqlBaselineQuery(joinParquet) .go(); }
@Test public void simple() throws Exception { final String query = "SELECT * FROM TABLE(dfs_test.arrowRegion(type => 'arrow'))"; test(query); testBuilder() .unOrdered() .sqlQuery(query) .sqlBaselineQuery("SELECT * FROM cp.\"region.json\"") .go(); }
@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(); }
@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 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 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 testSkipAll() throws Exception { final String query = "SELECT count(*) FROM cp.\"json/map_list_map.json\""; testPhysicalPlan(query, "columns=[]"); testBuilder() .sqlQuery(query) .unOrdered() .sqlBaselineQuery("SELECT count(id) FROM cp.\"json/map_list_map.json\"") .go(); }
@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 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 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 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 test4349() throws Exception { // start by creating a parquet file from the input csv file runSQL("CREATE TABLE dfs_test.\"4349\" AS SELECT columns[0] id, CAST(NULLIF(columns[1], '') AS DOUBLE) val FROM cp.\"parquet2/4349.csv.gz\""); // querying the parquet file should return the same results found in the csv file testBuilder() .unOrdered() .sqlQuery("SELECT * FROM dfs_test.\"4349\" WHERE id = 'b'") .sqlBaselineQuery("SELECT columns[0] id, CAST(NULLIF(columns[1], '') AS DOUBLE) val FROM cp.\"parquet2/4349.csv.gz\" WHERE columns[0] = 'b'") .go(); }
@Test public void groupByCompareCountExcelText() throws Exception { testBuilder() .sqlQuery(countQuery(E_GROUPBY)) .unOrdered() .sqlBaselineQuery(countQuery(T_GROUPBY)) .go(); }
@Test public void simpleCompareCountExcelText() throws Exception { testBuilder() .sqlQuery(countQuery(E_SIMPLE)) .unOrdered() .sqlBaselineQuery(countQuery(T_SIMPLE)) .go(); }
@Test public void orderByCompareCountExcelText() throws Exception { testBuilder() .sqlQuery(countQuery(E_ORDERBY)) .unOrdered() .sqlBaselineQuery(countQuery(T_ORDERBY)) .go(); }
@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 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 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 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(); }