Repository: vitessio/vitess
Pull request #8760 (2021-09-02 12:31:52)

Support for remaining subquery queries in Gen4
<!-- if this PR is Work in Progress please create it as a Draft Pull Request -->

## Description
This PR adds support for the remaining queries with subqueries in Gen4

## Related Issue(s)
#7280 

## Checklist
- [ ] Should this PR be backported?
- [x] Tests were added or are not required
- [x] Documentation was added or is not required

## Deployment Notes
<!-- Notes regarding deployment of the contained body of work. These should note any db migrations, etc. -->


Patch:
diff --git a/go/vt/vtgate/planbuilder/rewrite.go b/go/vt/vtgate/planbuilder/rewrite.go
index e784568559..d70f9d98b8 100644
--- a/go/vt/vtgate/planbuilder/rewrite.go
+++ b/go/vt/vtgate/planbuilder/rewrite.go
@@ -28,6 +28,7 @@ type rewriter struct {
 	err          error
 	semTable     *semantics.SemTable
 	reservedVars *sqlparser.ReservedVars
+	isInSubquery int
 }
 
 func starRewrite(statement sqlparser.SelectStatement, semTable *semantics.SemTable) error {
@@ -112,18 +113,61 @@ func queryRewrite(ctx planningContext, statement sqlparser.SelectStatement) erro
 		semTable:     ctx.semTable,
 		reservedVars: ctx.reservedVars,
 	}
-	sqlparser.Rewrite(statement, r.rewrite, nil)
+	sqlparser.Rewrite(statement, r.rewriteDown, r.rewriteUp)
 	return nil
 }
 
-func (r *rewriter) rewrite(cursor *sqlparser.Cursor) bool {
+func (r *rewriter) rewriteDown(cursor *sqlparser.Cursor) bool {
 	switch node := cursor.Node().(type) {
 	case *sqlparser.Select:
 		rewriteHavingClause(node)
 	case *sqlparser.ExistsExpr:
 		return r.rewriteExistsSubquery(cursor, node)
 	case *sqlparser.Subquery:
+		r.isInSubquery++
 		rewriteSubquery(cursor, r, node)
+	case *sqlparser.AliasedTableExpr:
+		// rewrite names of the routed tables for the subquery
+		// We only need to do this for non-derived tables and if they are in a subquery
+		if _, isDerived := node.Expr.(*sqlparser.DerivedTable); isDerived || r.isInSubquery == 0 {
+			break
+		}
+		// find the tableSet and tableInfo that this table points to
+		// tableInfo should contain the information for the original table that the routed table points to
+		tableSet := r.semTable.TableSetFor(node)
+		tableInfo, err := r.semTable.TableInfoFor(tableSet)
+		if err != nil {
+			// Fail-safe code, should never happen
+			break
+		}
+		// vindexTable is the original table
+		vindexTable := tableInfo.GetVindexTable()
+		if vindexTable == nil {
+			break
+		}
+		tableName := node.Expr.(sqlparser.TableName)
+		// if the table name matches what the original is, then we do not need to rewrite
+		if sqlparser.EqualsTableIdent(vindexTable.Name, tableName.Name) {
+			break
+		}
+		// if there is no as clause, then move the routed table to the as clause.
+		// i.e
+		// routed as x -> original as x
+		// routed -> original as routed
+		if node.As.IsEmpty() {
+			node.As = tableName.Name
+		}
+		// replace the table name with the original table
+		tableName.Name = vindexTable.Name
+		node.Expr = tableName
+	}
+	return true
+}
+
+func (r *rewriter) rewriteUp(cursor *sqlparser.Cursor) bool {
+	switch cursor.Node().(type) {
+	case *sqlparser.Subquery:
+		r.isInSubquery--
 	}
 	return true
 }
diff --git a/go/vt/vtgate/planbuilder/route_planning.go b/go/vt/vtgate/planbuilder/route_planning.go
index 7f6d4f60c5..c4cea1ac41 100644
--- a/go/vt/vtgate/planbuilder/route_planning.go
+++ b/go/vt/vtgate/planbuilder/route_planning.go
@@ -107,20 +107,15 @@ func optimizeSubQuery(ctx planningContext, op *abstract.SubQuery) (queryTree, er
 		}
 
 		preds := inner.Inner.UnsolvedPredicates(ctx.semTable)
-		var mergeErr error
-		merger := func(a, b *routeTree) *routeTree {
-			var merged *routeTree
-			merged, mergeErr = mergeSubQuery(ctx, a, inner)
-			return merged
+		merger := func(a, b *routeTree) (*routeTree, error) {
+			return mergeSubQuery(ctx, a, inner)
 		}
 
-		merged, err := tryMerge(ctx, outerTree, treeInner, preds, merger)
+		merged, err := tryMergeSubQuery(ctx, outerTree, treeInner, inner, preds, merger)
 		if err != nil {
 			return nil, err
 		}
-		if mergeErr != nil {
-			return nil, mergeErr
-		}
+
 		if merged == nil {
 			if len(preds) > 0 {
 				return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: cross-shard correlated subquery")
@@ -152,6 +147,92 @@ func optimizeSubQuery(ctx planningContext, op *abstract.SubQuery) (queryTree, er
 	return outerTree, nil
 }
 
+func tryMergeSubQuery(ctx planningContext, outer, subq queryTree, subQueryInner *abstract.SubQueryInner, joinPredicates []sqlparser.Expr, merger mergeFunc) (queryTree, error) {
+	var merged queryTree
+	var err error
+	switch outerTree := outer.(type) {
+	case *routeTree:
+		merged, err = tryMerge(ctx, outerTree, subq, joinPredicates, merger)
+		if err != nil {
+			return nil, err
+		}
+		return merged, err
+	case *joinTree:
+		if outerTree.outer {
+			return nil, nil
+		}
+		newMergefunc := func(a, b *routeTree) (*routeTree, error) {
+			rt, err := merger(a, b)
+			if err != nil {
+				return nil, err
+			}
+			return rt, rewriteSubqueryDependenciesForJoin(a, outerTree, subQueryInner, ctx)
+		}
+		merged, err = tryMergeSubQuery(ctx, outerTree.lhs, subq, subQueryInner, joinPredicates, newMergefunc)
+		if err != nil {
+			return nil, err
+		}
+		if merged != nil {
+			outerTree.lhs = merged
+			return outerTree, nil
+		}
+
+		merged, err = tryMergeSubQuery(ctx, outerTree.rhs, subq, subQueryInner, joinPredicates, newMergefunc)
+		if err != nil {
+			return nil, err
+		}
+		if merged != nil {
+			outerTree.rhs = merged
+			return outerTree, nil
+		}
+		return nil, nil
+	default:
+		return nil, nil
+	}
+}
+
+func rewriteSubqueryDependenciesForJoin(a *routeTree, outerTree *joinTree, subQueryInner *abstract.SubQueryInner, ctx planningContext) error {
+	// first we find the other side of the tree by comparing the tableIDs
+	// other side is RHS if the subquery is in the LHS, otherwise it is LHS
+	otherTree := outerTree.lhs
+	if a.tableID() == outerTree.lhs.tableID() {
+		otherTree = outerTree.rhs
+	}
+
+	var rewriteError error
+	// go over the entire where expression in the subquery
+	sqlparser.Rewrite(subQueryInner.SelectStatement.Where.Expr, func(cursor *sqlparser.Cursor) bool {
+		sqlNode := cursor.Node()
+		switch node := sqlNode.(type) {
+		case *sqlparser.ColName:
+			// check weather the column name belongs to the other side of the join tree
+			if ctx.semTable.Dependencies(node).IsSolvedBy(otherTree.tableID()) {
+				// get the bindVariable for that column name and replace it in the subquery
+				bindVar := node.CompliantName()
+				cursor.Replace(sqlparser.NewArgument(bindVar))
+				// check wether the bindVariable already exists in the joinVars of the other tree
+				_, alreadyExists := outerTree.vars[bindVar]
+				if alreadyExists {
+					return false
+				}
+				// if it does not exist, then push this as an output column there and add it to the joinVars
+				columnIndexes, err := otherTree.pushOutputColumns([]*sqlparser.ColName{node}, ctx.semTable)
+				if err != nil {
+					rewriteError = err
+					return false
+				}
+				columnIndex := columnIndexes[0]
+				outerTree.vars[bindVar] = columnIndex
+				return false
+			}
+		}
+		return true
+	}, nil)
+
+	// return any error while rewriting
+	return rewriteError
+}
+
 func mergeSubQuery(ctx planningContext, outer *routeTree, subq *abstract.SubQueryInner) (*routeTree, error) {
 	ctx.sqToReplace[subq.ArgName] = subq.SelectStatement
 	err := outer.resetRoutingSelections(ctx)
@@ -315,11 +396,11 @@ func mergeOrJoinInner(ctx planningContext, lhs, rhs queryTree, joinPredicates []
 func mergeOrJoin(ctx planningContext, lhs, rhs queryTree, joinPredicates []sqlparser.Expr, inner bool) (queryTree, error) {
 	newTabletSet := lhs.tableID() | rhs.tableID()
 
-	merger := func(a, b *routeTree) *routeTree {
+	merger := func(a, b *routeTree) (*routeTree, error) {
 		if inner {
-			return createRoutePlanForInner(a, b, newTabletSet, joinPredicates)
+			return createRoutePlanForInner(a, b, newTabletSet, joinPredicates), nil
 		}
-		return createRoutePlanForOuter(ctx, a, b, newTabletSet, joinPredicates)
+		return createRoutePlanForOuter(ctx, a, b, newTabletSet, joinPredicates), nil
 	}
 
 	newPlan, _ := tryMerge(ctx, lhs, rhs, joinPredicates, merger)
@@ -643,10 +724,10 @@ func canMergeOnFilters(ctx planningContext, a, b *routeTree, joinPredicates []sq
 	return false
 }
 
-type mergeFunc func(a, b *routeTree) *routeTree
+type mergeFunc func(a, b *routeTree) (*routeTree, error)
 
 func tryMerge(ctx planningContext, a, b queryTree, joinPredicates []sqlparser.Expr, merger mergeFunc) (queryTree, error) {
-	aRoute, bRoute := joinTreesToRoutes(a.clone(), b.clone())
+	aRoute, bRoute := queryTreesToRoutes(a.clone(), b.clone())
 	if aRoute == nil || bRoute == nil {
 		return nil, nil
 	}
@@ -654,23 +735,23 @@ func tryMerge(ctx planningContext, a, b queryTree, joinPredicates []sqlparser.Ex
 	sameKeyspace := aRoute.keyspace == bRoute.keyspace
 
 	if sameKeyspace {
-		tree := tryMergeReferenceTable(aRoute, bRoute, merger)
-		if tree != nil {
-			return tree, nil
+		tree, err := tryMergeReferenceTable(aRoute, bRoute, merger)
+		if tree != nil || err != nil {
+			return tree, err
 		}
 	}
 
 	switch aRoute.routeOpCode {
 	case engine.SelectUnsharded, engine.SelectDBA:
 		if aRoute.routeOpCode == bRoute.routeOpCode {
-			return merger(aRoute, bRoute), nil
+			return merger(aRoute, bRoute)
 		}
 	case engine.SelectEqualUnique:
 		// if they are already both being sent to the same shard, we can merge
 		if bRoute.routeOpCode == engine.SelectEqualUnique {
 			if aRoute.selectedVindex() == bRoute.selectedVindex() &&
 				gen4ValuesEqual(ctx, aRoute.vindexExpressions(), bRoute.vindexExpressions()) {
-				return merger(aRoute, bRoute), nil
+				return merger(aRoute, bRoute)
 			}
 			return nil, nil
 		}
@@ -683,21 +764,21 @@ func tryMerge(ctx planningContext, a, b queryTree, joinPredicates []sqlparser.Ex
 			return nil, nil
 		}
 		if !sameKeyspace {
-			return nil, vterrors.New(vtrpcpb.Code_INTERNAL, "unsupported: cross-shard correlated subquery")
+			return nil, vterrors.New(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: cross-shard correlated subquery")
 		}
 
 		canMerge := canMergeOnFilters(ctx, aRoute, bRoute, joinPredicates)
 		if !canMerge {
 			return nil, nil
 		}
-		r := merger(aRoute, bRoute)
+		r, err := merger(aRoute, bRoute)
 		r.pickBestAvailableVindex()
-		return r, nil
+		return r, err
 	}
 	return nil, nil
 }
 
-func tryMergeReferenceTable(aRoute *routeTree, bRoute *routeTree, merger mergeFunc) *routeTree {
+func tryMergeReferenceTable(aRoute *routeTree, bRoute *routeTree, merger mergeFunc) (*routeTree, error) {
 	// if either side is a reference table, we can just merge it and use the opcode of the other side
 	var opCode engine.RouteOpcode
 	var selected *vindexOption
@@ -710,13 +791,16 @@ func tryMergeReferenceTable(aRoute *routeTree, bRoute *routeTree, merger mergeFu
 		selected = aRoute.selected
 		opCode = aRoute.routeOpCode
 	default:
-		return nil
+		return nil, nil
 	}
 
-	r := merger(aRoute, bRoute)
+	r, err := merger(aRoute, bRoute)
+	if err != nil {
+		return nil, err
+	}
 	r.routeOpCode = opCode
 	r.selected = selected
-	return r
+	return r, nil
 }
 
 func makeRoute(j queryTree) *routeTree {
@@ -750,7 +834,7 @@ func makeRoute(j queryTree) *routeTree {
 	return inner
 }
 
-func joinTreesToRoutes(a, b queryTree) (*routeTree, *routeTree) {
+func queryTreesToRoutes(a, b queryTree) (*routeTree, *routeTree) {
 	aRoute := makeRoute(a)
 	if aRoute == nil {
 		return nil, nil
diff --git a/go/vt/vtgate/semantics/semantic_state.go b/go/vt/vtgate/semantics/semantic_state.go
index 244ed368e1..a48ff6d5ee 100644
--- a/go/vt/vtgate/semantics/semantic_state.go
+++ b/go/vt/vtgate/semantics/semantic_state.go
@@ -35,6 +35,7 @@ type (
 		Authoritative() bool
 		Name() (sqlparser.TableName, error)
 		GetExpr() *sqlparser.AliasedTableExpr
+		GetVindexTable() *vindexes.Table
 		GetColumns() []ColumnInfo
 		IsActualTable() bool
 
@@ -319,6 +320,11 @@ func (v *vTableInfo) GetExpr() *sqlparser.AliasedTableExpr {
 	return v.ASTNode
 }
 
+// GetVindexTable implements the TableInfo interface
+func (v *vTableInfo) GetVindexTable() *vindexes.Table {
+	return nil
+}
+
 func (v *vTableInfo) GetColumns() []ColumnInfo {
 	cols := make([]ColumnInfo, 0, len(v.columnNames))
 	for _, col := range v.columnNames {
@@ -371,6 +377,11 @@ func (a *AliasedTable) GetExpr() *sqlparser.AliasedTableExpr {
 	return a.ASTNode
 }
 
+// GetVindexTable implements the TableInfo interface
+func (a *AliasedTable) GetVindexTable() *vindexes.Table {
+	return a.Table
+}
+
 // Name implements the TableInfo interface
 func (a *AliasedTable) Name() (sqlparser.TableName, error) {
 	return a.ASTNode.TableName()
@@ -396,6 +407,11 @@ func (r *RealTable) GetExpr() *sqlparser.AliasedTableExpr {
 	return r.ASTNode
 }
 
+// GetVindexTable implements the TableInfo interface
+func (r *RealTable) GetVindexTable() *vindexes.Table {
+	return r.Table
+}
+
 // Name implements the TableInfo interface
 func (r *RealTable) Name() (sqlparser.TableName, error) {
 	return r.ASTNode.TableName()


Test patch:
diff --git a/go/vt/vtgate/planbuilder/route_planning_test.go b/go/vt/vtgate/planbuilder/route_planning_test.go
index 3144478d08..97a6e63d70 100644
--- a/go/vt/vtgate/planbuilder/route_planning_test.go
+++ b/go/vt/vtgate/planbuilder/route_planning_test.go
@@ -171,8 +171,8 @@ func TestCreateRoutePlanForOuter(t *testing.T) {
 	assert.NotNil(merge)
 }
 
-func fakeMerger(a, _ *routeTree) *routeTree {
-	return a
+func fakeMerger(a, _ *routeTree) (*routeTree, error) {
+	return a, nil
 }
 
 func equals(left, right sqlparser.Expr) sqlparser.Expr {
diff --git a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
index e466b6b101..4f31c23bba 100644
--- a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
@@ -1258,6 +1258,141 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select u.m from user_extra join user u where u.id in (select m2 from user where user.id = u.id and user_extra.col = user.col) and u.id in (user_extra.col, 1)",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_extra_col": 0
+    },
+    "TableName": "user_extra_`user`",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select user_extra.col from user_extra where 1 != 1",
+        "Query": "select user_extra.col from user_extra",
+        "Table": "user_extra"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectIN",
+        "Keyspace": {
+          "Name": "user",
+          "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",
+        "Table": "`user`",
+        "Values": [
+          [
+            ":user_extra_col",
+            1
+          ]
+        ],
+        "Vindex": "user_index"
+      }
+    ]
+  }
+}
+
+# correlated subquery merge-able into a route of a join tree
+"select u.m from user_extra join user u where u.id in (select m2 from user where user.id = u.id) and u.id in (user_extra.col, 1)"
+{
+  "QueryType": "SELECT",
+  "Original": "select u.m from user_extra join user u where u.id in (select m2 from user where user.id = u.id) and u.id in (user_extra.col, 1)",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_extra_col": 0
+    },
+    "TableName": "user_extra_`user`",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select user_extra.col from user_extra where 1 != 1",
+        "Query": "select user_extra.col from user_extra",
+        "Table": "user_extra"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectIN",
+        "Keyspace": {
+          "Name": "user",
+          "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 ::__vals and u.id in (select m2 from `user` where `user`.id = u.id)",
+        "Table": "`user`",
+        "Values": [
+          [
+            ":user_extra_col",
+            1
+          ]
+        ],
+        "Vindex": "user_index"
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select u.m from user_extra join user u where u.id in (select m2 from user where user.id = u.id) and u.id in (user_extra.col, 1)",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_extra_col": 0
+    },
+    "TableName": "user_extra_`user`",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select user_extra.col from user_extra where 1 != 1",
+        "Query": "select user_extra.col from user_extra",
+        "Table": "user_extra"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectIN",
+        "Keyspace": {
+          "Name": "user",
+          "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",
+        "Table": "`user`",
+        "Values": [
+          [
+            ":user_extra_col",
+            1
+          ]
+        ],
+        "Vindex": "user_index"
+      }
+    ]
+  }
+}
 
 # ensure subquery reordering gets us a better plan
 "select u.m from user_extra join user u where u.id in (select m2 from user where user.id = 5) and u.id = 5"
@@ -1299,6 +1434,44 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select u.m from user_extra join user u where u.id in (select m2 from user where user.id = 5) and u.id = 5",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "1",
+    "TableName": "user_extra_`user`",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select 1 from user_extra where 1 != 1",
+        "Query": "select 1 from user_extra",
+        "Table": "user_extra"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectEqualUnique",
+        "Keyspace": {
+          "Name": "user",
+          "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",
+        "Table": "`user`",
+        "Values": [
+          5
+        ],
+        "Vindex": "user_index"
+      }
+    ]
+  }
+}
 
 # nested subquery
 "select u.m from user_extra join user u where u.id in (select m2 from user where user.id = u.id and user_extra.col = user.col and user.id in (select m3 from user_extra where user_extra.user_id = user.id)) and u.id in (user_extra.col, 1)"
@@ -1346,6 +1519,50 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select u.m from user_extra join user u where u.id in (select m2 from user where user.id = u.id and user_extra.col = user.col and user.id in (select m3 from user_extra where user_extra.user_id = user.id)) and u.id in (user_extra.col, 1)",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_extra_col": 0
+    },
+    "TableName": "user_extra_`user`",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select user_extra.col from user_extra where 1 != 1",
+        "Query": "select user_extra.col from user_extra",
+        "Table": "user_extra"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectIN",
+        "Keyspace": {
+          "Name": "user",
+          "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",
+        "Table": "`user`",
+        "Values": [
+          [
+            ":user_extra_col",
+            1
+          ]
+        ],
+        "Vindex": "user_index"
+      }
+    ]
+  }
+}
 
 # Correlated subquery in where clause
 "select id from user where user.col in (select user_extra.col from user_extra where user_extra.user_id = user.id)"
@@ -1733,6 +1950,59 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select id1 from user where id = (select id2 from user where id2 in (select id3 from user))",
+  "Instructions": {
+    "OperatorType": "Subquery",
+    "Variant": "PulloutValue",
+    "Inputs": [
+      {
+        "OperatorType": "Subquery",
+        "Variant": "PulloutIn",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select id3 from `user` where 1 != 1",
+            "Query": "select id3 from `user`",
+            "Table": "`user`"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select id2 from `user` where 1 != 1",
+            "Query": "select id2 from `user` where id2 in ::__sq2",
+            "Table": "`user`"
+          }
+        ]
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectEqualUnique",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select id1 from `user` where 1 != 1",
+        "Query": "select id1 from `user` where id = :__sq1",
+        "Table": "`user`",
+        "Values": [
+          ":__sq1"
+        ],
+        "Vindex": "user_index"
+      }
+    ]
+  }
+}
 
 # routing rules subquery merge
 "select col from user where id = (select id from route1 where route1.id = user.id)"
@@ -1751,6 +2021,7 @@ Gen4 plan same as above
     "Table": "`user`"
   }
 }
+Gen4 plan same as above
 
 # routing rules subquery pullout
 "select col from user where id = (select id from route2)"
@@ -2853,3 +3124,22 @@ Gen4 plan same as above
     ]
   }
 }
+
+# routing rules subquery merge with alias
+"select col from user where id = (select id from route1 as a where a.id = user.id)"
+{
+  "QueryType": "SELECT",
+  "Original": "select col from user where id = (select id from route1 as a where a.id = user.id)",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectScatter",
+    "Keyspace": {
+      "Name": "user",
+      "Sharded": true
+    },
+    "FieldQuery": "select col from `user` where 1 != 1",
+    "Query": "select col from `user` where id = (select id from `user` as a where a.id = `user`.id)",
+    "Table": "`user`"
+  }
+}
+Gen4 plan same as above
diff --git a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
index e19a91d502..0689dd30ab 100644
--- a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
@@ -1210,6 +1210,43 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select u.id, e.id from user u join user_extra e where u.col = e.col and u.col in (select * from user where user.id = u.id order by col)",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-2,1",
+    "JoinVars": {
+      "u_col": 0
+    },
+    "TableName": "`user`_user_extra",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select u.col, u.id from `user` as u where 1 != 1",
+        "Query": "select u.col, u.id from `user` as u where u.col in (select * from `user` where `user`.id = u.id order by col asc)",
+        "Table": "`user`"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select e.id from user_extra as e where 1 != 1",
+        "Query": "select e.id from user_extra as e where e.col = :u_col",
+        "Table": "user_extra"
+      }
+    ]
+  }
+}
 
 # Order by, verify outer symtab is searched according to its own context.
 "select u.id from user u having u.id in (select col2 from user where user.id = u.id order by u.col)"
diff --git a/go/vt/vtgate/planbuilder/testdata/tpcc_cases.txt b/go/vt/vtgate/planbuilder/testdata/tpcc_cases.txt
index 1cdc65a1b2..5feb186faa 100644
--- a/go/vt/vtgate/planbuilder/testdata/tpcc_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/tpcc_cases.txt
@@ -911,6 +911,25 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select o.o_id,o.o_d_id from orders1 o, (select o_c_id,o_w_id,o_d_id,count(distinct o_w_id),o_id from orders1 where o_w_id=1  and o_id \u003e 2100 and o_id \u003c 11153 group by o_c_id,o_d_id,o_w_id having count( distinct o_id) \u003e 1 limit 1) t where t.o_w_id=o.o_w_id and t.o_d_id=o.o_d_id and t.o_c_id=o.o_c_id limit 1",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectEqualUnique",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": true
+    },
+    "FieldQuery": "select o.o_id, o.o_d_id from orders1 as o, (select o_c_id, o_w_id, o_d_id, count(distinct o_w_id), o_id from orders1 where 1 != 1) as t where 1 != 1",
+    "Query": "select o.o_id, o.o_d_id from orders1 as o, (select o_c_id, o_w_id, o_d_id, count(distinct o_w_id), o_id from orders1 where o_w_id = 1 and o_id \u003e 2100 and o_id \u003c 11153) as t where t.o_w_id = o.o_w_id and t.o_d_id = o.o_d_id and t.o_c_id = o.o_c_id limit 1",
+    "Table": "orders1",
+    "Values": [
+      1
+    ],
+    "Vindex": "hash"
+  }
+}
 
 # TPC-C delete order_line1
 "DELETE FROM order_line1 where ol_w_id=178 AND ol_d_id=1 AND ol_o_id=84"
diff --git a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
index 2ae36cf83d..a4d14fe5a0 100644
--- a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
@@ -727,7 +727,7 @@ Gen4 plan same as above
 # TPC-H query 21
 "select s_name, count(*) as numwait from supplier, lineitem l1, orders, nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey ) and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate ) and s_nationkey = n_nationkey and n_name = 'SAUDI ARABIA' group by s_name order by numwait desc, s_name limit 100"
 "unsupported: cross-shard query with aggregates"
-"gen4 does not yet support: *sqlparser.StarExpr in select list"
+Gen4 plan same as above
 
 # TPC-H query 22
 "select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal from ( select substring(c_phone from 1 for 2) as cntrycode, c_acctbal from customer where substring(c_phone from 1 for 2) in ('13', '31', '23', '29', '30', '18', '17') and c_acctbal > ( select avg(c_acctbal) from customer where c_acctbal > 0.00 and substring(c_phone from 1 for 2) in ('13', '31', '23', '29', '30', '18', '17') ) and not exists ( select * from orders where o_custkey = c_custkey ) ) as custsale group by cntrycode order by cntrycode"