@Test // DRILL-3374 public void partitionByCtasFromView() throws Exception { final String newTblName = "partitionByCtasColList2"; final String newView = "partitionByCtasColListView"; try { final String viewCreate = String.format("create or replace view %s.%s (col_int, col_varchar) " + "AS select cast(n_nationkey as int), cast(n_name as varchar(30)) from cp.\"tpch/nation.parquet\"", TEMP_SCHEMA, newView); final String ctasQuery = String.format("CREATE TABLE %s.%s PARTITION BY (col_int) AS SELECT * from %s.%s", TEMP_SCHEMA, newTblName, TEMP_SCHEMA, newView); test(viewCreate); test(ctasQuery); final String baselineQuery = "select cast(n_nationkey as int) as col_int, cast(n_name as varchar(30)) as col_varchar " + "from cp.\"tpch/nation.parquet\""; final String selectFromCreatedTable = String.format("select col_int, col_varchar from %s.%s", TEMP_SCHEMA, newTblName); testBuilder() .sqlQuery(selectFromCreatedTable) .unOrdered() .sqlBaselineQuery(baselineQuery) .build() .run(); final String viewDrop = String.format("DROP VIEW %s.%s", TEMP_SCHEMA, newView); test(viewDrop); } finally { FileUtils.deleteQuietly(new File(getDfsTestTmpSchemaLocation(), newTblName)); } }
.sqlQuery("select * from cp.\"tpch/nation.parquet\" where n_regionkey in (select r_regionkey from cp.\"tpch/region.parquet\")") .sqlBaselineQuery("select n_nationkey, n_name, n_regionkey, n_comment from cp.\"tpch/nation.parquet\" where n_regionkey in (select r_regionkey from cp.\"tpch/region.parquet\")") .build().run(); .sqlQuery("select * from cp.\"tpch/nation.parquet\" where (n_nationkey, n_name) in ( select n_nationkey, n_name from cp.\"tpch/nation.parquet\")") .sqlBaselineQuery("select n_nationkey, n_name, n_regionkey, n_comment from cp.\"tpch/nation.parquet\" where (n_nationkey, n_name) in ( select n_nationkey, n_name from cp.\"tpch/nation.parquet\")") .build().run(); "where n_regionkey in ( select r_regionkey from cp.\"tpch/region.parquet\") and " + " n_name in (select n_name from cp.\"tpch/nation.parquet\")") .build().run(); " from cp.\"tpch/nation.parquet\" n2, cp.\"tpch/region.parquet\" r2 " + " where n2.n_regionkey = r2.r_regionkey)") .build().run();
@Ignore @Test // DRILL-2092: count distinct, non distinct aggregate with group-by public void testDrill2092() throws Exception { String query = "select a1, b1, count(distinct c1) as dist1, \n" + "sum(c1) as sum1, count(c1) as cnt1, count(*) as cnt \n" + "from cp.\"agg/bugs/drill2092/input.json\" \n" + "group by a1, b1 order by a1, b1"; String baselineQuery = "select case when columns[0]='null' then cast(null as bigint) else cast(columns[0] as bigint) end as a1, \n" + "case when columns[1]='null' then cast(null as bigint) else cast(columns[1] as bigint) end as b1, \n" + "case when columns[2]='null' then cast(null as bigint) else cast(columns[2] as bigint) end as dist1, \n" + "case when columns[3]='null' then cast(null as bigint) else cast(columns[3] as bigint) end as sum1, \n" + "case when columns[4]='null' then cast(null as bigint) else cast(columns[4] as bigint) end as cnt1, \n" + "case when columns[5]='null' then cast(null as bigint) else cast(columns[5] as bigint) end as cnt \n" + "from cp.\"agg/bugs/drill2092/result.tsv\""; // NOTE: this type of query gets rewritten by Calcite into an inner join of subqueries, so // we need to test with both hash join and merge join testBuilder() .sqlQuery(query) .ordered() .optionSettingQueriesForTestQuery("alter system set \"planner.enable_hashjoin\" = true") .sqlBaselineQuery(baselineQuery) .build().run(); testBuilder() .sqlQuery(query) .ordered() .optionSettingQueriesForTestQuery("alter system set \"planner.enable_hashjoin\" = false") .sqlBaselineQuery(baselineQuery) .build().run(); }
@Test public void testRow_NumberInView() throws Exception { try { test("use dfs_test;"); final String view1 = "create view TestFunctionsWithTypeExpoQueries_testViewShield1 as \n" + "select rnum, position_id, " + " ntile(4) over(order by position_id) " + " from (select position_id, row_number() " + " over(order by position_id) as rnum " + " from cp.\"employee.json\")"; final String view2 = "create view TestFunctionsWithTypeExpoQueries_testViewShield2 as \n" + "select row_number() over(order by position_id) as rnum, " + " position_id, " + " ntile(4) over(order by position_id) " + " from cp.\"employee.json\""; test(view1); test(view2); testBuilder() .sqlQuery("select * from TestFunctionsWithTypeExpoQueries_testViewShield1") .ordered() .sqlBaselineQuery("select * from TestFunctionsWithTypeExpoQueries_testViewShield2") .build() .run(); } finally { test("drop view TestFunctionsWithTypeExpoQueries_testViewShield1;"); test("drop view TestFunctionsWithTypeExpoQueries_testViewShield2;"); } }
@Test public void testCastIntToVarchar() throws Exception { final String sqlQueryBasic = "select city from %s where cast(review_count as varchar) = '33'"; testBuilder() .sqlQuery(String.format(sqlQueryBasic, ELASTIC_TABLE)) .unOrdered() .baselineColumns("city") .baselineValues("San Diego") .go(); // There is some logic to allow some casts to be handled by elastic implicit conversion // in the PredicateAnalyzer, which constructs an equality or range filter. // Concat is added here to force scripts to be used, specifically to test the // cast within a script. String sqlQuery = "select city from %s where concat(cast(review_count as varchar), '') = '33'"; testBuilder() .sqlQuery(String.format(sqlQuery, ELASTIC_TABLE)) .unOrdered() .baselineColumns("city") .baselineValues("San Diego") .go(); testBuilder() .sqlQuery(String.format(sqlQuery, ELASTIC_TABLE)) .unOrdered() .sqlBaselineQuery(String.format(sqlQuery, PARQUET_TABLE)) .go(); testPlanSubstrPatterns(String.format(sqlQuery, ELASTIC_TABLE), new String[]{ "(doc[\\\"review_count\\\"].empty) ? false : ( ( Long.toString(doc[\\\"review_count\\\"].value) + '' ) == '33' )" }, null); }
.sqlQuery(queryPmulti) .sqlBaselineQuery(queryPmulti) .go();
@Test public void testBinaryNumericFunctions() throws Exception { final List<String> binaryFunctions = Lists.newArrayList("+", "-", "*" /*, "/"*/); // sanity check the parquet file // NOTE: this uses the test builder constructor directly instead of the testBuilder() helper method // because of an elastic specific override to run tests twice (disabling project pushdown on one run) // this is not needed here and using it actually causes a failure because of the simple way it rewrites // queries (looking for elasticsearch and replacing it, a word that appears in this path...) new TestBuilder(allocator) .sqlQuery("select stars, review_count from " + PARQUET_TABLE) .unOrdered() .baselineColumns("stars", "review_count") .baselineValues(4.5f, 11) .baselineValues(3.5f, 22) .baselineValues(5.0f, 33) .baselineValues(4.5f, 11) .baselineValues(1f, 1) .go(); final String exprs = " cast((stars %s review_count) as double), cast((stars %s stars) as double), cast((review_count %s review_count) as double) "; for (String s : binaryFunctions) { String elasticQuery = String.format("select " + exprs + " from %s", s, s, s, ELASTIC_TABLE); testBuilder() .sqlQuery(elasticQuery) .unOrdered() .sqlBaselineQuery(String.format("select " + exprs + " from %s", s, s, s, PARQUET_TABLE)) .go(); } }
public void runTestAndValidate(String selection, String validationSelection, String inputTable, String outputFile, boolean sort) throws Exception { try { deleteTableIfExists(outputFile); test("use dfs_test"); // test("ALTER SESSION SET \"planner.add_producer_consumer\" = false"); String query = select(selection, inputTable, sort); System.out.println(outputFile); String create = "CREATE TABLE " + outputFile + " AS " + query; String validateQuery = select(validationSelection, outputFile, sort); test(create); test(validateQuery); // TODO: remove testBuilder() .unOrdered() .sqlQuery(validateQuery) .sqlBaselineQuery(query) .go(); Configuration hadoopConf = new Configuration(); Path output = new Path(getDfsTestTmpSchemaLocation(), outputFile); FileSystem fs = output.getFileSystem(hadoopConf); for (FileStatus file : fs.listStatus(output)) { ParquetMetadata footer = ParquetFileReader.readFooter(hadoopConf, file, SKIP_ROW_GROUPS); String version = footer.getFileMetaData().getKeyValueMetaData().get(DREMIO_VERSION_PROPERTY); assertEquals(DremioVersionInfo.getVersion(), version); PageHeaderUtil.validatePageHeaders(file.getPath(), footer); } } finally { deleteTableIfExists(outputFile); } }
@Test // DRILL-3679, DRILL-3680 public void testWindowFunInNestSubQSlice1() throws Exception { // final String query = " select n_nationkey , n_regionkey , " + " lead(n_regionkey) OVER ( PARTITION BY n_regionkey ORDER BY n_nationkey) lead_c2 " + " FROM (SELECT n_nationkey ,n_regionkey, " + " ntile(3) over(PARTITION BY n_regionkey ORDER BY n_nationkey) " + " FROM cp.\"tpch/nation.parquet\") " + " order by n_regionkey, n_nationkey"; test(query); final String baselineQuery = "select n_nationkey , n_regionkey , " + " lead(n_regionkey) OVER ( PARTITION BY n_regionkey ORDER BY n_nationkey) lead_c2 " + "FROM cp.\"tpch/nation.parquet\" " + "order by n_regionkey, n_nationkey"; try{ testBuilder() .sqlQuery(query) .ordered() .optionSettingQueriesForTestQuery("alter session set \"planner.slice_target\" = 1") .sqlBaselineQuery(baselineQuery) .build() .run(); } finally { test("alter session set \"planner.slice_target\" = " + ExecConstants.SLICE_TARGET_DEFAULT); } }
@Test public void testPartitionRoundRobin() throws Exception { try { setSessionOption(ExecConstants.SLICE_TARGET, "1"); final String input = "sys.options"; final String tableName = "dfs_test.roundrobintable"; final String query = "CREATE TABLE " + tableName + " PARTITION BY (TYPE, KIND) as SELECT * FROM " + input; runSQL(query); testBuilder() .unOrdered() .sqlQuery("SELECT name, kind, type, status, num_val, string_val FROM " + tableName) .sqlBaselineQuery("SELECT name, kind, type, status, num_val, string_val FROM " + input) .go(); final String tableName2 = "dfs_test.roundrobintable2"; final String query2 = "CREATE TABLE " + tableName2 + " ROUNDROBIN PARTITION BY (TYPE, KIND) as SELECT * FROM " + input; runSQL(query2); testBuilder() .unOrdered() .sqlQuery("SELECT name, kind, type, status, num_val, string_val FROM " + tableName2) .sqlBaselineQuery("SELECT name, kind, type, status, num_val, string_val FROM " + input) .go(); } finally { setSessionOption(ExecConstants.SLICE_TARGET, String.valueOf(ExecConstants.SLICE_TARGET_DEFAULT)); } }
@Test public void testPartitionHash() throws Exception { try { setSessionOption(ExecConstants.SLICE_TARGET, "1"); final String input = "sys.options"; final String tableName = "dfs_test.hashpartitiontable"; final String query = "CREATE TABLE " + tableName + " PARTITION BY (TYPE, KIND) as SELECT * FROM " + input; runSQL(query); testBuilder() .unOrdered() .sqlQuery("SELECT name, kind, type, status, num_val, string_val FROM " + tableName) .sqlBaselineQuery("SELECT name, kind, type, status, num_val, string_val FROM " + input) .go(); final String tableName2 = "dfs_test.hashpartitiontable2"; final String query2 = "CREATE TABLE " + tableName2 + " HASH PARTITION BY (TYPE, KIND) as SELECT * FROM " + input; runSQL(query2); testBuilder() .unOrdered() .sqlQuery("SELECT name, kind, type, status, num_val, string_val FROM " + tableName2) .sqlBaselineQuery("SELECT name, kind, type, status, num_val, string_val FROM " + input) .go(); } finally { setSessionOption(ExecConstants.SLICE_TARGET, String.valueOf(ExecConstants.SLICE_TARGET_DEFAULT)); } }
@Test // DRILL-4147 // group-by on top of union-all public void testDrill4147_2() throws Exception { final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString(); final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString(); final String query = String.format("Select o_custkey, count(*) as cnt from \n" + " (SELECT o_custkey FROM dfs_test.\"%s\" \n" + "Union All SELECT o_custkey FROM dfs_test.\"%s\") \n" + "group by o_custkey", l, r); // Validate the plan final String[] expectedPlan = {"(?s)UnionExchange.*HashAgg.*HashToRandomExchange.*UnionAll.*"}; final String[] excludedPlan = {}; try { test(sliceTargetSmall); PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan); testBuilder() .optionSettingQueriesForTestQuery(sliceTargetSmall) .optionSettingQueriesForBaseline(sliceTargetDefault) .unOrdered() .sqlQuery(query) .sqlBaselineQuery(query) .build() .run(); } finally { test(sliceTargetDefault); } }
@Test // DRILL-4147 // base case public void testDrill4147_1() throws Exception { final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString(); final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString(); final String query = String.format("SELECT o_custkey FROM dfs_test.\"%s\" \n" + "Union All SELECT o_custkey FROM dfs_test.\"%s\"", l, r); // Validate the plan final String[] expectedPlan = {"UnionExchange.*\n", ".*Project.*\n" + ".*UnionAll"}; final String[] excludedPlan = {}; try { test(sliceTargetSmall); PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan); testBuilder() .optionSettingQueriesForTestQuery(sliceTargetSmall) .optionSettingQueriesForBaseline(sliceTargetDefault) .unOrdered() .sqlQuery(query) .sqlBaselineQuery(query) .build() .run(); } finally { test(sliceTargetDefault); } }
@Test // DRILL-4147 // union-all above a hash join public void testDrill4147_3() throws Exception { final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString(); final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString(); final String query = String.format("SELECT o_custkey FROM \n" + " (select o1.o_custkey from dfs_test.\"%s\" o1 inner join dfs_test.\"%s\" o2 on o1.o_orderkey = o2.o_custkey) \n" + " Union All SELECT o_custkey FROM dfs_test.\"%s\" where o_custkey < 10", l, r, l); // Validate the plan final String[] expectedPlan = {"(?s)UnionExchange.*UnionAll.*HashJoin.*"}; final String[] excludedPlan = {}; try { test(sliceTargetSmall); PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan); testBuilder() .optionSettingQueriesForTestQuery(sliceTargetSmall) .optionSettingQueriesForBaseline(sliceTargetDefault) .unOrdered() .sqlQuery(query) .sqlBaselineQuery(query) .build() .run(); } finally { test(sliceTargetDefault); } }
@Test // DRILL-3679, DRILL-3680 public void testWindowFunInNestSubQ() throws Exception { test("set planner.slice_target = 1"); final String query = " select n_nationkey , n_regionkey , " + " lead(n_regionkey) OVER ( PARTITION BY n_regionkey ORDER BY n_nationkey) lead_c2 " + " FROM (SELECT n_nationkey ,n_regionkey, " + " ntile(3) over(PARTITION BY n_regionkey ORDER BY n_nationkey) " + " FROM cp.\"tpch/nation.parquet\") " + " order by n_regionkey, n_nationkey"; test(query); final String baselineQuery = "select n_nationkey , n_regionkey , " + " lead(n_regionkey) OVER ( PARTITION BY n_regionkey ORDER BY n_nationkey) lead_c2 " + "FROM cp.\"tpch/nation.parquet\" " + "order by n_regionkey, n_nationkey"; testBuilder() .sqlQuery(query) .ordered() .sqlBaselineQuery(baselineQuery) .build() .run(); }
@Test // DRILL-4147 // union-distinct base case public void testDrill4147_1() throws Exception { final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString(); final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString(); final String query = String.format("SELECT o_custkey FROM dfs_test.\"%s\" \n" + "Union distinct SELECT o_custkey FROM dfs_test.\"%s\"", l, r); // Validate the plan final String[] expectedPlan = {"(?s)UnionExchange.*HashAgg.*HashToRandomExchange.*UnionAll.*"}; final String[] excludedPlan = {}; try { test(sliceTargetSmall); PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan); testBuilder() .optionSettingQueriesForTestQuery(sliceTargetSmall) .optionSettingQueriesForBaseline(sliceTargetDefault) .unOrdered() .sqlQuery(query) .sqlBaselineQuery(query) .build() .run(); } finally { test(sliceTargetDefault); } }
@Test // see DRILL-2408 public void testWriteEmptyFileAfterFlush() throws Exception { final String outputFile = "testparquetwriteremptyfiles_test_write_empty_file_after_flush"; deleteTableIfExists(outputFile); try { // this specific value will force a flush just after the final row is written // this may cause the creation of a new "empty" parquet file test("ALTER SESSION SET \"store.parquet.block-size\" = 19926"); final String query = "SELECT * FROM cp.\"employee.json\" LIMIT 100"; test("CREATE TABLE dfs_test.%s AS %s", outputFile, query); // this query will fail if an "empty" file was created testBuilder() .unOrdered() .sqlQuery("SELECT * FROM dfs_test.%s", outputFile) .sqlBaselineQuery(query) .go(); } finally { // restore the session option test("ALTER SESSION SET \"store.parquet.block-size\" = %d", ExecConstants.PARQUET_BLOCK_SIZE_VALIDATOR.getDefault().getNumVal()); deleteTableIfExists(outputFile); } }