@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 testRepeatedMapEmptyBetween() throws Exception { final String query = "select * from cp.\"vector/complex/repeated-map-empty-between.json\""; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a") .baselineValues(listOf(mapOf("b", 1L))) .baselineValues(listOf()) .baselineValues(listOf(mapOf("b", 2L))) .go(); }
@Test public void testNullAndEmptyLists() throws Exception { testBuilder() .ordered() .sqlQuery("SELECT mylist FROM cp.\"/json/null_list.json\" LIMIT 10") .baselineColumns("mylist") .baselineValues(listOf("a", "b", "c")) .baselineValues(((JsonStringArrayList<Object>) null)) .baselineValues(listOf()) .baselineValues(((JsonStringArrayList<Object>) null)) .baselineValues(listOf("a", "b", "c")) .build() .run(); }
@Test public void testMultiLevelRepeatedListEmptyFirst() throws Exception { final String query = "select * from cp.\"vector/complex/multi-repeated-list-empty-first.json\""; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a") .baselineValues(listOf()) .baselineValues(listOf(listOf(listOf(1L), listOf(3L)), listOf(listOf(5L, 7L)))) .baselineValues(listOf(listOf(listOf(2L), listOf(4L)))) .go(); }
@Test public void testDRILL2361_SortColumnAliasWithDots() throws Exception { testBuilder() .sqlQuery("select o_custkey as \"x.y.z\" from cp.\"tpch/orders.parquet\" where o_orderkey < 5 order by \"x.y.z\"") .unOrdered() .baselineColumns("`x.y.z`") .baselineValues(370) .baselineValues(781) .baselineValues(1234) .baselineValues(1369) .build().run(); }
@Test // DX-9034 public void pruningEverythingAcrossUnion() throws Exception { String sql = "select ts from cp.\"parquet/singlets.parquet\" where ts = TIMESTAMP '1908-10-05 05:13:14.000'" + "UNION ALL select ts from cp.\"parquet/singlets.parquet\" where ts = TIMESTAMP '1908-10-05 05:13:14.000'"; testPlanMatchingPatterns(sql, new String[]{"Empty"}, "Filter"); testBuilder() .sqlQuery(sql) .unOrdered() .baselineColumns("ts") .expectsEmptyResultSet() .go(); }
@Test public void queryEmptyHiveTable() throws Exception { testBuilder() .sqlQuery("SELECT * FROM hive.empty_table") .expectsEmptyResultSet() .go(); }
@Test // DRILL-3739 public void readingFromStorageHandleBasedTable() throws Exception { testBuilder() .sqlQuery("SELECT * FROM hive.kv_sh ORDER BY key LIMIT 2") .ordered() .baselineColumns("key", "value") .expectsEmptyResultSet() .go(); }
@Test // Union-All where same column is projected twice in right child public void testUnionAll6() throws Exception { String query = "select n_nationkey, n_regionkey from cp.\"tpch/nation.parquet\" where n_regionkey = 1 union all select r_regionkey, r_regionkey from cp.\"tpch/region.parquet\" where r_regionkey = 2"; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q6.tsv") .baselineTypes(MinorType.INT, MinorType.INT) .baselineColumns("n_nationkey", "n_regionkey") .build().run(); }
@Test public void test3648Fix() throws Exception { testBuilder() .sqlQuery(getFile("window/3648.sql"), TEST_RES_PATH) .ordered() .csvBaselineFile("window/3648.tsv") .baselineColumns("ntile") .build() .run(); }
@Test public void testDrill_2013() throws Exception { String query = "select flatten(complex), rownum from cp.\"/store/json/test_flatten_mappify2.json\" where rownum > 5"; testPlanSubstrPatterns(query, new String[] {"columns=[`rownum`, `complex`]"}, null); testBuilder() .sqlQuery(query) .expectsEmptyResultSet() .build().run(); }
@Test // '\n' is treated as standard delimiter // if user has indicated custom line delimiter but input file contains '\n', split will occur on both public void testCustomTextLineDelimiterAndNewLine() throws Exception { String tableName = genCSVTable("testTextLineDelimiter", "b|1", "b|2"); testWithResult(format("select columns from table(%s(type => 'TeXT', lineDelimiter => '|'))", tableName), listOf("b"), listOf("1"), listOf("b"), listOf("2")); }
@Test public void testSubstr() throws Exception { testBuilder() .sqlQuery("select substr(n_name, 'UN.TE.') a from cp.\"tpch/nation.parquet\" where ilike(n_name, 'united%') = true") .unOrdered() .baselineColumns("a") .baselineValues("UNITED") .baselineValues("UNITED") .build() .run(); }
@Test // DX-9408 public void pruningBasedOnCurrentTimeStamp() throws Exception { final String query = "SELECT ts FROM cp.\"parquet/singlets.parquet\" WHERE ts > CURRENT_TIMESTAMP"; testPlanMatchingPatterns(query, new String[]{"Empty"}, "Filter"); testBuilder() .sqlQuery(query) .unOrdered() .baselineColumns("ts") .expectsEmptyResultSet() .go(); }
@Test public final void dateLessThan() throws Exception{ String query = "select datefield from elasticsearch." + schema + "." + table + " where datefield2 <= datefield"; testBuilder().sqlQuery(query).expectsEmptyResultSet() .go(); }
@Test // DRILL-3739 public void readingFromStorageHandleBasedTable2() throws Exception { try { test(String.format("alter session set \"%s\" = true", HivePluginOptions.HIVE_OPTIMIZE_SCAN_WITH_NATIVE_READERS)); testBuilder() .sqlQuery("SELECT * FROM hive.kv_sh ORDER BY key LIMIT 2") .ordered() .baselineColumns("key", "value") .expectsEmptyResultSet() .go(); } finally { test(String.format("alter session set \"%s\" = false", HivePluginOptions.HIVE_OPTIMIZE_SCAN_WITH_NATIVE_READERS)); } }
@Test // Union-All where same column is projected twice in left and right child public void testUnionAll6_1() throws Exception { String query = "select n_nationkey, n_nationkey from cp.\"tpch/nation.parquet\" union all select r_regionkey, r_regionkey from cp.\"tpch/region.parquet\""; testBuilder() .sqlQuery(query) .unOrdered() .csvBaselineFile("testframework/testUnionAllQueries/q6_1.tsv") .baselineTypes(MinorType.INT, MinorType.INT) .baselineColumns("n_nationkey", "n_nationkey1") .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 public void testFlattenWithProjIntoScan_6() throws Exception { String query = "select flatten(t.z) as zflat from cp.\"/jsoninput/input2.json\" t"; testPlanSubstrPatterns(query, new String[] {"columns=[`z`]"}, null); testBuilder().sqlQuery(query).unOrdered().baselineColumns("zflat") .baselineValues(mapOf("orange", "yellow", "pink", "red")) .baselineValues(mapOf("pink", "purple")) .baselineValues(mapOf("pink", "lilac")) .baselineValues(mapOf("orange", "stucco")) .go(); }