DRILL-6850: JDBC integration tests failures

- Fix RDBMS integration tests (expected decimal output and testCrossSourceMultiFragmentJoin)
- Update libraries versions
- Resolve NPE for empty result

2 files changed, 143 insertions, 121 deletions

diff --git a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithDerbyIT.java b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithDerbyIT.java
index e2e408956..65a1ea564 100644
--- a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithDerbyIT.java
+++ b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithDerbyIT.java
@@ -22,9 +22,14 @@ import org.apache.drill.PlanTestBase;
 import org.apache.drill.exec.expr.fn.impl.DateUtility;
 import org.apache.drill.exec.proto.UserBitShared;
 
+import org.apache.drill.exec.util.StoragePluginTestUtils;
+import org.junit.BeforeClass;
 import org.junit.Test;
 import org.junit.experimental.categories.Category;
 
+import java.math.BigDecimal;
+import java.nio.file.Paths;
+
 import static org.junit.Assert.assertEquals;
 
 /**
@@ -33,107 +38,114 @@ import static org.junit.Assert.assertEquals;
 @Category(JdbcStorageTest.class)
 public class TestJdbcPluginWithDerbyIT extends PlanTestBase {
 
+  private static final String TABLE_PATH = "jdbcmulti/";
+  private static final String TABLE_NAME = String.format("%s.`%s`", StoragePluginTestUtils.DFS_PLUGIN_NAME, TABLE_PATH);
+
+  @BeforeClass
+  public static void copyData() throws Exception {
+    dirTestWatcher.copyResourceToRoot(Paths.get(TABLE_PATH));
+  }
+
   @Test
   public void testCrossSourceMultiFragmentJoin() throws Exception {
-    testNoResult("USE derby");
     testNoResult("SET `planner.slice_target` = 1");
-    String query = "select x.person_id, y.salary from DRILL_DERBY_TEST.PERSON x "
-        + "join dfs.`${WORKING_PATH}/src/test/resources/jdbcmulti/` y on x.person_id = y.person_id ";
-    test(query);
+    test("select x.person_id, y.salary from derby.drill_derby_test.person x "
+        + "join %s y on x.person_id = y.person_id ", TABLE_NAME);
   }
 
   @Test
   public void validateResult() throws Exception {
-
     // Skip date, time, and timestamp types since derby mangles these due to improper timezone support.
     testBuilder()
-            .sqlQuery(
-                    "select PERSON_ID, FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIP, JSON, BIGINT_FIELD, SMALLINT_FIELD, " +
-                            "NUMERIC_FIELD, BOOLEAN_FIELD, DOUBLE_FIELD, FLOAT_FIELD, REAL_FIELD, TIME_FIELD, TIMESTAMP_FIELD, " +
-                            "DATE_FIELD, CLOB_FIELD from derby.DRILL_DERBY_TEST.PERSON")
-            .ordered()
-            .baselineColumns("PERSON_ID", "FIRST_NAME", "LAST_NAME", "ADDRESS", "CITY", "STATE", "ZIP", "JSON",
-                    "BIGINT_FIELD", "SMALLINT_FIELD", "NUMERIC_FIELD", "BOOLEAN_FIELD", "DOUBLE_FIELD",
-                    "FLOAT_FIELD", "REAL_FIELD", "TIME_FIELD", "TIMESTAMP_FIELD", "DATE_FIELD", "CLOB_FIELD")
-            .baselineValues(1, "first_name_1", "last_name_1", "1401 John F Kennedy Blvd",   "Philadelphia",     "PA",
-                            19107, "{ a : 5, b : 6 }",            123456L,         1, 10.01, false, 1.0, 1.1, 111.00,
-                            DateUtility.parseLocalTime("13:00:01.0"), DateUtility.parseLocalDateTime("2012-02-29 13:00:01.0"), DateUtility.parseLocalDate("2012-02-29"), "some clob data 1")
-            .baselineValues(2, "first_name_2", "last_name_2", "One Ferry Building",         "San Francisco",    "CA",
-                            94111, "{ foo : \"abc\" }",            95949L,         2, 20.02, true, 2.0, 2.1, 222.00,
-                            DateUtility.parseLocalTime("23:59:59.0"),  DateUtility.parseLocalDateTime("1999-09-09 23:59:59.0"), DateUtility.parseLocalDate("1999-09-09"), "some more clob data")
-            .baselineValues(3, "first_name_3", "last_name_3", "176 Bowery",                 "New York",         "NY",
-                            10012, "{ z : [ 1, 2, 3 ] }",           45456L,        3, 30.04, true, 3.0, 3.1, 333.00,
-                            DateUtility.parseLocalTime("11:34:21.0"),  DateUtility.parseLocalDateTime("2011-10-30 11:34:21.0"), DateUtility.parseLocalDate("2011-10-30"), "clobber")
-            .baselineValues(4, null, null, "2 15th St NW", "Washington", "DC", 20007, "{ z : { a : 1, b : 2, c : 3 } " +
-                    "}", -67L, 4, 40.04, false, 4.0, 4.1, 444.00, DateUtility.parseLocalTime("16:00:01.0"), DateUtility.parseLocalDateTime("2015-06-01 16:00:01.0"),  DateUtility.parseLocalDate("2015-06-01"), "xxx")
-            .baselineValues(5, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
-                            null, null, null, null)
-            .build().run();
+        .sqlQuery(
+            "select person_id, first_name, last_name, address, city, state, zip, json, bigint_field, smallint_field, " +
+                "numeric_field, boolean_field, double_field, float_field, real_field, time_field, timestamp_field, " +
+                "date_field, clob_field from derby.`drill_derby_test`.person")
+        .ordered()
+        .baselineColumns("person_id", "first_name", "last_name", "address", "city", "state", "zip", "json",
+            "bigint_field", "smallint_field", "numeric_field", "boolean_field", "double_field", "float_field",
+            "real_field", "time_field", "timestamp_field", "date_field", "clob_field")
+        .baselineValues(1, "first_name_1", "last_name_1", "1401 John F Kennedy Blvd",   "Philadelphia",     "PA", 19107,
+            "{ a : 5, b : 6 }", 123456L, 1, new BigDecimal("10.01"), false, 1.0, 1.1, 111.00,
+            DateUtility.parseLocalTime("13:00:01.0"), DateUtility.parseLocalDateTime("2012-02-29 13:00:01.0"),
+            DateUtility.parseLocalDate("2012-02-29"), "some clob data 1")
+        .baselineValues(2, "first_name_2", "last_name_2", "One Ferry Building", "San Francisco", "CA", 94111,
+            "{ foo : \"abc\" }", 95949L, 2, new BigDecimal("20.02"), true, 2.0, 2.1, 222.00,
+            DateUtility.parseLocalTime("23:59:59.0"),  DateUtility.parseLocalDateTime("1999-09-09 23:59:59.0"),
+            DateUtility.parseLocalDate("1999-09-09"), "some more clob data")
+        .baselineValues(3, "first_name_3", "last_name_3", "176 Bowery", "New York", "NY", 10012, "{ z : [ 1, 2, 3 ] }",
+            45456L, 3, new BigDecimal("30.04"), true, 3.0, 3.1, 333.00, DateUtility.parseLocalTime("11:34:21.0"),
+            DateUtility.parseLocalDateTime("2011-10-30 11:34:21.0"), DateUtility.parseLocalDate("2011-10-30"), "clobber")
+        .baselineValues(4, null, null, "2 15th St NW", "Washington", "DC", 20007, "{ z : { a : 1, b : 2, c : 3 } }",
+            -67L, 4, new BigDecimal("40.04"), false, 4.0, 4.1, 444.00, DateUtility.parseLocalTime("16:00:01.0"),
+            DateUtility.parseLocalDateTime("2015-06-01 16:00:01.0"),  DateUtility.parseLocalDate("2015-06-01"), "xxx")
+        .baselineValues(5, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
+            null, null, null, null)
+        .go();
   }
 
   @Test
   public void pushdownJoin() throws Exception {
     testNoResult("use derby");
-    String query = "select x.person_id from (select person_id from DRILL_DERBY_TEST.PERSON) x "
-            + "join (select person_id from DRILL_DERBY_TEST.PERSON) y on x.person_id = y.person_id ";
+    String query = "select x.person_id from (select person_id from derby.drill_derby_test.person) x "
+            + "join (select person_id from derby.drill_derby_test.person) y on x.person_id = y.person_id ";
     testPlanMatchingPatterns(query, new String[]{}, new String[]{"Join"});
   }
 
   @Test
   public void pushdownJoinAndFilterPushDown() throws Exception {
     final String query = "select * from \n" +
-            "derby.DRILL_DERBY_TEST.PERSON e\n" +
-            "INNER JOIN \n" +
-            "derby.DRILL_DERBY_TEST.PERSON s\n" +
-            "ON e.FIRST_NAME = s.FIRST_NAME\n" +
-            "WHERE e.LAST_NAME > 'hello'";
+        "derby.drill_derby_test.person e\n" +
+        "INNER JOIN \n" +
+        "derby.drill_derby_test.person s\n" +
+        "ON e.FIRST_NAME = s.FIRST_NAME\n" +
+        "WHERE e.LAST_NAME > 'hello'";
 
     testPlanMatchingPatterns(query, new String[] {}, new String[] { "Join", "Filter" });
   }
 
   @Test
   public void pushdownAggregation() throws Exception {
-    final String query = "select count(*) from derby.DRILL_DERBY_TEST.PERSON";
+    final String query = "select count(*) from derby.drill_derby_test.person";
     testPlanMatchingPatterns(query, new String[] {}, new String[] { "Aggregate" });
   }
 
   @Test
   public void pushdownDoubleJoinAndFilter() throws Exception {
     final String query = "select * from \n" +
-            "derby.DRILL_DERBY_TEST.PERSON e\n" +
-            "INNER JOIN \n" +
-            "derby.DRILL_DERBY_TEST.PERSON s\n" +
-            "ON e.PERSON_ID = s.PERSON_ID\n" +
-            "INNER JOIN \n" +
-            "derby.DRILL_DERBY_TEST.PERSON ed\n" +
-            "ON e.PERSON_ID = ed.PERSON_ID\n" +
-            "WHERE s.FIRST_NAME > 'abc' and ed.FIRST_NAME > 'efg'";
+        "derby.drill_derby_test.person e\n" +
+        "INNER JOIN \n" +
+        "derby.drill_derby_test.person s\n" +
+        "ON e.person_ID = s.person_ID\n" +
+        "INNER JOIN \n" +
+        "derby.drill_derby_test.person ed\n" +
+        "ON e.person_ID = ed.person_ID\n" +
+        "WHERE s.first_name > 'abc' and ed.first_name > 'efg'";
     testPlanMatchingPatterns(query, new String[] {}, new String[] { "Join", "Filter" });
   }
 
   @Test
   public void showTablesDefaultSchema() throws Exception {
-    testNoResult("use derby");
-    assertEquals(1, testRunAndPrint(UserBitShared.QueryType.SQL, "show tables like 'PERSON'"));
+    test("use derby");
+    assertEquals(1, testRunAndPrint(UserBitShared.QueryType.SQL, "show tables like 'person'"));
   }
 
   @Test
   public void describe() throws Exception {
-    testNoResult("use derby");
-    assertEquals(19, testRunAndPrint(UserBitShared.QueryType.SQL, "describe PERSON"));
+    test("use derby");
+    assertEquals(19, testRunAndPrint(UserBitShared.QueryType.SQL, "describe drill_derby_test.person"));
   }
 
   @Test
   public void ensureDrillFunctionsAreNotPushedDown() throws Exception {
     // This should verify that we're not trying to push CONVERT_FROM into the JDBC storage plugin. If were pushing
     // this function down, the SQL query would fail.
-    testNoResult("select CONVERT_FROM(JSON, 'JSON') from derby.DRILL_DERBY_TEST.PERSON where PERSON_ID = 4");
+    testNoResult("select CONVERT_FROM(JSON, 'JSON') from derby.drill_derby_test.person where person_ID = 4");
   }
 
   @Test
   public void pushdownFilter() throws Exception {
-    testNoResult("use derby");
-    String query = "select * from DRILL_DERBY_TEST.PERSON where PERSON_ID = 1";
+    String query = "select * from derby.drill_derby_test.person where person_ID = 1";
     testPlanMatchingPatterns(query, new String[]{}, new String[]{"Filter"});
   }
 }
diff --git a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java
index 0f377e3e8..c8396b509 100644
--- a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java
+++ b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java
@@ -24,6 +24,8 @@ import org.apache.drill.PlanTestBase;
 import org.junit.Test;
 import org.junit.experimental.categories.Category;
 
+import java.math.BigDecimal;
+
 /**
  * JDBC storage plugin tests against MySQL.
  * Note: it requires libaio.so library in the system
@@ -35,77 +37,77 @@ public class TestJdbcPluginWithMySQLIT extends PlanTestBase {
   public void validateResult() throws Exception {
 
     testBuilder()
-            .sqlQuery(
-                    "select person_id, " +
-                            "first_name, last_name, address, city, state, zip, " +
-                            "bigint_field, smallint_field, numeric_field, " +
-                            "boolean_field, double_field, float_field, real_field, " +
-                            "date_field, datetime_field, year_field, time_field, " +
-                            "json, text_field, tiny_text_field, medium_text_field, long_text_field, " +
-                            "blob_field, bit_field, enum_field " +
-                    "from mysql.`drill_mysql_test`.person")
-            .ordered()
-            .baselineColumns("person_id",
-                    "first_name", "last_name", "address", "city", "state", "zip",
-                    "bigint_field", "smallint_field", "numeric_field",
-                    "boolean_field",
-                    "double_field", "float_field", "real_field",
-                    "date_field", "datetime_field", "year_field", "time_field",
-                    "json", "text_field", "tiny_text_field", "medium_text_field", "long_text_field",
-                    "blob_field", "bit_field", "enum_field")
-            .baselineValues(1,
-                    "first_name_1", "last_name_1", "1401 John F Kennedy Blvd", "Philadelphia", "PA", 19107,
-                    123456789L, 1, 10.01,
-                    false,
-                    1.0, 1.1, 1.2,
-                    DateUtility.parseLocalDate("2012-02-29"), DateUtility.parseLocalDateTime("2012-02-29 13:00:01.0"), DateUtility.parseLocalDate("2015-01-01"), DateUtility.parseLocalTime("13:00:01.0"),
-                    "{ a : 5, b : 6 }",
-                    "It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout",
-                    "xxx",
-                    "a medium piece of text",
-                    "a longer piece of text this is going on.....",
-                    "this is a test".getBytes(),
-                    true, "XXX")
-            .baselineValues(2,
-                    "first_name_2", "last_name_2", "One Ferry Building", "San Francisco", "CA", 94111,
-                    45456767L, 3, 30.04,
-                    true,
-                    3.0, 3.1, 3.2,
-                    DateUtility.parseLocalDate("2011-10-30"), DateUtility.parseLocalDateTime("2011-10-30 11:34:21.0"), DateUtility.parseLocalDate("2015-01-01"), DateUtility.parseLocalTime("11:34:21.0"),
-                    "{ z : [ 1, 2, 3 ] }",
-                    "It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout",
-                    "abc",
-                    "a medium piece of text 2",
-                    "somewhat more text",
-                    "this is a test 2".getBytes(),
-                    false, "YYY")
-            .baselineValues(3,
-                    "first_name_3", "last_name_3", "176 Bowery", "New York", "NY", 10012,
-                    123090L, -3, 55.12,
-                    false,
-                    5.0, 5.1, 5.55,
-                    DateUtility.parseLocalDate("2015-06-01"), DateUtility.parseLocalDateTime("2015-09-22 15:46:10.0"), DateUtility.parseLocalDate("1901-01-01"), DateUtility.parseLocalTime("16:00:01.0"),
-                    "{ [ a, b, c ] }",
-                    "Neque porro quisquam est qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit",
-                    "abc",
-                    "a medium piece of text 3",
-                    "somewhat more text",
-                    "this is a test 3".getBytes(),
-                    true, "ZZZ")
-            .baselineValues(5,
-                    null, null, null, null, null, null,
-                    null, null, null,
-                    null,
-                    null, null, null,
-                    null, null, null, null,
-                    null,
-                    null,
-                    null,
-                    null,
-                    null,
-                    null,
-                    null, "XXX")
-                  .build().run();
+        .sqlQuery(
+            "select person_id, " +
+                "first_name, last_name, address, city, state, zip, " +
+                "bigint_field, smallint_field, numeric_field, " +
+                "boolean_field, double_field, float_field, real_field, " +
+                "date_field, datetime_field, year_field, time_field, " +
+                "json, text_field, tiny_text_field, medium_text_field, long_text_field, " +
+                "blob_field, bit_field, enum_field " +
+            "from mysql.`drill_mysql_test`.person")
+        .ordered()
+        .baselineColumns("person_id",
+            "first_name", "last_name", "address", "city", "state", "zip",
+            "bigint_field", "smallint_field", "numeric_field",
+            "boolean_field",
+            "double_field", "float_field", "real_field",
+            "date_field", "datetime_field", "year_field", "time_field",
+            "json", "text_field", "tiny_text_field", "medium_text_field", "long_text_field",
+            "blob_field", "bit_field", "enum_field")
+        .baselineValues(1,
+            "first_name_1", "last_name_1", "1401 John F Kennedy Blvd", "Philadelphia", "PA", 19107,
+            123456789L, 1, new BigDecimal("10.01"),
+            false,
+            1.0, 1.1, 1.2,
+            DateUtility.parseLocalDate("2012-02-29"), DateUtility.parseLocalDateTime("2012-02-29 13:00:01.0"), DateUtility.parseLocalDate("2015-01-01"), DateUtility.parseLocalTime("13:00:01.0"),
+            "{ a : 5, b : 6 }",
+            "It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout",
+            "xxx",
+            "a medium piece of text",
+            "a longer piece of text this is going on.....",
+            "this is a test".getBytes(),
+            true, "XXX")
+        .baselineValues(2,
+            "first_name_2", "last_name_2", "One Ferry Building", "San Francisco", "CA", 94111,
+            45456767L, 3, new BigDecimal("30.04"),
+            true,
+            3.0, 3.1, 3.2,
+            DateUtility.parseLocalDate("2011-10-30"), DateUtility.parseLocalDateTime("2011-10-30 11:34:21.0"), DateUtility.parseLocalDate("2015-01-01"), DateUtility.parseLocalTime("11:34:21.0"),
+            "{ z : [ 1, 2, 3 ] }",
+            "It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout",
+            "abc",
+            "a medium piece of text 2",
+            "somewhat more text",
+            "this is a test 2".getBytes(),
+            false, "YYY")
+        .baselineValues(3,
+            "first_name_3", "last_name_3", "176 Bowery", "New York", "NY", 10012,
+            123090L, -3, new BigDecimal("55.12"),
+            false,
+            5.0, 5.1, 5.55,
+            DateUtility.parseLocalDate("2015-06-01"), DateUtility.parseLocalDateTime("2015-09-22 15:46:10.0"), DateUtility.parseLocalDate("1901-01-01"), DateUtility.parseLocalTime("16:00:01.0"),
+            "{ [ a, b, c ] }",
+            "Neque porro quisquam est qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit",
+            "abc",
+            "a medium piece of text 3",
+            "somewhat more text",
+            "this is a test 3".getBytes(),
+            true, "ZZZ")
+        .baselineValues(5,
+            null, null, null, null, null, null,
+            null, null, null,
+            null,
+            null, null, null,
+            null, null, null, null,
+            null,
+            null,
+            null,
+            null,
+            null,
+            null,
+            null, "XXX")
+            .go();
   }
 
   @Test
@@ -132,4 +134,12 @@ public class TestJdbcPluginWithMySQLIT extends PlanTestBase {
     testPhysicalPlanExecutionBasedOnQuery("select * from mysql.`drill_mysql_test`.person");
   }
 
+  @Test
+  public void emptyOutput() throws Exception {
+    String query = "select * from mysql.`drill_mysql_test`.person e limit 0";
+
+    test(query);
+  }
+
+
 }