This is an automated email from the ASF dual-hosted git repository. dzamo pushed a commit to branch 1.20 in repository https://gitbox.apache.org/repos/asf/drill.git
commit aa5341df6896efae3cf31d03f6d9c42053672593 Author: James Turton <[email protected]> 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 f7000c86cd..3f6d216692 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;
