@Test public void testShortFile() throws Exception { // short file: 2 characters worth (shorter than the UTF-8 BOM), without BOMs File testFolder = tempDir.newFolder("testShortFilesFolder"); File testFile2 = new File(testFolder, "twobyte.csv"); PrintStream p2 = new PrintStream(testFile2); p2.print("y\n"); p2.close(); testBuilder() .sqlQuery(String.format("select * from table(dfs.\"%s\" (type => 'text', " + "fieldDelimiter => ',', lineDelimiter => '\n', extractHeader => true)) ", testFile2.getAbsolutePath())) .unOrdered() .baselineColumns("y") .expectsEmptyResultSet() .go(); }
@Test public void queryEmptyHiveTable() throws Exception { testBuilder() .sqlQuery("SELECT * FROM hive.empty_table") .expectsEmptyResultSet() .go(); }
@Test public void and() throws Exception { final String query = "SELECT * from hive.orc_region where r_regionkey <=2 AND r_name = 'EUROPE'"; testPlanMatchingPatterns(query, new String[] { quote("[leaf-0 = (LESS_THAN_EQUALS r_regionkey 2), leaf-1 = (EQUALS r_name EUROPE), expr = (and leaf-0 leaf-1)]") }); testBuilder() .sqlQuery(query) .unOrdered() .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 public final void dateGreaterThan() throws Exception{ String query = "select datefield from elasticsearch." + schema + "." + table + " where datefield > datefield"; testBuilder().sqlQuery(query).expectsEmptyResultSet() .go(); }
@Test public void queryPartitionedEmptyHiveTable() throws Exception { testBuilder() .sqlQuery("SELECT * FROM hive.partitioned_empty_table") .expectsEmptyResultSet() .go(); }
@Test public void testEmptyResultSet() throws Exception { testBuilder() .sqlQuery("select * from cp.\"store/json/json_simple_with_null.json\" where 1=0") .expectsEmptyResultSet() .build().run(); try { testBuilder() .sqlQuery("select * from cp.\"store/json/json_simple_with_null.json\"") .expectsEmptyResultSet() .build().run(); } catch (AssertionError ex) { assertTrue(ex.getMessage().contains("Different number of records returned - expected:<0> but was:<4>")); // this indicates successful completion of the test return; } throw new Exception("Test framework verification failed, expected failure on unexpected records."); }
@Test public void filterMergeAlwaysFalse() throws Exception { final String query = "SELECT \"integer\" FROM (SELECT \"integer\" FROM cp.\"jsoninput/input2.json\" " + "WHERE \"integer\" > 1) WHERE \"integer\" IS NULL"; testPlanSubstrPatterns(query, new String[]{"Empty"}, new String[]{"Values"}); testBuilder() .sqlQuery(query) .expectsEmptyResultSet() .go(); }
@Test public final void dateLessThan2() throws Exception{ // this test exposes a bug in the groovy scripts, but since this will soon be deprecated, no point in fixing assumeTrue(ElasticsearchCluster.USE_EXTERNAL_ES5); String query = "select datefield from elasticsearch." + schema + "." + table + " where datefield < CURRENT_TIMESTAMP and datefield2 <= datefield"; testBuilder().sqlQuery(query).expectsEmptyResultSet() .go(); }
protected void showTablesHelper(final String db, List<String> expectedTables) throws Exception { final String dbQualified = hivePluginName + "." + db; final TestBuilder testBuilder = testBuilder() .sqlQuery("SHOW TABLES IN " + dbQualified) .unOrdered() .baselineColumns("TABLE_SCHEMA", "TABLE_NAME"); if (expectedTables.size() == 0) { testBuilder.expectsEmptyResultSet(); } else { for (String tbl : expectedTables) { testBuilder.baselineValues(dbQualified, tbl); } } testBuilder.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 // 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 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 // 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 // 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 //DRILL_3004 public void testDRILL_3004() throws Exception { final String query = "SELECT\n" + " nations.N_NAME,\n" + " regions.R_NAME\n" + "FROM\n" + " cp.\"tpch/nation.parquet\" nations\n" + "JOIN\n" + " cp.\"tpch/region.parquet\" regions\n" + "on nations.N_REGIONKEY = regions.R_REGIONKEY " + "where 1 = 0"; testBuilder() .sqlQuery(query) .expectsEmptyResultSet() .optionSettingQueriesForTestQuery("ALTER SESSION SET \"planner.enable_hashjoin\" = false; " + "ALTER SESSION SET \"planner.disable_exchanges\" = true; ALTER SESSION SET \"planner.enable_mergejoin\" = true") .build() .run(); }
@Test public void testCreateViewInWSWithNoPermissionsForQueryUser() throws Exception { // Workspace dir owned by "processUser", workspace group is "group0" and "user2" is not part of "group0" final String viewSchema = MINIDFS_STORAGE_PLUGIN_NAME + ".dremioTestGrp0_755"; final String viewName = "view1"; updateClient(user2); test("USE " + viewSchema); final String query = "CREATE VIEW " + viewName + " AS SELECT " + "c_custkey, c_nationkey FROM cp.\"tpch/customer.parquet\" ORDER BY c_custkey;"; final String expErrorMsg = "PERMISSION ERROR: Permission denied: user=dremioTestUser2, access=WRITE, inode=\"/dremioTestGrp0_755/"; errorMsgTestHelper(query, expErrorMsg); // SHOW TABLES is expected to return no records as view creation fails above. testBuilder() .sqlQuery("SHOW TABLES") .expectsEmptyResultSet() .go(); test("SHOW FILES"); }
@Test public void testDateToChar() throws Exception { final String dateValues = "(values(date '1900-01-01'), (date '3500-01-01'), (date '2000-12-31'), (date '2005-12-30'), (date '2015-02-28'), (cast(null as date))) as t(date1)"; testBuilder() .sqlQuery("select to_char(date1, 'YYYY-MM-DD') res1 from " + dateValues) .unOrdered() .baselineColumns("res1") .baselineValues("1900-01-01") .baselineValues("3500-01-01") .baselineValues("2000-12-31") .baselineValues("2005-12-30") .baselineValues("2015-02-28") .baselineValues(null) .go(); try { testBuilder() .sqlQuery("select to_char(date1, 'invalid format') res1 from " + dateValues) .expectsEmptyResultSet().go(); fail("expected exception on invalid date format was not thrown."); } catch (RpcException ex) { assertTrue("Failed to match exception message", ex.getMessage().contains("Invalid date format string 'invalid format'")); } }
@Test public void testEmptyIndex() throws Exception { final String query = "select * from elasticsearch." + schema + "." + table; testBuilder() .sqlQuery("describe elasticsearch." + schema + "." + table) .unOrdered() .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") .baselineValues("city", "CHARACTER VARYING", "YES") .baselineValues("full_address", "CHARACTER VARYING", "YES") .baselineValues("review_count", "INTEGER", "YES") .baselineValues("stars", "FLOAT", "YES") .go(); testBuilder() .sqlQuery(query) .unOrdered() .baselineColumns(BatchSchema.SCHEMA_UNKNOWN_NO_DATA_COLNAME) .expectsEmptyResultSet() .go(); }
@Test public void resetAllSessionOptions() throws Exception { // change options test("SET \"%s\" = %b;", ENABLE_VERBOSE_ERRORS_KEY, true); // check changed testBuilder() .sqlQuery("SELECT bool_val FROM sys.options WHERE type = 'SESSION' AND name = '%s'", ENABLE_VERBOSE_ERRORS_KEY) .unOrdered() .baselineColumns("bool_val") .baselineValues(true) .build() .run(); // reset all options test("RESET ALL;"); // check no session options changed testBuilder() .sqlQuery("SELECT status FROM sys.options WHERE status <> 'DEFAULT' AND type = 'SESSION'") .unOrdered() .expectsEmptyResultSet() .build() .run(); }