Repository: vitessio/vitess
Pull request #8679 (2021-08-25 05:12:01)

Gen4 refactoring and clean up
## Description
Fixes an issue where the gen4 planner was not looking at the tables of columns when comparing them to other columns or with vindexes.

Also includes a non-trivial amount of clean ups.

## 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/vtgate/engine/cached_size.go b/go/vt/vtgate/engine/cached_size.go
index 1876c9819d..7cc36a3a6e 100644
--- a/go/vt/vtgate/engine/cached_size.go
+++ b/go/vt/vtgate/engine/cached_size.go
@@ -727,7 +727,7 @@ func (cached *Set) CachedSize(alloc bool) int64 {
 	}
 	return size
 }
-func (cached *Subquery) CachedSize(alloc bool) int64 {
+func (cached *SimpleProjection) CachedSize(alloc bool) int64 {
 	if cached == nil {
 		return int64(0)
 	}
@@ -739,8 +739,8 @@ func (cached *Subquery) CachedSize(alloc bool) int64 {
 	{
 		size += int64(cap(cached.Cols)) * int64(8)
 	}
-	// field Subquery vitess.io/vitess/go/vt/vtgate/engine.Primitive
-	if cc, ok := cached.Subquery.(cachedObject); ok {
+	// field Input vitess.io/vitess/go/vt/vtgate/engine.Primitive
+	if cc, ok := cached.Input.(cachedObject); ok {
 		size += cc.CachedSize(true)
 	}
 	return size
diff --git a/go/vt/vtgate/engine/ordered_aggregate.go b/go/vt/vtgate/engine/ordered_aggregate.go
index 87f9b66d43..c31de3de5f 100644
--- a/go/vt/vtgate/engine/ordered_aggregate.go
+++ b/go/vt/vtgate/engine/ordered_aggregate.go
@@ -85,10 +85,9 @@ type AggregateParams struct {
 	KeyCol    int
 	WCol      int
 	WAssigned bool
-	// Alias is set only for distinct opcodes.
-	Alias string `json:",omitempty"`
 
-	Expr sqlparser.Expr
+	Alias string `json:",omitempty"`
+	Expr  sqlparser.Expr
 }
 
 func (ap *AggregateParams) isDistinct() bool {
diff --git a/go/vt/vtgate/engine/projection.go b/go/vt/vtgate/engine/projection.go
index d886e2a933..8306efed86 100644
--- a/go/vt/vtgate/engine/projection.go
+++ b/go/vt/vtgate/engine/projection.go
@@ -8,6 +8,7 @@ import (
 
 var _ Primitive = (*Projection)(nil)
 
+// Projection can evaluate expressions and project the results
 type Projection struct {
 	Cols  []string
 	Exprs []evalengine.Expr
@@ -15,18 +16,22 @@ type Projection struct {
 	noTxNeeded
 }
 
+// RouteType implements the Primitive interface
 func (p *Projection) RouteType() string {
 	return p.Input.RouteType()
 }
 
+// GetKeyspaceName implements the Primitive interface
 func (p *Projection) GetKeyspaceName() string {
 	return p.Input.GetKeyspaceName()
 }
 
+// GetTableName implements the Primitive interface
 func (p *Projection) GetTableName() string {
 	return p.Input.GetTableName()
 }
 
+// Execute implements the Primitive interface
 func (p *Projection) Execute(vcursor VCursor, bindVars map[string]*querypb.BindVariable, wantfields bool) (*sqltypes.Result, error) {
 	result, err := p.Input.Execute(vcursor, bindVars, wantfields)
 	if err != nil {
@@ -59,6 +64,7 @@ func (p *Projection) Execute(vcursor VCursor, bindVars map[string]*querypb.BindV
 	return result, nil
 }
 
+// StreamExecute implements the Primitive interface
 func (p *Projection) StreamExecute(vcursor VCursor, bindVars map[string]*querypb.BindVariable, wantields bool, callback func(*sqltypes.Result) error) error {
 	result, err := p.Input.Execute(vcursor, bindVars, wantields)
 	if err != nil {
@@ -91,6 +97,7 @@ func (p *Projection) StreamExecute(vcursor VCursor, bindVars map[string]*querypb
 	return callback(result)
 }
 
+// GetFields implements the Primitive interface
 func (p *Projection) GetFields(vcursor VCursor, bindVars map[string]*querypb.BindVariable) (*sqltypes.Result, error) {
 	qr, err := p.Input.GetFields(vcursor, bindVars)
 	if err != nil {
@@ -118,10 +125,12 @@ func (p *Projection) addFields(qr *sqltypes.Result, bindVars map[string]*querypb
 	return nil
 }
 
+// Inputs implements the Primitive interface
 func (p *Projection) Inputs() []Primitive {
 	return []Primitive{p.Input}
 }
 
+// description implements the Primitive interface
 func (p *Projection) description() PrimitiveDescription {
 	var exprs []string
 	for _, e := range p.Exprs {
diff --git a/go/vt/vtgate/engine/simple_projection.go b/go/vt/vtgate/engine/simple_projection.go
new file mode 100644
index 0000000000..af0740901e
--- /dev/null
+++ b/go/vt/vtgate/engine/simple_projection.go
@@ -0,0 +1,119 @@
+/*
+Copyright 2019 The Vitess Authors.
+
+Licensed under the Apache License, Version 2.0 (the "License");
+you may not use this file except in compliance with the License.
+You may obtain a copy of the License at
+
+    http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package engine
+
+import (
+	"vitess.io/vitess/go/sqltypes"
+	querypb "vitess.io/vitess/go/vt/proto/query"
+)
+
+var _ Primitive = (*SimpleProjection)(nil)
+
+// SimpleProjection selects which columns to keep from the input
+type SimpleProjection struct {
+	// Cols defines the column numbers from the underlying primitive
+	// to be returned.
+	Cols  []int
+	Input Primitive
+}
+
+// NeedsTransaction implements the Primitive interface
+func (sc *SimpleProjection) NeedsTransaction() bool {
+	return sc.Input.NeedsTransaction()
+}
+
+// RouteType returns a description of the query routing type used by the primitive
+func (sc *SimpleProjection) RouteType() string {
+	return sc.Input.RouteType()
+}
+
+// GetKeyspaceName specifies the Keyspace that this primitive routes to.
+func (sc *SimpleProjection) GetKeyspaceName() string {
+	return sc.Input.GetKeyspaceName()
+}
+
+// GetTableName specifies the table that this primitive routes to.
+func (sc *SimpleProjection) GetTableName() string {
+	return sc.Input.GetTableName()
+}
+
+// Execute performs a non-streaming exec.
+func (sc *SimpleProjection) Execute(vcursor VCursor, bindVars map[string]*querypb.BindVariable, wantfields bool) (*sqltypes.Result, error) {
+	inner, err := sc.Input.Execute(vcursor, bindVars, wantfields)
+	if err != nil {
+		return nil, err
+	}
+	return sc.buildResult(inner), nil
+}
+
+// StreamExecute performs a streaming exec.
+func (sc *SimpleProjection) StreamExecute(vcursor VCursor, bindVars map[string]*querypb.BindVariable, wantfields bool, callback func(*sqltypes.Result) error) error {
+	return sc.Input.StreamExecute(vcursor, bindVars, wantfields, func(inner *sqltypes.Result) error {
+		return callback(sc.buildResult(inner))
+	})
+}
+
+// GetFields fetches the field info.
+func (sc *SimpleProjection) GetFields(vcursor VCursor, bindVars map[string]*querypb.BindVariable) (*sqltypes.Result, error) {
+	inner, err := sc.Input.GetFields(vcursor, bindVars)
+	if err != nil {
+		return nil, err
+	}
+	return &sqltypes.Result{Fields: sc.buildFields(inner)}, nil
+}
+
+// Inputs returns the input to this primitive
+func (sc *SimpleProjection) Inputs() []Primitive {
+	return []Primitive{sc.Input}
+}
+
+// buildResult builds a new result by pulling the necessary columns from
+// the input in the requested order.
+func (sc *SimpleProjection) buildResult(inner *sqltypes.Result) *sqltypes.Result {
+	result := &sqltypes.Result{Fields: sc.buildFields(inner)}
+	result.Rows = make([][]sqltypes.Value, 0, len(inner.Rows))
+	for _, innerRow := range inner.Rows {
+		row := make([]sqltypes.Value, 0, len(sc.Cols))
+		for _, col := range sc.Cols {
+			row = append(row, innerRow[col])
+		}
+		result.Rows = append(result.Rows, row)
+	}
+	result.RowsAffected = inner.RowsAffected
+	return result
+}
+
+func (sc *SimpleProjection) buildFields(inner *sqltypes.Result) []*querypb.Field {
+	if len(inner.Fields) == 0 {
+		return nil
+	}
+	fields := make([]*querypb.Field, 0, len(sc.Cols))
+	for _, col := range sc.Cols {
+		fields = append(fields, inner.Fields[col])
+	}
+	return fields
+}
+
+func (sc *SimpleProjection) description() PrimitiveDescription {
+	other := map[string]interface{}{
+		"Columns": sc.Cols,
+	}
+	return PrimitiveDescription{
+		OperatorType: "SimpleProjection",
+		Other:        other,
+	}
+}
diff --git a/go/vt/vtgate/engine/subquery.go b/go/vt/vtgate/engine/subquery.go
deleted file mode 100644
index 7e058c77db..0000000000
--- a/go/vt/vtgate/engine/subquery.go
+++ /dev/null
@@ -1,118 +0,0 @@
-/*
-Copyright 2019 The Vitess Authors.
-
-Licensed under the Apache License, Version 2.0 (the "License");
-you may not use this file except in compliance with the License.
-You may obtain a copy of the License at
-
-    http://www.apache.org/licenses/LICENSE-2.0
-
-Unless required by applicable law or agreed to in writing, software
-distributed under the License is distributed on an "AS IS" BASIS,
-WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-See the License for the specific language governing permissions and
-limitations under the License.
-*/
-
-package engine
-
-import (
-	"vitess.io/vitess/go/sqltypes"
-	querypb "vitess.io/vitess/go/vt/proto/query"
-)
-
-var _ Primitive = (*Subquery)(nil)
-
-// Subquery specifies the parameters for a subquery primitive.
-type Subquery struct {
-	// Cols defines the column numbers from the underlying primitive
-	// to be returned.
-	Cols     []int
-	Subquery Primitive
-}
-
-func (sq *Subquery) NeedsTransaction() bool {
-	return sq.Subquery.NeedsTransaction()
-}
-
-// RouteType returns a description of the query routing type used by the primitive
-func (sq *Subquery) RouteType() string {
-	return sq.Subquery.RouteType()
-}
-
-// GetKeyspaceName specifies the Keyspace that this primitive routes to.
-func (sq *Subquery) GetKeyspaceName() string {
-	return sq.Subquery.GetKeyspaceName()
-}
-
-// GetTableName specifies the table that this primitive routes to.
-func (sq *Subquery) GetTableName() string {
-	return sq.Subquery.GetTableName()
-}
-
-// Execute performs a non-streaming exec.
-func (sq *Subquery) Execute(vcursor VCursor, bindVars map[string]*querypb.BindVariable, wantfields bool) (*sqltypes.Result, error) {
-	inner, err := sq.Subquery.Execute(vcursor, bindVars, wantfields)
-	if err != nil {
-		return nil, err
-	}
-	return sq.buildResult(inner), nil
-}
-
-// StreamExecute performs a streaming exec.
-func (sq *Subquery) StreamExecute(vcursor VCursor, bindVars map[string]*querypb.BindVariable, wantfields bool, callback func(*sqltypes.Result) error) error {
-	return sq.Subquery.StreamExecute(vcursor, bindVars, wantfields, func(inner *sqltypes.Result) error {
-		return callback(sq.buildResult(inner))
-	})
-}
-
-// GetFields fetches the field info.
-func (sq *Subquery) GetFields(vcursor VCursor, bindVars map[string]*querypb.BindVariable) (*sqltypes.Result, error) {
-	inner, err := sq.Subquery.GetFields(vcursor, bindVars)
-	if err != nil {
-		return nil, err
-	}
-	return &sqltypes.Result{Fields: sq.buildFields(inner)}, nil
-}
-
-// Inputs returns the input to this primitive
-func (sq *Subquery) Inputs() []Primitive {
-	return []Primitive{sq.Subquery}
-}
-
-// buildResult builds a new result by pulling the necessary columns from
-// the subquery in the requested order.
-func (sq *Subquery) buildResult(inner *sqltypes.Result) *sqltypes.Result {
-	result := &sqltypes.Result{Fields: sq.buildFields(inner)}
-	result.Rows = make([][]sqltypes.Value, 0, len(inner.Rows))
-	for _, innerRow := range inner.Rows {
-		row := make([]sqltypes.Value, 0, len(sq.Cols))
-		for _, col := range sq.Cols {
-			row = append(row, innerRow[col])
-		}
-		result.Rows = append(result.Rows, row)
-	}
-	result.RowsAffected = inner.RowsAffected
-	return result
-}
-
-func (sq *Subquery) buildFields(inner *sqltypes.Result) []*querypb.Field {
-	if len(inner.Fields) == 0 {
-		return nil
-	}
-	fields := make([]*querypb.Field, 0, len(sq.Cols))
-	for _, col := range sq.Cols {
-		fields = append(fields, inner.Fields[col])
-	}
-	return fields
-}
-
-func (sq *Subquery) description() PrimitiveDescription {
-	other := map[string]interface{}{
-		"Columns": sq.Cols,
-	}
-	return PrimitiveDescription{
-		OperatorType: "Subquery",
-		Other:        other,
-	}
-}
diff --git a/go/vt/vtgate/planbuilder/abstract/queryprojection.go b/go/vt/vtgate/planbuilder/abstract/queryprojection.go
index 0bbc37b4c9..df7cfa7e3d 100644
--- a/go/vt/vtgate/planbuilder/abstract/queryprojection.go
+++ b/go/vt/vtgate/planbuilder/abstract/queryprojection.go
@@ -62,7 +62,7 @@ type (
 )
 
 // CreateQPFromSelect created the QueryProjection for the input *sqlparser.Select
-func CreateQPFromSelect(sel *sqlparser.Select) (*QueryProjection, error) {
+func CreateQPFromSelect(sel *sqlparser.Select, semTable *semantics.SemTable) (*QueryProjection, error) {
 	qp := &QueryProjection{
 		Distinct: sel.Distinct,
 	}
@@ -89,7 +89,7 @@ func CreateQPFromSelect(sel *sqlparser.Select) (*QueryProjection, error) {
 	}
 
 	for _, group := range sel.GroupBy {
-		expr, weightStrExpr, err := qp.getSimplifiedExpr(group, "group statement")
+		expr, weightStrExpr, err := qp.getSimplifiedExpr(group, semTable)
 		if err != nil {
 			return nil, err
 		}
@@ -101,7 +101,7 @@ func CreateQPFromSelect(sel *sqlparser.Select) (*QueryProjection, error) {
 
 	canPushDownSorting := true
 	for _, order := range sel.OrderBy {
-		expr, weightStrExpr, err := qp.getSimplifiedExpr(order.Expr, "order clause")
+		expr, weightStrExpr, err := qp.getSimplifiedExpr(order.Expr, semTable)
 		if err != nil {
 			return nil, err
 		}
@@ -184,7 +184,7 @@ func (qp *QueryProjection) getNonAggrExprNotMatchingGroupByExprs() sqlparser.Exp
 
 // getSimplifiedExpr takes an expression used in ORDER BY or GROUP BY, which can reference both aliased columns and
 // column offsets, and returns an expression that is simpler to evaluate
-func (qp *QueryProjection) getSimplifiedExpr(e sqlparser.Expr, caller string) (expr sqlparser.Expr, weightStrExpr sqlparser.Expr, err error) {
+func (qp *QueryProjection) getSimplifiedExpr(e sqlparser.Expr, semTable *semantics.SemTable) (expr sqlparser.Expr, weightStrExpr sqlparser.Expr, err error) {
 	// Order by is the column offset to be used from the select expressions
 	// Eg - select id from music order by 1
 	literalExpr, isLiteral := e.(*sqlparser.Literal)
@@ -200,6 +200,7 @@ func (qp *QueryProjection) getSimplifiedExpr(e sqlparser.Expr, caller string) (e
 			expr = &sqlparser.ColName{
 				Name: aliasedExpr.As,
 			}
+			semTable.CopyDependencies(e, expr)
 		}
 
 		return expr, aliasedExpr.Expr, nil
diff --git a/go/vt/vtgate/planbuilder/filtering.go b/go/vt/vtgate/planbuilder/filtering.go
index 4621cf169a..21f732b752 100644
--- a/go/vt/vtgate/planbuilder/filtering.go
+++ b/go/vt/vtgate/planbuilder/filtering.go
@@ -72,7 +72,7 @@ func planFilter(pb *primitiveBuilder, input logicalPlan, filter sqlparser.Expr,
 		return node, nil
 	case *vindexFunc:
 		return filterVindexFunc(node, filter)
-	case *subquery:
+	case *simpleProjection:
 		return nil, errors.New("unsupported: filtering on results of cross-shard subquery")
 	case *orderedAggregate:
 		return nil, errors.New("unsupported: filtering on results of aggregates")
diff --git a/go/vt/vtgate/planbuilder/from.go b/go/vt/vtgate/planbuilder/from.go
index f783419158..be78964a31 100644
--- a/go/vt/vtgate/planbuilder/from.go
+++ b/go/vt/vtgate/planbuilder/from.go
@@ -118,7 +118,7 @@ func (pb *primitiveBuilder) processAliasedTable(tableExpr *sqlparser.AliasedTabl
 		subroute, ok := spb.plan.(*route)
 		if !ok {
 			var err error
-			pb.plan, pb.st, err = newSubquery(tableExpr.As, spb.plan)
+			pb.plan, pb.st, err = newSimpleProjection(tableExpr.As, spb.plan)
 			if err != nil {
 				return err
 			}
diff --git a/go/vt/vtgate/planbuilder/gen4_planner.go b/go/vt/vtgate/planbuilder/gen4_planner.go
new file mode 100644
index 0000000000..0f3d3fb04d
--- /dev/null
+++ b/go/vt/vtgate/planbuilder/gen4_planner.go
@@ -0,0 +1,182 @@
+/*
+Copyright 2020 The Vitess Authors.
+
+Licensed under the Apache License, Version 2.0 (the "License");
+you may not use this file except in compliance with the License.
+You may obtain a copy of the License at
+
+    http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package planbuilder
+
+import (
+	vtrpcpb "vitess.io/vitess/go/vt/proto/vtrpc"
+	"vitess.io/vitess/go/vt/sqlparser"
+	"vitess.io/vitess/go/vt/vterrors"
+	"vitess.io/vitess/go/vt/vtgate/engine"
+	"vitess.io/vitess/go/vt/vtgate/planbuilder/abstract"
+	"vitess.io/vitess/go/vt/vtgate/semantics"
+)
+
+var _ selectPlanner = gen4Planner
+
+func gen4Planner(_ string) func(sqlparser.Statement, *sqlparser.ReservedVars, ContextVSchema) (engine.Primitive, error) {
+	return func(stmt sqlparser.Statement, reservedVars *sqlparser.ReservedVars, vschema ContextVSchema) (engine.Primitive, error) {
+		sel, ok := stmt.(*sqlparser.Select)
+		if !ok {
+			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "%T not yet supported", stmt)
+		}
+
+		// handle dual table for processing at vtgate.
+		p, err := handleDualSelects(sel, vschema)
+		if err != nil {
+			return nil, err
+		}
+		if p != nil {
+			return p, nil
+		}
+
+		getPlan := func(sel *sqlparser.Select) (logicalPlan, error) {
+			return newBuildSelectPlan(sel, reservedVars, vschema)
+		}
+
+		plan, err := getPlan(sel)
+		if err != nil {
+			return nil, err
+		}
+
+		if shouldRetryWithCNFRewriting(plan) {
+			// by transforming the predicates to CNF, the planner will sometimes find better plans
+			primitive := rewriteToCNFAndReplan(stmt, getPlan)
+			if primitive != nil {
+				return primitive, nil
+			}
+		}
+		return plan.Primitive(), nil
+	}
+}
+
+func newBuildSelectPlan(sel *sqlparser.Select, reservedVars *sqlparser.ReservedVars, vschema ContextVSchema) (logicalPlan, error) {
+	ksName := ""
+	if ks, _ := vschema.DefaultKeyspace(); ks != nil {
+		ksName = ks.Name
+	}
+	semTable, err := semantics.Analyze(sel, ksName, vschema, starRewrite)
+	if err != nil {
+		return nil, err
+	}
+
+	ctx := newPlanningContext(reservedVars, semTable, vschema)
+	err = subqueryRewrite(ctx, sel)
+	if err != nil {
+		return nil, err
+	}
+
+	opTree, err := abstract.CreateOperatorFromSelect(sel, semTable)
+	if err != nil {
+		return nil, err
+	}
+
+	tree, err := optimizeQuery(ctx, opTree)
+	if err != nil {
+		return nil, err
+	}
+
+	plan, err := transformToLogicalPlan(ctx, tree, semTable)
+	if err != nil {
+		return nil, err
+	}
+
+	plan, err = planHorizon(ctx, plan, sel)
+	if err != nil {
+		return nil, err
+	}
+
+	if err := setMiscFunc(plan, sel); err != nil {
+		return nil, err
+	}
+
+	if err := plan.WireupGen4(semTable); err != nil {
+		return nil, err
+	}
+
+	directives := sqlparser.ExtractCommentDirectives(sel.Comments)
+	if directives.IsSet(sqlparser.DirectiveScatterErrorsAsWarnings) {
+		visit(plan, func(logicalPlan logicalPlan) (bool, logicalPlan, error) {
+			switch plan := logicalPlan.(type) {
+			case *route:
+				plan.eroute.ScatterErrorsAsWarnings = true
+			}
+			return true, logicalPlan, nil
+		})
+	}
+
+	return plan, nil
+}
+
+func newPlanningContext(reservedVars *sqlparser.ReservedVars, semTable *semantics.SemTable, vschema ContextVSchema) planningContext {
+	ctx := planningContext{
+		reservedVars: reservedVars,
+		semTable:     semTable,
+		vschema:      vschema,
+		sqToReplace:  map[string]*sqlparser.Select{},
+	}
+	return ctx
+}
+
+func planLimit(limit *sqlparser.Limit, plan logicalPlan) (logicalPlan, error) {
+	if limit == nil {
+		return plan, nil
+	}
+	rb, ok := plan.(*route)
+	if ok && rb.isSingleShard() {
+		rb.SetLimit(limit)
+		return plan, nil
+	}
+
+	lPlan, err := createLimit(plan, limit)
+	if err != nil {
+		return nil, err
+	}
+
+	// visit does not modify the plan.
+	_, err = visit(lPlan, setUpperLimit)
+	if err != nil {
+		return nil, err
+	}
+	return lPlan, nil
+}
+
+func checkUnsupportedConstructs(sel *sqlparser.Select) error {
+	if sel.Having != nil {
+		return semantics.Gen4NotSupportedF("HAVING")
+	}
+	return nil
+}
+
+func planHorizon(ctx planningContext, plan logicalPlan, sel *sqlparser.Select) (logicalPlan, error) {
+	hp := horizonPlanning{
+		sel: sel,
+	}
+
+	replaceSubQuery(ctx.sqToReplace, sel)
+	var err error
+	plan, err = hp.planHorizon(ctx, plan)
+	if err != nil {
+		return nil, err
+	}
+
+	plan, err = planLimit(sel.Limit, plan)
+	if err != nil {
+		return nil, err
+	}
+	return plan, nil
+
+}
diff --git a/go/vt/vtgate/planbuilder/horizon_planning.go b/go/vt/vtgate/planbuilder/horizon_planning.go
index aba7b7e76e..e3d54e6343 100644
--- a/go/vt/vtgate/planbuilder/horizon_planning.go
+++ b/go/vt/vtgate/planbuilder/horizon_planning.go
@@ -29,6 +29,105 @@ import (
 	"vitess.io/vitess/go/vt/vtgate/engine"
 )
 
+type horizonPlanning struct {
+	sel             *sqlparser.Select
+	qp              *abstract.QueryProjection
+	needsTruncation bool
+	vtgateGrouping  bool
+}
+
+func (hp *horizonPlanning) planHorizon(ctx planningContext, plan logicalPlan) (logicalPlan, error) {
+	rb, ok := plan.(*route)
+	if !ok && ctx.semTable.ProjectionErr != nil {
+		return nil, ctx.semTable.ProjectionErr
+	}
+
+	if ok && rb.isSingleShard() {
+		createSingleShardRoutePlan(hp.sel, rb)
+		return plan, nil
+	}
+
+	qp, err := abstract.CreateQPFromSelect(hp.sel, ctx.semTable)
+	if err != nil {
+		return nil, err
+	}
+
+	hp.qp = qp
+
+	if err := checkUnsupportedConstructs(hp.sel); err != nil {
+		return nil, err
+	}
+
+	if hp.qp.NeedsAggregation() {
+		plan, err = hp.planAggregations(ctx, plan)
+		if err != nil {
+			return nil, err
+		}
+	} else {
+		_, isOA := plan.(*orderedAggregate)
+		if isOA {
+			plan = &simpleProjection{
+				logicalPlanCommon: newBuilderCommon(plan),
+				eSimpleProj:       &engine.SimpleProjection{},
+			}
+		}
+		for _, e := range hp.qp.SelectExprs {
+			if _, _, err := pushProjection(e.Col, plan, ctx.semTable, true, false); err != nil {
+				return nil, err
+			}
+		}
+	}
+
+	if len(hp.qp.OrderExprs) > 0 {
+		plan, err = hp.planOrderBy(ctx, hp.qp.OrderExprs, plan)
+		if err != nil {
+			return nil, err
+		}
+	}
+
+	if hp.qp.CanPushDownSorting && hp.vtgateGrouping {
+		plan, err = hp.planOrderByUsingGroupBy(ctx, plan)
+		if err != nil {
+			return nil, err
+		}
+	}
+
+	plan, err = hp.planDistinct(ctx, plan)
+	if err != nil {
+		return nil, err
+	}
+
+	err = hp.truncateColumnsIfNeeded(plan)
+	if err != nil {
+		return nil, err
+	}
+
+	return plan, nil
+}
+
+func (hp *horizonPlanning) truncateColumnsIfNeeded(plan logicalPlan) error {
+	if !hp.needsTruncation {
+		return nil
+	}
+
+	switch p := plan.(type) {
+	case *route:
+		p.eroute.SetTruncateColumnCount(hp.sel.GetColumnCount())
+	case *joinGen4:
+		// since this is a join, we can safely add extra columns and not need to truncate them
+	case *orderedAggregate:
+		p.eaggr.SetTruncateColumnCount(hp.sel.GetColumnCount())
+	case *memorySort:
+		p.truncater.SetTruncateColumnCount(hp.sel.GetColumnCount())
+	case *pulloutSubquery:
+		return hp.truncateColumnsIfNeeded(p.underlying)
+	default:
+		return vterrors.Errorf(vtrpcpb.Code_INTERNAL, "plan type not known for column truncation: %T", plan)
+	}
+
+	return nil
+}
+
 func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *semantics.SemTable, inner bool, reuseCol bool) (int, bool, error) {
 	switch node := plan.(type) {
 	case *route:
@@ -43,7 +142,7 @@ func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *sem
 		}
 		sel := node.Select.(*sqlparser.Select)
 		if reuseCol {
-			if i := checkIfAlreadyExists(expr, sel); i != -1 {
+			if i := checkIfAlreadyExists(expr, sel, semTable); i != -1 {
 				return i, false, nil
 			}
 		}
@@ -90,6 +189,24 @@ func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *sem
 	case *pulloutSubquery:
 		// push projection to the outer query
 		return pushProjection(expr, node.underlying, semTable, inner, reuseCol)
+	case *simpleProjection:
+		offset, _, err := pushProjection(expr, node.input, semTable, inner, reuseCol)
+		if err != nil {
+			return 0, false, err
+		}
+		node.eSimpleProj.Cols = append(node.eSimpleProj.Cols, offset)
+		return len(node.eSimpleProj.Cols) - 1, true, nil
+	case *orderedAggregate:
+		colName, isColName := expr.Expr.(*sqlparser.ColName)
+		for _, aggregate := range node.eaggr.Aggregates {
+			if sqlparser.EqualsExpr(aggregate.Expr, expr.Expr) {
+				return aggregate.Col, false, nil
+			}
+			if isColName && colName.Name.EqualString(aggregate.Alias) {
+				return aggregate.Col, false, nil
+			}
+		}
+		return 0, false, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "cannot push projections in ordered aggregates")
 	default:
 		return 0, false, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "[BUG] push projection does not yet support: %T", node)
 	}
@@ -104,7 +221,9 @@ func removeQualifierFromColName(expr *sqlparser.AliasedExpr) *sqlparser.AliasedE
 	return expr
 }
 
-func checkIfAlreadyExists(expr *sqlparser.AliasedExpr, sel *sqlparser.Select) int {
+func checkIfAlreadyExists(expr *sqlparser.AliasedExpr, sel *sqlparser.Select, semTable *semantics.SemTable) int {
+	exprDep := semTable.BaseTableDependencies(expr.Expr)
+
 	for i, selectExpr := range sel.SelectExprs {
 		selectExpr, ok := selectExpr.(*sqlparser.AliasedExpr)
 		if !ok {
@@ -113,6 +232,12 @@ func checkIfAlreadyExists(expr *sqlparser.AliasedExpr, sel *sqlparser.Select) in
 
 		selectExprCol, isSelectExprCol := selectExpr.Expr.(*sqlparser.ColName)
 		exprCol, isExprCol := expr.Expr.(*sqlparser.ColName)
+		selectExprDep := semTable.BaseTableDependencies(selectExpr.Expr)
+
+		// Check that the two expressions have the same dependencies
+		if selectExprDep != exprDep {
+			continue
+		}
 
 		if selectExpr.As.IsEmpty() {
 			// we don't have an alias
@@ -125,7 +250,7 @@ func checkIfAlreadyExists(expr *sqlparser.AliasedExpr, sel *sqlparser.Select) in
 				return i
 			}
 		} else if isExprCol && selectExpr.As.Equal(exprCol.Name) {
-			// we have an aliased column, checking if the the expression is matching the alias
+			// we have an aliased column, checking if the expression is matching the alias
 			return i
 		}
 	}
@@ -136,16 +261,16 @@ func (hp *horizonPlanning) haveToTruncate(v bool) {
 	hp.needsTruncation = hp.needsTruncation || v
 }
 
-func (hp *horizonPlanning) planAggregations() error {
-	newPlan := hp.plan
+func (hp *horizonPlanning) planAggregations(ctx planningContext, plan logicalPlan) (logicalPlan, error) {
+	newPlan := plan
 	var oa *orderedAggregate
-	uniqVindex := hasUniqueVindex(hp.vschema, hp.semTable, hp.qp.GroupByExprs)
-	_, joinPlan := hp.plan.(*joinGen4)
+	uniqVindex := hasUniqueVindex(ctx.vschema, ctx.semTable, hp.qp.GroupByExprs)
+	_, joinPlan := plan.(*joinGen4)
 	if !uniqVindex || joinPlan {
 		eaggr := &engine.OrderedAggregate{}
 		oa = &orderedAggregate{
 			resultsBuilder: resultsBuilder{
-				logicalPlanCommon: newBuilderCommon(hp.plan),
+				logicalPlanCommon: newBuilderCommon(plan),
 				weightStrings:     make(map[*resultColumn]int),
 				truncater:         eaggr,
 			},
@@ -158,30 +283,30 @@ func (hp *horizonPlanning) planAggregations() error {
 	for _, e := range hp.qp.SelectExprs {
 		// push all expression if they are non-aggregating or the plan is not ordered aggregated plan.
 		if !e.Aggr || oa == nil {
-			_, _, err := pushProjection(e.Col, hp.plan, hp.semTable, true, false)
+			_, _, err := pushProjection(e.Col, plan, ctx.semTable, true, false)
 			if err != nil {
-				return err
+				return nil, err
 			}
 			continue
 		}
 
 		fExpr, isFunc := e.Col.Expr.(*sqlparser.FuncExpr)
 		if !isFunc {
-			return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: complex aggregate expression")
+			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: complex aggregate expression")
 		}
 		opcode, found := engine.SupportedAggregates[fExpr.Name.Lowered()]
 		if !found {
-			return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: complex aggregate expression")
+			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: complex aggregate expression")
 		}
-		handleDistinct, innerAliased, err := hp.needDistinctHandling(fExpr, opcode, oa.input)
+		handleDistinct, innerAliased, err := hp.needDistinctHandling(ctx, fExpr, opcode, plan)
 		if err != nil {
-			return err
+			return nil, err
 		}
 
 		pushExpr, alias, opcode := hp.createPushExprAndAlias(e, handleDistinct, innerAliased, opcode, oa)
-		offset, _, err := pushProjection(pushExpr, oa.input, hp.semTable, true, false)
+		offset, _, err := pushProjection(pushExpr, plan, ctx.semTable, true, false)
 		if err != nil {
-			return err
+			return nil, err
 		}
 		oa.eaggr.Aggregates = append(oa.eaggr.Aggregates, &engine.AggregateParams{
 			Opcode: opcode,
@@ -192,9 +317,9 @@ func (hp *horizonPlanning) planAggregations() error {
 	}
 
 	for _, groupExpr := range hp.qp.GroupByExprs {
-		added, err := planGroupByGen4(groupExpr, newPlan, hp.semTable)
+		added, err := planGroupByGen4(groupExpr, newPlan, ctx.semTable)
 		if err != nil {
-			return err
+			return nil, err
 		}
 		hp.haveToTruncate(added)
 	}
@@ -209,29 +334,29 @@ func (hp *horizonPlanning) planAggregations() error {
 			)
 		}
 		if len(orderExprs) > 0 {
-			newInput, err := hp.planOrderBy(orderExprs, hp.plan)
+			newInput, err := hp.planOrderBy(ctx, orderExprs, plan)
 			if err != nil {
-				return err
+				return nil, err
 			}
 			oa.input = newInput
-			hp.plan = oa
+			plan = oa
 		}
 	} else {
-		hp.plan = newPlan
+		plan = newPlan
 	}
 
 	// done with aggregation planning. let's check if we should fail the query
-	if _, planIsRoute := hp.plan.(*route); !planIsRoute {
+	if _, planIsRoute := plan.(*route); !planIsRoute {
 		// if we had to build up additional operators around the route, we have to fail this query
 		for _, expr := range hp.qp.SelectExprs {
 			colExpr := expr.Col.Expr
 			if !sqlparser.IsAggregation(colExpr) && sqlparser.ContainsAggregation(colExpr) {
-				return vterrors.New(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: complex aggregate expression")
+				return nil, vterrors.New(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: complex aggregate expression")
 			}
 		}
 	}
 
-	return nil
+	return plan, nil
 }
 
 // createPushExprAndAlias creates the expression that should be pushed down to the leaves,
@@ -245,6 +370,11 @@ func (hp *horizonPlanning) createPushExprAndAlias(
 ) (*sqlparser.AliasedExpr, string, engine.AggregateOpcode) {
 	pushExpr := expr.Col
 	var alias string
+	if expr.Col.As.IsEmpty() {
+		alias = sqlparser.String(expr.Col.Expr)
+	} else {
+		alias = expr.Col.As.String()
+	}
 	if handleDistinct {
 		pushExpr = innerAliased
 
@@ -254,11 +384,6 @@ func (hp *horizonPlanning) createPushExprAndAlias(
 		case engine.AggregateSum:
 			opcode = engine.AggregateSumDistinct
 		}
-		if expr.Col.As.IsEmpty() {
-			alias = sqlparser.String(expr.Col.Expr)
-		} else {
-			alias = expr.Col.As.String()
-		}
 
 		oa.eaggr.PreProcess = true
 		hp.haveToTruncate(true)
@@ -313,7 +438,7 @@ func planGroupByGen4(groupExpr abstract.GroupBy, plan logicalPlan, semTable *sem
 	}
 }
 
-func (hp *horizonPlanning) planOrderByUsingGroupBy() (logicalPlan, error) {
+func (hp *horizonPlanning) planOrderByUsingGroupBy(ctx planningContext, plan logicalPlan) (logicalPlan, error) {
 	var orderExprs []abstract.OrderBy
 	for _, groupExpr := range hp.qp.GroupByExprs {
 		addExpr := true
@@ -331,22 +456,22 @@ func (hp *horizonPlanning) planOrderByUsingGroupBy() (logicalPlan, error) {
 		}
 	}
 	if len(orderExprs) > 0 {
-		return hp.planOrderBy(orderExprs, hp.plan)
+		return hp.planOrderBy(ctx, orderExprs, plan)
 	}
-	return hp.plan, nil
+	return plan, nil
 }
 
-func (hp *horizonPlanning) planOrderBy(orderExprs []abstract.OrderBy, plan logicalPlan) (logicalPlan, error) {
+func (hp *horizonPlanning) planOrderBy(ctx planningContext, orderExprs []abstract.OrderBy, plan logicalPlan) (logicalPlan, error) {
 	switch plan := plan.(type) {
 	case *route:
-		newPlan, truncate, err := planOrderByForRoute(orderExprs, plan, hp.semTable)
+		newPlan, truncate, err := planOrderByForRoute(orderExprs, plan, ctx.semTable)
 		if err != nil {
 			return nil, err
 		}
 		hp.haveToTruncate(truncate)
 		return newPlan, nil
 	case *joinGen4:
-		newPlan, err := hp.planOrderByForJoin(orderExprs, plan)
+		newPlan, err := hp.planOrderByForJoin(ctx, orderExprs, plan)
 		if err != nil {
 			return nil, err
 		}
@@ -372,7 +497,7 @@ func (hp *horizonPlanning) planOrderBy(orderExprs []abstract.OrderBy, plan logic
 				return ms, nil
 			}
 		}
-		newInput, err := hp.planOrderBy(orderExprs, plan.input)
+		newInput, err := hp.planOrderBy(ctx, orderExprs, plan.input)
 		if err != nil {
 			return nil, err
 		}
@@ -381,7 +506,7 @@ func (hp *horizonPlanning) planOrderBy(orderExprs []abstract.OrderBy, plan logic
 	case *memorySort:
 		return plan, nil
 	case *pulloutSubquery:
-		newUnderlyingPlan, err := hp.planOrderBy(orderExprs, plan.underlying)
+		newUnderlyingPlan, err := hp.planOrderBy(ctx, orderExprs, plan.underlying)
 		if err != nil {
 			return nil, err
 		}
@@ -456,16 +581,16 @@ func weightStringFor(expr sqlparser.Expr) sqlparser.Expr {
 
 }
 
-func (hp *horizonPlanning) planOrderByForJoin(orderExprs []abstract.OrderBy, plan *joinGen4) (logicalPlan, error) {
-	if allLeft(orderExprs, hp.semTable, plan.Left.ContainsTables()) {
-		newLeft, err := hp.planOrderBy(orderExprs, plan.Left)
+func (hp *horizonPlanning) planOrderByForJoin(ctx planningContext, orderExprs []abstract.OrderBy, plan *joinGen4) (logicalPlan, error) {
+	if allLeft(orderExprs, ctx.semTable, plan.Left.ContainsTables()) {
+		newLeft, err := hp.planOrderBy(ctx, orderExprs, plan.Left)
 		if err != nil {
 			return nil, err
 		}
 		plan.Left = newLeft
 		return plan, nil
 	}
-	sortPlan, err := hp.createMemorySortPlan(plan, orderExprs)
+	sortPlan, err := hp.createMemorySortPlan(ctx, plan, orderExprs)
 	if err != nil {
 		return nil, err
 	}
@@ -512,7 +637,7 @@ func findExprInOrderedAggr(plan *orderedAggregate, order abstract.OrderBy) (int,
 	return 0, 0, false
 }
 
-func (hp *horizonPlanning) createMemorySortPlan(plan logicalPlan, orderExprs []abstract.OrderBy) (logicalPlan, error) {
+func (hp *horizonPlanning) createMemorySortPlan(ctx planningContext, plan logicalPlan, orderExprs []abstract.OrderBy) (logicalPlan, error) {
 	primitive := &engine.MemorySort{}
 	ms := &memorySort{
 		resultsBuilder: resultsBuilder{
@@ -524,7 +649,7 @@ func (hp *horizonPlanning) createMemorySortPlan(plan logicalPlan, orderExprs []a
 	}
 
 	for _, order := range orderExprs {
-		offset, weightStringOffset, added, err := wrapAndPushExpr(order.Inner.Expr, order.WeightStrExpr, plan, hp.semTable)
+		offset, weightStringOffset, added, err := wrapAndPushExpr(order.Inner.Expr, order.WeightStrExpr, plan, ctx.semTable)
 		if err != nil {
 			return nil, err
 		}
@@ -549,33 +674,34 @@ func allLeft(orderExprs []abstract.OrderBy, semTable *semantics.SemTable, lhsTab
 	return true
 }
 
-func (hp *horizonPlanning) planDistinct() error {
+func (hp *horizonPlanning) planDistinct(ctx planningContext, plan logicalPlan) (logicalPlan, error) {
 	if !hp.qp.NeedsDistinct() {
-		return nil
+		return plan, nil
 	}
-	switch p := hp.plan.(type) {
+	switch p := plan.(type) {
 	case *route:
 		// we always make the underlying query distinct,
 		// and then we might also add a distinct operator on top if it is needed
 		p.Select.MakeDistinct()
-		if !p.isSingleShard() && !selectHasUniqueVindex(hp.vschema, hp.semTable, hp.qp.SelectExprs) {
-			return hp.addDistinct()
+		if p.isSingleShard() || selectHasUniqueVindex(ctx.vschema, ctx.semTable, hp.qp.SelectExprs) {
+			return plan, nil
 		}
-		return nil
+
+		return hp.addDistinct(ctx, plan)
 	case *joinGen4:
-		return hp.addDistinct()
+		return hp.addDistinct(ctx, plan)
 	case *orderedAggregate:
 		return hp.planDistinctOA(p)
 	default:
-		return vterrors.Errorf(vtrpcpb.Code_INTERNAL, "unknown plan type for DISTINCT %T", hp.plan)
+		return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "unknown plan type for DISTINCT %T", plan)
 	}
 }
 
-func (hp *horizonPlanning) planDistinctOA(currPlan *orderedAggregate) error {
+func (hp *horizonPlanning) planDistinctOA(currPlan *orderedAggregate) (logicalPlan, error) {
 	eaggr := &engine.OrderedAggregate{}
 	oa := &orderedAggregate{
 		resultsBuilder: resultsBuilder{
-			logicalPlanCommon: newBuilderCommon(hp.plan),
+			logicalPlanCommon: newBuilderCommon(currPlan),
 			weightStrings:     make(map[*resultColumn]int),
 			truncater:         eaggr,
 		},
@@ -601,24 +727,23 @@ func (hp *horizonPlanning) planDistinctOA(currPlan *orderedAggregate) error {
 			}
 		}
 		if !found {
-			return vterrors.Errorf(vtrpcpb.Code_INTERNAL, "[BUG] unable to plan distinct query as the column is not projected: %s", sqlparser.String(sExpr.Col))
+			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "[BUG] unable to plan distinct query as the column is not projected: %s", sqlparser.String(sExpr.Col))
 		}
 	}
-	hp.plan = oa
-	return nil
+	return oa, nil
 }
 
-func (hp *horizonPlanning) addDistinct() error {
+func (hp *horizonPlanning) addDistinct(ctx planningContext, plan logicalPlan) (logicalPlan, error) {
 	eaggr := &engine.OrderedAggregate{}
 	var orderExprs []abstract.OrderBy
 	for index, sExpr := range hp.qp.SelectExprs {
 		if isAmbiguousOrderBy(index, sExpr.Col.As, hp.qp.SelectExprs) {
-			return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "generating order by clause: ambiguous symbol reference: %s", sqlparser.String(sExpr.Col.As))
+			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "generating order by clause: ambiguous symbol reference: %s", sqlparser.String(sExpr.Col.As))
 		}
 		grpParam := &engine.GroupByParams{KeyCol: index, WeightStringCol: -1}
-		_, wOffset, added, err := wrapAndPushExpr(sExpr.Col.Expr, sExpr.Col.Expr, hp.plan, hp.semTable)
+		_, wOffset, added, err := wrapAndPushExpr(sExpr.Col.Expr, sExpr.Col.Expr, plan, ctx.semTable)
 		if err != nil {
-			return err
+			return nil, err
 		}
 		hp.needsTruncation = hp.needsTruncation || added
 		grpParam.WeightStringCol = wOffset
@@ -627,6 +752,7 @@ func (hp *horizonPlanning) addDistinct() error {
 		var inner sqlparser.Expr
 		if !sExpr.Col.As.IsEmpty() {
 			inner = sqlparser.NewColName(sExpr.Col.As.String())
+			ctx.semTable.CopyDependencies(sExpr.Col.Expr, inner)
 		} else {
 			inner = sExpr.Col.Expr
 		}
@@ -635,11 +761,11 @@ func (hp *horizonPlanning) addDistinct() error {
 			WeightStrExpr: sExpr.Col.Expr},
 		)
 	}
-	innerPlan, err := hp.planOrderBy(orderExprs, hp.plan)
+	innerPlan, err := hp.planOrderBy(ctx, orderExprs, plan)
 	if err != nil {
-		return err
+		return nil, err
 	}
-	hp.plan = &orderedAggregate{
+	oa := &orderedAggregate{
 		resultsBuilder: resultsBuilder{
 			logicalPlanCommon: newBuilderCommon(innerPlan),
 			weightStrings:     make(map[*resultColumn]int),
@@ -647,7 +773,7 @@ func (hp *horizonPlanning) addDistinct() error {
 		},
 		eaggr: eaggr,
 	}
-	return nil
+	return oa, nil
 }
 
 func isAmbiguousOrderBy(index int, col sqlparser.ColIdent, exprs []abstract.SelectExpr) bool {
@@ -683,7 +809,7 @@ func selectHasUniqueVindex(vschema ContextVSchema, semTable *semantics.SemTable,
 // needDistinctHandling returns true if oa needs to handle the distinct clause.
 // If true, it will also return the aliased expression that needs to be pushed
 // down into the underlying route.
-func (hp *horizonPlanning) needDistinctHandling(funcExpr *sqlparser.FuncExpr, opcode engine.AggregateOpcode, input logicalPlan) (bool, *sqlparser.AliasedExpr, error) {
+func (hp *horizonPlanning) needDistinctHandling(ctx planningContext, funcExpr *sqlparser.FuncExpr, opcode engine.AggregateOpcode, input logicalPlan) (bool, *sqlparser.AliasedExpr, error) {
 	if !funcExpr.Distinct {
 		return false, nil, nil
 	}
@@ -699,7 +825,7 @@ func (hp *horizonPlanning) needDistinctHandling(funcExpr *sqlparser.FuncExpr, op
 		// Unreachable
 		return true, innerAliased, nil
 	}
-	if exprHasUniqueVindex(hp.vschema, hp.semTable, innerAliased.Expr) {
+	if exprHasUniqueVindex(ctx.vschema, ctx.semTable, innerAliased.Expr) {
 		// if we can see a unique vindex on this table/column,
 		// we know the results will be unique, and we don't need to DISTINCTify them
 		return false, nil, nil
diff --git a/go/vt/vtgate/planbuilder/insert.go b/go/vt/vtgate/planbuilder/insert.go
index fd3144f175..72465a11b5 100644
--- a/go/vt/vtgate/planbuilder/insert.go
+++ b/go/vt/vtgate/planbuilder/insert.go
@@ -146,7 +146,7 @@ func buildInsertShardedPlan(ins *sqlparser.Insert, table *vindexes.Table) (engin
 	case sqlparser.Values:
 		rows = insertValues
 		if hasSubquery(rows) {
-			return nil, errors.New("unsupported: subquery in insert values")
+			return nil, errors.New("unsupported: simpleProjection in insert values")
 		}
 	default:
 		return nil, fmt.Errorf("BUG: unexpected construct in insert: %T", insertValues)
diff --git a/go/vt/vtgate/planbuilder/ordering.go b/go/vt/vtgate/planbuilder/ordering.go
index 4c7bb8f957..596fb5e5dc 100644
--- a/go/vt/vtgate/planbuilder/ordering.go
+++ b/go/vt/vtgate/planbuilder/ordering.go
@@ -24,7 +24,7 @@ import (
 
 func planOrdering(pb *primitiveBuilder, input logicalPlan, orderBy sqlparser.OrderBy) (logicalPlan, error) {
 	switch node := input.(type) {
-	case *subquery, *vindexFunc:
+	case *simpleProjection, *vindexFunc:
 		if len(orderBy) == 0 {
 			return node, nil
 		}
diff --git a/go/vt/vtgate/planbuilder/project.go b/go/vt/vtgate/planbuilder/project.go
index 669118346e..2c1188fa6f 100644
--- a/go/vt/vtgate/planbuilder/project.go
+++ b/go/vt/vtgate/planbuilder/project.go
@@ -132,7 +132,7 @@ func planProjection(pb *primitiveBuilder, in logicalPlan, expr *sqlparser.Aliase
 			return nil, nil, 0, err
 		}
 		return node, rc, idx, nil
-	case *subquery:
+	case *simpleProjection:
 		col, ok := expr.Expr.(*sqlparser.ColName)
 		if !ok {
 			return nil, nil, 0, errors.New("unsupported: expression on results of a cross-shard subquery")
@@ -140,7 +140,7 @@ func planProjection(pb *primitiveBuilder, in logicalPlan, expr *sqlparser.Aliase
 
 		// colNumber should already be set for subquery columns.
 		inner := col.Metadata.(*column).colNumber
-		node.esubquery.Cols = append(node.esubquery.Cols, inner)
+		node.eSimpleProj.Cols = append(node.eSimpleProj.Cols, inner)
 
 		// Build a new column reference to represent the result column.
 		rc := newResultColumn(expr, node)
diff --git a/go/vt/vtgate/planbuilder/querytree_transformers.go b/go/vt/vtgate/planbuilder/querytree_transformers.go
index 1b1d37af22..c11ceaa9c4 100644
--- a/go/vt/vtgate/planbuilder/querytree_transformers.go
+++ b/go/vt/vtgate/planbuilder/querytree_transformers.go
@@ -29,66 +29,72 @@ import (
 	"vitess.io/vitess/go/vt/vterrors"
 )
 
-func transformToLogicalPlan(tree queryTree, semTable *semantics.SemTable, processing *postProcessor) (logicalPlan, error) {
+func transformToLogicalPlan(ctx planningContext, tree queryTree, semTable *semantics.SemTable) (logicalPlan, error) {
 	switch n := tree.(type) {
 	case *routeTree:
-		return transformRoutePlan(n, semTable, processing.sqToReplace)
+		return transformRoutePlan(n, semTable, ctx.sqToReplace)
 
 	case *joinTree:
-		return transformJoinPlan(n, semTable, processing)
+		return transformJoinPlan(ctx, n, semTable)
 
 	case *derivedTree:
-		// transforming the inner part of the derived table into a logical plan
-		// so that we can do horizon planning on the inner. If the logical plan
-		// we've produced is a Route, we set its Select.From field to be an aliased
-		// expression containing our derived table's inner select and the derived
-		// table's alias.
+		return transformDerivedPlan(ctx, n, semTable)
+	case *subqueryTree:
+		return transformSubqueryTree(ctx, n, semTable)
+	}
 
-		plan, err := transformToLogicalPlan(n.inner, semTable, processing)
-		if err != nil {
-			return nil, err
-		}
-		processing.inDerived = true
-		plan, err = processing.planHorizon(plan, n.query, nil)
-		if err != nil {
-			return nil, err
-		}
-		processing.inDerived = false
+	return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "[BUG] unknown query tree encountered: %T", tree)
+}
 
-		rb, isRoute := plan.(*route)
-		if !isRoute {
-			return plan, nil
-		}
-		innerSelect := rb.Select
-		derivedTable := &sqlparser.DerivedTable{Select: innerSelect}
-		tblExpr := &sqlparser.AliasedTableExpr{
-			Expr: derivedTable,
-			As:   sqlparser.NewTableIdent(n.alias),
-		}
-		rb.Select = &sqlparser.Select{
-			From: []sqlparser.TableExpr{tblExpr},
-		}
-		return plan, nil
-	case *subqueryTree:
-		innerPlan, err := transformToLogicalPlan(n.inner, semTable, processing)
-		if err != nil {
-			return nil, err
-		}
-		innerPlan, err = processing.planHorizon(innerPlan, n.subquery, nil)
-		if err != nil {
-			return nil, err
-		}
+func transformSubqueryTree(ctx planningContext, n *subqueryTree, semTable *semantics.SemTable) (logicalPlan, error) {
+	innerPlan, err := transformToLogicalPlan(ctx, n.inner, semTable)
+	if err != nil {
+		return nil, err
+	}
+	innerPlan, err = planHorizon(ctx, innerPlan, n.subquery)
+	if err != nil {
+		return nil, err
+	}
 
-		plan := newPulloutSubquery(n.opcode, n.argName, "", innerPlan)
-		outerPlan, err := transformToLogicalPlan(n.outer, semTable, processing)
-		if err != nil {
-			return nil, err
-		}
-		plan.underlying = outerPlan
-		return plan, err
+	plan := newPulloutSubquery(n.opcode, n.argName, "", innerPlan)
+	outerPlan, err := transformToLogicalPlan(ctx, n.outer, semTable)
+	if err != nil {
+		return nil, err
 	}
+	plan.underlying = outerPlan
+	return plan, err
+}
 
-	return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "[BUG] unknown query tree encountered: %T", tree)
+func transformDerivedPlan(ctx planningContext, n *derivedTree, semTable *semantics.SemTable) (logicalPlan, error) {
+	// transforming the inner part of the derived table into a logical plan
+	// so that we can do horizon planning on the inner. If the logical plan
+	// we've produced is a Route, we set its Select.From field to be an aliased
+	// expression containing our derived table's inner select and the derived
+	// table's alias.
+
+	plan, err := transformToLogicalPlan(ctx, n.inner, semTable)
+	if err != nil {
+		return nil, err
+	}
+	plan, err = planHorizon(ctx, plan, n.query)
+	if err != nil {
+		return nil, err
+	}
+
+	rb, isRoute := plan.(*route)
+	if !isRoute {
+		return plan, nil
+	}
+	innerSelect := rb.Select
+	derivedTable := &sqlparser.DerivedTable{Select: innerSelect}
+	tblExpr := &sqlparser.AliasedTableExpr{
+		Expr: derivedTable,
+		As:   sqlparser.NewTableIdent(n.alias),
+	}
+	rb.Select = &sqlparser.Select{
+		From: []sqlparser.TableExpr{tblExpr},
+	}
+	return plan, nil
 }
 
 func transformRoutePlan(n *routeTree, semTable *semantics.SemTable, sqToReplace map[string]*sqlparser.Select) (*route, error) {
@@ -194,12 +200,12 @@ func transformRoutePlan(n *routeTree, semTable *semantics.SemTable, sqToReplace
 	}, nil
 }
 
-func transformJoinPlan(n *joinTree, semTable *semantics.SemTable, processing *postProcessor) (logicalPlan, error) {
-	lhs, err := transformToLogicalPlan(n.lhs, semTable, processing)
+func transformJoinPlan(ctx planningContext, n *joinTree, semTable *semantics.SemTable) (logicalPlan, error) {
+	lhs, err := transformToLogicalPlan(ctx, n.lhs, semTable)
 	if err != nil {
 		return nil, err
 	}
-	rhs, err := transformToLogicalPlan(n.rhs, semTable, processing)
+	rhs, err := transformToLogicalPlan(ctx, n.rhs, semTable)
 	if err != nil {
 		return nil, err
 	}
diff --git a/go/vt/vtgate/planbuilder/rewrite.go b/go/vt/vtgate/planbuilder/rewrite.go
index 9e1687c5e8..8ae13429c6 100644
--- a/go/vt/vtgate/planbuilder/rewrite.go
+++ b/go/vt/vtgate/planbuilder/rewrite.go
@@ -109,13 +109,13 @@ func expandTableColumns(tables []semantics.TableInfo, starExpr *sqlparser.StarEx
 	return starExpanded, colNames, nil
 }
 
-func subqueryRewrite(statement sqlparser.SelectStatement, semTable *semantics.SemTable, reservedVars *sqlparser.ReservedVars) error {
-	if len(semTable.SubqueryMap) == 0 {
+func subqueryRewrite(ctx planningContext, statement sqlparser.SelectStatement) error {
+	if len(ctx.semTable.SubqueryMap) == 0 {
 		return nil
 	}
 	r := rewriter{
-		semTable:     semTable,
-		reservedVars: reservedVars,
+		semTable:     ctx.semTable,
+		reservedVars: ctx.reservedVars,
 	}
 	sqlparser.Rewrite(statement, r.subqueryRewrite, nil)
 	return nil
diff --git a/go/vt/vtgate/planbuilder/route_planning.go b/go/vt/vtgate/planbuilder/route_planning.go
index f3a9971f2c..db325a2a85 100644
--- a/go/vt/vtgate/planbuilder/route_planning.go
+++ b/go/vt/vtgate/planbuilder/route_planning.go
@@ -33,144 +33,7 @@ import (
 	"vitess.io/vitess/go/vt/vtgate/engine"
 )
 
-var _ selectPlanner = gen4Planner
-
-func gen4Planner(_ string) func(sqlparser.Statement, *sqlparser.ReservedVars, ContextVSchema) (engine.Primitive, error) {
-	return func(stmt sqlparser.Statement, reservedVars *sqlparser.ReservedVars, vschema ContextVSchema) (engine.Primitive, error) {
-		sel, ok := stmt.(*sqlparser.Select)
-		if !ok {
-			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "%T not yet supported", stmt)
-		}
-
-		// handle dual table for processing at vtgate.
-		p, err := handleDualSelects(sel, vschema)
-		if err != nil {
-			return nil, err
-		}
-		if p != nil {
-			return p, nil
-		}
-
-		getPlan := func(sel *sqlparser.Select) (logicalPlan, error) {
-			return newBuildSelectPlan(sel, reservedVars, vschema)
-		}
-
-		plan, err := getPlan(sel)
-		if err != nil {
-			return nil, err
-		}
-
-		if shouldRetryWithCNFRewriting(plan) {
-			// by transforming the predicates to CNF, the planner will sometimes find better plans
-			primitive := rewriteToCNFAndReplan(stmt, getPlan)
-			if primitive != nil {
-				return primitive, nil
-			}
-		}
-		return plan.Primitive(), nil
-	}
-}
-
-type postProcessor struct {
-	inDerived   bool
-	semTable    *semantics.SemTable
-	vschema     ContextVSchema
-	sqToReplace map[string]*sqlparser.Select
-}
-
-func (pp *postProcessor) planHorizon(plan logicalPlan, sel *sqlparser.Select, sqToReplace map[string]*sqlparser.Select) (logicalPlan, error) {
-	hp := horizonPlanning{
-		sel:       sel,
-		plan:      plan,
-		semTable:  pp.semTable,
-		vschema:   pp.vschema,
-		inDerived: pp.inDerived,
-	}
-
-	replaceSubQuery(sqToReplace, hp.sel)
-
-	plan, err := hp.planHorizon()
-	if err != nil {
-		return nil, err
-	}
-
-	plan, err = planLimit(sel.Limit, plan)
-	if err != nil {
-		return nil, err
-	}
-	return plan, nil
-
-}
-
-func newBuildSelectPlan(sel *sqlparser.Select, reservedVars *sqlparser.ReservedVars, vschema ContextVSchema) (logicalPlan, error) {
-	ksName := ""
-	if ks, _ := vschema.DefaultKeyspace(); ks != nil {
-		ksName = ks.Name
-	}
-	semTable, err := semantics.Analyze(sel, ksName, vschema, starRewrite)
-	if err != nil {
-		return nil, err
-	}
-
-	err = subqueryRewrite(sel, semTable, reservedVars)
-	if err != nil {
-		return nil, err
-	}
-
-	opTree, err := abstract.CreateOperatorFromSelect(sel, semTable)
-	if err != nil {
-		return nil, err
-	}
-
-	ctx := optimizeContext{
-		reservedVars: reservedVars,
-		semTable:     semTable,
-		vschema:      vschema,
-		sqToReplace:  map[string]*sqlparser.Select{},
-	}
-	tree, err := optimizeQuery(ctx, opTree)
-	if err != nil {
-		return nil, err
-	}
-
-	postProcessing := &postProcessor{
-		semTable:    semTable,
-		vschema:     vschema,
-		sqToReplace: ctx.sqToReplace,
-	}
-	plan, err := transformToLogicalPlan(tree, semTable, postProcessing)
-	if err != nil {
-		return nil, err
-	}
-
-	plan, err = postProcessing.planHorizon(plan, sel, postProcessing.sqToReplace)
-	if err != nil {
-		return nil, err
-	}
-
-	if err := setMiscFunc(plan, sel); err != nil {
-		return nil, err
-	}
-
-	if err := plan.WireupGen4(semTable); err != nil {
-		return nil, err
-	}
-
-	directives := sqlparser.ExtractCommentDirectives(sel.Comments)
-	if directives.IsSet(sqlparser.DirectiveScatterErrorsAsWarnings) {
-		visit(plan, func(logicalPlan logicalPlan) (bool, logicalPlan, error) {
-			switch plan := logicalPlan.(type) {
-			case *route:
-				plan.eroute.ScatterErrorsAsWarnings = true
-			}
-			return true, logicalPlan, nil
-		})
-	}
-
-	return plan, nil
-}
-
-type optimizeContext struct {
+type planningContext struct {
 	reservedVars *sqlparser.ReservedVars
 	semTable     *semantics.SemTable
 	vschema      ContextVSchema
@@ -178,12 +41,12 @@ type optimizeContext struct {
 	sqToReplace map[string]*sqlparser.Select
 }
 
-func (c optimizeContext) isSubQueryToReplace(name string) bool {
+func (c planningContext) isSubQueryToReplace(name string) bool {
 	_, found := c.sqToReplace[name]
 	return found
 }
 
-func optimizeQuery(ctx optimizeContext, opTree abstract.Operator) (queryTree, error) {
+func optimizeQuery(ctx planningContext, opTree abstract.Operator) (queryTree, error) {
 	switch op := opTree.(type) {
 	case *abstract.QueryGraph:
 		switch {
@@ -229,7 +92,7 @@ func optimizeQuery(ctx optimizeContext, opTree abstract.Operator) (queryTree, er
 	}
 }
 
-func optimizeSubQuery(ctx optimizeContext, op *abstract.SubQuery) (queryTree, error) {
+func optimizeSubQuery(ctx planningContext, op *abstract.SubQuery) (queryTree, error) {
 	outerTree, err := optimizeQuery(ctx, op.Outer)
 	if err != nil {
 		return nil, err
@@ -286,7 +149,7 @@ func optimizeSubQuery(ctx optimizeContext, op *abstract.SubQuery) (queryTree, er
 	return outerTree, nil
 }
 
-func mergeSubQuery(ctx optimizeContext, outer *routeTree, subq *abstract.SubQueryInner) (*routeTree, error) {
+func mergeSubQuery(ctx planningContext, outer *routeTree, subq *abstract.SubQueryInner) (*routeTree, error) {
 	ctx.sqToReplace[subq.ArgName] = subq.SelectStatement
 	err := outer.resetRoutingSelections(ctx)
 	if err != nil {
@@ -295,133 +158,6 @@ func mergeSubQuery(ctx optimizeContext, outer *routeTree, subq *abstract.SubQuer
 	return outer, nil
 }
 
-func planLimit(limit *sqlparser.Limit, plan logicalPlan) (logicalPlan, error) {
-	if limit == nil {
-		return plan, nil
-	}
-	rb, ok := plan.(*route)
-	if ok && rb.isSingleShard() {
-		rb.SetLimit(limit)
-		return plan, nil
-	}
-
-	lPlan, err := createLimit(plan, limit)
-	if err != nil {
-		return nil, err
-	}
-
-	// visit does not modify the plan.
-	_, err = visit(lPlan, setUpperLimit)
-	if err != nil {
-		return nil, err
-	}
-	return lPlan, nil
-}
-
-type horizonPlanning struct {
-	sel             *sqlparser.Select
-	plan            logicalPlan
-	semTable        *semantics.SemTable
-	vschema         ContextVSchema
-	qp              *abstract.QueryProjection
-	inDerived       bool
-	needsTruncation bool
-	vtgateGrouping  bool
-}
-
-func (hp *horizonPlanning) planHorizon() (logicalPlan, error) {
-	rb, ok := hp.plan.(*route)
-	if !ok && hp.semTable.ProjectionErr != nil {
-		return nil, hp.semTable.ProjectionErr
-	}
-
-	if hp.inDerived {
-		for _, expr := range hp.sel.SelectExprs {
-			if sqlparser.ContainsAggregation(expr) {
-				return nil, semantics.Gen4NotSupportedF("aggregation inside of derived table")
-			}
-		}
-	}
-
-	if ok && rb.isSingleShard() {
-		createSingleShardRoutePlan(hp.sel, rb)
-		return hp.plan, nil
-	}
-
-	qp2, err := abstract.CreateQPFromSelect(hp.sel)
-	if err != nil {
-		return nil, err
-	}
-
-	hp.qp = qp2
-
-	if err := checkUnsupportedConstructs(hp.sel); err != nil {
-		return nil, err
-	}
-
-	if hp.qp.NeedsAggregation() {
-		err = hp.planAggregations()
-		if err != nil {
-			return nil, err
-		}
-	} else {
-		for _, e := range hp.qp.SelectExprs {
-			if _, _, err := pushProjection(e.Col, hp.plan, hp.semTable, true, false); err != nil {
-				return nil, err
-			}
-		}
-	}
-
-	if len(hp.qp.OrderExprs) > 0 {
-		hp.plan, err = hp.planOrderBy(hp.qp.OrderExprs, hp.plan)
-		if err != nil {
-			return nil, err
-		}
-	}
-
-	if hp.qp.CanPushDownSorting && hp.vtgateGrouping {
-		hp.plan, err = hp.planOrderByUsingGroupBy()
-		if err != nil {
-			return nil, err
-		}
-	}
-
-	err = hp.planDistinct()
-	if err != nil {
-		return nil, err
-	}
-
-	err = hp.truncateColumnsIfNeeded(hp.plan)
-	if err != nil {
-		return nil, err
-	}
-
-	return hp.plan, nil
-}
-
-func (hp *horizonPlanning) truncateColumnsIfNeeded(plan logicalPlan) error {
-	if !hp.needsTruncation {
-		return nil
-	}
-
-	switch p := plan.(type) {
-	case *route:
-		p.eroute.SetTruncateColumnCount(hp.sel.GetColumnCount())
-	case *joinGen4:
-		// since this is a join, we can safely add extra columns and not need to truncate them
-	case *orderedAggregate:
-		p.eaggr.SetTruncateColumnCount(hp.sel.GetColumnCount())
-	case *memorySort:
-		p.truncater.SetTruncateColumnCount(hp.sel.GetColumnCount())
-	case *pulloutSubquery:
-		return hp.truncateColumnsIfNeeded(p.underlying)
-	default:
-		return vterrors.Errorf(vtrpcpb.Code_INTERNAL, "plan type not known for column truncation: %T", hp.plan)
-	}
-
-	return nil
-}
-
 func exprHasUniqueVindex(vschema ContextVSchema, semTable *semantics.SemTable, expr sqlparser.Expr) bool {
 	col, isCol := expr.(*sqlparser.ColName)
 	if !isCol {
@@ -465,14 +201,7 @@ func createSingleShardRoutePlan(sel *sqlparser.Select, rb *route) {
 	}
 }
 
-func checkUnsupportedConstructs(sel *sqlparser.Select) error {
-	if sel.Having != nil {
-		return semantics.Gen4NotSupportedF("HAVING")
-	}
-	return nil
-}
-
-func pushJoinPredicate(ctx optimizeContext, exprs []sqlparser.Expr, tree queryTree) (queryTree, error) {
+func pushJoinPredicate(ctx planningContext, exprs []sqlparser.Expr, tree queryTree) (queryTree, error) {
 	switch node := tree.(type) {
 	case *routeTree:
 		plan := node.clone().(*routeTree)
@@ -575,11 +304,11 @@ func breakPredicateInLHSandRHS(expr sqlparser.Expr, semTable *semantics.SemTable
 	return
 }
 
-func mergeOrJoinInner(ctx optimizeContext, lhs, rhs queryTree, joinPredicates []sqlparser.Expr) (queryTree, error) {
+func mergeOrJoinInner(ctx planningContext, lhs, rhs queryTree, joinPredicates []sqlparser.Expr) (queryTree, error) {
 	return mergeOrJoin(ctx, lhs, rhs, joinPredicates, true)
 }
 
-func mergeOrJoin(ctx optimizeContext, lhs, rhs queryTree, joinPredicates []sqlparser.Expr, inner bool) (queryTree, error) {
+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 {
@@ -611,7 +340,7 @@ type (
 	and removes the two inputs to this cheapest plan and instead adds the join.
 	As an optimization, it first only considers joining tables that have predicates defined between them
 */
-func greedySolve(ctx optimizeContext, qg *abstract.QueryGraph) (queryTree, error) {
+func greedySolve(ctx planningContext, qg *abstract.QueryGraph) (queryTree, error) {
 	joinTrees, err := seedPlanList(ctx, qg)
 	planCache := cacheMap{}
 	if err != nil {
@@ -625,7 +354,7 @@ func greedySolve(ctx optimizeContext, qg *abstract.QueryGraph) (queryTree, error
 	return tree, nil
 }
 
-func mergeJoinTrees(ctx optimizeContext, qg *abstract.QueryGraph, joinTrees []queryTree, planCache cacheMap, crossJoinsOK bool) (queryTree, error) {
+func mergeJoinTrees(ctx planningContext, qg *abstract.QueryGraph, joinTrees []queryTree, planCache cacheMap, crossJoinsOK bool) (queryTree, error) {
 	if len(joinTrees) == 0 {
 		return nil, nil
 	}
@@ -655,7 +384,7 @@ func mergeJoinTrees(ctx optimizeContext, qg *abstract.QueryGraph, joinTrees []qu
 	return joinTrees[0], nil
 }
 
-func (cm cacheMap) getJoinTreeFor(ctx optimizeContext, lhs, rhs queryTree, joinPredicates []sqlparser.Expr) (queryTree, error) {
+func (cm cacheMap) getJoinTreeFor(ctx planningContext, lhs, rhs queryTree, joinPredicates []sqlparser.Expr) (queryTree, error) {
 	solves := tableSetPair{left: lhs.tableID(), right: rhs.tableID()}
 	cachedPlan := cm[solves]
 	if cachedPlan != nil {
@@ -671,7 +400,7 @@ func (cm cacheMap) getJoinTreeFor(ctx optimizeContext, lhs, rhs queryTree, joinP
 }
 
 func findBestJoinTree(
-	ctx optimizeContext,
+	ctx planningContext,
 	qg *abstract.QueryGraph,
 	plans []queryTree,
 	planCache cacheMap,
@@ -704,7 +433,7 @@ func findBestJoinTree(
 	return bestPlan, lIdx, rIdx, nil
 }
 
-func leftToRightSolve(ctx optimizeContext, qg *abstract.QueryGraph) (queryTree, error) {
+func leftToRightSolve(ctx planningContext, qg *abstract.QueryGraph) (queryTree, error) {
 	plans, err := seedPlanList(ctx, qg)
 	if err != nil {
 		return nil, err
@@ -727,7 +456,7 @@ func leftToRightSolve(ctx optimizeContext, qg *abstract.QueryGraph) (queryTree,
 }
 
 // seedPlanList returns a routeTree for each table in the qg
-func seedPlanList(ctx optimizeContext, qg *abstract.QueryGraph) ([]queryTree, error) {
+func seedPlanList(ctx planningContext, qg *abstract.QueryGraph) ([]queryTree, error) {
 	plans := make([]queryTree, len(qg.Tables))
 
 	// we start by seeding the table with the single routes
@@ -749,7 +478,7 @@ func removeAt(plans []queryTree, idx int) []queryTree {
 	return append(plans[:idx], plans[idx+1:]...)
 }
 
-func createRoutePlan(ctx optimizeContext, table *abstract.QueryTable, solves semantics.TableSet) (*routeTree, error) {
+func createRoutePlan(ctx planningContext, table *abstract.QueryTable, solves semantics.TableSet) (*routeTree, error) {
 	if table.IsInfSchema {
 		ks, err := ctx.vschema.AnyKeyspace()
 		if err != nil {
@@ -831,7 +560,7 @@ func createRoutePlan(ctx optimizeContext, table *abstract.QueryTable, solves sem
 	return plan, nil
 }
 
-func findColumnVindex(ctx optimizeContext, a *routeTree, exp sqlparser.Expr) vindexes.SingleColumn {
+func findColumnVindex(ctx planningContext, a *routeTree, exp sqlparser.Expr) vindexes.SingleColumn {
 	_, isCol := exp.(*sqlparser.ColName)
 	if !isCol {
 		return nil
@@ -873,7 +602,7 @@ func findColumnVindex(ctx optimizeContext, a *routeTree, exp sqlparser.Expr) vin
 	return singCol
 }
 
-func canMergeOnFilter(ctx optimizeContext, a, b *routeTree, predicate sqlparser.Expr) bool {
+func canMergeOnFilter(ctx planningContext, a, b *routeTree, predicate sqlparser.Expr) bool {
 	comparison, ok := predicate.(*sqlparser.ComparisonExpr)
 	if !ok {
 		return false
@@ -899,7 +628,7 @@ func canMergeOnFilter(ctx optimizeContext, a, b *routeTree, predicate sqlparser.
 	return rVindex == lVindex
 }
 
-func canMergeOnFilters(ctx optimizeContext, a, b *routeTree, joinPredicates []sqlparser.Expr) bool {
+func canMergeOnFilters(ctx planningContext, a, b *routeTree, joinPredicates []sqlparser.Expr) bool {
 	for _, predicate := range joinPredicates {
 		for _, expr := range sqlparser.SplitAndExpression(nil, predicate) {
 			if canMergeOnFilter(ctx, a, b, expr) {
@@ -912,7 +641,7 @@ func canMergeOnFilters(ctx optimizeContext, a, b *routeTree, joinPredicates []sq
 
 type mergeFunc func(a, b *routeTree) *routeTree
 
-func tryMerge(ctx optimizeContext, a, b queryTree, joinPredicates []sqlparser.Expr, merger mergeFunc) (queryTree, error) {
+func tryMerge(ctx planningContext, a, b queryTree, joinPredicates []sqlparser.Expr, merger mergeFunc) (queryTree, error) {
 	aRoute, bRoute := joinTreesToRoutes(a.clone(), b.clone())
 	if aRoute == nil || bRoute == nil {
 		return nil, nil
@@ -1064,7 +793,7 @@ func findTables(deps semantics.TableSet, tables parenTables) (relation, relation
 	return nil, nil, tables
 }
 
-func createRoutePlanForOuter(ctx optimizeContext, aRoute, bRoute *routeTree, newTabletSet semantics.TableSet, joinPredicates []sqlparser.Expr) *routeTree {
+func createRoutePlanForOuter(ctx planningContext, aRoute, bRoute *routeTree, newTabletSet semantics.TableSet, joinPredicates []sqlparser.Expr) *routeTree {
 	// create relation slice with all tables
 	tables := bRoute.tables
 	// we are doing an outer join where the outer part contains multiple tables - we have to turn the outer part into a join or two
@@ -1102,7 +831,7 @@ func createRoutePlanForOuter(ctx optimizeContext, aRoute, bRoute *routeTree, new
 	}
 }
 
-func gen4ValuesEqual(ctx optimizeContext, a, b []sqlparser.Expr) bool {
+func gen4ValuesEqual(ctx planningContext, a, b []sqlparser.Expr) bool {
 	if len(a) != len(b) {
 		return false
 	}
@@ -1118,7 +847,7 @@ func gen4ValuesEqual(ctx optimizeContext, a, b []sqlparser.Expr) bool {
 	return true
 }
 
-func gen4ValEqual(ctx optimizeContext, a, b sqlparser.Expr) bool {
+func gen4ValEqual(ctx planningContext, a, b sqlparser.Expr) bool {
 	switch a := a.(type) {
 	case *sqlparser.ColName:
 		if b, ok := b.(*sqlparser.ColName); ok {
diff --git a/go/vt/vtgate/planbuilder/routetree.go b/go/vt/vtgate/planbuilder/routetree.go
index 39528f972e..22b64c29ba 100644
--- a/go/vt/vtgate/planbuilder/routetree.go
+++ b/go/vt/vtgate/planbuilder/routetree.go
@@ -149,7 +149,7 @@ outer:
 
 // addPredicate adds these predicates added to it. if the predicates can help,
 // they will improve the routeOpCode
-func (rp *routeTree) addPredicate(ctx optimizeContext, predicates ...sqlparser.Expr) error {
+func (rp *routeTree) addPredicate(ctx planningContext, predicates ...sqlparser.Expr) error {
 	if rp.canImprove() {
 		newVindexFound, err := rp.searchForNewVindexes(ctx, predicates)
 		if err != nil {
@@ -173,7 +173,7 @@ func (rp *routeTree) canImprove() bool {
 	return rp.routeOpCode != engine.SelectNone
 }
 
-func (rp *routeTree) searchForNewVindexes(ctx optimizeContext, predicates []sqlparser.Expr) (bool, error) {
+func (rp *routeTree) searchForNewVindexes(ctx planningContext, predicates []sqlparser.Expr) (bool, error) {
 	newVindexFound := false
 	for _, filter := range predicates {
 		switch node := filter.(type) {
@@ -251,7 +251,7 @@ func (rp *routeTree) isImpossibleNotIN(node *sqlparser.ComparisonExpr) bool {
 	return false
 }
 
-func (rp *routeTree) planEqualOp(ctx optimizeContext, node *sqlparser.ComparisonExpr) (bool, error) {
+func (rp *routeTree) planEqualOp(ctx planningContext, node *sqlparser.ComparisonExpr) (bool, error) {
 	column, ok := node.Left.(*sqlparser.ColName)
 	other := node.Right
 	vdValue := other
@@ -271,7 +271,7 @@ func (rp *routeTree) planEqualOp(ctx optimizeContext, node *sqlparser.Comparison
 	return rp.haveMatchingVindex(ctx, node, vdValue, column, *val, equalOrEqualUnique, justTheVindex), err
 }
 
-func (rp *routeTree) planSimpleInOp(ctx optimizeContext, node *sqlparser.ComparisonExpr, left *sqlparser.ColName) (bool, error) {
+func (rp *routeTree) planSimpleInOp(ctx planningContext, node *sqlparser.ComparisonExpr, left *sqlparser.ColName) (bool, error) {
 	vdValue := node.Right
 	value, err := rp.makePlanValue(ctx, vdValue)
 	if err != nil || value == nil {
@@ -288,7 +288,7 @@ func (rp *routeTree) planSimpleInOp(ctx optimizeContext, node *sqlparser.Compari
 	return rp.haveMatchingVindex(ctx, node, vdValue, left, *value, opcode, justTheVindex), err
 }
 
-func (rp *routeTree) planCompositeInOp(ctx optimizeContext, node *sqlparser.ComparisonExpr, left sqlparser.ValTuple) (bool, error) {
+func (rp *routeTree) planCompositeInOp(ctx planningContext, node *sqlparser.ComparisonExpr, left sqlparser.ValTuple) (bool, error) {
 	right, rightIsValTuple := node.Right.(sqlparser.ValTuple)
 	if !rightIsValTuple {
 		return false, nil
@@ -296,7 +296,7 @@ func (rp *routeTree) planCompositeInOp(ctx optimizeContext, node *sqlparser.Comp
 	return rp.planCompositeInOpRecursive(ctx, node, left, right, nil)
 }
 
-func (rp *routeTree) planCompositeInOpRecursive(ctx optimizeContext, node *sqlparser.ComparisonExpr, left, right sqlparser.ValTuple, coordinates []int) (bool, error) {
+func (rp *routeTree) planCompositeInOpRecursive(ctx planningContext, node *sqlparser.ComparisonExpr, left, right sqlparser.ValTuple, coordinates []int) (bool, error) {
 	foundVindex := false
 	cindex := len(coordinates)
 	coordinates = append(coordinates, 0)
@@ -341,7 +341,7 @@ func (rp *routeTree) planCompositeInOpRecursive(ctx optimizeContext, node *sqlpa
 	return foundVindex, nil
 }
 
-func (rp *routeTree) planInOp(ctx optimizeContext, node *sqlparser.ComparisonExpr) (bool, error) {
+func (rp *routeTree) planInOp(ctx planningContext, node *sqlparser.ComparisonExpr) (bool, error) {
 	switch left := node.Left.(type) {
 	case *sqlparser.ColName:
 		return rp.planSimpleInOp(ctx, node, left)
@@ -351,7 +351,7 @@ func (rp *routeTree) planInOp(ctx optimizeContext, node *sqlparser.ComparisonExp
 	return false, nil
 }
 
-func (rp *routeTree) planLikeOp(ctx optimizeContext, node *sqlparser.ComparisonExpr) (bool, error) {
+func (rp *routeTree) planLikeOp(ctx planningContext, node *sqlparser.ComparisonExpr) (bool, error) {
 	column, ok := node.Left.(*sqlparser.ColName)
 	if !ok {
 		return false, nil
@@ -376,7 +376,7 @@ func (rp *routeTree) planLikeOp(ctx optimizeContext, node *sqlparser.ComparisonE
 	return rp.haveMatchingVindex(ctx, node, vdValue, column, *val, selectEqual, vdx), err
 }
 
-func (rp *routeTree) planIsExpr(ctx optimizeContext, node *sqlparser.IsExpr) (bool, error) {
+func (rp *routeTree) planIsExpr(ctx planningContext, node *sqlparser.IsExpr) (bool, error) {
 	// we only handle IS NULL correct. IsExpr can contain other expressions as well
 	if node.Right != sqlparser.IsNullOp {
 		return false, nil
@@ -399,7 +399,7 @@ func (rp *routeTree) planIsExpr(ctx optimizeContext, node *sqlparser.IsExpr) (bo
 // 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.
-func (rp *routeTree) makePlanValue(ctx optimizeContext, n sqlparser.Expr) (*sqltypes.PlanValue, error) {
+func (rp *routeTree) makePlanValue(ctx planningContext, n sqlparser.Expr) (*sqltypes.PlanValue, error) {
 	if ctx.isSubQueryToReplace(argumentName(n)) {
 		return nil, nil
 	}
@@ -429,7 +429,7 @@ func (rp *routeTree) hasVindex(column *sqlparser.ColName) bool {
 }
 
 func (rp *routeTree) haveMatchingVindex(
-	ctx optimizeContext,
+	ctx planningContext,
 	node sqlparser.Expr,
 	valueExpr sqlparser.Expr,
 	column *sqlparser.ColName,
@@ -498,7 +498,7 @@ func (rp *routeTree) Predicates() sqlparser.Expr {
 }
 
 // resetRoutingSelections resets all vindex selections and replans this routeTree
-func (rp *routeTree) resetRoutingSelections(ctx optimizeContext) error {
+func (rp *routeTree) resetRoutingSelections(ctx planningContext) error {
 
 	vschemaTable := rp.tables[0].(*routeTable).vtable
 
diff --git a/go/vt/vtgate/planbuilder/subquery.go b/go/vt/vtgate/planbuilder/simple_projection.go
similarity index 65%
rename from go/vt/vtgate/planbuilder/subquery.go
rename to go/vt/vtgate/planbuilder/simple_projection.go
index d434112caf..9580fe0b57 100644
--- a/go/vt/vtgate/planbuilder/subquery.go
+++ b/go/vt/vtgate/planbuilder/simple_projection.go
@@ -23,31 +23,32 @@ import (
 	"vitess.io/vitess/go/vt/vtgate/engine"
 )
 
-var _ logicalPlan = (*subquery)(nil)
+var _ logicalPlan = (*simpleProjection)(nil)
 
-// subquery is a logicalPlan that wraps a subquery.
-// This primitive wraps any subquery that results
+// simpleProjection is used for wrapping a derived table.
+// This primitive wraps any derived table that results
 // in something that's not a route. It builds a
-// 'table' for the subquery allowing higher level
-// constructs to reference its columns. If a subquery
+// 'table' for the derived table allowing higher level
+// constructs to reference its columns. If a derived table
 // results in a route primitive, we instead build
 // a new route that keeps the subquery in the FROM
 // clause, because a route is more versatile than
-// a subquery.
-type subquery struct {
+// a simpleProjection.
+// this should not be used by the gen4 planner
+type simpleProjection struct {
 	logicalPlanCommon
 	resultColumns []*resultColumn
-	esubquery     *engine.Subquery
+	eSimpleProj   *engine.SimpleProjection
 }
 
-// newSubquery builds a new subquery.
-func newSubquery(alias sqlparser.TableIdent, plan logicalPlan) (*subquery, *symtab, error) {
-	sq := &subquery{
+// newSimpleProjection builds a new simpleProjection.
+func newSimpleProjection(alias sqlparser.TableIdent, plan logicalPlan) (*simpleProjection, *symtab, error) {
+	sq := &simpleProjection{
 		logicalPlanCommon: newBuilderCommon(plan),
-		esubquery:         &engine.Subquery{},
+		eSimpleProj:       &engine.SimpleProjection{},
 	}
 
-	// Create a 'table' that represents the subquery.
+	// Create a 'table' that represents the derived table.
 	t := &table{
 		alias:  sqlparser.TableName{Name: alias},
 		origin: sq,
@@ -68,18 +69,18 @@ func newSubquery(alias sqlparser.TableIdent, plan logicalPlan) (*subquery, *symt
 }
 
 // Primitive implements the logicalPlan interface
-func (sq *subquery) Primitive() engine.Primitive {
-	sq.esubquery.Subquery = sq.input.Primitive()
-	return sq.esubquery
+func (sq *simpleProjection) Primitive() engine.Primitive {
+	sq.eSimpleProj.Input = sq.input.Primitive()
+	return sq.eSimpleProj
 }
 
 // ResultColumns implements the logicalPlan interface
-func (sq *subquery) ResultColumns() []*resultColumn {
+func (sq *simpleProjection) ResultColumns() []*resultColumn {
 	return sq.resultColumns
 }
 
 // SupplyCol implements the logicalPlan interface
-func (sq *subquery) SupplyCol(col *sqlparser.ColName) (rc *resultColumn, colNumber int) {
+func (sq *simpleProjection) SupplyCol(col *sqlparser.ColName) (rc *resultColumn, colNumber int) {
 	c := col.Metadata.(*column)
 	for i, rc := range sq.resultColumns {
 		if rc.column == c {
@@ -89,7 +90,7 @@ func (sq *subquery) SupplyCol(col *sqlparser.ColName) (rc *resultColumn, colNumb
 
 	// columns that reference subqueries will have their colNumber set.
 	// Let's use it here.
-	sq.esubquery.Cols = append(sq.esubquery.Cols, c.colNumber)
+	sq.eSimpleProj.Cols = append(sq.eSimpleProj.Cols, c.colNumber)
 	sq.resultColumns = append(sq.resultColumns, &resultColumn{column: c})
 	return rc, len(sq.resultColumns) - 1
 }
diff --git a/go/vt/vtgate/semantics/semantic_state.go b/go/vt/vtgate/semantics/semantic_state.go
index 951c20f41d..306964cc50 100644
--- a/go/vt/vtgate/semantics/semantic_state.go
+++ b/go/vt/vtgate/semantics/semantic_state.go
@@ -138,6 +138,12 @@ type (
 	}
 )
 
+// CopyDependencies copies the dependencies from one expression into the other
+func (st *SemTable) CopyDependencies(from, to sqlparser.Expr) {
+	st.ExprBaseTableDeps[to] = st.BaseTableDependencies(from)
+	st.ExprDeps[to] = st.Dependencies(from)
+}
+
 // GetExprFor implements the TableInfo interface
 func (v *vTableInfo) GetExprFor(s string) (sqlparser.Expr, error) {
 	for i, colName := range v.columnNames {


Test patch:
diff --git a/go/vt/vtgate/engine/subquery_test.go b/go/vt/vtgate/engine/simple_projection_test.go
similarity index 91%
rename from go/vt/vtgate/engine/subquery_test.go
rename to go/vt/vtgate/engine/simple_projection_test.go
index 60644fa42b..7281870c18 100644
--- a/go/vt/vtgate/engine/subquery_test.go
+++ b/go/vt/vtgate/engine/simple_projection_test.go
@@ -42,9 +42,9 @@ func TestSubqueryExecute(t *testing.T) {
 		},
 	}
 
-	sq := &Subquery{
-		Cols:     []int{0, 2},
-		Subquery: prim,
+	sq := &SimpleProjection{
+		Cols:  []int{0, 2},
+		Input: prim,
 	}
 
 	bv := map[string]*querypb.BindVariable{
@@ -69,7 +69,7 @@ func TestSubqueryExecute(t *testing.T) {
 	))
 
 	// Error case.
-	sq.Subquery = &fakePrimitive{
+	sq.Input = &fakePrimitive{
 		sendErr: errors.New("err"),
 	}
 	_, err = sq.Execute(nil, bv, true)
@@ -91,9 +91,9 @@ func TestSubqueryStreamExecute(t *testing.T) {
 		},
 	}
 
-	sq := &Subquery{
-		Cols:     []int{0, 2},
-		Subquery: prim,
+	sq := &SimpleProjection{
+		Cols:  []int{0, 2},
+		Input: prim,
 	}
 
 	bv := map[string]*querypb.BindVariable{
@@ -118,7 +118,7 @@ func TestSubqueryStreamExecute(t *testing.T) {
 	))
 
 	// Error case.
-	sq.Subquery = &fakePrimitive{
+	sq.Input = &fakePrimitive{
 		sendErr: errors.New("err"),
 	}
 	_, err = wrapStreamExecute(sq, nil, bv, true)
@@ -140,9 +140,9 @@ func TestSubqueryGetFields(t *testing.T) {
 		},
 	}
 
-	sq := &Subquery{
-		Cols:     []int{0, 2},
-		Subquery: prim,
+	sq := &SimpleProjection{
+		Cols:  []int{0, 2},
+		Input: prim,
 	}
 
 	bv := map[string]*querypb.BindVariable{
@@ -165,7 +165,7 @@ func TestSubqueryGetFields(t *testing.T) {
 	))
 
 	// Error case.
-	sq.Subquery = &fakePrimitive{
+	sq.Input = &fakePrimitive{
 		sendErr: errors.New("err"),
 	}
 	_, err = sq.GetFields(nil, bv)
diff --git a/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go b/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
index 01718e2f76..1e3dee330c 100644
--- a/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
+++ b/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
@@ -96,7 +96,7 @@ func TestQP(t *testing.T) {
 			_, err = semantics.Analyze(sel, "", &semantics.FakeSI{}, semantics.NoRewrite)
 			require.NoError(t, err)
 
-			qp, err := CreateQPFromSelect(sel)
+			qp, err := CreateQPFromSelect(sel, semantics.NewSemTable())
 			if tcase.expErr != "" {
 				require.Error(t, err)
 				require.Contains(t, err.Error(), tcase.expErr)
@@ -203,7 +203,7 @@ func TestQPSimplifiedExpr(t *testing.T) {
 			_, err = semantics.Analyze(sel, "", &semantics.FakeSI{}, semantics.NoRewrite)
 			require.NoError(t, err)
 
-			qp, err := CreateQPFromSelect(sel)
+			qp, err := CreateQPFromSelect(sel, semantics.NewSemTable())
 			require.NoError(t, err)
 			require.Equal(t, tc.expected[1:], qp.toString())
 		})
diff --git a/go/vt/vtgate/planbuilder/horizon_planning_test.go b/go/vt/vtgate/planbuilder/horizon_planning_test.go
index 3737634797..7671c7bc3d 100644
--- a/go/vt/vtgate/planbuilder/horizon_planning_test.go
+++ b/go/vt/vtgate/planbuilder/horizon_planning_test.go
@@ -21,6 +21,8 @@ import (
 
 	"github.com/stretchr/testify/assert"
 
+	"vitess.io/vitess/go/vt/vtgate/semantics"
+
 	"vitess.io/vitess/go/vt/sqlparser"
 )
 
@@ -69,8 +71,9 @@ func TestCheckIfAlreadyExists(t *testing.T) {
 		},
 	}
 	for _, tt := range tests {
+		semTable := semantics.NewSemTable()
 		t.Run(tt.name, func(t *testing.T) {
-			got := checkIfAlreadyExists(tt.expr, tt.sel)
+			got := checkIfAlreadyExists(tt.expr, tt.sel, semTable)
 			assert.Equal(t, tt.want, got)
 		})
 	}
diff --git a/go/vt/vtgate/planbuilder/rewrite_test.go b/go/vt/vtgate/planbuilder/rewrite_test.go
index 57320033b3..51ddf399fb 100644
--- a/go/vt/vtgate/planbuilder/rewrite_test.go
+++ b/go/vt/vtgate/planbuilder/rewrite_test.go
@@ -229,7 +229,8 @@ func TestSubqueryRewrite(t *testing.T) {
 			require.True(t, isSelectStatement, "analyzer expects a select statement")
 			semTable, err := semantics.Analyze(selectStatement, "", &semantics.FakeSI{}, semantics.NoRewrite)
 			require.NoError(t, err)
-			err = subqueryRewrite(selectStatement, semTable, reservedVars)
+			ctx := newPlanningContext(reservedVars, semTable, nil)
+			err = subqueryRewrite(ctx, selectStatement)
 			require.NoError(t, err)
 			assert.Equal(t, tcase.output, sqlparser.String(selectStatement))
 		})
diff --git a/go/vt/vtgate/planbuilder/route_planning_test.go b/go/vt/vtgate/planbuilder/route_planning_test.go
index e01883b123..9657cd176f 100644
--- a/go/vt/vtgate/planbuilder/route_planning_test.go
+++ b/go/vt/vtgate/planbuilder/route_planning_test.go
@@ -102,7 +102,7 @@ func TestMergeJoins(t *testing.T) {
 	}}
 	for i, tc := range tests {
 		t.Run(fmt.Sprintf("%d", i), func(t *testing.T) {
-			result, _ := tryMerge(optimizeContext{semTable: semantics.NewSemTable()}, tc.l, tc.r, tc.predicates, nil) // fakeMerger ? how to test this
+			result, _ := tryMerge(planningContext{semTable: semantics.NewSemTable()}, tc.l, tc.r, tc.predicates, nil) // fakeMerger ? how to test this
 			assert.Equal(t, tc.expected, result)
 		})
 	}
@@ -167,7 +167,7 @@ func TestCreateRoutePlanForOuter(t *testing.T) {
 		predicates:  []sqlparser.Expr{equals(col1, col2)},
 	}
 	semTable := semantics.NewSemTable()
-	merge, _ := tryMerge(optimizeContext{semTable: semTable}, a, b, []sqlparser.Expr{}, fakeMerger)
+	merge, _ := tryMerge(planningContext{semTable: semTable}, a, b, []sqlparser.Expr{}, fakeMerger)
 	assert.NotNil(merge)
 }
 
diff --git a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
index 145db7ac5e..33986fb34a 100644
--- a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
@@ -133,7 +133,7 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(0)",
+    "Aggregates": "count(0) AS count(*)",
     "GroupBy": "(1|4), (2|5), (3|6)",
     "ResultColumns": 4,
     "Inputs": [
@@ -179,7 +179,30 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select count(*), intcol from user group by intcol",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(0) AS count(*)",
+    "GroupBy": "1",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select count(*), intcol from `user` where 1 != 1 group by intcol",
+        "OrderBy": "1 ASC",
+        "Query": "select count(*), intcol from `user` group by intcol order by intcol asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter group by a text column, reuse existing weight_string
 "select count(*) k, a, textcol1, b from user group by a, textcol1, b order by k, textcol1"
@@ -229,7 +252,7 @@ Gen4 plan same as above
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
-        "Aggregates": "count(0)",
+        "Aggregates": "count(0) AS k",
         "GroupBy": "(1|4), (2|5), (3|6)",
         "Inputs": [
           {
@@ -274,7 +297,28 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select count(*) from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(0) AS count(*)",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select count(*) from `user` where 1 != 1",
+        "Query": "select count(*) from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # sum aggregate
 "select sum(col) from user"
@@ -300,7 +344,28 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select sum(col) from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "sum(0) AS sum(col)",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select sum(col) from `user` where 1 != 1",
+        "Query": "select sum(col) from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # min aggregate
 "select min(col) from user"
@@ -326,7 +391,28 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select min(col) from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "min(0) AS min(col)",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select min(col) from `user` where 1 != 1",
+        "Query": "select min(col) from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # max aggregate
 "select max(col) from user"
@@ -352,7 +438,28 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select max(col) from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "max(0) AS max(col)",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select max(col) from `user` where 1 != 1",
+        "Query": "select max(col) from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # distinct and group by together for scatter route
 "select distinct col1, col2 from user group by col1"
@@ -490,7 +597,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(1)",
+    "Aggregates": "count(1) AS count(*)",
     "GroupBy": "(0|2)",
     "ResultColumns": 2,
     "Inputs": [
@@ -519,7 +626,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(1)",
+    "Aggregates": "count(1) AS count(*)",
     "GroupBy": "(0|2), (3|4)",
     "ResultColumns": 2,
     "Inputs": [
@@ -572,7 +679,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(1)",
+    "Aggregates": "count(1) AS count(*)",
     "GroupBy": "(0|2)",
     "ResultColumns": 2,
     "Inputs": [
@@ -695,7 +802,7 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
   "QueryType": "SELECT",
   "Original": "select a from (select count(*) as a from user) t",
   "Instructions": {
-    "OperatorType": "Subquery",
+    "OperatorType": "SimpleProjection",
     "Columns": [
       0
     ],
@@ -721,6 +828,36 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select a from (select count(*) as a from user) t",
+  "Instructions": {
+    "OperatorType": "SimpleProjection",
+    "Columns": [
+      0
+    ],
+    "Inputs": [
+      {
+        "OperatorType": "Aggregate",
+        "Variant": "Ordered",
+        "Aggregates": "count(0) AS a",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select count(*) as a from `user` where 1 != 1",
+            "Query": "select count(*) as a from `user`",
+            "Table": "`user`"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # scatter aggregate with non-aggregate expressions.
 "select id, count(*) from user"
@@ -902,7 +1039,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(1)",
+    "Aggregates": "count(1) AS count(distinct id)",
     "GroupBy": "(0|2)",
     "ResultColumns": 2,
     "Inputs": [
@@ -1165,7 +1302,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "min(1)",
+    "Aggregates": "min(1) AS min(distinct col2)",
     "GroupBy": "(0|2)",
     "ResultColumns": 2,
     "Inputs": [
@@ -1290,7 +1427,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(2)",
+    "Aggregates": "count(2) AS count(*)",
     "GroupBy": "(1|3), (0|4)",
     "ResultColumns": 3,
     "Inputs": [
@@ -1343,7 +1480,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(2)",
+    "Aggregates": "count(2) AS count(*)",
     "GroupBy": "(1|3), (0|4)",
     "ResultColumns": 3,
     "Inputs": [
@@ -1396,7 +1533,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(2)",
+    "Aggregates": "count(2) AS count(*)",
     "GroupBy": "(1|3), (0|4)",
     "ResultColumns": 3,
     "Inputs": [
@@ -1496,7 +1633,28 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select count(*) from user order by null",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(0) AS count(*)",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select count(*) from `user` where 1 != 1",
+        "Query": "select count(*) from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter aggregate with complex select list (can't build order by)
 "select distinct a+1 from user"
@@ -1593,7 +1751,7 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(4)",
+    "Aggregates": "count(4) AS count(*)",
     "GroupBy": "(0|5), (1|6), (2|7), (3|8)",
     "ResultColumns": 5,
     "Inputs": [
@@ -1646,7 +1804,7 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(4)",
+    "Aggregates": "count(4) AS count(*)",
     "GroupBy": "(2|5), (1|6), (0|7), (3|8)",
     "ResultColumns": 5,
     "Inputs": [
@@ -1699,7 +1857,7 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(3)",
+    "Aggregates": "count(3) AS count(*)",
     "GroupBy": "(2|4), (1|5), (0|6)",
     "ResultColumns": 4,
     "Inputs": [
@@ -1767,7 +1925,7 @@ Gen4 plan same as above
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
-        "Aggregates": "count(1)",
+        "Aggregates": "count(1) AS count(*)",
         "GroupBy": "(0|2)",
         "ResultColumns": 2,
         "Inputs": [
@@ -1928,7 +2086,7 @@ Gen4 error: In aggregated query without GROUP BY, expression of SELECT list cont
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
-        "Aggregates": "count(1)",
+        "Aggregates": "count(1) AS count(*)",
         "GroupBy": "(0|2)",
         "Inputs": [
           {
@@ -2050,7 +2208,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(0)",
+    "Aggregates": "count(0) AS count(*)",
     "Inputs": [
       {
         "OperatorType": "Route",
@@ -2076,7 +2234,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(0)",
+    "Aggregates": "count(0) AS count(*)",
     "Inputs": [
       {
         "OperatorType": "Join",
@@ -2168,7 +2326,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(1)",
+    "Aggregates": "count(1) AS count(*)",
     "GroupBy": "(0|2)",
     "ResultColumns": 2,
     "Inputs": [
@@ -2216,7 +2374,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(1)",
+    "Aggregates": "count(1) AS count(user_extra.a)",
     "GroupBy": "(0|2)",
     "ResultColumns": 2,
     "Inputs": [
@@ -2327,7 +2485,7 @@ Gen4 plan same as above
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
-        "Aggregates": "count(1)",
+        "Aggregates": "count(1) AS k",
         "GroupBy": "(0|2)",
         "Inputs": [
           {
@@ -2381,7 +2539,7 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(1)",
+    "Aggregates": "count(1) AS k",
     "GroupBy": "(0|2)",
     "ResultColumns": 2,
     "Inputs": [
@@ -2400,4 +2558,3 @@ Gen4 plan same as above
     ]
   }
 }
-
diff --git a/go/vt/vtgate/planbuilder/testdata/from_cases.txt b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
index a7ef1afc56..39f0acd766 100644
--- a/go/vt/vtgate/planbuilder/testdata/from_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
@@ -2204,7 +2204,7 @@ Gen4 plan same as above
     "TableName": "`user`_user_extra_unsharded",
     "Inputs": [
       {
-        "OperatorType": "Subquery",
+        "OperatorType": "SimpleProjection",
         "Columns": [
           1,
           0
@@ -2256,6 +2256,63 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select t.col1 from (select user.id, user.col1 from user join user_extra) as t join unsharded on unsharded.col1 = t.col1 and unsharded.id = t.id",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-2",
+    "JoinVars": {
+      "t_col1": 0,
+      "t_id": 1
+    },
+    "TableName": "`user`_user_extra_unsharded",
+    "Inputs": [
+      {
+        "OperatorType": "Join",
+        "Variant": "Join",
+        "JoinColumnIndexes": "-1,-2",
+        "TableName": "`user`_user_extra",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select `user`.id, `user`.col1 from `user` where 1 != 1",
+            "Query": "select `user`.id, `user`.col1 from `user`",
+            "Table": "`user`"
+          },
+          {
+            "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": "SelectUnsharded",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select 1 from unsharded where 1 != 1",
+        "Query": "select 1 from unsharded where unsharded.col1 = :t_col1 and unsharded.id = :t_id",
+        "Table": "unsharded"
+      }
+    ]
+  }
+}
 
 # wire-up on within cross-shard derived table
 "select t.id from (select user.id, user.col1 from user join user_extra on user_extra.col = user.col) as t"
@@ -2263,7 +2320,7 @@ Gen4 plan same as above
   "QueryType": "SELECT",
   "Original": "select t.id from (select user.id, user.col1 from user join user_extra on user_extra.col = user.col) as t",
   "Instructions": {
-    "OperatorType": "Subquery",
+    "OperatorType": "SimpleProjection",
     "Columns": [
       0
     ],
@@ -2328,7 +2385,7 @@ Gen4 plan same as above
         "Table": "unsharded_a"
       },
       {
-        "OperatorType": "Subquery",
+        "OperatorType": "SimpleProjection",
         "Columns": [
           1
         ],
@@ -3125,7 +3182,7 @@ Gen4 plan same as above
   "QueryType": "SELECT",
   "Original": "select id, t.id from (select user.id from user join user_extra) as t",
   "Instructions": {
-    "OperatorType": "Subquery",
+    "OperatorType": "SimpleProjection",
     "Columns": [
       0,
       0
diff --git a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
index f4b2fd0fde..8f87adbfae 100644
--- a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
@@ -240,7 +240,7 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
-        "Aggregates": "count(1)",
+        "Aggregates": "count(1) AS k",
         "GroupBy": "(2|3)",
         "Inputs": [
           {
@@ -273,7 +273,7 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
     "ResultColumns": 1,
     "Inputs": [
       {
-        "OperatorType": "Subquery",
+        "OperatorType": "SimpleProjection",
         "Columns": [
           0
         ],
diff --git a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
index ca73d75a1a..340f7f63d0 100644
--- a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
@@ -1702,7 +1702,7 @@ Gen4 error: In aggregated query without GROUP BY, expression of SELECT list cont
   "Instructions": {
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
-    "Aggregates": "count(0)",
+    "Aggregates": "count(0) AS count(id)",
     "GroupBy": "(1|2)",
     "ResultColumns": 2,
     "Inputs": [
@@ -1768,7 +1768,7 @@ Gen4 error: In aggregated query without GROUP BY, expression of SELECT list cont
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
-        "Aggregates": "count(0)",
+        "Aggregates": "count(0) AS count(id)",
         "GroupBy": "(1|2)",
         "Inputs": [
           {
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases.txt b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
index a493d0cc96..abb9b840fa 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
@@ -95,7 +95,28 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select /*vt+ QUERY_TIMEOUT_MS=1000 */ count(*) from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(0) AS count(*)",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select count(*) from `user` where 1 != 1",
+        "Query": "select /*vt+ QUERY_TIMEOUT_MS=1000 */ count(*) from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # select limit with timeout directive sets QueryTimeout in the route
 "select /*vt+ QUERY_TIMEOUT_MS=1000 */ * from user limit 10"
@@ -165,7 +186,29 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select /*vt+ SCATTER_ERRORS_AS_WARNINGS=1 */ count(*) from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(0) AS count(*)",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select count(*) from `user` where 1 != 1",
+        "Query": "select /*vt+ SCATTER_ERRORS_AS_WARNINGS=1 */ count(*) from `user`",
+        "ScatterErrorsAsWarnings": true,
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # select aggregation with partial scatter directive - added comments to try to confuse the hint extraction
 "/*VT_SPAN_CONTEXT=123*/select /*vt+ SCATTER_ERRORS_AS_WARNINGS=1 */ count(*) from user"
@@ -192,7 +235,29 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "/*VT_SPAN_CONTEXT=123*/select /*vt+ SCATTER_ERRORS_AS_WARNINGS=1 */ count(*) from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(0) AS count(*)",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select count(*) from `user` where 1 != 1",
+        "Query": "select /*vt+ SCATTER_ERRORS_AS_WARNINGS=1 */ count(*) from `user`",
+        "ScatterErrorsAsWarnings": true,
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # select limit with partial scatter directive
 "select /*vt+ SCATTER_ERRORS_AS_WARNINGS=1 */ * from user limit 10"
@@ -1182,7 +1247,7 @@ Gen4 plan same as above
   "QueryType": "SELECT",
   "Original": "select * from (select user.id id1, user_extra.id id2 from user join user_extra) as t",
   "Instructions": {
-    "OperatorType": "Subquery",
+    "OperatorType": "SimpleProjection",
     "Columns": [
       0,
       1
@@ -1721,3 +1786,76 @@ Gen4 plan same as above
   }
 }
 Gen4 plan same as above
+
+# Add two tables with the same column in a join
+"select t.id, s.id from user t join user_extra s on t.id = s.user_id join unsharded"
+{
+  "QueryType": "SELECT",
+  "Original": "select t.id, s.id from user t join user_extra s on t.id = s.user_id join unsharded",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-1,-2",
+    "TableName": "`user`, user_extra_unsharded",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select t.id, s.id from `user` as t join user_extra as s on t.id = s.user_id where 1 != 1",
+        "Query": "select t.id, s.id from `user` as t join user_extra as s on t.id = s.user_id",
+        "Table": "`user`, user_extra"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectUnsharded",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select 1 from unsharded where 1 != 1",
+        "Query": "select 1 from unsharded",
+        "Table": "unsharded"
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select t.id, s.id from user t join user_extra s on t.id = s.user_id join unsharded",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "1,2",
+    "TableName": "unsharded_`user`, user_extra",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectUnsharded",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select 1 from unsharded where 1 != 1",
+        "Query": "select 1 from unsharded",
+        "Table": "unsharded"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select t.id, s.id from `user` as t, user_extra as s where 1 != 1",
+        "Query": "select t.id, s.id from `user` as t, user_extra as s where t.id = s.user_id",
+        "Table": "`user`, user_extra"
+      }
+    ]
+  }
+}
+
+