Repository: vitessio/vitess
Pull request #8453 (2021-07-12 14:35:05)

Gen4: groupby support
<!--
  Thank you for your contribution to the Vitess project.
  How to contribute: https://vitess.io/docs/contributing/
  Please first make sure there is an open Issue to discuss the feature/fix suggested in this PR.
  If this is a new feature, please mark the Issue as "RFC".
 -->

<!-- if this PR is Work in Progress please create it as a Draft Pull Request -->

## Description
<!-- A few sentences describing the overall goals of the pull request's commits. -->
<!-- If this is a bug fix and you think the fix should be backported, please write so. -->

This PR adds support for grouping and aggregations.
Gen4 still needs to handle grouping with distinct.

## Related Issue(s)
<!-- List related issues and pull requests. If this PR fixes an issue, please add it using Fixes #????  -->
#7280 

## Checklist
- [x] Tests were added or are not required
- [x] Documentation was added or is not required

## Deployment Notes
<!-- Notes regarding deployment of the contained body of work. These should note any db migrations, etc. -->


Patch:
diff --git a/go/mysql/sql_error.go b/go/mysql/sql_error.go
index 43ad76f6fe..fc436cd778 100644
--- a/go/mysql/sql_error.go
+++ b/go/mysql/sql_error.go
@@ -176,6 +176,7 @@ var stateToMysqlCode = map[vterrors.State]struct {
 	vterrors.NoSuchTable:                  {num: ERNoSuchTable, state: SSUnknownTable},
 	vterrors.NotSupportedYet:              {num: ERNotSupportedYet, state: SSClientError},
 	vterrors.ForbidSchemaChange:           {num: ERForbidSchemaChange, state: SSUnknownSQLState},
+	vterrors.MixOfGroupFuncAndFields:      {num: ERMixOfGroupFuncAndFields, state: SSClientError},
 	vterrors.NetPacketTooLarge:            {num: ERNetPacketTooLarge, state: SSNetError},
 	vterrors.NonUniqError:                 {num: ERNonUniq, state: SSConstraintViolation},
 	vterrors.NonUniqTable:                 {num: ERNonUniqTable, state: SSClientError},
diff --git a/go/vt/sqlparser/ast_funcs.go b/go/vt/sqlparser/ast_funcs.go
index 7f8b090c3b..624ce3a39e 100644
--- a/go/vt/sqlparser/ast_funcs.go
+++ b/go/vt/sqlparser/ast_funcs.go
@@ -1366,3 +1366,22 @@ func ToString(exprs []TableExpr) string {
 	}
 	return buf.String()
 }
+
+// ContainsAggregation returns true if the expression contains aggregation
+func ContainsAggregation(e Expr) bool {
+	hasAggregates := false
+	_ = Walk(func(node SQLNode) (kontinue bool, err error) {
+		switch node := node.(type) {
+		case *FuncExpr:
+			if node.IsAggregate() {
+				hasAggregates = true
+				return false, nil
+			}
+		case *GroupConcatExpr:
+			hasAggregates = true
+			return false, nil
+		}
+		return true, nil
+	}, e)
+	return hasAggregates
+}
diff --git a/go/vt/vterrors/state.go b/go/vt/vterrors/state.go
index d83a8b34d8..40ec8390db 100644
--- a/go/vt/vterrors/state.go
+++ b/go/vt/vterrors/state.go
@@ -39,6 +39,7 @@ const (
 	WrongTypeForVar
 	WrongValueForVar
 	LockOrActiveTransaction
+	MixOfGroupFuncAndFields
 
 	// failed precondition
 	NoDB
diff --git a/go/vt/vtgate/engine/cached_size.go b/go/vt/vtgate/engine/cached_size.go
index e348cfb7a4..0c1ce058f6 100644
--- a/go/vt/vtgate/engine/cached_size.go
+++ b/go/vt/vtgate/engine/cached_size.go
@@ -33,10 +33,14 @@ func (cached *AggregateParams) CachedSize(alloc bool) int64 {
 	}
 	size := int64(0)
 	if alloc {
-		size += int64(32)
+		size += int64(48)
 	}
 	// field Alias string
 	size += int64(len(cached.Alias))
+	// field Expr vitess.io/vitess/go/vt/sqlparser.Expr
+	if cc, ok := cached.Expr.(cachedObject); ok {
+		size += cc.CachedSize(true)
+	}
 	return size
 }
 func (cached *AlterVSchema) CachedSize(alloc bool) int64 {
@@ -185,6 +189,20 @@ func (cached *Generate) CachedSize(alloc bool) int64 {
 	size += cached.Values.CachedSize(false)
 	return size
 }
+func (cached *GroupByParams) CachedSize(alloc bool) int64 {
+	if cached == nil {
+		return int64(0)
+	}
+	size := int64(0)
+	if alloc {
+		size += int64(32)
+	}
+	// field Expr vitess.io/vitess/go/vt/sqlparser.Expr
+	if cc, ok := cached.Expr.(cachedObject); ok {
+		size += cc.CachedSize(true)
+	}
+	return size
+}
 func (cached *Insert) CachedSize(alloc bool) int64 {
 	if cached == nil {
 		return int64(0)
@@ -323,7 +341,7 @@ func (cached *MemorySort) CachedSize(alloc bool) int64 {
 	}
 	// field UpperLimit vitess.io/vitess/go/sqltypes.PlanValue
 	size += cached.UpperLimit.CachedSize(false)
-	// field OrderBy []vitess.io/vitess/go/vt/vtgate/engine.OrderbyParams
+	// field OrderBy []vitess.io/vitess/go/vt/vtgate/engine.OrderByParams
 	{
 		size += int64(cap(cached.OrderBy)) * int64(32)
 	}
@@ -350,7 +368,7 @@ func (cached *MergeSort) CachedSize(alloc bool) int64 {
 			}
 		}
 	}
-	// field OrderBy []vitess.io/vitess/go/vt/vtgate/engine.OrderbyParams
+	// field OrderBy []vitess.io/vitess/go/vt/vtgate/engine.OrderByParams
 	{
 		size += int64(cap(cached.OrderBy)) * int64(32)
 	}
@@ -390,14 +408,17 @@ func (cached *OrderedAggregate) CachedSize(alloc bool) int64 {
 	}
 	// field Aggregates []vitess.io/vitess/go/vt/vtgate/engine.AggregateParams
 	{
-		size += int64(cap(cached.Aggregates)) * int64(32)
+		size += int64(cap(cached.Aggregates)) * int64(48)
 		for _, elem := range cached.Aggregates {
 			size += elem.CachedSize(false)
 		}
 	}
-	// field Keys []int
+	// field GroupByKeys []vitess.io/vitess/go/vt/vtgate/engine.GroupByParams
 	{
-		size += int64(cap(cached.Keys)) * int64(8)
+		size += int64(cap(cached.GroupByKeys)) * int64(32)
+		for _, elem := range cached.GroupByKeys {
+			size += elem.CachedSize(false)
+		}
 	}
 	// field Input vitess.io/vitess/go/vt/vtgate/engine.Primitive
 	if cc, ok := cached.Input.(cachedObject); ok {
@@ -572,7 +593,7 @@ func (cached *Route) CachedSize(alloc bool) int64 {
 			size += elem.CachedSize(false)
 		}
 	}
-	// field OrderBy []vitess.io/vitess/go/vt/vtgate/engine.OrderbyParams
+	// field OrderBy []vitess.io/vitess/go/vt/vtgate/engine.OrderByParams
 	{
 		size += int64(cap(cached.OrderBy)) * int64(32)
 	}
diff --git a/go/vt/vtgate/engine/comparer.go b/go/vt/vtgate/engine/comparer.go
index 90b4e62f22..305593cba8 100644
--- a/go/vt/vtgate/engine/comparer.go
+++ b/go/vt/vtgate/engine/comparer.go
@@ -59,8 +59,8 @@ func (c *comparer) compare(r1, r2 []sqltypes.Value) (int, error) {
 	return cmp, nil
 }
 
-// extractSlices extracts the three fields of OrderbyParams into a slice of comparers
-func extractSlices(input []OrderbyParams) []*comparer {
+// extractSlices extracts the three fields of OrderByParams into a slice of comparers
+func extractSlices(input []OrderByParams) []*comparer {
 	var result []*comparer
 	for _, order := range input {
 		result = append(result, &comparer{
diff --git a/go/vt/vtgate/engine/memory_sort.go b/go/vt/vtgate/engine/memory_sort.go
index 560b029356..1b8c60635a 100644
--- a/go/vt/vtgate/engine/memory_sort.go
+++ b/go/vt/vtgate/engine/memory_sort.go
@@ -35,7 +35,7 @@ var _ Primitive = (*MemorySort)(nil)
 // MemorySort is a primitive that performs in-memory sorting.
 type MemorySort struct {
 	UpperLimit sqltypes.PlanValue
-	OrderBy    []OrderbyParams
+	OrderBy    []OrderByParams
 	Input      Primitive
 
 	// TruncateColumnCount specifies the number of columns to return
@@ -183,6 +183,9 @@ func (ms *MemorySort) description() PrimitiveDescription {
 	if !value.IsNull() {
 		other["UpperLimit"] = value.String()
 	}
+	if ms.TruncateColumnCount > 0 {
+		other["ResultColumns"] = ms.TruncateColumnCount
+	}
 	return PrimitiveDescription{
 		OperatorType: "Sort",
 		Variant:      "Memory",
@@ -191,7 +194,7 @@ func (ms *MemorySort) description() PrimitiveDescription {
 }
 
 func orderByParamsToString(i interface{}) string {
-	return i.(OrderbyParams).String()
+	return i.(OrderByParams).String()
 }
 
 //GenericJoin will iterate over arrays, slices or maps, and executes the f function to get a
diff --git a/go/vt/vtgate/engine/merge_sort.go b/go/vt/vtgate/engine/merge_sort.go
index 83dc64fae3..dbee39ee8e 100644
--- a/go/vt/vtgate/engine/merge_sort.go
+++ b/go/vt/vtgate/engine/merge_sort.go
@@ -50,7 +50,7 @@ var _ Primitive = (*MergeSort)(nil)
 // so that vdiff can use it. In that situation, only StreamExecute is used.
 type MergeSort struct {
 	Primitives              []StreamExecutor
-	OrderBy                 []OrderbyParams
+	OrderBy                 []OrderByParams
 	ScatterErrorsAsWarnings bool
 	noInputs
 	noTxNeeded
diff --git a/go/vt/vtgate/engine/ordered_aggregate.go b/go/vt/vtgate/engine/ordered_aggregate.go
index 9411ee1a24..747a77db04 100644
--- a/go/vt/vtgate/engine/ordered_aggregate.go
+++ b/go/vt/vtgate/engine/ordered_aggregate.go
@@ -20,6 +20,8 @@ import (
 	"fmt"
 	"strconv"
 
+	"vitess.io/vitess/go/vt/sqlparser"
+
 	"google.golang.org/protobuf/proto"
 
 	"vitess.io/vitess/go/sqltypes"
@@ -45,9 +47,9 @@ type OrderedAggregate struct {
 	// aggregation function: function opcode and input column number.
 	Aggregates []AggregateParams
 
-	// Keys specifies the input values that must be used for
+	// GroupByKeys specifies the input values that must be used for
 	// the aggregation key.
-	Keys []int
+	GroupByKeys []GroupByParams
 
 	// TruncateColumnCount specifies the number of columns to return
 	// in the final result. Rest of the columns are truncated
@@ -58,6 +60,21 @@ type OrderedAggregate struct {
 	Input Primitive
 }
 
+// GroupByParams specify the grouping key to be used.
+type GroupByParams struct {
+	KeyCol          int
+	WeightStringCol int
+	Expr            sqlparser.Expr
+}
+
+// String returns a string. Used for plan descriptions
+func (gbp GroupByParams) String() string {
+	if gbp.WeightStringCol == -1 || gbp.KeyCol == gbp.WeightStringCol {
+		return strconv.Itoa(gbp.KeyCol)
+	}
+	return fmt.Sprintf("(%d|%d)", gbp.KeyCol, gbp.WeightStringCol)
+}
+
 // AggregateParams specify the parameters for each aggregation.
 // It contains the opcode and input column number.
 type AggregateParams struct {
@@ -65,6 +82,7 @@ type AggregateParams struct {
 	Col    int
 	// Alias is set only for distinct opcodes.
 	Alias string `json:",omitempty"`
+	Expr  sqlparser.Expr
 }
 
 func (ap AggregateParams) isDistinct() bool {
@@ -201,7 +219,7 @@ func (oa *OrderedAggregate) execute(vcursor VCursor, bindVars map[string]*queryp
 		current, curDistinct = oa.convertRow(row)
 	}
 
-	if len(result.Rows) == 0 && len(oa.Keys) == 0 {
+	if len(result.Rows) == 0 && len(oa.GroupByKeys) == 0 {
 		// When doing aggregation without grouping keys, we need to produce a single row containing zero-value for the
 		// different aggregation functions
 		row, err := oa.createEmptyRow()
@@ -350,10 +368,18 @@ func (oa *OrderedAggregate) NeedsTransaction() bool {
 }
 
 func (oa *OrderedAggregate) keysEqual(row1, row2 []sqltypes.Value) (bool, error) {
-	for _, key := range oa.Keys {
-		cmp, err := evalengine.NullsafeCompare(row1[key], row2[key])
+	for _, key := range oa.GroupByKeys {
+		cmp, err := evalengine.NullsafeCompare(row1[key.KeyCol], row2[key.KeyCol])
 		if err != nil {
-			return false, err
+			_, isComparisonErr := err.(evalengine.UnsupportedComparisonError)
+			if !(isComparisonErr && key.WeightStringCol != -1) {
+				return false, err
+			}
+			key.KeyCol = key.WeightStringCol
+			cmp, err = evalengine.NullsafeCompare(row1[key.WeightStringCol], row2[key.WeightStringCol])
+			if err != nil {
+				return false, err
+			}
 		}
 		if cmp != 0 {
 			return false, nil
@@ -450,18 +476,20 @@ func aggregateParamsToString(in interface{}) string {
 	return in.(AggregateParams).String()
 }
 
-func intToString(i interface{}) string {
-	return strconv.Itoa(i.(int))
+func groupByParamsToString(i interface{}) string {
+	return i.(GroupByParams).String()
 }
 
 func (oa *OrderedAggregate) description() PrimitiveDescription {
 	aggregates := GenericJoin(oa.Aggregates, aggregateParamsToString)
-	groupBy := GenericJoin(oa.Keys, intToString)
+	groupBy := GenericJoin(oa.GroupByKeys, groupByParamsToString)
 	other := map[string]interface{}{
 		"Aggregates": aggregates,
 		"GroupBy":    groupBy,
 	}
-
+	if oa.TruncateColumnCount > 0 {
+		other["ResultColumns"] = oa.TruncateColumnCount
+	}
 	return PrimitiveDescription{
 		OperatorType: "Aggregate",
 		Variant:      "Ordered",
diff --git a/go/vt/vtgate/engine/route.go b/go/vt/vtgate/engine/route.go
index 3f00ee3c92..cc5df58aea 100644
--- a/go/vt/vtgate/engine/route.go
+++ b/go/vt/vtgate/engine/route.go
@@ -80,7 +80,7 @@ type Route struct {
 	// OrderBy specifies the key order for merge sorting. This will be
 	// set only for scatter queries that need the results to be
 	// merge-sorted.
-	OrderBy []OrderbyParams
+	OrderBy []OrderByParams
 
 	// TruncateColumnCount specifies the number of columns to return
 	// in the final result. Rest of the columns are truncated
@@ -122,9 +122,9 @@ func NewRoute(opcode RouteOpcode, keyspace *vindexes.Keyspace, query, fieldQuery
 	}
 }
 
-// OrderbyParams specifies the parameters for ordering.
+// OrderByParams specifies the parameters for ordering.
 // This is used for merge-sorting scatter queries.
-type OrderbyParams struct {
+type OrderByParams struct {
 	Col int
 	// WeightStringCol is the weight_string column that will be used for sorting.
 	// It is set to -1 if such a column is not added to the query
@@ -133,11 +133,15 @@ type OrderbyParams struct {
 	StarColFixedIndex int
 }
 
-func (obp OrderbyParams) String() string {
+// String returns a string. Used for plan descriptions
+func (obp OrderByParams) String() string {
 	val := strconv.Itoa(obp.Col)
 	if obp.StarColFixedIndex > obp.Col {
 		val = strconv.Itoa(obp.StarColFixedIndex)
 	}
+	if obp.WeightStringCol != -1 && obp.WeightStringCol != obp.Col {
+		val = fmt.Sprintf("(%s|%d)", val, obp.WeightStringCol)
+	}
 	if obp.Desc {
 		val += " DESC"
 	} else {
@@ -822,7 +826,7 @@ func (route *Route) description() PrimitiveDescription {
 }
 
 func orderByToString(in interface{}) string {
-	return in.(OrderbyParams).String()
+	return in.(OrderByParams).String()
 }
 
 // BvTableName is used to fill in the table name for information_schema queries with routed tables
diff --git a/go/vt/vtgate/planbuilder/abstract/queryprojection.go b/go/vt/vtgate/planbuilder/abstract/queryprojection.go
new file mode 100644
index 0000000000..2e97aa9a73
--- /dev/null
+++ b/go/vt/vtgate/planbuilder/abstract/queryprojection.go
@@ -0,0 +1,206 @@
+/*
+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 (
+	"encoding/json"
+	"strconv"
+
+	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/semantics"
+)
+
+type (
+	// SelectExpr provides whether the columns is aggregation expression or not.
+	SelectExpr struct {
+		Col  *sqlparser.AliasedExpr
+		Aggr bool
+	}
+
+	// QueryProjection contains the information about the projections, group by and order by expressions used to do horizon planning.
+	QueryProjection struct {
+		// If you change the contents here, please update the toString() method
+		SelectExprs        []SelectExpr
+		HasAggr            bool
+		GroupByExprs       []GroupBy
+		OrderExprs         []OrderBy
+		CanPushDownSorting bool
+	}
+
+	// OrderBy contains the expression to used in order by and also if ordering is needed at VTGate level then what the weight_string function expression to be sent down for evaluation.
+	OrderBy struct {
+		Inner         *sqlparser.Order
+		WeightStrExpr sqlparser.Expr
+	}
+
+	// GroupBy contains the expression to used in group by and also if grouping is needed at VTGate level then what the weight_string function expression to be sent down for evaluation.
+	GroupBy struct {
+		Inner         sqlparser.Expr
+		WeightStrExpr sqlparser.Expr
+	}
+)
+
+// CreateQPFromSelect created the QueryProjection for the input *sqlparser.Select
+func CreateQPFromSelect(sel *sqlparser.Select) (*QueryProjection, error) {
+	qp := &QueryProjection{}
+
+	hasNonAggr := false
+	for _, selExp := range sel.SelectExprs {
+		exp, ok := selExp.(*sqlparser.AliasedExpr)
+		if !ok {
+			return nil, semantics.Gen4NotSupportedF("%T in select list", selExp)
+		}
+		fExpr, ok := exp.Expr.(*sqlparser.FuncExpr)
+		if ok && fExpr.IsAggregate() {
+			if len(fExpr.Exprs) != 1 {
+				return nil, vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.SyntaxError, "aggregate functions take a single argument '%s'", sqlparser.String(fExpr))
+			}
+			if fExpr.Distinct {
+				return nil, semantics.Gen4NotSupportedF("distinct aggregation")
+			}
+			qp.HasAggr = true
+			qp.SelectExprs = append(qp.SelectExprs, SelectExpr{
+				Col:  exp,
+				Aggr: true,
+			})
+			continue
+		}
+
+		if sqlparser.ContainsAggregation(exp.Expr) {
+			return nil, vterrors.New(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: complex aggregate expression")
+		}
+		hasNonAggr = true
+		qp.SelectExprs = append(qp.SelectExprs, SelectExpr{Col: exp})
+	}
+
+	if hasNonAggr && qp.HasAggr && sel.GroupBy == nil {
+		return nil, vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.MixOfGroupFuncAndFields, "Mixing of aggregation and non-aggregation columns is not allowed if there is no GROUP BY clause")
+	}
+
+	for _, group := range sel.GroupBy {
+		expr, weightStrExpr, err := qp.getSimplifiedExpr(group, "group statement")
+		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))
+		}
+		qp.GroupByExprs = append(qp.GroupByExprs, GroupBy{Inner: expr, WeightStrExpr: weightStrExpr})
+	}
+
+	canPushDownSorting := true
+	for _, order := range sel.OrderBy {
+		expr, weightStrExpr, err := qp.getSimplifiedExpr(order.Expr, "order clause")
+		if err != nil {
+			return nil, err
+		}
+		qp.OrderExprs = append(qp.OrderExprs, OrderBy{
+			Inner: &sqlparser.Order{
+				Expr:      expr,
+				Direction: order.Direction,
+			},
+			WeightStrExpr: weightStrExpr,
+		})
+		canPushDownSorting = canPushDownSorting && !sqlparser.ContainsAggregation(weightStrExpr)
+	}
+	qp.CanPushDownSorting = canPushDownSorting
+
+	return qp, nil
+}
+
+// getSimplifiedExpr takes an expression used in ORDER BY or GROUP BY, which can reference both aliased columns and
+// column offsets, and returns an expression that is simpler to evaluate
+func (qp *QueryProjection) getSimplifiedExpr(e sqlparser.Expr, caller string) (expr sqlparser.Expr, weightStrExpr sqlparser.Expr, err error) {
+	// Order by is the column offset to be used from the select expressions
+	// Eg - select id from music order by 1
+	literalExpr, isLiteral := e.(*sqlparser.Literal)
+	if isLiteral && literalExpr.Type == sqlparser.IntVal {
+		num, _ := strconv.Atoi(literalExpr.Val)
+		if num > len(qp.SelectExprs) {
+			return nil, nil, vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.BadFieldError, "Unknown column '%d' in '%s'", num, caller)
+		}
+		aliasedExpr := qp.SelectExprs[num-1].Col
+		expr = aliasedExpr.Expr
+		if !aliasedExpr.As.IsEmpty() {
+			// the column is aliased, so we'll add an expression ordering by the alias and not the underlying expression
+			expr = &sqlparser.ColName{
+				Name: aliasedExpr.As,
+			}
+		}
+
+		return expr, aliasedExpr.Expr, nil
+	}
+
+	// If the ORDER BY is against a column alias, we need to remember the expression
+	// behind the alias. The weightstring(.) calls needs to be done against that expression and not the alias.
+	// Eg - select music.foo as bar, weightstring(music.foo) from music order by bar
+	colExpr, isColName := e.(*sqlparser.ColName)
+	if isColName && colExpr.Qualifier.IsEmpty() {
+		for _, selectExpr := range qp.SelectExprs {
+			isAliasExpr := !selectExpr.Col.As.IsEmpty()
+			if isAliasExpr && colExpr.Name.Equal(selectExpr.Col.As) {
+				return e, selectExpr.Col.Expr, nil
+			}
+		}
+	}
+
+	return e, e, nil
+}
+
+// toString should only be used for tests
+func (qp *QueryProjection) toString() string {
+	type output struct {
+		Select   []string
+		Grouping []string
+		OrderBy  []string
+	}
+	out := output{
+		Select:   []string{},
+		Grouping: []string{},
+		OrderBy:  []string{},
+	}
+
+	for _, expr := range qp.SelectExprs {
+		e := sqlparser.String(expr.Col.Expr)
+
+		if expr.Aggr {
+			e = "aggr: " + e
+		}
+
+		if !expr.Col.As.IsEmpty() {
+			e += " AS " + expr.Col.As.String()
+		}
+		out.Select = append(out.Select, e)
+	}
+
+	for _, expr := range qp.GroupByExprs {
+		out.Grouping = append(out.Grouping, sqlparser.String(expr.Inner))
+	}
+	for _, expr := range qp.OrderExprs {
+		out.OrderBy = append(out.OrderBy, sqlparser.String(expr.Inner))
+	}
+
+	bytes, _ := json.MarshalIndent(out, "", "  ")
+	return string(bytes)
+}
+
+// NeedsAggregation returns true if we either have aggregate functions or grouping defined
+func (qp *QueryProjection) NeedsAggregation() bool {
+	return qp.HasAggr || len(qp.GroupByExprs) > 0
+}
diff --git a/go/vt/vtgate/planbuilder/grouping.go b/go/vt/vtgate/planbuilder/grouping.go
index fec8ad3841..1f17aa4ec5 100644
--- a/go/vt/vtgate/planbuilder/grouping.go
+++ b/go/vt/vtgate/planbuilder/grouping.go
@@ -20,6 +20,7 @@ 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"
 )
 
 func planGroupBy(pb *primitiveBuilder, input logicalPlan, groupBy sqlparser.GroupBy) (logicalPlan, error) {
@@ -76,7 +77,7 @@ func planGroupBy(pb *primitiveBuilder, input logicalPlan, groupBy sqlparser.Grou
 			default:
 				return nil, vterrors.New(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: only simple references allowed")
 			}
-			node.eaggr.Keys = append(node.eaggr.Keys, colNumber)
+			node.eaggr.GroupByKeys = append(node.eaggr.GroupByKeys, engine.GroupByParams{KeyCol: colNumber, WeightStringCol: -1})
 		}
 		// Append the distinct aggregate if any.
 		if node.extraDistinct != nil {
@@ -109,7 +110,7 @@ func planDistinct(input logicalPlan) (logicalPlan, error) {
 			if rc.column.Origin() == node {
 				return newDistinct(node), nil
 			}
-			node.eaggr.Keys = append(node.eaggr.Keys, i)
+			node.eaggr.GroupByKeys = append(node.eaggr.GroupByKeys, engine.GroupByParams{KeyCol: i, WeightStringCol: -1})
 		}
 		newInput, err := planDistinct(node.input)
 		if err != nil {
diff --git a/go/vt/vtgate/planbuilder/memory_sort.go b/go/vt/vtgate/planbuilder/memory_sort.go
index 4b9890ce0c..a95d279178 100644
--- a/go/vt/vtgate/planbuilder/memory_sort.go
+++ b/go/vt/vtgate/planbuilder/memory_sort.go
@@ -83,7 +83,7 @@ func newMemorySort(plan logicalPlan, orderBy sqlparser.OrderBy) (*memorySort, er
 			return nil, fmt.Errorf("unsupported: memory sort: order by must reference a column in the select list: %s", sqlparser.String(order))
 		}
 
-		ob := engine.OrderbyParams{
+		ob := engine.OrderByParams{
 			Col:               colNumber,
 			WeightStringCol:   -1,
 			Desc:              order.Direction == sqlparser.DescOrder,
diff --git a/go/vt/vtgate/planbuilder/memory_sort_gen4.go b/go/vt/vtgate/planbuilder/memory_sort_gen4.go
index 9f93df3a49..34ec8a6a71 100644
--- a/go/vt/vtgate/planbuilder/memory_sort_gen4.go
+++ b/go/vt/vtgate/planbuilder/memory_sort_gen4.go
@@ -28,7 +28,7 @@ import (
 var _ logicalPlan = (*memorySortGen4)(nil)
 
 type memorySortGen4 struct {
-	orderBy             []engine.OrderbyParams
+	orderBy             []engine.OrderByParams
 	input               logicalPlan
 	truncateColumnCount int
 }
diff --git a/go/vt/vtgate/planbuilder/ordered_aggregate.go b/go/vt/vtgate/planbuilder/ordered_aggregate.go
index e3a82a08f4..d7e9a0a29b 100644
--- a/go/vt/vtgate/planbuilder/ordered_aggregate.go
+++ b/go/vt/vtgate/planbuilder/ordered_aggregate.go
@@ -331,14 +331,15 @@ func (oa *orderedAggregate) needDistinctHandling(pb *primitiveBuilder, funcExpr
 // compare those instead. This is because we currently don't have the
 // ability to mimic mysql's collation behavior.
 func (oa *orderedAggregate) Wireup(plan logicalPlan, jt *jointab) error {
-	for i, colNumber := range oa.eaggr.Keys {
-		rc := oa.resultColumns[colNumber]
+	for i, gbk := range oa.eaggr.GroupByKeys {
+		rc := oa.resultColumns[gbk.KeyCol]
 		if sqltypes.IsText(rc.column.typ) {
 			if weightcolNumber, ok := oa.weightStrings[rc]; ok {
-				oa.eaggr.Keys[i] = weightcolNumber
+				oa.eaggr.GroupByKeys[i].WeightStringCol = weightcolNumber
+				oa.eaggr.GroupByKeys[i].KeyCol = weightcolNumber
 				continue
 			}
-			weightcolNumber, err := oa.input.SupplyWeightString(colNumber)
+			weightcolNumber, err := oa.input.SupplyWeightString(gbk.KeyCol)
 			if err != nil {
 				_, isUnsupportedErr := err.(UnsupportedSupplyWeightString)
 				if isUnsupportedErr {
@@ -347,7 +348,8 @@ func (oa *orderedAggregate) Wireup(plan logicalPlan, jt *jointab) error {
 				return err
 			}
 			oa.weightStrings[rc] = weightcolNumber
-			oa.eaggr.Keys[i] = weightcolNumber
+			oa.eaggr.GroupByKeys[i].WeightStringCol = weightcolNumber
+			oa.eaggr.GroupByKeys[i].KeyCol = weightcolNumber
 			oa.eaggr.TruncateColumnCount = len(oa.resultColumns)
 		}
 	}
diff --git a/go/vt/vtgate/planbuilder/ordering.go b/go/vt/vtgate/planbuilder/ordering.go
index d3d53f4d72..4c7bb8f957 100644
--- a/go/vt/vtgate/planbuilder/ordering.go
+++ b/go/vt/vtgate/planbuilder/ordering.go
@@ -76,7 +76,7 @@ func planOAOrdering(pb *primitiveBuilder, orderBy sqlparser.OrderBy, oa *ordered
 	}
 
 	// referenced tracks the keys referenced by the order by clause.
-	referenced := make([]bool, len(oa.eaggr.Keys))
+	referenced := make([]bool, len(oa.eaggr.GroupByKeys))
 	postSort := false
 	selOrderBy := make(sqlparser.OrderBy, 0, len(orderBy))
 	for _, order := range orderBy {
@@ -103,8 +103,8 @@ func planOAOrdering(pb *primitiveBuilder, orderBy sqlparser.OrderBy, oa *ordered
 
 		// Match orderByCol against the group by columns.
 		found := false
-		for j, key := range oa.eaggr.Keys {
-			if oa.resultColumns[key].column != orderByCol {
+		for j, groupBy := range oa.eaggr.GroupByKeys {
+			if oa.resultColumns[groupBy.KeyCol].column != orderByCol {
 				continue
 			}
 
@@ -119,12 +119,12 @@ func planOAOrdering(pb *primitiveBuilder, orderBy sqlparser.OrderBy, oa *ordered
 	}
 
 	// Append any unreferenced keys at the end of the order by.
-	for i, key := range oa.eaggr.Keys {
+	for i, groupByKey := range oa.eaggr.GroupByKeys {
 		if referenced[i] {
 			continue
 		}
 		// Build a brand new reference for the key.
-		col, err := BuildColName(oa.input.ResultColumns(), key)
+		col, err := BuildColName(oa.input.ResultColumns(), groupByKey.KeyCol)
 		if err != nil {
 			return nil, vterrors.Wrapf(err, "generating order by clause")
 		}
@@ -313,7 +313,7 @@ func planRouteOrdering(orderBy sqlparser.OrderBy, node *route) (logicalPlan, err
 			}
 		}
 
-		ob := engine.OrderbyParams{
+		ob := engine.OrderByParams{
 			Col:               colNumber,
 			WeightStringCol:   -1,
 			Desc:              order.Direction == sqlparser.DescOrder,
diff --git a/go/vt/vtgate/planbuilder/queryprojection.go b/go/vt/vtgate/planbuilder/queryprojection.go
deleted file mode 100644
index 1096a7d6a7..0000000000
--- a/go/vt/vtgate/planbuilder/queryprojection.go
+++ /dev/null
@@ -1,129 +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 (
-	"strconv"
-
-	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/semantics"
-)
-
-type queryProjection struct {
-	selectExprs []*sqlparser.AliasedExpr
-	aggrExprs   []*sqlparser.AliasedExpr
-	orderExprs  []orderBy
-}
-
-type orderBy struct {
-	inner         *sqlparser.Order
-	weightStrExpr sqlparser.Expr
-}
-
-func newQueryProjection() *queryProjection {
-	return &queryProjection{}
-}
-
-func createQPFromSelect(sel *sqlparser.Select) (*queryProjection, error) {
-	qp := newQueryProjection()
-
-	for _, selExp := range sel.SelectExprs {
-		exp, ok := selExp.(*sqlparser.AliasedExpr)
-		if !ok {
-			return nil, semantics.Gen4NotSupportedF("%T in select list", selExp)
-		}
-		fExpr, ok := exp.Expr.(*sqlparser.FuncExpr)
-		if ok && fExpr.IsAggregate() {
-			if len(fExpr.Exprs) != 1 {
-				return nil, vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.SyntaxError, "aggregate functions take a single argument '%s'", sqlparser.String(fExpr))
-			}
-			qp.aggrExprs = append(qp.aggrExprs, exp)
-			continue
-		}
-		if nodeHasAggregates(exp.Expr) {
-			return nil, vterrors.New(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: complex aggregate expression")
-		}
-		qp.selectExprs = append(qp.selectExprs, exp)
-	}
-
-	if len(qp.selectExprs) > 0 && len(qp.aggrExprs) > 0 {
-		return nil, semantics.Gen4NotSupportedF("aggregation and non-aggregation expressions, together are not supported in cross-shard query")
-	}
-
-	allExpr := append(qp.selectExprs, qp.aggrExprs...)
-
-	for _, order := range sel.OrderBy {
-		err := qp.addOrderBy(order, allExpr)
-		if err != nil {
-			return nil, err
-		}
-	}
-	return qp, nil
-}
-
-func (qp *queryProjection) addOrderBy(order *sqlparser.Order, allExpr []*sqlparser.AliasedExpr) error {
-	// Order by is the column offset to be used from the select expressions
-	// Eg - select id from music order by 1
-	literalExpr, isLiteral := order.Expr.(*sqlparser.Literal)
-	if isLiteral && literalExpr.Type == sqlparser.IntVal {
-		num, _ := strconv.Atoi(literalExpr.Val)
-		if num > len(allExpr) {
-			return vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.BadFieldError, "Unknown column '%d' in 'order clause'", num)
-		}
-		aliasedExpr := allExpr[num-1]
-		expr := aliasedExpr.Expr
-		if !aliasedExpr.As.IsEmpty() {
-			// the column is aliased, so we'll add an expression ordering by the alias and not the underlying expression
-			expr = &sqlparser.ColName{
-				Name: aliasedExpr.As,
-			}
-		}
-		qp.orderExprs = append(qp.orderExprs, orderBy{
-			inner: &sqlparser.Order{
-				Expr:      expr,
-				Direction: order.Direction,
-			},
-			weightStrExpr: aliasedExpr.Expr,
-		})
-		return nil
-	}
-
-	// If the ORDER BY is against a column alias, we need to remember the expression
-	// behind the alias. The weightstring(.) calls needs to be done against that expression and not the alias.
-	// Eg - select music.foo as bar, weightstring(music.foo) from music order by bar
-	colExpr, isColName := order.Expr.(*sqlparser.ColName)
-	if isColName && colExpr.Qualifier.IsEmpty() {
-		for _, expr := range allExpr {
-			isAliasExpr := !expr.As.IsEmpty()
-			if isAliasExpr && colExpr.Name.Equal(expr.As) {
-				qp.orderExprs = append(qp.orderExprs, orderBy{
-					inner:         order,
-					weightStrExpr: expr.Expr,
-				})
-				return nil
-			}
-		}
-	}
-
-	qp.orderExprs = append(qp.orderExprs, orderBy{
-		inner:         order,
-		weightStrExpr: order.Expr,
-	})
-	return nil
-}
diff --git a/go/vt/vtgate/planbuilder/route_planning.go b/go/vt/vtgate/planbuilder/route_planning.go
index 84fdec1574..59f9dade24 100644
--- a/go/vt/vtgate/planbuilder/route_planning.go
+++ b/go/vt/vtgate/planbuilder/route_planning.go
@@ -80,7 +80,7 @@ func newBuildSelectPlan(sel *sqlparser.Select, vschema ContextVSchema) (engine.P
 		return nil, err
 	}
 
-	plan, err = planHorizon(sel, plan, semTable)
+	plan, err = planHorizon(sel, plan, semTable, vschema)
 	if err != nil {
 		return nil, err
 	}
@@ -154,7 +154,7 @@ func planLimit(limit *sqlparser.Limit, plan logicalPlan) (logicalPlan, error) {
 	return lPlan, nil
 }
 
-func planHorizon(sel *sqlparser.Select, plan logicalPlan, semTable *semantics.SemTable) (logicalPlan, error) {
+func planHorizon(sel *sqlparser.Select, plan logicalPlan, semTable *semantics.SemTable, vschema ContextVSchema) (logicalPlan, error) {
 	rb, ok := plan.(*route)
 	if !ok && semTable.ProjectionErr != nil {
 		return nil, semTable.ProjectionErr
@@ -169,37 +169,54 @@ func planHorizon(sel *sqlparser.Select, plan logicalPlan, semTable *semantics.Se
 		return nil, err
 	}
 
-	qp, err := createQPFromSelect(sel)
+	qp, err := abstract.CreateQPFromSelect(sel)
 	if err != nil {
 		return nil, err
 	}
-	for _, e := range qp.selectExprs {
-		if _, _, err := pushProjection(e, plan, semTable, true); err != nil {
-			return nil, err
-		}
-	}
 
-	for _, expr := range qp.aggrExprs {
-		funcExpr, ok := expr.Expr.(*sqlparser.FuncExpr)
-		if !ok {
-			return nil, vterrors.New(vtrpcpb.Code_INTERNAL, "expected an aggregation here")
+	var needsTruncation, vtgateGrouping bool
+	if qp.NeedsAggregation() {
+		plan, needsTruncation, vtgateGrouping, err = planAggregations(qp, plan, semTable, vschema)
+		if err != nil {
+			return nil, err
 		}
-		if funcExpr.Distinct {
-			return nil, semantics.Gen4NotSupportedF("distinct aggregation")
+	} else {
+		for _, e := range qp.SelectExprs {
+			if _, _, err := pushProjection(e.Col, plan, semTable, true, false); err != nil {
+				return nil, err
+			}
 		}
 	}
 
-	if len(qp.aggrExprs) > 0 {
-		plan, err = planAggregations(qp, plan, semTable)
+	if len(qp.OrderExprs) > 0 {
+		var colAdded bool
+		plan, colAdded, err = planOrderBy(qp, qp.OrderExprs, plan, semTable)
 		if err != nil {
 			return nil, err
 		}
+		needsTruncation = needsTruncation || colAdded
 	}
-	if len(sel.OrderBy) > 0 {
-		plan, err = planOrderBy(qp, qp.orderExprs, plan, semTable)
+
+	if qp.CanPushDownSorting && vtgateGrouping {
+		var colAdded bool
+		plan, colAdded, err = planOrderByUsingGroupBy(qp, plan, semTable)
 		if err != nil {
 			return nil, err
 		}
+		needsTruncation = needsTruncation || colAdded
+	}
+
+	if needsTruncation {
+		switch p := plan.(type) {
+		case *route:
+			p.eroute.SetTruncateColumnCount(sel.GetColumnCount())
+		case *orderedAggregate:
+			p.eaggr.SetTruncateColumnCount(sel.GetColumnCount())
+		case *memorySort:
+			p.truncater.SetTruncateColumnCount(sel.GetColumnCount())
+		default:
+			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "plan type not known for column truncation: %T", plan)
+		}
 	}
 
 	return plan, nil
@@ -223,9 +240,6 @@ func checkUnsupportedConstructs(sel *sqlparser.Select) error {
 	if sel.Distinct {
 		return semantics.Gen4NotSupportedF("DISTINCT")
 	}
-	if sel.GroupBy != nil {
-		return semantics.Gen4NotSupportedF("GROUP BY")
-	}
 	if sel.Having != nil {
 		return semantics.Gen4NotSupportedF("HAVING")
 	}
diff --git a/go/vt/vtgate/planbuilder/selectGen4.go b/go/vt/vtgate/planbuilder/selectGen4.go
index 42520e0440..583620301a 100644
--- a/go/vt/vtgate/planbuilder/selectGen4.go
+++ b/go/vt/vtgate/planbuilder/selectGen4.go
@@ -18,6 +18,7 @@ package planbuilder
 
 import (
 	"vitess.io/vitess/go/sqltypes"
+	"vitess.io/vitess/go/vt/vtgate/planbuilder/abstract"
 
 	"vitess.io/vitess/go/vt/vtgate/semantics"
 
@@ -28,7 +29,7 @@ import (
 	"vitess.io/vitess/go/vt/vtgate/engine"
 )
 
-func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *semantics.SemTable, inner bool) (int, bool, error) {
+func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *semantics.SemTable, inner bool, reuseCol bool) (int, bool, error) {
 	switch node := plan.(type) {
 	case *route:
 		value, err := makePlanValue(expr.Expr)
@@ -41,9 +42,10 @@ func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *sem
 			return 0, false, vterrors.New(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: cross-shard left join and column expressions")
 		}
 		sel := node.Select.(*sqlparser.Select)
-		i := checkIfAlreadyExists(expr, sel)
-		if i != -1 {
-			return i, false, nil
+		if reuseCol {
+			if i := checkIfAlreadyExists(expr, sel); i != -1 {
+				return i, false, nil
+			}
 		}
 		expr = removeQualifierFromColName(expr)
 
@@ -58,14 +60,14 @@ func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *sem
 		var appended bool
 		switch {
 		case deps.IsSolvedBy(lhsSolves):
-			offset, added, err := pushProjection(expr, node.Left, semTable, inner)
+			offset, added, err := pushProjection(expr, node.Left, semTable, inner, true)
 			if err != nil {
 				return 0, false, err
 			}
 			column = -(offset + 1)
 			appended = added
 		case deps.IsSolvedBy(rhsSolves):
-			offset, added, err := pushProjection(expr, node.Right, semTable, inner && node.Opcode != engine.LeftJoin)
+			offset, added, err := pushProjection(expr, node.Right, semTable, inner && node.Opcode != engine.LeftJoin, true)
 			if err != nil {
 				return 0, false, err
 			}
@@ -74,7 +76,7 @@ func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *sem
 		default:
 			return 0, false, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "unknown dependencies for %s", sqlparser.String(expr))
 		}
-		if !appended {
+		if reuseCol && !appended {
 			for idx, col := range node.Cols {
 				if column == col {
 					return idx, false, nil
@@ -119,82 +121,216 @@ func checkIfAlreadyExists(expr *sqlparser.AliasedExpr, sel *sqlparser.Select) in
 	return -1
 }
 
-func planAggregations(qp *queryProjection, plan logicalPlan, semTable *semantics.SemTable) (logicalPlan, error) {
-	eaggr := &engine.OrderedAggregate{}
-	oa := &orderedAggregate{
-		resultsBuilder: resultsBuilder{
-			logicalPlanCommon: newBuilderCommon(plan),
-			weightStrings:     make(map[*resultColumn]int),
-			truncater:         eaggr,
-		},
-		eaggr: eaggr,
+func planAggregations(qp *abstract.QueryProjection, plan logicalPlan, semTable *semantics.SemTable, vschema ContextVSchema) (logicalPlan, bool, bool, error) {
+	newPlan := plan
+	var oa *orderedAggregate
+	if !hasUniqueVindex(vschema, semTable, qp.GroupByExprs) {
+		eaggr := &engine.OrderedAggregate{}
+		oa = &orderedAggregate{
+			resultsBuilder: resultsBuilder{
+				logicalPlanCommon: newBuilderCommon(plan),
+				weightStrings:     make(map[*resultColumn]int),
+				truncater:         eaggr,
+			},
+			eaggr: eaggr,
+		}
+		newPlan = oa
 	}
-	for _, e := range qp.aggrExprs {
-		offset, _, err := pushProjection(e, plan, semTable, true)
+
+	for _, e := range qp.SelectExprs {
+		offset, _, err := pushProjection(e.Col, plan, semTable, true, false)
 		if err != nil {
-			return nil, err
+			return nil, false, false, err
 		}
-		fExpr := e.Expr.(*sqlparser.FuncExpr)
-		opcode := engine.SupportedAggregates[fExpr.Name.Lowered()]
-		oa.eaggr.Aggregates = append(oa.eaggr.Aggregates, engine.AggregateParams{
-			Opcode: opcode,
-			Col:    offset,
-		})
+		if e.Aggr && oa != nil {
+			fExpr := e.Col.Expr.(*sqlparser.FuncExpr)
+			opcode := engine.SupportedAggregates[fExpr.Name.Lowered()]
+			oa.eaggr.Aggregates = append(oa.eaggr.Aggregates, engine.AggregateParams{
+				Opcode: opcode,
+				Col:    offset,
+				Expr:   fExpr,
+			})
+		}
+	}
+
+	var colAdded bool
+	for _, groupExpr := range qp.GroupByExprs {
+		added, err := planGroupByGen4(groupExpr, newPlan, semTable)
+		if err != nil {
+			return nil, false, false, err
+		}
+		colAdded = colAdded || added
 	}
-	return oa, nil
+
+	if !qp.CanPushDownSorting && oa != nil {
+		var orderExprs []abstract.OrderBy
+		// if we can't at a later stage push down the sorting to our inputs, we have to do ordering here
+		for _, groupExpr := range qp.GroupByExprs {
+			orderExprs = append(orderExprs, abstract.OrderBy{
+				Inner:         &sqlparser.Order{Expr: groupExpr.Inner},
+				WeightStrExpr: groupExpr.WeightStrExpr},
+			)
+		}
+		if len(orderExprs) > 0 {
+			newInput, added, err := planOrderBy(qp, orderExprs, plan, semTable)
+			if err != nil {
+				return nil, false, false, err
+			}
+			oa.input = newInput
+			return oa, colAdded || added, true, nil
+		}
+	}
+	return newPlan, colAdded, oa != nil, nil
 }
 
-func planOrderBy(qp *queryProjection, orderExprs []orderBy, plan logicalPlan, semTable *semantics.SemTable) (logicalPlan, error) {
+func hasUniqueVindex(vschema ContextVSchema, semTable *semantics.SemTable, groupByExprs []abstract.GroupBy) bool {
+	for _, groupByExpr := range groupByExprs {
+		col, isCol := groupByExpr.WeightStrExpr.(*sqlparser.ColName)
+		if !isCol {
+			continue
+		}
+		ts := semTable.Dependencies(groupByExpr.WeightStrExpr)
+		tableInfo, err := semTable.TableInfoFor(ts)
+		if err != nil {
+			continue
+		}
+		tableName, err := tableInfo.Name()
+		if err != nil {
+			continue
+		}
+		vschemaTable, _, _, _, _, err := vschema.FindTableOrVindex(tableName)
+		if err != nil {
+			continue
+		}
+		for _, vindex := range vschemaTable.ColumnVindexes {
+			if len(vindex.Columns) > 1 || !vindex.Vindex.IsUnique() {
+				continue
+			}
+			if col.Name.Equal(vindex.Columns[0]) {
+				return true
+			}
+		}
+	}
+	return false
+}
+
+func planGroupByGen4(groupExpr abstract.GroupBy, plan logicalPlan, semTable *semantics.SemTable) (bool, error) {
+	switch node := plan.(type) {
+	case *route:
+		sel := node.Select.(*sqlparser.Select)
+		sel.GroupBy = append(sel.GroupBy, groupExpr.Inner)
+		return false, nil
+	case *orderedAggregate:
+		keyCol, weightStringOffset, colAdded, err := wrapAndPushExpr(groupExpr.Inner, groupExpr.WeightStrExpr, node.input, semTable)
+		if err != nil {
+			return false, err
+		}
+		node.eaggr.GroupByKeys = append(node.eaggr.GroupByKeys, engine.GroupByParams{KeyCol: keyCol, WeightStringCol: weightStringOffset, Expr: groupExpr.WeightStrExpr})
+		colAddedRecursively, err := planGroupByGen4(groupExpr, node.input, semTable)
+		if err != nil {
+			return false, err
+		}
+		return colAdded || colAddedRecursively, nil
+	default:
+		return false, semantics.Gen4NotSupportedF("group by on: %T", plan)
+	}
+}
+
+func planOrderByUsingGroupBy(qp *abstract.QueryProjection, plan logicalPlan, semTable *semantics.SemTable) (logicalPlan, bool, error) {
+	var orderExprs []abstract.OrderBy
+	for _, groupExpr := range qp.GroupByExprs {
+		addExpr := true
+		for _, orderExpr := range qp.OrderExprs {
+			if sqlparser.EqualsExpr(groupExpr.Inner, orderExpr.Inner.Expr) {
+				addExpr = false
+				break
+			}
+		}
+		if addExpr {
+			orderExprs = append(orderExprs, abstract.OrderBy{
+				Inner:         &sqlparser.Order{Expr: groupExpr.Inner},
+				WeightStrExpr: groupExpr.WeightStrExpr},
+			)
+		}
+	}
+	if len(orderExprs) > 0 {
+		return planOrderBy(qp, orderExprs, plan, semTable)
+	}
+	return plan, false, nil
+}
+
+func planOrderBy(qp *abstract.QueryProjection, orderExprs []abstract.OrderBy, plan logicalPlan, semTable *semantics.SemTable) (logicalPlan, bool, error) {
 	switch plan := plan.(type) {
 	case *route:
 		return planOrderByForRoute(orderExprs, plan, semTable)
 	case *joinGen4:
 		return planOrderByForJoin(qp, orderExprs, plan, semTable)
+	case *orderedAggregate:
+		for _, order := range orderExprs {
+			if sqlparser.ContainsAggregation(order.WeightStrExpr) {
+				ms, err := createMemorySortPlanOnAggregation(plan, orderExprs)
+				if err != nil {
+					return nil, false, err
+				}
+				return ms, false, nil
+			}
+		}
+		newInput, colAdded, err := planOrderBy(qp, orderExprs, plan.input, semTable)
+		if err != nil {
+			return nil, false, err
+		}
+		plan.input = newInput
+		return plan, colAdded, nil
+	case *memorySort:
+		return plan, false, nil
 	default:
-		return nil, semantics.Gen4NotSupportedF("ordering on complex query")
+		return nil, false, semantics.Gen4NotSupportedF("ordering on complex query %T", plan)
 	}
 }
 
-func planOrderByForRoute(orderExprs []orderBy, plan *route, semTable *semantics.SemTable) (logicalPlan, error) {
+func planOrderByForRoute(orderExprs []abstract.OrderBy, plan *route, semTable *semantics.SemTable) (logicalPlan, bool, error) {
 	origColCount := plan.Select.GetColumnCount()
 	for _, order := range orderExprs {
-		expr := order.inner.Expr
-		offset, err := wrapExprAndPush(expr, plan, semTable)
+		offset, weightStringOffset, _, err := wrapAndPushExpr(order.Inner.Expr, order.WeightStrExpr, plan, semTable)
 		if err != nil {
-			return nil, err
-		}
-		colName, ok := order.inner.Expr.(*sqlparser.ColName)
-		if !ok {
-			return nil, semantics.Gen4NotSupportedF("order by non-column expression")
+			return nil, false, err
 		}
 
-		table := semTable.Dependencies(colName)
-		tbl, err := semTable.TableInfoFor(table)
-		if err != nil {
-			return nil, err
-		}
-		weightStringNeeded := needsWeightString(tbl, colName)
-
-		weightStringOffset := -1
-		if weightStringNeeded {
-			weightStringOffset, err = wrapExprAndPush(weightStringFor(order.weightStrExpr), plan, semTable)
-			if err != nil {
-				return nil, err
-			}
-		}
-
-		plan.eroute.OrderBy = append(plan.eroute.OrderBy, engine.OrderbyParams{
+		plan.eroute.OrderBy = append(plan.eroute.OrderBy, engine.OrderByParams{
 			Col:             offset,
 			WeightStringCol: weightStringOffset,
-			Desc:            order.inner.Direction == sqlparser.DescOrder,
+			Desc:            order.Inner.Direction == sqlparser.DescOrder,
 		})
-		plan.Select.AddOrder(order.inner)
+		plan.Select.AddOrder(order.Inner)
+	}
+	return plan, origColCount != plan.Select.GetColumnCount(), nil
+}
+
+func wrapAndPushExpr(expr sqlparser.Expr, weightStrExpr sqlparser.Expr, plan logicalPlan, semTable *semantics.SemTable) (int, int, bool, error) {
+	offset, added, err := pushProjection(&sqlparser.AliasedExpr{Expr: expr}, plan, semTable, true, true)
+	if err != nil {
+		return 0, 0, false, err
 	}
-	if origColCount != plan.Select.GetColumnCount() {
-		plan.eroute.TruncateColumnCount = origColCount
+	colName, ok := expr.(*sqlparser.ColName)
+	if !ok {
+		return 0, 0, false, semantics.Gen4NotSupportedF("group by/order by non-column expression")
 	}
+	table := semTable.Dependencies(colName)
+	tbl, err := semTable.TableInfoFor(table)
+	if err != nil {
+		return 0, 0, false, err
+	}
+	wsNeeded := needsWeightString(tbl, colName)
 
-	return plan, nil
+	weightStringOffset := -1
+	var wAdded bool
+	if wsNeeded {
+		weightStringOffset, wAdded, err = pushProjection(&sqlparser.AliasedExpr{Expr: weightStringFor(weightStrExpr)}, plan, semTable, true, true)
+		if err != nil {
+			return 0, 0, false, err
+		}
+	}
+	return offset, weightStringOffset, added || wAdded, nil
 }
 
 func weightStringFor(expr sqlparser.Expr) sqlparser.Expr {
@@ -218,22 +354,19 @@ func needsWeightString(tbl semantics.TableInfo, colName *sqlparser.ColName) bool
 	return true // we didn't find the column. better to add just to be safe1
 }
 
-func wrapExprAndPush(exp sqlparser.Expr, plan logicalPlan, semTable *semantics.SemTable) (int, error) {
-	aliasedExpr := &sqlparser.AliasedExpr{Expr: exp}
-	offset, _, err := pushProjection(aliasedExpr, plan, semTable, true)
-	return offset, err
-}
-
-func planOrderByForJoin(qp *queryProjection, orderExprs []orderBy, plan *joinGen4, semTable *semantics.SemTable) (logicalPlan, error) {
+func planOrderByForJoin(qp *abstract.QueryProjection, orderExprs []abstract.OrderBy, plan *joinGen4, semTable *semantics.SemTable) (logicalPlan, bool, error) {
 	if allLeft(orderExprs, semTable, plan.Left.ContainsTables()) {
-		newLeft, err := planOrderBy(qp, orderExprs, plan.Left, semTable)
+		newLeft, _, err := planOrderBy(qp, orderExprs, plan.Left, semTable)
 		if err != nil {
-			return nil, err
+			return nil, false, err
 		}
 		plan.Left = newLeft
-		return plan, nil
+		return plan, false, nil
 	}
+	return createMemorySortPlan(plan, orderExprs, semTable)
+}
 
+func createMemorySortPlanOnAggregation(plan *orderedAggregate, orderExprs []abstract.OrderBy) (logicalPlan, error) {
 	primitive := &engine.MemorySort{}
 	ms := &memorySort{
 		resultsBuilder: resultsBuilder{
@@ -245,45 +378,65 @@ func planOrderByForJoin(qp *queryProjection, orderExprs []orderBy, plan *joinGen
 	}
 
 	for _, order := range orderExprs {
-		expr := order.inner.Expr
-		offset, err := wrapExprAndPush(expr, plan, semTable)
-		if err != nil {
-			return nil, err
+		offset, woffset, found := findExprInOrderedAggr(plan, order)
+		if !found {
+			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "expected to find the order by expression (%s) in orderedAggregate", sqlparser.String(order.Inner))
 		}
+		ms.eMemorySort.OrderBy = append(ms.eMemorySort.OrderBy, engine.OrderByParams{
+			Col:               offset,
+			WeightStringCol:   woffset,
+			Desc:              order.Inner.Direction == sqlparser.DescOrder,
+			StarColFixedIndex: offset,
+		})
+	}
+	return ms, nil
+}
 
-		table := semTable.Dependencies(expr)
-		tbl, err := semTable.TableInfoFor(table)
-		if err != nil {
-			return nil, err
+func findExprInOrderedAggr(plan *orderedAggregate, order abstract.OrderBy) (int, int, bool) {
+	for _, key := range plan.eaggr.GroupByKeys {
+		if sqlparser.EqualsExpr(order.WeightStrExpr, key.Expr) {
+			return key.KeyCol, key.WeightStringCol, true
 		}
-		col, isCol := expr.(*sqlparser.ColName)
-		if !isCol {
-			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "order by complex expression not supported")
+	}
+	for _, aggregate := range plan.eaggr.Aggregates {
+		if sqlparser.EqualsExpr(order.WeightStrExpr, aggregate.Expr) {
+			return aggregate.Col, -1, true
 		}
+	}
+	return 0, 0, false
+}
 
-		weightStringOffset := -1
-		if needsWeightString(tbl, col) {
-			weightStringOffset, err = wrapExprAndPush(weightStringFor(order.weightStrExpr), plan, semTable)
-			if err != nil {
-				return nil, err
-			}
-		}
+func createMemorySortPlan(plan logicalPlan, orderExprs []abstract.OrderBy, semTable *semantics.SemTable) (logicalPlan, bool, error) {
+	primitive := &engine.MemorySort{}
+	ms := &memorySort{
+		resultsBuilder: resultsBuilder{
+			logicalPlanCommon: newBuilderCommon(plan),
+			weightStrings:     make(map[*resultColumn]int),
+			truncater:         primitive,
+		},
+		eMemorySort: primitive,
+	}
 
-		ms.eMemorySort.OrderBy = append(ms.eMemorySort.OrderBy, engine.OrderbyParams{
+	var colAdded bool
+	for _, order := range orderExprs {
+		offset, weightStringOffset, added, err := wrapAndPushExpr(order.Inner.Expr, order.WeightStrExpr, plan, semTable)
+		if err != nil {
+			return nil, false, err
+		}
+		colAdded = colAdded || added
+		ms.eMemorySort.OrderBy = append(ms.eMemorySort.OrderBy, engine.OrderByParams{
 			Col:               offset,
 			WeightStringCol:   weightStringOffset,
-			Desc:              order.inner.Direction == sqlparser.DescOrder,
+			Desc:              order.Inner.Direction == sqlparser.DescOrder,
 			StarColFixedIndex: offset,
 		})
 	}
-
-	return ms, nil
-
+	return ms, colAdded, nil
 }
 
-func allLeft(orderExprs []orderBy, semTable *semantics.SemTable, lhsTables semantics.TableSet) bool {
+func allLeft(orderExprs []abstract.OrderBy, semTable *semantics.SemTable, lhsTables semantics.TableSet) bool {
 	for _, expr := range orderExprs {
-		exprDependencies := semTable.Dependencies(expr.inner.Expr)
+		exprDependencies := semTable.Dependencies(expr.Inner.Expr)
 		if !exprDependencies.IsSolvedBy(lhsTables) {
 			return false
 		}
diff --git a/go/vt/wrangler/vdiff.go b/go/vt/wrangler/vdiff.go
index 868a4104ea..9eea5470aa 100644
--- a/go/vt/wrangler/vdiff.go
+++ b/go/vt/wrangler/vdiff.go
@@ -529,28 +529,36 @@ func (df *vdiff) buildTablePlan(table *tabletmanagerdatapb.TableDefinition, quer
 	// the results, which engine.OrderedAggregate can do.
 	if len(aggregates) != 0 {
 		td.sourcePrimitive = &engine.OrderedAggregate{
-			Aggregates: aggregates,
-			Keys:       td.pkCols,
-			Input:      td.sourcePrimitive,
+			Aggregates:  aggregates,
+			GroupByKeys: pkColsToGroupByParams(td.pkCols),
+			Input:       td.sourcePrimitive,
 		}
 	}
 
 	return td, nil
 }
 
+func pkColsToGroupByParams(pkCols []int) []engine.GroupByParams {
+	var res []engine.GroupByParams
+	for _, col := range pkCols {
+		res = append(res, engine.GroupByParams{KeyCol: col, WeightStringCol: -1})
+	}
+	return res
+}
+
 // newMergeSorter creates an engine.MergeSort based on the shard streamers and pk columns.
 func newMergeSorter(participants map[string]*shardStreamer, comparePKs []compareColInfo) *engine.MergeSort {
 	prims := make([]engine.StreamExecutor, 0, len(participants))
 	for _, participant := range participants {
 		prims = append(prims, participant)
 	}
-	ob := make([]engine.OrderbyParams, 0, len(comparePKs))
+	ob := make([]engine.OrderByParams, 0, len(comparePKs))
 	for _, cpk := range comparePKs {
 		weightStringCol := -1
 		if cpk.weightStringIndex != cpk.colIndex {
 			weightStringCol = cpk.weightStringIndex
 		}
-		ob = append(ob, engine.OrderbyParams{Col: cpk.colIndex, WeightStringCol: weightStringCol})
+		ob = append(ob, engine.OrderByParams{Col: cpk.colIndex, WeightStringCol: weightStringCol})
 	}
 	return &engine.MergeSort{
 		Primitives: prims,


Test patch:
diff --git a/go/test/endtoend/vtgate/gen4/gen4_test.go b/go/test/endtoend/vtgate/gen4/gen4_test.go
index aa42b8aa3a..34b463fec1 100644
--- a/go/test/endtoend/vtgate/gen4/gen4_test.go
+++ b/go/test/endtoend/vtgate/gen4/gen4_test.go
@@ -34,6 +34,10 @@ func TestOrderBy(t *testing.T) {
 	require.NoError(t, err)
 	defer conn.Close()
 
+	defer func() {
+		_, _ = exec(t, conn, `delete from t1`)
+	}()
+
 	// insert some data.
 	checkedExec(t, conn, `insert into t1(id, col) values (100, 123),(10, 12),(1, 13),(1000, 1234)`)
 
@@ -48,6 +52,40 @@ func TestOrderBy(t *testing.T) {
 	require.Error(t, err)
 }
 
+func TestGroupBy(t *testing.T) {
+	ctx := context.Background()
+	conn, err := mysql.Connect(ctx, &vtParams)
+	require.NoError(t, err)
+	defer conn.Close()
+
+	defer func() {
+		_, _ = exec(t, conn, `delete from t1`)
+		_, _ = exec(t, conn, `delete from t2`)
+	}()
+
+	// insert some data.
+	checkedExec(t, conn, `insert into t1(id, col) values (1, 123),(2, 12),(3, 13),(4, 1234)`)
+	checkedExec(t, conn, `insert into t2(id, tcol1, tcol2) values (1, 'A', 'A'),(2, 'B', 'C'),(3, 'A', 'C'),(4, 'C', 'A'),(5, 'A', 'A'),(6, 'B', 'C'),(7, 'B', 'A'),(8, 'C', 'B')`)
+
+	// Gen4 only supported query.
+	assertMatches(t, conn, `select tcol2, tcol1, count(id) from t2 group by tcol2, tcol1`,
+		`[[VARCHAR("A") VARCHAR("A") INT64(2)] [VARCHAR("A") VARCHAR("B") INT64(1)] [VARCHAR("A") VARCHAR("C") INT64(1)] [VARCHAR("B") VARCHAR("C") INT64(1)] [VARCHAR("C") VARCHAR("A") INT64(1)] [VARCHAR("C") VARCHAR("B") INT64(2)]]`)
+
+	assertMatches(t, conn, `select tcol1, tcol1 from t2 order by tcol1`,
+		`[[VARCHAR("A") VARCHAR("A")] [VARCHAR("A") VARCHAR("A")] [VARCHAR("A") VARCHAR("A")] [VARCHAR("B") VARCHAR("B")] [VARCHAR("B") VARCHAR("B")] [VARCHAR("B") VARCHAR("B")] [VARCHAR("C") VARCHAR("C")] [VARCHAR("C") VARCHAR("C")]]`)
+
+	assertMatches(t, conn, `select tcol1, tcol1 from t1 join t2 on t1.id = t2.id order by tcol1`,
+		`[[VARCHAR("A") VARCHAR("A")] [VARCHAR("A") VARCHAR("A")] [VARCHAR("B") VARCHAR("B")] [VARCHAR("C") VARCHAR("C")]]`)
+
+	assertMatches(t, conn, `select count(*) k, tcol1, tcol2, "abc" b from t2 group by tcol1, tcol2, b order by k, tcol2, tcol1`,
+		`[[INT64(1) VARCHAR("B") VARCHAR("A") VARCHAR("abc")] `+
+			`[INT64(1) VARCHAR("C") VARCHAR("A") VARCHAR("abc")] `+
+			`[INT64(1) VARCHAR("C") VARCHAR("B") VARCHAR("abc")] `+
+			`[INT64(1) VARCHAR("A") VARCHAR("C") VARCHAR("abc")] `+
+			`[INT64(2) VARCHAR("A") VARCHAR("A") VARCHAR("abc")] `+
+			`[INT64(2) VARCHAR("B") VARCHAR("C") VARCHAR("abc")]]`)
+}
+
 func assertMatches(t *testing.T, conn *mysql.Conn, query, expected string) {
 	t.Helper()
 	qr := checkedExec(t, conn, query)
diff --git a/go/test/endtoend/vtgate/gen4/main_test.go b/go/test/endtoend/vtgate/gen4/main_test.go
index 03a9bd25a9..d892042ae1 100644
--- a/go/test/endtoend/vtgate/gen4/main_test.go
+++ b/go/test/endtoend/vtgate/gen4/main_test.go
@@ -35,6 +35,13 @@ var (
 	col bigint,
 	primary key(id)
 ) Engine=InnoDB;
+
+create table t2(
+	id bigint,
+	tcol1 varchar(50),
+	tcol2 varchar(50),
+	primary key(id)
+) Engine=InnoDB;
 `
 
 	VSchema = `
@@ -53,6 +60,20 @@ var (
           "name": "xxhash"
         }
       ]
+    },
+    "t2": {
+      "column_vindexes": [
+        {
+          "column": "id",
+          "name": "xxhash"
+        }
+      ],
+      "columns": [
+        {
+          "name": "tcol1",
+          "type": "VARCHAR"
+        }
+      ]
     }
   }
 }`
@@ -84,7 +105,7 @@ func TestMain(m *testing.M) {
 		}
 
 		// Start vtgate
-		clusterInstance.VtGateExtraArgs = []string{"-planner_version", "Gen4Fallback"} // enable Gen4 planner.
+		clusterInstance.VtGateExtraArgs = []string{"-planner_version", "Gen4"} // enable Gen4 planner.
 		err = clusterInstance.StartVtgate()
 		if err != nil {
 			return 1
diff --git a/go/vt/vtgate/engine/memory_sort_test.go b/go/vt/vtgate/engine/memory_sort_test.go
index 41bb465d94..c63ffedb04 100644
--- a/go/vt/vtgate/engine/memory_sort_test.go
+++ b/go/vt/vtgate/engine/memory_sort_test.go
@@ -45,7 +45,7 @@ func TestMemorySortExecute(t *testing.T) {
 	}
 
 	ms := &MemorySort{
-		OrderBy: []OrderbyParams{{
+		OrderBy: []OrderByParams{{
 			WeightStringCol: -1,
 			Col:             1,
 		}},
@@ -100,7 +100,7 @@ func TestMemorySortStreamExecuteWeightString(t *testing.T) {
 	}
 
 	ms := &MemorySort{
-		OrderBy: []OrderbyParams{{
+		OrderBy: []OrderByParams{{
 			WeightStringCol: 0,
 			Col:             1,
 		}},
@@ -168,7 +168,7 @@ func TestMemorySortExecuteWeightString(t *testing.T) {
 	}
 
 	ms := &MemorySort{
-		OrderBy: []OrderbyParams{{
+		OrderBy: []OrderByParams{{
 			WeightStringCol: 1,
 			Col:             0,
 		}},
@@ -223,7 +223,7 @@ func TestMemorySortStreamExecute(t *testing.T) {
 	}
 
 	ms := &MemorySort{
-		OrderBy: []OrderbyParams{{
+		OrderBy: []OrderByParams{{
 			WeightStringCol: -1,
 			Col:             1,
 		}},
@@ -302,7 +302,7 @@ func TestMemorySortExecuteTruncate(t *testing.T) {
 	}
 
 	ms := &MemorySort{
-		OrderBy: []OrderbyParams{{
+		OrderBy: []OrderByParams{{
 			WeightStringCol: -1,
 			Col:             1,
 		}},
@@ -341,7 +341,7 @@ func TestMemorySortStreamExecuteTruncate(t *testing.T) {
 	}
 
 	ms := &MemorySort{
-		OrderBy: []OrderbyParams{{
+		OrderBy: []OrderByParams{{
 			WeightStringCol: -1,
 			Col:             1,
 		}},
@@ -384,7 +384,7 @@ func TestMemorySortMultiColumn(t *testing.T) {
 	}
 
 	ms := &MemorySort{
-		OrderBy: []OrderbyParams{{
+		OrderBy: []OrderByParams{{
 			Col:             1,
 			WeightStringCol: -1,
 		}, {
@@ -459,7 +459,7 @@ func TestMemorySortMaxMemoryRows(t *testing.T) {
 		}
 
 		ms := &MemorySort{
-			OrderBy: []OrderbyParams{{
+			OrderBy: []OrderByParams{{
 				WeightStringCol: -1,
 				Col:             1,
 			}},
@@ -495,7 +495,7 @@ func TestMemorySortExecuteNoVarChar(t *testing.T) {
 	}
 
 	ms := &MemorySort{
-		OrderBy: []OrderbyParams{{
+		OrderBy: []OrderByParams{{
 			WeightStringCol: -1,
 			Col:             0,
 		}},
diff --git a/go/vt/vtgate/engine/merge_sort_test.go b/go/vt/vtgate/engine/merge_sort_test.go
index ede93d784b..5cda296000 100644
--- a/go/vt/vtgate/engine/merge_sort_test.go
+++ b/go/vt/vtgate/engine/merge_sort_test.go
@@ -56,7 +56,7 @@ func TestMergeSortNormal(t *testing.T) {
 			"8|h",
 		),
 	}}
-	orderBy := []OrderbyParams{{
+	orderBy := []OrderByParams{{
 		WeightStringCol: -1,
 		Col:             0,
 	}}
@@ -114,7 +114,7 @@ func TestMergeSortWeightString(t *testing.T) {
 			"8|h",
 		),
 	}}
-	orderBy := []OrderbyParams{{
+	orderBy := []OrderByParams{{
 		WeightStringCol: 0,
 		Col:             1,
 	}}
@@ -174,7 +174,7 @@ func TestMergeSortDescending(t *testing.T) {
 			"4|d",
 		),
 	}}
-	orderBy := []OrderbyParams{{
+	orderBy := []OrderByParams{{
 		WeightStringCol: -1,
 		Col:             0,
 		Desc:            true,
@@ -225,7 +225,7 @@ func TestMergeSortEmptyResults(t *testing.T) {
 	}, {
 		results: sqltypes.MakeTestStreamingResults(idColFields),
 	}}
-	orderBy := []OrderbyParams{{
+	orderBy := []OrderByParams{{
 		WeightStringCol: -1,
 		Col:             0,
 	}}
@@ -253,7 +253,7 @@ func TestMergeSortEmptyResults(t *testing.T) {
 // TestMergeSortResultFailures tests failures at various
 // stages of result return.
 func TestMergeSortResultFailures(t *testing.T) {
-	orderBy := []OrderbyParams{{
+	orderBy := []OrderByParams{{
 		WeightStringCol: -1,
 		Col:             0,
 	}}
@@ -299,7 +299,7 @@ func TestMergeSortDataFailures(t *testing.T) {
 			"2.1|b",
 		),
 	}}
-	orderBy := []OrderbyParams{{
+	orderBy := []OrderByParams{{
 		WeightStringCol: -1,
 		Col:             0,
 	}}
@@ -325,7 +325,7 @@ func TestMergeSortDataFailures(t *testing.T) {
 	require.EqualError(t, err, want)
 }
 
-func testMergeSort(shardResults []*shardResult, orderBy []OrderbyParams, callback func(qr *sqltypes.Result) error) error {
+func testMergeSort(shardResults []*shardResult, orderBy []OrderByParams, callback func(qr *sqltypes.Result) error) error {
 	prims := make([]StreamExecutor, 0, len(shardResults))
 	for _, sr := range shardResults {
 		prims = append(prims, sr)
diff --git a/go/vt/vtgate/engine/ordered_aggregate_test.go b/go/vt/vtgate/engine/ordered_aggregate_test.go
index 045291e0e4..b471b94a58 100644
--- a/go/vt/vtgate/engine/ordered_aggregate_test.go
+++ b/go/vt/vtgate/engine/ordered_aggregate_test.go
@@ -53,8 +53,8 @@ func TestOrderedAggregateExecute(t *testing.T) {
 			Opcode: AggregateCount,
 			Col:    1,
 		}},
-		Keys:  []int{0},
-		Input: fp,
+		GroupByKeys: []GroupByParams{{KeyCol: 0}},
+		Input:       fp,
 	}
 
 	result, err := oa.Execute(nil, nil, false)
@@ -90,7 +90,7 @@ func TestOrderedAggregateExecuteTruncate(t *testing.T) {
 			Opcode: AggregateCount,
 			Col:    1,
 		}},
-		Keys:                []int{2},
+		GroupByKeys:         []GroupByParams{{KeyCol: 2}},
 		TruncateColumnCount: 2,
 		Input:               fp,
 	}
@@ -132,8 +132,8 @@ func TestOrderedAggregateStreamExecute(t *testing.T) {
 			Opcode: AggregateCount,
 			Col:    1,
 		}},
-		Keys:  []int{0},
-		Input: fp,
+		GroupByKeys: []GroupByParams{{KeyCol: 0}},
+		Input:       fp,
 	}
 
 	var results []*sqltypes.Result
@@ -175,7 +175,7 @@ func TestOrderedAggregateStreamExecuteTruncate(t *testing.T) {
 			Opcode: AggregateCount,
 			Col:    1,
 		}},
-		Keys:                []int{2},
+		GroupByKeys:         []GroupByParams{{KeyCol: 2}},
 		TruncateColumnCount: 2,
 		Input:               fp,
 	}
@@ -316,8 +316,8 @@ func TestOrderedAggregateExecuteCountDistinct(t *testing.T) {
 			Opcode: AggregateCount,
 			Col:    2,
 		}},
-		Keys:  []int{0},
-		Input: fp,
+		GroupByKeys: []GroupByParams{{KeyCol: 0}},
+		Input:       fp,
 	}
 
 	result, err := oa.Execute(nil, nil, false)
@@ -392,8 +392,8 @@ func TestOrderedAggregateStreamCountDistinct(t *testing.T) {
 			Opcode: AggregateCount,
 			Col:    2,
 		}},
-		Keys:  []int{0},
-		Input: fp,
+		GroupByKeys: []GroupByParams{{KeyCol: 0}},
+		Input:       fp,
 	}
 
 	var results []*sqltypes.Result
@@ -480,8 +480,8 @@ func TestOrderedAggregateSumDistinctGood(t *testing.T) {
 			Opcode: AggregateSum,
 			Col:    2,
 		}},
-		Keys:  []int{0},
-		Input: fp,
+		GroupByKeys: []GroupByParams{{KeyCol: 0}},
+		Input:       fp,
 	}
 
 	result, err := oa.Execute(nil, nil, false)
@@ -525,8 +525,8 @@ func TestOrderedAggregateSumDistinctTolerateError(t *testing.T) {
 			Col:    1,
 			Alias:  "sum(distinct col2)",
 		}},
-		Keys:  []int{0},
-		Input: fp,
+		GroupByKeys: []GroupByParams{{KeyCol: 0}},
+		Input:       fp,
 	}
 
 	result, err := oa.Execute(nil, nil, false)
@@ -560,8 +560,8 @@ func TestOrderedAggregateKeysFail(t *testing.T) {
 			Opcode: AggregateCount,
 			Col:    1,
 		}},
-		Keys:  []int{0},
-		Input: fp,
+		GroupByKeys: []GroupByParams{{KeyCol: 0}},
+		Input:       fp,
 	}
 
 	want := "types are not comparable: VARCHAR vs VARCHAR"
@@ -593,8 +593,8 @@ func TestOrderedAggregateMergeFail(t *testing.T) {
 			Opcode: AggregateCount,
 			Col:    1,
 		}},
-		Keys:  []int{0},
-		Input: fp,
+		GroupByKeys: []GroupByParams{{KeyCol: 0}},
+		Input:       fp,
 	}
 
 	result := &sqltypes.Result{
@@ -721,8 +721,8 @@ func TestNoInputAndNoGroupingKeys(outer *testing.T) {
 					Col:    0,
 					Alias:  test.name,
 				}},
-				Keys:  []int{},
-				Input: fp,
+				GroupByKeys: []GroupByParams{},
+				Input:       fp,
 			}
 
 			result, err := oa.Execute(nil, nil, false)
diff --git a/go/vt/vtgate/engine/plan_description_test.go b/go/vt/vtgate/engine/plan_description_test.go
index c2fac8471c..c74b08ab50 100644
--- a/go/vt/vtgate/engine/plan_description_test.go
+++ b/go/vt/vtgate/engine/plan_description_test.go
@@ -60,7 +60,7 @@ func createRoute() *Route {
 		FieldQuery:        "more query",
 		Vindex:            hash.(*vindexes.Hash),
 		Values:            []sqltypes.PlanValue{},
-		OrderBy:           []OrderbyParams{},
+		OrderBy:           []OrderByParams{},
 	}
 }
 
diff --git a/go/vt/vtgate/engine/route_test.go b/go/vt/vtgate/engine/route_test.go
index 6671b4cb69..aee0abf936 100644
--- a/go/vt/vtgate/engine/route_test.go
+++ b/go/vt/vtgate/engine/route_test.go
@@ -805,7 +805,7 @@ func TestRouteSort(t *testing.T) {
 		"dummy_select",
 		"dummy_select_field",
 	)
-	sel.OrderBy = []OrderbyParams{{
+	sel.OrderBy = []OrderByParams{{
 		Col:             0,
 		WeightStringCol: -1,
 	}}
@@ -887,7 +887,7 @@ func TestRouteSortWeightStrings(t *testing.T) {
 		"dummy_select",
 		"dummy_select_field",
 	)
-	sel.OrderBy = []OrderbyParams{{
+	sel.OrderBy = []OrderByParams{{
 		Col:             1,
 		WeightStringCol: 0,
 	}}
@@ -953,7 +953,7 @@ func TestRouteSortWeightStrings(t *testing.T) {
 	})
 
 	t.Run("Error when no weight string set", func(t *testing.T) {
-		sel.OrderBy = []OrderbyParams{{
+		sel.OrderBy = []OrderByParams{{
 			Col:             1,
 			WeightStringCol: -1,
 		}}
@@ -989,7 +989,7 @@ func TestRouteSortTruncate(t *testing.T) {
 		"dummy_select",
 		"dummy_select_field",
 	)
-	sel.OrderBy = []OrderbyParams{{
+	sel.OrderBy = []OrderByParams{{
 		Col: 0,
 	}}
 	sel.TruncateColumnCount = 1
@@ -1080,7 +1080,7 @@ func TestRouteStreamSortTruncate(t *testing.T) {
 		"dummy_select",
 		"dummy_select_field",
 	)
-	sel.OrderBy = []OrderbyParams{{
+	sel.OrderBy = []OrderByParams{{
 		Col: 0,
 	}}
 	sel.TruncateColumnCount = 1
@@ -1223,7 +1223,7 @@ func TestExecFail(t *testing.T) {
 		vc.Rewind()
 		vc.resultErr = mysql.NewSQLError(mysql.ERQueryInterrupted, "", "query timeout -20")
 		// test when there is order by column
-		sel.OrderBy = []OrderbyParams{{
+		sel.OrderBy = []OrderByParams{{
 			WeightStringCol: -1,
 			Col:             0,
 		}}
diff --git a/go/vt/vtgate/planbuilder/queryprojection_test.go b/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
similarity index 50%
rename from go/vt/vtgate/planbuilder/queryprojection_test.go
rename to go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
index 5c2f98d715..e1d90c148a 100644
--- a/go/vt/vtgate/planbuilder/queryprojection_test.go
+++ b/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
@@ -14,12 +14,13 @@ See the License for the specific language governing permissions and
 limitations under the License.
 */
 
-package planbuilder
+package abstract
 
 import (
 	"testing"
 
 	"github.com/stretchr/testify/assert"
+
 	"github.com/stretchr/testify/require"
 
 	"vitess.io/vitess/go/vt/sqlparser"
@@ -30,7 +31,7 @@ func TestQP(t *testing.T) {
 		sql string
 
 		expErr   string
-		expOrder []orderBy
+		expOrder []OrderBy
 	}{
 		{
 			sql:    "select * from user",
@@ -45,7 +46,7 @@ func TestQP(t *testing.T) {
 		},
 		{
 			sql:    "select 1, count(1) from user",
-			expErr: "gen4 does not yet support: aggregation and non-aggregation expressions, together are not supported in cross-shard query",
+			expErr: "Mixing of aggregation and non-aggregation columns is not allowed if there is no GROUP BY clause",
 		},
 		{
 			sql: "select max(id) from user",
@@ -60,14 +61,14 @@ func TestQP(t *testing.T) {
 		},
 		{
 			sql:    "select 1, count(1) from user order by 1",
-			expErr: "gen4 does not yet support: aggregation and non-aggregation expressions, together are not supported in cross-shard query",
+			expErr: "Mixing of aggregation and non-aggregation columns is not allowed if there is no GROUP BY clause",
 		},
 		{
 			sql: "select id from user order by col, id, 1",
-			expOrder: []orderBy{
-				{inner: &sqlparser.Order{Expr: sqlparser.NewColName("col")}, weightStrExpr: sqlparser.NewColName("col")},
-				{inner: &sqlparser.Order{Expr: sqlparser.NewColName("id")}, weightStrExpr: sqlparser.NewColName("id")},
-				{inner: &sqlparser.Order{Expr: sqlparser.NewColName("id")}, weightStrExpr: sqlparser.NewColName("id")},
+			expOrder: []OrderBy{
+				{Inner: &sqlparser.Order{Expr: sqlparser.NewColName("col")}, WeightStrExpr: sqlparser.NewColName("col")},
+				{Inner: &sqlparser.Order{Expr: sqlparser.NewColName("id")}, WeightStrExpr: sqlparser.NewColName("id")},
+				{Inner: &sqlparser.Order{Expr: sqlparser.NewColName("id")}, WeightStrExpr: sqlparser.NewColName("id")},
 			},
 		},
 		{
@@ -76,10 +77,10 @@ func TestQP(t *testing.T) {
 		},
 		{
 			sql: "SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable, tbl2 ORDER BY full_name", // alias in order not supported
-			expOrder: []orderBy{
+			expOrder: []OrderBy{
 				{
-					inner: &sqlparser.Order{Expr: sqlparser.NewColName("full_name")},
-					weightStrExpr: &sqlparser.FuncExpr{
+					Inner: &sqlparser.Order{Expr: sqlparser.NewColName("full_name")},
+					WeightStrExpr: &sqlparser.FuncExpr{
 						Name: sqlparser.NewColIdent("CONCAT"),
 						Exprs: sqlparser.SelectExprs{
 							&sqlparser.AliasedExpr{Expr: sqlparser.NewColName("last_name")},
@@ -89,6 +90,9 @@ func TestQP(t *testing.T) {
 					},
 				},
 			},
+		}, {
+			sql:    "select count(*) b from user group by b",
+			expErr: "Can't group on 'b'",
 		},
 	}
 
@@ -98,17 +102,83 @@ func TestQP(t *testing.T) {
 			require.NoError(t, err)
 
 			sel := stmt.(*sqlparser.Select)
-			qp, err := createQPFromSelect(sel)
+			qp, err := CreateQPFromSelect(sel)
 			if tcase.expErr != "" {
 				require.EqualError(t, err, tcase.expErr)
 			} else {
 				require.NoError(t, err)
-				assert.Equal(t, len(sel.SelectExprs), len(qp.selectExprs)+len(qp.aggrExprs))
+				assert.Equal(t, len(sel.SelectExprs), len(qp.SelectExprs))
 				for index, expOrder := range tcase.expOrder {
-					assert.True(t, sqlparser.EqualsSQLNode(expOrder.inner, qp.orderExprs[index].inner), "want: %+v, got %+v", sqlparser.String(expOrder.inner), sqlparser.String(qp.orderExprs[index].inner))
-					assert.True(t, sqlparser.EqualsSQLNode(expOrder.weightStrExpr, qp.orderExprs[index].weightStrExpr), "want: %v, got %v", sqlparser.String(expOrder.weightStrExpr), sqlparser.String(qp.orderExprs[index].weightStrExpr))
+					assert.True(t, sqlparser.EqualsSQLNode(expOrder.Inner, qp.OrderExprs[index].Inner), "want: %+v, got %+v", sqlparser.String(expOrder.Inner), sqlparser.String(qp.OrderExprs[index].Inner))
+					assert.True(t, sqlparser.EqualsSQLNode(expOrder.WeightStrExpr, qp.OrderExprs[index].WeightStrExpr), "want: %v, got %v", sqlparser.String(expOrder.WeightStrExpr), sqlparser.String(qp.OrderExprs[index].WeightStrExpr))
 				}
 			}
 		})
 	}
 }
+
+func TestQPSimplifiedExpr(t *testing.T) {
+	testCases := []struct {
+		query, expected string
+	}{
+		{
+			query: "select intcol, count(*) from user group by 1",
+			expected: `
+{
+  "Select": [
+    "intcol",
+    "aggr: count(*)"
+  ],
+  "Grouping": [
+    "intcol"
+  ],
+  "OrderBy": []
+}`,
+		},
+		{
+			query: "select intcol, textcol from user order by 1, textcol",
+			expected: `
+{
+  "Select": [
+    "intcol",
+    "textcol"
+  ],
+  "Grouping": [],
+  "OrderBy": [
+    "intcol asc",
+    "textcol asc"
+  ]
+}`,
+		},
+		{
+			query: "select intcol, textcol, count(id) from user group by intcol, textcol, extracol order by 2 desc",
+			expected: `
+{
+  "Select": [
+    "intcol",
+    "textcol",
+    "aggr: count(id)"
+  ],
+  "Grouping": [
+    "intcol",
+    "textcol",
+    "extracol"
+  ],
+  "OrderBy": [
+    "textcol desc"
+  ]
+}`,
+		},
+	}
+
+	for _, tc := range testCases {
+		t.Run(tc.query, func(t *testing.T) {
+			ast, err := sqlparser.Parse(tc.query)
+			require.NoError(t, err)
+			sel := ast.(*sqlparser.Select)
+			qp, err := CreateQPFromSelect(sel)
+			require.NoError(t, err)
+			require.Equal(t, tc.expected[1:], qp.toString())
+		})
+	}
+}
diff --git a/go/vt/vtgate/planbuilder/plan_test.go b/go/vt/vtgate/planbuilder/plan_test.go
index fd600cb258..4778728ea9 100644
--- a/go/vt/vtgate/planbuilder/plan_test.go
+++ b/go/vt/vtgate/planbuilder/plan_test.go
@@ -499,6 +499,7 @@ func testFile(t *testing.T, filename, tempDir string, vschema *vschemaWrapper, c
 			vschema.version = Gen4
 			out, err := getPlanOutput(tcase, vschema)
 			if err != nil && tcase.output2ndPlanner == "" && strings.HasPrefix(err.Error(), "gen4 does not yet support") {
+				expected.WriteString("\n")
 				continue
 			}
 
diff --git a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
index 343ddb4d82..c8caa2a717 100644
--- a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
@@ -107,6 +107,7 @@ Gen4 plan same as above
     "Variant": "Ordered",
     "Aggregates": "count(0)",
     "GroupBy": "1, 4, 3",
+    "ResultColumns": 4,
     "Inputs": [
       {
         "OperatorType": "Route",
@@ -116,7 +117,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select count(*), a, textcol1, b, weight_string(textcol1), weight_string(a), weight_string(b) from `user` where 1 != 1 group by a, textcol1, b",
-        "OrderBy": "1 ASC, 2 ASC, 3 ASC",
+        "OrderBy": "(1|5) ASC, (2|4) ASC, (3|6) ASC",
         "Query": "select count(*), a, textcol1, b, weight_string(textcol1), weight_string(a), weight_string(b) from `user` group by a, textcol1, b order by a asc, textcol1 asc, b asc",
         "ResultColumns": 5,
         "Table": "`user`"
@@ -124,6 +125,31 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select count(*), a, textcol1, b from user group by a, textcol1, b",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(0)",
+    "GroupBy": "(1|4), (2|5), (3|6)",
+    "ResultColumns": 4,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select count(*), a, textcol1, b, weight_string(a), weight_string(textcol1), weight_string(b) from `user` where 1 != 1 group by a, textcol1, b",
+        "OrderBy": "(1|4) ASC, (2|5) ASC, (3|6) ASC",
+        "Query": "select count(*), a, textcol1, b, weight_string(a), weight_string(textcol1), weight_string(b) from `user` group by a, textcol1, b order by a asc, textcol1 asc, b asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter group by a integer column. Do not add weight strings for this.
 "select count(*), intcol from user group by intcol"
@@ -151,6 +177,7 @@ Gen4 plan same as above
     ]
   }
 }
+Gen4 plan same as above
 
 # scatter group by a text column, reuse existing weight_string
 "select count(*) k, a, textcol1, b from user group by a, textcol1, b order by k, textcol1"
@@ -160,13 +187,15 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "0 ASC, 2 ASC",
+    "OrderBy": "0 ASC, (2|4) ASC",
+    "ResultColumns": 4,
     "Inputs": [
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
         "Aggregates": "count(0)",
         "GroupBy": "1, 4, 3",
+        "ResultColumns": 5,
         "Inputs": [
           {
             "OperatorType": "Route",
@@ -176,7 +205,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select count(*) as k, a, textcol1, b, weight_string(textcol1), weight_string(a), weight_string(b) from `user` where 1 != 1 group by a, textcol1, b",
-            "OrderBy": "2 ASC, 1 ASC, 3 ASC",
+            "OrderBy": "(2|4) ASC, (1|5) ASC, (3|6) ASC",
             "Query": "select count(*) as k, a, textcol1, b, weight_string(textcol1), weight_string(a), weight_string(b) from `user` group by a, textcol1, b order by textcol1 asc, a asc, b asc",
             "ResultColumns": 5,
             "Table": "`user`"
@@ -186,6 +215,38 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select count(*) k, a, textcol1, b from user group by a, textcol1, b order by k, textcol1",
+  "Instructions": {
+    "OperatorType": "Sort",
+    "Variant": "Memory",
+    "OrderBy": "0 ASC, (2|5) ASC",
+    "ResultColumns": 4,
+    "Inputs": [
+      {
+        "OperatorType": "Aggregate",
+        "Variant": "Ordered",
+        "Aggregates": "count(0)",
+        "GroupBy": "(1|4), (2|5), (3|6)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select count(*) as k, a, textcol1, b, weight_string(a), weight_string(textcol1), weight_string(b) from `user` where 1 != 1 group by a, textcol1, b",
+            "OrderBy": "(1|4) ASC, (2|5) ASC, (3|6) ASC",
+            "Query": "select count(*) as k, a, textcol1, b, weight_string(a), weight_string(textcol1), weight_string(b) from `user` group by a, textcol1, b order by a asc, textcol1 asc, b asc",
+            "Table": "`user`"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # count aggregate
 "select count(*) from user"
@@ -309,7 +370,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col1, col2, weight_string(col1), weight_string(col2) from `user` where 1 != 1 group by col1",
-        "OrderBy": "0 ASC, 1 ASC, 0 ASC",
+        "OrderBy": "(0|2) ASC, (1|3) ASC, (0|2) ASC",
         "Query": "select distinct col1, col2, weight_string(col1), weight_string(col2) from `user` group by col1 order by col1 asc, col2 asc, col1 asc",
         "ResultColumns": 2,
         "Table": "`user`"
@@ -372,6 +433,7 @@ Gen4 plan same as above
     "Table": "`user`"
   }
 }
+Gen4 plan same as above
 
 # group by a unique vindex and other column should use a simple route
 "select id, col, count(*) from user group by id, col"
@@ -390,6 +452,7 @@ Gen4 plan same as above
     "Table": "`user`"
   }
 }
+Gen4 plan same as above
 
 # group by a non-vindex column should use an OrderdAggregate primitive
 "select col, count(*) from user group by col"
@@ -410,7 +473,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col, count(*), weight_string(col) from `user` where 1 != 1 group by col",
-        "OrderBy": "0 ASC",
+        "OrderBy": "(0|2) ASC",
         "Query": "select col, count(*), weight_string(col) from `user` group by col order by col asc",
         "ResultColumns": 2,
         "Table": "`user`"
@@ -418,12 +481,62 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select col, count(*) from user group by col",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(1)",
+    "GroupBy": "(0|2)",
+    "ResultColumns": 2,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select col, count(*), weight_string(col) from `user` where 1 != 1 group by col",
+        "OrderBy": "(0|2) ASC",
+        "Query": "select col, count(*), weight_string(col) from `user` group by col order by col asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # group by must only reference expressions in the select list
 "select col, count(*) from user group by col, baz"
 "unsupported: in scatter query: group by column must reference column in SELECT list"
+{
+  "QueryType": "SELECT",
+  "Original": "select col, count(*) from user group by col, baz",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(1)",
+    "GroupBy": "(0|2), (3|4)",
+    "ResultColumns": 2,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select col, count(*), weight_string(col), baz, weight_string(baz) from `user` where 1 != 1 group by col, baz",
+        "OrderBy": "(0|2) ASC, (3|4) ASC",
+        "Query": "select col, count(*), weight_string(col), baz, weight_string(baz) from `user` group by col, baz order by col asc, baz asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
-# group by a non-unique vindex column should use an OrderdAggregate primitive
+# group by a non-unique vindex column should use an OrderedAggregate primitive
 "select name, count(*) from user group by name"
 {
   "QueryType": "SELECT",
@@ -442,7 +555,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select `name`, count(*), weight_string(`name`) from `user` where 1 != 1 group by `name`",
-        "OrderBy": "0 ASC",
+        "OrderBy": "(0|2) ASC",
         "Query": "select `name`, count(*), weight_string(`name`) from `user` group by `name` order by `name` asc",
         "ResultColumns": 2,
         "Table": "`user`"
@@ -450,6 +563,31 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select name, count(*) from user group by name",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(1)",
+    "GroupBy": "(0|2)",
+    "ResultColumns": 2,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `name`, count(*), weight_string(`name`) from `user` where 1 != 1 group by `name`",
+        "OrderBy": "(0|2) ASC",
+        "Query": "select `name`, count(*), weight_string(`name`) from `user` group by `name` order by `name` asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # group by a unique vindex should use a simple route, even if aggr is complex
 "select id, 1+count(*) from user group by id"
@@ -602,7 +740,6 @@ Gen4 plan same as above
     ]
   }
 }
-"gen4 does not yet support: aggregation and non-aggregation expressions, together are not supported in cross-shard query"
 
 # scatter aggregate using distinct
 "select distinct col from user"
@@ -622,7 +759,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col, weight_string(col) from `user` where 1 != 1",
-        "OrderBy": "0 ASC",
+        "OrderBy": "(0|1) ASC",
         "Query": "select distinct col, weight_string(col) from `user` order by col asc",
         "ResultColumns": 1,
         "Table": "`user`"
@@ -649,7 +786,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col, weight_string(col) from `user` where 1 != 1 group by col",
-        "OrderBy": "0 ASC",
+        "OrderBy": "(0|1) ASC",
         "Query": "select col, weight_string(col) from `user` group by col order by col asc",
         "ResultColumns": 1,
         "Table": "`user`"
@@ -657,6 +794,30 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select col from user group by col",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "GroupBy": "(0|1)",
+    "ResultColumns": 1,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select col, weight_string(col) from `user` where 1 != 1 group by col",
+        "OrderBy": "(0|1) ASC",
+        "Query": "select col, weight_string(col) from `user` group by col order by col asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # count with distinct group by unique vindex
 "select id, count(distinct col) from user group by id"
@@ -695,7 +856,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col, count(distinct id), weight_string(col) from `user` where 1 != 1 group by col",
-        "OrderBy": "0 ASC",
+        "OrderBy": "(0|2) ASC",
         "Query": "select col, count(distinct id), weight_string(col) from `user` group by col order by col asc",
         "ResultColumns": 2,
         "Table": "`user`"
@@ -723,7 +884,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col1, col2, weight_string(col1), weight_string(col2) from `user` where 1 != 1 group by col1, col2",
-        "OrderBy": "0 ASC, 1 ASC",
+        "OrderBy": "(0|2) ASC, (1|3) ASC",
         "Query": "select col1, col2, weight_string(col1), weight_string(col2) from `user` group by col1, col2 order by col1 asc, col2 asc",
         "ResultColumns": 2,
         "Table": "`user`"
@@ -750,7 +911,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col2, weight_string(col2) from `user` where 1 != 1 group by col2",
-        "OrderBy": "0 ASC",
+        "OrderBy": "(0|1) ASC",
         "Query": "select col2, weight_string(col2) from `user` group by col2 order by col2 asc",
         "ResultColumns": 1,
         "Table": "`user`"
@@ -778,7 +939,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col1, col2, weight_string(col1), weight_string(col2) from `user` where 1 != 1 group by col1, col2",
-        "OrderBy": "0 ASC, 1 ASC",
+        "OrderBy": "(0|2) ASC, (1|3) ASC",
         "Query": "select col1, col2, weight_string(col1), weight_string(col2) from `user` group by col1, col2 order by col1 asc, col2 asc",
         "ResultColumns": 2,
         "Table": "`user`"
@@ -806,7 +967,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col1, col2, weight_string(col1), weight_string(col2) from `user` where 1 != 1 group by col1, col2",
-        "OrderBy": "0 ASC, 1 ASC",
+        "OrderBy": "(0|2) ASC, (1|3) ASC",
         "Query": "select col1, col2, weight_string(col1), weight_string(col2) from `user` group by col1, col2 order by col1 asc, col2 asc",
         "ResultColumns": 2,
         "Table": "`user`"
@@ -834,7 +995,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col1, min(distinct col2), weight_string(col1) from `user` where 1 != 1 group by col1",
-        "OrderBy": "0 ASC",
+        "OrderBy": "(0|2) ASC",
         "Query": "select col1, min(distinct col2), weight_string(col1) from `user` group by col1 order by col1 asc",
         "ResultColumns": 2,
         "Table": "`user`"
@@ -867,7 +1028,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select col1, col2, weight_string(col1), weight_string(col2) from `user` where 1 != 1 group by col1, col2",
-            "OrderBy": "0 ASC, 1 ASC",
+            "OrderBy": "(0|2) ASC, (1|3) ASC",
             "Query": "select col1, col2, weight_string(col1), weight_string(col2) from `user` group by col1, col2 order by col1 asc, col2 asc",
             "ResultColumns": 2,
             "Table": "`user`"
@@ -879,8 +1040,9 @@ Gen4 plan same as above
 }
 
 # scatter aggregate group by aggregate function
-" select count(*) b from user group by b"
+"select count(*) b from user group by b"
 "Can't group on 'b'"
+Gen4 plan same as above
 
 # scatter aggregate multiple group by (columns)
 "select a, b, count(*) from user group by b, a"
@@ -901,7 +1063,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select a, b, count(*), weight_string(b), weight_string(a) from `user` where 1 != 1 group by b, a",
-        "OrderBy": "1 ASC, 0 ASC",
+        "OrderBy": "(1|3) ASC, (0|4) ASC",
         "Query": "select a, b, count(*), weight_string(b), weight_string(a) from `user` group by b, a order by b asc, a asc",
         "ResultColumns": 3,
         "Table": "`user`"
@@ -909,6 +1071,31 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, count(*) from user group by b, a",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(2)",
+    "GroupBy": "(1|3), (0|4)",
+    "ResultColumns": 3,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select a, b, count(*), weight_string(b), weight_string(a) from `user` where 1 != 1 group by b, a",
+        "OrderBy": "(1|3) ASC, (0|4) ASC",
+        "Query": "select a, b, count(*), weight_string(b), weight_string(a) from `user` group by b, a order by b asc, a asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter aggregate multiple group by (numbers)
 "select a, b, count(*) from user group by 2, 1"
@@ -929,7 +1116,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select a, b, count(*), weight_string(b), weight_string(a) from `user` where 1 != 1 group by 2, 1",
-        "OrderBy": "1 ASC, 0 ASC",
+        "OrderBy": "(1|3) ASC, (0|4) ASC",
         "Query": "select a, b, count(*), weight_string(b), weight_string(a) from `user` group by 2, 1 order by b asc, a asc",
         "ResultColumns": 3,
         "Table": "`user`"
@@ -937,6 +1124,31 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, count(*) from user group by 2, 1",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(2)",
+    "GroupBy": "(1|3), (0|4)",
+    "ResultColumns": 3,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select a, b, count(*), weight_string(b), weight_string(a) from `user` where 1 != 1 group by b, a",
+        "OrderBy": "(1|3) ASC, (0|4) ASC",
+        "Query": "select a, b, count(*), weight_string(b), weight_string(a) from `user` group by b, a order by b asc, a asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter aggregate multiple group by columns inverse order
 "select a, b, count(*) from user group by b, a"
@@ -957,7 +1169,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select a, b, count(*), weight_string(b), weight_string(a) from `user` where 1 != 1 group by b, a",
-        "OrderBy": "1 ASC, 0 ASC",
+        "OrderBy": "(1|3) ASC, (0|4) ASC",
         "Query": "select a, b, count(*), weight_string(b), weight_string(a) from `user` group by b, a order by b asc, a asc",
         "ResultColumns": 3,
         "Table": "`user`"
@@ -984,7 +1196,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col, weight_string(col) from `user` where 1 != 1 group by 1",
-        "OrderBy": "0 ASC",
+        "OrderBy": "(0|1) ASC",
         "Query": "select col, weight_string(col) from `user` group by 1 order by col asc",
         "ResultColumns": 1,
         "Table": "`user`"
@@ -996,6 +1208,7 @@ Gen4 plan same as above
 # scatter aggregate group by invalid column number
 "select col from user group by 2"
 "Unknown column '2' in 'group statement'"
+Gen4 plan same as above
 
 # scatter aggregate order by null
 "select count(*) from user order by null"
@@ -1045,7 +1258,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select a, b, c, d, count(*), weight_string(a), weight_string(b), weight_string(c) from `user` where 1 != 1 group by 1, 2, 3",
-        "OrderBy": "0 ASC, 1 ASC, 2 ASC",
+        "OrderBy": "(0|5) ASC, (1|6) ASC, (2|7) ASC",
         "Query": "select a, b, c, d, count(*), weight_string(a), weight_string(b), weight_string(c) from `user` group by 1, 2, 3 order by 1 asc, 2 asc, 3 asc",
         "ResultColumns": 5,
         "Table": "`user`"
@@ -1053,6 +1266,31 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, c, d, count(*) from user group by 1, 2, 3 order by 1, 2, 3",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(4)",
+    "GroupBy": "(0|5), (1|6), (2|7)",
+    "ResultColumns": 5,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select a, b, c, d, count(*), weight_string(a), weight_string(b), weight_string(c) from `user` where 1 != 1 group by a, b, c",
+        "OrderBy": "(0|5) ASC, (1|6) ASC, (2|7) ASC",
+        "Query": "select a, b, c, d, count(*), weight_string(a), weight_string(b), weight_string(c) from `user` group by a, b, c order by a asc, b asc, c asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter aggregate with named order by columns
 "select a, b, c, d, count(*) from user group by 1, 2, 3 order by a, b, c"
@@ -1073,7 +1311,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select a, b, c, d, count(*), weight_string(a), weight_string(b), weight_string(c) from `user` where 1 != 1 group by 1, 2, 3",
-        "OrderBy": "0 ASC, 1 ASC, 2 ASC",
+        "OrderBy": "(0|5) ASC, (1|6) ASC, (2|7) ASC",
         "Query": "select a, b, c, d, count(*), weight_string(a), weight_string(b), weight_string(c) from `user` group by 1, 2, 3 order by a asc, b asc, c asc",
         "ResultColumns": 5,
         "Table": "`user`"
@@ -1081,6 +1319,31 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, c, d, count(*) from user group by 1, 2, 3 order by a, b, c",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(4)",
+    "GroupBy": "(0|5), (1|6), (2|7)",
+    "ResultColumns": 5,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select a, b, c, d, count(*), weight_string(a), weight_string(b), weight_string(c) from `user` where 1 != 1 group by a, b, c",
+        "OrderBy": "(0|5) ASC, (1|6) ASC, (2|7) ASC",
+        "Query": "select a, b, c, d, count(*), weight_string(a), weight_string(b), weight_string(c) from `user` group by a, b, c order by a asc, b asc, c asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter aggregate with jumbled order by columns
 "select a, b, c, d, count(*) from user group by 1, 2, 3, 4 order by d, b, a, c"
@@ -1101,7 +1364,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select a, b, c, d, count(*), weight_string(d), weight_string(b), weight_string(a), weight_string(c) from `user` where 1 != 1 group by 1, 2, 3, 4",
-        "OrderBy": "3 ASC, 1 ASC, 0 ASC, 2 ASC",
+        "OrderBy": "(3|5) ASC, (1|6) ASC, (0|7) ASC, (2|8) ASC",
         "Query": "select a, b, c, d, count(*), weight_string(d), weight_string(b), weight_string(a), weight_string(c) from `user` group by 1, 2, 3, 4 order by d asc, b asc, a asc, c asc",
         "ResultColumns": 5,
         "Table": "`user`"
@@ -1109,6 +1372,31 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, c, d, count(*) from user group by 1, 2, 3, 4 order by d, b, a, c",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(4)",
+    "GroupBy": "(0|5), (1|6), (2|7), (3|8)",
+    "ResultColumns": 5,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select a, b, c, d, count(*), weight_string(a), weight_string(b), weight_string(c), weight_string(d) from `user` where 1 != 1 group by a, b, c, d",
+        "OrderBy": "(3|8) ASC, (1|6) ASC, (0|5) ASC, (2|7) ASC",
+        "Query": "select a, b, c, d, count(*), weight_string(a), weight_string(b), weight_string(c), weight_string(d) from `user` group by a, b, c, d order by d asc, b asc, a asc, c asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter aggregate with jumbled group by and order by columns
 "select a, b, c, d, count(*) from user group by 3, 2, 1, 4 order by d, b, a, c"
@@ -1129,7 +1417,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select a, b, c, d, count(*), weight_string(d), weight_string(b), weight_string(a), weight_string(c) from `user` where 1 != 1 group by 3, 2, 1, 4",
-        "OrderBy": "3 ASC, 1 ASC, 0 ASC, 2 ASC",
+        "OrderBy": "(3|5) ASC, (1|6) ASC, (0|7) ASC, (2|8) ASC",
         "Query": "select a, b, c, d, count(*), weight_string(d), weight_string(b), weight_string(a), weight_string(c) from `user` group by 3, 2, 1, 4 order by d asc, b asc, a asc, c asc",
         "ResultColumns": 5,
         "Table": "`user`"
@@ -1137,6 +1425,31 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, c, d, count(*) from user group by 3, 2, 1, 4 order by d, b, a, c",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(4)",
+    "GroupBy": "(2|5), (1|6), (0|7), (3|8)",
+    "ResultColumns": 5,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select a, b, c, d, count(*), weight_string(c), weight_string(b), weight_string(a), weight_string(d) from `user` where 1 != 1 group by c, b, a, d",
+        "OrderBy": "(3|8) ASC, (1|6) ASC, (0|7) ASC, (2|5) ASC",
+        "Query": "select a, b, c, d, count(*), weight_string(c), weight_string(b), weight_string(a), weight_string(d) from `user` group by c, b, a, d order by d asc, b asc, a asc, c asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter aggregate with some descending order by cols
 "select a, b, c, count(*) from user group by 3, 2, 1 order by 1 desc, 3 desc, b"
@@ -1157,7 +1470,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select a, b, c, count(*), weight_string(a), weight_string(c), weight_string(b) from `user` where 1 != 1 group by 3, 2, 1",
-        "OrderBy": "0 DESC, 2 DESC, 1 ASC",
+        "OrderBy": "(0|4) DESC, (2|5) DESC, (1|6) ASC",
         "Query": "select a, b, c, count(*), weight_string(a), weight_string(c), weight_string(b) from `user` group by 3, 2, 1 order by 1 desc, 3 desc, b asc",
         "ResultColumns": 4,
         "Table": "`user`"
@@ -1165,6 +1478,31 @@ Gen4 plan same as above
     ]
   }
 }
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, c, count(*) from user group by 3, 2, 1 order by 1 desc, 3 desc, b",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(3)",
+    "GroupBy": "(2|4), (1|5), (0|6)",
+    "ResultColumns": 4,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select a, b, c, count(*), weight_string(c), weight_string(b), weight_string(a) from `user` where 1 != 1 group by c, b, a",
+        "OrderBy": "(0|6) DESC, (2|4) DESC, (1|5) ASC",
+        "Query": "select a, b, c, count(*), weight_string(c), weight_string(b), weight_string(a) from `user` group by c, b, a order by a desc, c desc, b asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # invalid order by column numner for scatter
 "select col, count(*) from user group by col order by 5 limit 10"
@@ -1194,7 +1532,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select col, count(*), weight_string(col) from `user` where 1 != 1 group by col",
-            "OrderBy": "0 ASC",
+            "OrderBy": "(0|2) ASC",
             "Query": "select col, count(*), weight_string(col) from `user` group by col order by col asc limit :__upper_limit",
             "ResultColumns": 2,
             "Table": "`user`"
@@ -1288,7 +1626,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select a, count(*), weight_string(a) from `user` where 1 != 1",
-            "OrderBy": "0 ASC",
+            "OrderBy": "(0|2) ASC",
             "Query": "select a, count(*), weight_string(a) from `user` order by a asc",
             "ResultColumns": 2,
             "Table": "`user`"
@@ -1321,7 +1659,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select a, count(*), weight_string(a) from `user` where 1 != 1 group by a",
-            "OrderBy": "0 ASC, 0 ASC",
+            "OrderBy": "(0|2) ASC, (0|2) ASC",
             "Query": "select a, count(*), weight_string(a) from `user` group by a order by a asc, a asc",
             "ResultColumns": 2,
             "Table": "`user`"
@@ -1339,6 +1677,7 @@ Gen4 plan same as above
 # Group by out of range column number (code is duplicated from symab).
 "select id from user group by 2"
 "Unknown column '2' in 'group statement'"
+Gen4 plan same as above
 
 # syntax error detected by planbuilder
 "select count(distinct *) from user"
diff --git a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
index dd16a534f3..4131b6930a 100644
--- a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
@@ -7,13 +7,15 @@
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "1 ASC",
+    "OrderBy": "(1|3) ASC",
+    "ResultColumns": 3,
     "Inputs": [
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
         "Aggregates": "count(2)",
         "GroupBy": "0",
+        "ResultColumns": 4,
         "Inputs": [
           {
             "OperatorType": "Route",
@@ -23,7 +25,7 @@
               "Sharded": true
             },
             "FieldQuery": "select a, b, count(*), weight_string(b), weight_string(a) from `user` where 1 != 1 group by a",
-            "OrderBy": "0 ASC",
+            "OrderBy": "(0|4) ASC",
             "Query": "select a, b, count(*), weight_string(b), weight_string(a) from `user` group by a order by a asc",
             "ResultColumns": 4,
             "Table": "`user`"
@@ -58,7 +60,7 @@
               "Sharded": true
             },
             "FieldQuery": "select a, b, count(*) as k, weight_string(a) from `user` where 1 != 1 group by a",
-            "OrderBy": "0 ASC",
+            "OrderBy": "(0|3) ASC",
             "Query": "select a, b, count(*) as k, weight_string(a) from `user` group by a order by a asc",
             "ResultColumns": 3,
             "Table": "`user`"
@@ -77,13 +79,15 @@
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "1 ASC, 0 ASC, 2 ASC",
+    "OrderBy": "(1|3) ASC, (0|4) ASC, 2 ASC",
+    "ResultColumns": 3,
     "Inputs": [
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
         "Aggregates": "count(2)",
         "GroupBy": "0",
+        "ResultColumns": 5,
         "Inputs": [
           {
             "OperatorType": "Route",
@@ -93,7 +97,7 @@
               "Sharded": true
             },
             "FieldQuery": "select a, b, count(*) as k, weight_string(b), weight_string(a) from `user` where 1 != 1 group by a",
-            "OrderBy": "0 ASC",
+            "OrderBy": "(0|4) ASC",
             "Query": "select a, b, count(*) as k, weight_string(b), weight_string(a) from `user` group by a order by a asc",
             "ResultColumns": 5,
             "Table": "`user`"
@@ -132,7 +136,7 @@
                   "Sharded": true
                 },
                 "FieldQuery": "select a, b, count(*) as k, weight_string(a) from `user` where 1 != 1 group by a",
-                "OrderBy": "0 ASC",
+                "OrderBy": "(0|3) ASC",
                 "Query": "select a, b, count(*) as k, weight_string(a) from `user` group by a order by a asc",
                 "ResultColumns": 3,
                 "Table": "`user`"
@@ -153,13 +157,15 @@
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "0 ASC, 2 ASC",
+    "OrderBy": "(0|3) ASC, 2 ASC",
+    "ResultColumns": 3,
     "Inputs": [
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
         "Aggregates": "count(2)",
         "GroupBy": "0",
+        "ResultColumns": 4,
         "Inputs": [
           {
             "OperatorType": "Route",
@@ -169,7 +175,7 @@
               "Sharded": true
             },
             "FieldQuery": "select a, b, count(*) as k, weight_string(a) from `user` where 1 != 1 group by a",
-            "OrderBy": "0 ASC",
+            "OrderBy": "(0|3) ASC",
             "Query": "select a, b, count(*) as k, weight_string(a) from `user` group by a order by 1 asc",
             "ResultColumns": 4,
             "Table": "`user`"
@@ -189,13 +195,15 @@
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "0 ASC, 1 ASC, 0 ASC",
+    "OrderBy": "(0|2) ASC, 1 ASC, (0|2) ASC",
+    "ResultColumns": 2,
     "Inputs": [
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
         "Aggregates": "count(1)",
         "GroupBy": "2",
+        "ResultColumns": 3,
         "Inputs": [
           {
             "OperatorType": "Route",
@@ -205,7 +213,7 @@
               "Sharded": true
             },
             "FieldQuery": "select textcol1 as t, count(*) as k, weight_string(textcol1) from `user` where 1 != 1 group by textcol1",
-            "OrderBy": "0 ASC, 0 ASC",
+            "OrderBy": "(0|2) ASC, (0|2) ASC",
             "Query": "select textcol1 as t, count(*) as k, weight_string(textcol1) from `user` group by textcol1 order by textcol1 asc, textcol1 asc",
             "ResultColumns": 3,
             "Table": "`user`"
@@ -224,7 +232,8 @@
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "0 ASC",
+    "OrderBy": "(0|2) ASC",
+    "ResultColumns": 1,
     "Inputs": [
       {
         "OperatorType": "Subquery",
@@ -276,7 +285,8 @@
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "2 ASC",
+    "OrderBy": "(2|3) ASC",
+    "ResultColumns": 3,
     "Inputs": [
       {
         "OperatorType": "Join",
@@ -325,7 +335,8 @@
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "2 ASC",
+    "OrderBy": "(2|3) ASC",
+    "ResultColumns": 3,
     "Inputs": [
       {
         "OperatorType": "Join",
@@ -377,7 +388,8 @@
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "0 ASC, 2 DESC, 1 ASC",
+    "OrderBy": "(0|3) ASC, (2|4) DESC, (1|5) ASC",
+    "ResultColumns": 3,
     "Inputs": [
       {
         "OperatorType": "Join",
@@ -426,7 +438,8 @@
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "0 ASC, 2 DESC, 1 ASC",
+    "OrderBy": "(0|3) ASC, (2|4) DESC, (1|5) ASC",
+    "ResultColumns": 3,
     "Inputs": [
       {
         "OperatorType": "Join",
@@ -478,7 +491,8 @@
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "1 ASC, 2 ASC",
+    "OrderBy": "(1|3) ASC, (2|4) ASC",
+    "ResultColumns": 3,
     "Inputs": [
       {
         "OperatorType": "Join",
@@ -523,7 +537,8 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "1 ASC, 2 ASC",
+    "OrderBy": "(1|3) ASC, (2|4) ASC",
+    "ResultColumns": 3,
     "Inputs": [
       {
         "OperatorType": "Join",
@@ -605,7 +620,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select a, weight_string(a) from `user` where 1 != 1",
-    "OrderBy": "0 DESC",
+    "OrderBy": "(0|1) DESC",
     "Query": "select a, weight_string(a) from `user` order by binary a desc",
     "ResultColumns": 1,
     "Table": "`user`"
@@ -631,7 +646,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select u.a, weight_string(u.a) from `user` as u where 1 != 1",
-        "OrderBy": "0 DESC",
+        "OrderBy": "(0|1) DESC",
         "Query": "select u.a, weight_string(u.a) from `user` as u order by binary a desc",
         "ResultColumns": 1,
         "Table": "`user`"
@@ -685,7 +700,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select col, id, weight_string(id) from `user` where 1 != 1",
-    "OrderBy": "1 ASC",
+    "OrderBy": "(1|2) ASC",
     "Query": "select col, id, weight_string(id) from `user` order by id asc",
     "ResultColumns": 1,
     "Table": "`user`"
diff --git a/go/vt/vtgate/planbuilder/testdata/onecase.txt b/go/vt/vtgate/planbuilder/testdata/onecase.txt
index e819513f35..16a368ddaf 100644
--- a/go/vt/vtgate/planbuilder/testdata/onecase.txt
+++ b/go/vt/vtgate/planbuilder/testdata/onecase.txt
@@ -1,1 +1,1 @@
-# Add your test case here for debugging and run go test -run=One.
+# Add your test case here for debugging and run go test -run=One.
\ No newline at end of file
diff --git a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
index 5e6418c4f0..a06bec71dc 100644
--- a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
@@ -192,7 +192,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select col, weight_string(col) from `user` where 1 != 1",
-    "OrderBy": "0 ASC",
+    "OrderBy": "(0|1) ASC",
     "Query": "select col, weight_string(col) from `user` order by col asc",
     "ResultColumns": 1,
     "Table": "`user`"
@@ -213,7 +213,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select t.*, t.col, weight_string(t.col) from `user` as t where 1 != 1",
-    "OrderBy": "9 ASC",
+    "OrderBy": "(9|2) ASC",
     "Query": "select t.*, t.col, weight_string(t.col) from `user` as t order by t.col asc",
     "ResultColumns": 2,
     "Table": "`user`"
@@ -233,7 +233,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select *, col, weight_string(col) from `user` where 1 != 1",
-    "OrderBy": "9 ASC",
+    "OrderBy": "(9|2) ASC",
     "Query": "select *, col, weight_string(col) from `user` order by col asc",
     "ResultColumns": 2,
     "Table": "`user`"
@@ -253,7 +253,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select t.*, t.`name`, t.*, t.col, weight_string(t.col) from `user` as t where 1 != 1",
-    "OrderBy": "19 ASC",
+    "OrderBy": "(19|4) ASC",
     "Query": "select t.*, t.`name`, t.*, t.col, weight_string(t.col) from `user` as t order by t.col asc",
     "ResultColumns": 4,
     "Table": "`user`"
@@ -273,7 +273,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select *, `name`, *, col, weight_string(col) from `user` where 1 != 1",
-    "OrderBy": "19 ASC",
+    "OrderBy": "(19|4) ASC",
     "Query": "select *, `name`, *, col, weight_string(col) from `user` order by col asc",
     "ResultColumns": 4,
     "Table": "`user`"
@@ -293,7 +293,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select user_id, col1, col2, weight_string(user_id) from authoritative where 1 != 1",
-    "OrderBy": "0 ASC",
+    "OrderBy": "(0|3) ASC",
     "Query": "select user_id, col1, col2, weight_string(user_id) from authoritative order by user_id asc",
     "ResultColumns": 3,
     "Table": "authoritative"
@@ -310,7 +310,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select authoritative.user_id as user_id, authoritative.col1 as col1, authoritative.col2 as col2, weight_string(authoritative.user_id) from authoritative where 1 != 1",
-    "OrderBy": "0 ASC",
+    "OrderBy": "(0|3) ASC",
     "Query": "select authoritative.user_id as user_id, authoritative.col1 as col1, authoritative.col2 as col2, weight_string(authoritative.user_id) from authoritative order by user_id asc",
     "ResultColumns": 3,
     "Table": "authoritative"
@@ -330,7 +330,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select user_id, col1, col2, weight_string(col1) from authoritative where 1 != 1",
-    "OrderBy": "1 ASC",
+    "OrderBy": "(1|3) ASC",
     "Query": "select user_id, col1, col2, weight_string(col1) from authoritative order by col1 asc",
     "ResultColumns": 3,
     "Table": "authoritative"
@@ -347,7 +347,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select authoritative.user_id as user_id, authoritative.col1 as col1, authoritative.col2 as col2, weight_string(authoritative.col1) from authoritative where 1 != 1",
-    "OrderBy": "1 ASC",
+    "OrderBy": "(1|3) ASC",
     "Query": "select authoritative.user_id as user_id, authoritative.col1 as col1, authoritative.col2 as col2, weight_string(authoritative.col1) from authoritative order by col1 asc",
     "ResultColumns": 3,
     "Table": "authoritative"
@@ -367,7 +367,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select a, textcol1, b, weight_string(a), weight_string(textcol1), weight_string(b) from `user` where 1 != 1",
-    "OrderBy": "0 ASC, 1 ASC, 2 ASC",
+    "OrderBy": "(0|3) ASC, (1|4) ASC, (2|5) ASC",
     "Query": "select a, textcol1, b, weight_string(a), weight_string(textcol1), weight_string(b) from `user` order by a asc, textcol1 asc, b asc",
     "ResultColumns": 3,
     "Table": "`user`"
@@ -388,7 +388,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select a, `user`.textcol1, b, weight_string(a), weight_string(`user`.textcol1), weight_string(b) from `user` where 1 != 1",
-    "OrderBy": "0 ASC, 1 ASC, 2 ASC",
+    "OrderBy": "(0|3) ASC, (1|4) ASC, (2|5) ASC",
     "Query": "select a, `user`.textcol1, b, weight_string(a), weight_string(`user`.textcol1), weight_string(b) from `user` order by a asc, textcol1 asc, b asc",
     "ResultColumns": 3,
     "Table": "`user`"
@@ -405,7 +405,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select a, `user`.textcol1, b, weight_string(a), textcol1, weight_string(textcol1), weight_string(b) from `user` where 1 != 1",
-    "OrderBy": "0 ASC, 4 ASC, 2 ASC",
+    "OrderBy": "(0|3) ASC, (4|5) ASC, (2|6) ASC",
     "Query": "select a, `user`.textcol1, b, weight_string(a), textcol1, weight_string(textcol1), weight_string(b) from `user` order by a asc, textcol1 asc, b asc",
     "ResultColumns": 3,
     "Table": "`user`"
@@ -425,7 +425,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select a, textcol1, b, textcol2, weight_string(a), weight_string(textcol1), weight_string(b), weight_string(textcol2) from `user` where 1 != 1",
-    "OrderBy": "0 ASC, 1 ASC, 2 ASC, 3 ASC",
+    "OrderBy": "(0|4) ASC, (1|5) ASC, (2|6) ASC, (3|7) ASC",
     "Query": "select a, textcol1, b, textcol2, weight_string(a), weight_string(textcol1), weight_string(b), weight_string(textcol2) from `user` order by a asc, textcol1 asc, b asc, textcol2 asc",
     "ResultColumns": 4,
     "Table": "`user`"
@@ -451,7 +451,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select id as foo, weight_string(id) from music where 1 != 1",
-    "OrderBy": "0 ASC",
+    "OrderBy": "(0|1) ASC",
     "Query": "select id as foo, weight_string(id) from music order by 1 asc",
     "ResultColumns": 1,
     "Table": "music"
@@ -468,7 +468,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select id as foo, weight_string(id) from music where 1 != 1",
-    "OrderBy": "0 ASC",
+    "OrderBy": "(0|1) ASC",
     "Query": "select id as foo, weight_string(id) from music order by foo asc",
     "ResultColumns": 1,
     "Table": "music"
@@ -521,7 +521,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select col, weight_string(col) from `user` where 1 != 1",
-        "OrderBy": "0 ASC",
+        "OrderBy": "(0|1) ASC",
         "Query": "select col, weight_string(col) from `user` where :__sq_has_values1 = 1 and col in ::__sq1 order by col asc",
         "ResultColumns": 1,
         "Table": "`user`"
@@ -681,9 +681,8 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select `user`.id, `user`.col1 as a, `user`.col2, weight_string(`user`.col1) from `user` where 1 != 1",
-        "OrderBy": "1 ASC",
+        "OrderBy": "(1|3) ASC",
         "Query": "select `user`.id, `user`.col1 as a, `user`.col2, weight_string(`user`.col1) from `user` where `user`.id = 1 order by a asc",
-        "ResultColumns": 3,
         "Table": "`user`",
         "Values": [
           1
@@ -1323,7 +1322,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select id as foo, weight_string(id) from music where 1 != 1",
-    "OrderBy": "0 ASC",
+    "OrderBy": "(0|1) ASC",
     "Query": "select id as foo, weight_string(id) from music order by foo asc",
     "ResultColumns": 1,
     "Table": "music"
@@ -1344,7 +1343,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select id as foo, id2 as id, weight_string(id2) from music where 1 != 1",
-    "OrderBy": "1 ASC",
+    "OrderBy": "(1|2) ASC",
     "Query": "select id as foo, id2 as id, weight_string(id2) from music order by id asc",
     "ResultColumns": 2,
     "Table": "music"
@@ -1371,7 +1370,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select `name`, weight_string(`name`) from `user` where 1 != 1",
-        "OrderBy": "0 ASC",
+        "OrderBy": "(0|1) ASC",
         "Query": "select `name`, weight_string(`name`) from `user` order by `name` asc",
         "ResultColumns": 1,
         "Table": "`user`"
@@ -1390,7 +1389,41 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select name from user, music order by name",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-1",
+    "TableName": "`user`_music",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `name`, weight_string(`name`) from `user` where 1 != 1",
+        "OrderBy": "(0|1) ASC",
+        "Query": "select `name`, weight_string(`name`) from `user` order by `name` asc",
+        "Table": "`user`"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select 1 from music where 1 != 1",
+        "Query": "select 1 from music",
+        "Table": "music"
+      }
+    ]
+  }
+}
 
 # aggregation and non-aggregations column without group by
 "select count(id), num from user"
@@ -1425,12 +1458,14 @@ Gen4 plan same as above
   "Instructions": {
     "OperatorType": "Sort",
     "Variant": "Memory",
-    "OrderBy": "1 ASC",
+    "OrderBy": "(1|2) ASC",
+    "ResultColumns": 2,
     "Inputs": [
       {
         "OperatorType": "Aggregate",
         "Variant": "Ordered",
         "Aggregates": "count(0)",
+        "ResultColumns": 3,
         "Inputs": [
           {
             "OperatorType": "Route",
@@ -1468,7 +1503,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select count(id), num, weight_string(num) from `user` where 1 != 1 group by 2",
-        "OrderBy": "1 ASC",
+        "OrderBy": "(1|2) ASC",
         "Query": "select count(id), num, weight_string(num) from `user` group by 2 order by num asc",
         "ResultColumns": 2,
         "Table": "`user`"
@@ -1501,7 +1536,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select count(id), num, weight_string(num) from `user` where 1 != 1 group by 2",
-            "OrderBy": "1 ASC",
+            "OrderBy": "(1|2) ASC",
             "Query": "select count(id), num, weight_string(num) from `user` group by 2 order by num asc",
             "ResultColumns": 2,
             "Table": "`user`"
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases.txt b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
index 68e697dbc2..9da1cad578 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
@@ -931,7 +931,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select user_id, weight_string(user_id) from music where 1 != 1",
-        "OrderBy": "0 ASC",
+        "OrderBy": "(0|1) ASC",
         "Query": "select user_id, weight_string(user_id) from music order by user_id asc limit :__upper_limit",
         "ResultColumns": 1,
         "Table": "music"
@@ -1631,7 +1631,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select user_id, count(id), weight_string(user_id) from music where 1 != 1 group by user_id",
-            "OrderBy": "0 ASC",
+            "OrderBy": "(0|2) ASC",
             "Query": "select user_id, count(id), weight_string(user_id) from music group by user_id having count(user_id) = 1 order by user_id asc limit :__upper_limit",
             "ResultColumns": 2,
             "Table": "music"
@@ -1687,10 +1687,10 @@ Gen4 plan same as above
 }
 
 # select from unsharded keyspace into outfile
-"select * from main.unsharded into outfile 'x.txt' character set binary fields terminated by 'term' optionally enclosed by 'c' escaped by 'e' lines starting by 'a' terminated by '\\n'"
+"select * from main.unsharded into outfile 'x.txt' character set binary fields terminated by 'term' optionally enclosed by 'c' escaped by 'e' lines starting by 'a' terminated by '\n'"
 {
   "QueryType": "SELECT",
-  "Original": "select * from main.unsharded into outfile 'x.txt' character set binary fields terminated by 'term' optionally enclosed by 'c' escaped by 'e' lines starting by 'a' terminated by '\\n'",
+  "Original": "select * from main.unsharded into outfile 'x.txt' character set binary fields terminated by 'term' optionally enclosed by 'c' escaped by 'e' lines starting by 'a' terminated by '\n'",
   "Instructions": {
     "OperatorType": "Route",
     "Variant": "SelectUnsharded",
@@ -1705,10 +1705,10 @@ Gen4 plan same as above
 }
 
 # select from unsharded keyspace into outfile s3
-"select * from main.unsharded into outfile s3 'out_file_name' character set binary format csv header fields terminated by 'term' optionally enclosed by 'c' escaped by 'e' lines starting by 'a' terminated by '\\n' manifest on overwrite off"
+"select * from main.unsharded into outfile s3 'out_file_name' character set binary format csv header fields terminated by 'term' optionally enclosed by 'c' escaped by 'e' lines starting by 'a' terminated by '\n' manifest on overwrite off"
 {
   "QueryType": "SELECT",
-  "Original": "select * from main.unsharded into outfile s3 'out_file_name' character set binary format csv header fields terminated by 'term' optionally enclosed by 'c' escaped by 'e' lines starting by 'a' terminated by '\\n' manifest on overwrite off",
+  "Original": "select * from main.unsharded into outfile s3 'out_file_name' character set binary format csv header fields terminated by 'term' optionally enclosed by 'c' escaped by 'e' lines starting by 'a' terminated by '\n' manifest on overwrite off",
   "Instructions": {
     "OperatorType": "Route",
     "Variant": "SelectUnsharded",
diff --git a/go/vt/vtgate/planbuilder/testdata/show_cases.txt b/go/vt/vtgate/planbuilder/testdata/show_cases.txt
index 4b79891df1..df4a2a8679 100644
--- a/go/vt/vtgate/planbuilder/testdata/show_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/show_cases.txt
@@ -553,6 +553,7 @@ Gen4 plan same as above
     "OperatorType": "Aggregate",
     "Variant": "Ordered",
     "Aggregates": "vgtid(1) AS global vgtid_executed",
+    "ResultColumns": 2,
     "Inputs": [
       {
         "OperatorType": "Send",
diff --git a/go/vt/vtgate/planbuilder/testdata/union_cases.txt b/go/vt/vtgate/planbuilder/testdata/union_cases.txt
index 82e018ceeb..a746ecba78 100644
--- a/go/vt/vtgate/planbuilder/testdata/union_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/union_cases.txt
@@ -120,7 +120,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select id, weight_string(id) from `user` where 1 != 1",
-            "OrderBy": "0 DESC",
+            "OrderBy": "(0|1) DESC",
             "Query": "select id, weight_string(id) from `user` order by id desc limit :__upper_limit",
             "ResultColumns": 1,
             "Table": "`user`"
@@ -139,7 +139,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select id, weight_string(id) from music where 1 != 1",
-            "OrderBy": "0 DESC",
+            "OrderBy": "(0|1) DESC",
             "Query": "select id, weight_string(id) from music order by id desc limit :__upper_limit",
             "ResultColumns": 1,
             "Table": "music"
@@ -208,7 +208,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select id, weight_string(id) from `user` where 1 != 1",
-            "OrderBy": "0 ASC",
+            "OrderBy": "(0|1) ASC",
             "Query": "select id, weight_string(id) from `user` order by id asc limit :__upper_limit",
             "ResultColumns": 1,
             "Table": "`user`"
@@ -227,7 +227,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select id, weight_string(id) from music where 1 != 1",
-            "OrderBy": "0 DESC",
+            "OrderBy": "(0|1) DESC",
             "Query": "select id, weight_string(id) from music order by id desc limit :__upper_limit",
             "ResultColumns": 1,
             "Table": "music"
@@ -651,7 +651,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select 1, weight_string(1) from `user` where 1 != 1",
-            "OrderBy": "0 DESC",
+            "OrderBy": "(0|1) DESC",
             "Query": "select 1, weight_string(1) from `user` order by 1 desc",
             "ResultColumns": 1,
             "Table": "`user`"
@@ -664,7 +664,7 @@ Gen4 plan same as above
               "Sharded": true
             },
             "FieldQuery": "select 1, weight_string(1) from `user` where 1 != 1",
-            "OrderBy": "0 ASC",
+            "OrderBy": "(0|1) ASC",
             "Query": "select 1, weight_string(1) from `user` order by 1 asc",
             "ResultColumns": 1,
             "Table": "`user`"
diff --git a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
index c2355ad7ea..f0b32471c7 100644
--- a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
@@ -131,14 +131,53 @@ Gen4 error: aggregate functions take a single argument 'count(a, b)'
       "Name": "user",
       "Sharded": true
     },
-    "FieldQuery": "select id, weight_string(id) from `user` where 1 != 1",
-    "OrderBy": "0 ASC",
-    "Query": "select id, weight_string(id) from `user` order by id asc",
-    "ResultColumns": 1,
+    "FieldQuery": "select id, id, weight_string(id) from `user` where 1 != 1",
+    "OrderBy": "(0|2) ASC",
+    "Query": "select id, id, weight_string(id) from `user` order by id asc",
+    "ResultColumns": 2,
     "Table": "`user`"
   }
 }
 
+# join order by with ambiguous column reference ; valid in MySQL
+"select name, name from user, music order by name"
+"ambiguous symbol reference: `name`"
+{
+  "QueryType": "SELECT",
+  "Original": "select name, name from user, music order by name",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-1,-1",
+    "TableName": "`user`_music",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `name`, weight_string(`name`) from `user` where 1 != 1",
+        "OrderBy": "(0|1) ASC",
+        "Query": "select `name`, weight_string(`name`) from `user` order by `name` asc",
+        "Table": "`user`"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select 1 from music where 1 != 1",
+        "Query": "select 1 from music",
+        "Table": "music"
+      }
+    ]
+  }
+}
+
 # scatter aggregate with ambiguous aliases
 "select distinct a, b as a from user"
 "generating order by clause: ambiguous symbol reference: a"
@@ -512,3 +551,29 @@ Gen4 plan same as above
   }
 }
 Gen4 plan same as above
+
+# aggr and non-aggr without group by (with query does not give useful result out)
+"select id, count(*) from user"
+{
+  "QueryType": "SELECT",
+  "Original": "select id, count(*) from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(1)",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select id, count(*) from `user` where 1 != 1",
+        "Query": "select id, count(*) from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
+"Mixing of aggregation and non-aggregation columns is not allowed if there is no GROUP BY clause"
diff --git a/go/vt/wrangler/vdiff_test.go b/go/vt/wrangler/vdiff_test.go
index 5d6e2b3ef4..a0ed626a66 100644
--- a/go/vt/wrangler/vdiff_test.go
+++ b/go/vt/wrangler/vdiff_test.go
@@ -394,8 +394,8 @@ func TestVDiffPlanSuccess(t *testing.T) {
 					Opcode: engine.AggregateSum,
 					Col:    3,
 				}},
-				Keys:  []int{0},
-				Input: newMergeSorter(nil, []compareColInfo{{0, 0, true}}),
+				GroupByKeys: []engine.GroupByParams{{KeyCol: 0, WeightStringCol: -1}},
+				Input:       newMergeSorter(nil, []compareColInfo{{0, 0, true}}),
 			},
 			targetPrimitive: newMergeSorter(nil, []compareColInfo{{0, 0, true}}),
 		},