Repository: vitessio/vitess
Pull request #8894 (2021-09-28 05:01:04)

Gen4: add `__sq_has_values` for `in`/`not in` subqueries
## Description

This pull request adds the `__sq_has_values` predicates to queries that contain `in`/`not in` subqueries.

## Related Issue(s)

- #7280 

## Checklist
- [x] Tests were added or are not required
- [x] Documentation was added or is not required




Patch:
diff --git a/go/vt/sqlparser/analyzer.go b/go/vt/sqlparser/analyzer.go
index 6114071082..5e479d281d 100644
--- a/go/vt/sqlparser/analyzer.go
+++ b/go/vt/sqlparser/analyzer.go
@@ -326,8 +326,23 @@ func SplitAndExpression(filters []Expr, node Expr) []Expr {
 	return append(filters, node)
 }
 
-// AndExpressions ands together two expression, minimising the expr when possible
-func AndExpressions(exprs ...Expr) Expr {
+// SplitOrExpression breaks up the Expr into OR-separated conditions
+// and appends them to filters. Outer parenthesis are removed. Precedence
+// should be taken into account if expressions are recombined.
+func SplitOrExpression(filters []Expr, node Expr) []Expr {
+	if node == nil {
+		return filters
+	}
+	switch node := node.(type) {
+	case *OrExpr:
+		filters = SplitOrExpression(filters, node.Left)
+		return SplitOrExpression(filters, node.Right)
+	}
+	return append(filters, node)
+}
+
+// joinExpressions join together a list of Expr using the baseType as operator (either AndExpr or OrExpr).
+func joinExpressions(baseType Expr, exprs ...Expr) Expr {
 	switch len(exprs) {
 	case 0:
 		return nil
@@ -336,6 +351,9 @@ func AndExpressions(exprs ...Expr) Expr {
 	default:
 		result := (Expr)(nil)
 		for i, expr := range exprs {
+			if expr == nil {
+				continue
+			}
 			if result == nil {
 				result = expr
 			} else {
@@ -347,12 +365,28 @@ func AndExpressions(exprs ...Expr) Expr {
 					}
 				}
 				if !found {
-					result = &AndExpr{Left: result, Right: expr}
+					switch baseType.(type) {
+					case *AndExpr:
+						result = &AndExpr{Left: result, Right: expr}
+					case *OrExpr:
+						result = &OrExpr{Left: result, Right: expr}
+					}
 				}
 			}
 		}
 		return result
 	}
+
+}
+
+// AndExpressions ands together two or more expressions, minimising the expr when possible
+func AndExpressions(exprs ...Expr) Expr {
+	return joinExpressions(&AndExpr{}, exprs...)
+}
+
+// OrExpressions ors together two or more expressions, minimising the expr when possible
+func OrExpressions(exprs ...Expr) Expr {
+	return joinExpressions(&OrExpr{}, exprs...)
 }
 
 // TableFromStatement returns the qualified table name for the query.
diff --git a/go/vt/sqlparser/ast_rewriting.go b/go/vt/sqlparser/ast_rewriting.go
index 0f44cca453..a35e1eaf9e 100644
--- a/go/vt/sqlparser/ast_rewriting.go
+++ b/go/vt/sqlparser/ast_rewriting.go
@@ -101,6 +101,22 @@ func (r *ReservedVars) ReserveSubQuery() string {
 	}
 }
 
+// ReserveSubQueryWithHasValues returns the next argument name to replace subquery with pullout value.
+func (r *ReservedVars) ReserveSubQueryWithHasValues() (string, string) {
+	for {
+		r.sqNext++
+		joinVar := strconv.AppendInt(subQueryBaseArgName, r.sqNext, 10)
+		hasValuesJoinVar := strconv.AppendInt(HasValueSubQueryBaseName, r.sqNext, 10)
+		_, joinVarOK := r.reserved[string(joinVar)]
+		_, hasValuesJoinVarOK := r.reserved[string(hasValuesJoinVar)]
+		if !joinVarOK && !hasValuesJoinVarOK {
+			r.reserved[string(joinVar)] = struct{}{}
+			r.reserved[string(hasValuesJoinVar)] = struct{}{}
+			return string(joinVar), string(hasValuesJoinVar)
+		}
+	}
+}
+
 // ReserveHasValuesSubQuery returns the next argument name to replace subquery with has value.
 func (r *ReservedVars) ReserveHasValuesSubQuery() string {
 	for {
diff --git a/go/vt/vtgate/planbuilder/abstract/operator.go b/go/vt/vtgate/planbuilder/abstract/operator.go
index 1727ede5d6..896f6b8efd 100644
--- a/go/vt/vtgate/planbuilder/abstract/operator.go
+++ b/go/vt/vtgate/planbuilder/abstract/operator.go
@@ -34,7 +34,6 @@ type (
 	//  *  SubQuery - Represents a query that encapsulates one or more sub-queries (SubQueryInner).
 	//  *  Vindex - Represents a query that selects from vindex tables.
 	//  *  Concatenate - Represents concatenation of the outputs of all the input sources
-	//  *  Distinct - Represents elimination of duplicates from the output of the input source
 	Operator interface {
 		// TableID returns a TableSet of the tables contained within
 		TableID() semantics.TableSet
@@ -211,10 +210,13 @@ func createOperatorFromSelect(sel *sqlparser.Select, semTable *semantics.SemTabl
 				return nil, err
 			}
 			resultantOp.Inner = append(resultantOp.Inner, &SubQueryInner{
-				SelectStatement: subquerySelectStatement,
-				Inner:           opInner,
-				Type:            sq.OpCode,
-				ArgName:         sq.ArgName,
+				SelectStatement:  subquerySelectStatement,
+				Inner:            opInner,
+				Type:             sq.OpCode,
+				ArgName:          sq.ArgName,
+				HasValues:        sq.HasValues,
+				ExprsNeedReplace: sq.ExprsNeedReplace,
+				ReplaceBy:        sq.ReplaceBy,
 			})
 		}
 	}
diff --git a/go/vt/vtgate/planbuilder/abstract/subquery.go b/go/vt/vtgate/planbuilder/abstract/subquery.go
index 4e1b3e31fd..7ac4776f16 100644
--- a/go/vt/vtgate/planbuilder/abstract/subquery.go
+++ b/go/vt/vtgate/planbuilder/abstract/subquery.go
@@ -34,10 +34,35 @@ var _ Operator = (*SubQuery)(nil)
 
 // SubQueryInner stores the subquery information for a select statement
 type SubQueryInner struct {
-	Inner           Operator
-	Type            engine.PulloutOpcode
+	// Inner is the Operator inside the parenthesis of the subquery.
+	// i.e: select (select 1 union select 1), the Inner here would be
+	// of type Concatenate since we have a Union.
+	Inner Operator
+
+	// Type represents the type of the subquery (value, in, not in, exists)
+	Type engine.PulloutOpcode
+
+	// SelectStatement is the inner's select
 	SelectStatement *sqlparser.Select
-	ArgName         string
+
+	// ArgName is the substitution argument string for the subquery.
+	// Subquery argument name looks like: `__sq1`, with `1` being an
+	// unique identifier. This is used when we wish to replace the
+	// subquery by an argument for PullOut subqueries.
+	ArgName string
+
+	// HasValues is a string of form `__sq_has_values1` with `1` being
+	// a unique identifier that matches the one used in ArgName.
+	// We use `__sq_has_values` for in and not in subqueries.
+	HasValues string
+
+	// ExprsNeedReplace is a slice of all the expressions that were
+	// introduced by the rewrite of the subquery and that potentially
+	// need to be re-replace if we can merge the subquery into a route.
+	// An expression that contains at least all of ExprsNeedReplace will
+	// be replaced by the expression in ReplaceBy.
+	ExprsNeedReplace []sqlparser.Expr
+	ReplaceBy        sqlparser.Expr
 }
 
 // TableID implements the Operator interface
diff --git a/go/vt/vtgate/planbuilder/gen4_planner.go b/go/vt/vtgate/planbuilder/gen4_planner.go
index 0cbd64fb57..9913b15d82 100644
--- a/go/vt/vtgate/planbuilder/gen4_planner.go
+++ b/go/vt/vtgate/planbuilder/gen4_planner.go
@@ -178,10 +178,11 @@ func newBuildSelectPlan(selStmt sqlparser.SelectStatement, reservedVars *sqlpars
 
 func newPlanningContext(reservedVars *sqlparser.ReservedVars, semTable *semantics.SemTable, vschema ContextVSchema) *planningContext {
 	ctx := &planningContext{
-		reservedVars: reservedVars,
-		semTable:     semTable,
-		vschema:      vschema,
-		sqToReplace:  map[string]*sqlparser.Select{},
+		reservedVars:          reservedVars,
+		semTable:              semTable,
+		vschema:               vschema,
+		argToReplaceBySelect:  map[string]*sqlparser.Select{},
+		exprToReplaceBySqExpr: map[sqlparser.Expr]sqlparser.Expr{},
 	}
 	return ctx
 }
@@ -216,7 +217,7 @@ func planHorizon(ctx *planningContext, plan logicalPlan, in sqlparser.SelectStat
 			sel: node,
 		}
 
-		replaceSubQuery(ctx.sqToReplace, node)
+		replaceSubQuery(ctx.exprToReplaceBySqExpr, node)
 		var err error
 		plan, err = hp.planHorizon(ctx, plan)
 		if err != nil {
diff --git a/go/vt/vtgate/planbuilder/querytree_transformers.go b/go/vt/vtgate/planbuilder/querytree_transformers.go
index c881a7d1b9..66222519ce 100644
--- a/go/vt/vtgate/planbuilder/querytree_transformers.go
+++ b/go/vt/vtgate/planbuilder/querytree_transformers.go
@@ -98,7 +98,7 @@ func transformSubqueryTree(ctx *planningContext, n *subqueryTree) (logicalPlan,
 		return nil, err
 	}
 
-	plan := newPulloutSubquery(n.opcode, n.argName, "", innerPlan)
+	plan := newPulloutSubquery(n.opcode, n.argName, n.hasValues, innerPlan)
 	outerPlan, err := transformToLogicalPlan(ctx, n.outer)
 	if err != nil {
 		return nil, err
@@ -324,7 +324,7 @@ func transformRoutePlan(ctx *planningContext, n *routeTree) (*route, error) {
 		Comments:    ctx.semTable.Comments,
 	}
 
-	replaceSubQuery(ctx.sqToReplace, sel)
+	replaceSubQuery(ctx.exprToReplaceBySqExpr, sel)
 
 	// TODO clean up when gen4 is the only planner
 	var condition sqlparser.Expr
@@ -439,39 +439,56 @@ func relToTableExpr(t relation) (sqlparser.TableExpr, error) {
 }
 
 type subQReplacer struct {
-	sqToReplace map[string]*sqlparser.Select
-	err         error
-	replaced    bool
+	exprToReplaceBySqExpr map[sqlparser.Expr]sqlparser.Expr
+	replaced              bool
 }
 
 func (sqr *subQReplacer) replacer(cursor *sqlparser.Cursor) bool {
-	argName := argumentName(cursor.Node())
-	if argName == "" {
+	var exprs []sqlparser.Expr
+	switch node := cursor.Node().(type) {
+	case *sqlparser.AndExpr:
+		exprs = sqlparser.SplitAndExpression(nil, node)
+	case *sqlparser.OrExpr:
+		exprs = sqlparser.SplitOrExpression(nil, node)
+	case sqlparser.Argument:
+		exprs = append(exprs, node)
+	case sqlparser.ListArg:
+		exprs = append(exprs, node)
+	case *sqlparser.ExistsExpr:
+		exprs = append(exprs, node)
+	default:
 		return true
 	}
 
-	var node sqlparser.SQLNode
-	subqSelect, exists := sqr.sqToReplace[argName]
-	if !exists {
-		sqr.err = vterrors.Errorf(vtrpcpb.Code_INTERNAL, "[BUG] unable to find subquery with argument: %s", argName)
-		return false
+	var replaceBy sqlparser.Expr
+	var remainder sqlparser.Expr
+	for _, expr := range exprs {
+		found := false
+		for sqExprToReplace, replaceByExpr := range sqr.exprToReplaceBySqExpr {
+			if sqlparser.EqualsExpr(expr, sqExprToReplace) {
+				allReplaceByExprs := sqlparser.SplitAndExpression(nil, replaceBy)
+				allReplaceByExprs = append(allReplaceByExprs, replaceByExpr)
+				replaceBy = sqlparser.AndExpressions(allReplaceByExprs...)
+				found = true
+				break
+			}
+		}
+		if !found {
+			remainder = sqlparser.AndExpressions(remainder, expr)
+		}
 	}
-	sq := &sqlparser.Subquery{Select: subqSelect}
-	node = sq
-
-	// if the subquery is in an EXISTS, e.g. "__sq_has_values1"
-	// then we encapsulate the subquery in an exists expression.
-	if strings.HasPrefix(argName, string(sqlparser.HasValueSubQueryBaseName)) {
-		node = &sqlparser.ExistsExpr{Subquery: sq}
+	if replaceBy == nil {
+		return true
 	}
-	cursor.Replace(node)
+	newNode := sqlparser.AndExpressions(remainder, replaceBy)
+	cursor.Replace(newNode)
 	sqr.replaced = true
 	return false
 }
 
-func replaceSubQuery(sqToReplace map[string]*sqlparser.Select, sel *sqlparser.Select) {
-	if len(sqToReplace) > 0 {
-		sqr := &subQReplacer{sqToReplace: sqToReplace}
+func replaceSubQuery(exprToReplaceBySqExpr map[sqlparser.Expr]sqlparser.Expr, sel *sqlparser.Select) {
+	if len(exprToReplaceBySqExpr) > 0 {
+		sqr := &subQReplacer{exprToReplaceBySqExpr: exprToReplaceBySqExpr}
 		sqlparser.Rewrite(sel, sqr.replacer, nil)
 		for sqr.replaced {
 			// to handle subqueries inside subqueries, we need to do this again and again until no replacements are left
diff --git a/go/vt/vtgate/planbuilder/rewrite.go b/go/vt/vtgate/planbuilder/rewrite.go
index bf787d8575..a07187fcaa 100644
--- a/go/vt/vtgate/planbuilder/rewrite.go
+++ b/go/vt/vtgate/planbuilder/rewrite.go
@@ -41,6 +41,8 @@ func (r *rewriter) rewriteDown(cursor *sqlparser.Cursor) bool {
 	switch node := cursor.Node().(type) {
 	case *sqlparser.Select:
 		rewriteHavingClause(node)
+	case *sqlparser.ComparisonExpr:
+		rewriteInSubquery(cursor, r, node)
 	case *sqlparser.ExistsExpr:
 		return r.rewriteExistsSubquery(cursor, node)
 	case *sqlparser.Subquery:
@@ -92,22 +94,76 @@ func (r *rewriter) rewriteUp(cursor *sqlparser.Cursor) bool {
 	return true
 }
 
-func rewriteSubquery(cursor *sqlparser.Cursor, r *rewriter, node *sqlparser.Subquery) {
-	semTableSQ, found := r.semTable.SubqueryRef[node]
+func rewriteInSubquery(cursor *sqlparser.Cursor, r *rewriter, node *sqlparser.ComparisonExpr) {
+	if node.Operator != sqlparser.InOp && node.Operator != sqlparser.NotInOp {
+		return
+	}
+	subq, exp := filterSubqueryAndCompareExpr(node)
+	if subq == nil || exp == nil {
+		return
+	}
+
+	semTableSQ, found := r.semTable.SubqueryRef[subq]
 	if !found {
 		// should never happen
 		return
 	}
 
-	argName := r.reservedVars.ReserveSubQuery()
+	argName, hasValuesArg := r.reservedVars.ReserveSubQueryWithHasValues()
 	semTableSQ.ArgName = argName
+	semTableSQ.HasValues = hasValuesArg
+	semTableSQ.ReplaceBy = node
+	newSubQExpr := &sqlparser.ComparisonExpr{
+		Operator: node.Operator,
+		Left:     exp,
+		Right:    sqlparser.NewListArg(argName),
+	}
+
+	hasValuesExpr := &sqlparser.ComparisonExpr{
+		Operator: sqlparser.EqualOp,
+		Left:     sqlparser.NewArgument(hasValuesArg),
+	}
+	switch node.Operator {
+	case sqlparser.InOp:
+		hasValuesExpr.Right = sqlparser.NewIntLiteral("1")
+		cursor.Replace(sqlparser.AndExpressions(hasValuesExpr, newSubQExpr))
+	case sqlparser.NotInOp:
+		hasValuesExpr.Right = sqlparser.NewIntLiteral("0")
+		cursor.Replace(sqlparser.OrExpressions(hasValuesExpr, newSubQExpr))
+	}
+	semTableSQ.ExprsNeedReplace = append(semTableSQ.ExprsNeedReplace, hasValuesExpr, newSubQExpr)
 
-	switch semTableSQ.OpCode {
-	case engine.PulloutIn, engine.PulloutNotIn:
-		cursor.Replace(sqlparser.NewListArg(argName))
-	default:
-		cursor.Replace(sqlparser.NewArgument(argName))
+	// setting the dependencies of the new has_value expression to the subquery's dependencies so
+	// later on, we know has_values depends on the same tables as the subquery
+	r.semTable.Recursive[hasValuesExpr] = r.semTable.RecursiveDeps(newSubQExpr)
+	r.semTable.Direct[hasValuesExpr] = r.semTable.DirectDeps(newSubQExpr)
+}
+
+func filterSubqueryAndCompareExpr(node *sqlparser.ComparisonExpr) (*sqlparser.Subquery, sqlparser.Expr) {
+	var subq *sqlparser.Subquery
+	var exp sqlparser.Expr
+	if lSubq, lIsSubq := node.Left.(*sqlparser.Subquery); lIsSubq {
+		subq = lSubq
+		exp = node.Right
+	} else if rSubq, rIsSubq := node.Right.(*sqlparser.Subquery); rIsSubq {
+		subq = rSubq
+		exp = node.Left
+	}
+	return subq, exp
+}
+
+func rewriteSubquery(cursor *sqlparser.Cursor, r *rewriter, node *sqlparser.Subquery) {
+	semTableSQ, found := r.semTable.SubqueryRef[node]
+	if !found || semTableSQ.OpCode != engine.PulloutValue {
+		return
 	}
+
+	argName := r.reservedVars.ReserveSubQuery()
+	arg := sqlparser.NewArgument(argName)
+	semTableSQ.ArgName = argName
+	semTableSQ.ExprsNeedReplace = append(semTableSQ.ExprsNeedReplace, arg)
+	semTableSQ.ReplaceBy = node
+	cursor.Replace(arg)
 }
 
 func (r *rewriter) rewriteExistsSubquery(cursor *sqlparser.Cursor, node *sqlparser.ExistsExpr) bool {
@@ -118,9 +174,11 @@ func (r *rewriter) rewriteExistsSubquery(cursor *sqlparser.Cursor, node *sqlpars
 	}
 
 	argName := r.reservedVars.ReserveHasValuesSubQuery()
+	arg := sqlparser.NewArgument(argName)
 	semTableSQ.ArgName = argName
-
-	cursor.Replace(sqlparser.NewArgument(argName))
+	semTableSQ.ExprsNeedReplace = append(semTableSQ.ExprsNeedReplace, arg)
+	semTableSQ.ReplaceBy = node
+	cursor.Replace(arg)
 	return false
 }
 
diff --git a/go/vt/vtgate/planbuilder/route_planning.go b/go/vt/vtgate/planbuilder/route_planning.go
index b9770a625a..fd162d555c 100644
--- a/go/vt/vtgate/planbuilder/route_planning.go
+++ b/go/vt/vtgate/planbuilder/route_planning.go
@@ -38,12 +38,14 @@ type planningContext struct {
 	reservedVars *sqlparser.ReservedVars
 	semTable     *semantics.SemTable
 	vschema      ContextVSchema
-	// these helps in replacing the argNames with the subquery
-	sqToReplace map[string]*sqlparser.Select
+	// these help in replacing the argNames with the subquery
+	argToReplaceBySelect map[string]*sqlparser.Select
+	// these help in replacing the argument's expressions by the original expression
+	exprToReplaceBySqExpr map[sqlparser.Expr]sqlparser.Expr
 }
 
 func (c planningContext) isSubQueryToReplace(name string) bool {
-	_, found := c.sqToReplace[name]
+	_, found := c.argToReplaceBySelect[name]
 	return found
 }
 
@@ -149,10 +151,11 @@ func optimizeSubQuery(ctx *planningContext, op *abstract.SubQuery) (queryTree, e
 				return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: cross-shard correlated subquery")
 			}
 			unmerged = append(unmerged, &subqueryTree{
-				subquery: inner.SelectStatement,
-				inner:    treeInner,
-				opcode:   inner.Type,
-				argName:  inner.ArgName,
+				subquery:  inner.SelectStatement,
+				inner:     treeInner,
+				opcode:    inner.Type,
+				argName:   inner.ArgName,
+				hasValues: inner.HasValues,
 			})
 		} else {
 			outerTree = merged
@@ -266,7 +269,10 @@ func rewriteSubqueryDependenciesForJoin(ctx *planningContext, otherTree queryTre
 }
 
 func mergeSubQuery(ctx *planningContext, outer *routeTree, inner *routeTree, subq *abstract.SubQueryInner) (*routeTree, error) {
-	ctx.sqToReplace[subq.ArgName] = subq.SelectStatement
+	ctx.argToReplaceBySelect[subq.ArgName] = subq.SelectStatement
+	for _, expr := range subq.ExprsNeedReplace {
+		ctx.exprToReplaceBySqExpr[expr] = subq.ReplaceBy
+	}
 	// go over the subquery and add its tables to the one's solved by the route it is merged with
 	// this is needed to so that later when we try to push projections, we get the correct
 	// solved tableID from the route, since it also includes the tables from the subquery after merging
diff --git a/go/vt/vtgate/planbuilder/routetree.go b/go/vt/vtgate/planbuilder/routetree.go
index 3004f8b9ef..29bcb4d1fa 100644
--- a/go/vt/vtgate/planbuilder/routetree.go
+++ b/go/vt/vtgate/planbuilder/routetree.go
@@ -409,7 +409,7 @@ func (rp *routeTree) planIsExpr(ctx *planningContext, node *sqlparser.IsExpr) (b
 }
 
 // makePlanValue transforms the given sqlparser.Expr into a sqltypes.PlanValue.
-// If the given sqlparser.Expr is an argument and can be found in the rp.sqToReplace then the
+// If the given sqlparser.Expr is an argument and can be found in the rp.argToReplaceBySelect then the
 // method will stops and return nil values.
 // Otherwise, the method will try to apply makePlanValue for any equality the sqlparser.Expr n has.
 // The first PlanValue that is successfully produced will be returned.
diff --git a/go/vt/vtgate/planbuilder/subquerytree.go b/go/vt/vtgate/planbuilder/subquerytree.go
index 328c336c32..2e2cbe4365 100644
--- a/go/vt/vtgate/planbuilder/subquerytree.go
+++ b/go/vt/vtgate/planbuilder/subquerytree.go
@@ -25,11 +25,12 @@ import (
 )
 
 type subqueryTree struct {
-	subquery *sqlparser.Select
-	outer    queryTree
-	inner    queryTree
-	opcode   engine.PulloutOpcode
-	argName  string
+	subquery  *sqlparser.Select
+	outer     queryTree
+	inner     queryTree
+	opcode    engine.PulloutOpcode
+	argName   string
+	hasValues string
 }
 
 var _ queryTree = (*subqueryTree)(nil)
@@ -44,11 +45,12 @@ func (s *subqueryTree) cost() int {
 
 func (s *subqueryTree) clone() queryTree {
 	result := &subqueryTree{
-		subquery: s.subquery,
-		outer:    s.outer.clone(),
-		inner:    s.inner.clone(),
-		opcode:   s.opcode,
-		argName:  s.argName,
+		subquery:  s.subquery,
+		outer:     s.outer.clone(),
+		inner:     s.inner.clone(),
+		opcode:    s.opcode,
+		argName:   s.argName,
+		hasValues: s.hasValues,
 	}
 	return result
 }
diff --git a/go/vt/vtgate/semantics/semantic_state.go b/go/vt/vtgate/semantics/semantic_state.go
index 01a9f0734d..3f48c30a78 100644
--- a/go/vt/vtgate/semantics/semantic_state.go
+++ b/go/vt/vtgate/semantics/semantic_state.go
@@ -104,9 +104,15 @@ type (
 	}
 
 	subquery struct {
-		ArgName  string
-		SubQuery *sqlparser.Subquery
-		OpCode   engine.PulloutOpcode
+		ArgName   string
+		HasValues string
+		SubQuery  *sqlparser.Subquery
+		OpCode    engine.PulloutOpcode
+
+		// ExprsNeedReplace list all the expressions that, if the subquery is later rewritten, need to
+		// be removed and replaced by ReplaceBy.
+		ExprsNeedReplace []sqlparser.Expr
+		ReplaceBy        sqlparser.Expr
 	}
 
 	// SchemaInformation is used tp provide table information from Vschema.


Test patch:
diff --git a/go/test/endtoend/vtgate/misc_test.go b/go/test/endtoend/vtgate/misc_test.go
index 386a0b1005..d4cfe57dbe 100644
--- a/go/test/endtoend/vtgate/misc_test.go
+++ b/go/test/endtoend/vtgate/misc_test.go
@@ -753,6 +753,19 @@ func TestSimpleOrderBy(t *testing.T) {
 	assertMatches(t, conn, `SELECT id2 FROM t1 ORDER BY id2 ASC`, `[[INT64(5)] [INT64(6)] [INT64(7)] [INT64(8)] [INT64(9)] [INT64(10)]]`)
 }
 
+func TestSubqueriesHasValues(t *testing.T) {
+	defer cluster.PanicHandler(t)
+	ctx := context.Background()
+	conn, err := mysql.Connect(ctx, &vtParams)
+	require.NoError(t, err)
+	defer conn.Close()
+
+	defer exec(t, conn, `delete from t1`)
+	exec(t, conn, "insert into t1(id1, id2) values (0,1),(1,2),(2,3),(3,4),(4,5),(5,6)")
+	assertMatches(t, conn, `SELECT id2 FROM t1 WHERE id1 IN (SELECT id1 FROM t1 WHERE id1 > 10)`, `[]`)
+	assertMatches(t, conn, `SELECT id2 FROM t1 WHERE id1 NOT IN (SELECT id1 FROM t1 WHERE id1 > 10) ORDER BY id2`, `[[INT64(1)] [INT64(2)] [INT64(3)] [INT64(4)] [INT64(5)] [INT64(6)]]`)
+}
+
 func TestSelectEqualUniqueOuterJoinRightPredicate(t *testing.T) {
 	defer cluster.PanicHandler(t)
 	ctx := context.Background()
diff --git a/go/vt/vtgate/planbuilder/rewrite_test.go b/go/vt/vtgate/planbuilder/rewrite_test.go
index 40722b5b8d..cda6bf66f0 100644
--- a/go/vt/vtgate/planbuilder/rewrite_test.go
+++ b/go/vt/vtgate/planbuilder/rewrite_test.go
@@ -41,10 +41,10 @@ func TestSubqueryRewrite(t *testing.T) {
 		output: "select 1 from t1 where :__sq_has_values1",
 	}, {
 		input:  "select id from t1 where id in (select 1)",
-		output: "select id from t1 where id in ::__sq1",
+		output: "select id from t1 where :__sq_has_values1 = 1 and id in ::__sq1",
 	}, {
 		input:  "select id from t1 where id not in (select 1)",
-		output: "select id from t1 where id not in ::__sq1",
+		output: "select id from t1 where :__sq_has_values1 = 0 or id not in ::__sq1",
 	}, {
 		input:  "select id from t1 where id = (select 1)",
 		output: "select id from t1 where id = :__sq1",
@@ -65,7 +65,7 @@ func TestSubqueryRewrite(t *testing.T) {
 		output: "select id from t1 where not :__sq_has_values1 and :__sq_has_values2",
 	}, {
 		input:  "select (select 1), (select 2) from t1 join t2 on t1.id = (select 1) where t1.id in (select 1)",
-		output: "select :__sq2, :__sq3 from t1 join t2 on t1.id = :__sq1 where t1.id in ::__sq4",
+		output: "select :__sq2, :__sq3 from t1 join t2 on t1.id = :__sq1 where :__sq_has_values4 = 1 and t1.id in ::__sq4",
 	}}
 	for _, tcase := range tcases {
 		t.Run(tcase.input, func(t *testing.T) {
@@ -123,10 +123,9 @@ func TestHavingRewrite(t *testing.T) {
 		output: "select count(*) as k from t1 where (x = 1 or y = 2) and a = 1 having count(*) = 1",
 	}, {
 		input:  "select 1 from t1 where x in (select 1 from t2 having a = 1)",
-		output: "select 1 from t1 where x in ::__sq1",
+		output: "select 1 from t1 where :__sq_has_values1 = 1 and x in ::__sq1",
 		sqs:    map[string]string{"__sq1": "select 1 from t2 where a = 1"},
-	}, {
-		input:  "select 1 from t1 group by a having a = 1 and count(*) > 1",
+	}, {input: "select 1 from t1 group by a having a = 1 and count(*) > 1",
 		output: "select 1 from t1 where a = 1 group by a having count(*) > 1",
 	}}
 	for _, tcase := range tcases {
diff --git a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
index d9627baa8b..a6a0e7f3b2 100644
--- a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
@@ -1308,7 +1308,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select u.m from `user` as u where 1 != 1",
-        "Query": "select u.m from `user` as u where u.id in (select m2 from `user` where `user`.id = u.id and `user`.col = :user_extra_col) and u.id in ::__vals",
+        "Query": "select u.m from `user` as u where u.id in ::__vals and u.id in (select m2 from `user` where `user`.id = u.id and `user`.col = :user_extra_col)",
         "Table": "`user`",
         "Values": [
           [
@@ -1399,7 +1399,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select u.m from `user` as u where 1 != 1",
-        "Query": "select u.m from `user` as u where u.id in (select m2 from `user` where `user`.id = u.id) and u.id in ::__vals",
+        "Query": "select u.m from `user` as u where u.id in ::__vals and u.id in (select m2 from `user` where `user`.id = u.id)",
         "Table": "`user`",
         "Values": [
           [
@@ -1481,7 +1481,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select u.m from `user` as u where 1 != 1",
-        "Query": "select u.m from `user` as u where u.id in (select m2 from `user` where `user`.id = 5) and u.id = 5",
+        "Query": "select u.m from `user` as u where u.id = 5 and u.id in (select m2 from `user` where `user`.id = 5)",
         "Table": "`user`",
         "Values": [
           5
@@ -1569,7 +1569,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select u.m from `user` as u where 1 != 1",
-        "Query": "select u.m from `user` as u where u.id in (select m2 from `user` where `user`.id = u.id and `user`.col = :user_extra_col and `user`.id in (select m3 from user_extra where user_extra.user_id = `user`.id)) and u.id in ::__vals",
+        "Query": "select u.m from `user` as u where u.id in ::__vals and u.id in (select m2 from `user` where `user`.id = u.id and `user`.col = :user_extra_col and `user`.id in (select m3 from user_extra where user_extra.user_id = `user`.id))",
         "Table": "`user`",
         "Values": [
           [
@@ -1760,7 +1760,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select id from `user` where 1 != 1",
-        "Query": "select id from `user` where id in ::__vals",
+        "Query": "select id from `user` where :__sq_has_values1 = 1 and id in ::__vals",
         "Table": "`user`",
         "Values": [
           "::__sq1"
@@ -1831,7 +1831,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select id from `user` where 1 != 1",
-        "Query": "select id from `user` where id not in ::__sq1",
+        "Query": "select id from `user` where :__sq_has_values1 = 0 or id not in ::__sq1",
         "Table": "`user`"
       }
     ]
@@ -1999,7 +1999,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select id2 from `user` where 1 != 1",
-            "Query": "select id2 from `user` where id2 in ::__sq2",
+            "Query": "select id2 from `user` where :__sq_has_values2 = 1 and id2 in ::__sq2",
             "Table": "`user`"
           }
         ]
@@ -2414,7 +2414,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select id from `user` where 1 != 1",
-            "Query": "select id from `user` where not id in ::__sq1 and id in ::__vals",
+            "Query": "select id from `user` where not (:__sq_has_values1 = 1 and id in ::__sq1) and :__sq_has_values2 = 1 and id in ::__vals",
             "Table": "`user`",
             "Values": [
               "::__sq2"
@@ -2574,7 +2574,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select id from `user` where 1 != 1",
-        "Query": "select id from `user` where id in ::__vals",
+        "Query": "select id from `user` where :__sq_has_values1 = 1 and id in ::__vals",
         "Table": "`user`",
         "Values": [
           "::__sq1"
@@ -2828,7 +2828,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select id from `user` where 1 != 1",
-        "Query": "select id from `user` where id = 5 and not id in (select user_extra.col from user_extra where user_extra.user_id = 5) and id in ::__sq2",
+        "Query": "select id from `user` where id = 5 and not id in (select user_extra.col from user_extra where user_extra.user_id = 5) and :__sq_has_values2 = 1 and id in ::__sq2",
         "Table": "`user`",
         "Values": [
           5
@@ -2911,7 +2911,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select id from `user` where 1 != 1",
-        "Query": "select id from `user` where id = 5 and not id in ::__sq1 and id in (select user_extra.col from user_extra where user_extra.user_id = 5)",
+        "Query": "select id from `user` where id = 5 and not (:__sq_has_values1 = 1 and id in ::__sq1) and id in (select user_extra.col from user_extra where user_extra.user_id = 5)",
         "Table": "`user`",
         "Values": [
           5
diff --git a/go/vt/vtgate/planbuilder/testdata/from_cases.txt b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
index f65a8a41fc..467f9b7582 100644
--- a/go/vt/vtgate/planbuilder/testdata/from_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
@@ -2955,7 +2955,7 @@ Gen4 plan same as above
           "Sharded": false
         },
         "FieldQuery": "select unsharded_a.col from unsharded_a, unsharded_b where 1 != 1",
-        "Query": "select unsharded_a.col from unsharded_a, unsharded_b where unsharded_a.col in ::__sq1",
+        "Query": "select unsharded_a.col from unsharded_a, unsharded_b where :__sq_has_values1 = 1 and unsharded_a.col in ::__sq1",
         "Table": "unsharded_a, unsharded_b"
       }
     ]
@@ -3058,7 +3058,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select 1 from `user` where 1 != 1",
-            "Query": "select 1 from `user` where `user`.col in ::__sq1",
+            "Query": "select 1 from `user` where :__sq_has_values1 = 1 and `user`.col in ::__sq1",
             "Table": "`user`"
           }
         ]
@@ -3164,7 +3164,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select 1 from `user` where 1 != 1",
-            "Query": "select 1 from `user` where `user`.col in ::__sq1",
+            "Query": "select 1 from `user` where :__sq_has_values1 = 1 and `user`.col in ::__sq1",
             "Table": "`user`"
           }
         ]
@@ -3278,7 +3278,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select 1 from `user` where 1 != 1",
-            "Query": "select 1 from `user` where `user`.col in ::__sq1",
+            "Query": "select 1 from `user` where :__sq_has_values1 = 1 and `user`.col in ::__sq1",
             "Table": "`user`"
           },
           {
diff --git a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
index b72fcce9c6..150d3bd3b1 100644
--- a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
@@ -243,7 +243,7 @@ Gen4 error: Column 'col1' in field list is ambiguous
           "Sharded": true
         },
         "FieldQuery": "select id from `user` where 1 != 1",
-        "Query": "select id from `user` where id in ::__vals",
+        "Query": "select id from `user` where :__sq_has_values1 = 1 and id in ::__vals",
         "Table": "`user`",
         "Values": [
           "::__sq1"
@@ -604,7 +604,7 @@ Gen4 plan same as above
         },
         "FieldQuery": "select col, weight_string(col) from `user` where 1 != 1",
         "OrderBy": "(0|1) ASC",
-        "Query": "select col, weight_string(col) from `user` where col in ::__sq1 order by col asc",
+        "Query": "select col, weight_string(col) from `user` where :__sq_has_values1 = 1 and col in ::__sq1 order by col asc",
         "ResultColumns": 1,
         "Table": "`user`"
       }
@@ -953,7 +953,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col from `user` where 1 != 1",
-        "Query": "select col from `user` where col in ::__sq1 order by null",
+        "Query": "select col from `user` where :__sq_has_values1 = 1 and col in ::__sq1 order by null",
         "Table": "`user`"
       }
     ]
@@ -1133,7 +1133,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col from `user` where 1 != 1",
-        "Query": "select col from `user` where col in ::__sq1 order by rand()",
+        "Query": "select col from `user` where :__sq_has_values1 = 1 and col in ::__sq1 order by rand()",
         "Table": "`user`"
       }
     ]
@@ -1689,7 +1689,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select col from `user` where 1 != 1",
-            "Query": "select col from `user` where col in ::__sq1 limit :__upper_limit",
+            "Query": "select col from `user` where :__sq_has_values1 = 1 and col in ::__sq1 limit :__upper_limit",
             "Table": "`user`"
           }
         ]
diff --git a/go/vt/vtgate/planbuilder/testdata/wireup_cases.txt b/go/vt/vtgate/planbuilder/testdata/wireup_cases.txt
index 487b1d9758..708c1cb4ad 100644
--- a/go/vt/vtgate/planbuilder/testdata/wireup_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/wireup_cases.txt
@@ -1185,7 +1185,7 @@
           "Sharded": true
         },
         "FieldQuery": "select 1 from `user` where 1 != 1",
-        "Query": "select 1 from `user` where id in ::__vals",
+        "Query": "select 1 from `user` where :__sq_has_values1 = 1 and id in ::__vals",
         "Table": "`user`",
         "Values": [
           "::__sq1"