@Test // DRILL-2094 public void testOrderbyArrayElementInSubquery() throws Exception { String root = FileUtils.getResourceAsFile("/store/json/orderByArrayElement.json").toURI().getPath().toString(); String query = String.format("select s.id from \n" + "(select id \n" + "from dfs.\"%s\" \n" + "order by list[0]) s", root); testBuilder() .sqlQuery(query) .ordered() .baselineColumns("id") .baselineValues((long) 1) .baselineValues((long) 5) .baselineValues((long) 4) .baselineValues((long) 2) .baselineValues((long) 3) .build().run(); }
@Test public void currentTimestampTypes() throws Exception { testBuilder() .sqlQuery("SELECT typeof(CURRENT_TIMESTAMP) c1, typeof(CURRENT_TIMESTAMP(3)) c2 FROM (VALUES(1))") .unOrdered() .baselineColumns("c1", "c2") .baselineValues("TIMESTAMPMILLI", "TIMESTAMPMILLI") .go(); }
@Test public void testValues2() throws Exception { String query = "SELECT id FROM (VALUES(''),(''),('non-null-value')) tbl(id) WHERE NULLIF(id,'') IS NOT NULL"; testBuilder().sqlQuery(query).unOrdered().baselineColumns("id").baselineValues("non-null-value").go(); }
@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 //DRILL-2163 public void testNestedTypesPastJoinReportsValidResult() throws Exception { final String query = "select t1.uid, t1.events, t1.events[0].evnt_id as event_id, t2.transactions, " + "t2.transactions[0] as trans, t1.odd, t2.even from cp.\"project/complex/a.json\" t1, " + "cp.\"project/complex/b.json\" t2 where t1.uid = t2.uid"; testBuilder() .sqlQuery(query) .ordered() .jsonBaselineFile("project/complex/drill-2163-result.json") .build() .run(); }
@Test // DRILL-2479 public void testCorrelatedExistsWithInSubq() throws Exception { String query = "select count(*) as cnt from cp.\"tpch/lineitem.parquet\" l where exists " + " (select ps.ps_suppkey from cp.\"tpch/partsupp.parquet\" ps where ps.ps_suppkey = l.l_suppkey and ps.ps_partkey " + " in (select p.p_partkey from cp.\"tpch/part.parquet\" p where p.p_type like '%NICKEL'))"; testBuilder() .sqlQuery(query) .unOrdered() .baselineColumns("cnt") .baselineValues(60175l) .go(); }
@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 testValuesNullIf() throws Exception { String query = "SELECT id FROM (VALUES(''),('asdfkjhasdjkhgavdjhkgdvkjhg'),('aaaaa'),(''),('zzzzzzz'),('a'),('z'),('non-null-value')) tbl(id) WHERE NULLIF(id,'') IS NULL"; testBuilder().sqlQuery(query).unOrdered().baselineColumns("id") .baselineValues("") .baselineValues("") .go(); }
@Test // DRILL-1927 public void testGroupByCaseInSubquery2() throws Exception { String query2 = "select sum(case when t.r_regionkey in (3) then 0 else 1 end) as col \n" + "from cp.\"tpch/region.parquet\" t"; testBuilder() .sqlQuery(query2) .unOrdered() .baselineColumns("col") .baselineValues((long) 4) .build() .run(); }
@Test public void stringLiteralComparison() throws Exception { String sql = "SELECT a = b as e FROM (VALUES('foo', 'foo'),('bar', 'bar ')) tbl(a, b)"; testBuilder() .sqlQuery(sql) .ordered() .baselineColumns("e") .baselineValues(true) .baselineValues(false) .go(); }
@Test // DRILL-2063 public void testAggExpressionWithGroupBy() throws Exception { String query = "select l_suppkey, sum(l_extendedprice)/sum(l_quantity) as avg_price \n" + " from cp.\"tpch/lineitem.parquet\" where l_orderkey in \n" + " (select o_orderkey from cp.\"tpch/orders.parquet\" where o_custkey = 2) \n" + " and l_suppkey = 4 group by l_suppkey"; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("l_suppkey", "avg_price") .baselineValues(4, 1374.47) .build().run(); }
@Test // DRILL-1888 public void testAggExpressionWithGroupByHaving() throws Exception { String query = "select l_suppkey, sum(l_extendedprice)/sum(l_quantity) as avg_price \n" + " from cp.\"tpch/lineitem.parquet\" where l_orderkey in \n" + " (select o_orderkey from cp.\"tpch/orders.parquet\" where o_custkey = 2) \n" + " group by l_suppkey having sum(l_extendedprice)/sum(l_quantity) > 1850.0"; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("l_suppkey", "avg_price") .baselineValues(98, 1854.95) .build().run(); }
@Test // DRILL-2914 public void testGroupByStarSchemaless() throws Exception { String query = "SELECT n.n_nationkey AS col \n" + "FROM (SELECT * FROM cp.\"tpch/nation.parquet\") AS n \n" + "GROUP BY n.n_nationkey \n" + "ORDER BY n.n_nationkey"; testBuilder() .sqlQuery(query) .ordered() .csvBaselineFile("testframework/testExampleQueries/testGroupByStarSchemaless.tsv") .baselineTypes(MinorType.INT) .baselineColumns("col") .build() .run(); }
@Test // DRILL-1927 public void testGroupByCaseInSubquery1() throws Exception { String query1 = "select (case when t.r_regionkey in (3) then 0 else 1 end) as col \n" + "from cp.\"tpch/region.parquet\" t \n" + "group by (case when t.r_regionkey in (3) then 0 else 1 end)"; testBuilder() .sqlQuery(query1) .unOrdered() .baselineColumns("col") .baselineValues(0) .baselineValues(1) .build() .run(); }
@Test // DRILL-1927 public void testGroupByCaseInSubquery3() throws Exception { String query3 = "select (case when (r_regionkey IN (0, 2, 3, 4)) then 0 else r_regionkey end) as col1, min(r_regionkey) as col2 \n" + "from cp.\"tpch/region.parquet\" \n" + "group by (case when (r_regionkey IN (0, 2, 3, 4)) then 0 else r_regionkey end)"; testBuilder() .sqlQuery(query3) .unOrdered() .baselineColumns("col1", "col2") .baselineValues(0, 0) .baselineValues(1, 1) .build() .run(); }
@Test public void testSimilar() throws Exception { String query = "select n_nationkey " + "from cp.\"tpch/nation.parquet\" " + "where n_name similar to 'CHINA' " + "order by n_regionkey"; testBuilder() .sqlQuery(query) .unOrdered() .optionSettingQueriesForTestQuery("alter session set \"planner.slice_target\" = 1") .baselineColumns("n_nationkey") .baselineValues(18) .go(); test("alter session set \"planner.slice_target\" = " + ExecConstants.SLICE_TARGET_DEFAULT); }
@Test public void testRepeatedListProjectionPastJoin() throws Exception { final String query = "select * from cp.\"join/join-left-drill-3032.json\" f1 inner join cp.\"join/join-right-drill-3032.json\" f2 on f1.id = f2.id"; testBuilder() .sqlQuery(query) .unOrdered() .baselineColumns("id", "id0", "aaa") .baselineValues(1L, 1L, listOf(listOf(listOf("val1"), listOf("val2")))) .go(); }
@Test public void testInt8Parquet() throws Exception { String query = "select * from cp.\"/parquet/intTypes/int_8.parquet\""; testBuilder() .sqlQuery(query) .unOrdered() .baselineColumns("index", "value") .baselineValues(1, 0) .baselineValues(2, -1) .baselineValues(3, 1) .baselineValues(4, -128) .baselineValues(5, 127) .go(); }
@Test public void testInt16Parquet() throws Exception { String query = "select * from cp.\"/parquet/intTypes/int_16.parquet\""; testBuilder() .sqlQuery(query) .unOrdered() .baselineColumns("index", "value") .baselineValues(1, 0) .baselineValues(2, -1) .baselineValues(3, 1) .baselineValues(4, -32768) .baselineValues(5, 32767) .go(); }
@Test public void testMinVarCharWithGroupBy() throws Exception { String sql = "select l_linenumber, min(l_shipmode) as min_ship_mode from cp.\"tpch/lineitem.parquet\" group by l_linenumber"; testBuilder() .sqlQuery(sql) .unOrdered() .baselineColumns("l_linenumber", "min_ship_mode") .baselineValues(1, "AIR") .baselineValues(2, "AIR") .baselineValues(3, "AIR") .baselineValues(4, "AIR") .baselineValues(5, "AIR") .baselineValues(6, "AIR") .baselineValues(7, "AIR") .go(); }