hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From hashut...@apache.org
Subject [4/4] hive git commit: HIVE-15721 : Allow IN/NOT IN correlated subquery with aggregates (Vineet Garg via Ashutosh Chauhan)
Date Thu, 26 Jan 2017 07:17:24 GMT
HIVE-15721 : Allow  IN/NOT IN correlated subquery with aggregates (Vineet Garg via Ashutosh
Chauhan)

Signed-off-by: Ashutosh Chauhan <hashutosh@apache.org>


Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/2d501879
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/2d501879
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/2d501879

Branch: refs/heads/master
Commit: 2d501879c38f755273dfd97e4f66ac9217cd20b3
Parents: 4625964
Author: Vineet Garg <vgarg@hortonworks.com>
Authored: Wed Jan 25 23:16:14 2017 -0800
Committer: Ashutosh Chauhan <hashutosh@apache.org>
Committed: Wed Jan 25 23:16:14 2017 -0800

----------------------------------------------------------------------
 .../calcite/rules/HiveSubQueryRemoveRule.java   |   28 +
 .../apache/hadoop/hive/ql/parse/QBSubQuery.java |   72 +-
 .../hadoop/hive/ql/parse/SubQueryUtils.java     |   15 +-
 .../ql/udf/generic/GenericUDFSQCountCheck.java  |   22 +-
 .../hadoop/hive/ql/parse/TestQBSubQuery.java    |    2 +-
 .../clientnegative/subquery_in_implicit_gby.q   |   12 +-
 .../subquery_notin_implicit_gby.q               |   11 +
 .../test/queries/clientpositive/subquery_in.q   |   51 +
 .../queries/clientpositive/subquery_notin.q     |   18 +
 .../subquery_exists_implicit_gby.q.out          |    2 +-
 .../subquery_in_implicit_gby.q.out              |   39 +-
 .../subquery_notexists_implicit_gby.q.out       |    2 +-
 .../subquery_notin_implicit_gby.q.out           |   38 +
 .../clientpositive/llap/subquery_in.q.out       | 2187 +++++++++++++++++-
 .../clientpositive/llap/subquery_notin.q.out    |  624 +++++
 .../clientpositive/spark/subquery_in.q.out      | 1950 +++++++++++++++-
 16 files changed, 4963 insertions(+), 110 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveSubQueryRemoveRule.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveSubQueryRemoveRule.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveSubQueryRemoveRule.java
index 564ef7a..ffe569a 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveSubQueryRemoveRule.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveSubQueryRemoveRule.java
@@ -250,6 +250,34 @@ public abstract class HiveSubQueryRemoveRule extends RelOptRule{
                 switch (e.getKind()) {
                     case IN:
                         fields.addAll(builder.fields());
+                        // Transformation: sq_count_check(count(*), true) FILTER is generated
on top
+                        //  of subquery which is then joined (LEFT or INNER) with outer query
+                        //  This transformation is done to add run time check using sq_count_check
to
+                        //  throw an error if subquery is producing zero row, since with
aggregate this
+                        //  will produce wrong results (because we further rewrite such queries
into JOIN)
+                        if(isCorrScalarAgg) {
+                            // returns single row/column
+                            builder.aggregate(builder.groupKey(),
+                                    builder.count(false, "cnt_in"));
+
+                            if (!variablesSet.isEmpty()) {
+                                builder.join(JoinRelType.LEFT, builder.literal(true), variablesSet);
+                            } else {
+                                builder.join(JoinRelType.INNER, builder.literal(true), variablesSet);
+                            }
+
+                            SqlFunction inCountCheck = new SqlFunction("sq_count_check",
SqlKind.OTHER_FUNCTION, ReturnTypes.BIGINT,
+                                    InferTypes.RETURN_TYPE, OperandTypes.NUMERIC, SqlFunctionCategory.USER_DEFINED_FUNCTION);
+
+                            // we create FILTER (sq_count_check(count()) > 0) instead
of PROJECT because RelFieldTrimmer
+                            //  ends up getting rid of Project since it is not used further
up the tree
+                            builder.filter(builder.call(SqlStdOperatorTable.GREATER_THAN,
+                                    //true here indicates that sq_count_check is for IN/NOT
IN subqueries
+                                    builder.call(inCountCheck, builder.field("cnt_in"), builder.literal(true)),
+                                    builder.literal(0)));
+                            offset =  offset + 1;
+                            builder.push(e.rel);
+                        }
                 }
 
                 // First, the cross join

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/java/org/apache/hadoop/hive/ql/parse/QBSubQuery.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/QBSubQuery.java b/ql/src/java/org/apache/hadoop/hive/ql/parse/QBSubQuery.java
index 7ca722a..87ff581 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/parse/QBSubQuery.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/QBSubQuery.java
@@ -53,11 +53,21 @@ public class QBSubQuery implements ISubQueryJoinInfo {
       }
 
       switch(opNode.getType()) {
+        // opNode's type is always either KW_EXISTS or KW_IN never NOTEXISTS or NOTIN
+        //  to figure this out we need to check it's grand parent's parent
       case HiveParser.KW_EXISTS:
+        if(opNode.getParent().getParent().getParent() != null
+                && opNode.getParent().getParent().getParent().getType() == HiveParser.KW_NOT)
{
+          return NOT_EXISTS;
+        }
         return EXISTS;
       case HiveParser.TOK_SUBQUERY_OP_NOTEXISTS:
         return NOT_EXISTS;
       case HiveParser.KW_IN:
+        if(opNode.getParent().getParent().getParent() != null
+                && opNode.getParent().getParent().getParent().getType() == HiveParser.KW_NOT)
{
+          return NOT_IN;
+        }
         return IN;
       case HiveParser.TOK_SUBQUERY_OP_NOTIN:
         return NOT_IN;
@@ -544,13 +554,18 @@ public class QBSubQuery implements ISubQueryJoinInfo {
 
     boolean hasAggreateExprs = false;
     boolean hasWindowing = false;
+
+    // we need to know if aggregate is COUNT since IN corr subq with count aggregate
+    // is not special cased later in subquery remove rule
+    boolean hasCount = false;
     for(int i= selectExprStart; i < selectClause.getChildCount(); i++ ) {
 
       ASTNode selectItem = (ASTNode) selectClause.getChild(i);
       int r = SubQueryUtils.checkAggOrWindowing(selectItem);
 
-      hasWindowing = hasWindowing | ( r == 2);
-      hasAggreateExprs = hasAggreateExprs | ( r == 1 );
+      hasWindowing = hasWindowing | ( r == 3);
+      hasAggreateExprs = hasAggreateExprs | ( r == 1 | r== 2 );
+      hasCount = hasCount | ( r == 2 );
     }
 
 
@@ -602,30 +617,47 @@ public class QBSubQuery implements ISubQueryJoinInfo {
      * Specification doc for details.
      * Similarly a not exists on a SubQuery with a implied GBY will always return false.
      */
+      // Following is special cases for different type of subqueries which have aggregate
and no implicit group by
+      // and are correlatd
+      // * EXISTS/NOT EXISTS - NOT allowed, throw an error for now. We plan to allow this
later
+      // * SCALAR - only allow if it has non equi join predicate. This should return true
since later in subquery remove
+      //              rule we need to know about this case.
+      // * IN - always allowed, BUT returns true for cases with aggregate other than COUNT
since later in subquery remove
+      //        rule we need to know about this case.
+      // * NOT IN - always allow, but always return true because later subq remove rule will
generate diff plan for this case
       if (hasAggreateExprs &&
-              noImplicityGby ) {
-
-        if( hasCorrelation && (operator.getType() == SubQueryType.EXISTS
-                || operator.getType() == SubQueryType.NOT_EXISTS
-                || operator.getType() == SubQueryType.IN
-                || operator.getType() == SubQueryType.NOT_IN)) {
-          throw new CalciteSubquerySemanticException(ErrorMsg.INVALID_SUBQUERY_EXPRESSION.getMsg(
-                subQueryAST,
-                "A predicate on EXISTS/NOT EXISTS/IN/NOT IN SubQuery with implicit Aggregation(no
Group By clause) " +
-                        "cannot be rewritten."));
-        }
-        else if(operator.getType() == SubQueryType.SCALAR && hasNonEquiJoinPred)
{
-          // throw an error if predicates are not equal
+              noImplicityGby) {
+
+        if(operator.getType() == SubQueryType.EXISTS
+                || operator.getType() == SubQueryType.NOT_EXISTS) {
+          if(hasCorrelation) {
             throw new CalciteSubquerySemanticException(ErrorMsg.INVALID_SUBQUERY_EXPRESSION.getMsg(
                     subQueryAST,
-                    "Scalar subqueries with aggregate cannot have non-equi join predicate"));
+                    "A predicate on EXISTS/NOT EXISTS SubQuery with implicit Aggregation(no
Group By clause) " +
+                            "cannot be rewritten."));
+          }
+        }
+        else if(operator.getType() == SubQueryType.SCALAR) {
+            if(hasNonEquiJoinPred) {
+              throw new CalciteSubquerySemanticException(ErrorMsg.INVALID_SUBQUERY_EXPRESSION.getMsg(
+                      subQueryAST,
+                      "Scalar subqueries with aggregate cannot have non-equi join predicate"));
+            }
+            if(hasCorrelation) {
+              return true;
+            }
         }
-        else if(operator.getType() == SubQueryType.SCALAR && hasCorrelation) {
+        else if(operator.getType() == SubQueryType.IN) {
+          if(hasCount && hasCorrelation) {
             return true;
+          }
+        }
+        else if (operator.getType() == SubQueryType.NOT_IN) {
+            if(hasCorrelation) {
+              return true;
+            }
         }
-
       }
-
     return false;
   }
 
@@ -684,7 +716,7 @@ public class QBSubQuery implements ISubQueryJoinInfo {
       ASTNode selectItem = (ASTNode) selectClause.getChild(i);
       int r = SubQueryUtils.checkAggOrWindowing(selectItem);
 
-      containsWindowing = containsWindowing | ( r == 2);
+      containsWindowing = containsWindowing | ( r == 3);
       containsAggregationExprs = containsAggregationExprs | ( r == 1 );
     }
 

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/java/org/apache/hadoop/hive/ql/parse/SubQueryUtils.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/SubQueryUtils.java b/ql/src/java/org/apache/hadoop/hive/ql/parse/SubQueryUtils.java
index bd771f9..f0165dd 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/parse/SubQueryUtils.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/SubQueryUtils.java
@@ -32,6 +32,8 @@ import org.apache.hadoop.hive.ql.exec.FunctionRegistry;
 import org.apache.hadoop.hive.ql.exec.Operator;
 import org.apache.hadoop.hive.ql.parse.QBSubQuery.SubQueryType;
 import org.apache.hadoop.hive.ql.parse.QBSubQuery.SubQueryTypeDef;
+import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFCount;
+import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFResolver;
 
 public class SubQueryUtils {
 
@@ -252,7 +254,8 @@ public class SubQueryUtils {
    * @return
    * 0 if implies neither
    * 1 if implies aggregation
-   * 2 if implies windowing
+   * 2 if implies count
+   * 3 if implies windowing
    */
   static int checkAggOrWindowing(ASTNode expressionTree) throws SemanticException {
     int exprTokenType = expressionTree.getToken().getType();
@@ -262,12 +265,18 @@ public class SubQueryUtils {
       assert (expressionTree.getChildCount() != 0);
       if (expressionTree.getChild(expressionTree.getChildCount()-1).getType()
           == HiveParser.TOK_WINDOWSPEC) {
-        return 2;
+        return 3;
       }
       if (expressionTree.getChild(0).getType() == HiveParser.Identifier) {
         String functionName = SemanticAnalyzer.unescapeIdentifier(expressionTree.getChild(0)
             .getText());
-        if (FunctionRegistry.getGenericUDAFResolver(functionName) != null) {
+        GenericUDAFResolver udafResolver = FunctionRegistry.getGenericUDAFResolver(functionName);
+        if (udafResolver != null) {
+            // we need to know if it is COUNT since this is specialized for IN/NOT IN
+            // corr subqueries.
+          if(udafResolver instanceof GenericUDAFCount) {
+            return 2;
+          }
           return 1;
         }
       }

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFSQCountCheck.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFSQCountCheck.java
b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFSQCountCheck.java
index 53e6231..89fa0de 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFSQCountCheck.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFSQCountCheck.java
@@ -46,9 +46,9 @@ public class GenericUDFSQCountCheck extends GenericUDF {
 
   @Override
   public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException
{
-    if (arguments.length != 1) {
+    if (arguments.length > 2) {
       throw new UDFArgumentLengthException(
-          "Invalid scalar subquery expression. Subquery count check expected one argument
but received: " + arguments.length);
+          "Invalid scalar subquery expression. Subquery count check expected two argument
but received: " + arguments.length);
     }
 
     converters[0] = ObjectInspectorConverters.getConverter(arguments[0],
@@ -63,11 +63,23 @@ public class GenericUDFSQCountCheck extends GenericUDF {
   public Object evaluate(DeferredObject[] arguments) throws HiveException {
     Object valObject = arguments[0].get();
     assert(valObject != null);
+
     Long val = getLongValue(arguments, 0, converters);
     assert(val >= 0);
-    if(val > 1) {
-      throw new UDFArgumentException(
-              " Scalar subquery expression returns more than one row.");
+
+    switch (arguments.length){
+      case 1: //Scalar queries, should expect value/count less than 1
+        if (val > 1) {
+          throw new UDFArgumentException(
+                  " Scalar subquery expression returns more than one row.");
+        }
+        break;
+      case 2:
+        if (val == 0) { // IN/NOT IN subqueries with aggregate
+          throw new UDFArgumentException(
+                  " IN/NOT IN subquery with aggregate returning zero result. Currently this
is not supported.");
+        }
+        break;
     }
 
     resultLong.set(val);

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/test/org/apache/hadoop/hive/ql/parse/TestQBSubQuery.java
----------------------------------------------------------------------
diff --git a/ql/src/test/org/apache/hadoop/hive/ql/parse/TestQBSubQuery.java b/ql/src/test/org/apache/hadoop/hive/ql/parse/TestQBSubQuery.java
index f9db2c8..311a34d 100644
--- a/ql/src/test/org/apache/hadoop/hive/ql/parse/TestQBSubQuery.java
+++ b/ql/src/test/org/apache/hadoop/hive/ql/parse/TestQBSubQuery.java
@@ -122,7 +122,7 @@ public class TestQBSubQuery {
 
     Assert.assertEquals(0, SubQueryUtils.checkAggOrWindowing((ASTNode) select.getChild(0)));
     Assert.assertEquals(1, SubQueryUtils.checkAggOrWindowing((ASTNode) select.getChild(1)));
-    Assert.assertEquals(2, SubQueryUtils.checkAggOrWindowing((ASTNode) select.getChild(2)));
+    Assert.assertEquals(3, SubQueryUtils.checkAggOrWindowing((ASTNode) select.getChild(2)));
   }
 
   private ASTNode where(ASTNode qry) {

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/test/queries/clientnegative/subquery_in_implicit_gby.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientnegative/subquery_in_implicit_gby.q b/ql/src/test/queries/clientnegative/subquery_in_implicit_gby.q
index 338747e..4e87a33 100644
--- a/ql/src/test/queries/clientnegative/subquery_in_implicit_gby.q
+++ b/ql/src/test/queries/clientnegative/subquery_in_implicit_gby.q
@@ -1 +1,11 @@
-explain select * from part where p_partkey IN (select count(*) from part pp where pp.p_type
= part.p_type);
+create table t(i int, j int);
+insert into t values(0,1), (0,2);
+
+create table tt(i int, j int);
+insert into tt values(0,3);
+
+-- since this is correlated with COUNT aggregate and subquery returns 0 rows for group by
(i=j) it should be a runtime error
+select * from t where i IN (select count(i) from tt where tt.j = t.j);
+
+drop table t;
+drop table tt;

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/test/queries/clientnegative/subquery_notin_implicit_gby.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientnegative/subquery_notin_implicit_gby.q b/ql/src/test/queries/clientnegative/subquery_notin_implicit_gby.q
new file mode 100644
index 0000000..367603e
--- /dev/null
+++ b/ql/src/test/queries/clientnegative/subquery_notin_implicit_gby.q
@@ -0,0 +1,11 @@
+create table t(i int, j int);
+insert into t values(0,1), (0,2);
+
+create table tt(i int, j int);
+insert into tt values(0,3);
+
+-- since this is correlated with COUNT aggregate and subquery returns 0 rows for group by
(i=j) it should be a runtime error
+select * from t where i NOT IN (select count(i) from tt where tt.j = t.j);
+
+drop table t;
+drop table tt;

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/test/queries/clientpositive/subquery_in.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/subquery_in.q b/ql/src/test/queries/clientpositive/subquery_in.q
index 7293c77..4ba170a 100644
--- a/ql/src/test/queries/clientpositive/subquery_in.q
+++ b/ql/src/test/queries/clientpositive/subquery_in.q
@@ -55,6 +55,24 @@ part where part.p_size in
 	)
 ;
 
+-- agg, corr
+explain
+select p_mfgr, p_name, p_size
+from part b where b.p_size in
+	(select min(p_size)
+	 from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from
part) a
+	 where r <= 2 and b.p_mfgr = a.p_mfgr
+	)
+;
+
+select p_mfgr, p_name, p_size
+from part b where b.p_size in
+	(select min(p_size)
+	 from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from
part) a
+	 where r <= 2 and b.p_mfgr = a.p_mfgr
+	)
+;
+
 -- distinct, corr
 explain 
 select * 
@@ -188,6 +206,26 @@ explain select p_partkey from
 select p_partkey from 
 	(select p_size, p_partkey from part where p_name in (select p.p_name from part p left outer
join part pp on p.p_type = pp.p_type where pp.p_size = part.p_size)) subq;
 
+-- corr IN with COUNT aggregate
+explain select * from part where p_size IN (select count(*) from part pp where pp.p_type
= part.p_type);
+select * from part where p_size IN (select count(*) from part pp where pp.p_type = part.p_type);
+
+-- corr IN with aggregate other than COUNT
+explain select * from part where p_size in (select avg(pp.p_size) from part pp where pp.p_partkey
= part.p_partkey);
+select * from part where p_size in (select avg(pp.p_size) from part pp where pp.p_partkey
= part.p_partkey);
+
+-- corr IN with aggregate other than COUNT (MIN) with non-equi join
+explain select * from part where p_size in (select min(pp.p_size) from part pp where pp.p_partkey
> part.p_partkey);
+select * from part where p_size in (select min(pp.p_size) from part pp where pp.p_partkey
> part.p_partkey);
+
+-- corr IN with COUNT aggregate
+explain select * from part where p_size NOT IN (select count(*) from part pp where pp.p_type
= part.p_type);
+select * from part where p_size NOT IN (select count(*) from part pp where pp.p_type = part.p_type);
+
+-- corr IN with aggregate other than COUNT
+explain select * from part where p_size not in (select avg(pp.p_size) from part pp where
pp.p_partkey = part.p_partkey);
+select * from part where p_size not in (select avg(pp.p_size) from part pp where pp.p_partkey
= part.p_partkey);
+
 create table t(i int);
 insert into t values(1);
 insert into t values(0);
@@ -216,3 +254,16 @@ select * from part where p_size IN (select i from tnull);
 select * from tnull where i IN (select i from tnull);
 
 drop table tempty;
+
+create table t(i int, j int);
+insert into t values(0,1), (0,2);
+
+create table tt(i int, j int);
+insert into tt values(0,3);
+
+-- corr IN with aggregate other than COUNT return zero rows
+explain select * from t where i IN (select sum(i) from tt where tt.j = t.j);
+select * from t where i IN (select sum(i) from tt where tt.j = t.j);
+
+drop table t;
+drop table tt;

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/test/queries/clientpositive/subquery_notin.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/subquery_notin.q b/ql/src/test/queries/clientpositive/subquery_notin.q
index 0883c89..e23eb2b 100644
--- a/ql/src/test/queries/clientpositive/subquery_notin.q
+++ b/ql/src/test/queries/clientpositive/subquery_notin.q
@@ -57,6 +57,24 @@ part where part.p_size not in
 order by p_name, p_size
 ;
 
+-- agg, corr
+explain
+select p_mfgr, p_name, p_size
+from part b where b.p_size not in
+  (select min(p_size)
+  from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from
part) a
+  where r <= 2 and b.p_mfgr = a.p_mfgr
+  )
+;
+
+select p_mfgr, p_name, p_size
+from part b where b.p_size not in
+  (select min(p_size)
+  from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from
part) a
+  where r <= 2 and b.p_mfgr = a.p_mfgr
+  )
+;
+
 -- non agg, non corr, Group By in Parent Query
 select li.l_partkey, count(*)
 from lineitem li

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/test/results/clientnegative/subquery_exists_implicit_gby.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientnegative/subquery_exists_implicit_gby.q.out b/ql/src/test/results/clientnegative/subquery_exists_implicit_gby.q.out
index bab6138..b650309 100644
--- a/ql/src/test/results/clientnegative/subquery_exists_implicit_gby.q.out
+++ b/ql/src/test/results/clientnegative/subquery_exists_implicit_gby.q.out
@@ -1 +1 @@
-FAILED: SemanticException [Error 10250]: org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSubquerySemanticException:
Line 7:7 Invalid SubQuery expression ''val_9'': A predicate on EXISTS/NOT EXISTS/IN/NOT IN
SubQuery with implicit Aggregation(no Group By clause) cannot be rewritten.
+FAILED: SemanticException [Error 10250]: org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSubquerySemanticException:
Line 7:7 Invalid SubQuery expression ''val_9'': A predicate on EXISTS/NOT EXISTS SubQuery
with implicit Aggregation(no Group By clause) cannot be rewritten.

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/test/results/clientnegative/subquery_in_implicit_gby.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientnegative/subquery_in_implicit_gby.q.out b/ql/src/test/results/clientnegative/subquery_in_implicit_gby.q.out
index a882fbc..3ce8cc1 100644
--- a/ql/src/test/results/clientnegative/subquery_in_implicit_gby.q.out
+++ b/ql/src/test/results/clientnegative/subquery_in_implicit_gby.q.out
@@ -1 +1,38 @@
-FAILED: SemanticException [Error 10250]: org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSubquerySemanticException:
Line 1:68 Invalid SubQuery expression 'p_type': A predicate on EXISTS/NOT EXISTS/IN/NOT IN
SubQuery with implicit Aggregation(no Group By clause) cannot be rewritten.
+PREHOOK: query: create table t(i int, j int)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@t
+POSTHOOK: query: create table t(i int, j int)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@t
+PREHOOK: query: insert into t values(0,1), (0,2)
+PREHOOK: type: QUERY
+PREHOOK: Output: default@t
+POSTHOOK: query: insert into t values(0,1), (0,2)
+POSTHOOK: type: QUERY
+POSTHOOK: Output: default@t
+POSTHOOK: Lineage: t.i EXPRESSION [(values__tmp__table__1)values__tmp__table__1.FieldSchema(name:tmp_values_col1,
type:string, comment:), ]
+POSTHOOK: Lineage: t.j EXPRESSION [(values__tmp__table__1)values__tmp__table__1.FieldSchema(name:tmp_values_col2,
type:string, comment:), ]
+PREHOOK: query: create table tt(i int, j int)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@tt
+POSTHOOK: query: create table tt(i int, j int)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@tt
+PREHOOK: query: insert into tt values(0,3)
+PREHOOK: type: QUERY
+PREHOOK: Output: default@tt
+POSTHOOK: query: insert into tt values(0,3)
+POSTHOOK: type: QUERY
+POSTHOOK: Output: default@tt
+POSTHOOK: Lineage: tt.i EXPRESSION [(values__tmp__table__2)values__tmp__table__2.FieldSchema(name:tmp_values_col1,
type:string, comment:), ]
+POSTHOOK: Lineage: tt.j EXPRESSION [(values__tmp__table__2)values__tmp__table__2.FieldSchema(name:tmp_values_col2,
type:string, comment:), ]
+PREHOOK: query: select * from t where i IN (select count(i) from tt where tt.j = t.j)
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t
+PREHOOK: Input: default@tt
+#### A masked pattern was here ####
+FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/test/results/clientnegative/subquery_notexists_implicit_gby.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientnegative/subquery_notexists_implicit_gby.q.out b/ql/src/test/results/clientnegative/subquery_notexists_implicit_gby.q.out
index bab6138..b650309 100644
--- a/ql/src/test/results/clientnegative/subquery_notexists_implicit_gby.q.out
+++ b/ql/src/test/results/clientnegative/subquery_notexists_implicit_gby.q.out
@@ -1 +1 @@
-FAILED: SemanticException [Error 10250]: org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSubquerySemanticException:
Line 7:7 Invalid SubQuery expression ''val_9'': A predicate on EXISTS/NOT EXISTS/IN/NOT IN
SubQuery with implicit Aggregation(no Group By clause) cannot be rewritten.
+FAILED: SemanticException [Error 10250]: org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSubquerySemanticException:
Line 7:7 Invalid SubQuery expression ''val_9'': A predicate on EXISTS/NOT EXISTS SubQuery
with implicit Aggregation(no Group By clause) cannot be rewritten.

http://git-wip-us.apache.org/repos/asf/hive/blob/2d501879/ql/src/test/results/clientnegative/subquery_notin_implicit_gby.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientnegative/subquery_notin_implicit_gby.q.out b/ql/src/test/results/clientnegative/subquery_notin_implicit_gby.q.out
new file mode 100644
index 0000000..a208951
--- /dev/null
+++ b/ql/src/test/results/clientnegative/subquery_notin_implicit_gby.q.out
@@ -0,0 +1,38 @@
+PREHOOK: query: create table t(i int, j int)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@t
+POSTHOOK: query: create table t(i int, j int)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@t
+PREHOOK: query: insert into t values(0,1), (0,2)
+PREHOOK: type: QUERY
+PREHOOK: Output: default@t
+POSTHOOK: query: insert into t values(0,1), (0,2)
+POSTHOOK: type: QUERY
+POSTHOOK: Output: default@t
+POSTHOOK: Lineage: t.i EXPRESSION [(values__tmp__table__1)values__tmp__table__1.FieldSchema(name:tmp_values_col1,
type:string, comment:), ]
+POSTHOOK: Lineage: t.j EXPRESSION [(values__tmp__table__1)values__tmp__table__1.FieldSchema(name:tmp_values_col2,
type:string, comment:), ]
+PREHOOK: query: create table tt(i int, j int)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@tt
+POSTHOOK: query: create table tt(i int, j int)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@tt
+PREHOOK: query: insert into tt values(0,3)
+PREHOOK: type: QUERY
+PREHOOK: Output: default@tt
+POSTHOOK: query: insert into tt values(0,3)
+POSTHOOK: type: QUERY
+POSTHOOK: Output: default@tt
+POSTHOOK: Lineage: tt.i EXPRESSION [(values__tmp__table__2)values__tmp__table__2.FieldSchema(name:tmp_values_col1,
type:string, comment:), ]
+POSTHOOK: Lineage: tt.j EXPRESSION [(values__tmp__table__2)values__tmp__table__2.FieldSchema(name:tmp_values_col2,
type:string, comment:), ]
+PREHOOK: query: select * from t where i NOT IN (select count(i) from tt where tt.j = t.j)
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t
+PREHOOK: Input: default@tt
+#### A masked pattern was here ####
+FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask


Mime
View raw message