Repository: vitessio/vitess
Pull request #9531 (2022-01-19 08:43:40)

Filter after left join
## Description
Adds planner support for handling filtering after doing an outer join, such as in the following query:

```sql
SELECT user.id 
FROM user 
  LEFT JOIN user_extra ON user.col = user_extra.col
WHERE user_extra.id IS NULL
```

## Checklist
- [x] Tests were added or are not required
- [x] Documentation was added or is not required


Patch:
diff --git a/go/vt/vtgate/evalengine/convert.go b/go/vt/vtgate/evalengine/convert.go
index b04bec9483..7bd372b28c 100644
--- a/go/vt/vtgate/evalengine/convert.go
+++ b/go/vt/vtgate/evalengine/convert.go
@@ -87,7 +87,7 @@ func convertComparisonExpr2(op sqlparser.ComparisonExprOperator, left, right Exp
 	case sqlparser.NotLikeOp:
 		return &LikeExpr{BinaryCoercedExpr: coercedExpr, Negate: true}, nil
 	default:
-		return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "%s: %s", ErrConvertExprNotSupported, op.ToString())
+		return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, op.ToString())
 	}
 }
 
@@ -187,7 +187,7 @@ func convertExpr(e sqlparser.Expr, lookup ConverterLookup) (Expr, error) {
 	switch node := e.(type) {
 	case *sqlparser.ColName:
 		if lookup == nil {
-			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "%s: cannot lookup column", ErrConvertExprNotSupported)
+			return nil, vterrors.Wrap(convertNotSupported(e), "cannot lookup column")
 		}
 		idx, err := lookup.ColumnLookup(node)
 		if err != nil {
@@ -196,7 +196,11 @@ func convertExpr(e sqlparser.Expr, lookup ConverterLookup) (Expr, error) {
 		collation := getCollation(node, lookup)
 		return NewColumn(idx, collation), nil
 	case *sqlparser.ComparisonExpr:
-		return convertComparisonExpr(node.Operator, node.Left, node.Right, lookup)
+		expr, err := convertComparisonExpr(node.Operator, node.Left, node.Right, lookup)
+		if err != nil {
+			return nil, convertNotSupported(e)
+		}
+		return expr, err
 	case sqlparser.Argument:
 		collation := getCollation(e, lookup)
 		return NewBindVar(string(node), collation), nil
@@ -213,7 +217,7 @@ func convertExpr(e sqlparser.Expr, lookup ConverterLookup) (Expr, error) {
 			collation := getCollation(e, lookup)
 			return NewLiteralString(node.Bytes(), collation), nil
 		case sqlparser.HexNum:
-			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "%s: hexadecimal value: %s", ErrConvertExprNotSupported, node.Val)
+			return nil, convertNotSupported(e)
 		}
 	case sqlparser.BoolVal:
 		if node {
@@ -244,7 +248,7 @@ func convertExpr(e sqlparser.Expr, lookup ConverterLookup) (Expr, error) {
 		case sqlparser.DivOp:
 			op = &OpDivision{}
 		default:
-			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "%s: %T", ErrConvertExprNotSupported, e)
+			return nil, convertNotSupported(e)
 		}
 		left, err := convertExpr(node.Left, lookup)
 		if err != nil {
@@ -311,5 +315,9 @@ func convertExpr(e sqlparser.Expr, lookup ConverterLookup) (Expr, error) {
 	case *sqlparser.IsExpr:
 		return convertIsExpr(node.Left, node.Right, lookup)
 	}
-	return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "%s: %T", ErrConvertExprNotSupported, e)
+	return nil, convertNotSupported(e)
+}
+
+func convertNotSupported(e sqlparser.Expr) error {
+	return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "%s: %s", ErrConvertExprNotSupported, sqlparser.String(e))
 }
diff --git a/go/vt/vtgate/planbuilder/abstract/concatenate.go b/go/vt/vtgate/planbuilder/abstract/concatenate.go
index 3e8148fcef..d75e739906 100644
--- a/go/vt/vtgate/planbuilder/abstract/concatenate.go
+++ b/go/vt/vtgate/planbuilder/abstract/concatenate.go
@@ -46,21 +46,24 @@ func (c *Concatenate) TableID() semantics.TableSet {
 }
 
 // PushPredicate implements the Operator interface
-func (c *Concatenate) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error {
+func (c *Concatenate) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) (LogicalOperator, error) {
+	newSources := make([]LogicalOperator, 0, len(c.Sources))
 	for index, source := range c.Sources {
 		if len(c.SelectStmts[index].SelectExprs) != 1 {
-			return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "can't push predicates on concatenate")
+			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "can't push predicates on concatenate")
 		}
 		if _, isStarExpr := c.SelectStmts[index].SelectExprs[0].(*sqlparser.StarExpr); !isStarExpr {
-			return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "can't push predicates on concatenate")
+			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "can't push predicates on concatenate")
 		}
 
-		err := source.PushPredicate(expr, semTable)
+		newSrc, err := source.PushPredicate(expr, semTable)
 		if err != nil {
-			return err
+			return nil, err
 		}
+		newSources = append(newSources, newSrc)
 	}
-	return nil
+	c.Sources = newSources
+	return c, nil
 }
 
 // UnsolvedPredicates implements the Operator interface
diff --git a/go/vt/vtgate/planbuilder/abstract/derived.go b/go/vt/vtgate/planbuilder/abstract/derived.go
index c38ae5c505..13a7d8a62d 100644
--- a/go/vt/vtgate/planbuilder/abstract/derived.go
+++ b/go/vt/vtgate/planbuilder/abstract/derived.go
@@ -41,20 +41,22 @@ func (d *Derived) TableID() semantics.TableSet {
 }
 
 // PushPredicate implements the Operator interface
-func (d *Derived) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error {
+func (d *Derived) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) (LogicalOperator, error) {
 	tableInfo, err := semTable.TableInfoForExpr(expr)
 	if err != nil {
 		if err == semantics.ErrMultipleTables {
-			return semantics.ProjError{Inner: vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: unable to split predicates to derived table: %s", sqlparser.String(expr))}
+			return nil, semantics.ProjError{Inner: vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: unable to split predicates to derived table: %s", sqlparser.String(expr))}
 		}
-		return err
+		return nil, err
 	}
 
 	newExpr, err := semantics.RewriteDerivedExpression(expr, tableInfo)
 	if err != nil {
-		return err
+		return nil, err
 	}
-	return d.Inner.PushPredicate(newExpr, semTable)
+	newSrc, err := d.Inner.PushPredicate(newExpr, semTable)
+	d.Inner = newSrc
+	return d, err
 }
 
 // UnsolvedPredicates implements the Operator interface
diff --git a/go/vt/vtgate/planbuilder/abstract/filter.go b/go/vt/vtgate/planbuilder/abstract/filter.go
new file mode 100644
index 0000000000..0408f46bc1
--- /dev/null
+++ b/go/vt/vtgate/planbuilder/abstract/filter.go
@@ -0,0 +1,62 @@
+/*
+Copyright 2021 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 abstract
+
+import (
+	"vitess.io/vitess/go/vt/sqlparser"
+	"vitess.io/vitess/go/vt/vtgate/semantics"
+)
+
+type Filter struct {
+	Source     LogicalOperator
+	Predicates []sqlparser.Expr
+}
+
+var _ LogicalOperator = (*Filter)(nil)
+
+// iLogical implements the LogicalOperator interface
+func (f *Filter) iLogical() {}
+
+// TableID implements the LogicalOperator interface
+func (f *Filter) TableID() semantics.TableSet {
+	return f.Source.TableID()
+}
+
+// UnsolvedPredicates implements the LogicalOperator interface
+func (f *Filter) UnsolvedPredicates(semTable *semantics.SemTable) []sqlparser.Expr {
+	return f.Source.UnsolvedPredicates(semTable)
+}
+
+// CheckValid implements the LogicalOperator interface
+func (f *Filter) CheckValid() error {
+	return f.Source.CheckValid()
+}
+
+// PushPredicate implements the LogicalOperator interface
+func (f *Filter) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) (LogicalOperator, error) {
+	f.Predicates = append(f.Predicates, expr)
+	return f, nil
+}
+
+// Compact implements the LogicalOperator interface
+func (f *Filter) Compact(semTable *semantics.SemTable) (LogicalOperator, error) {
+	if len(f.Predicates) == 0 {
+		return f.Source, nil
+	}
+
+	return f, nil
+}
diff --git a/go/vt/vtgate/planbuilder/abstract/join.go b/go/vt/vtgate/planbuilder/abstract/join.go
index 5eee83fb93..55ad992f3e 100644
--- a/go/vt/vtgate/planbuilder/abstract/join.go
+++ b/go/vt/vtgate/planbuilder/abstract/join.go
@@ -36,15 +36,17 @@ var _ LogicalOperator = (*Join)(nil)
 func (*Join) iLogical() {}
 
 // PushPredicate implements the Operator interface
-func (j *Join) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error {
+func (j *Join) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) (LogicalOperator, error) {
 	deps := semTable.RecursiveDeps(expr)
 	switch {
 	case deps.IsSolvedBy(j.LHS.TableID()):
-		return j.LHS.PushPredicate(expr, semTable)
-	case deps.IsSolvedBy(j.RHS.TableID()):
-		if !j.LeftJoin {
-			return j.RHS.PushPredicate(expr, semTable)
+		lhs, err := j.LHS.PushPredicate(expr, semTable)
+		if err != nil {
+			return nil, err
 		}
+		j.LHS = lhs
+		return j, nil
+	case deps.IsSolvedBy(j.RHS.TableID()):
 		// we are looking for predicates like `tbl.col = <>` or `<> = tbl.col`,
 		// where tbl is on the rhs of the left outer join
 		if cmp, isCmp := expr.(*sqlparser.ComparisonExpr); isCmp && cmp.Operator != sqlparser.NullSafeEqualOp &&
@@ -59,15 +61,26 @@ func (j *Join) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable)
 
 			// This is based on the paper "Canonical Abstraction for Outerjoin Optimization" by J Rao et al
 			j.LeftJoin = false
-			return j.RHS.PushPredicate(expr, semTable)
 		}
-		return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: cross-shard left join and where clause")
+		if !j.LeftJoin {
+			rhs, err := j.RHS.PushPredicate(expr, semTable)
+			if err != nil {
+				return nil, err
+			}
+			j.RHS = rhs
+			return j, err
+		}
+		op := &Filter{
+			Source:     j,
+			Predicates: []sqlparser.Expr{expr},
+		}
+		return op, nil
 	case deps.IsSolvedBy(j.LHS.TableID().Merge(j.RHS.TableID())):
 		j.Predicate = sqlparser.AndExpressions(j.Predicate, expr)
-		return nil
+		return j, nil
 	}
 
-	return vterrors.Errorf(vtrpcpb.Code_INTERNAL, "Cannot push predicate: %s", sqlparser.String(expr))
+	return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "Cannot push predicate: %s", sqlparser.String(expr))
 }
 
 // TableID implements the Operator interface
diff --git a/go/vt/vtgate/planbuilder/abstract/operator.go b/go/vt/vtgate/planbuilder/abstract/operator.go
index 2626112317..940a37832e 100644
--- a/go/vt/vtgate/planbuilder/abstract/operator.go
+++ b/go/vt/vtgate/planbuilder/abstract/operator.go
@@ -42,7 +42,7 @@ type (
 		iLogical()
 
 		// PushPredicate pushes a predicate to the closest possible operator
-		PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error
+		PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) (LogicalOperator, error)
 
 		// Compact will optimise the operator tree into a smaller but equivalent version
 		Compact(semTable *semantics.SemTable) (LogicalOperator, error)
@@ -104,7 +104,7 @@ func getOperatorFromTableExpr(tableExpr sqlparser.TableExpr, semTable *semantics
 			}
 			op := createJoin(lhs, rhs)
 			if tableExpr.Condition.On != nil {
-				err = op.PushPredicate(sqlparser.RemoveKeyspaceFromColName(tableExpr.Condition.On), semTable)
+				op, err = op.PushPredicate(sqlparser.RemoveKeyspaceFromColName(tableExpr.Condition.On), semTable)
 				if err != nil {
 					return nil, err
 				}
@@ -220,7 +220,7 @@ func createOperatorFromSelect(sel *sqlparser.Select, semTable *semantics.SemTabl
 	if sel.Where != nil {
 		exprs := sqlparser.SplitAndExpression(nil, sel.Where.Expr)
 		for _, expr := range exprs {
-			err := op.PushPredicate(sqlparser.RemoveKeyspaceFromColName(expr), semTable)
+			op, err = op.PushPredicate(sqlparser.RemoveKeyspaceFromColName(expr), semTable)
 			if err != nil {
 				return nil, err
 			}
diff --git a/go/vt/vtgate/planbuilder/abstract/querygraph.go b/go/vt/vtgate/planbuilder/abstract/querygraph.go
index 764c92026c..0e6da05e21 100644
--- a/go/vt/vtgate/planbuilder/abstract/querygraph.go
+++ b/go/vt/vtgate/planbuilder/abstract/querygraph.go
@@ -61,14 +61,14 @@ var _ LogicalOperator = (*QueryGraph)(nil)
 func (*QueryGraph) iLogical() {}
 
 // PushPredicate implements the Operator interface
-func (qg *QueryGraph) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error {
+func (qg *QueryGraph) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) (LogicalOperator, error) {
 	for _, e := range sqlparser.SplitAndExpression(nil, expr) {
 		err := qg.collectPredicate(e, semTable)
 		if err != nil {
-			return err
+			return nil, err
 		}
 	}
-	return nil
+	return qg, nil
 }
 
 // TableID implements the Operator interface
diff --git a/go/vt/vtgate/planbuilder/abstract/subquery.go b/go/vt/vtgate/planbuilder/abstract/subquery.go
index a0c346673b..4811e937e3 100644
--- a/go/vt/vtgate/planbuilder/abstract/subquery.go
+++ b/go/vt/vtgate/planbuilder/abstract/subquery.go
@@ -54,8 +54,8 @@ func (s *SubQuery) TableID() semantics.TableSet {
 }
 
 // PushPredicate implements the Operator interface
-func (s *SubQuery) PushPredicate(sqlparser.Expr, *semantics.SemTable) error {
-	return vterrors.Errorf(vtrpcpb.Code_INTERNAL, "[BUG] should not try to push predicate on subquery")
+func (s *SubQuery) PushPredicate(sqlparser.Expr, *semantics.SemTable) (LogicalOperator, error) {
+	return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "[BUG] should not try to push predicate on subquery")
 }
 
 // UnsolvedPredicates implements the Operator interface
diff --git a/go/vt/vtgate/planbuilder/abstract/vindex.go b/go/vt/vtgate/planbuilder/abstract/vindex.go
index 9aa23a2e62..49b453e25e 100644
--- a/go/vt/vtgate/planbuilder/abstract/vindex.go
+++ b/go/vt/vtgate/planbuilder/abstract/vindex.go
@@ -56,31 +56,31 @@ func (v *Vindex) TableID() semantics.TableSet {
 const vindexUnsupported = "unsupported: where clause for vindex function must be of the form id = <val> or id in(<val>,...)"
 
 // PushPredicate implements the Operator interface
-func (v *Vindex) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error {
+func (v *Vindex) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) (LogicalOperator, error) {
 	for _, e := range sqlparser.SplitAndExpression(nil, expr) {
 		deps := semTable.RecursiveDeps(e)
 		if deps.NumberOfTables() > 1 {
-			return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, vindexUnsupported+" (multiple tables involved)")
+			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, vindexUnsupported+" (multiple tables involved)")
 		}
 		// check if we already have a predicate
 		if v.OpCode != engine.VindexNone {
-			return vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (multiple filters)")
+			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (multiple filters)")
 		}
 
 		// check LHS
 		comparison, ok := e.(*sqlparser.ComparisonExpr)
 		if !ok {
-			return vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (not a comparison)")
+			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (not a comparison)")
 		}
 		if comparison.Operator != sqlparser.EqualOp && comparison.Operator != sqlparser.InOp {
-			return vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (not equality)")
+			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (not equality)")
 		}
 		colname, ok := comparison.Left.(*sqlparser.ColName)
 		if !ok {
-			return vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (lhs is not a column)")
+			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (lhs is not a column)")
 		}
 		if !colname.Name.EqualString("id") {
-			return vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (lhs is not id)")
+			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (lhs is not id)")
 		}
 
 		// check RHS
@@ -88,15 +88,15 @@ func (v *Vindex) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable
 		if sqlparser.IsValue(comparison.Right) || sqlparser.IsSimpleTuple(comparison.Right) {
 			v.Value = comparison.Right
 		} else {
-			return vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (rhs is not a value)")
+			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+" (rhs is not a value)")
 		}
 		if err != nil {
-			return vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+": %v", err)
+			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, vindexUnsupported+": %v", err)
 		}
 		v.OpCode = engine.VindexMap
 		v.Table.Predicates = append(v.Table.Predicates, e)
 	}
-	return nil
+	return v, nil
 }
 
 // UnsolvedPredicates implements the Operator interface
diff --git a/go/vt/vtgate/planbuilder/concatenate.go b/go/vt/vtgate/planbuilder/concatenate.go
index d0d11f97fc..8c6d43a8d2 100644
--- a/go/vt/vtgate/planbuilder/concatenate.go
+++ b/go/vt/vtgate/planbuilder/concatenate.go
@@ -21,10 +21,10 @@ import (
 	"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/semantics"
 )
 
 type concatenate struct {
+	v3Plan
 	lhs, rhs logicalPlan
 	order    int
 }
@@ -54,14 +54,6 @@ func (c *concatenate) Wireup(plan logicalPlan, jt *jointab) error {
 	return c.rhs.Wireup(plan, jt)
 }
 
-func (c *concatenate) WireupGen4(semTable *semantics.SemTable) error {
-	err := c.lhs.WireupGen4(semTable)
-	if err != nil {
-		return err
-	}
-	return c.rhs.WireupGen4(semTable)
-}
-
 func (c *concatenate) SupplyVar(from, to int, col *sqlparser.ColName, varname string) {
 	panic("implement me")
 }
@@ -93,10 +85,6 @@ func (c *concatenate) Rewrite(inputs ...logicalPlan) error {
 	return nil
 }
 
-func (c *concatenate) ContainsTables() semantics.TableSet {
-	return c.lhs.ContainsTables().Merge(c.rhs.ContainsTables())
-}
-
 // Inputs implements the logicalPlan interface
 func (c *concatenate) Inputs() []logicalPlan {
 	return []logicalPlan{c.lhs, c.rhs}
diff --git a/go/vt/vtgate/planbuilder/concatenateGen4.go b/go/vt/vtgate/planbuilder/concatenateGen4.go
index 8a9e3300ba..29617b2fb8 100644
--- a/go/vt/vtgate/planbuilder/concatenateGen4.go
+++ b/go/vt/vtgate/planbuilder/concatenateGen4.go
@@ -109,3 +109,8 @@ func (c *concatenateGen4) ContainsTables() semantics.TableSet {
 func (c *concatenateGen4) Inputs() []logicalPlan {
 	return c.sources
 }
+
+// OutputColumns implements the logicalPlan interface
+func (c *concatenateGen4) OutputColumns() []sqlparser.SelectExpr {
+	return c.sources[0].OutputColumns()
+}
diff --git a/go/vt/vtgate/planbuilder/filter.go b/go/vt/vtgate/planbuilder/filter.go
index d6f475c637..861325c672 100644
--- a/go/vt/vtgate/planbuilder/filter.go
+++ b/go/vt/vtgate/planbuilder/filter.go
@@ -34,8 +34,9 @@ type (
 	}
 
 	simpleConverterLookup struct {
-		ctx  *plancontext.PlanningContext
-		plan logicalPlan
+		ctx               *plancontext.PlanningContext
+		plan              logicalPlan
+		canPushProjection bool
 	}
 )
 
@@ -47,7 +48,7 @@ func (s *simpleConverterLookup) ColumnLookup(col *sqlparser.ColName) (int, error
 	if err != nil {
 		return 0, err
 	}
-	if added {
+	if added && !s.canPushProjection {
 		return 0, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "column should not be pushed to projection while doing a column lookup")
 	}
 	return offset, nil
diff --git a/go/vt/vtgate/planbuilder/hash_join.go b/go/vt/vtgate/planbuilder/hash_join.go
index 99c70b5d94..a0bf8d607b 100644
--- a/go/vt/vtgate/planbuilder/hash_join.go
+++ b/go/vt/vtgate/planbuilder/hash_join.go
@@ -20,6 +20,7 @@ import (
 	"vitess.io/vitess/go/mysql/collations"
 	querypb "vitess.io/vitess/go/vt/proto/query"
 	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/semantics"
@@ -89,3 +90,20 @@ func (hj *hashJoin) Rewrite(inputs ...logicalPlan) error {
 func (hj *hashJoin) ContainsTables() semantics.TableSet {
 	return hj.Left.ContainsTables().Merge(hj.Right.ContainsTables())
 }
+
+// OutputColumns implements the logicalPlan interface
+func (hj *hashJoin) OutputColumns() []sqlparser.SelectExpr {
+	return getOutputColumnsFromJoin(hj.Cols, hj.Left.OutputColumns(), hj.Right.OutputColumns())
+}
+
+func getOutputColumnsFromJoin(ints []int, lhs []sqlparser.SelectExpr, rhs []sqlparser.SelectExpr) (cols []sqlparser.SelectExpr) {
+	for _, col := range ints {
+		if col < 0 {
+			col *= -1
+			cols = append(cols, lhs[col-1])
+		} else {
+			cols = append(cols, rhs[col-1])
+		}
+	}
+	return
+}
diff --git a/go/vt/vtgate/planbuilder/horizon_planning.go b/go/vt/vtgate/planbuilder/horizon_planning.go
index 18625d5367..051658d965 100644
--- a/go/vt/vtgate/planbuilder/horizon_planning.go
+++ b/go/vt/vtgate/planbuilder/horizon_planning.go
@@ -34,10 +34,9 @@ import (
 )
 
 type horizonPlanning struct {
-	sel             *sqlparser.Select
-	qp              *abstract.QueryProjection
-	needsTruncation bool
-	vtgateGrouping  bool
+	sel            *sqlparser.Select
+	qp             *abstract.QueryProjection
+	vtgateGrouping bool
 }
 
 func (hp *horizonPlanning) planHorizon(ctx *plancontext.PlanningContext, plan logicalPlan) (logicalPlan, error) {
@@ -115,7 +114,7 @@ func (hp *horizonPlanning) planHorizon(ctx *plancontext.PlanningContext, plan lo
 		return nil, err
 	}
 
-	err = hp.truncateColumnsIfNeeded(plan)
+	plan, err = hp.truncateColumnsIfNeeded(ctx, plan)
 	if err != nil {
 		return nil, err
 	}
@@ -136,11 +135,10 @@ func pushProjections(ctx *plancontext.PlanningContext, plan logicalPlan, selectE
 	return nil
 }
 
-func (hp *horizonPlanning) truncateColumnsIfNeeded(plan logicalPlan) error {
-	if !hp.needsTruncation {
-		return nil
+func (hp *horizonPlanning) truncateColumnsIfNeeded(ctx *plancontext.PlanningContext, plan logicalPlan) (logicalPlan, error) {
+	if len(plan.OutputColumns()) == hp.sel.GetColumnCount() {
+		return plan, nil
 	}
-
 	switch p := plan.(type) {
 	case *routeGen4:
 		p.eroute.SetTruncateColumnCount(hp.sel.GetColumnCount())
@@ -151,14 +149,23 @@ func (hp *horizonPlanning) truncateColumnsIfNeeded(plan logicalPlan) error {
 	case *memorySort:
 		p.truncater.SetTruncateColumnCount(hp.sel.GetColumnCount())
 	case *pulloutSubquery:
-		return hp.truncateColumnsIfNeeded(p.underlying)
-	case *filter:
-		return hp.truncateColumnsIfNeeded(p.input)
+		newUnderlyingPlan, err := hp.truncateColumnsIfNeeded(ctx, p.underlying)
+		if err != nil {
+			return nil, err
+		}
+		p.underlying = newUnderlyingPlan
 	default:
-		return vterrors.Errorf(vtrpcpb.Code_INTERNAL, "plan type not known for column truncation: %T", plan)
-	}
+		plan = &simpleProjection{
+			logicalPlanCommon: newBuilderCommon(plan),
+			eSimpleProj:       &engine.SimpleProjection{},
+		}
 
-	return nil
+		err := pushProjections(ctx, plan, hp.qp.SelectExprs)
+		if err != nil {
+			return nil, err
+		}
+	}
+	return plan, nil
 }
 
 // pushProjection pushes a projection to the plan.
@@ -337,6 +344,11 @@ func pushProjection(ctx *plancontext.PlanningContext, expr *sqlparser.AliasedExp
 				return aggregate.Col, false, nil
 			}
 		}
+		for _, key := range node.eaggr.GroupByKeys {
+			if sqlparser.EqualsExpr(key.Expr, expr.Expr) {
+				return key.KeyCol, false, nil
+			}
+		}
 		return 0, false, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "cannot push projections in ordered aggregates")
 	case *vindexFunc:
 		colsBefore := len(node.eVindexFunc.Cols)
@@ -349,6 +361,8 @@ func pushProjection(ctx *plancontext.PlanningContext, expr *sqlparser.AliasedExp
 		return pushProjection(ctx, expr, node.input, inner, reuseCol, hasAggregation)
 	case *distinct:
 		return pushProjection(ctx, expr, node.input, inner, reuseCol, hasAggregation)
+	case *filter:
+		return pushProjection(ctx, expr, node.input, inner, reuseCol, hasAggregation)
 	case *semiJoin:
 		passDownReuseCol := reuseCol
 		if !reuseCol {
@@ -442,10 +456,6 @@ func checkIfAlreadyExists(expr *sqlparser.AliasedExpr, node sqlparser.SelectStat
 	return -1
 }
 
-func (hp *horizonPlanning) haveToTruncate(v bool) {
-	hp.needsTruncation = hp.needsTruncation || v
-}
-
 func (hp *horizonPlanning) planAggregations(ctx *plancontext.PlanningContext, plan logicalPlan) (logicalPlan, error) {
 	newPlan := plan
 	var oa *orderedAggregate
@@ -515,11 +525,10 @@ func (hp *horizonPlanning) planAggregations(ctx *plancontext.PlanningContext, pl
 	}
 
 	for _, groupExpr := range hp.qp.GroupByExprs {
-		added, err := planGroupByGen4(ctx, groupExpr, newPlan, false)
+		err := planGroupByGen4(ctx, groupExpr, newPlan, false)
 		if err != nil {
 			return nil, err
 		}
-		hp.haveToTruncate(added)
 	}
 
 	newPlan, err := hp.planHaving(ctx, newPlan)
@@ -595,7 +604,6 @@ func (hp *horizonPlanning) createPushExprAndAlias(
 		}
 
 		oa.eaggr.PreProcess = true
-		hp.haveToTruncate(true)
 		by := abstract.GroupBy{
 			Inner:             innerAliased.Expr,
 			WeightStrExpr:     innerAliased.Expr,
@@ -615,7 +623,7 @@ func hasUniqueVindex(vschema plancontext.VSchema, semTable *semantics.SemTable,
 	return false
 }
 
-func planGroupByGen4(ctx *plancontext.PlanningContext, groupExpr abstract.GroupBy, plan logicalPlan, wsAdded bool) (bool, error) {
+func planGroupByGen4(ctx *plancontext.PlanningContext, groupExpr abstract.GroupBy, plan logicalPlan, wsAdded bool) error {
 	switch node := plan.(type) {
 	case *routeGen4:
 		sel := node.Select.(*sqlparser.Select)
@@ -626,14 +634,14 @@ func planGroupByGen4(ctx *plancontext.PlanningContext, groupExpr abstract.GroupB
 		if wsAdded {
 			sel.GroupBy = append(sel.GroupBy, weightStringFor(groupExpr.WeightStrExpr))
 		}
-		return false, nil
+		return nil
 	case *joinGen4, *hashJoin:
-		_, _, added, err := wrapAndPushExpr(ctx, groupExpr.Inner, groupExpr.WeightStrExpr, node)
-		return added, err
+		_, _, err := wrapAndPushExpr(ctx, groupExpr.Inner, groupExpr.WeightStrExpr, node)
+		return err
 	case *orderedAggregate:
-		keyCol, wsOffset, colAdded, err := wrapAndPushExpr(ctx, groupExpr.Inner, groupExpr.WeightStrExpr, node.input)
+		keyCol, wsOffset, err := wrapAndPushExpr(ctx, groupExpr.Inner, groupExpr.WeightStrExpr, node.input)
 		if err != nil {
-			return false, err
+			return err
 		}
 		if groupExpr.DistinctAggrIndex == 0 {
 			node.eaggr.GroupByKeys = append(node.eaggr.GroupByKeys, &engine.GroupByParams{KeyCol: keyCol, WeightStringCol: wsOffset, Expr: groupExpr.WeightStrExpr, CollationID: ctx.SemTable.CollationFor(groupExpr.Inner)})
@@ -643,17 +651,17 @@ func planGroupByGen4(ctx *plancontext.PlanningContext, groupExpr abstract.GroupB
 				node.eaggr.Aggregates[groupExpr.DistinctAggrIndex-1].WCol = wsOffset
 			}
 		}
-		colAddedRecursively, err := planGroupByGen4(ctx, groupExpr, node.input, wsOffset != -1)
+		err = planGroupByGen4(ctx, groupExpr, node.input, wsOffset != -1)
 		if err != nil {
-			return false, err
+			return err
 		}
-		return colAdded || colAddedRecursively, nil
+		return nil
 	case *pulloutSubquery:
 		return planGroupByGen4(ctx, groupExpr, node.underlying, wsAdded)
 	case *semiJoin:
-		return false, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: group by in a query having a correlated subquery")
+		return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: group by in a query having a correlated subquery")
 	default:
-		return false, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: group by on: %T", plan)
+		return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: group by on: %T", plan)
 	}
 }
 
@@ -683,11 +691,10 @@ func (hp *horizonPlanning) planGroupByUsingOrderBy(ctx *plancontext.PlanningCont
 func (hp *horizonPlanning) planOrderBy(ctx *plancontext.PlanningContext, orderExprs []abstract.OrderBy, plan logicalPlan) (logicalPlan, error) {
 	switch plan := plan.(type) {
 	case *routeGen4:
-		newPlan, truncate, err := planOrderByForRoute(ctx, orderExprs, plan, hp.qp.HasStar)
+		newPlan, err := planOrderByForRoute(ctx, orderExprs, plan, hp.qp.HasStar)
 		if err != nil {
 			return nil, err
 		}
-		hp.haveToTruncate(truncate)
 		return newPlan, nil
 	case *joinGen4:
 		newPlan, err := hp.planOrderByForJoin(ctx, orderExprs, plan)
@@ -763,20 +770,19 @@ func isSpecialOrderBy(o abstract.OrderBy) bool {
 	return isFunction && f.Name.Lowered() == "rand"
 }
 
-func planOrderByForRoute(ctx *plancontext.PlanningContext, orderExprs []abstract.OrderBy, plan *routeGen4, hasStar bool) (logicalPlan, bool, error) {
-	origColCount := plan.Select.GetColumnCount()
+func planOrderByForRoute(ctx *plancontext.PlanningContext, orderExprs []abstract.OrderBy, plan *routeGen4, hasStar bool) (logicalPlan, error) {
 	for _, order := range orderExprs {
 		err := checkOrderExprCanBePlannedInScatter(plan, order, hasStar)
 		if err != nil {
-			return nil, false, err
+			return nil, err
 		}
 		plan.Select.AddOrder(order.Inner)
 		if isSpecialOrderBy(order) {
 			continue
 		}
-		offset, weightStringOffset, _, err := wrapAndPushExpr(ctx, order.Inner.Expr, order.WeightStrExpr, plan)
+		offset, weightStringOffset, err := wrapAndPushExpr(ctx, order.Inner.Expr, order.WeightStrExpr, plan)
 		if err != nil {
-			return nil, false, err
+			return nil, err
 		}
 		plan.eroute.OrderBy = append(plan.eroute.OrderBy, engine.OrderByParams{
 			Col:             offset,
@@ -785,7 +791,7 @@ func planOrderByForRoute(ctx *plancontext.PlanningContext, orderExprs []abstract
 			CollationID:     ctx.SemTable.CollationFor(order.Inner.Expr),
 		})
 	}
-	return plan, origColCount != plan.Select.GetColumnCount(), nil
+	return plan, nil
 }
 
 // checkOrderExprCanBePlannedInScatter verifies that the given order by expression can be planned.
@@ -811,20 +817,20 @@ func checkOrderExprCanBePlannedInScatter(plan *routeGen4, order abstract.OrderBy
 
 // wrapAndPushExpr pushes the expression and weighted_string function to the plan using semantics.SemTable
 // It returns (expr offset, weight_string offset, new_column added, error)
-func wrapAndPushExpr(ctx *plancontext.PlanningContext, expr sqlparser.Expr, weightStrExpr sqlparser.Expr, plan logicalPlan) (int, int, bool, error) {
-	offset, added, err := pushProjection(ctx, &sqlparser.AliasedExpr{Expr: expr}, plan, true, true, false)
+func wrapAndPushExpr(ctx *plancontext.PlanningContext, expr sqlparser.Expr, weightStrExpr sqlparser.Expr, plan logicalPlan) (int, int, error) {
+	offset, _, err := pushProjection(ctx, &sqlparser.AliasedExpr{Expr: expr}, plan, true, true, false)
 	if err != nil {
-		return 0, 0, false, err
+		return 0, 0, err
 	}
 	if weightStrExpr == nil {
-		return offset, -1, added, nil
+		return offset, -1, nil
 	}
 	if !sqlparser.IsColName(expr) {
 		unary, ok := expr.(*sqlparser.UnaryExpr)
 		if ok && sqlparser.IsColName(unary.Expr) {
 			expr = unary.Expr
 		} else {
-			return 0, 0, false, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: complex order by expression: %s", sqlparser.String(expr))
+			return 0, 0, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: complex order by expression: %s", sqlparser.String(expr))
 		}
 	}
 	qt := ctx.SemTable.TypeFor(expr)
@@ -834,14 +840,13 @@ func wrapAndPushExpr(ctx *plancontext.PlanningContext, expr sqlparser.Expr, weig
 	}
 
 	weightStringOffset := -1
-	var wAdded bool
 	if wsNeeded {
-		weightStringOffset, wAdded, err = pushProjection(ctx, &sqlparser.AliasedExpr{Expr: weightStringFor(weightStrExpr)}, plan, true, true, false)
+		weightStringOffset, _, err = pushProjection(ctx, &sqlparser.AliasedExpr{Expr: weightStringFor(weightStrExpr)}, plan, true, true, false)
 		if err != nil {
-			return 0, 0, false, err
+			return 0, 0, err
 		}
 	}
-	return offset, weightStringOffset, added || wAdded, nil
+	return offset, weightStringOffset, nil
 }
 
 func weightStringFor(expr sqlparser.Expr) sqlparser.Expr {
@@ -971,11 +976,10 @@ func (hp *horizonPlanning) createMemorySortPlan(ctx *plancontext.PlanningContext
 		if !useWeightStr {
 			wsExpr = nil
 		}
-		offset, weightStringOffset, added, err := wrapAndPushExpr(ctx, order.Inner.Expr, wsExpr, plan)
+		offset, weightStringOffset, err := wrapAndPushExpr(ctx, order.Inner.Expr, wsExpr, plan)
 		if err != nil {
 			return nil, err
 		}
-		hp.haveToTruncate(added)
 		ms.eMemorySort.OrderBy = append(ms.eMemorySort.OrderBy, engine.OrderByParams{
 			Col:               offset,
 			WeightStringCol:   weightStringOffset,
@@ -1082,11 +1086,10 @@ func (hp *horizonPlanning) addDistinct(ctx *plancontext.PlanningContext, plan lo
 			ctx.SemTable.CopyDependencies(aliasExpr.Expr, inner)
 		}
 		grpParam := &engine.GroupByParams{KeyCol: index, WeightStringCol: -1, CollationID: ctx.SemTable.CollationFor(inner)}
-		_, wOffset, added, err := wrapAndPushExpr(ctx, aliasExpr.Expr, aliasExpr.Expr, plan)
+		_, wOffset, err := wrapAndPushExpr(ctx, aliasExpr.Expr, aliasExpr.Expr, plan)
 		if err != nil {
 			return nil, err
 		}
-		hp.needsTruncation = hp.needsTruncation || added
 		grpParam.WeightStringCol = wOffset
 		eaggr.GroupByKeys = append(eaggr.GroupByKeys, grpParam)
 
diff --git a/go/vt/vtgate/planbuilder/join.go b/go/vt/vtgate/planbuilder/join.go
index b5c499973f..84738152bf 100644
--- a/go/vt/vtgate/planbuilder/join.go
+++ b/go/vt/vtgate/planbuilder/join.go
@@ -17,8 +17,6 @@ limitations under the License.
 package planbuilder
 
 import (
-	"vitess.io/vitess/go/vt/vtgate/semantics"
-
 	vtrpcpb "vitess.io/vitess/go/vt/proto/vtrpc"
 	"vitess.io/vitess/go/vt/vterrors"
 
@@ -32,6 +30,7 @@ var _ logicalPlan = (*join)(nil)
 // It's used to build a normal join or a left join
 // operation.
 type join struct {
+	v3Plan
 	order         int
 	resultColumns []*resultColumn
 	weightStrings map[*resultColumn]int
@@ -151,15 +150,6 @@ func (jb *join) Wireup(plan logicalPlan, jt *jointab) error {
 	return jb.Left.Wireup(plan, jt)
 }
 
-// Wireup2 implements the logicalPlan interface
-func (jb *join) WireupGen4(semTable *semantics.SemTable) error {
-	err := jb.Right.WireupGen4(semTable)
-	if err != nil {
-		return err
-	}
-	return jb.Left.WireupGen4(semTable)
-}
-
 // SupplyVar implements the logicalPlan interface
 func (jb *join) SupplyVar(from, to int, col *sqlparser.ColName, varname string) {
 	if !jb.isOnLeft(from) {
@@ -244,11 +234,6 @@ func (jb *join) Rewrite(inputs ...logicalPlan) error {
 	return nil
 }
 
-// Solves implements the logicalPlan interface
-func (jb *join) ContainsTables() semantics.TableSet {
-	return jb.Left.ContainsTables().Merge(jb.Right.ContainsTables())
-}
-
 // Inputs implements the logicalPlan interface
 func (jb *join) Inputs() []logicalPlan {
 	return []logicalPlan{jb.Left, jb.Right}
diff --git a/go/vt/vtgate/planbuilder/joinGen4.go b/go/vt/vtgate/planbuilder/joinGen4.go
index 8255a48130..debfe6d42e 100644
--- a/go/vt/vtgate/planbuilder/joinGen4.go
+++ b/go/vt/vtgate/planbuilder/joinGen4.go
@@ -18,6 +18,7 @@ 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/semantics"
@@ -76,3 +77,8 @@ func (j *joinGen4) Rewrite(inputs ...logicalPlan) error {
 func (j *joinGen4) ContainsTables() semantics.TableSet {
 	return j.Left.ContainsTables().Merge(j.Right.ContainsTables())
 }
+
+// OutputColumns implements the logicalPlan interface
+func (j *joinGen4) OutputColumns() []sqlparser.SelectExpr {
+	return getOutputColumnsFromJoin(j.Cols, j.Left.OutputColumns(), j.Right.OutputColumns())
+}
diff --git a/go/vt/vtgate/planbuilder/logical_plan.go b/go/vt/vtgate/planbuilder/logical_plan.go
index c599a0689f..6dda9e6ddf 100644
--- a/go/vt/vtgate/planbuilder/logical_plan.go
+++ b/go/vt/vtgate/planbuilder/logical_plan.go
@@ -85,6 +85,9 @@ type logicalPlan interface {
 	// ContainsTables keeps track which query tables are being solved by this logical plan
 	// This is only applicable for plans that have been built with the Gen4 planner
 	ContainsTables() semantics.TableSet
+
+	// OutputColumns shows the columns that this plan will produce
+	OutputColumns() []sqlparser.SelectExpr
 }
 
 // gen4Plan implements a few methods from logicalPlan that are unused by Gen4.
@@ -125,6 +128,21 @@ func (*gen4Plan) SupplyWeightString(int, bool) (weightcolNumber int, err error)
 	panic("[BUG]: should not be called. This is a Gen4 primitive")
 }
 
+// v3Plan implements methods that are only used by gen4
+type v3Plan struct{}
+
+func (*v3Plan) WireupGen4(*semantics.SemTable) error {
+	panic("[BUG]: should not be called. This is a V3 primitive")
+}
+
+func (*v3Plan) ContainsTables() semantics.TableSet {
+	panic("[BUG]: should not be called. This is a V3 primitive")
+}
+
+func (*v3Plan) OutputColumns() []sqlparser.SelectExpr {
+	panic("[BUG]: should not be called. This is a V3 primitive")
+}
+
 type planVisitor func(logicalPlan) (bool, logicalPlan, error)
 
 func visit(node logicalPlan, visitor planVisitor) (logicalPlan, error) {
@@ -224,11 +242,16 @@ func (bc *logicalPlanCommon) Inputs() []logicalPlan {
 	return []logicalPlan{bc.input}
 }
 
-// Solves implements the logicalPlan interface
+// ContainsTables implements the logicalPlan interface
 func (bc *logicalPlanCommon) ContainsTables() semantics.TableSet {
 	return bc.input.ContainsTables()
 }
 
+// OutputColumns implements the logicalPlan interface
+func (bc *logicalPlanCommon) OutputColumns() []sqlparser.SelectExpr {
+	return bc.input.OutputColumns()
+}
+
 //-------------------------------------------------------------------------
 
 // resultsBuilder is a superset of logicalPlanCommon. It also handles
diff --git a/go/vt/vtgate/planbuilder/memory_sort_gen4.go b/go/vt/vtgate/planbuilder/memory_sort_gen4.go
deleted file mode 100644
index 894ae7010b..0000000000
--- a/go/vt/vtgate/planbuilder/memory_sort_gen4.go
+++ /dev/null
@@ -1,89 +0,0 @@
-/*
-Copyright 2021 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/semantics"
-)
-
-var _ logicalPlan = (*memorySortGen4)(nil)
-
-type memorySortGen4 struct {
-	orderBy             []engine.OrderByParams
-	input               logicalPlan
-	truncateColumnCount int
-}
-
-func (m *memorySortGen4) Order() int {
-	panic("implement me")
-}
-
-func (m *memorySortGen4) ResultColumns() []*resultColumn {
-	panic("implement me")
-}
-
-func (m *memorySortGen4) Reorder(i int) {
-	panic("implement me")
-}
-
-func (m *memorySortGen4) Wireup(lp logicalPlan, jt *jointab) error {
-	panic("implement me")
-}
-
-func (m *memorySortGen4) WireupGen4(semTable *semantics.SemTable) error {
-	return m.input.WireupGen4(semTable)
-}
-
-func (m *memorySortGen4) SupplyVar(from, to int, col *sqlparser.ColName, varname string) {
-	panic("implement me")
-}
-
-func (m *memorySortGen4) SupplyCol(col *sqlparser.ColName) (rc *resultColumn, colNumber int) {
-	panic("implement me")
-}
-
-func (m *memorySortGen4) SupplyWeightString(colNumber int, alsoAddToGroupBy bool) (weightcolNumber int, err error) {
-	panic("implement me")
-}
-
-func (m *memorySortGen4) Primitive() engine.Primitive {
-	return &engine.MemorySort{
-		OrderBy:             m.orderBy,
-		Input:               m.input.Primitive(),
-		TruncateColumnCount: m.truncateColumnCount,
-	}
-}
-
-func (m *memorySortGen4) Inputs() []logicalPlan {
-	return []logicalPlan{m.input}
-}
-
-func (m *memorySortGen4) Rewrite(inputs ...logicalPlan) error {
-	if len(inputs) != 1 {
-		return vterrors.New(vtrpcpb.Code_INTERNAL, "[BUG]: expected only 1 input")
-	}
-	m.input = inputs[0]
-	return nil
-}
-
-func (m *memorySortGen4) ContainsTables() semantics.TableSet {
-	return m.input.ContainsTables()
-}
diff --git a/go/vt/vtgate/planbuilder/merge_sort.go b/go/vt/vtgate/planbuilder/merge_sort.go
index 2646d9b262..ee94cdd422 100644
--- a/go/vt/vtgate/planbuilder/merge_sort.go
+++ b/go/vt/vtgate/planbuilder/merge_sort.go
@@ -18,6 +18,7 @@ package planbuilder
 
 import (
 	"vitess.io/vitess/go/sqltypes"
+	"vitess.io/vitess/go/vt/sqlparser"
 	"vitess.io/vitess/go/vt/vtgate/engine"
 	"vitess.io/vitess/go/vt/vtgate/semantics"
 )
@@ -87,3 +88,12 @@ func (ms *mergeSort) Wireup(plan logicalPlan, jt *jointab) error {
 func (ms *mergeSort) WireupGen4(semTable *semantics.SemTable) error {
 	return ms.input.WireupGen4(semTable)
 }
+
+// OutputColumns implements the logicalPlan interface
+func (ms *mergeSort) OutputColumns() []sqlparser.SelectExpr {
+	outputCols := ms.input.OutputColumns()
+	if ms.truncateColumnCount > 0 {
+		return outputCols[:ms.truncateColumnCount]
+	}
+	return outputCols
+}
diff --git a/go/vt/vtgate/planbuilder/operator_transformers.go b/go/vt/vtgate/planbuilder/operator_transformers.go
index c79a8c047a..df56a179b1 100644
--- a/go/vt/vtgate/planbuilder/operator_transformers.go
+++ b/go/vt/vtgate/planbuilder/operator_transformers.go
@@ -55,6 +55,29 @@ func transformToLogicalPlan(ctx *plancontext.PlanningContext, op abstract.Physic
 		return transformCorrelatedSubQueryPlan(ctx, op)
 	case *physical.Derived:
 		return transformDerivedPlan(ctx, op)
+	case *physical.Filter:
+		plan, err := transformToLogicalPlan(ctx, op.Source)
+		if err != nil {
+			return nil, err
+		}
+		scl := &simpleConverterLookup{
+			canPushProjection: true,
+			ctx:               ctx,
+			plan:              plan,
+		}
+		ast := sqlparser.AndExpressions(op.Predicates...)
+		predicate, err := evalengine.Convert(ast, scl)
+		if err != nil {
+			return nil, err
+		}
+
+		return &filter{
+			logicalPlanCommon: newBuilderCommon(plan),
+			efilter: &engine.Filter{
+				Predicate:    predicate,
+				ASTPredicate: ast,
+			},
+		}, nil
 	}
 
 	return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "[BUG] unknown type encountered: %T (transformToLogicalPlan)", op)
diff --git a/go/vt/vtgate/planbuilder/ordered_aggregate.go b/go/vt/vtgate/planbuilder/ordered_aggregate.go
index 3f68ed0b61..bb7f058d8d 100644
--- a/go/vt/vtgate/planbuilder/ordered_aggregate.go
+++ b/go/vt/vtgate/planbuilder/ordered_aggregate.go
@@ -335,3 +335,15 @@ func (oa *orderedAggregate) Wireup(plan logicalPlan, jt *jointab) error {
 func (oa *orderedAggregate) WireupGen4(semTable *semantics.SemTable) error {
 	return oa.input.WireupGen4(semTable)
 }
+
+// OutputColumns implements the logicalPlan interface
+func (oa *orderedAggregate) OutputColumns() []sqlparser.SelectExpr {
+	outputCols := sqlparser.CloneSelectExprs(oa.input.OutputColumns())
+	for _, aggr := range oa.eaggr.Aggregates {
+		outputCols[aggr.Col] = &sqlparser.AliasedExpr{Expr: aggr.Expr, As: sqlparser.NewColIdent(aggr.Alias)}
+	}
+	if oa.eaggr.TruncateColumnCount > 0 {
+		return outputCols[:oa.eaggr.TruncateColumnCount]
+	}
+	return outputCols
+}
diff --git a/go/vt/vtgate/planbuilder/physical/route_planning.go b/go/vt/vtgate/planbuilder/physical/route_planning.go
index 3b85608ca1..168288bf69 100644
--- a/go/vt/vtgate/planbuilder/physical/route_planning.go
+++ b/go/vt/vtgate/planbuilder/physical/route_planning.go
@@ -78,6 +78,15 @@ func CreatePhysicalOperator(ctx *plancontext.PlanningContext, opTree abstract.Lo
 		return optimizeVindex(ctx, op)
 	case *abstract.Concatenate:
 		return optimizeUnion(ctx, op)
+	case *abstract.Filter:
+		src, err := CreatePhysicalOperator(ctx, op.Source)
+		if err != nil {
+			return nil, err
+		}
+		return &Filter{
+			Source:     src,
+			Predicates: op.Predicates,
+		}, nil
 	default:
 		return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "invalid operator tree: %T", op)
 	}
diff --git a/go/vt/vtgate/planbuilder/pullout_subquery.go b/go/vt/vtgate/planbuilder/pullout_subquery.go
index 68f3d0073e..7cf0a5985c 100644
--- a/go/vt/vtgate/planbuilder/pullout_subquery.go
+++ b/go/vt/vtgate/planbuilder/pullout_subquery.go
@@ -124,7 +124,7 @@ func (ps *pulloutSubquery) Rewrite(inputs ...logicalPlan) error {
 	return nil
 }
 
-// Solves implements the logicalPlan interface
+// ContainsTables implements the logicalPlan interface
 func (ps *pulloutSubquery) ContainsTables() semantics.TableSet {
 	return ps.underlying.ContainsTables().Merge(ps.subquery.ContainsTables())
 }
@@ -133,3 +133,8 @@ func (ps *pulloutSubquery) ContainsTables() semantics.TableSet {
 func (ps *pulloutSubquery) Inputs() []logicalPlan {
 	return []logicalPlan{ps.underlying, ps.subquery}
 }
+
+// OutputColumns implements the logicalPlan interface
+func (ps *pulloutSubquery) OutputColumns() []sqlparser.SelectExpr {
+	return ps.underlying.OutputColumns()
+}
diff --git a/go/vt/vtgate/planbuilder/route.go b/go/vt/vtgate/planbuilder/route.go
index ef43e7cec8..ee07b06adf 100644
--- a/go/vt/vtgate/planbuilder/route.go
+++ b/go/vt/vtgate/planbuilder/route.go
@@ -36,6 +36,7 @@ var _ logicalPlan = (*route)(nil)
 // are moved into this node, which will be used to build
 // the final SQL for this route.
 type route struct {
+	v3Plan
 	order int
 
 	// Redirect may point to another route if this route
@@ -65,9 +66,6 @@ type route struct {
 
 	// eroute is the primitive being built.
 	eroute *engine.Route
-
-	// tables keeps track of which tables this route is covering
-	tables semantics.TableSet
 }
 
 type tableSubstitution struct {
@@ -133,24 +131,6 @@ func (rb *route) SetLimit(limit *sqlparser.Limit) {
 	rb.Select.SetLimit(limit)
 }
 
-// WireupGen4 implements the logicalPlan interface
-func (rb *route) WireupGen4(semTable *semantics.SemTable) error {
-	rb.prepareTheAST()
-
-	rb.eroute.Query = sqlparser.String(rb.Select)
-
-	buffer := sqlparser.NewTrackedBuffer(sqlparser.FormatImpossibleQuery)
-	node := buffer.WriteNode(rb.Select)
-	query := node.ParsedQuery()
-	rb.eroute.FieldQuery = query.Query
-	return nil
-}
-
-// ContainsTables implements the logicalPlan interface
-func (rb *route) ContainsTables() semantics.TableSet {
-	return rb.tables
-}
-
 // Wireup implements the logicalPlan interface
 func (rb *route) Wireup(plan logicalPlan, jt *jointab) error {
 	// Precaution: update ERoute.Values only if it's not set already.
diff --git a/go/vt/vtgate/planbuilder/routeGen4.go b/go/vt/vtgate/planbuilder/routeGen4.go
index e0a07410b0..f9e59c4a5b 100644
--- a/go/vt/vtgate/planbuilder/routeGen4.go
+++ b/go/vt/vtgate/planbuilder/routeGen4.go
@@ -79,6 +79,11 @@ func (rb *routeGen4) ContainsTables() semantics.TableSet {
 	return rb.tables
 }
 
+// OutputColumns implements the logicalPlan interface
+func (rb *routeGen4) OutputColumns() []sqlparser.SelectExpr {
+	return sqlparser.GetFirstSelect(rb.Select).SelectExprs
+}
+
 // prepareTheAST does minor fixups of the SELECT struct before producing the query string
 func (rb *routeGen4) prepareTheAST() {
 	_ = sqlparser.Walk(func(node sqlparser.SQLNode) (bool, error) {
diff --git a/go/vt/vtgate/planbuilder/semi_join.go b/go/vt/vtgate/planbuilder/semi_join.go
index fb4a42df17..9a7968f2c1 100644
--- a/go/vt/vtgate/planbuilder/semi_join.go
+++ b/go/vt/vtgate/planbuilder/semi_join.go
@@ -30,6 +30,7 @@ var _ logicalPlan = (*semiJoin)(nil)
 // This gets built if a rhs is correlated and can
 // be pulled out but requires some variables to be supplied from outside.
 type semiJoin struct {
+	gen4Plan
 	rhs  logicalPlan
 	lhs  logicalPlan
 	vars map[string]int
@@ -45,16 +46,6 @@ func newSemiJoin(lhs, rhs logicalPlan, vars map[string]int) *semiJoin {
 	}
 }
 
-// Order implements the logicalPlan interface
-func (ps *semiJoin) Order() int {
-	panic("[BUG]: should not be called. This is a Gen4 primitive")
-}
-
-// Reorder implements the logicalPlan interface
-func (ps *semiJoin) Reorder(order int) {
-	panic("[BUG]: should not be called. This is a Gen4 primitive")
-}
-
 // Primitive implements the logicalPlan interface
 func (ps *semiJoin) Primitive() engine.Primitive {
 	return &engine.SemiJoin{
@@ -65,16 +56,6 @@ func (ps *semiJoin) Primitive() engine.Primitive {
 	}
 }
 
-// ResultColumns implements the logicalPlan interface
-func (ps *semiJoin) ResultColumns() []*resultColumn {
-	panic("[BUG]: should not be called. This is a Gen4 primitive")
-}
-
-// Wireup implements the logicalPlan interface
-func (ps *semiJoin) Wireup(plan logicalPlan, jt *jointab) error {
-	panic("[BUG]: should not be called. This is a Gen4 primitive")
-}
-
 // WireupGen4 implements the logicalPlan interface
 func (ps *semiJoin) WireupGen4(semTable *semantics.SemTable) error {
 	if err := ps.lhs.WireupGen4(semTable); err != nil {
@@ -83,21 +64,6 @@ func (ps *semiJoin) WireupGen4(semTable *semantics.SemTable) error {
 	return ps.rhs.WireupGen4(semTable)
 }
 
-// SupplyVar implements the logicalPlan interface
-func (ps *semiJoin) SupplyVar(from, to int, col *sqlparser.ColName, varname string) {
-	panic("[BUG]: should not be called. This is a Gen4 primitive")
-}
-
-// SupplyCol implements the logicalPlan interface
-func (ps *semiJoin) SupplyCol(col *sqlparser.ColName) (rc *resultColumn, colNumber int) {
-	panic("[BUG]: should not be called. This is a Gen4 primitive")
-}
-
-// SupplyWeightString implements the logicalPlan interface
-func (ps *semiJoin) SupplyWeightString(colNumber int, alsoAddToGroupBy bool) (weightcolNumber int, err error) {
-	panic("[BUG]: should not be called. This is a Gen4 primitive")
-}
-
 // Rewrite implements the logicalPlan interface
 func (ps *semiJoin) Rewrite(inputs ...logicalPlan) error {
 	if len(inputs) != 2 {
@@ -117,3 +83,8 @@ func (ps *semiJoin) ContainsTables() semantics.TableSet {
 func (ps *semiJoin) Inputs() []logicalPlan {
 	return []logicalPlan{ps.lhs, ps.rhs}
 }
+
+// OutputColumns implements the logicalPlan interface
+func (ps *semiJoin) OutputColumns() []sqlparser.SelectExpr {
+	return ps.lhs.OutputColumns()
+}
diff --git a/go/vt/vtgate/planbuilder/simple_projection.go b/go/vt/vtgate/planbuilder/simple_projection.go
index 9580fe0b57..8811ccc7a3 100644
--- a/go/vt/vtgate/planbuilder/simple_projection.go
+++ b/go/vt/vtgate/planbuilder/simple_projection.go
@@ -94,3 +94,13 @@ func (sq *simpleProjection) SupplyCol(col *sqlparser.ColName) (rc *resultColumn,
 	sq.resultColumns = append(sq.resultColumns, &resultColumn{column: c})
 	return rc, len(sq.resultColumns) - 1
 }
+
+// OutputColumns implements the logicalPlan interface
+func (sq *simpleProjection) OutputColumns() []sqlparser.SelectExpr {
+	exprs := make([]sqlparser.SelectExpr, 0, len(sq.eSimpleProj.Cols))
+	outputCols := sq.input.OutputColumns()
+	for _, colID := range sq.eSimpleProj.Cols {
+		exprs = append(exprs, outputCols[colID])
+	}
+	return exprs
+}
diff --git a/go/vt/vtgate/planbuilder/sql_calc_found_rows.go b/go/vt/vtgate/planbuilder/sql_calc_found_rows.go
index 4a4c589541..e7e017a8f2 100644
--- a/go/vt/vtgate/planbuilder/sql_calc_found_rows.go
+++ b/go/vt/vtgate/planbuilder/sql_calc_found_rows.go
@@ -110,3 +110,8 @@ func (s *sqlCalcFoundRows) Rewrite(inputs ...logicalPlan) error {
 func (s *sqlCalcFoundRows) Inputs() []logicalPlan {
 	return []logicalPlan{s.LimitQuery, s.CountQuery}
 }
+
+// OutputColumns implements the logicalPlan interface
+func (s *sqlCalcFoundRows) OutputColumns() []sqlparser.SelectExpr {
+	return s.LimitQuery.OutputColumns()
+}
diff --git a/go/vt/vtgate/planbuilder/vindex_func.go b/go/vt/vtgate/planbuilder/vindex_func.go
index 70c724cb25..5b951ec40d 100644
--- a/go/vt/vtgate/planbuilder/vindex_func.go
+++ b/go/vt/vtgate/planbuilder/vindex_func.go
@@ -47,6 +47,15 @@ type vindexFunc struct {
 	eVindexFunc *engine.VindexFunc
 }
 
+var colnames = []string{
+	"id",
+	"keyspace_id",
+	"range_start",
+	"range_end",
+	"hex_keyspace_id",
+	"shard",
+}
+
 func newVindexFunc(alias sqlparser.TableName, vindex vindexes.SingleColumn) (*vindexFunc, *symtab) {
 	vf := &vindexFunc{
 		order: 1,
@@ -61,13 +70,9 @@ func newVindexFunc(alias sqlparser.TableName, vindex vindexes.SingleColumn) (*vi
 		origin: vf,
 	}
 
-	// Column names are hard-coded to id, keyspace_id
-	t.addColumn(sqlparser.NewColIdent("id"), &column{origin: vf})
-	t.addColumn(sqlparser.NewColIdent("keyspace_id"), &column{origin: vf})
-	t.addColumn(sqlparser.NewColIdent("range_start"), &column{origin: vf})
-	t.addColumn(sqlparser.NewColIdent("range_end"), &column{origin: vf})
-	t.addColumn(sqlparser.NewColIdent("hex_keyspace_id"), &column{origin: vf})
-	t.addColumn(sqlparser.NewColIdent("shard"), &column{origin: vf})
+	for _, colName := range colnames {
+		t.addColumn(sqlparser.NewColIdent(colName), &column{origin: vf})
+	}
 	t.isAuthoritative = true
 
 	st := newSymtab()
@@ -222,3 +227,12 @@ func vindexColumnToIndex(column *sqlparser.ColName) int {
 		return -1
 	}
 }
+
+// OutputColumns implements the logicalPlan interface
+func (vf *vindexFunc) OutputColumns() []sqlparser.SelectExpr {
+	exprs := make([]sqlparser.SelectExpr, 0, len(colnames))
+	for _, field := range vf.eVindexFunc.Fields {
+		exprs = append(exprs, &sqlparser.AliasedExpr{Expr: sqlparser.NewColName(field.Name)})
+	}
+	return exprs
+}


Test patch:
diff --git a/go/test/endtoend/vtgate/misc_test.go b/go/test/endtoend/vtgate/misc_test.go
index 5d1ce979a5..d5426009e0 100644
--- a/go/test/endtoend/vtgate/misc_test.go
+++ b/go/test/endtoend/vtgate/misc_test.go
@@ -789,3 +789,19 @@ func TestCharsetIntro(t *testing.T) {
 	qr := utils.Exec(t, conn, "select id1 from t4 where id2 = _utf8mb4'xyz'")
 	require.EqualValues(t, 0, qr.RowsAffected)
 }
+
+func TestFilterAfterLeftJoin(t *testing.T) {
+	defer cluster.PanicHandler(t)
+	conn, err := mysql.Connect(context.Background(), &vtParams)
+	require.NoError(t, err)
+	defer conn.Close()
+
+	utils.Exec(t, conn, "delete from t1")
+	defer utils.Exec(t, conn, "delete from t1")
+	utils.Exec(t, conn, "insert into t1 (id1,id2) values (1, 10)")
+	utils.Exec(t, conn, "insert into t1 (id1,id2) values (2, 3)")
+	utils.Exec(t, conn, "insert into t1 (id1,id2) values (3, 2)")
+
+	query := "select /* GEN4_COMPARE_ONLY_GEN4 */ A.id1, A.id2 from t1 as A left join t1 as B on A.id1 = B.id2 WHERE B.id1 IS NULL"
+	utils.AssertMatches(t, conn, query, `[[INT64(1) INT64(10)]]`)
+}
diff --git a/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go b/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go
index 197892f9ef..9c4d465b47 100644
--- a/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go
+++ b/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go
@@ -118,7 +118,7 @@ func TestEqualFilterOnScatter(t *testing.T) {
 			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a = 5.00", `[[INT64(5)]]`)
 			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a, val1 from aggr_test group by val1 having a = 1.00", `[[INT64(1) VARCHAR("a")] [INT64(1) VARCHAR("b")] [INT64(1) VARCHAR("c")] [INT64(1) VARCHAR("d")] [INT64(1) VARCHAR("e")]]`)
 
-			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) = 5", `expr cannot be converted, not supported: *sqlparser.FuncExpr`) // will fail since `count(*)` is a FuncExpr
+			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) = 5", `expr cannot be converted, not supported`) // will fail since `count(*)` is a FuncExpr
 		})
 	}
 }
@@ -142,16 +142,16 @@ func TestNotEqualFilterOnScatter(t *testing.T) {
 		t.Run(workload, func(t *testing.T) {
 			utils.Exec(t, conn, fmt.Sprintf("set workload = '%s'", workload))
 
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != 5", `[]`)               // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having 5 != a", `[]`)               // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != a", `[]`)               // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != 3+2", `[]`)             // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != 1", `[[INT64(5)]]`)     // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != \"1\"", `[[INT64(5)]]`) // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != \"5\"", `[]`)           // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != 5.00", `[]`)            // having clause
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != 5", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having 5 != a", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != a", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != 3+2", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != 1", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != \"1\"", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != \"5\"", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a != 5.00", `[]`)
 
-			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) != 5", `expr cannot be converted, not supported: *sqlparser.FuncExpr`) // will fail since `count(*)` is a FuncExpr
+			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) != 5", `expr cannot be converted, not supported`) // will fail since `count(*)` is a FuncExpr
 		})
 	}
 }
@@ -174,16 +174,16 @@ func TestLessFilterOnScatter(t *testing.T) {
 	for _, workload := range workloads {
 		t.Run(workload, func(t *testing.T) {
 			utils.Exec(t, conn, fmt.Sprintf("set workload = '%s'", workload))
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < 10", `[[INT64(5)]]`)     // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having 1 < a", `[[INT64(5)]]`)      // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < a", `[]`)                // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < 3+2", `[]`)              // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < 1", `[]`)                // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < \"10\"", `[[INT64(5)]]`) // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < \"5\"", `[]`)            // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < 6.00", `[[INT64(5)]]`)   // having clause
-
-			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) < 5", `expr cannot be converted, not supported: *sqlparser.FuncExpr`) // will fail since `count(*)` is a FuncExpr
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < 10", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having 1 < a", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < a", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < 3+2", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < 1", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < \"10\"", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < \"5\"", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a < 6.00", `[[INT64(5)]]`)
+
+			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) < 5", `expr cannot be converted, not supported`) // will fail since `count(*)` is a FuncExpr
 		})
 	}
 }
@@ -207,16 +207,16 @@ func TestLessEqualFilterOnScatter(t *testing.T) {
 		t.Run(workload, func(t *testing.T) {
 			utils.Exec(t, conn, fmt.Sprintf("set workload = '%s'", workload))
 
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= 10", `[[INT64(5)]]`)     // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having 1 <= a", `[[INT64(5)]]`)      // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= a", `[[INT64(5)]]`)      // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= 3+2", `[[INT64(5)]]`)    // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= 1", `[]`)                // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= \"10\"", `[[INT64(5)]]`) // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= \"5\"", `[[INT64(5)]]`)  // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= 5.00", `[[INT64(5)]]`)   // having clause
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= 10", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having 1 <= a", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= a", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= 3+2", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= 1", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= \"10\"", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= \"5\"", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a <= 5.00", `[[INT64(5)]]`)
 
-			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) <= 5", `expr cannot be converted, not supported: *sqlparser.FuncExpr`) // will fail since `count(*)` is a FuncExpr
+			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) <= 5", `expr cannot be converted, not supported`) // will fail since `count(*)` is a FuncExpr
 		})
 	}
 }
@@ -240,16 +240,16 @@ func TestGreaterFilterOnScatter(t *testing.T) {
 		t.Run(workload, func(t *testing.T) {
 			utils.Exec(t, conn, fmt.Sprintf("set workload = '%s'", workload))
 
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > 1", `[[INT64(5)]]`)     // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having 1 > a", `[]`)               // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > a", `[]`)               // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > 3+1", `[[INT64(5)]]`)   // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > 10", `[]`)              // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > \"1\"", `[[INT64(5)]]`) // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > \"5\"", `[]`)           // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > 4.00", `[[INT64(5)]]`)  // having clause
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > 1", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having 1 > a", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > a", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > 3+1", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > 10", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > \"1\"", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > \"5\"", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a > 4.00", `[[INT64(5)]]`)
 
-			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) > 5", `expr cannot be converted, not supported: *sqlparser.FuncExpr`) // will fail since `count(*)` is a FuncExpr
+			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) > 5", `expr cannot be converted, not supported`) // will fail since `count(*)` is a FuncExpr
 		})
 	}
 }
@@ -273,16 +273,16 @@ func TestGreaterEqualFilterOnScatter(t *testing.T) {
 		t.Run(workload, func(t *testing.T) {
 			utils.Exec(t, conn, fmt.Sprintf("set workload = '%s'", workload))
 
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= 1", `[[INT64(5)]]`)     // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having 1 >= a", `[]`)               // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= a", `[[INT64(5)]]`)     // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= 3+2", `[[INT64(5)]]`)   // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= 10", `[]`)              // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= \"1\"", `[[INT64(5)]]`) // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= \"5\"", `[[INT64(5)]]`) // having clause
-			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= 5.00", `[[INT64(5)]]`)  // having clause
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= 1", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having 1 >= a", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= a", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= 3+2", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= 10", `[]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= \"1\"", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= \"5\"", `[[INT64(5)]]`)
+			utils.AssertMatches(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ count(*) as a from aggr_test having a >= 5.00", `[[INT64(5)]]`)
 
-			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) >= 5", `expr cannot be converted, not supported: *sqlparser.FuncExpr`) // will fail since `count(*)` is a FuncExpr
+			utils.AssertContainsError(t, conn, "select /* GEN4_COMPARE_ONLY_GEN4 */ 1 from aggr_test having count(*) >= 5", `expr cannot be converted, not supported`) // will fail since `count(*)` is a FuncExpr
 		})
 	}
 }
diff --git a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
index 991440323f..b194b4c707 100644
--- a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
@@ -946,7 +946,7 @@ Gen4 plan same as above
 }
 Gen4 error: In aggregated query without GROUP BY, expression of SELECT list contains nonaggregated column 'id'; this is incompatible with sql_mode=only_full_group_by
 
-# scatter aggregate using distinct
+# scatter aggregate using distinctdistinct
 "select distinct col from user"
 {
   "QueryType": "SELECT",
@@ -2986,3 +2986,80 @@ Gen4 plan same as above
     ]
   }
 }
+
+# Less Equal filter on scatter with grouping
+"select col, count(*) a from user group by col having a <= 10"
+"unsupported: filtering on results of aggregates"
+{
+  "QueryType": "SELECT",
+  "Original": "select col, count(*) a from user group by col having a \u003c= 10",
+  "Instructions": {
+    "OperatorType": "Filter",
+    "Predicate": "a \u003c= 10",
+    "Inputs": [
+      {
+        "OperatorType": "Aggregate",
+        "Variant": "Ordered",
+        "Aggregates": "count(1) AS a",
+        "GroupBy": "0",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select col, count(*) as a from `user` where 1 != 1 group by col",
+            "OrderBy": "0 ASC",
+            "Query": "select col, count(*) as a from `user` group by col order by col asc",
+            "Table": "`user`"
+          }
+        ]
+      }
+    ]
+  }
+}
+
+# We should be able to find grouping keys on ordered aggregates
+"select count(*) as a, val1 from user group by val1 having a = 1.00"
+"unsupported: filtering on results of aggregates"
+{
+  "QueryType": "SELECT",
+  "Original": "select count(*) as a, val1 from user group by val1 having a = 1.00",
+  "Instructions": {
+    "OperatorType": "SimpleProjection",
+    "Columns": [
+      0,
+      1
+    ],
+    "Inputs": [
+      {
+        "OperatorType": "Filter",
+        "Predicate": "a = 1.00",
+        "Inputs": [
+          {
+            "OperatorType": "Aggregate",
+            "Variant": "Ordered",
+            "Aggregates": "count(0) AS a",
+            "GroupBy": "(1|2)",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectScatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select count(*) as a, val1, weight_string(val1) from `user` where 1 != 1 group by val1, weight_string(val1)",
+                "OrderBy": "(1|2) ASC",
+                "Query": "select count(*) as a, val1, weight_string(val1) from `user` group by val1, weight_string(val1) order by val1 asc",
+                "Table": "`user`"
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
diff --git a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
index 2bdf8ec110..f12e34dc52 100644
--- a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
@@ -3184,7 +3184,62 @@ Gen4 plan same as above
 # this query lead to a nil pointer error
 "select user.id from user left join user_extra on user.col = user_extra.col where foo(user_extra.foobar)"
 "unsupported: cross-shard left join and where clause"
-Gen4 plan same as above
+Gen4 error: expr cannot be converted, not supported: foo(user_extra.foobar)
+
+# filter after outer join
+"select user.id from user left join user_extra on user.col = user_extra.col where user_extra.id is null"
+"unsupported: cross-shard left join and where clause"
+{
+  "QueryType": "SELECT",
+  "Original": "select user.id from user left join user_extra on user.col = user_extra.col where user_extra.id is null",
+  "Instructions": {
+    "OperatorType": "SimpleProjection",
+    "Columns": [
+      1
+    ],
+    "Inputs": [
+      {
+        "OperatorType": "Filter",
+        "Predicate": "user_extra.id is null",
+        "Inputs": [
+          {
+            "OperatorType": "Join",
+            "Variant": "LeftJoin",
+            "JoinColumnIndexes": "1,-2",
+            "JoinVars": {
+              "user_col": 0
+            },
+            "TableName": "`user`_user_extra",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectScatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select `user`.col, `user`.id from `user` where 1 != 1",
+                "Query": "select `user`.col, `user`.id from `user`",
+                "Table": "`user`"
+              },
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectScatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select user_extra.id from user_extra where 1 != 1",
+                "Query": "select user_extra.id from user_extra where user_extra.col = :user_col",
+                "Table": "user_extra"
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
 
 #subquery on other table
 "select distinct user.id, user.col from user where user.col in (select id from music where col2 = 'a')"
@@ -3857,4 +3912,4 @@ Gen4 plan same as above
     "Table": "unsharded_a, unsharded_b"
   }
 }
-Gen4 error: unsupported: cross-shard left join and where clause
+Gen4 error: expr cannot be converted, not supported: coalesce(unsharded_b.col, 4) = 5
diff --git a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
index 8e2421a292..3276bd5200 100644
--- a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
@@ -71,7 +71,7 @@ Gen4 plan same as above
 # left join where clauses #2
 "select user.id from user left join user_extra on user.col = user_extra.col where coalesce(user_extra.col, 4) = 5"
 "unsupported: cross-shard left join and where clause"
-Gen4 plan same as above
+Gen4 error: expr cannot be converted, not supported: coalesce(user_extra.col, 4) = 5
 
 # * expresson not allowed for cross-shard joins
 "select * from user join user_extra"
@@ -510,11 +510,11 @@ Gen4 plan same as above
 # TODO this should be planned without using OA and MS
 "select u.id from user u join user_extra ue on ue.id = u.id group by u.id having count(u.name) = 3"
 "unsupported: cross-shard query with aggregates"
-Gen4 error: expr cannot be converted, not supported: *sqlparser.FuncExpr
+Gen4 error: expr cannot be converted, not supported: count(u.`name`) = 3
 
 "select (select 1 from user u having count(ue.col) > 10) from user_extra ue"
 "symbol ue.col not found in subquery"
-Gen4 error: expr cannot be converted, not supported: *sqlparser.FuncExpr
+Gen4 error: expr cannot be converted, not supported: count(ue.col) > 10
 
 # aggregation filtering by having on a route with no group by
 "select 1 from user having count(id) = 10"
@@ -533,7 +533,7 @@ Gen4 error: expr cannot be converted, not supported: *sqlparser.FuncExpr
     "Table": "`user`"
   }
 }
-Gen4 error: expr cannot be converted, not supported: *sqlparser.FuncExpr
+Gen4 error: expr cannot be converted, not supported: count(id) = 10
 
 # aggregation filtering by having on a route with no group by with non-unique vindex filter
 "select 1 from user having count(id) = 10 and name = 'a'"
@@ -556,7 +556,7 @@ Gen4 error: expr cannot be converted, not supported: *sqlparser.FuncExpr
     "Vindex": "name_user_map"
   }
 }
-Gen4 error: expr cannot be converted, not supported: *sqlparser.FuncExpr
+Gen4 error: expr cannot be converted, not supported: count(id) = 10
 
 # subquery of information_schema with itself and star expression in outer select
 "select a.*, u.id from information_schema.a a, user u where a.id in (select * from information_schema.b)"
@@ -636,7 +636,7 @@ Gen4 plan same as above
 # Equal filter with hexadecimal value
 "select count(*) a from user having a = 0x01"
 "unsupported: filtering on results of aggregates"
-Gen4 error: expr cannot be converted, not supported: hexadecimal value: 0x01
+Gen4 error: expr cannot be converted, not supported: a = 0x01
 
 # scatter aggregate with complex select list (can't build order by)
 "select distinct a+1 from user"