Repository: vitessio/vitess
Pull request #8904 (2021-09-29 13:23:02)

Gen4: Even more fixes
## Description
More gen4 small fixes

- Grouping on top of PulloutSubqueries
- UNION between SELECT * queries
- Compact joins - after creating the operator tree, there might have opened up new opportunities to merge QueryGraphs
- Merge UNIONs more aggressively - when it's a UNION DISTINCT, we can reorder the SELECTs to see if there are more chances to merge routes together

## 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/join.go b/go/vt/vtgate/engine/join.go
index 55d0742fef..2424c271cd 100644
--- a/go/vt/vtgate/engine/join.go
+++ b/go/vt/vtgate/engine/join.go
@@ -263,7 +263,7 @@ func (jn *Join) description() PrimitiveDescription {
 		"JoinColumnIndexes": strings.Trim(strings.Join(strings.Fields(fmt.Sprint(jn.Cols)), ","), "[]"),
 	}
 	if len(jn.Vars) > 0 {
-		other["JoinVars"] = jn.Vars
+		other["JoinVars"] = orderedStringIntMap(jn.Vars)
 	}
 	return PrimitiveDescription{
 		OperatorType: "Join",
diff --git a/go/vt/vtgate/engine/plan_description.go b/go/vt/vtgate/engine/plan_description.go
index f3a0b21494..73bb693229 100644
--- a/go/vt/vtgate/engine/plan_description.go
+++ b/go/vt/vtgate/engine/plan_description.go
@@ -133,3 +133,61 @@ func PrimitiveToPlanDescription(in Primitive) PrimitiveDescription {
 
 	return this
 }
+
+func orderedStringIntMap(in map[string]int) orderedMap {
+	result := make(orderedMap, 0, len(in))
+	for k, v := range in {
+		result = append(result, keyVal{key: k, val: v})
+	}
+	sort.Sort(result)
+	return result
+}
+
+type keyVal struct {
+	key string
+	val interface{}
+}
+
+// Define an ordered, sortable map
+type orderedMap []keyVal
+
+func (m orderedMap) Len() int {
+	return len(m)
+}
+
+func (m orderedMap) Less(i, j int) bool {
+	return m[i].key < m[j].key
+}
+
+func (m orderedMap) Swap(i, j int) {
+	m[i], m[j] = m[j], m[i]
+}
+
+var _ sort.Interface = (orderedMap)(nil)
+
+func (m orderedMap) MarshalJSON() ([]byte, error) {
+	var buf bytes.Buffer
+
+	buf.WriteString("{")
+	for i, kv := range m {
+		if i != 0 {
+			buf.WriteString(",")
+		}
+		// marshal key
+		key, err := json.Marshal(kv.key)
+		if err != nil {
+			return nil, err
+		}
+		buf.Write(key)
+		buf.WriteString(":")
+		// marshal value
+		val, err := json.Marshal(kv.val)
+		if err != nil {
+			return nil, err
+		}
+		buf.Write(val)
+	}
+
+	buf.WriteString("}")
+	return buf.Bytes(), nil
+}
diff --git a/go/vt/vtgate/planbuilder/abstract/concatenate.go b/go/vt/vtgate/planbuilder/abstract/concatenate.go
index a09a8370ab..48e40bff16 100644
--- a/go/vt/vtgate/planbuilder/abstract/concatenate.go
+++ b/go/vt/vtgate/planbuilder/abstract/concatenate.go
@@ -65,7 +65,7 @@ func (c *Concatenate) CheckValid() error {
 }
 
 // Compact implements the Operator interface
-func (c *Concatenate) Compact() Operator {
+func (c *Concatenate) Compact(*semantics.SemTable) (Operator, error) {
 	var newSources []Operator
 	var newSels []*sqlparser.Select
 	for i, source := range c.Sources {
@@ -90,5 +90,5 @@ func (c *Concatenate) Compact() Operator {
 	}
 	c.Sources = newSources
 	c.SelectStmts = newSels
-	return c
+	return c, nil
 }
diff --git a/go/vt/vtgate/planbuilder/abstract/derived.go b/go/vt/vtgate/planbuilder/abstract/derived.go
index 9620fd58f8..394ec0245a 100644
--- a/go/vt/vtgate/planbuilder/abstract/derived.go
+++ b/go/vt/vtgate/planbuilder/abstract/derived.go
@@ -65,6 +65,6 @@ func (d *Derived) CheckValid() error {
 }
 
 // Compact implements the Operator interface
-func (d *Derived) Compact() Operator {
-	return d
+func (d *Derived) Compact(*semantics.SemTable) (Operator, error) {
+	return d, nil
 }
diff --git a/go/vt/vtgate/planbuilder/abstract/join.go b/go/vt/vtgate/planbuilder/abstract/join.go
index 67a5152af5..c8a802a7be 100644
--- a/go/vt/vtgate/planbuilder/abstract/join.go
+++ b/go/vt/vtgate/planbuilder/abstract/join.go
@@ -103,6 +103,26 @@ func (j *Join) CheckValid() error {
 }
 
 // Compact implements the Operator interface
-func (j *Join) Compact() Operator {
-	return j
+func (j *Join) Compact(semTable *semantics.SemTable) (Operator, error) {
+	if j.LeftJoin {
+		// we can't merge outer joins into a single QG
+		return j, nil
+	}
+
+	lqg, lok := j.LHS.(*QueryGraph)
+	rqg, rok := j.RHS.(*QueryGraph)
+	if !lok || !rok {
+		return j, nil
+	}
+
+	op := &QueryGraph{
+		Tables:     append(lqg.Tables, rqg.Tables...),
+		innerJoins: append(lqg.innerJoins, rqg.innerJoins...),
+		NoDeps:     sqlparser.AndExpressions(lqg.NoDeps, rqg.NoDeps),
+	}
+	err := op.collectPredicate(j.Predicate, semTable)
+	if err != nil {
+		return nil, err
+	}
+	return op, nil
 }
diff --git a/go/vt/vtgate/planbuilder/abstract/operator.go b/go/vt/vtgate/planbuilder/abstract/operator.go
index 1727ede5d6..57574ed8ad 100644
--- a/go/vt/vtgate/planbuilder/abstract/operator.go
+++ b/go/vt/vtgate/planbuilder/abstract/operator.go
@@ -50,7 +50,7 @@ type (
 		CheckValid() error
 
 		// Compact will optimise the operator tree into a smaller but equivalent version
-		Compact() Operator
+		Compact(semTable *semantics.SemTable) (Operator, error)
 	}
 )
 
@@ -170,7 +170,7 @@ func CreateOperatorFromAST(selStmt sqlparser.SelectStatement, semTable *semantic
 	if err != nil {
 		return nil, err
 	}
-	return op.Compact(), nil
+	return op.Compact(semTable)
 }
 
 func createOperatorFromUnion(node *sqlparser.Union, semTable *semantics.SemTable) (Operator, error) {
@@ -261,15 +261,9 @@ func createJoin(LHS, RHS Operator) Operator {
 	if lok && rok {
 		op := &QueryGraph{
 			Tables:     append(lqg.Tables, rqg.Tables...),
-			innerJoins: map[semantics.TableSet][]sqlparser.Expr{},
+			innerJoins: append(lqg.innerJoins, rqg.innerJoins...),
 			NoDeps:     sqlparser.AndExpressions(lqg.NoDeps, rqg.NoDeps),
 		}
-		for k, v := range lqg.innerJoins {
-			op.innerJoins[k] = v
-		}
-		for k, v := range rqg.innerJoins {
-			op.innerJoins[k] = v
-		}
 		return op
 	}
 	return &Join{LHS: LHS, RHS: RHS}
diff --git a/go/vt/vtgate/planbuilder/abstract/querygraph.go b/go/vt/vtgate/planbuilder/abstract/querygraph.go
index 770247c263..7f5db07730 100644
--- a/go/vt/vtgate/planbuilder/abstract/querygraph.go
+++ b/go/vt/vtgate/planbuilder/abstract/querygraph.go
@@ -36,12 +36,17 @@ type (
 		Tables []*QueryTable
 
 		// innerJoins contains the predicates that need multiple Tables
-		innerJoins map[semantics.TableSet][]sqlparser.Expr
+		innerJoins []*innerJoin
 
 		// NoDeps contains the predicates that can be evaluated anywhere.
 		NoDeps sqlparser.Expr
 	}
 
+	innerJoin struct {
+		deps  semantics.TableSet
+		exprs []sqlparser.Expr
+	}
+
 	// QueryTable is a single FROM table, including all predicates particular to this table
 	QueryTable struct {
 		TableID     semantics.TableSet
@@ -77,7 +82,8 @@ func (qg *QueryGraph) TableID() semantics.TableSet {
 // GetPredicates returns the predicates that are applicable for the two given TableSets
 func (qg *QueryGraph) GetPredicates(lhs, rhs semantics.TableSet) []sqlparser.Expr {
 	var allExprs []sqlparser.Expr
-	for tableSet, exprs := range qg.innerJoins {
+	for _, join := range qg.innerJoins {
+		tableSet, exprs := join.deps, join.exprs
 		if tableSet.IsSolvedBy(lhs.Merge(rhs)) &&
 			tableSet.IsOverlapping(rhs) &&
 			tableSet.IsOverlapping(lhs) {
@@ -88,9 +94,7 @@ func (qg *QueryGraph) GetPredicates(lhs, rhs semantics.TableSet) []sqlparser.Exp
 }
 
 func newQueryGraph() *QueryGraph {
-	return &QueryGraph{
-		innerJoins: map[semantics.TableSet][]sqlparser.Expr{},
-	}
+	return &QueryGraph{}
 }
 
 func (qg *QueryGraph) collectPredicates(sel *sqlparser.Select, semTable *semantics.SemTable) error {
@@ -105,6 +109,28 @@ func (qg *QueryGraph) collectPredicates(sel *sqlparser.Select, semTable *semanti
 	return nil
 }
 
+func (qg *QueryGraph) getPredicateByDeps(ts semantics.TableSet) ([]sqlparser.Expr, bool) {
+	for _, join := range qg.innerJoins {
+		if join.deps == ts {
+			return join.exprs, true
+		}
+	}
+	return nil, false
+}
+func (qg *QueryGraph) addJoinPredicates(ts semantics.TableSet, expr sqlparser.Expr) {
+	for _, join := range qg.innerJoins {
+		if join.deps == ts {
+			join.exprs = append(join.exprs, expr)
+			return
+		}
+	}
+
+	qg.innerJoins = append(qg.innerJoins, &innerJoin{
+		deps:  ts,
+		exprs: []sqlparser.Expr{expr},
+	})
+}
+
 func (qg *QueryGraph) collectPredicate(predicate sqlparser.Expr, semTable *semantics.SemTable) error {
 	deps := semTable.RecursiveDeps(predicate)
 	switch deps.NumberOfTables() {
@@ -116,13 +142,7 @@ func (qg *QueryGraph) collectPredicate(predicate sqlparser.Expr, semTable *seman
 			return vterrors.Errorf(vtrpcpb.Code_INTERNAL, "table %v for predicate %v not found", deps, sqlparser.String(predicate))
 		}
 	default:
-		allPredicates, found := qg.innerJoins[deps]
-		if found {
-			allPredicates = append(allPredicates, predicate)
-		} else {
-			allPredicates = []sqlparser.Expr{predicate}
-		}
-		qg.innerJoins[deps] = allPredicates
+		qg.addJoinPredicates(deps, predicate)
 	}
 	return nil
 }
@@ -151,7 +171,8 @@ func (qg *QueryGraph) addNoDepsPredicate(predicate sqlparser.Expr) {
 // UnsolvedPredicates implements the Operator interface
 func (qg *QueryGraph) UnsolvedPredicates(_ *semantics.SemTable) []sqlparser.Expr {
 	var result []sqlparser.Expr
-	for set, exprs := range qg.innerJoins {
+	for _, join := range qg.innerJoins {
+		set, exprs := join.deps, join.exprs
 		if !set.IsSolvedBy(qg.TableID()) {
 			result = append(result, exprs...)
 		}
@@ -165,6 +186,6 @@ func (qg *QueryGraph) CheckValid() error {
 }
 
 // Compact implements the Operator interface
-func (qg *QueryGraph) Compact() Operator {
-	return qg
+func (qg *QueryGraph) Compact(*semantics.SemTable) (Operator, error) {
+	return qg, nil
 }
diff --git a/go/vt/vtgate/planbuilder/abstract/queryprojection.go b/go/vt/vtgate/planbuilder/abstract/queryprojection.go
index 32ef786d25..5514821a77 100644
--- a/go/vt/vtgate/planbuilder/abstract/queryprojection.go
+++ b/go/vt/vtgate/planbuilder/abstract/queryprojection.go
@@ -18,6 +18,7 @@ package abstract
 
 import (
 	"encoding/json"
+	"strings"
 
 	vtrpcpb "vitess.io/vitess/go/vt/proto/vtrpc"
 	"vitess.io/vitess/go/vt/sqlparser"
@@ -99,9 +100,11 @@ func CreateQPFromSelect(sel *sqlparser.Select, semTable *semantics.SemTable) (*Q
 		if err != nil {
 			return nil, err
 		}
-		if sqlparser.ContainsAggregation(weightStrExpr) {
-			return nil, vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.WrongGroupField, "Can't group on '%s'", sqlparser.String(expr))
+		err = checkForInvalidGroupingExpressions(weightStrExpr)
+		if err != nil {
+			return nil, err
 		}
+
 		qp.GroupByExprs = append(qp.GroupByExprs, GroupBy{Inner: expr, WeightStrExpr: weightStrExpr})
 	}
 
@@ -339,3 +342,17 @@ func (qp *QueryProjection) NeedsDistinct() bool {
 	}
 	return true
 }
+
+func checkForInvalidGroupingExpressions(expr sqlparser.Expr) error {
+	return sqlparser.Walk(func(node sqlparser.SQLNode) (bool, error) {
+		if sqlparser.IsAggregation(node) {
+			return false, vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.WrongGroupField, "Can't group on '%s'", sqlparser.String(expr))
+		}
+		_, isSubQ := node.(*sqlparser.Subquery)
+		arg, isArg := node.(sqlparser.Argument)
+		if isSubQ || (isArg && strings.HasPrefix(string(arg), "__sq")) {
+			return false, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: subqueries disallowed in GROUP BY")
+		}
+		return true, nil
+	}, expr)
+}
diff --git a/go/vt/vtgate/planbuilder/abstract/subquery.go b/go/vt/vtgate/planbuilder/abstract/subquery.go
index 4e1b3e31fd..998d504cb8 100644
--- a/go/vt/vtgate/planbuilder/abstract/subquery.go
+++ b/go/vt/vtgate/planbuilder/abstract/subquery.go
@@ -88,6 +88,6 @@ func (s *SubQuery) CheckValid() error {
 }
 
 // Compact implements the Operator interface
-func (s *SubQuery) Compact() Operator {
-	return s
+func (s *SubQuery) Compact(*semantics.SemTable) (Operator, error) {
+	return s, nil
 }
diff --git a/go/vt/vtgate/planbuilder/abstract/vindex.go b/go/vt/vtgate/planbuilder/abstract/vindex.go
index fe1f4dc2ce..a0fdf0b2b1 100644
--- a/go/vt/vtgate/planbuilder/abstract/vindex.go
+++ b/go/vt/vtgate/planbuilder/abstract/vindex.go
@@ -112,6 +112,6 @@ func (v *Vindex) CheckValid() error {
 }
 
 // Compact implements the Operator interface
-func (v *Vindex) Compact() Operator {
-	return v
+func (v *Vindex) Compact(*semantics.SemTable) (Operator, error) {
+	return v, nil
 }
diff --git a/go/vt/vtgate/planbuilder/horizon_planning.go b/go/vt/vtgate/planbuilder/horizon_planning.go
index d4ad176416..0cba5c4a72 100644
--- a/go/vt/vtgate/planbuilder/horizon_planning.go
+++ b/go/vt/vtgate/planbuilder/horizon_planning.go
@@ -518,7 +518,7 @@ func planGroupByGen4(groupExpr abstract.GroupBy, plan logicalPlan, semTable *sem
 		}
 		return colAdded || colAddedRecursively, nil
 	case *pulloutSubquery:
-		return false, vterrors.New(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: subqueries disallowed in GROUP BY")
+		return planGroupByGen4(groupExpr, node.underlying, semTable, wsAdded)
 	default:
 		return false, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: group by on: %T", plan)
 	}
diff --git a/go/vt/vtgate/planbuilder/querytree_transformers.go b/go/vt/vtgate/planbuilder/querytree_transformers.go
index c881a7d1b9..9f51a8d4a4 100644
--- a/go/vt/vtgate/planbuilder/querytree_transformers.go
+++ b/go/vt/vtgate/planbuilder/querytree_transformers.go
@@ -153,23 +153,14 @@ func transformDerivedPlan(ctx *planningContext, n *derivedTree) (logicalPlan, er
 
 func transformConcatenatePlan(ctx *planningContext, n *concatenateTree) (logicalPlan, error) {
 	var sources []logicalPlan
-
-	for i, source := range n.sources {
-		plan, err := createLogicalPlan(ctx, source, n.selectStmts[i])
-		if err != nil {
-			return nil, err
-		}
-		if i == 0 {
-			sources = append(sources, plan)
-			continue
-		}
-		last := sources[len(sources)-1]
-		newPlan := mergeUnionLogicalPlans(ctx, last, plan)
-		if newPlan != nil {
-			sources[len(sources)-1] = newPlan
-			continue
-		}
-		sources = append(sources, plan)
+	var err error
+	if n.distinct {
+		sources, err = transformAndMerge(ctx, n)
+	} else {
+		sources, err = transformAndMergeInOrder(ctx, n)
+	}
+	if err != nil {
+		return nil, err
 	}
 
 	if n.distinct {
@@ -200,6 +191,71 @@ func transformConcatenatePlan(ctx *planningContext, n *concatenateTree) (logical
 	return result, nil
 }
 
+func transformAndMergeInOrder(ctx *planningContext, n *concatenateTree) (sources []logicalPlan, err error) {
+	for i, source := range n.sources {
+		plan, err := createLogicalPlan(ctx, source, n.selectStmts[i])
+		if err != nil {
+			return nil, err
+		}
+		if i == 0 {
+			sources = append(sources, plan)
+			continue
+		}
+		last := sources[len(sources)-1]
+		newPlan := mergeUnionLogicalPlans(ctx, last, plan)
+		if newPlan != nil {
+			sources[len(sources)-1] = newPlan
+			continue
+		}
+		sources = append(sources, plan)
+	}
+	return sources, nil
+}
+
+func transformAndMerge(ctx *planningContext, n *concatenateTree) ([]logicalPlan, error) {
+	var sources []logicalPlan
+	for i, source := range n.sources {
+		plan, err := createLogicalPlan(ctx, source, n.selectStmts[i])
+		if err != nil {
+			return nil, err
+		}
+		sources = append(sources, plan)
+	}
+
+	idx := 0
+	for idx < len(sources) {
+		keep := make([]bool, len(sources))
+		srcA := sources[idx]
+		merged := false
+		for j, srcB := range sources {
+			if j <= idx {
+				continue
+			}
+			newPlan := mergeUnionLogicalPlans(ctx, srcA, srcB)
+			if newPlan != nil {
+				sources[idx] = newPlan
+				srcA = newPlan
+				merged = true
+			} else {
+				keep[j] = true
+			}
+		}
+		if !merged {
+			return sources, nil
+		}
+		var phase []logicalPlan
+		for i, source := range sources {
+			if keep[i] || i <= idx {
+				phase = append(phase, source)
+			}
+		}
+		idx++
+		sources = phase
+	}
+
+	return sources, nil
+}
+
 func mergeUnionLogicalPlans(ctx *planningContext, left logicalPlan, right logicalPlan) logicalPlan {
 	lroute, ok := left.(*route)
 	if !ok {
diff --git a/go/vt/vtgate/planbuilder/route_planning.go b/go/vt/vtgate/planbuilder/route_planning.go
index f21422d8f2..465b56e0bd 100644
--- a/go/vt/vtgate/planbuilder/route_planning.go
+++ b/go/vt/vtgate/planbuilder/route_planning.go
@@ -529,10 +529,6 @@ func breakPredicateInLHSandRHS(
 	return
 }
 
-func mergeOrJoinInner(ctx *planningContext, lhs, rhs queryTree, joinPredicates []sqlparser.Expr) (queryTree, error) {
-	return mergeOrJoin(ctx, lhs, rhs, joinPredicates, true)
-}
-
 func mergeOrJoin(ctx *planningContext, lhs, rhs queryTree, joinPredicates []sqlparser.Expr, inner bool) (queryTree, error) {
 	newTabletSet := lhs.tableID().Merge(rhs.tableID())
 
@@ -616,7 +612,7 @@ func (cm cacheMap) getJoinTreeFor(ctx *planningContext, lhs, rhs queryTree, join
 		return cachedPlan, nil
 	}
 
-	join, err := mergeOrJoinInner(ctx, lhs, rhs, joinPredicates)
+	join, err := mergeOrJoin(ctx, lhs, rhs, joinPredicates, true)
 	if err != nil {
 		return nil, err
 	}
@@ -671,7 +667,7 @@ func leftToRightSolve(ctx *planningContext, qg *abstract.QueryGraph) (queryTree,
 			continue
 		}
 		joinPredicates := qg.GetPredicates(acc.tableID(), plan.tableID())
-		acc, err = mergeOrJoinInner(ctx, acc, plan, joinPredicates)
+		acc, err = mergeOrJoin(ctx, acc, plan, joinPredicates, true)
 		if err != nil {
 			return nil, err
 		}
diff --git a/go/vt/vtgate/semantics/analyzer.go b/go/vt/vtgate/semantics/analyzer.go
index 289c4eb1db..197cb6f73b 100644
--- a/go/vt/vtgate/semantics/analyzer.go
+++ b/go/vt/vtgate/semantics/analyzer.go
@@ -156,44 +156,6 @@ func (a *analyzer) analyzeDown(cursor *sqlparser.Cursor) bool {
 	return true
 }
 
-func checkForStar(s sqlparser.SelectExprs) error {
-	for _, expr := range s {
-		_, isStar := expr.(*sqlparser.StarExpr)
-		if isStar {
-			return ProjError{
-				Inner: vterrors.Errorf(vtrpcpb.Code_NOT_FOUND, "can't handle * between UNIONs"),
-			}
-		}
-	}
-	return nil
-}
-
-func checkUnionColumns(cursor *sqlparser.Cursor) error {
-	union, isUnion := cursor.Node().(*sqlparser.Union)
-	if !isUnion {
-		return nil
-	}
-	firstProj := sqlparser.GetFirstSelect(union).SelectExprs
-	err := checkForStar(firstProj)
-	if err != nil {
-		return err
-	}
-
-	count := len(firstProj)
-
-	secondProj := sqlparser.GetFirstSelect(union.Right).SelectExprs
-	err = checkForStar(secondProj)
-	if err != nil {
-		return err
-	}
-
-	if len(secondProj) != count {
-		return vterrors.NewErrorf(vtrpcpb.Code_FAILED_PRECONDITION, vterrors.WrongNumberOfColumnsInSelect, "The used SELECT statements have a different number of columns")
-	}
-
-	return nil
-}
-
 func (a *analyzer) analyzeUp(cursor *sqlparser.Cursor) bool {
 	if !a.shouldContinue() {
 		return false
@@ -223,6 +185,41 @@ func (a *analyzer) analyzeUp(cursor *sqlparser.Cursor) bool {
 	return a.shouldContinue()
 }
 
+func containsStar(s sqlparser.SelectExprs) bool {
+	for _, expr := range s {
+		_, isStar := expr.(*sqlparser.StarExpr)
+		if isStar {
+			return true
+		}
+	}
+	return false
+}
+
+func checkUnionColumns(cursor *sqlparser.Cursor) error {
+	union, isUnion := cursor.Node().(*sqlparser.Union)
+	if !isUnion {
+		return nil
+	}
+	firstProj := sqlparser.GetFirstSelect(union).SelectExprs
+	if containsStar(firstProj) {
+		// if we still have *, we can't figure out if the query is invalid or not
+		// we'll fail it at run time instead
+		return nil
+	}
+	count := len(firstProj)
+
+	secondProj := sqlparser.GetFirstSelect(union.Right).SelectExprs
+	if containsStar(secondProj) {
+		return nil
+	}
+
+	if len(secondProj) != count {
+		return vterrors.NewErrorf(vtrpcpb.Code_FAILED_PRECONDITION, vterrors.WrongNumberOfColumnsInSelect, "The used SELECT statements have a different number of columns")
+	}
+
+	return nil
+}
+
 /*
 	errors that happen when we are evaluating SELECT expressions are saved until we know
 	if we can merge everything into a single route or not


Test patch:
diff --git a/go/vt/vtgate/planbuilder/abstract/operator_test.go b/go/vt/vtgate/planbuilder/abstract/operator_test.go
index 83505a578c..9e1a7c90dd 100644
--- a/go/vt/vtgate/planbuilder/abstract/operator_test.go
+++ b/go/vt/vtgate/planbuilder/abstract/operator_test.go
@@ -207,7 +207,8 @@ func (qg *QueryGraph) crossPredicateString() string {
 		return ""
 	}
 	var joinPreds []string
-	for deps, predicates := range qg.innerJoins {
+	for _, join := range qg.innerJoins {
+		deps, predicates := join.deps, join.exprs
 		var expressions []string
 		for _, expr := range predicates {
 			expressions = append(expressions, sqlparser.String(expr))
diff --git a/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go b/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
index a36a62d440..1281ce0433 100644
--- a/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
+++ b/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
@@ -78,7 +78,7 @@ func TestQP(t *testing.T) {
 			},
 		}, {
 			sql:    "select count(*) b from user group by b",
-			expErr: "Can't group on 'b'",
+			expErr: "Can't group on 'count(*)'",
 		},
 	}
 
diff --git a/go/vt/vtgate/planbuilder/plan_test.go b/go/vt/vtgate/planbuilder/plan_test.go
index 4c09e23e8a..2beb152a49 100644
--- a/go/vt/vtgate/planbuilder/plan_test.go
+++ b/go/vt/vtgate/planbuilder/plan_test.go
@@ -174,28 +174,28 @@ func TestPlan(t *testing.T) {
 	// the column is named as Id. This is to make sure that
 	// column names are case-preserved, but treated as
 	// case-insensitive even if they come from the vschema.
-	testFile(t, "aggr_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "dml_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "from_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "filter_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "postprocess_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "select_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "symtab_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "unsupported_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "vindex_func_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "wireup_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "memory_sort_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "use_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "set_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "union_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "transaction_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "lock_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "large_cases.txt", testOutputTempDir, vschemaWrapper, true)
-	testFile(t, "ddl_cases_no_default_keyspace.txt", testOutputTempDir, vschemaWrapper, false)
-	testFile(t, "flush_cases_no_default_keyspace.txt", testOutputTempDir, vschemaWrapper, false)
-	testFile(t, "show_cases_no_default_keyspace.txt", testOutputTempDir, vschemaWrapper, false)
-	testFile(t, "stream_cases.txt", testOutputTempDir, vschemaWrapper, false)
-	testFile(t, "systemtables_cases.txt", testOutputTempDir, vschemaWrapper, true)
+	testFile(t, "aggr_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "dml_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "from_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "filter_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "postprocess_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "select_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "symtab_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "unsupported_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "vindex_func_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "wireup_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "memory_sort_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "use_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "set_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "union_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "transaction_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "lock_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "large_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "ddl_cases_no_default_keyspace.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "flush_cases_no_default_keyspace.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "show_cases_no_default_keyspace.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "stream_cases.txt", testOutputTempDir, vschemaWrapper)
+	testFile(t, "systemtables_cases.txt", testOutputTempDir, vschemaWrapper)
 }
 
 func TestSysVarSetDisabled(t *testing.T) {
@@ -207,7 +207,7 @@ func TestSysVarSetDisabled(t *testing.T) {
 	testOutputTempDir, err := ioutil.TempDir("", "plan_test")
 	require.NoError(t, err)
 	defer os.RemoveAll(testOutputTempDir)
-	testFile(t, "set_sysvar_disabled_cases.txt", testOutputTempDir, vschemaWrapper, false)
+	testFile(t, "set_sysvar_disabled_cases.txt", testOutputTempDir, vschemaWrapper)
 }
 
 func TestOne(t *testing.T) {
@@ -215,7 +215,7 @@ func TestOne(t *testing.T) {
 		v: loadSchema(t, "schema_test.json"),
 	}
 
-	testFile(t, "onecase.txt", "", vschema, true)
+	testFile(t, "onecase.txt", "", vschema)
 }
 
 func TestRubyOnRailsQueries(t *testing.T) {
@@ -232,7 +232,7 @@ func TestRubyOnRailsQueries(t *testing.T) {
 		}
 	}()
 
-	testFile(t, "rails_cases.txt", testOutputTempDir, vschemaWrapper, true)
+	testFile(t, "rails_cases.txt", testOutputTempDir, vschemaWrapper)
 }
 
 func TestOLTP(t *testing.T) {
@@ -249,7 +249,7 @@ func TestOLTP(t *testing.T) {
 		}
 	}()
 
-	testFile(t, "oltp_cases.txt", testOutputTempDir, vschemaWrapper, true)
+	testFile(t, "oltp_cases.txt", testOutputTempDir, vschemaWrapper)
 }
 
 func TestTPCC(t *testing.T) {
@@ -266,7 +266,7 @@ func TestTPCC(t *testing.T) {
 		}
 	}()
 
-	testFile(t, "tpcc_cases.txt", testOutputTempDir, vschemaWrapper, true)
+	testFile(t, "tpcc_cases.txt", testOutputTempDir, vschemaWrapper)
 }
 
 func TestTPCH(t *testing.T) {
@@ -283,7 +283,7 @@ func TestTPCH(t *testing.T) {
 		}
 	}()
 
-	testFile(t, "tpch_cases.txt", testOutputTempDir, vschemaWrapper, true)
+	testFile(t, "tpch_cases.txt", testOutputTempDir, vschemaWrapper)
 }
 
 func BenchmarkOLTP(b *testing.B) {
@@ -330,7 +330,7 @@ func TestBypassPlanningShardTargetFromFile(t *testing.T) {
 		tabletType: topodatapb.TabletType_PRIMARY,
 		dest:       key.DestinationShard("-80")}
 
-	testFile(t, "bypass_shard_cases.txt", testOutputTempDir, vschema, true)
+	testFile(t, "bypass_shard_cases.txt", testOutputTempDir, vschema)
 }
 func TestBypassPlanningKeyrangeTargetFromFile(t *testing.T) {
 	testOutputTempDir, err := ioutil.TempDir("", "plan_test")
@@ -349,7 +349,7 @@ func TestBypassPlanningKeyrangeTargetFromFile(t *testing.T) {
 		dest:       key.DestinationExactKeyRange{KeyRange: keyRange[0]},
 	}
 
-	testFile(t, "bypass_keyrange_cases.txt", testOutputTempDir, vschema, true)
+	testFile(t, "bypass_keyrange_cases.txt", testOutputTempDir, vschema)
 }
 
 func TestWithDefaultKeyspaceFromFile(t *testing.T) {
@@ -370,12 +370,12 @@ func TestWithDefaultKeyspaceFromFile(t *testing.T) {
 		tabletType: topodatapb.TabletType_PRIMARY,
 	}
 
-	testFile(t, "alterVschema_cases.txt", testOutputTempDir, vschema, false)
-	testFile(t, "ddl_cases.txt", testOutputTempDir, vschema, false)
-	testFile(t, "migration_cases.txt", testOutputTempDir, vschema, false)
-	testFile(t, "flush_cases.txt", testOutputTempDir, vschema, false)
-	testFile(t, "show_cases.txt", testOutputTempDir, vschema, false)
-	testFile(t, "call_cases.txt", testOutputTempDir, vschema, false)
+	testFile(t, "alterVschema_cases.txt", testOutputTempDir, vschema)
+	testFile(t, "ddl_cases.txt", testOutputTempDir, vschema)
+	testFile(t, "migration_cases.txt", testOutputTempDir, vschema)
+	testFile(t, "flush_cases.txt", testOutputTempDir, vschema)
+	testFile(t, "show_cases.txt", testOutputTempDir, vschema)
+	testFile(t, "call_cases.txt", testOutputTempDir, vschema)
 }
 
 func TestWithSystemSchemaAsDefaultKeyspace(t *testing.T) {
@@ -389,7 +389,7 @@ func TestWithSystemSchemaAsDefaultKeyspace(t *testing.T) {
 		tabletType: topodatapb.TabletType_PRIMARY,
 	}
 
-	testFile(t, "sysschema_default.txt", testOutputTempDir, vschema, false)
+	testFile(t, "sysschema_default.txt", testOutputTempDir, vschema)
 }
 
 func TestOtherPlanningFromFile(t *testing.T) {
@@ -406,8 +406,8 @@ func TestOtherPlanningFromFile(t *testing.T) {
 		tabletType: topodatapb.TabletType_PRIMARY,
 	}
 
-	testFile(t, "other_read_cases.txt", testOutputTempDir, vschema, false)
-	testFile(t, "other_admin_cases.txt", testOutputTempDir, vschema, false)
+	testFile(t, "other_read_cases.txt", testOutputTempDir, vschema)
+	testFile(t, "other_admin_cases.txt", testOutputTempDir, vschema)
 }
 
 func loadSchema(t testing.TB, filename string) *vindexes.VSchema {
@@ -580,11 +580,9 @@ func escapeNewLines(in string) string {
 	return strings.ReplaceAll(in, "\n", "\\n")
 }
 
-func testFile(t *testing.T, filename, tempDir string, vschema *vschemaWrapper, checkGen4equalPlan bool) {
-	var checkAllTests = false
+func testFile(t *testing.T, filename, tempDir string, vschema *vschemaWrapper) {
 	t.Run(filename, func(t *testing.T) {
 		expected := &strings.Builder{}
-		fail := checkAllTests
 		var outFirstPlanner string
 		for tcase := range iterateExecFile(filename) {
 			t.Run(fmt.Sprintf("%d V3: %s", tcase.lineno, tcase.comments), func(t *testing.T) {
@@ -593,7 +591,6 @@ func testFile(t *testing.T, filename, tempDir string, vschema *vschemaWrapper, c
 				out := getPlanOrErrorOutput(err, plan)
 
 				if out != tcase.output {
-					fail = true
 					t.Errorf("V3 - %s:%d\nDiff:\n%s\n[%s] \n[%s]", filename, tcase.lineno, cmp.Diff(tcase.output, out), tcase.output, out)
 				}
 				if err != nil {
@@ -604,11 +601,6 @@ func testFile(t *testing.T, filename, tempDir string, vschema *vschemaWrapper, c
 				expected.WriteString(fmt.Sprintf("%s\"%s\"\n%s\n", tcase.comments, escapeNewLines(tcase.input), out))
 			})
 
-			empty := false
-			if tcase.output2ndPlanner == "" {
-				empty = true
-			}
-
 			vschema.version = Gen4
 			out, err := getPlanOutput(tcase, vschema)
 			if err != nil && tcase.output2ndPlanner == "" && strings.HasPrefix(err.Error(), "gen4 does not yet support") {
@@ -624,40 +616,29 @@ func testFile(t *testing.T, filename, tempDir string, vschema *vschemaWrapper, c
 			//       with this last expectation, it is an error if the Gen4 planner
 			//       produces the same plan as the V3 planner does
 			testName := fmt.Sprintf("%d Gen4: %s", tcase.lineno, tcase.comments)
-			if !empty || checkAllTests {
-				t.Run(testName, func(t *testing.T) {
-					if out != tcase.output2ndPlanner {
-						fail = true
-						t.Errorf("Gen4 - %s:%d\nDiff:\n%s\n[%s] \n[%s]", filename, tcase.lineno, cmp.Diff(tcase.output2ndPlanner, out), tcase.output, out)
+			t.Run(testName, func(t *testing.T) {
+				if out != tcase.output2ndPlanner {
+					t.Errorf("Gen4 - %s:%d\nDiff:\n%s\n[%s] \n[%s]", filename, tcase.lineno, cmp.Diff(tcase.output2ndPlanner, out), tcase.output, out)
+				}
+				if err != nil {
+					out = `"` + out + `"`
+				}
 
-					}
+				if outFirstPlanner == out {
+					expected.WriteString(samePlanMarker)
+				} else {
 					if err != nil {
-						out = `"` + out + `"`
-					}
-
-					if outFirstPlanner == out {
-						expected.WriteString(samePlanMarker)
+						out = out[1 : len(out)-1] // remove the double quotes
+						expected.WriteString(fmt.Sprintf("Gen4 error: %s\n", out))
 					} else {
-						if err != nil {
-							out = out[1 : len(out)-1] // remove the double quotes
-							expected.WriteString(fmt.Sprintf("Gen4 error: %s\n", out))
-						} else {
-							expected.WriteString(fmt.Sprintf("%s\n", out))
-						}
+						expected.WriteString(fmt.Sprintf("%s\n", out))
 					}
-				})
-			} else {
-				if out == tcase.output && checkGen4equalPlan {
-					t.Run(testName, func(t *testing.T) {
-						t.Errorf("Gen4 - %s:%d\nplanner produces same output as V3", filename, tcase.lineno)
-					})
 				}
-			}
-
+			})
 			expected.WriteString("\n")
 		}
 
-		if fail && tempDir != "" {
+		if tempDir != "" {
 			gotFile := fmt.Sprintf("%s/%s", tempDir, filename)
 			_ = ioutil.WriteFile(gotFile, []byte(strings.TrimSpace(expected.String())+"\n"), 0644)
 			fmt.Println(fmt.Sprintf("Errors found in plantests. If the output is correct, run `cp %s/* testdata/` to update test expectations", tempDir)) // nolint
diff --git a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
index 170983ed43..6ae7d747a6 100644
--- a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
@@ -1432,7 +1432,7 @@ Gen4 plan same as above
 # scatter aggregate group by aggregate function
 "select count(*) b from user group by b"
 "Can't group on 'b'"
-Gen4 plan same as above
+Gen4 error: Can't group on 'count(*)'
 
 # scatter aggregate multiple group by (columns)
 "select a, b, count(*) from user group by b, a"
diff --git a/go/vt/vtgate/planbuilder/testdata/ddl_cases.txt b/go/vt/vtgate/planbuilder/testdata/ddl_cases.txt
index 7cedc58be4..404bfad40e 100644
--- a/go/vt/vtgate/planbuilder/testdata/ddl_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/ddl_cases.txt
@@ -311,6 +311,7 @@ Gen4 plan same as above
     "Query": "create view view_a as select id from unsharded union select id from unsharded_auto union select id from unsharded_auto union select `name` from unsharded"
   }
 }
+Gen4 error: nesting of unions at the right-hand side is not yet supported
 
 # Alter View
 "alter view user.user_extra as select* from user.user"
diff --git a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
index 769e387d3c..8e2716e7d8 100644
--- a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
@@ -734,6 +734,44 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select u.a from user u join music m on u.a = m.a order by binary a desc",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "u_a": 0
+    },
+    "TableName": "`user`_music",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select u.a, binary a, weight_string(binary a) from `user` as u where 1 != 1",
+        "OrderBy": "(1|2) DESC",
+        "Query": "select u.a, binary a, weight_string(binary a) from `user` as u order by binary a desc",
+        "Table": "`user`"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select 1 from music as m where 1 != 1",
+        "Query": "select 1 from music as m where m.a = :u_a",
+        "Table": "music"
+      }
+    ]
+  }
+}
 
 # intcol order by
 "select id, intcol from user order by intcol"
diff --git a/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt b/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
index 5012e24ea3..ad1c60c15c 100644
--- a/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
@@ -185,6 +185,44 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select * from information_schema.tables where table_schema = 'user' union select * from information_schema.tables where table_schema = 'main'",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from information_schema.`tables` where 1 != 1",
+            "Query": "select distinct * from information_schema.`tables` where table_schema = :__vtschemaname",
+            "SysTableTableSchema": "[VARBINARY(\"user\")]",
+            "Table": "information_schema.`tables`"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from information_schema.`tables` where 1 != 1",
+            "Query": "select distinct * from information_schema.`tables` where table_schema = :__vtschemaname",
+            "SysTableTableSchema": "[VARBINARY(\"main\")]",
+            "Table": "information_schema.`tables`"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # Select from information schema query with two tables that route should be merged
 "SELECT DELETE_RULE, UPDATE_RULE FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC ON KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME WHERE KCU.TABLE_SCHEMA = 'test' AND KCU.TABLE_NAME = 'data_type_table' AND KCU.COLUMN_NAME = 'id' AND KCU.REFERENCED_TABLE_SCHEMA = 'test' AND KCU.CONSTRAINT_NAME = 'data_type_table_id_fkey' ORDER BY KCU.CONSTRAINT_NAME, KCU.COLUMN_NAME"
diff --git a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
index 02570b7495..4eb920b0ec 100644
--- a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
@@ -116,6 +116,174 @@ Gen4 error: unsupported: cross-shard correlated subquery
 # TPC-H query 5 - Gen4 produces plan but the plan output is flaky
 "select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' and o_orderdate >= date('1994-01-01') and o_orderdate < date('1994-01-01') + interval '1' year group by n_name order by revenue desc"
 "unsupported: cross-shard query with aggregates"
+{
+  "QueryType": "SELECT",
+  "Original": "select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' and o_orderdate \u003e= date('1994-01-01') and o_orderdate \u003c date('1994-01-01') + interval '1' year group by n_name order by revenue desc",
+  "Instructions": {
+    "OperatorType": "Sort",
+    "Variant": "Memory",
+    "OrderBy": "1 DESC",
+    "ResultColumns": 2,
+    "Inputs": [
+      {
+        "OperatorType": "Aggregate",
+        "Variant": "Ordered",
+        "Aggregates": "sum(1) AS revenue",
+        "GroupBy": "(0|2)",
+        "Inputs": [
+          {
+            "OperatorType": "Sort",
+            "Variant": "Memory",
+            "OrderBy": "(0|2) ASC",
+            "Inputs": [
+              {
+                "OperatorType": "Join",
+                "Variant": "Join",
+                "JoinColumnIndexes": "1,-2,2",
+                "JoinVars": {
+                  "s_nationkey": 0
+                },
+                "TableName": "orders_customer_lineitem_supplier_nation_region",
+                "Inputs": [
+                  {
+                    "OperatorType": "Join",
+                    "Variant": "Join",
+                    "JoinColumnIndexes": "1,2",
+                    "JoinVars": {
+                      "c_nationkey": 1,
+                      "o_orderkey": 0
+                    },
+                    "TableName": "orders_customer_lineitem_supplier",
+                    "Inputs": [
+                      {
+                        "OperatorType": "Join",
+                        "Variant": "Join",
+                        "JoinVars": {
+                          "o_custkey": 0
+                        },
+                        "TableName": "orders_customer",
+                        "Inputs": [
+                          {
+                            "OperatorType": "Route",
+                            "Variant": "SelectScatter",
+                            "Keyspace": {
+                              "Name": "main",
+                              "Sharded": true
+                            },
+                            "FieldQuery": "select o_custkey, o_orderkey from orders where 1 != 1",
+                            "Query": "select o_custkey, o_orderkey from orders where o_orderdate \u003e= date('1994-01-01') and o_orderdate \u003c date('1994-01-01') + interval '1' year",
+                            "Table": "orders"
+                          },
+                          {
+                            "OperatorType": "Route",
+                            "Variant": "SelectEqualUnique",
+                            "Keyspace": {
+                              "Name": "main",
+                              "Sharded": true
+                            },
+                            "FieldQuery": "select c_nationkey from customer where 1 != 1",
+                            "Query": "select c_nationkey from customer where c_custkey = :o_custkey",
+                            "Table": "customer",
+                            "Values": [
+                              ":o_custkey"
+                            ],
+                            "Vindex": "hash"
+                          }
+                        ]
+                      },
+                      {
+                        "OperatorType": "Join",
+                        "Variant": "Join",
+                        "JoinColumnIndexes": "1,-2",
+                        "JoinVars": {
+                          "l_suppkey": 0
+                        },
+                        "TableName": "lineitem_supplier",
+                        "Inputs": [
+                          {
+                            "OperatorType": "Route",
+                            "Variant": "SelectEqualUnique",
+                            "Keyspace": {
+                              "Name": "main",
+                              "Sharded": true
+                            },
+                            "FieldQuery": "select l_suppkey, sum(l_extendedprice * (1 - l_discount)) as revenue from lineitem where 1 != 1",
+                            "Query": "select l_suppkey, sum(l_extendedprice * (1 - l_discount)) as revenue from lineitem where l_orderkey = :o_orderkey",
+                            "Table": "lineitem",
+                            "Values": [
+                              ":o_orderkey"
+                            ],
+                            "Vindex": "lineitem_map"
+                          },
+                          {
+                            "OperatorType": "Route",
+                            "Variant": "SelectEqualUnique",
+                            "Keyspace": {
+                              "Name": "main",
+                              "Sharded": true
+                            },
+                            "FieldQuery": "select s_nationkey from supplier where 1 != 1",
+                            "Query": "select s_nationkey from supplier where s_suppkey = :l_suppkey and s_nationkey = :c_nationkey",
+                            "Table": "supplier",
+                            "Values": [
+                              ":l_suppkey"
+                            ],
+                            "Vindex": "hash"
+                          }
+                        ]
+                      }
+                    ]
+                  },
+                  {
+                    "OperatorType": "Join",
+                    "Variant": "Join",
+                    "JoinColumnIndexes": "-2,-3",
+                    "JoinVars": {
+                      "n_regionkey": 0
+                    },
+                    "TableName": "nation_region",
+                    "Inputs": [
+                      {
+                        "OperatorType": "Route",
+                        "Variant": "SelectEqualUnique",
+                        "Keyspace": {
+                          "Name": "main",
+                          "Sharded": true
+                        },
+                        "FieldQuery": "select n_regionkey, n_name, weight_string(n_name) from nation where 1 != 1",
+                        "Query": "select n_regionkey, n_name, weight_string(n_name) from nation where n_nationkey = :s_nationkey",
+                        "Table": "nation",
+                        "Values": [
+                          ":s_nationkey"
+                        ],
+                        "Vindex": "hash"
+                      },
+                      {
+                        "OperatorType": "Route",
+                        "Variant": "SelectEqualUnique",
+                        "Keyspace": {
+                          "Name": "main",
+                          "Sharded": true
+                        },
+                        "FieldQuery": "select 1 from region where 1 != 1",
+                        "Query": "select 1 from region where r_name = 'ASIA' and r_regionkey = :n_regionkey",
+                        "Table": "region",
+                        "Values": [
+                          ":n_regionkey"
+                        ],
+                        "Vindex": "hash"
+                      }
+                    ]
+                  }
+                ]
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # TPC-H query 6
 "select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date('1994-01-01') and l_shipdate < date('1994-01-01') + interval '1' year and l_discount between 0.06 - 0.01 and 0.06 + 0.01 and l_quantity < 24"
@@ -500,6 +668,87 @@ Gen4 plan same as above
 # TPC-H query 16
 "select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt from partsupp, part where p_partkey = ps_partkey and p_brand <> 'Brand#45' and p_type not like 'MEDIUM POLISHED%' and p_size in (49, 14, 23, 45, 19, 3, 36, 9) and ps_suppkey not in ( select s_suppkey from supplier where s_comment like '%Customer%Complaints%' ) group by p_brand, p_type, p_size order by supplier_cnt desc, p_brand, p_type, p_size"
 "unsupported: cross-shard query with aggregates"
+{
+  "QueryType": "SELECT",
+  "Original": "select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt from partsupp, part where p_partkey = ps_partkey and p_brand \u003c\u003e 'Brand#45' and p_type not like 'MEDIUM POLISHED%' and p_size in (49, 14, 23, 45, 19, 3, 36, 9) and ps_suppkey not in ( select s_suppkey from supplier where s_comment like '%Customer%Complaints%' ) group by p_brand, p_type, p_size order by supplier_cnt desc, p_brand, p_type, p_size",
+  "Instructions": {
+    "OperatorType": "Sort",
+    "Variant": "Memory",
+    "OrderBy": "3 DESC, (0|4) ASC, (1|5) ASC, (2|6) ASC",
+    "ResultColumns": 4,
+    "Inputs": [
+      {
+        "OperatorType": "Aggregate",
+        "Variant": "Ordered",
+        "Aggregates": "count_distinct(3|7) AS supplier_cnt",
+        "GroupBy": "(0|4), (1|5), (2|6)",
+        "Inputs": [
+          {
+            "OperatorType": "Subquery",
+            "Variant": "PulloutNotIn",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectScatter",
+                "Keyspace": {
+                  "Name": "main",
+                  "Sharded": true
+                },
+                "FieldQuery": "select s_suppkey from supplier where 1 != 1",
+                "Query": "select s_suppkey from supplier where s_comment like '%Customer%Complaints%'",
+                "Table": "supplier"
+              },
+              {
+                "OperatorType": "Sort",
+                "Variant": "Memory",
+                "OrderBy": "(0|4) ASC, (1|5) ASC, (2|6) ASC, (3|7) ASC",
+                "Inputs": [
+                  {
+                    "OperatorType": "Join",
+                    "Variant": "Join",
+                    "JoinColumnIndexes": "1,2,3,-2,4,5,6,-3",
+                    "JoinVars": {
+                      "ps_partkey": 0
+                    },
+                    "TableName": "partsupp_part",
+                    "Inputs": [
+                      {
+                        "OperatorType": "Route",
+                        "Variant": "SelectScatter",
+                        "Keyspace": {
+                          "Name": "main",
+                          "Sharded": true
+                        },
+                        "FieldQuery": "select ps_partkey, ps_suppkey, weight_string(ps_suppkey) from partsupp where 1 != 1",
+                        "Query": "select ps_partkey, ps_suppkey, weight_string(ps_suppkey) from partsupp where ps_suppkey not in ::__sq1",
+                        "Table": "partsupp"
+                      },
+                      {
+                        "OperatorType": "Route",
+                        "Variant": "SelectEqualUnique",
+                        "Keyspace": {
+                          "Name": "main",
+                          "Sharded": true
+                        },
+                        "FieldQuery": "select p_brand, p_type, p_size, weight_string(p_brand), weight_string(p_type), weight_string(p_size) from part where 1 != 1",
+                        "Query": "select p_brand, p_type, p_size, weight_string(p_brand), weight_string(p_type), weight_string(p_size) from part where p_brand != 'Brand#45' and p_type not like 'MEDIUM POLISHED%' and p_size in (49, 14, 23, 45, 19, 3, 36, 9) and p_partkey = :ps_partkey",
+                        "Table": "part",
+                        "Values": [
+                          ":ps_partkey"
+                        ],
+                        "Vindex": "hash"
+                      }
+                    ]
+                  }
+                ]
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # TPC-H query 17
 "select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#23' and p_container = 'MED BOX' and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey )"
@@ -509,6 +758,101 @@ Gen4 error: unsupported: cross-shard correlated subquery
 # TPC-H query 18
 "select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 300 ) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate limit 100"
 "unsupported: cross-shard query with aggregates"
+{
+  "QueryType": "SELECT",
+  "Original": "select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) \u003e 300 ) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate limit 100",
+  "Instructions": {
+    "OperatorType": "Limit",
+    "Count": 100,
+    "Inputs": [
+      {
+        "OperatorType": "Subquery",
+        "Variant": "PulloutIn",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": true
+            },
+            "FieldQuery": "select l_orderkey from lineitem where 1 != 1 group by l_orderkey",
+            "Query": "select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) \u003e 300",
+            "Table": "lineitem"
+          },
+          {
+            "OperatorType": "Join",
+            "Variant": "Join",
+            "JoinColumnIndexes": "-2,-3,-1,-4,-5,1,-6,-7,-8,-9,-10",
+            "JoinVars": {
+              "o_orderkey": 0
+            },
+            "TableName": "orders_customer_lineitem",
+            "Inputs": [
+              {
+                "OperatorType": "Join",
+                "Variant": "Join",
+                "JoinColumnIndexes": "-2,1,2,-3,-4,3,4,-5,-6,-7",
+                "JoinVars": {
+                  "o_custkey": 0
+                },
+                "TableName": "orders_customer",
+                "Inputs": [
+                  {
+                    "OperatorType": "Route",
+                    "Variant": "SelectIN",
+                    "Keyspace": {
+                      "Name": "main",
+                      "Sharded": true
+                    },
+                    "FieldQuery": "select o_custkey, o_orderkey, o_orderdate, o_totalprice, weight_string(o_orderkey), weight_string(o_orderdate), weight_string(o_totalprice) from orders where 1 != 1",
+                    "OrderBy": "(3|6) DESC, (2|5) ASC",
+                    "Query": "select o_custkey, o_orderkey, o_orderdate, o_totalprice, weight_string(o_orderkey), weight_string(o_orderdate), weight_string(o_totalprice) from orders where o_orderkey in ::__vals order by o_totalprice desc, o_orderdate asc",
+                    "Table": "orders",
+                    "Values": [
+                      "::__sq1"
+                    ],
+                    "Vindex": "hash"
+                  },
+                  {
+                    "OperatorType": "Route",
+                    "Variant": "SelectEqualUnique",
+                    "Keyspace": {
+                      "Name": "main",
+                      "Sharded": true
+                    },
+                    "FieldQuery": "select c_name, c_custkey, weight_string(c_name), weight_string(c_custkey) from customer where 1 != 1",
+                    "Query": "select c_name, c_custkey, weight_string(c_name), weight_string(c_custkey) from customer where c_custkey = :o_custkey",
+                    "Table": "customer",
+                    "Values": [
+                      ":o_custkey"
+                    ],
+                    "Vindex": "hash"
+                  }
+                ]
+              },
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectEqualUnique",
+                "Keyspace": {
+                  "Name": "main",
+                  "Sharded": true
+                },
+                "FieldQuery": "select sum(l_quantity) from lineitem where 1 != 1",
+                "Query": "select sum(l_quantity) from lineitem where l_orderkey = :o_orderkey",
+                "Table": "lineitem",
+                "Values": [
+                  ":o_orderkey"
+                ],
+                "Vindex": "lineitem_map"
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # TPC-H query 19
 "select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#12' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 1 and l_quantity <= 1 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#23' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= 10 and l_quantity <= 10 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#34' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 20 and l_quantity <= 20 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' )"
diff --git a/go/vt/vtgate/planbuilder/testdata/union_cases.txt b/go/vt/vtgate/planbuilder/testdata/union_cases.txt
index 0a1cd8e429..f1375f268d 100644
--- a/go/vt/vtgate/planbuilder/testdata/union_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/union_cases.txt
@@ -355,6 +355,42 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select * from information_schema.a union select * from unsharded",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from information_schema.a where 1 != 1",
+            "Query": "select distinct * from information_schema.a",
+            "Table": "information_schema.a"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectUnsharded",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from unsharded where 1 != 1",
+            "Query": "select distinct * from unsharded",
+            "Table": "unsharded"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # union of information_schema with normal table
 "select * from unsharded union select * from information_schema.a"
@@ -394,6 +430,42 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select * from unsharded union select * from information_schema.a",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectUnsharded",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from unsharded where 1 != 1",
+            "Query": "select distinct * from unsharded",
+            "Table": "unsharded"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from information_schema.a where 1 != 1",
+            "Query": "select distinct * from information_schema.a",
+            "Table": "information_schema.a"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # multi-shard union
 "(select id from user union select id from music) union select 1 from dual"
@@ -654,6 +726,50 @@ Gen4 error: nesting of unions at the right-hand side is not yet supported
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select * from music where id = 1 union select * from user where id = 1",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectEqualUnique",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select * from music where 1 != 1",
+            "Query": "select distinct * from music where id = 1",
+            "Table": "music",
+            "Values": [
+              1
+            ],
+            "Vindex": "music_user_map"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectEqualUnique",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select * from `user` where 1 != 1",
+            "Query": "select distinct * from `user` where id = 1",
+            "Table": "`user`",
+            "Values": [
+              1
+            ],
+            "Vindex": "user_index"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # union with different target shards
 "select 1 from music where id = 1 union select 1 from music where id = 2"
@@ -747,10 +863,10 @@ Gen4 error: nesting of unions at the right-hand side is not yet supported
 }
 
 # multiple select statement have inner order by with union - TODO (systay) no need to send down ORDER BY if we are going to loose it with UNION DISTINCT
-"(select 1 from user order by 1 desc) union (select 1 from user order by 1 asc)"
+"(select id from user order by 1 desc) union (select id from user order by 1 asc)"
 {
   "QueryType": "SELECT",
-  "Original": "(select 1 from user order by 1 desc) union (select 1 from user order by 1 asc)",
+  "Original": "(select id from user order by 1 desc) union (select id from user order by 1 asc)",
   "Instructions": {
     "OperatorType": "Distinct",
     "Inputs": [
@@ -764,9 +880,9 @@ Gen4 error: nesting of unions at the right-hand side is not yet supported
               "Name": "user",
               "Sharded": true
             },
-            "FieldQuery": "select 1, weight_string(1) from `user` where 1 != 1",
+            "FieldQuery": "select id, weight_string(id) from `user` where 1 != 1",
             "OrderBy": "(0|1) DESC",
-            "Query": "select 1, weight_string(1) from `user` order by 1 desc",
+            "Query": "select id, weight_string(id) from `user` order by 1 desc",
             "ResultColumns": 1,
             "Table": "`user`"
           },
@@ -777,9 +893,9 @@ Gen4 error: nesting of unions at the right-hand side is not yet supported
               "Name": "user",
               "Sharded": true
             },
-            "FieldQuery": "select 1, weight_string(1) from `user` where 1 != 1",
+            "FieldQuery": "select id, weight_string(id) from `user` where 1 != 1",
             "OrderBy": "(0|1) ASC",
-            "Query": "select 1, weight_string(1) from `user` order by 1 asc",
+            "Query": "select id, weight_string(id) from `user` order by 1 asc",
             "ResultColumns": 1,
             "Table": "`user`"
           }
@@ -788,6 +904,28 @@ Gen4 error: nesting of unions at the right-hand side is not yet supported
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "(select id from user order by 1 desc) union (select id from user order by 1 asc)",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "(select id, weight_string(id) from `user` where 1 != 1) union (select id, weight_string(id) from `user` where 1 != 1)",
+        "OrderBy": "(0|1) DESC",
+        "Query": "(select id, weight_string(id) from `user` order by id desc) union (select id, weight_string(id) from `user` order by id asc)",
+        "ResultColumns": 1,
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # multiple unions
 "select 1 union select null union select 1.0 union select '1' union select 2 union select 2.0 from user"
@@ -1435,6 +1573,123 @@ Gen4 error: nesting of unions at the right-hand side is not yet supported
 }
 Gen4 plan same as above
 
+# UNION that needs to be reordered to be merged more aggressively. Gen4 is able to get it down to 2 routes
+"select col from unsharded union select id from user union select col2 from unsharded union select col from user_extra"
+{
+  "QueryType": "SELECT",
+  "Original": "select col from unsharded union select id from user union select col2 from unsharded union select col from user_extra",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Distinct",
+            "Inputs": [
+              {
+                "OperatorType": "Concatenate",
+                "Inputs": [
+                  {
+                    "OperatorType": "Distinct",
+                    "Inputs": [
+                      {
+                        "OperatorType": "Concatenate",
+                        "Inputs": [
+                          {
+                            "OperatorType": "Route",
+                            "Variant": "SelectUnsharded",
+                            "Keyspace": {
+                              "Name": "main",
+                              "Sharded": false
+                            },
+                            "FieldQuery": "select col from unsharded where 1 != 1",
+                            "Query": "select col from unsharded",
+                            "Table": "unsharded"
+                          },
+                          {
+                            "OperatorType": "Route",
+                            "Variant": "SelectScatter",
+                            "Keyspace": {
+                              "Name": "user",
+                              "Sharded": true
+                            },
+                            "FieldQuery": "select id from `user` where 1 != 1",
+                            "Query": "select id from `user`",
+                            "Table": "`user`"
+                          }
+                        ]
+                      }
+                    ]
+                  },
+                  {
+                    "OperatorType": "Route",
+                    "Variant": "SelectUnsharded",
+                    "Keyspace": {
+                      "Name": "main",
+                      "Sharded": false
+                    },
+                    "FieldQuery": "select col2 from unsharded where 1 != 1",
+                    "Query": "select col2 from unsharded",
+                    "Table": "unsharded"
+                  }
+                ]
+              }
+            ]
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select col from user_extra where 1 != 1",
+            "Query": "select col from user_extra",
+            "Table": "user_extra"
+          }
+        ]
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select col from unsharded union select id from user union select col2 from unsharded union select col from user_extra",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectUnsharded",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select col from unsharded where 1 != 1 union select col2 from unsharded where 1 != 1",
+            "Query": "select col from unsharded union select col2 from unsharded",
+            "Table": "unsharded"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select id from `user` where 1 != 1 union select col from user_extra where 1 != 1",
+            "Query": "select id from `user` union select col from user_extra",
+            "Table": "`user`"
+          }
+        ]
+      }
+    ]
+  }
+}
+
 # ambiguous LIMIT
 "select id from user limit 1 union all select id from music limit 1"
 "syntax error at position 34 near 'union'"