DRILL-3791: MySQL tests for JDBC plugin

This commit adds integration tests for the JDBC plugin with MySQL. It
also refactors the existing Derby tests to have the same general pattern
as the MySQL tests: data is defined in an external .sql file and maven
is used to start/stop external resources for testing.

Add tests for ENUM and YEAR types.

Tests for the CLOB type with Derby.

This closes #251

6 files changed, 425 insertions, 188 deletions

diff --git a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPlugin.java b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPlugin.java
deleted file mode 100644
index 2eb419cda..000000000
--- a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPlugin.java
+++ /dev/null
@@ -1,181 +0,0 @@
-package org.apache.drill.exec.store.jdbc;
-
-import static org.junit.Assert.assertEquals;
-
-import java.net.InetAddress;
-import java.sql.Connection;
-
-import org.apache.commons.dbcp.BasicDataSource;
-import org.apache.derby.drda.NetworkServerControl;
-import org.apache.drill.BaseTestQuery;
-import org.apache.drill.PlanTestBase;
-import org.apache.drill.exec.proto.UserBitShared.QueryType;
-import org.junit.AfterClass;
-import org.junit.BeforeClass;
-import org.junit.Test;
-
-/**
- * Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE
- * file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the
- * License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
- * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations under the License.
- */
-public class TestJdbcPlugin extends PlanTestBase {
-
-  static NetworkServerControl server;
-
-  @BeforeClass
-  public static void setupDefaultTestCluster() throws Exception {
-    System.setProperty("derby.drda.startNetworkServer", "true");
-    server = new NetworkServerControl(InetAddress.getByName("localhost"),
-        20000,
-        "admin",
-        "admin");
-    java.io.PrintWriter consoleWriter = new java.io.PrintWriter(System.out, true);
-    server.start(consoleWriter);
-
-    BasicDataSource source = new BasicDataSource();
-    source.setUrl("jdbc:derby://localhost:20000/memory:testDB;create=true");
-    source.setDriverClassName("org.apache.derby.jdbc.ClientDriver");
-
-    final String insertValues1 = "INSERT INTO person VALUES (1, 'Smith', null, '{number:\"123 Main\"}','mtrx', "
-        + "'xy', 333.333, 444.444, 555.00, TIME('15:09:02'), DATE('1994-02-23'), TIMESTAMP('1962-09-23 03:23:34.234'),"
-        + " 666.66, 1, -1, false)";
-    final String insertValues2 = "INSERT INTO person (PersonId) VALUES (null)";
-    try (Connection c = source.getConnection()) {
-      c.createStatement().execute("CREATE TABLE person\n" +
-          "(\n" +
-          "PersonID int,\n" +
-          "LastName varchar(255),\n" +
-          "FirstName varchar(255),\n" +
-          "Address varchar(255),\n" +
-          "City varchar(255),\n" +
-          "Code char(2),\n" +
-          "dbl double,\n" +
-          "flt float,\n" +
-          "rel real,\n" +
-          "tm time,\n" +
-          "dt date,\n" +
-          "tms timestamp,\n" +
-          "num numeric(10,2), \n" +
-          "sm smallint,\n" +
-          "bi bigint,\n" +
-          "bool boolean\n" +
-
-          ")");
-
-      c.createStatement().execute(insertValues1);
-      c.createStatement().execute(insertValues2);
-      c.createStatement().execute(insertValues1);
-    }
-
-    BaseTestQuery.setupDefaultTestCluster();
-  }
-
-  @AfterClass
-  public static void shutdownDb() throws Exception {
-    server.shutdown();
-  }
-
-  @Test
-  public void validateResult() throws Exception {
-    // we'll test data except for date, time and timestamps. Derby mangles these due to improper timezone support.
-    testBuilder()
-        .sqlQuery(
-            "select PERSONID, LASTNAME, FIRSTNAME, ADDRESS, CITY, CODE, DBL, FLT, REL, NUM, SM, BI, BOOL from testdb.PERSON")
-        .ordered()
-        .baselineColumns("PERSONID", "LASTNAME", "FIRSTNAME", "ADDRESS", "CITY", "CODE", "DBL", "FLT", "REL",
-            "NUM", "SM", "BI", "BOOL")
-        .baselineValues(1, "Smith", null, "{number:\"123 Main\"}", "mtrx", "xy", 333.333, 444.444, 555.00,
-            666.66, 1, -1l, false)
-        .baselineValues(null, null, null, null, null, null, null, null, null, null, null, null, null)
-        .baselineValues(1, "Smith", null, "{number:\"123 Main\"}", "mtrx", "xy", 333.333, 444.444, 555.00,
-            666.66, 1, -1l, false)
-        .build().run();
-  }
-
-  @Test
-  public void queryDefaultSchema() throws Exception {
-    testNoResult("select * from testdb.PERSON");
-  }
-
-  @Test
-  public void queryDifferentCase() throws Exception {
-    testNoResult("select * from testdb.person");
-  }
-
-  @Test
-  public void pushdownJoin() throws Exception {
-    testNoResult("use testdb");
-    String query = "select x.PersonId from (select PersonId from person)x "
-        + "join (select PersonId from person)y on x.PersonId = y.PersonId ";
-    testPlanMatchingPatterns(query, new String[] {}, new String[] { "Join" });
-
-  }
-
-  @Test
-  public void pushdownJoinAndFilterPushDown() throws Exception {
-    final String query = "select * from \n" +
-        "testdb.PERSON e\n" +
-        "INNER JOIN \n" +
-        "testdb.PERSON s\n" +
-        "ON e.FirstName = s.FirstName\n" +
-        "WHERE e.LastName > 'hello'";
-
-    testPlanMatchingPatterns(query, new String[] {}, new String[] { "Join", "Filter" });
-  }
-
-  @Test
-  public void pushdownAggregation() throws Exception {
-    final String query = "select count(*) from \n" +
-        "testdb.PERSON";
-
-    testPlanMatchingPatterns(query, new String[] {}, new String[] { "Aggregate" });
-  }
-
-  @Test
-  public void pushdownDoubleJoinAndFilter() throws Exception {
-    final String query = "select * from \n" +
-        "testdb.PERSON e\n" +
-        "INNER JOIN \n" +
-        "testdb.PERSON s\n" +
-        "ON e.PersonId = s.PersonId\n" +
-        "INNER JOIN \n" +
-        "testdb.PERSON ed\n" +
-        "ON e.PersonId = ed.PersonId\n" +
-        "WHERE s.FirstName > 'abc' and ed.FirstName > 'efg'";
-    testPlanMatchingPatterns(query, new String[] {}, new String[] { "Join", "Filter" });
-  }
-
-  @Test
-  public void showTablesDefaultSchema() throws Exception {
-    testNoResult("use testdb");
-    assertEquals(1, testRunAndPrint(QueryType.SQL, "show tables like 'PERSON'"));
-  }
-
-  @Test
-  public void describe() throws Exception {
-    testNoResult("use testdb");
-    assertEquals(16, testRunAndPrint(QueryType.SQL, "describe 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(Address, 'JSON') from testdb.person where PersonId = 1");
-  }
-
-  @Test
-  public void pushdownFilter() throws Exception {
-    testNoResult("use testdb");
-    String query = "select * from person where PersonId = 1";
-    testPlanMatchingPatterns(query, new String[] {}, new String[] { "Filter" });
-  }
-}
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
new file mode 100644
index 000000000..021ed665d
--- /dev/null
+++ b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithDerbyIT.java
@@ -0,0 +1,129 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.drill.exec.store.jdbc;
+
+import org.apache.drill.PlanTestBase;
+import org.apache.drill.exec.proto.UserBitShared;
+
+import org.joda.time.DateTime;
+
+import org.junit.Test;
+
+import static org.junit.Assert.assertEquals;
+
+/**
+ * JDBC storage plugin tests against Derby.
+ */
+public class TestJdbcPluginWithDerbyIT extends PlanTestBase {
+
+  @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,
+                            new DateTime(1970, 1, 1, 13, 0, 1), new DateTime(2012, 2, 29, 13, 0, 1), new DateTime(2012, 2, 29, 0, 0, 0), "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,
+                            new DateTime(1970, 1, 1, 23, 59, 59),  new DateTime(1999, 9, 9, 23, 59, 59), new DateTime(1999, 9, 9, 0, 0, 0), "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,
+                            new DateTime(1970, 1, 1, 11, 34, 21),  new DateTime(2011, 10, 30, 11, 34, 21), new DateTime(2011, 10, 30, 0, 0, 0), "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, new DateTime(1970, 1, 1, 16, 0, 1), new DateTime
+                    (2015, 6, 1, 16, 0, 1),  new DateTime(2015, 6, 1, 0, 0, 0), "xxx")
+            .baselineValues(5, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
+                            null, null, null, null)
+            .build().run();
+  }
+
+  @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 ";
+    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'";
+
+    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";
+    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'";
+    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
+  public void describe() throws Exception {
+    testNoResult("use derby");
+    assertEquals(19, testRunAndPrint(UserBitShared.QueryType.SQL, "describe 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");
+  }
+
+  @Test
+  public void pushdownFilter() throws Exception {
+    testNoResult("use derby");
+    String query = "select * from 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
new file mode 100644
index 000000000..34c4c1b2f
--- /dev/null
+++ b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java
@@ -0,0 +1,128 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.drill.exec.store.jdbc;
+
+import org.apache.drill.PlanTestBase;
+
+import org.joda.time.DateTime;
+
+import org.junit.Test;
+
+
+/**
+ * JDBC storage plugin tests against MySQL.
+ */
+public class TestJdbcPluginWithMySQLIT extends PlanTestBase {
+
+  @Test
+  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, " +
+                            "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",
+                    "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,
+                    new DateTime(2012, 2, 29, 0, 0, 0), new DateTime(2012, 2, 29, 13, 0, 1), new DateTime(2015, 1, 1, 0, 0, 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,
+                    new DateTime(2011, 10, 30, 0, 0, 0), new DateTime(2011, 10, 30, 11, 34, 21), new DateTime(2015, 1, 1, 0, 0, 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,
+                    new DateTime(2015, 6, 1, 0, 0, 0), new DateTime(2015, 9, 22, 15, 46, 10), new DateTime(1901, 1, 1, 0, 0, 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, "XXX")
+                  .build().run();
+  }
+
+  @Test
+  public void pushdownJoin() throws Exception {
+    String query = "select x.person_id from (select person_id from mysql.`drill_mysql_test`.person) x "
+            + "join (select person_id from mysql.`drill_mysql_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 " +
+            "mysql.`drill_mysql_test`.person e " +
+            "INNER JOIN " +
+            "mysql.`drill_mysql_test`.person s " +
+            "ON e.first_name = s.first_name " +
+            "WHERE e.last_name > 'hello'";
+
+    testPlanMatchingPatterns(query, new String[] {}, new String[] { "Join", "Filter" });
+  }
+
+}
diff --git a/contrib/storage-jdbc/src/test/resources/bootstrap-storage-plugins.json b/contrib/storage-jdbc/src/test/resources/bootstrap-storage-plugins.json
index 200ab93e4..add980847 100755
--- a/contrib/storage-jdbc/src/test/resources/bootstrap-storage-plugins.json
+++ b/contrib/storage-jdbc/src/test/resources/bootstrap-storage-plugins.json
@@ -1,10 +1,17 @@
 {
-  "storage":{
-    testdb : {
-      type:"jdbc",
-      enabled: true,
-      driver:"org.apache.derby.jdbc.ClientDriver",
-      url:"jdbc:derby://localhost:20000/memory:testDB;"
+    "storage" : {
+        derby : {
+          type    : "jdbc",
+          enabled : true,
+          driver  : "org.apache.derby.jdbc.ClientDriver",
+          url     : "jdbc:derby://localhost:${derby.reserved.port}/memory:${derby.database.name};user=root;password=root"
+        },
+        mysql : {
+          type    : "jdbc",
+          enabled : true,
+          driver  : "com.mysql.jdbc.Driver",
+          url     : "jdbc:mysql://localhost:${mysql.reserved.port}/${mysql.database.name}?user=root&password=root&useJDBCCompliantTimezoneShift=true"
+      }
     }
-  }
 }
+
diff --git a/contrib/storage-jdbc/src/test/resources/derby-test-data.sql b/contrib/storage-jdbc/src/test/resources/derby-test-data.sql
new file mode 100644
index 000000000..0d69724b4
--- /dev/null
+++ b/contrib/storage-jdbc/src/test/resources/derby-test-data.sql
@@ -0,0 +1,57 @@
+
+create SCHEMA drill_derby_test;
+set schema drill_derby_test;
+
+create table person (
+  person_id       INT NOT NULL PRIMARY KEY,
+
+  first_name      VARCHAR(255),
+  last_name       VARCHAR(255),
+  address         VARCHAR(255),
+  city            VARCHAR(255),
+  state           CHAR(2),
+  zip             INT,
+
+  json            VARCHAR(255),
+
+  bigint_field    BIGINT,
+  smallint_field  SMALLINT,
+  numeric_field   NUMERIC(10, 2),
+  boolean_field   BOOLEAN,
+  double_field    DOUBLE,
+  float_field     FLOAT,
+  real_field      REAL,
+
+  time_field      TIME,
+  timestamp_field TIMESTAMP,
+  date_field      DATE,
+
+  clob_field      CLOB
+);
+
+insert into person (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)
+  values (1, 'first_name_1', 'last_name_1', '1401 John F Kennedy Blvd', 'Philadelphia', 'PA', 19107,
+          '{ a : 5, b : 6 }', 123456, 1, 10.01, false, 1.0, 1.1, 111.00, '13:00:01', '2012-02-29 13:00:01',
+          '2012-02-29', 'some clob data 1');
+
+insert into person (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)
+  values (2, 'first_name_2', 'last_name_2', 'One Ferry Building', 'San Francisco', 'CA', 94111,
+          '{ foo : "abc" }', 95949, 2, 20.02, true, 2.0, 2.1, 222.00, '23:59:59', '1999-09-09 23:59:59', '1999-09-09',
+          'some more clob data');
+
+insert into person (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)
+  values (3, 'first_name_3', 'last_name_3', '176 Bowery', 'New York', 'NY', 10012,
+          '{ z : [ 1, 2, 3 ] }', 45456, 3, 30.04, true, 3.0, 3.1, 333.00, '11:34:21', '2011-10-30 11:34:21',
+          '2011-10-30', 'clobber');
+
+insert into person (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)
+  values (4, NULL, NULL, '2 15th St NW', 'Washington', 'DC', 20007,
+          '{ z : { a : 1, b : 2, c : 3 } }', -67, 4, 40.04, false, 4.0, 4.1, 444.00, '16:00:01',
+          '2015-06-01 16:00:01', '2015-06-01', 'xxx');
+
+insert into person (person_id) values (5);
\ No newline at end of file
diff --git a/contrib/storage-jdbc/src/test/resources/mysql-test-data.sql b/contrib/storage-jdbc/src/test/resources/mysql-test-data.sql
new file mode 100644
index 000000000..6875d9959
--- /dev/null
+++ b/contrib/storage-jdbc/src/test/resources/mysql-test-data.sql
@@ -0,0 +1,97 @@
+
+set global time_zone = "+00:00";
+
+use drill_mysql_test;
+
+create table x (
+  a   BLOB
+);
+
+insert into x (a) values ('this is a test');
+
+
+create table person (
+  person_id       INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+
+  first_name      VARCHAR(255),
+  last_name       VARCHAR(255),
+  address         VARCHAR(255),
+  city            VARCHAR(255),
+  state           CHAR(2),
+  zip             INT,
+
+  json            VARCHAR(255),
+
+  bigint_field    BIGINT,
+  smallint_field  SMALLINT,
+  numeric_field   NUMERIC(10, 2),
+  boolean_field   BOOLEAN,
+  double_field    DOUBLE,
+  float_field     FLOAT,
+  real_field      REAL,
+
+  time_field      TIME,
+  timestamp_field TIMESTAMP,
+  date_field      DATE,
+  datetime_field  DATETIME,
+  year_field      YEAR(4),
+
+  text_field      TEXT,
+  tiny_text_field TINYTEXT,
+  medium_text_field MEDIUMTEXT,
+  long_text_field LONGTEXT,
+  blob_field      BLOB,
+  bit_field       BIT,
+
+  enum_field      ENUM('XXX', 'YYY', 'ZZZ') NOT NULL
+);
+
+insert into person (first_name, last_name, address, city, state, zip, bigint_field, smallint_field, numeric_field,
+                    boolean_field, double_field, float_field, real_field,
+                    time_field, timestamp_field, date_field, datetime_field, year_field,
+                    json,
+                    text_field, tiny_text_field, medium_text_field, long_text_field, blob_field, bit_field,
+                    enum_field)
+    values ('first_name_1', 'last_name_1', '1401 John F Kennedy Blvd', 'Philadelphia', 'PA', 19107, 123456789, 1, 10.01,
+            false, 1.0, 1.1, 1.2,
+            '13:00:01', '2012-02-29 13:00:01', '2012-02-29', '2012-02-29 13:00:01', 2015,
+            '{ 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',
+            1,
+            'XXX');
+
+insert into person (first_name, last_name, address, city, state, zip, bigint_field, smallint_field, numeric_field,
+                    boolean_field, double_field, float_field, real_field,
+                    time_field, timestamp_field, date_field, datetime_field, year_field,
+                    json,
+                    text_field, tiny_text_field, medium_text_field, long_text_field, blob_field, bit_field,
+                    enum_field)
+    values ('first_name_2', 'last_name_2', 'One Ferry Building', 'San Francisco', 'CA', 94111, 45456767, 3, 30.04,
+            true, 3.0, 3.1, 3.2,
+            '11:34:21', '2011-10-30 11:34:21', '2011-10-30', '2011-10-30 11:34:21', '2015',
+            '{ 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',
+            0,
+            'YYY');
+
+insert into person (first_name, last_name, address, city, state, zip, bigint_field, smallint_field, numeric_field,
+                    boolean_field, double_field, float_field, real_field,
+                    time_field, timestamp_field, date_field, datetime_field, year_field,
+                    json,
+                    text_field, tiny_text_field, medium_text_field, long_text_field, blob_field, bit_field,
+                    enum_field)
+    values ('first_name_3', 'last_name_3', '176 Bowery', 'New York', 'NY', 10012, 123090, -3, 55.12,
+            false, 5.0, 5.1, 5.55,
+            '16:00:01', '2015-06-02 10:01:01', '2015-06-01', '2015-09-22 15:46:10', 1901,
+            '{ [ 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',
+            1,
+            'ZZZ');
+
+insert into person (person_id) values (5);