@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 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 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 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 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 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 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 // DRILL-1889 public void testStarWithOtherExpression() throws Exception { testBuilder() .ordered() .sqlQuery("select * from cp.\"tpch/nation.parquet\" order by substr(n_name, 2, 5) limit 3") .sqlBaselineQuery("select n_comment, n_name, n_nationkey, n_regionkey from cp.\"tpch/nation.parquet\" order by substr(n_name, 2, 5) limit 3 ") .build().run(); testBuilder() .ordered() .sqlQuery("select *, n_nationkey + 5 as myexpr from cp.\"tpch/nation.parquet\" limit 3") .sqlBaselineQuery("select n_comment, n_name, n_nationkey, n_regionkey, n_nationkey + 5 as myexpr from cp.\"tpch/nation.parquet\" order by n_nationkey limit 3") .build().run(); testBuilder() .ordered() .sqlQuery("select * from cp.\"tpch/nation.parquet\" where n_nationkey + 5 > 10 limit 3") .sqlBaselineQuery("select n_comment, n_name, n_nationkey, n_regionkey from cp.\"tpch/nation.parquet\" where n_nationkey + 5 > 10 order by n_nationkey limit 3") .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 // see DRILL-1828 public void testSelStarMultipleStars() throws Exception { testBuilder() .sqlQuery("select *, *, n_name from cp.\"tpch/nation.parquet\"") .ordered() .csvBaselineFile("testframework/testStarQueries/testSelStarMultipleStars/q1.tsv") .baselineTypes(MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.VARCHAR) .baselineColumns("n_nationkey", "n_name", "n_regionkey", "n_comment", "n_nationkey0", "n_name0", "n_regionkey0", "n_comment0", "n_name8") .build().run(); testBuilder() .sqlQuery("select *, *, n_name from cp.\"tpch/nation.parquet\" limit 2") .ordered() .csvBaselineFile("testframework/testStarQueries/testSelStarMultipleStars/q2.tsv") .baselineTypes(MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.VARCHAR) .baselineColumns("n_nationkey", "n_name", "n_regionkey", "n_comment", "n_nationkey0", "n_name0", "n_regionkey0", "n_comment0", "n_name8") .build().run(); }
@Test // see DRILL-1979 public void testSelStarMultipleStarsRegularColumnAsAlias() throws Exception { testBuilder() .sqlQuery("select *, n_name as extra, *, n_name as extra from cp.\"tpch/nation.parquet\"") .ordered() .csvBaselineFile("testframework/testStarQueries/testSelStarMultipleStarsRegularColumnAsAlias/q1.tsv") .baselineTypes(MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.VARCHAR) .baselineColumns("n_nationkey", "n_name", "n_regionkey", "n_comment", "extra", "n_nationkey0", "n_name0", "n_regionkey0", "n_comment0", "extra9") .build().run(); testBuilder() .sqlQuery("select *, n_name as extra, *, n_name as extra from cp.\"tpch/nation.parquet\" limit 2") .ordered() .csvBaselineFile("testframework/testStarQueries/testSelStarMultipleStarsRegularColumnAsAlias/q2.tsv") .baselineTypes(MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.INT, MinorType.VARCHAR, MinorType.VARCHAR) .baselineColumns("n_nationkey", "n_name", "n_regionkey", "n_comment", "extra", "n_nationkey0", "n_name0", "n_regionkey0", "n_comment0", "extra9") .build().run(); }
testBuilder() .unOrdered() .sqlQuery("select * from cp.\"tpch/nation.parquet\" where n_regionkey in (select r_regionkey from cp.\"tpch/region.parquet\")") testBuilder() .unOrdered() .sqlQuery("select * from cp.\"tpch/nation.parquet\" where (n_nationkey, n_name) in ( select n_nationkey, n_name from cp.\"tpch/nation.parquet\")") testBuilder() .unOrdered() .sqlQuery( testBuilder() .unOrdered() .sqlQuery(
testBuilder() .sqlQuery("select n_name, *, n_name, n_name from cp.\"tpch/nation.parquet\"") .ordered() .build().run(); testBuilder() .sqlQuery("select n_name, *, n_name, n_name from cp.\"tpch/nation.parquet\" limit 2") .ordered() .build().run(); testBuilder() .sqlQuery("select *, n_name, *, n_name, n_name from cp.\"tpch/nation.parquet\"") .ordered() .build().run(); testBuilder() .sqlQuery("select *, n_name, *, n_name, n_name from cp.\"tpch/nation.parquet\" limit 2") .ordered()