This is an automated email from the ASF dual-hosted git repository.
zabetak pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
commit efae863fe010ed5c4b7de1874a336ed93b3c60b8
Author: Stamatis Zampetakis <zabe...@gmail.com>
AuthorDate: Tue Jun 7 17:02:12 2022 +0200
HIVE-26296: RuntimeException when executing EXPLAIN CBO JOINCOST on query
with JDBC tables (Stamatis Zampetakis, reviewed by Alessandro Solimando,
Krisztian Kasa)
Compute selectivity for all types of joins in the same way. There is no
particular reason to throw an exception when the Join operator is not
an instance of HiveJoin.
Closes #3349
---
data/scripts/q_test_author_book_tables.sql | 19 +++++
.../calcite/stats/HiveRelMdSelectivity.java | 5 +-
.../queries/clientpositive/cbo_jdbc_joincost.q | 34 ++++++++
.../clientpositive/llap/cbo_jdbc_joincost.q.out | 93 ++++++++++++++++++++++
4 files changed, 147 insertions(+), 4 deletions(-)
diff --git a/data/scripts/q_test_author_book_tables.sql
b/data/scripts/q_test_author_book_tables.sql
new file mode 100644
index 00000000000..9b5ff99266b
--- /dev/null
+++ b/data/scripts/q_test_author_book_tables.sql
@@ -0,0 +1,19 @@
+create table author
+(
+ id int,
+ fname varchar(20),
+ lname varchar(20)
+);
+insert into author values (1, 'Victor', 'Hugo');
+insert into author values (2, 'Alexandre', 'Dumas');
+
+create table book
+(
+ id int,
+ title varchar(100),
+ author int
+);
+insert into book
+values (1, 'Les Miserables', 1);
+insert into book
+values (2, 'The Count Of Monte Cristo', 2);
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdSelectivity.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdSelectivity.java
index 2c36d8f14e6..19bd13de9a1 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdSelectivity.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdSelectivity.java
@@ -149,11 +149,8 @@ public class HiveRelMdSelectivity extends RelMdSelectivity
{
if (j.isSemiJoin() || (j.getJoinType().equals(JoinRelType.ANTI))) {
ndvEstimate = Math.min(mq.getRowCount(j.getLeft()),
ndvEstimate);
- } else if (j instanceof HiveJoin) {
- ndvEstimate = Math.min(mq.getRowCount(j.getLeft())
- * mq.getRowCount(j.getRight()), ndvEstimate);
} else {
- throw new RuntimeException("Unexpected Join type: " +
j.getClass().getName());
+ ndvEstimate = Math.min(mq.getRowCount(j.getLeft()) *
mq.getRowCount(j.getRight()), ndvEstimate);
}
}
diff --git a/ql/src/test/queries/clientpositive/cbo_jdbc_joincost.q
b/ql/src/test/queries/clientpositive/cbo_jdbc_joincost.q
new file mode 100644
index 00000000000..7255f3b87b0
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/cbo_jdbc_joincost.q
@@ -0,0 +1,34 @@
+--!qt:database:mysql:q_test_author_book_tables.sql
+CREATE EXTERNAL TABLE author
+(
+ id int,
+ fname varchar(20),
+ lname varchar(20)
+)
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MYSQL",
+ "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver",
+ "hive.sql.jdbc.url" = "jdbc:mysql://localhost:3306/qtestDB",
+ "hive.sql.dbcp.username" = "root",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "author"
+ );
+
+CREATE EXTERNAL TABLE book
+(
+ id int,
+ title varchar(100),
+ author int
+)
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MYSQL",
+ "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver",
+ "hive.sql.jdbc.url" = "jdbc:mysql://localhost:3306/qtestDB",
+ "hive.sql.dbcp.username" = "root",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "book"
+ );
+
+EXPLAIN CBO JOINCOST SELECT a.lname, b.title FROM author a JOIN book b ON
a.id=b.author;
diff --git a/ql/src/test/results/clientpositive/llap/cbo_jdbc_joincost.q.out
b/ql/src/test/results/clientpositive/llap/cbo_jdbc_joincost.q.out
new file mode 100644
index 00000000000..0dc3effcef3
--- /dev/null
+++ b/ql/src/test/results/clientpositive/llap/cbo_jdbc_joincost.q.out
@@ -0,0 +1,93 @@
+PREHOOK: query: CREATE EXTERNAL TABLE author
+(
+ id int,
+ fname varchar(20),
+ lname varchar(20)
+)
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MYSQL",
+ "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver",
+ "hive.sql.jdbc.url" = "jdbc:mysql://localhost:3306/qtestDB",
+ "hive.sql.dbcp.username" = "root",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "author"
+ )
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@author
+POSTHOOK: query: CREATE EXTERNAL TABLE author
+(
+ id int,
+ fname varchar(20),
+ lname varchar(20)
+)
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MYSQL",
+ "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver",
+ "hive.sql.jdbc.url" = "jdbc:mysql://localhost:3306/qtestDB",
+ "hive.sql.dbcp.username" = "root",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "author"
+ )
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@author
+PREHOOK: query: CREATE EXTERNAL TABLE book
+(
+ id int,
+ title varchar(100),
+ author int
+)
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MYSQL",
+ "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver",
+ "hive.sql.jdbc.url" = "jdbc:mysql://localhost:3306/qtestDB",
+ "hive.sql.dbcp.username" = "root",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "book"
+ )
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@book
+POSTHOOK: query: CREATE EXTERNAL TABLE book
+(
+ id int,
+ title varchar(100),
+ author int
+)
+STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "MYSQL",
+ "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver",
+ "hive.sql.jdbc.url" = "jdbc:mysql://localhost:3306/qtestDB",
+ "hive.sql.dbcp.username" = "root",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "book"
+ )
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@book
+PREHOOK: query: EXPLAIN CBO JOINCOST SELECT a.lname, b.title FROM author a
JOIN book b ON a.id=b.author
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: EXPLAIN CBO JOINCOST SELECT a.lname, b.title FROM author a
JOIN book b ON a.id=b.author
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveJdbcConverter(convention=[JDBC.MYSQL]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
+ JdbcProject(lname=[$1], title=[$2]): rowcount = ###Masked###, cumulative
cost = ###Masked###, id = ###Masked###
+ JdbcJoin(condition=[=($0, $3)], joinType=[inner]): rowcount =
###Masked###, cumulative cost = ###Masked###, id = ###Masked###
+ JdbcProject(id=[$0], lname=[$2]): rowcount = ###Masked###, cumulative
cost = ###Masked###, id = ###Masked###
+ JdbcFilter(condition=[IS NOT NULL($0)]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
+ JdbcHiveTableScan(table=[[default, author]], table:alias=[a]):
rowcount = ###Masked###, cumulative cost = ###Masked###, id = ###Masked###
+ JdbcProject(title=[$1], author=[$2]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
+ JdbcFilter(condition=[IS NOT NULL($2)]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
+ JdbcHiveTableScan(table=[[default, book]], table:alias=[b]):
rowcount = ###Masked###, cumulative cost = ###Masked###, id = ###Masked###
+
