You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by kr...@apache.org on 2021/10/14 13:11:41 UTC
[hive] branch master updated: HIVE-25530: AssertionError when query
involves multiple JDBC tables and views (Soumyakanti Das,
reviewed by Alessandro Solimando, Krisztian Kasa)
This is an automated email from the ASF dual-hosted git repository.
krisztiankasa pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/master by this push:
new 3e37ba4 HIVE-25530: AssertionError when query involves multiple JDBC tables and views (Soumyakanti Das, reviewed by Alessandro Solimando, Krisztian Kasa)
3e37ba4 is described below
commit 3e37ba473545a691f5f32c08fc4b62b49257cab4
Author: Soumyakanti Das <so...@gmail.com>
AuthorDate: Thu Oct 14 06:11:24 2021 -0700
HIVE-25530: AssertionError when query involves multiple JDBC tables and views (Soumyakanti Das, reviewed by Alessandro Solimando, Krisztian Kasa)
---
.../hadoop/hive/ql/parse/CalcitePlanner.java | 19 ++-
.../queries/clientpositive/external_jdbc_join_mv.q | 62 +++++++
.../llap/external_jdbc_join_mv.q.out | 182 +++++++++++++++++++++
3 files changed, 260 insertions(+), 3 deletions(-)
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
index 7cecfc8..e6ae265 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
@@ -128,6 +128,7 @@ import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.tools.Frameworks;
import org.apache.calcite.util.CompositeList;
import org.apache.calcite.util.ImmutableBitSet;
+import org.apache.calcite.util.ImmutableNullableList;
import org.apache.calcite.util.Pair;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.hive.conf.Constants;
@@ -1612,6 +1613,9 @@ public class CalcitePlanner extends SemanticAnalyzer {
private final StatsSource statsSource;
private RelNode dummyTableScan;
+ Map<List<String>, JdbcConvention> jdbcConventionMap = new HashMap<>();
+ Map<List<String>, JdbcSchema> schemaMap = new HashMap<>();
+
protected CalcitePlannerAction(
Map<String, PrunedPartitionList> partitionCache,
StatsSource statsSource,
@@ -3021,11 +3025,20 @@ public class CalcitePlanner extends SemanticAnalyzer {
DataSource ds = JdbcSchema.dataSource(url, driver, user, pswd);
SqlDialect jdbcDialect = JdbcSchema.createDialect(SqlDialectFactoryImpl.INSTANCE, ds);
+ String dialectName = jdbcDialect.getClass().getName();
if (LOG.isDebugEnabled()) {
- LOG.debug("Dialect for table {}: {}", tableName, jdbcDialect.getClass().getName());
+ LOG.debug("Dialect for table {}: {}", tableName, dialectName);
}
- JdbcConvention jc = JdbcConvention.of(jdbcDialect, null, dataBaseType);
- JdbcSchema schema = new JdbcSchema(ds, jc.dialect, jc, catalogName, schemaName);
+
+ List<String> jdbcConventionKey = ImmutableNullableList.of(url, driver, user, pswd, dialectName, dataBaseType);
+ jdbcConventionMap.putIfAbsent(jdbcConventionKey, JdbcConvention.of(jdbcDialect, null, dataBaseType));
+ JdbcConvention jc = jdbcConventionMap.get(jdbcConventionKey);
+
+ List<String> schemaKey = ImmutableNullableList.of(url, driver, user, pswd, dialectName, dataBaseType,
+ catalogName, schemaName);
+ schemaMap.putIfAbsent(schemaKey, new JdbcSchema(ds, jc.dialect, jc, catalogName, schemaName));
+ JdbcSchema schema = schemaMap.get(schemaKey);
+
JdbcTable jt = (JdbcTable) schema.getTable(tableName);
if (jt == null) {
throw new SemanticException("Table " + tableName + " was not found in the database");
diff --git a/ql/src/test/queries/clientpositive/external_jdbc_join_mv.q b/ql/src/test/queries/clientpositive/external_jdbc_join_mv.q
new file mode 100644
index 0000000..fde2383
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/external_jdbc_join_mv.q
@@ -0,0 +1,62 @@
+set hive.support.concurrency=true;
+set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
+
+CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput';
+
+SELECT
+dboutput ( 'jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_join_mv;create=true','','',
+'CREATE TABLE person ("id" INTEGER, "name" VARCHAR(25), "jid" INTEGER, "cid" INTEGER)' );
+
+SELECT
+dboutput ( 'jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_join_mv;create=true','','',
+'CREATE TABLE country ("id" INTEGER, "name" VARCHAR(25))' );
+
+CREATE EXTERNAL TABLE person
+(
+ id int,
+ name varchar(25),
+ jid int,
+ cid int
+)
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "DERBY",
+ "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
+ "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_join_mv;create=true;collation=TERRITORY_BASED:PRIMARY",
+ "hive.sql.dbcp.username" = "APP",
+ "hive.sql.dbcp.password" = "mine",
+ "hive.sql.table" = "PERSON",
+ "hive.sql.dbcp.maxActive" = "1"
+);
+
+CREATE EXTERNAL TABLE country
+(
+ id int,
+ name varchar(25)
+)
+ STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+ TBLPROPERTIES (
+ "hive.sql.database.type" = "DERBY",
+ "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
+ "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_join_mv;create=true;collation=TERRITORY_BASED:PRIMARY",
+ "hive.sql.dbcp.username" = "APP",
+ "hive.sql.dbcp.password" = "mine",
+ "hive.sql.table" = "COUNTRY",
+ "hive.sql.dbcp.maxActive" = "1"
+ );
+
+CREATE TABLE job (
+ id int,
+ title varchar(20)
+) STORED AS ORC TBLPROPERTIES ('transactional'='true');
+
+CREATE MATERIALIZED VIEW mv1 AS SELECT id, title FROM job WHERE title = 'Software Engineer';
+
+explain cbo
+select *
+from person
+join job on person.jid = job.id
+join country on person.cid = country.id
+where job.title = 'Software Engineer';
+
+DROP MATERIALIZED VIEW mv1;
diff --git a/ql/src/test/results/clientpositive/llap/external_jdbc_join_mv.q.out b/ql/src/test/results/clientpositive/llap/external_jdbc_join_mv.q.out
new file mode 100644
index 0000000..538e12c
--- /dev/null
+++ b/ql/src/test/results/clientpositive/llap/external_jdbc_join_mv.q.out
@@ -0,0 +1,182 @@
+PREHOOK: query: CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput'
+PREHOOK: type: CREATEFUNCTION
+PREHOOK: Output: dboutput
+POSTHOOK: query: CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput'
+POSTHOOK: type: CREATEFUNCTION
+POSTHOOK: Output: dboutput
+PREHOOK: query: SELECT
+#### A masked pattern was here ####
+'CREATE TABLE person ("id" INTEGER, "name" VARCHAR(25), "jid" INTEGER, "cid" INTEGER)' )
+PREHOOK: type: QUERY
+PREHOOK: Input: _dummy_database@_dummy_table
+#### A masked pattern was here ####
+POSTHOOK: query: SELECT
+#### A masked pattern was here ####
+'CREATE TABLE person ("id" INTEGER, "name" VARCHAR(25), "jid" INTEGER, "cid" INTEGER)' )
+POSTHOOK: type: QUERY
+POSTHOOK: Input: _dummy_database@_dummy_table
+#### A masked pattern was here ####
+0
+PREHOOK: query: SELECT
+#### A masked pattern was here ####
+'CREATE TABLE country ("id" INTEGER, "name" VARCHAR(25))' )
+PREHOOK: type: QUERY
+PREHOOK: Input: _dummy_database@_dummy_table
+#### A masked pattern was here ####
+POSTHOOK: query: SELECT
+#### A masked pattern was here ####
+'CREATE TABLE country ("id" INTEGER, "name" VARCHAR(25))' )
+POSTHOOK: type: QUERY
+POSTHOOK: Input: _dummy_database@_dummy_table
+#### A masked pattern was here ####
+0
+PREHOOK: query: CREATE EXTERNAL TABLE person
+(
+ id int,
+ name varchar(25),
+ jid int,
+ cid int
+)
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "DERBY",
+ "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
+#### A masked pattern was here ####
+ "hive.sql.dbcp.username" = "APP",
+ "hive.sql.dbcp.password" = "mine",
+ "hive.sql.table" = "PERSON",
+ "hive.sql.dbcp.maxActive" = "1"
+)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@person
+POSTHOOK: query: CREATE EXTERNAL TABLE person
+(
+ id int,
+ name varchar(25),
+ jid int,
+ cid int
+)
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "DERBY",
+ "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
+#### A masked pattern was here ####
+ "hive.sql.dbcp.username" = "APP",
+ "hive.sql.dbcp.password" = "mine",
+ "hive.sql.table" = "PERSON",
+ "hive.sql.dbcp.maxActive" = "1"
+)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@person
+PREHOOK: query: CREATE EXTERNAL TABLE country
+(
+ id int,
+ name varchar(25)
+)
+ STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+ TBLPROPERTIES (
+ "hive.sql.database.type" = "DERBY",
+ "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
+#### A masked pattern was here ####
+ "hive.sql.dbcp.username" = "APP",
+ "hive.sql.dbcp.password" = "mine",
+ "hive.sql.table" = "COUNTRY",
+ "hive.sql.dbcp.maxActive" = "1"
+ )
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@country
+POSTHOOK: query: CREATE EXTERNAL TABLE country
+(
+ id int,
+ name varchar(25)
+)
+ STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+ TBLPROPERTIES (
+ "hive.sql.database.type" = "DERBY",
+ "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
+#### A masked pattern was here ####
+ "hive.sql.dbcp.username" = "APP",
+ "hive.sql.dbcp.password" = "mine",
+ "hive.sql.table" = "COUNTRY",
+ "hive.sql.dbcp.maxActive" = "1"
+ )
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@country
+PREHOOK: query: CREATE TABLE job (
+ id int,
+ title varchar(20)
+) STORED AS ORC TBLPROPERTIES ('transactional'='true')
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@job
+POSTHOOK: query: CREATE TABLE job (
+ id int,
+ title varchar(20)
+) STORED AS ORC TBLPROPERTIES ('transactional'='true')
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@job
+PREHOOK: query: CREATE MATERIALIZED VIEW mv1 AS SELECT id, title FROM job WHERE title = 'Software Engineer'
+PREHOOK: type: CREATE_MATERIALIZED_VIEW
+PREHOOK: Input: default@job
+PREHOOK: Output: database:default
+PREHOOK: Output: default@mv1
+POSTHOOK: query: CREATE MATERIALIZED VIEW mv1 AS SELECT id, title FROM job WHERE title = 'Software Engineer'
+POSTHOOK: type: CREATE_MATERIALIZED_VIEW
+POSTHOOK: Input: default@job
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@mv1
+POSTHOOK: Lineage: mv1.id SIMPLE [(job)job.FieldSchema(name:id, type:int, comment:null), ]
+POSTHOOK: Lineage: mv1.title SIMPLE []
+PREHOOK: query: explain cbo
+select *
+from person
+join job on person.jid = job.id
+join country on person.cid = country.id
+where job.title = 'Software Engineer'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@country
+PREHOOK: Input: default@job
+PREHOOK: Input: default@mv1
+PREHOOK: Input: default@person
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo
+select *
+from person
+join job on person.jid = job.id
+join country on person.cid = country.id
+where job.title = 'Software Engineer'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@country
+POSTHOOK: Input: default@job
+POSTHOOK: Input: default@mv1
+POSTHOOK: Input: default@person
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(id=[$0], name=[$1], jid=[$2], cid=[$3], id1=[$4], title=[CAST(_UTF-16LE'Software Engineer':VARCHAR(20) CHARACTER SET "UTF-16LE"):VARCHAR(20) CHARACTER SET "UTF-16LE"], id2=[$5], name1=[$6])
+ HiveJoin(condition=[=($3, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($2, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(id=[$0], name=[$1], jid=[$2], cid=[$3])
+ HiveJdbcConverter(convention=[JDBC.DERBY])
+ JdbcFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($3))])
+ JdbcHiveTableScan(table=[[default, person]], table:alias=[person])
+ HiveProject(id=[$0])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, mv1]], table:alias=[default.mv1])
+ HiveProject(id=[$0], name=[$1])
+ HiveJdbcConverter(convention=[JDBC.DERBY])
+ JdbcFilter(condition=[IS NOT NULL($0)])
+ JdbcHiveTableScan(table=[[default, country]], table:alias=[country])
+
+PREHOOK: query: DROP MATERIALIZED VIEW mv1
+PREHOOK: type: DROP_MATERIALIZED_VIEW
+PREHOOK: Input: default@mv1
+PREHOOK: Output: default@mv1
+POSTHOOK: query: DROP MATERIALIZED VIEW mv1
+POSTHOOK: type: DROP_MATERIALIZED_VIEW
+POSTHOOK: Input: default@mv1
+POSTHOOK: Output: default@mv1