Repository: vitessio/vitess
Pull request #10262 (2022-05-11 09:19:30)

evalengine: CASE expressions
## Description
This PR adds support in the evalengine for `CASE` expressions. 


github-actions[bot]: ### Review Checklist

Hello reviewers! :wave: Please follow this checklist when reviewing this Pull Request.

#### General
- [ ] Ensure that the Pull Request has the correct `release notes` label. `release notes none` should only be used for PRs that are so trivial that they need not be included.
- [ ] If a new flag is being introduced, review whether it is really needed. The flag names should be clear and intuitive (as far as possible), and the flag's help should be descriptive.

#### Bug fixes
- [ ] There should be at least one unit or end-to-end test.
- [ ] The Pull Request description should either include a link to an issue that describes the bug OR an actual description of the bug and how to reproduce, along with a description of the fix.

#### Non-trivial changes
- [ ] There should be some code comments as to why things are implemented the way they are.

#### New/Existing features
- [ ] Should be documented, either by modifying the existing documentation or creating new documentation.
- [ ] New features should have a link to a feature request issue or an RFC that documents the use cases, corner cases and test cases.

#### Backward compatibility            
- [ ] Protobuf changes should be wire-compatible.
- [ ] Changes to `_vt` tables and RPCs need to be backward compatible.
- [ ] `vtctl` command output order should be stable and `awk`-able.
vmg: Added support for the alternate form of `CASE`. Our version does not match MySQL's behavior fully because I suspect MySQL has a bug here. I've reported upstream: https://bugs.mysql.com/bug.php?id=107287

Patch:
diff --git a/go/mysql/collations/coercion.go b/go/mysql/collations/coercion.go
index 17fab8ac43..d0408f3c7b 100644
--- a/go/mysql/collations/coercion.go
+++ b/go/mysql/collations/coercion.go
@@ -257,13 +257,13 @@ func (env *Environment) MergeCollations(left, right TypedCollation, opt Coercion
 		if left.Coercibility <= right.Coercibility {
 			return left, nil, nil, nil
 		}
-		return right, nil, nil, nil
+		goto coerceToRight
 	}
 	if _, rightIsBinary := rightColl.(*Collation_binary); rightIsBinary {
 		if left.Coercibility >= right.Coercibility {
 			return right, nil, nil, nil
 		}
-		return left, nil, nil, nil
+		goto coerceToLeft
 	}
 
 	if opt.ConvertToSuperset {
diff --git a/go/mysql/collations/collation.go b/go/mysql/collations/collation.go
index e0a1808652..651821381e 100644
--- a/go/mysql/collations/collation.go
+++ b/go/mysql/collations/collation.go
@@ -186,10 +186,5 @@ func Validate(collation Collation, input []byte) bool {
 // into a byte slice encoded in `dstCollation`'s charset. The resulting byte slice is
 // appended to `dst` and returned.
 func Convert(dst []byte, dstCollation Collation, src []byte, srcCollation Collation) ([]byte, error) {
-	switch srcCollation.(type) {
-	case *Collation_binary:
-		return charset.ConvertFromBinary(dst, dstCollation.Charset(), src)
-	default:
-		return charset.Convert(dst, dstCollation.Charset(), src, srcCollation.Charset())
-	}
+	return charset.Convert(dst, dstCollation.Charset(), src, srcCollation.Charset())
 }
diff --git a/go/mysql/collations/internal/charset/convert.go b/go/mysql/collations/internal/charset/convert.go
index 43189ade91..8518964529 100644
--- a/go/mysql/collations/internal/charset/convert.go
+++ b/go/mysql/collations/internal/charset/convert.go
@@ -21,10 +21,8 @@ import (
 	"unicode/utf8"
 )
 
-type ErrFailedConversion int
-
-func (e ErrFailedConversion) Error() string {
-	return fmt.Sprintf("failed to convert %d codepoints", e)
+func failedConversionError(from, to Charset, input []byte) error {
+	return fmt.Errorf("Cannot convert string %q from %s to %s", input, from.Name(), to.Name())
 }
 
 func convertFastFromUTF8(dst []byte, dstCharset Charset, src []byte) ([]byte, error) {
@@ -53,13 +51,14 @@ func convertFastFromUTF8(dst []byte, dstCharset Charset, src []byte) ([]byte, er
 	}
 
 	if failed > 0 {
-		return dst[:nDst], ErrFailedConversion(failed)
+		return dst[:nDst], failedConversionError(&Charset_utf8mb4{}, dstCharset, src)
 	}
 	return dst[:nDst], nil
 }
 
 func convertSlow(dst []byte, dstCharset Charset, src []byte, srcCharset Charset) ([]byte, error) {
 	var failed, nDst int
+	var original = src
 
 	if dst == nil {
 		dst = make([]byte, len(src)*3)
@@ -93,7 +92,7 @@ func convertSlow(dst []byte, dstCharset Charset, src []byte, srcCharset Charset)
 	}
 
 	if failed > 0 {
-		return dst[:nDst], ErrFailedConversion(failed)
+		return dst[:nDst], failedConversionError(srcCharset, dstCharset, original)
 	}
 	return dst[:nDst], nil
 }
@@ -115,6 +114,8 @@ func Convert(dst []byte, dstCharset Charset, src []byte, srcCharset Charset) ([]
 		return trans.Convert(dst, src, srcCharset)
 	}
 	switch srcCharset.(type) {
+	case Charset_binary:
+		return ConvertFromBinary(dst, dstCharset, src)
 	case Charset_utf8mb3, Charset_utf8mb4:
 		return convertFastFromUTF8(dst, dstCharset, src)
 	default:
@@ -141,7 +142,7 @@ func ConvertFromBinary(dst []byte, dstCharset Charset, src []byte) ([]byte, erro
 		dst = append(dst, src...)
 	}
 	if !Validate(dstCharset, dst) {
-		return nil, ErrFailedConversion(1)
+		return nil, failedConversionError(&Charset_binary{}, dstCharset, src)
 	}
 	return dst, nil
 }
diff --git a/go/vt/vtgate/engine/ordered_aggregate.go b/go/vt/vtgate/engine/ordered_aggregate.go
index d532dc9fb5..dc159a4970 100644
--- a/go/vt/vtgate/engine/ordered_aggregate.go
+++ b/go/vt/vtgate/engine/ordered_aggregate.go
@@ -102,8 +102,9 @@ type AggregateParams struct {
 	WAssigned   bool
 	CollationID collations.ID
 
-	Alias string `json:",omitempty"`
-	Expr  sqlparser.Expr
+	Alias    string `json:",omitempty"`
+	Expr     sqlparser.Expr
+	Original *sqlparser.AliasedExpr
 }
 
 func (ap *AggregateParams) isDistinct() bool {
@@ -141,6 +142,7 @@ const (
 	AggregateCountDistinct
 	AggregateSumDistinct
 	AggregateGtid
+	AggregateRandom
 )
 
 var (
@@ -178,7 +180,7 @@ func (code AggregateOpcode) String() string {
 			return k
 		}
 	}
-	panic("unreachable")
+	return "random"
 }
 
 // MarshalJSON serializes the AggregateOpcode as a JSON string.
@@ -476,6 +478,8 @@ func merge(
 			data, _ := proto.Marshal(vgtid)
 			val, _ := sqltypes.NewValue(sqltypes.VarBinary, data)
 			result[aggr.Col] = val
+		case AggregateRandom:
+			// we just grab the first value per grouping. no need to do anything more complicated here
 		default:
 			return nil, nil, fmt.Errorf("BUG: Unexpected opcode: %v", aggr.Opcode)
 		}
diff --git a/go/vt/vtgate/evalengine/collate.go b/go/vt/vtgate/evalengine/collate.go
index 15b8c0c953..61a53dfe8a 100644
--- a/go/vt/vtgate/evalengine/collate.go
+++ b/go/vt/vtgate/evalengine/collate.go
@@ -38,7 +38,7 @@ var collationNumeric = collations.TypedCollation{
 
 var collationBinary = collations.TypedCollation{
 	Collation:    collations.CollationBinaryID,
-	Coercibility: collations.CoerceExplicit,
+	Coercibility: collations.CoerceCoercible,
 	Repertoire:   collations.RepertoireASCII,
 }
 
@@ -51,7 +51,8 @@ func (c *CollateExpr) eval(env *ExpressionEnv, out *EvalResult) {
 }
 
 func (c *CollateExpr) typeof(env *ExpressionEnv) (sqltypes.Type, flag) {
-	return c.Inner.typeof(env)
+	t, f := c.Inner.typeof(env)
+	return t, f | flagExplicitCollation
 }
 
 type LookupDefaultCollation collations.ID
@@ -97,10 +98,16 @@ func mergeCollations(left, right *EvalResult) (collations.ID, error) {
 	}
 
 	if coerceLeft != nil {
-		left.bytes_, _ = coerceLeft(nil, left.bytes())
+		left.bytes_, err = coerceLeft(nil, left.bytes())
+		if err != nil {
+			throwEvalError(err)
+		}
 	}
 	if coerceRight != nil {
-		right.bytes_, _ = coerceRight(nil, right.bytes())
+		right.bytes_, err = coerceRight(nil, right.bytes())
+		if err != nil {
+			throwEvalError(err)
+		}
 	}
 
 	left.replaceCollation(mc)
diff --git a/go/vt/vtgate/evalengine/eval_result.go b/go/vt/vtgate/evalengine/eval_result.go
index 0472dccbab..3821d4bb7a 100644
--- a/go/vt/vtgate/evalengine/eval_result.go
+++ b/go/vt/vtgate/evalengine/eval_result.go
@@ -52,6 +52,8 @@ const (
 	flagHex flag = 1 << 8
 	// flagBit marks that this value originated from a bit literal
 	flagBit flag = 1 << 9
+	// flagExplicitCollation marks that this value has an explicit collation
+	flagExplicitCollation flag = 1 << 10
 
 	// flagIntegerRange are the flags that mark overflow/underflow in integers
 	flagIntegerRange = flagIntegerOvf | flagIntegerCap | flagIntegerUdf
@@ -329,9 +331,7 @@ func (er *EvalResult) isTruthy() boolean {
 
 func (er *EvalResult) makeBinary() {
 	er.resolve()
-	if er.bytes_ == nil {
-		er.bytes_ = er.toRawBytes()
-	}
+	er.bytes_ = er.toRawBytes()
 	er.type_ = int16(sqltypes.VarBinary)
 	er.collation_ = collationBinary
 	er.clearFlags(flagBit | flagHex)
@@ -339,32 +339,28 @@ func (er *EvalResult) makeBinary() {
 
 func (er *EvalResult) makeTextual(collation collations.ID) {
 	er.resolve()
-	if er.bytes_ == nil {
-		er.bytes_ = er.toRawBytes()
-	}
+	er.bytes_ = er.toRawBytes()
 	er.collation_.Collation = collation
 	er.type_ = int16(sqltypes.VarChar)
 }
 
 func (er *EvalResult) makeTextualAndConvert(collation collations.ID) bool {
 	er.resolve()
-	if er.bytes_ == nil {
-		er.bytes_ = er.toRawBytes()
-	}
+	er.bytes_ = er.toRawBytes()
 	if er.collation_.Collation == collations.Unknown {
 		er.collation_.Collation = collations.CollationBinaryID
 	}
-
-	var err error
-	environment := collations.Local()
-	fromCollation := environment.LookupByID(er.collation_.Collation)
-	toCollation := environment.LookupByID(collation)
-	er.bytes_, err = collations.Convert(nil, toCollation, er.bytes_, fromCollation)
-	if err != nil {
-		er.setNull()
-		return false
+	if collation != collations.CollationBinaryID && collation != er.collation_.Collation {
+		var err error
+		environment := collations.Local()
+		fromCollation := environment.LookupByID(er.collation_.Collation)
+		toCollation := environment.LookupByID(collation)
+		er.bytes_, err = collations.Convert(nil, toCollation, er.bytes_, fromCollation)
+		if err != nil {
+			er.setNull()
+			return false
+		}
 	}
-
 	er.collation_.Collation = collation
 	er.type_ = int16(sqltypes.VarChar)
 	return true
@@ -719,6 +715,9 @@ func (er *EvalResult) makeDecimal(m, d int32) {
 	var dec decimal.Decimal
 	switch tt := er.typeof(); {
 	case tt == sqltypes.Decimal:
+		if m == 0 && d == 0 {
+			return
+		}
 		dec = er.decimal()
 	case sqltypes.IsFloat(tt):
 		dec = decimal.NewFromFloatMySQL(er.float64())
@@ -727,7 +726,12 @@ func (er *EvalResult) makeDecimal(m, d int32) {
 	case sqltypes.IsUnsigned(tt):
 		dec = decimal.NewFromUint(er.uint64())
 	}
-	er.setDecimal(dec.Clamp(m-d, d), d)
+
+	if m == 0 && d == 0 {
+		er.setDecimal(dec, -dec.Exponent())
+	} else {
+		er.setDecimal(dec.Clamp(m-d, d), d)
+	}
 }
 
 func (er *EvalResult) makeNumeric() {
@@ -896,6 +900,42 @@ func (er *EvalResult) coerceToDecimal() decimal.Decimal {
 	}
 }
 
+func (er *EvalResult) coerce(typ sqltypes.Type, coll collations.ID) {
+	if coll == collations.Unknown {
+		panic("EvalResult.coerce with no collation")
+	}
+	if typ == sqltypes.VarChar || typ == sqltypes.Char {
+		// if we have an explicit VARCHAR coercion, always force it so the collation is replaced in the target
+		er.makeTextual(coll)
+		return
+	}
+	if er.typeof() == typ || er.isNull() {
+		// nothing to be done here
+		return
+	}
+	er.resolve()
+	switch typ {
+	case sqltypes.Null:
+		er.setNull()
+	case sqltypes.Binary, sqltypes.VarBinary:
+		er.makeBinary()
+	case sqltypes.Char, sqltypes.VarChar:
+		panic("unreacheable")
+	case sqltypes.Decimal:
+		er.makeDecimal(0, 0)
+	case sqltypes.Float32, sqltypes.Float64:
+		er.makeFloat()
+	case sqltypes.Int8, sqltypes.Int16, sqltypes.Int32, sqltypes.Int64:
+		er.makeSignedIntegral()
+	case sqltypes.Uint8, sqltypes.Uint16, sqltypes.Uint32, sqltypes.Uint64:
+		er.makeUnsignedIntegral()
+	case sqltypes.Date, sqltypes.Datetime, sqltypes.Year, sqltypes.TypeJSON, sqltypes.Time, sqltypes.Bit:
+		throwEvalError(vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "Unsupported type conversion: %s", typ.String()))
+	default:
+		panic(fmt.Sprintf("BUG: emitted unknown type: %s", typ))
+	}
+}
+
 func (er *EvalResult) String() string {
 	return er.value().String()
 }
@@ -949,6 +989,16 @@ func (er *EvalResult) Value() sqltypes.Value {
 	return er.value()
 }
 
+func (er *EvalResult) Collation() collations.ID {
+	if er.expr != nil {
+		panic("did not resolve EvalResult after evaluation")
+	}
+	if er.collation_.Collation == collations.Unknown {
+		return collations.CollationBinaryID
+	}
+	return er.collation_.Collation
+}
+
 // TupleValues allows for retrieval of the value we expose for public consumption
 func (er *EvalResult) TupleValues() []sqltypes.Value {
 	if er.expr != nil {
diff --git a/go/vt/vtgate/evalengine/expressions.go b/go/vt/vtgate/evalengine/expressions.go
index 6cf856bd69..07feb3e16d 100644
--- a/go/vt/vtgate/evalengine/expressions.go
+++ b/go/vt/vtgate/evalengine/expressions.go
@@ -234,7 +234,7 @@ func (env *ExpressionEnv) typecheck(expr Expr) {
 		}
 	case *CallExpr:
 		env.typecheck(expr.Arguments)
-	case *Literal, *Column, *BindVariable: // noop
+	case *Literal, *Column, *BindVariable, *CaseExpr: // noop
 	default:
 		panic(fmt.Sprintf("unhandled cardinality: %T", expr))
 	}
diff --git a/go/vt/vtgate/evalengine/func.go b/go/vt/vtgate/evalengine/func.go
index 6183379615..3803c88b51 100644
--- a/go/vt/vtgate/evalengine/func.go
+++ b/go/vt/vtgate/evalengine/func.go
@@ -82,7 +82,12 @@ func (builtinCoalesce) call(_ *ExpressionEnv, args []EvalResult, result *EvalRes
 }
 
 func (builtinCoalesce) typeof(env *ExpressionEnv, args []Expr) (sqltypes.Type, flag) {
-	return aggregatedType(env, args), flagNullable
+	var ta typeAggregation
+	for _, arg := range args {
+		tt, f := arg.typeof(env)
+		ta.add(tt, f)
+	}
+	return ta.result(), flagNullable
 }
 
 type multiComparisonFunc func(args []EvalResult, result *EvalResult, cmp int)
@@ -205,9 +210,15 @@ func compareAllText(args []EvalResult, result *EvalResult, cmp int) {
 	candidateB := args[0].toRawBytes()
 	collationB := args[0].collation()
 
+	var ca collationAggregation
+	ca.add(env, collationB)
+
 	for _, arg := range args[1:] {
 		thisB := arg.toRawBytes()
-		thisTC, coerceLeft, coerceRight, err := env.MergeCollations(arg.collation(), collationB, collations.CoercionOptions{ConvertToSuperset: true, ConvertWithCoercion: true})
+		thisColl := arg.collation()
+		ca.add(env, thisColl)
+
+		thisTC, coerceLeft, coerceRight, err := env.MergeCollations(thisColl, collationB, collations.CoercionOptions{ConvertToSuperset: true, ConvertWithCoercion: true})
 		if err != nil {
 			throwEvalError(err)
 		}
@@ -227,7 +238,29 @@ func compareAllText(args []EvalResult, result *EvalResult, cmp int) {
 		}
 	}
 
-	result.setRaw(sqltypes.VarChar, candidateB, collationB)
+	result.setRaw(sqltypes.VarChar, candidateB, ca.result())
+}
+
+type collationAggregation struct {
+	cur  collations.TypedCollation
+	init bool
+}
+
+func (ca *collationAggregation) add(env *collations.Environment, tc collations.TypedCollation) {
+	if !ca.init {
+		ca.cur = tc
+		ca.init = true
+	} else {
+		var err error
+		ca.cur, _, _, err = env.MergeCollations(ca.cur, tc, collations.CoercionOptions{ConvertToSuperset: true, ConvertWithCoercion: true})
+		if err != nil {
+			throwEvalError(err)
+		}
+	}
+}
+
+func (ca *collationAggregation) result() collations.TypedCollation {
+	return ca.cur
 }
 
 func compareAllBinary(args []EvalResult, result *EvalResult, cmp int) {
@@ -328,8 +361,11 @@ type builtinCollation struct{}
 
 func (builtinCollation) call(env *ExpressionEnv, args []EvalResult, result *EvalResult) {
 	coll := collations.Local().LookupByID(args[0].collation().Collation)
+
+	// the collation of a `COLLATION` expr is hardcoded to `utf8_general_ci`,
+	// not to the default collation of our connection. this is probably a bug in MySQL, but we match it
 	result.setString(coll.Name(), collations.TypedCollation{
-		Collation:    env.DefaultCollation,
+		Collation:    collations.CollationUtf8ID,
 		Coercibility: collations.CoerceImplicit,
 		Repertoire:   collations.RepertoireASCII,
 	})
@@ -435,77 +471,83 @@ func (c *WeightStringCallExpr) eval(env *ExpressionEnv, result *EvalResult) {
 	result.setRaw(sqltypes.VarBinary, weights, collationBinary)
 }
 
-func aggregatedType(env *ExpressionEnv, expr []Expr) sqltypes.Type {
-	var (
-		double   int
-		decimal  int
-		signed   int
-		unsigned int
-
-		signedMax   sqltypes.Type
-		unsignedMax sqltypes.Type
-
-		bit    int
-		year   int
-		char   int
-		binary int
-		json   int
-
-		date      int
-		time      int
-		timestamp int
-		datetime  int
-
-		geometry int
-		blob     int
-		total    int
-	)
+type typeAggregation struct {
+	double   uint16
+	decimal  uint16
+	signed   uint16
+	unsigned uint16
+
+	signedMax   sqltypes.Type
+	unsignedMax sqltypes.Type
+
+	bit       uint16
+	year      uint16
+	char      uint16
+	binary    uint16
+	charother uint16
+	json      uint16
+
+	date      uint16
+	time      uint16
+	timestamp uint16
+	datetime  uint16
+
+	geometry uint16
+	blob     uint16
+	total    uint16
+}
 
-	for _, e := range expr {
-		tt, _ := e.typeof(env)
-		switch tt {
-		case sqltypes.Float32, sqltypes.Float64:
-			double++
-		case sqltypes.Decimal:
-			decimal++
-		case sqltypes.Int8, sqltypes.Int16, sqltypes.Int24, sqltypes.Int32, sqltypes.Int64:
-			signed++
-			if tt > signedMax {
-				signedMax = tt
-			}
-		case sqltypes.Uint8, sqltypes.Uint16, sqltypes.Uint24, sqltypes.Uint32, sqltypes.Uint64:
-			unsigned++
-			if tt > unsignedMax {
-				unsignedMax = tt
-			}
-		case sqltypes.Bit:
-			bit++
-		case sqltypes.Year:
-			year++
-		case sqltypes.Char, sqltypes.VarChar, sqltypes.Set, sqltypes.Enum:
-			char++
-		case sqltypes.Binary, sqltypes.VarBinary:
-			binary++
-		case sqltypes.TypeJSON:
-			json++
-		case sqltypes.Date:
-			date++
-		case sqltypes.Datetime:
-			datetime++
-		case sqltypes.Time:
-			time++
-		case sqltypes.Timestamp:
-			timestamp++
-		case sqltypes.Geometry:
-			geometry++
-		case sqltypes.Blob:
-			blob++
-		default:
-			continue
-		}
-		total++
+func (ta *typeAggregation) add(tt sqltypes.Type, f flag) {
+	switch tt {
+	case sqltypes.Float32, sqltypes.Float64:
+		ta.double++
+	case sqltypes.Decimal:
+		ta.decimal++
+	case sqltypes.Int8, sqltypes.Int16, sqltypes.Int24, sqltypes.Int32, sqltypes.Int64:
+		ta.signed++
+		if tt > ta.signedMax {
+			ta.signedMax = tt
+		}
+	case sqltypes.Uint8, sqltypes.Uint16, sqltypes.Uint24, sqltypes.Uint32, sqltypes.Uint64:
+		ta.unsigned++
+		if tt > ta.unsignedMax {
+			ta.unsignedMax = tt
+		}
+	case sqltypes.Bit:
+		ta.bit++
+	case sqltypes.Year:
+		ta.year++
+	case sqltypes.Char, sqltypes.VarChar, sqltypes.Set, sqltypes.Enum:
+		if f&flagExplicitCollation != 0 {
+			ta.charother++
+		}
+		ta.char++
+	case sqltypes.Binary, sqltypes.VarBinary:
+		if f&flagHex != 0 {
+			ta.charother++
+		}
+		ta.binary++
+	case sqltypes.TypeJSON:
+		ta.json++
+	case sqltypes.Date:
+		ta.date++
+	case sqltypes.Datetime:
+		ta.datetime++
+	case sqltypes.Time:
+		ta.time++
+	case sqltypes.Timestamp:
+		ta.timestamp++
+	case sqltypes.Geometry:
+		ta.geometry++
+	case sqltypes.Blob:
+		ta.blob++
+	default:
+		return
 	}
+	ta.total++
+}
 
+func (ta *typeAggregation) result() sqltypes.Type {
 	/*
 		If all types are numeric, the aggregated type is also numeric:
 			If at least one argument is double precision, the result is double precision.
@@ -529,67 +571,74 @@ func aggregatedType(env *ExpressionEnv, expr []Expr) sqltypes.Type {
 		Literal NULL operands are ignored for type aggregation.
 	*/
 
-	if bit == total {
+	if ta.bit == ta.total {
 		return sqltypes.Bit
-	} else if bit > 0 {
-		signed += bit
-		signedMax = sqltypes.Int64
+	} else if ta.bit > 0 {
+		ta.signed += ta.bit
+		ta.signedMax = sqltypes.Int64
 	}
 
-	if year == total {
+	if ta.year == ta.total {
 		return sqltypes.Year
-	} else if year > 0 {
-		signed += year
-		if sqltypes.Int32 > signedMax {
-			signedMax = sqltypes.Int32
+	} else if ta.year > 0 {
+		ta.signed += ta.year
+		if sqltypes.Int32 > ta.signedMax {
+			ta.signedMax = sqltypes.Int32
 		}
 	}
 
-	if double+decimal+signed+unsigned == total {
-		if double > 0 {
+	if ta.double+ta.decimal+ta.signed+ta.unsigned == ta.total {
+		if ta.double > 0 {
 			return sqltypes.Float64
 		}
-		if decimal > 0 {
+		if ta.decimal > 0 {
 			return sqltypes.Decimal
 		}
-		if signed == total {
-			return signedMax
+		if ta.signed == ta.total {
+			return ta.signedMax
 		}
-		if unsigned == total {
-			return unsignedMax
+		if ta.unsigned == ta.total {
+			return ta.unsignedMax
 		}
-		if unsignedMax == sqltypes.Uint64 && signed > 0 {
+		if ta.unsignedMax == sqltypes.Uint64 && ta.signed > 0 {
 			return sqltypes.Decimal
 		}
 		// TODO
 		return sqltypes.Uint64
 	}
 
-	if char == total {
+	if ta.char == ta.total {
 		return sqltypes.VarChar
 	}
-	if char+binary == total {
+	if ta.char+ta.binary == ta.total {
+		// HACK: this is not in the official documentation, but groups of strings where
+		// one of the strings is not directly a VARCHAR or VARBINARY (e.g. a hex literal,
+		// or a VARCHAR that has been explicitly collated) will result in VARCHAR when
+		// aggregated
+		if ta.charother > 0 {
+			return sqltypes.VarChar
+		}
 		return sqltypes.VarBinary
 	}
-	if json == total {
+	if ta.json == ta.total {
 		return sqltypes.TypeJSON
 	}
-	if date+time+timestamp+datetime == total {
-		if date == total {
+	if ta.date+ta.time+ta.timestamp+ta.datetime == ta.total {
+		if ta.date == ta.total {
 			return sqltypes.Date
 		}
-		if time == total {
+		if ta.time == ta.total {
 			return sqltypes.Time
 		}
-		if timestamp == total {
+		if ta.timestamp == ta.total {
 			return sqltypes.Timestamp
 		}
 		return sqltypes.Datetime
 	}
-	if geometry == total {
+	if ta.geometry == ta.total {
 		return sqltypes.Geometry
 	}
-	if blob > 0 {
+	if ta.blob > 0 {
 		return sqltypes.Blob
 	}
 	return sqltypes.VarChar
diff --git a/go/vt/vtgate/evalengine/logical.go b/go/vt/vtgate/evalengine/logical.go
index 4035d39417..04d34f924b 100644
--- a/go/vt/vtgate/evalengine/logical.go
+++ b/go/vt/vtgate/evalengine/logical.go
@@ -17,6 +17,7 @@ limitations under the License.
 package evalengine
 
 import (
+	"vitess.io/vitess/go/mysql/collations"
 	"vitess.io/vitess/go/sqltypes"
 	"vitess.io/vitess/go/vt/sqlparser"
 )
@@ -39,6 +40,24 @@ type (
 	OpLogicalAnd struct{}
 
 	boolean int8
+
+	// IsExpr represents the IS expression in MySQL.
+	// boolean_primary IS [NOT] {TRUE | FALSE | NULL}
+	IsExpr struct {
+		UnaryExpr
+		Op    sqlparser.IsExprOperator
+		Check func(*EvalResult) bool
+	}
+
+	WhenThen struct {
+		when Expr
+		then Expr
+	}
+
+	CaseExpr struct {
+		cases []WhenThen
+		Else  Expr
+	}
 )
 
 const (
@@ -151,14 +170,6 @@ func (l *LogicalExpr) typeof(env *ExpressionEnv) (sqltypes.Type, flag) {
 	return sqltypes.Uint64, f1 | f2
 }
 
-// IsExpr represents the IS expression in MySQL.
-// boolean_primary IS [NOT] {TRUE | FALSE | NULL}
-type IsExpr struct {
-	UnaryExpr
-	Op    sqlparser.IsExprOperator
-	Check func(*EvalResult) bool
-}
-
 func (i *IsExpr) eval(env *ExpressionEnv, result *EvalResult) {
 	var in EvalResult
 	in.init(env, i.Inner)
@@ -168,3 +179,115 @@ func (i *IsExpr) eval(env *ExpressionEnv, result *EvalResult) {
 func (i *IsExpr) typeof(env *ExpressionEnv) (sqltypes.Type, flag) {
 	return sqltypes.Int64, 0
 }
+
+func (c *CaseExpr) eval(env *ExpressionEnv, result *EvalResult) {
+	var tmp EvalResult
+	var matched = false
+	var ca collationAggregation
+	var local = collations.Local()
+
+	// From what we can tell, MySQL actually evaluates all the branches
+	// of a CASE expression, even after a truthy match. I.e. the CASE
+	// operator does _not_ short-circuit.
+
+	for _, whenThen := range c.cases {
+		tmp.init(env, whenThen.when)
+		truthy := tmp.isTruthy() == boolTrue
+
+		tmp.init(env, whenThen.then)
+		ca.add(local, tmp.collation())
+
+		if !matched && truthy {
+			tmp.resolve()
+			*result = tmp
+			matched = true
+		}
+	}
+	if c.Else != nil {
+		tmp.init(env, c.Else)
+		ca.add(local, tmp.collation())
+
+		if !matched {
+			tmp.resolve()
+			*result = tmp
+			matched = true
+		}
+	}
+
+	if !matched {
+		result.setNull()
+	}
+
+	t, _ := c.typeof(env)
+	result.coerce(t, ca.result().Collation)
+}
+
+func (c *CaseExpr) typeof(env *ExpressionEnv) (sqltypes.Type, flag) {
+	var ta typeAggregation
+	var resultFlag flag
+
+	for _, whenthen := range c.cases {
+		t, f := whenthen.then.typeof(env)
+		ta.add(t, f)
+		resultFlag = resultFlag | f
+	}
+	if c.Else != nil {
+		t, f := c.Else.typeof(env)
+		ta.add(t, f)
+		resultFlag = f
+	}
+	return ta.result(), resultFlag
+}
+
+func (c *CaseExpr) format(buf *formatter, depth int) {
+	buf.WriteString("CASE")
+	for _, cs := range c.cases {
+		buf.WriteString(" WHEN ")
+		cs.when.format(buf, depth)
+		buf.WriteString(" THEN ")
+		cs.then.format(buf, depth)
+	}
+	if c.Else != nil {
+		buf.WriteString(" ELSE ")
+		c.Else.format(buf, depth)
+	}
+}
+
+func (c *CaseExpr) constant() bool {
+	// TODO we should be able to simplify more cases than constant/simplify allows us to today
+	// example: case when true then col end
+	if c.Else != nil {
+		if !c.Else.constant() {
+			return false
+		}
+	}
+
+	for _, then := range c.cases {
+		if !then.when.constant() || !then.then.constant() {
+			return false
+		}
+	}
+
+	return true
+}
+
+func (c *CaseExpr) simplify(env *ExpressionEnv) error {
+	var err error
+	for i := range c.cases {
+		whenThen := &c.cases[i]
+		whenThen.when, err = simplifyExpr(env, whenThen.when)
+		if err != nil {
+			return err
+		}
+		whenThen.then, err = simplifyExpr(env, whenThen.then)
+		if err != nil {
+			return err
+		}
+	}
+	if c.Else != nil {
+		c.Else, err = simplifyExpr(env, c.Else)
+	}
+	return err
+}
+
+var _ Expr = (*CaseExpr)(nil)
diff --git a/go/vt/vtgate/evalengine/translate.go b/go/vt/vtgate/evalengine/translate.go
index 259f9876b1..50fdcf798a 100644
--- a/go/vt/vtgate/evalengine/translate.go
+++ b/go/vt/vtgate/evalengine/translate.go
@@ -489,6 +489,54 @@ func translateConvertUsingExpr(expr *sqlparser.ConvertUsingExpr, lookup Translat
 	return &using, nil
 }
 
+func translateCaseExpr(node *sqlparser.CaseExpr, lookup TranslationLookup) (Expr, error) {
+	var err error
+	var result CaseExpr
+
+	if node.Else != nil {
+		result.Else, err = translateExpr(node.Else, lookup)
+		if err != nil {
+			return nil, err
+		}
+	}
+
+	var cmpbase Expr
+	if node.Expr != nil {
+		cmpbase, err = translateExpr(node.Expr, lookup)
+		if err != nil {
+			return nil, err
+		}
+	}
+
+	for _, when := range node.Whens {
+		var cond, val Expr
+
+		cond, err = translateExpr(when.Cond, lookup)
+		if err != nil {
+			return nil, err
+		}
+
+		val, err = translateExpr(when.Val, lookup)
+		if err != nil {
+			return nil, err
+		}
+
+		if cmpbase != nil {
+			cond, err = translateComparisonExpr2(sqlparser.EqualOp, cmpbase, cond)
+			if err != nil {
+				return nil, err
+			}
+		}
+
+		result.cases = append(result.cases, WhenThen{
+			when: cond,
+			then: val,
+		})
+	}
+
+	return &result, nil
+}
+
 func translateExprNotSupported(e sqlparser.Expr) error {
 	return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "%s: %s", ErrTranslateExprNotSupported, sqlparser.String(e))
 }
@@ -544,6 +592,8 @@ func translateExpr(e sqlparser.Expr, lookup TranslationLookup) (Expr, error) {
 		return translateConvertExpr(node, lookup)
 	case *sqlparser.ConvertUsingExpr:
 		return translateConvertUsingExpr(node, lookup)
+	case *sqlparser.CaseExpr:
+		return translateCaseExpr(node, lookup)
 	default:
 		return nil, translateExprNotSupported(e)
 	}
diff --git a/go/vt/vtgate/planbuilder/abstract/queryprojection.go b/go/vt/vtgate/planbuilder/abstract/queryprojection.go
index b80d23c744..f91c3ae576 100644
--- a/go/vt/vtgate/planbuilder/abstract/queryprojection.go
+++ b/go/vt/vtgate/planbuilder/abstract/queryprojection.go
@@ -26,7 +26,6 @@ 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/semantics"
 )
 
 type (
@@ -46,7 +45,6 @@ type (
 		OrderExprs         []OrderBy
 		CanPushDownSorting bool
 		HasStar            bool
-		ProjectionError    error
 	}
 
 	// 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.
@@ -66,6 +64,16 @@ type (
 		// The original aliased expression that this group by is referring
 		aliasedExpr *sqlparser.AliasedExpr
 	}
+
+	Aggr struct {
+		Original *sqlparser.AliasedExpr
+		Func     *sqlparser.FuncExpr
+		OpCode   engine.AggregateOpcode
+		Alias    string
+		// The index at which the user expects to see this aggregated function. Set to nil, if the user does not ask for it
+		Index    *int
+		Distinct bool
+	}
 )
 
 func (b GroupBy) AsOrderBy() OrderBy {
@@ -122,7 +130,7 @@ func (s SelectExpr) GetAliasedExpr() (*sqlparser.AliasedExpr, error) {
 }
 
 // CreateQPFromSelect creates the QueryProjection for the input *sqlparser.Select
-func CreateQPFromSelect(sel *sqlparser.Select, semTable *semantics.SemTable) (*QueryProjection, error) {
+func CreateQPFromSelect(sel *sqlparser.Select) (*QueryProjection, error) {
 	qp := &QueryProjection{
 		Distinct: sel.Distinct,
 	}
@@ -133,7 +141,7 @@ func CreateQPFromSelect(sel *sqlparser.Select, semTable *semantics.SemTable) (*Q
 	}
 	for _, group := range sel.GroupBy {
 		selectExprIdx, aliasExpr := qp.FindSelectExprIndexForExpr(group)
-		expr, weightStrExpr, err := qp.GetSimplifiedExpr(group, semTable)
+		expr, weightStrExpr, err := qp.GetSimplifiedExpr(group)
 		if err != nil {
 			return nil, err
 		}
@@ -152,23 +160,11 @@ func CreateQPFromSelect(sel *sqlparser.Select, semTable *semantics.SemTable) (*Q
 		qp.groupByExprs = append(qp.groupByExprs, groupBy)
 	}
 
-	err = qp.addOrderBy(sel.OrderBy, semTable)
+	err = qp.addOrderBy(sel.OrderBy)
 	if err != nil {
 		return nil, err
 	}
 
-	if qp.HasAggr || len(qp.groupByExprs) > 0 {
-		expr := qp.getNonAggrExprNotMatchingGroupByExprs()
-		// if we have aggregation functions, non aggregating columns and GROUP BY,
-		// the non-aggregating expressions must all be listed in the GROUP BY list
-		if expr != nil {
-			if len(qp.groupByExprs) == 0 {
-				return nil, vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.MixOfGroupFuncAndFields, "In aggregated query without GROUP BY, expression of SELECT list contains nonaggregated column '%s'; this is incompatible with sql_mode=only_full_group_by", sqlparser.String(expr))
-			}
-			qp.ProjectionError = vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.WrongFieldWithGroup, "Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column '%s' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by", sqlparser.String(expr))
-		}
-	}
-
 	if qp.Distinct && !qp.HasAggr {
 		qp.groupByExprs = nil
 	}
@@ -207,7 +203,7 @@ func (qp *QueryProjection) addSelectExpressions(sel *sqlparser.Select) error {
 }
 
 // CreateQPFromUnion creates the QueryProjection for the input *sqlparser.Union
-func CreateQPFromUnion(union *sqlparser.Union, semTable *semantics.SemTable) (*QueryProjection, error) {
+func CreateQPFromUnion(union *sqlparser.Union) (*QueryProjection, error) {
 	qp := &QueryProjection{}
 
 	sel := sqlparser.GetFirstSelect(union)
@@ -216,7 +212,7 @@ func CreateQPFromUnion(union *sqlparser.Union, semTable *semantics.SemTable) (*Q
 		return nil, err
 	}
 
-	err = qp.addOrderBy(union.OrderBy, semTable)
+	err = qp.addOrderBy(union.OrderBy)
 	if err != nil {
 		return nil, err
 	}
@@ -224,10 +220,10 @@ func CreateQPFromUnion(union *sqlparser.Union, semTable *semantics.SemTable) (*Q
 	return qp, nil
 }
 
-func (qp *QueryProjection) addOrderBy(orderBy sqlparser.OrderBy, semTable *semantics.SemTable) error {
+func (qp *QueryProjection) addOrderBy(orderBy sqlparser.OrderBy) error {
 	canPushDownSorting := true
 	for _, order := range orderBy {
-		expr, weightStrExpr, err := qp.GetSimplifiedExpr(order.Expr, semTable)
+		expr, weightStrExpr, err := qp.GetSimplifiedExpr(order.Expr)
 		if err != nil {
 			return err
 		}
@@ -272,34 +268,12 @@ func (qp *QueryProjection) getNonAggrExprNotMatchingGroupByExprs() sqlparser.Sel
 		if expr.Aggr {
 			continue
 		}
-		isGroupByOk := false
-		for _, groupByExpr := range qp.groupByExprs {
-			exp, err := expr.GetExpr()
-			if err != nil {
-				return expr.Col
-			}
-			if sqlparser.EqualsExpr(groupByExpr.WeightStrExpr, exp) {
-				isGroupByOk = true
-				break
-			}
-		}
-		if !isGroupByOk {
+		if !qp.isExprInGroupByExprs(expr) {
 			return expr.Col
 		}
 	}
 	for _, order := range qp.OrderExprs {
-		// ORDER BY NULL or Aggregation functions need not be present in group by
-		if sqlparser.IsNull(order.Inner.Expr) || sqlparser.IsAggregation(order.WeightStrExpr) {
-			continue
-		}
-		isGroupByOk := false
-		for _, groupByExpr := range qp.groupByExprs {
-			if sqlparser.EqualsExpr(groupByExpr.WeightStrExpr, order.WeightStrExpr) {
-				isGroupByOk = true
-				break
-			}
-		}
-		if !isGroupByOk {
+		if !qp.isOrderByExprInGroupBy(order) {
 			return &sqlparser.AliasedExpr{
 				Expr: order.Inner.Expr,
 			}
@@ -308,11 +282,34 @@ func (qp *QueryProjection) getNonAggrExprNotMatchingGroupByExprs() sqlparser.Sel
 	return nil
 }
 
+func (qp *QueryProjection) isOrderByExprInGroupBy(order OrderBy) bool {
+	// ORDER BY NULL or Aggregation functions need not be present in group by
+	if sqlparser.IsNull(order.Inner.Expr) || sqlparser.IsAggregation(order.WeightStrExpr) {
+		return true
+	}
+	for _, groupByExpr := range qp.groupByExprs {
+		if sqlparser.EqualsExpr(groupByExpr.WeightStrExpr, order.WeightStrExpr) {
+			return true
+		}
+	}
+	return false
+}
+
+func (qp *QueryProjection) isExprInGroupByExprs(expr SelectExpr) bool {
+	for _, groupByExpr := range qp.groupByExprs {
+		exp, err := expr.GetExpr()
+		if err != nil {
+			return false
+		}
+		if sqlparser.EqualsExpr(groupByExpr.WeightStrExpr, exp) {
+			return true
+		}
+	}
+	return false
+}
+
 // GetSimplifiedExpr takes an expression used in ORDER BY or GROUP BY, and returns an expression that is simpler to evaluate
-func (qp *QueryProjection) GetSimplifiedExpr(
-	e sqlparser.Expr,
-	semTable *semantics.SemTable,
-) (expr sqlparser.Expr, weightStrExpr sqlparser.Expr, err error) {
+func (qp *QueryProjection) GetSimplifiedExpr(e sqlparser.Expr) (expr sqlparser.Expr, weightStrExpr sqlparser.Expr, err error) {
 	// 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
@@ -405,25 +402,27 @@ func (qp *QueryProjection) NeedsDistinct() bool {
 	return true
 }
 
-type Aggr struct {
-	Original *sqlparser.AliasedExpr
-	Func     *sqlparser.FuncExpr
-	OpCode   engine.AggregateOpcode
-	Alias    string
-	// The index at which the user expects to see this aggregated function. Set to nil, if the user does not ask for it
-	Index    *int
-	Distinct bool
-}
-
 func (qp *QueryProjection) AggregationExpressions() (out []Aggr, err error) {
 	for idx, expr := range qp.SelectExprs {
-		if !sqlparser.ContainsAggregation(expr.Col) {
-			continue
-		}
 		aliasedExpr, err := expr.GetAliasedExpr()
 		if err != nil {
 			return nil, err
 		}
+
+		idxCopy := idx
+
+		if !sqlparser.ContainsAggregation(expr.Col) {
+			if !qp.isExprInGroupByExprs(expr) {
+				out = append(out, Aggr{
+					Original: aliasedExpr,
+					OpCode:   engine.AggregateRandom,
+					Alias:    aliasedExpr.ColumnName(),
+					Index:    &idxCopy,
+				})
+			}
+			continue
+		}
+
 		fExpr, isFunc := aliasedExpr.Expr.(*sqlparser.FuncExpr)
 		if !isFunc {
 			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: in scatter query: complex aggregate expression")
@@ -444,7 +443,6 @@ func (qp *QueryProjection) AggregationExpressions() (out []Aggr, err error) {
 			}
 		}
 
-		idxCopy := idx
 		out = append(out, Aggr{
 			Original: aliasedExpr,
 			Func:     fExpr,
@@ -454,6 +452,28 @@ func (qp *QueryProjection) AggregationExpressions() (out []Aggr, err error) {
 			Distinct: fExpr.Distinct,
 		})
 	}
+	for i, orderExpr := range qp.OrderExprs {
+		newIdx := i + len(qp.SelectExprs)
+		if !qp.isOrderByExprInGroupBy(orderExpr) {
+
+			original := &sqlparser.AliasedExpr{Expr: orderExpr.Inner.Expr}
+			found := false
+			for _, aggr := range out {
+				if sqlparser.EqualsRefOfAliasedExpr(aggr.Original, original) {
+					found = true
+					break
+				}
+			}
+			if found {
+				continue
+			}
+			out = append(out, Aggr{
+				Original: original,
+				OpCode:   engine.AggregateRandom,
+				Index:    &newIdx,
+			})
+		}
+	}
 	return
 }
 
diff --git a/go/vt/vtgate/planbuilder/aggregation_pushing.go b/go/vt/vtgate/planbuilder/aggregation_pushing.go
index 4707c667b6..bae6486f2b 100644
--- a/go/vt/vtgate/planbuilder/aggregation_pushing.go
+++ b/go/vt/vtgate/planbuilder/aggregation_pushing.go
@@ -38,7 +38,7 @@ func (hp *horizonPlanning) pushAggregation(
 	switch plan := plan.(type) {
 	case *routeGen4:
 		output = plan
-		groupingOffsets, outputAggrsOffset, err = pushAggrOnRoute(ctx, plan, aggregations, grouping, ignoreOutputOrder)
+		groupingOffsets, outputAggrsOffset, _, err = pushAggrOnRoute(ctx, plan, aggregations, grouping, ignoreOutputOrder)
 		return
 
 	case *joinGen4:
@@ -67,14 +67,18 @@ func pushAggrOnRoute(
 	aggregations []abstract.Aggr,
 	grouping []abstract.GroupBy,
 	ignoreOutputOrder bool,
-) ([]offsets, [][]offsets, error) {
+) (
+	groupingOffsets []offsets,
+	vtgateAggregation [][]offsets,
+	nonAggrOffsets []offsets,
+	err error,
+) {
 	columnOrderMatters := !ignoreOutputOrder
 	sel, isSel := plan.Select.(*sqlparser.Select)
 	if !isSel {
-		return nil, nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "can't plan aggregation on union")
+		return nil, nil, nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "can't plan aggregation on union")
 	}
 
-	var vtgateAggregation [][]offsets
 	var groupingCols []int
 	var reorg = passThrough
 
@@ -88,7 +92,7 @@ func pushAggrOnRoute(
 		grouping, reorg, it = sortOffsets(grouping, aggregations)
 		vtgateAggregation, groupingCols, err = pushAggrsAndGroupingInOrder(ctx, plan, it, sel, vtgateAggregation, groupingCols)
 		if err != nil {
-			return nil, nil, err
+			return nil, nil, nil, err
 		}
 	} else {
 		// if we haven't already pushed the aggregations, now is the time
@@ -98,7 +102,7 @@ func pushAggrOnRoute(
 		}
 	}
 
-	groupingOffsets := make([]offsets, 0, len(grouping))
+	groupingOffsets = make([]offsets, 0, len(grouping))
 	for idx, expr := range grouping {
 		sel.AddGroupBy(expr.Inner)
 		var pos offsets
@@ -106,7 +110,7 @@ func pushAggrOnRoute(
 			// we have not yet pushed anything, so we need to push the expression first
 			col, _, err := addExpressionToRoute(ctx, plan, &sqlparser.AliasedExpr{Expr: expr.Inner}, true)
 			if err != nil {
-				return nil, nil, err
+				return nil, nil, nil, err
 			}
 			pos = newOffset(col)
 		} else {
@@ -117,7 +121,7 @@ func pushAggrOnRoute(
 			wsExpr := weightStringFor(expr.WeightStrExpr)
 			wsCol, _, err := addExpressionToRoute(ctx, plan, &sqlparser.AliasedExpr{Expr: wsExpr}, true)
 			if err != nil {
-				return nil, nil, err
+				return nil, nil, nil, err
 			}
 			pos.wsCol = wsCol
 			sel.AddGroupBy(wsExpr)
@@ -126,7 +130,7 @@ func pushAggrOnRoute(
 	}
 
 	groupingOffsets, vtgateAggregation = reorg(groupingOffsets, vtgateAggregation)
-	return groupingOffsets, vtgateAggregation, nil
+	return groupingOffsets, vtgateAggregation, nil, nil
 }
 
 func pushAggrsAndGroupingInOrder(
@@ -214,7 +218,7 @@ func (hp *horizonPlanning) pushAggrOnJoin(
 
 	// We need to group by the columns used in the join condition.
 	// If we don't, the LHS will not be able to return the column, and it can't be used to send down to the RHS
-	lhsCols, err := hp.createGroupingsForColumns(ctx, join.LHSColumns)
+	lhsCols, err := hp.createGroupingsForColumns(join.LHSColumns)
 	if err != nil {
 		return nil, nil, err
 	}
@@ -298,7 +302,7 @@ func (hp *horizonPlanning) pushAggrOnSemiJoin(
 ) ([]offsets, [][]offsets, error) {
 	// We need to group by the columns used in the join condition.
 	// If we don't, the LHS will not be able to return the column, and it can't be used to send down to the RHS
-	lhsCols, err := hp.createGroupingsForColumns(ctx, join.LHSColumns)
+	lhsCols, err := hp.createGroupingsForColumns(join.LHSColumns)
 	if err != nil {
 		return nil, nil, err
 	}
diff --git a/go/vt/vtgate/planbuilder/gen4_planner.go b/go/vt/vtgate/planbuilder/gen4_planner.go
index 11445ddc27..996248046d 100644
--- a/go/vt/vtgate/planbuilder/gen4_planner.go
+++ b/go/vt/vtgate/planbuilder/gen4_planner.go
@@ -413,7 +413,7 @@ func planHorizon(ctx *plancontext.PlanningContext, plan logicalPlan, in sqlparse
 }
 
 func planOrderByOnUnion(ctx *plancontext.PlanningContext, plan logicalPlan, union *sqlparser.Union) (logicalPlan, error) {
-	qp, err := abstract.CreateQPFromUnion(union, ctx.SemTable)
+	qp, err := abstract.CreateQPFromUnion(union)
 	if err != nil {
 		return nil, err
 	}
diff --git a/go/vt/vtgate/planbuilder/horizon_planning.go b/go/vt/vtgate/planbuilder/horizon_planning.go
index 0033360542..4f45a15ec6 100644
--- a/go/vt/vtgate/planbuilder/horizon_planning.go
+++ b/go/vt/vtgate/planbuilder/horizon_planning.go
@@ -69,7 +69,7 @@ func (hp *horizonPlanning) planHorizon(ctx *plancontext.PlanningContext, plan lo
 	}
 
 	var err error
-	hp.qp, err = abstract.CreateQPFromSelect(hp.sel, ctx.SemTable)
+	hp.qp, err = abstract.CreateQPFromSelect(hp.sel)
 	if err != nil {
 		return nil, err
 	}
@@ -494,10 +494,6 @@ func (hp *horizonPlanning) planAggregations(ctx *plancontext.PlanningContext, pl
 		return newPlan, nil
 	}
 
-	if hp.qp.ProjectionError != nil {
-		return nil, hp.qp.ProjectionError
-	}
-
 	return hp.planAggrUsingOA(ctx, plan, grouping)
 }
 
@@ -652,15 +648,17 @@ func generateAggregateParams(aggrs []abstract.Aggr, aggrParamOffsets [][]offsets
 
 		opcode := engine.AggregateSum
 		if aggr.OpCode == engine.AggregateMin ||
-			aggr.OpCode == engine.AggregateMax {
+			aggr.OpCode == engine.AggregateMax ||
+			aggr.OpCode == engine.AggregateRandom {
 			opcode = aggr.OpCode
 		}
 
 		aggrParams[idx] = &engine.AggregateParams{
-			Opcode: opcode,
-			Col:    offset,
-			Alias:  aggr.Alias,
-			Expr:   aggr.Func,
+			Opcode:   opcode,
+			Col:      offset,
+			Alias:    aggr.Alias,
+			Expr:     aggr.Func,
+			Original: aggr.Original,
 		}
 	}
 	return aggrParams, nil
@@ -700,6 +698,7 @@ func addColumnsToOA(
 				WCol:        o.wsCol,
 				Alias:       a.Alias,
 				Expr:        a.Func,
+				Original:    a.Original,
 				CollationID: collID,
 			})
 		}
@@ -742,7 +741,7 @@ func (hp *horizonPlanning) handleDistinctAggr(ctx *plancontext.PlanningContext,
 			err = vterrors.Errorf(vtrpcpb.Code_INVALID_ARGUMENT, "syntax error: %s", sqlparser.String(expr.Original))
 			return
 		}
-		inner, innerWS, err := hp.qp.GetSimplifiedExpr(aliasedExpr.Expr, ctx.SemTable)
+		inner, innerWS, err := hp.qp.GetSimplifiedExpr(aliasedExpr.Expr)
 		if err != nil {
 			return nil, nil, nil, err
 		}
@@ -798,13 +797,10 @@ func newOffset(col int) offsets {
 	return offsets{col: col, wsCol: -1}
 }
 
-func (hp *horizonPlanning) createGroupingsForColumns(
-	ctx *plancontext.PlanningContext,
-	columns []*sqlparser.ColName,
-) ([]abstract.GroupBy, error) {
+func (hp *horizonPlanning) createGroupingsForColumns(columns []*sqlparser.ColName) ([]abstract.GroupBy, error) {
 	var lhsGrouping []abstract.GroupBy
 	for _, lhsColumn := range columns {
-		expr, wsExpr, err := hp.qp.GetSimplifiedExpr(lhsColumn, ctx.SemTable)
+		expr, wsExpr, err := hp.qp.GetSimplifiedExpr(lhsColumn)
 		if err != nil {
 			return nil, err
 		}
@@ -818,6 +814,9 @@ func (hp *horizonPlanning) createGroupingsForColumns(
 }
 
 func isCountStar(f *sqlparser.FuncExpr) bool {
+	if f == nil {
+		return false
+	}
 	_, isStar := f.Exprs[0].(*sqlparser.StarExpr)
 	return isStar
 }
@@ -1094,8 +1093,8 @@ func findExprInOrderedAggr(plan *orderedAggregate, order abstract.OrderBy) (keyC
 		}
 	}
 	for _, aggregate := range plan.aggregates {
-		if sqlparser.EqualsExpr(order.WeightStrExpr, aggregate.Expr) ||
-			sqlparser.EqualsExpr(order.Inner.Expr, aggregate.Expr) {
+		if sqlparser.EqualsExpr(order.WeightStrExpr, aggregate.Original.Expr) ||
+			sqlparser.EqualsExpr(order.Inner.Expr, aggregate.Original.Expr) {
 			return aggregate.Col, -1, true
 		}
 	}


Test patch:
diff --git a/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go b/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go
index dfc5a567b9..2e048c91bb 100644
--- a/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go
+++ b/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go
@@ -17,14 +17,15 @@ limitations under the License.
 package aggregation
 
 import (
+	"context"
 	"fmt"
 	"testing"
 
-	"vitess.io/vitess/go/test/endtoend/utils"
-
 	"github.com/stretchr/testify/require"
 
+	"vitess.io/vitess/go/mysql"
 	"vitess.io/vitess/go/test/endtoend/cluster"
+	"vitess.io/vitess/go/test/endtoend/utils"
 )
 
 func start(t *testing.T) (utils.MySQLCompare, func()) {
@@ -296,3 +297,20 @@ func TestGreaterEqualFilterOnScatter(t *testing.T) {
 		})
 	}
 }
+
+func TestGroupByOnlyFullGroupByOff(t *testing.T) {
+	vtParams := mysql.ConnParams{
+		Host: "localhost",
+		Port: clusterInstance.VtgateMySQLPort,
+	}
+	conn, err := mysql.Connect(context.Background(), &vtParams)
+	require.NoError(t, err)
+	defer conn.Close()
+
+	utils.Exec(t, conn, "insert into t9(id1, id2, id3) values(1,'a', '1'), (2,'Abc','2'), (3,'b', '3'), (4,'c', '4'), (5,'test', '5')")
+	utils.Exec(t, conn, "insert into t9(id1, id2, id3) values(6,'a', '11'), (7,'Abc','22'), (8,'b', '33'), (9,'c', '44'), (10,'test', '55')")
+	utils.Exec(t, conn, "set @@sql_mode = ' '")
+
+	// We do not use AssertMatches here because the results for the second column are random
+	utils.Exec(t, conn, "select /*vt+ PLANNER=gen4 */ id2, id3 from t9 group by id2")
+}
diff --git a/go/test/endtoend/vtgate/queries/aggregation/main_test.go b/go/test/endtoend/vtgate/queries/aggregation/main_test.go
index 19d68141d5..6c7a03aecc 100644
--- a/go/test/endtoend/vtgate/queries/aggregation/main_test.go
+++ b/go/test/endtoend/vtgate/queries/aggregation/main_test.go
@@ -48,6 +48,13 @@ create table t3_id7_idx(
     primary key(id)
 ) Engine=InnoDB;
 
+create table t9(
+	id1 bigint,
+	id2 varchar(10),
+	id3 varchar(10),
+	primary key(id1)
+) ENGINE=InnoDB DEFAULT charset=utf8mb4 COLLATE=utf8mb4_general_ci;
+
 create table aggr_test(
 	id bigint,
 	val1 varchar(16),
@@ -127,6 +134,14 @@ create table t7_xxhash_idx(
         }
       ]
     },
+	"t9": {
+      "column_vindexes": [
+        {
+          "column": "id1",
+          "name": "hash"
+        }
+      ]
+	},
 	"aggr_test": {
       "column_vindexes": [
         {
@@ -205,7 +220,7 @@ func TestMain(m *testing.M) {
 		}
 		clusterInstance.VtGateExtraArgs = []string{"--schema_change_signal"}
 		clusterInstance.VtTabletExtraArgs = []string{"--queryserver-config-schema-change-signal", "--queryserver-config-schema-change-signal-interval", "0.1"}
-		err = clusterInstance.StartKeyspace(*keyspace, []string{"-80", "80-"}, 1, true)
+		err = clusterInstance.StartKeyspace(*keyspace, []string{"-80", "80-"}, 0, false)
 		if err != nil {
 			return 1
 		}
diff --git a/go/vt/vtgate/evalengine/integration/comparison_test.go b/go/vt/vtgate/evalengine/integration/comparison_test.go
index dcbb3d0a26..8fa1e08a83 100644
--- a/go/vt/vtgate/evalengine/integration/comparison_test.go
+++ b/go/vt/vtgate/evalengine/integration/comparison_test.go
@@ -26,6 +26,7 @@ import (
 	"time"
 
 	"vitess.io/vitess/go/mysql"
+	"vitess.io/vitess/go/mysql/collations"
 	"vitess.io/vitess/go/sqltypes"
 	"vitess.io/vitess/go/vt/vtgate/evalengine"
 )
@@ -47,11 +48,14 @@ func perm1(a []string, f func([]string), i int) {
 	}
 }
 
-func normalize(v sqltypes.Value) string {
+func normalize(v sqltypes.Value, coll collations.ID) string {
 	typ := v.Type()
 	if typ == sqltypes.Null {
 		return "NULL"
 	}
+	if typ == sqltypes.VarChar && coll == collations.CollationBinaryID {
+		return fmt.Sprintf("VARBINARY(%q)", v.Raw())
+	}
 	if v.IsQuoted() || typ == sqltypes.Bit {
 		return fmt.Sprintf("%v(%q)", typ, v.Raw())
 	}
@@ -73,18 +77,33 @@ var debugPrintAll = flag.Bool("print-all", false, "print all matching tests")
 var debugNormalize = flag.Bool("normalize", true, "normalize comparisons against MySQL values")
 var debugSimplify = flag.Bool("simplify", time.Now().UnixNano()&1 != 0, "simplify expressions before evaluating them")
 var debugCheckTypes = flag.Bool("check-types", true, "check the TypeOf operator for all queries")
+var debugCheckCollations = flag.Bool("check-collations", true, "check the returned collations for all queries")
 
-func compareRemoteQuery(t *testing.T, conn *mysql.Conn, query string) {
+func compareRemoteExpr(t *testing.T, conn *mysql.Conn, expr string) {
 	t.Helper()
 
-	local, localType, localErr := safeEvaluate(query)
-	remote, remoteErr := conn.ExecuteFetch(query, 1, false)
+	localQuery := "SELECT " + expr
+	remoteQuery := "SELECT " + expr
+	if *debugCheckCollations {
+		remoteQuery = fmt.Sprintf("SELECT %s, COLLATION(%s)", expr, expr)
+	}
+
+	local, localType, localErr := safeEvaluate(localQuery)
+	remote, remoteErr := conn.ExecuteFetch(remoteQuery, 1, true)
 
 	var localVal, remoteVal string
+	var localCollation, remoteCollation collations.ID
 	if localErr == nil {
 		v := local.Value()
+		if *debugCheckCollations {
+			if v.IsNull() {
+				localCollation = collations.CollationBinaryID
+			} else {
+				localCollation = local.Collation()
+			}
+		}
 		if *debugNormalize {
-			localVal = normalize(v)
+			localVal = normalize(v, local.Collation())
 		} else {
 			localVal = v.String()
 		}
@@ -92,33 +111,41 @@ func compareRemoteQuery(t *testing.T, conn *mysql.Conn, query string) {
 			tt := v.Type()
 			if tt != sqltypes.Null && tt != localType {
 				t.Errorf("evaluation type mismatch: eval=%v vs typeof=%v\nlocal: %s\nquery: %s (SIMPLIFY=%v)",
-					tt, localType, localVal, query, *debugSimplify)
+					tt, localType, localVal, localQuery, *debugSimplify)
 			}
 		}
 	}
 	if remoteErr == nil {
 		if *debugNormalize {
-			remoteVal = normalize(remote.Rows[0][0])
+			remoteVal = normalize(remote.Rows[0][0], collations.ID(remote.Fields[0].Charset))
 		} else {
 			remoteVal = remote.Rows[0][0].String()
 		}
+		if *debugCheckCollations {
+			if remote.Rows[0][0].IsNull() {
+				// TODO: passthrough proper collations for nullable fields
+				remoteCollation = collations.CollationBinaryID
+			} else {
+				remoteCollation = collations.Local().LookupByName(remote.Rows[0][1].ToString()).ID()
+			}
+		}
 	}
-	if diff := compareResult(localErr, remoteErr, localVal, remoteVal); diff != "" {
-		t.Errorf("%s\nquery: %s (SIMPLIFY=%v)", diff, query, *debugSimplify)
+	if diff := compareResult(localErr, remoteErr, localVal, remoteVal, localCollation, remoteCollation); diff != "" {
+		t.Errorf("%s\nquery: %s (SIMPLIFY=%v)", diff, localQuery, *debugSimplify)
 	} else if *debugPrintAll {
-		t.Logf("local=%s mysql=%s\nquery: %s", localVal, remoteVal, query)
+		t.Logf("local=%s mysql=%s\nquery: %s", localVal, remoteVal, localQuery)
 	}
 }
 
+var comparisonElements = []string{"NULL", "-1", "0", "1",
+	`'foo'`, `'bar'`, `'FOO'`, `'BAR'`,
+	`'foo' collate utf8mb4_0900_as_cs`,
+	`'FOO' collate utf8mb4_0900_as_cs`,
+	`_latin1 'foo'`,
+	`_latin1 'FOO'`,
+}
+
 func TestAllComparisons(t *testing.T) {
-	var elems = []string{"NULL", "-1", "0", "1",
-		`'foo'`, `'bar'`, `'FOO'`, `'BAR'`,
-		`'foo' collate utf8mb4_as_cs`, // invalid collation for testing error messages
-		`'foo' collate utf8mb4_cs_as`,
-		`'FOO' collate utf8mb4_cs_as`,
-		`_latin1 'foo'`,
-		`_latin1 'FOO'`,
-	}
 	var operators = []string{"=", "!=", "<=>", "<", "<=", ">", ">="}
 
 	var conn = mysqlconn(t)
@@ -126,10 +153,10 @@ func TestAllComparisons(t *testing.T) {
 
 	for _, op := range operators {
 		t.Run(op, func(t *testing.T) {
-			for i := 0; i < len(elems); i++ {
-				for j := 0; j < len(elems); j++ {
-					query := fmt.Sprintf("SELECT %s %s %s", elems[i], op, elems[j])
-					compareRemoteQuery(t, conn, query)
+			for i := 0; i < len(comparisonElements); i++ {
+				for j := 0; j < len(comparisonElements); j++ {
+					query := fmt.Sprintf("%s %s %s", comparisonElements[i], op, comparisonElements[j])
+					compareRemoteExpr(t, conn, query)
 				}
 			}
 		})
@@ -152,8 +179,8 @@ func TestAllTupleComparisons(t *testing.T) {
 		t.Run(op, func(t *testing.T) {
 			for i := 0; i < len(tuples); i++ {
 				for j := 0; j < len(tuples); j++ {
-					query := fmt.Sprintf("SELECT %s %s %s", tuples[i], op, tuples[j])
-					compareRemoteQuery(t, conn, query)
+					query := fmt.Sprintf("%s %s %s", tuples[i], op, tuples[j])
+					compareRemoteExpr(t, conn, query)
 				}
 			}
 		})
@@ -181,8 +208,8 @@ func TestAllIsStatements(t *testing.T) {
 
 	for _, l := range left {
 		for _, r := range right {
-			query := fmt.Sprintf("SELECT %s IS %s", l, r)
-			compareRemoteQuery(t, conn, query)
+			query := fmt.Sprintf("%s IS %s", l, r)
+			compareRemoteExpr(t, conn, query)
 		}
 	}
 }
@@ -225,8 +252,8 @@ func TestMultiComparisons(t *testing.T) {
 		for _, argc := range []int{2, 3} {
 			t.Run(fmt.Sprintf("%s(#%d)", method, argc), func(t *testing.T) {
 				genSubsets(numbers, argc, func(num []string) {
-					query := fmt.Sprintf("SELECT %s(%s)", method, strings.Join(num, ","))
-					compareRemoteQuery(t, conn, query)
+					query := fmt.Sprintf("%s(%s)", method, strings.Join(num, ","))
+					compareRemoteExpr(t, conn, query)
 				})
 			})
 		}
@@ -254,8 +281,8 @@ func TestLikeComparison(t *testing.T) {
 
 	for _, lhs := range left {
 		for _, rhs := range right {
-			query := fmt.Sprintf("SELECT %s LIKE %s", lhs, rhs)
-			compareRemoteQuery(t, conn, query)
+			query := fmt.Sprintf("%s LIKE %s", lhs, rhs)
+			compareRemoteExpr(t, conn, query)
 		}
 	}
 }
@@ -273,7 +300,7 @@ func TestCollationOperations(t *testing.T) {
 	defer conn.Close()
 
 	for _, expr := range cases {
-		compareRemoteQuery(t, conn, "SELECT "+expr)
+		compareRemoteExpr(t, conn, expr)
 	}
 }
 
@@ -293,8 +320,8 @@ func TestNegateArithmetic(t *testing.T) {
 	defer conn.Close()
 
 	for _, rhs := range cases {
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT - %s", rhs))
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT -%s", rhs))
+		compareRemoteExpr(t, conn, fmt.Sprintf("- %s", rhs))
+		compareRemoteExpr(t, conn, fmt.Sprintf("-%s", rhs))
 	}
 }
 
@@ -324,7 +351,7 @@ func TestNumericTypes(t *testing.T) {
 	defer conn.Close()
 
 	for _, rhs := range numbers {
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT %s", rhs))
+		compareRemoteExpr(t, conn, fmt.Sprintf("%s", rhs))
 	}
 }
 
@@ -341,10 +368,10 @@ func TestHexArithmetic(t *testing.T) {
 
 	for _, lhs := range cases {
 		for _, rhs := range cases {
-			compareRemoteQuery(t, conn, fmt.Sprintf("SELECT %s + %s", lhs, rhs))
+			compareRemoteExpr(t, conn, fmt.Sprintf("%s + %s", lhs, rhs))
 
 			// compare with negative values too
-			compareRemoteQuery(t, conn, fmt.Sprintf("SELECT -%s + -%s", lhs, rhs))
+			compareRemoteExpr(t, conn, fmt.Sprintf("-%s + -%s", lhs, rhs))
 		}
 	}
 }
@@ -379,7 +406,7 @@ func TestTypes(t *testing.T) {
 	}
 
 	for _, query := range queries {
-		compareRemoteQuery(t, conn, "SELECT "+query)
+		compareRemoteExpr(t, conn, query)
 	}
 }
 
@@ -399,18 +426,18 @@ func TestFloatFormatting(t *testing.T) {
 	defer conn.Close()
 
 	for _, f := range floats {
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT %s + 0.0e0", f))
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT -%s", f))
+		compareRemoteExpr(t, conn, fmt.Sprintf("%s + 0.0e0", f))
+		compareRemoteExpr(t, conn, fmt.Sprintf("-%s", f))
 	}
 
 	for i := 0; i < 64; i++ {
 		v := uint64(1) << i
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT %d + 0.0e0", v))
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT %d + 0.0e0", v+1))
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT %d + 0.0e0", ^v))
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT -%de0", v))
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT -%de0", v+1))
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT -%de0", ^v))
+		compareRemoteExpr(t, conn, fmt.Sprintf("%d + 0.0e0", v))
+		compareRemoteExpr(t, conn, fmt.Sprintf("%d + 0.0e0", v+1))
+		compareRemoteExpr(t, conn, fmt.Sprintf("%d + 0.0e0", ^v))
+		compareRemoteExpr(t, conn, fmt.Sprintf("-%de0", v))
+		compareRemoteExpr(t, conn, fmt.Sprintf("-%de0", v+1))
+		compareRemoteExpr(t, conn, fmt.Sprintf("-%de0", ^v))
 	}
 }
 
@@ -427,7 +454,7 @@ func TestWeightStrings(t *testing.T) {
 	defer conn.Close()
 
 	for _, i := range inputs {
-		compareRemoteQuery(t, conn, fmt.Sprintf("SELECT WEIGHT_STRING(%s)", i))
+		compareRemoteExpr(t, conn, fmt.Sprintf("WEIGHT_STRING(%s)", i))
 	}
 }
 
@@ -450,7 +477,7 @@ func TestBitwiseOperators(t *testing.T) {
 		t.Run(op, func(t *testing.T) {
 			for _, lhs := range bitwiseInputs {
 				for _, rhs := range bitwiseInputs {
-					compareRemoteQuery(t, conn, fmt.Sprintf("SELECT %s %s %s", lhs, op, rhs))
+					compareRemoteExpr(t, conn, fmt.Sprintf("%s %s %s", lhs, op, rhs))
 				}
 			}
 		})
@@ -464,7 +491,7 @@ func TestBitwiseOperatorsUnary(t *testing.T) {
 	for _, op := range []string{"~", "BIT_COUNT"} {
 		t.Run(op, func(t *testing.T) {
 			for _, rhs := range bitwiseInputs {
-				compareRemoteQuery(t, conn, fmt.Sprintf("SELECT %s(%s)", op, rhs))
+				compareRemoteExpr(t, conn, fmt.Sprintf("%s(%s)", op, rhs))
 			}
 		})
 	}
@@ -486,8 +513,8 @@ func TestDecimalClamping(t *testing.T) {
 	for pos := 0; pos < len(Pi); pos++ {
 		for m := 0; m < min(len(Pi), 67); m += 2 {
 			for d := 0; d <= min(m, 33); d += 2 {
-				query := fmt.Sprintf("SELECT CAST(%s.%s AS DECIMAL(%d, %d))", Pi[:pos], Pi[pos:], m, d)
-				compareRemoteQuery(t, conn, query)
+				query := fmt.Sprintf("CAST(%s.%s AS DECIMAL(%d, %d))", Pi[:pos], Pi[pos:], m, d)
+				compareRemoteExpr(t, conn, query)
 			}
 		}
 	}
@@ -500,11 +527,11 @@ func TestLargeIntegers(t *testing.T) {
 	var largepi = Pi + Pi
 
 	for pos := 1; pos < len(largepi); pos++ {
-		query := fmt.Sprintf("SELECT %s", largepi[:pos])
-		compareRemoteQuery(t, conn, query)
+		query := fmt.Sprintf("%s", largepi[:pos])
+		compareRemoteExpr(t, conn, query)
 
-		query = fmt.Sprintf("SELECT -%s", largepi[:pos])
-		compareRemoteQuery(t, conn, query)
+		query = fmt.Sprintf("-%s", largepi[:pos])
+		compareRemoteExpr(t, conn, query)
 	}
 }
 
@@ -515,11 +542,11 @@ func TestLargeDecimals(t *testing.T) {
 	var largepi = Pi + Pi
 
 	for pos := 0; pos < len(largepi); pos++ {
-		query := fmt.Sprintf("SELECT %s.%s", largepi[:pos], largepi[pos:])
-		compareRemoteQuery(t, conn, query)
+		query := fmt.Sprintf("%s.%s", largepi[:pos], largepi[pos:])
+		compareRemoteExpr(t, conn, query)
 
-		query = fmt.Sprintf("SELECT -%s.%s", largepi[:pos], largepi[pos:])
-		compareRemoteQuery(t, conn, query)
+		query = fmt.Sprintf("-%s.%s", largepi[:pos], largepi[pos:])
+		compareRemoteExpr(t, conn, query)
 	}
 }
 
@@ -548,8 +575,8 @@ func TestConversionOperators(t *testing.T) {
 
 	for _, lhs := range left {
 		for _, rhs := range right {
-			compareRemoteQuery(t, conn, fmt.Sprintf("SELECT CAST(%s AS %s)", lhs, rhs))
-			compareRemoteQuery(t, conn, fmt.Sprintf("SELECT CONVERT(%s, %s)", lhs, rhs))
+			compareRemoteExpr(t, conn, fmt.Sprintf("CAST(%s AS %s)", lhs, rhs))
+			compareRemoteExpr(t, conn, fmt.Sprintf("CONVERT(%s, %s)", lhs, rhs))
 		}
 	}
 }
@@ -571,8 +598,76 @@ func TestCharsetConversionOperators(t *testing.T) {
 	for _, pfx := range introducers {
 		for _, lhs := range contents {
 			for _, rhs := range charsets {
-				compareRemoteQuery(t, conn, fmt.Sprintf("SELECT HEX(CONVERT(%s %s USING %s))", pfx, lhs, rhs))
+				compareRemoteExpr(t, conn, fmt.Sprintf("HEX(CONVERT(%s %s USING %s))", pfx, lhs, rhs))
+			}
+		}
+	}
+}
+
+func TestCaseExprWithPredicate(t *testing.T) {
+	var conn = mysqlconn(t)
+	defer conn.Close()
+
+	var predicates = []string{
+		"true",
+		"false",
+		"null",
+		"1=1",
+		"1=2",
+	}
+
+	var elements []string
+	elements = append(elements, bitwiseInputs...)
+	elements = append(elements, comparisonElements...)
+
+	for _, pred1 := range predicates {
+		for _, val1 := range elements {
+			for _, elseVal := range elements {
+				query := fmt.Sprintf("case when %s then %s else %s end", pred1, val1, elseVal)
+				compareRemoteExpr(t, conn, query)
+			}
+		}
+	}
+}
+
+// HACK: for CASE comparisons, the expression is supposed to decompose like this:
+//		CASE a WHEN b THEN bb WHEN c THEN cc ELSE d
+//			=> CASE WHEN a = b THEN bb WHEN a == c THEN cc ELSE d
+// See: https://dev.mysql.com/doc/refman/5.7/en/flow-control-functions.html#operator_case
+// However, MySQL does not seem to be using the real `=` operator for some of these comparisons
+// namely, numerical comparisons are coerced into an unsigned form when they shouldn't.
+// Example:
+//		SELECT -1 = 18446744073709551615
+//			=> 0
+//		SELECT -1 WHEN 18446744073709551615 THEN 1 ELSE 0 END
+//			=> 1
+// This does not happen for other types, which all follow the behavior of the `=` operator,
+// so we're going to assume this is a bug for now.
+func comparisonSkip(a, b string) bool {
+	if a == "-1" && b == "18446744073709551615" {
+		return true
+	}
+	if b == "-1" && a == "18446744073709551615" {
+		return true
+	}
+	return false
+}
+
+func TestCaseExprWithValue(t *testing.T) {
+	var conn = mysqlconn(t)
+	defer conn.Close()
+
+	var elements []string
+	elements = append(elements, bitwiseInputs...)
+	elements = append(elements, comparisonElements...)
+
+	for _, cmpbase := range elements {
+		for _, val1 := range elements {
+			if comparisonSkip(cmpbase, val1) {
+				continue
 			}
+			query := fmt.Sprintf("case %s when %s then 1 else 0 end", cmpbase, val1)
+			compareRemoteExpr(t, conn, query)
 		}
 	}
 }
diff --git a/go/vt/vtgate/evalengine/integration/fuzz_test.go b/go/vt/vtgate/evalengine/integration/fuzz_test.go
index fff7ed8401..e4b9b4ccec 100644
--- a/go/vt/vtgate/evalengine/integration/fuzz_test.go
+++ b/go/vt/vtgate/evalengine/integration/fuzz_test.go
@@ -27,6 +27,7 @@ import (
 	"testing"
 	"time"
 
+	"vitess.io/vitess/go/mysql/collations"
 	"vitess.io/vitess/go/sqltypes"
 	"vitess.io/vitess/go/vt/sqlparser"
 	"vitess.io/vitess/go/vt/vtgate/evalengine"
@@ -86,6 +87,7 @@ var knownErrors = []*regexp.Regexp{
 	regexp.MustCompile(`value is out of range in '(.*?)'`),
 	regexp.MustCompile(`Operand should contain (\d+) column\(s\)`),
 	regexp.MustCompile(`You have an error in your SQL syntax; (.*?)`),
+	regexp.MustCompile(`Cannot convert string '(.*?)' from \w+ to \w+`),
 }
 
 func errorsMatch(remote, local error) bool {
@@ -118,10 +120,10 @@ func safeEvaluate(query string) (evalengine.EvalResult, sqltypes.Type, error) {
 	local, err := func() (expr evalengine.Expr, err error) {
 		defer func() {
 			if r := recover(); r != nil {
-				err = fmt.Errorf("PANIC: %v", r)
+				err = fmt.Errorf("PANIC during translate: %v", r)
 			}
 		}()
-		expr, err = evalengine.TranslateEx(astExpr, evalengine.LookupDefaultCollation(255), *debugSimplify)
+		expr, err = evalengine.TranslateEx(astExpr, evalengine.LookupDefaultCollation(collations.CollationUtf8mb4ID), *debugSimplify)
 		return
 	}()
 
@@ -276,7 +278,7 @@ type mismatch struct {
 	localVal, remoteVal string
 }
 
-func compareResult(localErr, remoteErr error, localVal, remoteVal string) string {
+func compareResult(localErr, remoteErr error, localVal, remoteVal string, localCollation, remoteCollation collations.ID) string {
 	if localErr != nil {
 		if remoteErr == nil {
 			return fmt.Sprintf("%v; mysql response: %s", localErr, remoteVal)
@@ -296,13 +298,29 @@ func compareResult(localErr, remoteErr error, localVal, remoteVal string) string
 		return fmt.Sprintf("%v; mysql failed with: %s", localVal, remoteErr.Error())
 	}
 
+	var localCollationName string
+	var remoteCollationName string
+	env := collations.Local()
+	if coll := env.LookupByID(localCollation); coll != nil {
+		localCollationName = coll.Name()
+	}
+	if coll := env.LookupByID(remoteCollation); coll != nil {
+		remoteCollationName = coll.Name()
+	}
+
 	if localVal != remoteVal {
-		return fmt.Sprintf("different results: %s; mysql response: %s", localVal, remoteVal)
+		return fmt.Sprintf("different results: %s; mysql response: %s (local collation: %s; mysql collation: %s)",
+			localVal, remoteVal, localCollationName, remoteCollationName)
+	}
+	if localCollation != remoteCollation {
+		return fmt.Sprintf("different collations: %s; mysql response: %s (local result: %s; mysql result: %s)",
+			localCollationName, remoteCollationName, localVal, remoteVal,
+		)
 	}
 
 	return ""
 }
 
 func (cr *mismatch) Error() string {
-	return compareResult(cr.localErr, cr.remoteErr, cr.localVal, cr.remoteVal)
+	return compareResult(cr.localErr, cr.remoteErr, cr.localVal, cr.remoteVal, collations.Unknown, collations.Unknown)
 }
diff --git a/go/vt/vtgate/evalengine/mysql_test.go b/go/vt/vtgate/evalengine/mysql_test.go
index 5fa99cb2c4..6aee02bf7d 100644
--- a/go/vt/vtgate/evalengine/mysql_test.go
+++ b/go/vt/vtgate/evalengine/mysql_test.go
@@ -26,7 +26,6 @@ import (
 	"testing"
 
 	"vitess.io/vitess/go/mysql/collations"
-	"vitess.io/vitess/go/sqltypes"
 	"vitess.io/vitess/go/vt/sqlparser"
 )
 
@@ -72,14 +71,6 @@ func testSingle(t *testing.T, query string) (EvalResult, error) {
 	return EvalResult{}, err
 }
 
-func testSingleType(t *testing.T, query string) (sqltypes.Type, error) {
-	converted, err := convert(t, query, false)
-	if err == nil {
-		return EnvWithBindVars(nil, collations.CollationUtf8mb4ID).TypeOf(converted)
-	}
-	return 0, err
-}
-
 func TestMySQLGolden(t *testing.T) {
 	golden, _ := filepath.Glob("integration/testdata/*.json")
 	for _, gld := range golden {
@@ -135,7 +126,7 @@ func TestMySQLGolden(t *testing.T) {
 }
 
 func TestDebug1(t *testing.T) {
-	// Debug
-	eval, err := testSingleType(t, `SELECT --9223372036854775808`)
-	t.Logf("eval=%s err=%v", eval.String(), err) // want value=""
+	// Debu	g
+	eval, err := testSingle(t, `SELECT ('foo' collate utf8mb4_0900_as_cs) = 0xFF`)
+	t.Logf("eval=%s err=%v coll=%s", eval.String(), err, collations.Local().LookupByID(eval.Collation()).Name())
 }
diff --git a/go/vt/vtgate/evalengine/translate_test.go b/go/vt/vtgate/evalengine/translate_test.go
index 76f2bdcf69..5074d5c24d 100644
--- a/go/vt/vtgate/evalengine/translate_test.go
+++ b/go/vt/vtgate/evalengine/translate_test.go
@@ -88,6 +88,8 @@ func TestTranslateSimplification(t *testing.T) {
 		{"coalesce(NULL)", ok("COALESCE(NULL)"), ok("NULL")},
 		{"weight_string('foobar')", ok(`WEIGHT_STRING(VARCHAR("foobar"))`), ok(`VARBINARY("\x00F\x00O\x00O\x00B\x00A\x00R")`)},
 		{"weight_string('foobar' as char(12))", ok(`WEIGHT_STRING(VARCHAR("foobar") AS CHAR(12))`), ok(`VARBINARY("\x00F\x00O\x00O\x00B\x00A\x00R\x00 \x00 \x00 \x00 \x00 \x00 ")`)},
+		{"case when 1 = 1 then 2 else 3 end", ok("CASE WHEN INT64(1) = INT64(1) THEN INT64(2) ELSE INT64(3)"), ok("INT64(2)")},
+		{"case when null then 2 when 12 = 4 then 'ohnoes' else 42 end", ok(`CASE WHEN NULL THEN INT64(2) WHEN INT64(12) = INT64(4) THEN VARCHAR("ohnoes") ELSE INT64(42)`), ok(`VARCHAR("42")`)},
 	}
 
 	for _, tc := range testCases {
@@ -108,9 +110,7 @@ func TestTranslateSimplification(t *testing.T) {
 				}
 				return
 			}
-			if FormatExpr(converted) != tc.converted.literal {
-				t.Errorf("mismatch (simplify=false): got %s, expected %s", FormatExpr(converted), tc.converted.literal)
-			}
+			assert.Equal(t, tc.converted.literal, FormatExpr(converted))
 
 			simplified, err := TranslateEx(astExpr, LookupDefaultCollation(45), true)
 			if err != nil {
@@ -122,9 +122,7 @@ func TestTranslateSimplification(t *testing.T) {
 				}
 				return
 			}
-			if FormatExpr(simplified) != tc.simplified.literal {
-				t.Errorf("mismatch (simplify=true): got %s, expected %s", FormatExpr(simplified), tc.simplified.literal)
-			}
+			assert.Equal(t, tc.simplified.literal, FormatExpr(simplified))
 		})
 	}
 }
diff --git a/go/vt/vtgate/executor_select_test.go b/go/vt/vtgate/executor_select_test.go
index ccab352b40..9b5e1277a2 100644
--- a/go/vt/vtgate/executor_select_test.go
+++ b/go/vt/vtgate/executor_select_test.go
@@ -399,6 +399,49 @@ func TestSetSystemVariables(t *testing.T) {
 	utils.MustMatch(t, wantQueries, lookup.Queries)
 }
 
+func TestSetSystemVariablesWithReservedConnection(t *testing.T) {
+	executor, sbc1, _, _ := createExecutorEnv()
+	executor.normalize = true
+
+	session := NewAutocommitSession(&vtgatepb.Session{EnableSystemSettings: true, SystemVariables: map[string]string{}})
+
+	sbc1.SetResults([]*sqltypes.Result{{
+		Fields: []*querypb.Field{
+			{Name: "orig", Type: sqltypes.VarChar},
+			{Name: "new", Type: sqltypes.VarChar},
+		},
+		Rows: [][]sqltypes.Value{{
+			sqltypes.NewVarChar("only_full_group_by"),
+			sqltypes.NewVarChar(""),
+		}},
+	}})
+	_, err := executor.Execute(context.Background(), "TestSetStmt", session, "set @@sql_mode = ''", map[string]*querypb.BindVariable{})
+	require.NoError(t, err)
+
+	_, err = executor.Execute(context.Background(), "TestSelect", session, "select age, city from user group by age", map[string]*querypb.BindVariable{})
+	require.NoError(t, err)
+	require.True(t, session.InReservedConn())
+	wantQueries := []*querypb.BoundQuery{
+		{Sql: "select @@sql_mode orig, '' new"},
+		{Sql: "set @@sql_mode = ''"},
+		{Sql: "select age, city, weight_string(age) from `user` group by age, weight_string(age) order by age asc"},
+	}
+	utils.MustMatch(t, wantQueries, sbc1.Queries)
+
+	_, err = executor.Execute(context.Background(), "TestSelect", session, "select age, city+1 from user group by age", map[string]*querypb.BindVariable{})
+	require.NoError(t, err)
+	require.True(t, session.InReservedConn())
+	wantQueries = []*querypb.BoundQuery{
+		{Sql: "select @@sql_mode orig, '' new"},
+		{Sql: "set @@sql_mode = ''"},
+		{Sql: "select age, city, weight_string(age) from `user` group by age, weight_string(age) order by age asc"},
+		{Sql: "select age, city + :vtg1, weight_string(age) from `user` group by age, weight_string(age) order by age asc", BindVariables: map[string]*querypb.BindVariable{"vtg1": {Type: sqltypes.Int64, Value: []byte("1")}}},
+	}
+	utils.MustMatch(t, wantQueries, sbc1.Queries)
+	require.Equal(t, "''", session.SystemVariables["sql_mode"])
+	sbc1.Queries = nil
+}
+
 func TestCreateTableValidTimestamp(t *testing.T) {
 	executor, sbc1, _, _ := createExecutorEnv()
 	executor.normalize = true
diff --git a/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go b/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
index 905398df0b..dc1e40eeff 100644
--- a/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
+++ b/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
@@ -39,8 +39,7 @@ func TestQP(t *testing.T) {
 			sql: "select * from user",
 		},
 		{
-			sql:    "select 1, count(1) from user",
-			expErr: "In aggregated query without GROUP BY, expression of SELECT list contains nonaggregated column '1'; this is incompatible with sql_mode=only_full_group_by",
+			sql: "select 1, count(1) from user",
 		},
 		{
 			sql: "select max(id) from user",
@@ -50,8 +49,10 @@ func TestQP(t *testing.T) {
 			expErr: "aggregate functions take a single argument 'max(a, b)'",
 		},
 		{
-			sql:    "select 1, count(1) from user order by 1",
-			expErr: "In aggregated query without GROUP BY, expression of SELECT list contains nonaggregated column '1'; this is incompatible with sql_mode=only_full_group_by",
+			sql: "select 1, count(1) from user order by 1",
+			expOrder: []OrderBy{
+				{Inner: &sqlparser.Order{Expr: sqlparser.NewIntLiteral("1")}, WeightStrExpr: sqlparser.NewIntLiteral("1")},
+			},
 		},
 		{
 			sql: "select id from user order by col, id, 1",
@@ -91,7 +92,7 @@ func TestQP(t *testing.T) {
 			_, err = semantics.Analyze(sel, "", &semantics.FakeSI{})
 			require.NoError(t, err)
 
-			qp, err := CreateQPFromSelect(sel, semantics.EmptySemTable())
+			qp, err := CreateQPFromSelect(sel)
 			if tcase.expErr != "" {
 				require.Error(t, err)
 				require.Contains(t, err.Error(), tcase.expErr)
@@ -198,7 +199,7 @@ func TestQPSimplifiedExpr(t *testing.T) {
 			_, err = semantics.Analyze(sel, "", &semantics.FakeSI{})
 			require.NoError(t, err)
 
-			qp, err := CreateQPFromSelect(sel, semantics.EmptySemTable())
+			qp, err := CreateQPFromSelect(sel)
 			require.NoError(t, err)
 			require.Equal(t, tc.expected[1:], qp.toString())
 		})
diff --git a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
index 13df2feb0e..597a8e0263 100644
--- a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
@@ -944,7 +944,28 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 error: In aggregated query without GROUP BY, expression of SELECT list contains nonaggregated column 'id'; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select id, count(*) from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Scalar",
+    "Aggregates": "random(0) AS id, sum(1) AS count(*)",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select id, count(*) from `user` where 1 != 1",
+        "Query": "select id, count(*) from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter aggregate using distinctdistinct
 "select distinct col from user"
@@ -1692,7 +1713,31 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'd' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "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": "random(3) AS d, sum(4) AS count(*)",
+    "GroupBy": "(0|5), (1|6), (2|7)",
+    "ResultColumns": 5,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "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, weight_string(a), b, weight_string(b), c, weight_string(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, weight_string(a), b, weight_string(b), c, weight_string(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"
@@ -1721,7 +1766,31 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
     ]
   }
 }
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'd' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "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": "random(3) AS d, sum(4) AS count(*)",
+    "GroupBy": "(0|5), (1|6), (2|7)",
+    "ResultColumns": 5,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "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, weight_string(a), b, weight_string(b), c, weight_string(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, weight_string(a), b, weight_string(b), c, weight_string(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"
@@ -2043,7 +2112,7 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 error: In aggregated query without GROUP BY, expression of SELECT list contains nonaggregated column 'a'; this is incompatible with sql_mode=only_full_group_by
+Gen4 error: [BUG] unable to plan distinct query as the column is not projected: a
 
 # distinct and aggregate functions
 "select distinct a, count(*) from user group by a"
@@ -2113,7 +2182,31 @@ Gen4 error: In aggregated query without GROUP BY, expression of SELECT list cont
 # Group by invalid column number (code is duplicated from symab).
 "select id from user group by 1.1"
 "column number is not an int"
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select id from user group by 1.1",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "random(0) AS id",
+    "GroupBy": "1",
+    "ResultColumns": 1,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select id, 1.1 from `user` where 1 != 1 group by 1.1",
+        "OrderBy": "1 ASC",
+        "Query": "select id, 1.1 from `user` group by 1.1 order by 1.1 asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # Group by out of range column number (code is duplicated from symab).
 "select id from user group by 2"
diff --git a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
index e1f0703e80..d117545164 100644
--- a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
@@ -35,7 +35,31 @@
     ]
   }
 }
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, count(*) from user group by a order by b",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "random(1) AS b, sum(2) AS count(*)",
+    "GroupBy": "(0|3)",
+    "ResultColumns": 3,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select a, b, count(*), weight_string(a), weight_string(b) from `user` where 1 != 1 group by a, weight_string(a)",
+        "OrderBy": "(1|4) ASC, (0|3) ASC",
+        "Query": "select a, b, count(*), weight_string(a), weight_string(b) from `user` group by a, weight_string(a) order by b asc, a asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter aggregate order by references aggregate expression
 "select a, b, count(*) k from user group by a order by k"
@@ -71,9 +95,39 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
     ]
   }
 }
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, count(*) k from user group by a order by k",
+  "Instructions": {
+    "OperatorType": "Sort",
+    "Variant": "Memory",
+    "OrderBy": "2 ASC",
+    "ResultColumns": 3,
+    "Inputs": [
+      {
+        "OperatorType": "Aggregate",
+        "Variant": "Ordered",
+        "Aggregates": "random(1) AS b, sum(2) AS k",
+        "GroupBy": "(0|3)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select a, b, count(*) as k, weight_string(a) from `user` where 1 != 1 group by a, weight_string(a)",
+            "OrderBy": "(0|3) ASC",
+            "Query": "select a, b, count(*) as k, weight_string(a) from `user` group by a, weight_string(a) order by a asc",
+            "Table": "`user`"
+          }
+        ]
+      }
+    ]
+  }
+}
 
-# scatter aggregate order by references multiple non-group-by expressions
 "select a, b, count(*) k from user group by a order by b, a, k"
 {
   "QueryType": "SELECT",
@@ -109,7 +163,38 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
     ]
   }
 }
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, count(*) k from user group by a order by b, a, k",
+  "Instructions": {
+    "OperatorType": "Sort",
+    "Variant": "Memory",
+    "OrderBy": "1 ASC, (0|3) ASC, 2 ASC",
+    "ResultColumns": 3,
+    "Inputs": [
+      {
+        "OperatorType": "Aggregate",
+        "Variant": "Ordered",
+        "Aggregates": "random(1) AS b, sum(2) AS k",
+        "GroupBy": "(0|3)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select a, b, count(*) as k, weight_string(a) from `user` where 1 != 1 group by a, weight_string(a)",
+            "OrderBy": "(0|3) ASC",
+            "Query": "select a, b, count(*) as k, weight_string(a) from `user` group by a, weight_string(a) order by a asc",
+            "Table": "`user`"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # scatter aggregate with memory sort and limit
 "select a, b, count(*) k from user group by a order by k desc limit 10"
@@ -151,7 +236,44 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
     ]
   }
 }
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, count(*) k from user group by a order by k desc limit 10",
+  "Instructions": {
+    "OperatorType": "Limit",
+    "Count": "INT64(10)",
+    "Inputs": [
+      {
+        "OperatorType": "Sort",
+        "Variant": "Memory",
+        "OrderBy": "2 DESC",
+        "ResultColumns": 3,
+        "Inputs": [
+          {
+            "OperatorType": "Aggregate",
+            "Variant": "Ordered",
+            "Aggregates": "random(1) AS b, sum(2) AS k",
+            "GroupBy": "(0|3)",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "Scatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select a, b, count(*) as k, weight_string(a) from `user` where 1 != 1 group by a, weight_string(a)",
+                "OrderBy": "(0|3) ASC",
+                "Query": "select a, b, count(*) as k, weight_string(a) from `user` group by a, weight_string(a) order by a asc",
+                "Table": "`user`"
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # scatter aggregate with memory sort and order by number
 "select a, b, count(*) k from user group by a order by 1,3"
@@ -189,7 +311,38 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
     ]
   }
 }
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select a, b, count(*) k from user group by a order by 1,3",
+  "Instructions": {
+    "OperatorType": "Sort",
+    "Variant": "Memory",
+    "OrderBy": "(0|3) ASC, 2 ASC",
+    "ResultColumns": 3,
+    "Inputs": [
+      {
+        "OperatorType": "Aggregate",
+        "Variant": "Ordered",
+        "Aggregates": "random(1) AS b, sum(2) AS k",
+        "GroupBy": "(0|3)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select a, b, count(*) as k, weight_string(a) from `user` where 1 != 1 group by a, weight_string(a)",
+            "OrderBy": "(0|3) ASC",
+            "Query": "select a, b, count(*) as k, weight_string(a) from `user` group by a, weight_string(a) order by a asc",
+            "Table": "`user`"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # scatter aggregate with memory sort and order by number, reuse weight_string
 # we have to use a meaningless construct to test this. TODO: improve to do ordering once for textcol1
diff --git a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
index 97655e8236..af723e5177 100644
--- a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
@@ -1853,7 +1853,28 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 error: In aggregated query without GROUP BY, expression of SELECT list contains nonaggregated column 'num'; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select count(id), num from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Scalar",
+    "Aggregates": "sum(0) AS count(id), random(1) AS num",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select count(id), num from `user` where 1 != 1",
+        "Query": "select count(id), num from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # aggregation and non-aggregations column with order by
 "select count(id), num from user order by 2"
@@ -1888,7 +1909,30 @@ Gen4 error: In aggregated query without GROUP BY, expression of SELECT list cont
     ]
   }
 }
-Gen4 error: In aggregated query without GROUP BY, expression of SELECT list contains nonaggregated column 'num'; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select count(id), num from user order by 2",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Scalar",
+    "Aggregates": "sum(0) AS count(id), random(1) AS num",
+    "ResultColumns": 2,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select count(id), num, weight_string(num) from `user` where 1 != 1",
+        "OrderBy": "(1|2) ASC",
+        "Query": "select count(id), num, weight_string(num) from `user` order by num asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # aggregation and non-aggregations column with group by
 "select count(id), num from user group by 2"
@@ -2073,7 +2117,31 @@ Gen4 error: In aggregated query without GROUP BY, expression of SELECT list cont
 # Scatter order by and aggregation: order by column must reference column from select list
 "select col, count(*) from user group by col order by c1"
 "unsupported: memory sort: order by must reference a column in the select list: c1 asc"
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'c1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select col, count(*) from user group by col order by c1",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "sum(1) AS count(*), random(2)",
+    "GroupBy": "0",
+    "ResultColumns": 2,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select col, count(*), c1, weight_string(c1) from `user` where 1 != 1 group by col",
+        "OrderBy": "(2|3) ASC, 0 ASC",
+        "Query": "select col, count(*), c1, weight_string(c1) from `user` group by col order by c1 asc, col asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # Distinct with cross shard query
 "select distinct user.a from user join user_extra"
diff --git a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
index 03142ae620..c9a2cc9361 100644
--- a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
@@ -71,12 +71,36 @@ Gen4 error: cannot use column offsets in group statement when using `*`
 # group by must reference select list
 "select a from user group by b"
 "unsupported: in scatter query: group by column must reference column in SELECT list"
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select a from user group by b",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "random(0) AS a",
+    "GroupBy": "(1|2)",
+    "ResultColumns": 1,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select a, b, weight_string(b) from `user` where 1 != 1 group by b, weight_string(b)",
+        "OrderBy": "(1|2) ASC",
+        "Query": "select a, b, weight_string(b) from `user` group by b, weight_string(b) order by b asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # complex group by expression
 "select a from user group by a+1"
 "unsupported: in scatter query: only simple references allowed"
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+Gen4 error: unsupported: in scatter query: complex order by expression: a + 1
 
 # Complex aggregate expression on scatter
 "select 1+count(*) from user"
@@ -91,7 +115,30 @@ Gen4 error: aggregate functions take a single argument 'count(a, b)'
 # scatter aggregate symtab lookup error
 "select id, b as id, count(*) from user order by id"
 "ambiguous symbol reference: id"
-Gen4 error: In aggregated query without GROUP BY, expression of SELECT list contains nonaggregated column 'id'; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select id, b as id, count(*) from user order by id",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Scalar",
+    "Aggregates": "random(0) AS id, random(0) AS id, sum(2) AS count(*)",
+    "ResultColumns": 3,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select id, b as id, count(*), weight_string(b) from `user` where 1 != 1",
+        "OrderBy": "(1|3) ASC",
+        "Query": "select id, b as id, count(*), weight_string(b) from `user` order by id asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # scatter aggregate complex order by
 "select id from user group by id order by id+1"
@@ -101,7 +148,7 @@ Gen4 plan same as above
 # Scatter order by is complex with aggregates in select
 "select col, count(*) from user group by col order by col+1"
 "unsupported: in scatter query: complex order by expression: col + 1"
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col + 1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+Gen4 plan same as above
 
 # Aggregate detection (group_concat)
 "select group_concat(user.a) from user join user_extra"
@@ -111,12 +158,121 @@ Gen4 error: unsupported: in scatter query: complex aggregate expression
 # group by and ',' joins
 "select user.id from user, user_extra group by id"
 "unsupported: cross-shard query with aggregates"
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column '`user`.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select user.id from user, user_extra group by id",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "random(0) AS id",
+    "GroupBy": "(2|1)",
+    "ResultColumns": 1,
+    "Inputs": [
+      {
+        "OperatorType": "Projection",
+        "Expressions": [
+          "[COLUMN 2] * [COLUMN 3] as id",
+          "[COLUMN 1]",
+          "[COLUMN 0] as id"
+        ],
+        "Inputs": [
+          {
+            "OperatorType": "Join",
+            "Variant": "Join",
+            "JoinColumnIndexes": "L:0,L:1,L:0,R:0",
+            "TableName": "`user`_user_extra",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "Scatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select `user`.id, weight_string(id) from `user` where 1 != 1 group by id, weight_string(id)",
+                "OrderBy": "(0|1) ASC",
+                "Query": "select `user`.id, weight_string(id) from `user` group by id, weight_string(id) order by id asc",
+                "Table": "`user`"
+              },
+              {
+                "OperatorType": "Route",
+                "Variant": "Scatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select count(*) from user_extra where 1 != 1",
+                "Query": "select count(*) from user_extra",
+                "Table": "user_extra"
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # group by and ',' joins with condition
 "select user.col from user join user_extra on user_extra.col = user.col group by user.id"
 "unsupported: cross-shard query with aggregates"
-Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column '`user`.col' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select user.col from user join user_extra on user_extra.col = user.col group by user.id",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "random(0) AS col",
+    "GroupBy": "(2|1)",
+    "ResultColumns": 1,
+    "Inputs": [
+      {
+        "OperatorType": "Projection",
+        "Expressions": [
+          "[COLUMN 2] * [COLUMN 3] as col",
+          "[COLUMN 1]",
+          "[COLUMN 0] as id"
+        ],
+        "Inputs": [
+          {
+            "OperatorType": "Join",
+            "Variant": "Join",
+            "JoinColumnIndexes": "L:2,L:3,L:1,R:0",
+            "JoinVars": {
+              "user_col": 0
+            },
+            "TableName": "`user`_user_extra",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "Scatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select `user`.col, `user`.col, `user`.id, weight_string(`user`.id) from `user` where 1 != 1 group by `user`.col, `user`.id, weight_string(`user`.id)",
+                "OrderBy": "(2|3) ASC",
+                "Query": "select `user`.col, `user`.col, `user`.id, weight_string(`user`.id) from `user` group by `user`.col, `user`.id, weight_string(`user`.id) order by `user`.id asc",
+                "Table": "`user`"
+              },
+              {
+                "OperatorType": "Route",
+                "Variant": "Scatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select count(*) from user_extra where 1 != 1",
+                "Query": "select count(*) from user_extra where user_extra.col = :user_col",
+                "Table": "user_extra"
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # subqueries not supported in group by
 "select id from user group by id, (select id from user_extra)"
@@ -435,7 +591,28 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 error: In aggregated query without GROUP BY, expression of SELECT list contains nonaggregated column 'id'; this is incompatible with sql_mode=only_full_group_by
+{
+  "QueryType": "SELECT",
+  "Original": "select id, count(*) from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Scalar",
+    "Aggregates": "random(0) AS id, sum(1) AS count(*)",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select id, count(*) from `user` where 1 != 1",
+        "Query": "select id, count(*) from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # avg function on scatter query
 "select avg(id) from user"