You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by dz...@apache.org on 2022/05/19 15:23:36 UTC
[drill] branch master updated: DRILL-8227: JConnect and jTDS JDBC drivers do not implement Connection::getSchema (#2551)
This is an automated email from the ASF dual-hosted git repository.
dzamo pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/drill.git
The following commit(s) were added to refs/heads/master by this push:
new 0213d8e77d DRILL-8227: JConnect and jTDS JDBC drivers do not implement Connection::getSchema (#2551)
0213d8e77d is described below
commit 0213d8e77d6739fcd9072719743ba06a4a3744ba
Author: James Turton <91...@users.noreply.github.com>
AuthorDate: Thu May 19 17:23:31 2022 +0200
DRILL-8227: JConnect and jTDS JDBC drivers do not implement Connection::getSchema (#2551)
---
contrib/storage-jdbc/pom.xml | 22 ++
.../drill/exec/store/jdbc/JdbcCatalogSchema.java | 14 +-
.../exec/store/jdbc/TestJdbcPluginWithMSSQL.java | 338 +++++++++++++++++++++
.../src/test/resources/mssql-test-data.ms.sql | 72 +++++
4 files changed, 444 insertions(+), 2 deletions(-)
diff --git a/contrib/storage-jdbc/pom.xml b/contrib/storage-jdbc/pom.xml
index 0be11a8aa4..2b947f5feb 100644
--- a/contrib/storage-jdbc/pom.xml
+++ b/contrib/storage-jdbc/pom.xml
@@ -35,6 +35,8 @@
<clickhouse.jdbc.version>0.3.1</clickhouse.jdbc.version>
<h2.version>2.1.210</h2.version>
<postgresql.version>42.3.3</postgresql.version>
+ <mssql-jdbc.version>9.2.0.jre8</mssql-jdbc.version>
+ <jtds.version>1.3.1</jtds.version>
</properties>
<dependencies>
@@ -85,6 +87,20 @@
<version>${postgresql.version}</version>
<scope>test</scope>
</dependency>
+ <dependency>
+ <!-- Even if our unit tests of MSSQL use the jtds driver, the mssql-jdbc
+ driver is still required by testcontainers. -->
+ <groupId>com.microsoft.sqlserver</groupId>
+ <artifactId>mssql-jdbc</artifactId>
+ <version>${mssql-jdbc.version}</version>
+ <scope>test</scope>
+ </dependency>
+ <dependency>
+ <groupId>net.sourceforge.jtds</groupId>
+ <artifactId>jtds</artifactId>
+ <version>${jtds.version}</version>
+ <scope>test</scope>
+ </dependency>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>mysql</artifactId>
@@ -115,6 +131,12 @@
<version>${testcontainers.version}</version>
<scope>test</scope>
</dependency>
+ <dependency>
+ <groupId>org.testcontainers</groupId>
+ <artifactId>mssqlserver</artifactId>
+ <version>${testcontainers.version}</version>
+ <scope>test</scope>
+ </dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
diff --git a/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcCatalogSchema.java b/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcCatalogSchema.java
index dce76aaebe..333544afb3 100644
--- a/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcCatalogSchema.java
+++ b/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcCatalogSchema.java
@@ -52,11 +52,21 @@ class JdbcCatalogSchema extends AbstractSchema {
String connectionSchemaName = null;
try (Connection con = source.getConnection();
ResultSet set = con.getMetaData().getCatalogs()) {
- connectionSchemaName = con.getSchema();
+
+ try {
+ connectionSchemaName = con.getSchema();
+ } catch (AbstractMethodError ex) {
+ // DRILL-8227. Some Sybase JDBC drivers still don't implement this method, e.g. JConnect, jTDS.
+ logger.warn(
+ "{} does not provide an implementation of getSchema(), default schema will be guessed",
+ con.getClass()
+ );
+ }
+
while (set.next()) {
final String catalogName = set.getString(1);
if (catalogName == null) {
- // DB2 is an example of why of this escape is needed.
+ // DRILL-8219. DB2 is an example of why of this escape is needed.
continue;
}
diff --git a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMSSQL.java b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMSSQL.java
new file mode 100644
index 0000000000..68c6871261
--- /dev/null
+++ b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMSSQL.java
@@ -0,0 +1,338 @@
+/*
+ * 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.categories.JdbcStorageTest;
+import org.apache.drill.common.logical.security.PlainCredentialsProvider;
+import org.apache.drill.common.logical.StoragePluginConfig.AuthMode;
+import org.apache.drill.common.types.TypeProtos.MinorType;
+import org.apache.drill.exec.physical.rowSet.DirectRowSet;
+import org.apache.drill.exec.physical.rowSet.RowSet;
+import org.apache.drill.exec.record.metadata.SchemaBuilder;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
+import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableMap;
+import org.apache.drill.test.ClusterFixture;
+import org.apache.drill.test.ClusterTest;
+import org.apache.drill.test.rowSet.RowSetUtilities;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Ignore;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.testcontainers.containers.MSSQLServerContainer;
+
+import java.math.BigDecimal;
+import java.util.TimeZone;
+import java.util.Map;
+
+import static org.junit.Assert.assertEquals;
+
+/**
+ * JDBC storage plugin tests against MSSQL.
+ */
+@Category(JdbcStorageTest.class)
+public class TestJdbcPluginWithMSSQL extends ClusterTest {
+
+ private static MSSQLServerContainer jdbcContainer;
+
+ @BeforeClass
+ public static void initMSSQL() throws Exception {
+ startCluster(ClusterFixture.builder(dirTestWatcher));
+ TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
+
+ jdbcContainer = new MSSQLServerContainer<>()
+ .withPassword("B!llyG0atGruff") // must meet mssql's complexity requirements
+
+ .withInitScript("mssql-test-data.ms.sql")
+ .withUrlParam("trustServerCertificate", "true")
+ .acceptLicense();
+
+ jdbcContainer.start();
+
+ Map<String, String> credentials = ImmutableMap.<String, String>builder()
+ .put("username", jdbcContainer.getUsername())
+ .put("password", jdbcContainer.getPassword())
+ .build();
+ PlainCredentialsProvider credentialsProvider = new PlainCredentialsProvider(credentials);
+
+ // To test for unimplemented Connection::getSchema (DRILL-8227), we use the jTDS driver
+ // in this class. The jTDS driver is only at JDBC v3 and so does not support isValid.
+ // To satisfy Hikari we must therefore specify a connection test query.
+ Map<String, Object> sourceParms = ImmutableMap.<String, Object>builder()
+ .put("connectionTestQuery", "select 1")
+ .build();
+
+ JdbcStorageConfig jdbcStorageConfig = new JdbcStorageConfig(
+ "net.sourceforge.jtds.jdbc.Driver",
+ jdbcContainer.getJdbcUrl().replaceFirst("jdbc:", "jdbc:jtds:"), // mangle the reported URL for jTDS
+ null,
+ null,
+ true,
+ false,
+ sourceParms,
+ credentialsProvider,
+ AuthMode.SHARED_USER.name(),
+ 100000
+ );
+ jdbcStorageConfig.setEnabled(true);
+ cluster.defineStoragePlugin("mssql", jdbcStorageConfig);
+ }
+
+ @AfterClass
+ public static void stopMSSQL() {
+ if (jdbcContainer != null) {
+ jdbcContainer.stop();
+ }
+ }
+
+ @Test
+ public void validateResult() throws Exception {
+ String sql = "SELECT person_id, first_name, last_name, address, city, state, zip, " +
+ "json, bigint_field, smallint_field, decimal_field, bit_field, " +
+ "double_field, float_field, datetime_field " +
+ "FROM mssql.dbo.person ORDER BY person_id";
+
+ DirectRowSet results = queryBuilder().sql(sql).rowSet();
+
+ TupleMetadata expectedSchema = new SchemaBuilder()
+ .addNullable("person_id", MinorType.INT, 10)
+ .addNullable("first_name", MinorType.VARCHAR, 38)
+ .addNullable("last_name", MinorType.VARCHAR, 38)
+ .addNullable("address", MinorType.VARCHAR, 38)
+ .addNullable("city", MinorType.VARCHAR, 38)
+ .addNullable("state", MinorType.VARCHAR, 2)
+ .addNullable("zip", MinorType.INT, 10)
+ .addNullable("json", MinorType.VARCHAR, 38)
+ .addNullable("bigint_field", MinorType.BIGINT, 19)
+ .addNullable("smallint_field", MinorType.INT, 5)
+ .addNullable("decimal_field", MinorType.VARDECIMAL, 15, 2)
+ .addNullable("bit_field", MinorType.BIT, 1)
+ .addNullable("double_field", MinorType.FLOAT8, 15)
+ .addNullable("float_field", MinorType.FLOAT8, 15)
+ // TODO: these two types are mapped to VARCHARS instead of date/time types
+ //.addNullable("date_field", MinorType.VARCHAR, 10)
+ //.addNullable("datetime2_field", MinorType.TIMESTAMP, 23, 3)
+ .addNullable("datetime_field", MinorType.TIMESTAMP, 23, 3)
+ .buildSchema();
+
+ RowSet expected = client.rowSetBuilder(expectedSchema)
+ .addRow(1, "first_name_1", "last_name_1", "1401 John F Kennedy Blvd",
+ "Philadelphia", "PA", 19107, "{ a : 5, b : 6 }", 123456789L, 1,
+ new BigDecimal("123.32"), 1, 1.0, 1.1,
+ 1330520401000L)
+ .addRow(2, "first_name_2", "last_name_2", "One Ferry Building",
+ "San Francisco", "CA", 94111, "{ z : [ 1, 2, 3 ] }", 45456767L, 3,
+ null, 0, 3.0, 3.1,
+ 1319974461000L)
+ .addRow(3, "first_name_3", "last_name_3", "176 Bowery",
+ "New York", "NY", 10012, "{ [ a, b, c ] }", 123090L, -3,
+ null, 1, 5.0, 5.1,
+ 1442936770000L)
+ .addRow(4, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null)
+ .build();
+
+ RowSetUtilities.verify(expected, results);
+ }
+
+ @Test
+ public void pushDownJoin() throws Exception {
+ String query = "select x.person_id from (select person_id from mssql.dbo.person) x "
+ + "join (select person_id from mssql.dbo.person) y on x.person_id = y.person_id";
+ queryBuilder()
+ .sql(query)
+ .planMatcher()
+ .exclude("Join")
+ .match();
+ }
+
+ @Test
+ public void pushDownJoinAndFilterPushDown() throws Exception {
+ String query = "select * from " +
+ "mssql.dbo.person e " +
+ "INNER JOIN " +
+ "mssql.dbo.person s " +
+ "ON e.first_name = s.first_name " +
+ "WHERE e.last_name > 'hello'";
+
+ queryBuilder()
+ .sql(query)
+ .planMatcher()
+ .exclude("Join", "Filter")
+ .match();
+ }
+
+ @Test
+ public void testPhysicalPlanSubmission() throws Exception {
+ String query = "select * from mssql.dbo.person";
+ String plan = queryBuilder().sql(query).explainJson();
+ assertEquals(4, queryBuilder().physical(plan).run().recordCount());
+ }
+
+ @Test
+ public void emptyOutput() {
+ String query = "select * from mssql.dbo.person e limit 0";
+
+ testBuilder()
+ .sqlQuery(query)
+ .expectsEmptyResultSet();
+ }
+
+ @Test
+ public void testExpressionsWithoutAlias() throws Exception {
+ String sql = "select count(*), 1+1+2+3+5+8+13+21+34, (1+sqrt(5))/2\n" +
+ "from mssql.dbo.person";
+
+ DirectRowSet results = queryBuilder().sql(sql).rowSet();
+
+ TupleMetadata expectedSchema = new SchemaBuilder()
+ .addNullable("EXPR$0", MinorType.INT, 10)
+ .addNullable("EXPR$1", MinorType.INT, 10)
+ .addNullable("EXPR$2", MinorType.FLOAT8, 15)
+ .build();
+
+ RowSet expected = client.rowSetBuilder(expectedSchema)
+ .addRow(4L, 88L, 1.618033988749895)
+ .build();
+
+ RowSetUtilities.verify(expected, results);
+ }
+
+ @Test
+ public void testExpressionsWithoutAliasesPermutations() throws Exception {
+ String query = "select EXPR$1, EXPR$0, EXPR$2\n" +
+ "from (select 1+1+2+3+5+8+13+21+34, (1+sqrt(5))/2, count(*) from mssql.dbo.person)";
+
+ testBuilder()
+ .sqlQuery(query)
+ .unOrdered()
+ .baselineColumns("EXPR$1", "EXPR$0", "EXPR$2")
+ .baselineValues(1.618033988749895, 88, 4)
+ .go();
+ }
+
+ @Test
+ public void testExpressionsWithAliases() throws Exception {
+ String query = "SELECT person_id AS ID, 1+1+2+3+5+8+13+21+34 as FIBONACCI_SUM, (1+sqrt(5))/2 as golden_ratio\n" +
+ "FROM mssql.dbo.person limit 2";
+
+ testBuilder()
+ .sqlQuery(query)
+ .unOrdered()
+ .baselineColumns("ID", "FIBONACCI_SUM", "golden_ratio")
+ .baselineValues(1, 88, 1.618033988749895)
+ .baselineValues(2, 88, 1.618033988749895)
+ .go();
+ }
+
+ @Test
+ public void testJoinStar() throws Exception {
+ String query = "select * from (select person_id from mssql.dbo.person) t1 join " +
+ "(select person_id from mssql.dbo.person) t2 on t1.person_id = t2.person_id";
+
+ testBuilder()
+ .sqlQuery(query)
+ .unOrdered()
+ .baselineColumns("person_id", "person_id0")
+ .baselineValues(1, 1)
+ .baselineValues(2, 2)
+ .baselineValues(3, 3)
+ .baselineValues(4, 4)
+ .go();
+ }
+
+ @Test
+ public void testSemiJoin() throws Exception {
+ String query =
+ "select person_id from mssql.dbo.person t1\n" +
+ "where exists (" +
+ "select person_id from mssql.dbo.person\n" +
+ "where t1.person_id = person_id)";
+ testBuilder()
+ .sqlQuery(query)
+ .unOrdered()
+ .baselineColumns("person_id")
+ .baselineValuesForSingleColumn(1, 2, 3, 4)
+ .go();
+ }
+
+ @Test
+ public void testInformationSchemaViews() throws Exception {
+ String query = "select * from information_schema.`views`";
+ run(query);
+ }
+
+ @Test
+ public void testJdbcTableTypes() throws Exception {
+ String query = "select distinct table_type from information_schema.`tables` " +
+ "where table_schema like 'mssql%'";
+ testBuilder()
+ .sqlQuery(query)
+ .unOrdered()
+ .baselineColumns("table_type")
+ .baselineValuesForSingleColumn("TABLE", "VIEW")
+ .go();
+ }
+
+ // DRILL-8090
+ @Test
+ public void testLimitPushDown() throws Exception {
+ String query = "select person_id, first_name, last_name from mssql.dbo.person limit 100";
+ queryBuilder()
+ .sql(query)
+ .planMatcher()
+ .include("Jdbc\\(.*SELECT TOP \\(100\\)")
+ .exclude("Limit\\(")
+ .match();
+ }
+
+ @Test
+ public void testLimitPushDownWithOrderBy() throws Exception {
+ String query = "select person_id from mssql.dbo.person order by first_name limit 100";
+ queryBuilder()
+ .sql(query)
+ .planMatcher()
+ .include("Jdbc\\(.*SELECT TOP \\(100\\).*ORDER BY.*\"first_name\"")
+ .exclude("Limit\\(")
+ .match();
+ }
+
+ @Test
+ @Ignore
+ // TODO: Enable once the push down logic has been clarified.
+ public void testLimitPushDownWithOffset() throws Exception {
+ String query = "select person_id, first_name from mssql.dbo.person limit 100 offset 10";
+ queryBuilder()
+ .sql(query)
+ .planMatcher()
+ .include("Jdbc\\(.*SELECT TOP \\(110\\)")
+ .include("Limit\\(")
+ .match();
+ }
+
+ @Test
+ public void testLimitPushDownWithConvertFromJson() throws Exception {
+ String query = "select convert_fromJSON(first_name)['ppid'] from mssql.dbo.person LIMIT 100";
+ queryBuilder()
+ .sql(query)
+ .planMatcher()
+ .include("Jdbc\\(.*SELECT TOP \\(100\\)")
+ .exclude("Limit\\(")
+ .match();
+ }
+}
diff --git a/contrib/storage-jdbc/src/test/resources/mssql-test-data.ms.sql b/contrib/storage-jdbc/src/test/resources/mssql-test-data.ms.sql
new file mode 100644
index 0000000000..b4d790700e
--- /dev/null
+++ b/contrib/storage-jdbc/src/test/resources/mssql-test-data.ms.sql
@@ -0,0 +1,72 @@
+CREATE TABLE person
+(
+ person_id INT NOT NULL IDENTITY PRIMARY KEY,
+
+ first_name NVARCHAR(255),
+ last_name NVARCHAR(255),
+ address NVARCHAR(255),
+ city NVARCHAR(255),
+ state NCHAR(2),
+ zip INT,
+
+ json NVARCHAR(255),
+
+ bigint_field BIGINT,
+ smallint_field SMALLINT,
+ numeric_field NUMERIC(10, 2),
+ double_field DOUBLE PRECISION,
+ float_field DOUBLE PRECISION,
+ real_field REAL,
+
+ time_field TIME,
+ datetime2_field DATETIME2,
+ date_field DATE,
+ datetime_field DATETIME,
+ year_field INT,
+
+ blob_field VARBINARY(255),
+ bit_field BIT,
+ decimal_field DECIMAL(15, 2),
+);
+
+INSERT INTO person (first_name, last_name, address, city, state, zip, bigint_field, smallint_field, numeric_field,
+ double_field, float_field, real_field,
+ time_field, datetime2_field, date_field, datetime_field, year_field,
+ json,
+ blob_field, bit_field,
+ decimal_field)
+VALUES ('first_name_1', 'last_name_1', '1401 John F Kennedy Blvd', 'Philadelphia', 'PA', 19107, 123456789, 1, 10.01,
+ 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 }',
+ convert(varbinary, 'this is a test'),
+ 1,
+ 123.321);
+
+INSERT INTO person (first_name, last_name, address, city, state, zip, bigint_field, smallint_field, numeric_field,
+ double_field, float_field, real_field,
+ time_field, datetime2_field, date_field, datetime_field, year_field,
+ json,
+ blob_field, bit_field)
+VALUES ('first_name_2', 'last_name_2', 'One Ferry Building', 'San Francisco', 'CA', 94111, 45456767, 3, 30.04,
+ 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 ] }',
+ convert(varbinary, 'this is a test 2'),
+ 0);
+
+insert into person (first_name, last_name, address, city, state, zip, bigint_field, smallint_field, numeric_field,
+ double_field, float_field, real_field,
+ time_field, datetime2_field, date_field, datetime_field, year_field,
+ json,
+ blob_field, bit_field)
+values ('first_name_3', 'last_name_3', '176 Bowery', 'New York', 'NY', 10012, 123090, -3, 55.12,
+ 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 ] }',
+ convert(varbinary, 'this is a test 3'),
+ 1);
+
+INSERT INTO person (first_name) VALUES (null);
+
+CREATE VIEW person_view AS SELECT * FROM person;