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 <soumyakanti.das...@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