@Test // DRILL-2053 : column name is case-insensitive when join a CTE with a regluar table. public void testCaseSenJoinCTEWithRegTab() throws Exception { final String query1 = "with a as ( select * from cp.\"tpch/nation.parquet\" ) select * from a, cp.\"tpch/region.parquet\" b where a.N_REGIONKEY = b.R_REGIONKEY"; int actualRecordCount = testSql(query1); int expectedRecordCount = 25; assertEquals(String.format("Received unexpected number of rows in output for query:\n%s\n expected=%d, received=%s", query1, expectedRecordCount, actualRecordCount), expectedRecordCount, actualRecordCount); final String query2 = "with a as ( select * from cp.\"tpch/nation.parquet\" ) select * from a, cp.\"tpch/region.parquet\" b where a.n_regionkey = b.r_regionkey"; actualRecordCount = testSql(query2); expectedRecordCount = 25; assertEquals(String.format("Received unexpected number of rows in output for query:\n%s\n expected=%d, received=%s", query2, expectedRecordCount, actualRecordCount), expectedRecordCount, actualRecordCount); }
@Test // DRILL-1500 public void testStarPartitionFilterOrderBy() throws Exception { String query = String.format("select * from dfs.\"%s/multilevel/parquet\" where dir0=1994 and dir1='Q1' order by dir0 limit 1", TEST_RES_PATH); org.joda.time.LocalDateTime mydate = new org.joda.time.LocalDateTime("1994-01-20T00:00:00.000"); testBuilder() .sqlQuery(query) .ordered() .baselineColumns("dir0", "dir1", "o_clerk", "o_comment", "o_custkey", "o_orderdate", "o_orderkey", "o_orderpriority", "o_orderstatus", "o_shippriority", "o_totalprice") .baselineValues("1994", "Q1", "Clerk#000000743", "y pending requests integrate", 1292, mydate, 66, "5-LOW", "F", 0, 104190.66) .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(); }
@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 // Join a select star of SchemaTable, with a select star of Schema-less table. public void testSelStarJoinSchemaWithSchemaLess() throws Exception { String query = "select t1.name, t1.kind, t2.n_nationkey from " + "(select * from sys.options) t1 " + "join (select * from cp.\"tpch/nation.parquet\") t2 " + "on t1.name = t2.n_name"; test("alter session set \"planner.enable_broadcast_join\" = false"); test(query); test("alter session set \"planner.enable_broadcast_join\" = true"); test(query); }
@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 // Select * in SubQuery : regular columns appear in select clause, where, group by, order by. public void testSelStarSubQNoPrefix() throws Exception { test("select n_nationkey, n_name, n_regionkey from (select * from cp.\"tpch/nation.parquet\") where n_regionkey > 1 order by n_name" ); test("select n_regionkey, count(*) as cnt from ( select * from ( select * from cp.\"tpch/nation.parquet\") where n_nationkey < 10 ) where n_nationkey >1 group by n_regionkey order by n_regionkey ; "); test("select n_regionkey, count(*) as cnt from (select * from cp.\"tpch/nation.parquet\") t where n_nationkey > 1 group by n_regionkey order by n_regionkey;" ); }
@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 // Select * in SubQuery : regular columns appear in select clause, where, group by, order by. public void testSelStarSubQPrefix() throws Exception { test("select t.n_nationkey, t.n_name, t.n_regionkey from (select * from cp.\"tpch/nation.parquet\") t where t.n_regionkey > 1 order by t.n_name" ); test("select n.n_regionkey, count(*) as cnt from ( select * from ( select * from cp.\"tpch/nation.parquet\") t where t.n_nationkey < 10 ) n where n.n_nationkey >1 group by n.n_regionkey order by n.n_regionkey ; "); test("select t.n_regionkey, count(*) as cnt from (select * from cp.\"tpch/nation.parquet\") t where t.n_nationkey > 1 group by t.n_regionkey order by t.n_regionkey;" ); }
@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 // DRILL-595 : Select * in CTE WithClause : regular columns appear in select clause, where, group by, order by. public void testDRILL_595WithClause() throws Exception { test(" with x as (select * from cp.\"region.json\") \n" + " select x.region_id, x.sales_city \n" + " from x where x.region_id > 10 limit 5;"); test(" with x as (select * from cp.\"region.json\") \n" + " select region_id, sales_city \n" + " from x where region_id > 10 limit 5;"); test(" with x as (select * from cp.\"tpch/nation.parquet\") \n" + " select x.n_regionkey, count(*) as cnt \n" + " from x \n" + " where x.n_nationkey > 5 \n" + " group by x.n_regionkey \n" + " order by cnt limit 5; "); }
@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 // DRILL-595 : Join two CTE, each having select * : regular columns appear in the select , where and on clause, group by, order by. public void testDRILL_595WithClauseJoin() throws Exception { test("with n as (select * from cp.\"tpch/nation.parquet\"), \n " + " r as (select * from cp.\"tpch/region.parquet\") \n" + "select n.n_nationkey, n.n_name, n.n_regionkey, r.r_name \n" + "from n, r \n" + "where n.n_regionkey = r.r_regionkey ;" ); test("with n as (select * from cp.\"tpch/nation.parquet\"), \n " + " r as (select * from cp.\"tpch/region.parquet\") \n" + "select n.n_regionkey, count(*) as cnt \n" + "from n, r \n" + "where n.n_regionkey = r.r_regionkey and n.n_nationkey > 5 \n" + "group by n.n_regionkey \n" + "order by cnt;" ); }
@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 // join two SubQuery, each having select * : regular columns appear in the select , where and on clause, group by, order by. public void testSelStarSubQJoin() throws Exception { // select clause, where. test(" select n.n_nationkey, n.n_name, n.n_regionkey, r.r_name \n" + " from (select * from cp.\"tpch/nation.parquet\") n, \n" + " (select * from cp.\"tpch/region.parquet\") r \n" + " where n.n_regionkey = r.r_regionkey " ); // select clause, where, group by, order by test(" select n.n_regionkey, count(*) as cnt \n" + " from (select * from cp.\"tpch/nation.parquet\") n \n" + " , (select * from cp.\"tpch/region.parquet\") r \n" + " where n.n_regionkey = r.r_regionkey and n.n_nationkey > 10 \n" + " group by n.n_regionkey \n" + " order by n.n_regionkey; " ); // Outer query use select *. Join condition in where clause. test(" select * \n" + " from (select * from cp.\"tpch/nation.parquet\") n \n" + " , (select * from cp.\"tpch/region.parquet\") r \n" + " where n.n_regionkey = r.r_regionkey " ); // Outer query use select *. Join condition in on clause. test(" select * \n" + " from (select * from cp.\"tpch/nation.parquet\") n \n" + " join (select * from cp.\"tpch/region.parquet\") r \n" + " on n.n_regionkey = r.r_regionkey " ); }
@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 // select star for a SchemaTable. public void testSelStarSubQSchemaTable() throws Exception { test("select name, kind, type from (select * from sys.options);"); }