You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by ha...@apache.org on 2017/01/26 07:17:24 UTC
[4/4] hive git commit: HIVE-15721 : Allow IN/NOT IN correlated
subquery with aggregates (Vineet Garg via Ashutosh Chauhan)
HIVE-15721 : Allow IN/NOT IN correlated subquery with aggregates (Vineet Garg via Ashutosh Chauhan)
Signed-off-by: Ashutosh Chauhan <ha...@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 <vg...@hortonworks.com>
Authored: Wed Jan 25 23:16:14 2017 -0800
Committer: Ashutosh Chauhan <ha...@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