Repository: vitessio/vitess
Pull request #10296 (2022-05-13 08:43:35)

Add additional normalization rules for schemadiff
This adds additional normalization rules for schemadiff to reduce churn around default options and to remove things that are not supported.

- `USING BTREE` on an index is the default, so we drop that if specified.
- `VISIBLE` is the default for columns & indexes, so this is dropped when specified.
- `ALGORITHM = undefined` is the default for a `CREATE VIEW` so this is dropped when specified.
- `SQL SECURITY DEFINER` is the default for a `CREATE VIEW` so this is dropped when specified.

## Related Issue(s)

Part of #10203

## Checklist

-   [ ] "Backport me!" label has been added if this change should be backported
-   [x] Tests were added or are not required
-   [x] Documentation was added or is not required

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.

Patch:
diff --git a/go/mysql/collations/coercion.go b/go/mysql/collations/coercion.go
index 227389a52b..d0408f3c7b 100644
--- a/go/mysql/collations/coercion.go
+++ b/go/mysql/collations/coercion.go
@@ -138,7 +138,7 @@ func checkCompatibleCollations(
 			}
 		}
 
-	case charset.Charset_utf8, charset.Charset_ucs2, charset.Charset_utf16, charset.Charset_utf16le:
+	case charset.Charset_utf8mb3, charset.Charset_ucs2, charset.Charset_utf16, charset.Charset_utf16le:
 		switch {
 		case leftCoercibility < rightCoercibility:
 			return true
@@ -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/env.go b/go/mysql/collations/env.go
index 7bbf58ce7b..2dbf7e5e4c 100644
--- a/go/mysql/collations/env.go
+++ b/go/mysql/collations/env.go
@@ -162,24 +162,27 @@ func makeEnv(version collver) *Environment {
 	}
 
 	for collid, vi := range globalVersionInfo {
-		var ourname string
-		for mask, name := range vi.alias {
-			if mask&version != 0 {
-				ourname = name
-				break
+		var ournames []string
+		for _, alias := range vi.alias {
+			if alias.mask&version != 0 {
+				ournames = append(ournames, alias.name)
 			}
 		}
-		if ourname == "" {
+		if len(ournames) == 0 {
 			continue
 		}
 
 		collation, ok := globalAllCollations[collid]
 		if !ok {
-			env.unsupported[ourname] = collid
+			for _, name := range ournames {
+				env.unsupported[name] = collid
+			}
 			continue
 		}
 
-		env.byName[ourname] = collation
+		for _, name := range ournames {
+			env.byName[name] = collation
+		}
 		env.byID[collid] = collation
 
 		csname := collation.Charset().Name()
@@ -200,6 +203,11 @@ func makeEnv(version collver) *Environment {
 			defaults.Binary = collation
 		}
 	}
+
+	for from, to := range version.charsetAliases() {
+		env.byCharset[from] = env.byCharset[to]
+	}
+
 	return env
 }
 
@@ -211,6 +219,15 @@ const (
 	CollationBinaryID  = 63
 )
 
+// CharsetAlias returns the internal charset name for the given charset.
+// For now, this only maps `utf8` to `utf8mb3`; in future versions of MySQL,
+// this mapping will change, so it's important to use this helper so that
+// Vitess code has a consistent mapping for the active collations environment.
+func (env *Environment) CharsetAlias(charset string) (alias string, ok bool) {
+	alias, ok = env.version.charsetAliases()[charset]
+	return
+}
+
 // DefaultConnectionCharset is the default charset that Vitess will use when negotiating a
 // charset in a MySQL connection handshake. Note that in this context, a 'charset' is equivalent
 // to a Collation ID, with the exception that it can only fit in 1 byte.
diff --git a/go/mysql/collations/internal/charset/charset.go b/go/mysql/collations/internal/charset/charset.go
index 8385ba7f0b..41b43cdbbe 100644
--- a/go/mysql/collations/internal/charset/charset.go
+++ b/go/mysql/collations/internal/charset/charset.go
@@ -40,7 +40,7 @@ type UnicodeMapping = eightbit.UnicodeMapping
 
 // Unicode encodings
 
-type Charset_utf8 = unicode.Charset_utf8mb3
+type Charset_utf8mb3 = unicode.Charset_utf8mb3
 type Charset_utf8mb4 = unicode.Charset_utf8mb4
 type Charset_utf16 = unicode.Charset_utf16be
 type Charset_utf16le = unicode.Charset_utf16le
@@ -75,7 +75,7 @@ func IsMultibyteByName(csname string) bool {
 
 func IsUnicode(charset Charset) bool {
 	switch charset.(type) {
-	case Charset_utf8, Charset_utf8mb4:
+	case Charset_utf8mb3, Charset_utf8mb4:
 		return true
 	case Charset_utf16, Charset_utf16le, Charset_ucs2:
 		return true
@@ -88,7 +88,7 @@ func IsUnicode(charset Charset) bool {
 
 func IsUnicodeByName(csname string) bool {
 	switch csname {
-	case "utf8", "utf8mb4", "utf16", "utf16le", "ucs2", "utf32":
+	case "utf8", "utf8mb3", "utf8mb4", "utf16", "utf16le", "ucs2", "utf32":
 		return true
 	default:
 		return false
diff --git a/go/mysql/collations/internal/charset/convert.go b/go/mysql/collations/internal/charset/convert.go
index 66dd093f09..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,7 +114,9 @@ func Convert(dst []byte, dstCharset Charset, src []byte, srcCharset Charset) ([]
 		return trans.Convert(dst, src, srcCharset)
 	}
 	switch srcCharset.(type) {
-	case Charset_utf8, Charset_utf8mb4:
+	case Charset_binary:
+		return ConvertFromBinary(dst, dstCharset, src)
+	case Charset_utf8mb3, Charset_utf8mb4:
 		return convertFastFromUTF8(dst, dstCharset, src)
 	default:
 		return convertSlow(dst, dstCharset, src, srcCharset)
@@ -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/mysql/collations/internal/charset/unicode/utf8.go b/go/mysql/collations/internal/charset/unicode/utf8.go
index fa92a71e1f..e33df1fcbe 100644
--- a/go/mysql/collations/internal/charset/unicode/utf8.go
+++ b/go/mysql/collations/internal/charset/unicode/utf8.go
@@ -101,7 +101,7 @@ var acceptRanges = [16]acceptRange{
 type Charset_utf8mb3 struct{}
 
 func (u Charset_utf8mb3) Name() string {
-	return "utf8"
+	return "utf8mb3"
 }
 
 func (u Charset_utf8mb3) IsSuperset(other types.Charset) bool {
diff --git a/go/mysql/collations/mysqldata.go b/go/mysql/collations/mysqldata.go
index 4394954c8f..636e4ef60b 100644
--- a/go/mysql/collations/mysqldata.go
+++ b/go/mysql/collations/mysqldata.go
@@ -2782,7 +2782,7 @@ func init() {
 		id:      0x21,
 		name:    "utf8_general_ci",
 		unicase: unicaseInfo_default,
-		charset: charset.Charset_utf8{},
+		charset: charset.Charset_utf8mb3{},
 	})
 	register(&Collation_unicode_general_ci{
 		id:      0x23,
@@ -3223,7 +3223,7 @@ func init() {
 	register(&Collation_unicode_bin{
 		id:      0x53,
 		name:    "utf8_bin",
-		charset: charset.Charset_utf8{},
+		charset: charset.Charset_utf8mb3{},
 	})
 	register(&Collation_multibyte{
 		id:      0x55,
@@ -3905,14 +3905,14 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_unicode_ci",
 		id:           0xc0,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		maxCodepoint: 0xFFFF,
 	})
 	register(&Collation_uca_legacy{
 		name:         "utf8_icelandic_ci",
 		id:           0xc1,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_icelandic_ci,
 		maxCodepoint: 0xFFFF,
@@ -3920,7 +3920,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_latvian_ci",
 		id:           0xc2,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_latvian_ci,
 		maxCodepoint: 0xFFFF,
@@ -3928,7 +3928,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_romanian_ci",
 		id:           0xc3,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_romanian_ci,
 		maxCodepoint: 0xFFFF,
@@ -3936,7 +3936,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_slovenian_ci",
 		id:           0xc4,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_slovenian_ci,
 		maxCodepoint: 0xFFFF,
@@ -3944,7 +3944,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_polish_ci",
 		id:           0xc5,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_polish_ci,
 		maxCodepoint: 0xFFFF,
@@ -3952,7 +3952,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_estonian_ci",
 		id:           0xc6,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_estonian_ci,
 		maxCodepoint: 0xFFFF,
@@ -3960,7 +3960,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_spanish_ci",
 		id:           0xc7,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_spanish_ci,
 		maxCodepoint: 0xFFFF,
@@ -3968,7 +3968,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_swedish_ci",
 		id:           0xc8,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_swedish_ci,
 		maxCodepoint: 0xFFFF,
@@ -3976,7 +3976,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_turkish_ci",
 		id:           0xc9,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_turkish_ci,
 		maxCodepoint: 0xFFFF,
@@ -3984,7 +3984,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_czech_ci",
 		id:           0xca,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_czech_ci,
 		contract:     contractor_utf16_czech_ci{},
@@ -3993,7 +3993,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_danish_ci",
 		id:           0xcb,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_danish_ci,
 		contract:     contractor_utf16_danish_ci{},
@@ -4002,7 +4002,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_lithuanian_ci",
 		id:           0xcc,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_lithuanian_ci,
 		contract:     contractor_utf16_lithuanian_ci{},
@@ -4011,7 +4011,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_slovak_ci",
 		id:           0xcd,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_slovak_ci,
 		contract:     contractor_utf16_czech_ci{},
@@ -4020,7 +4020,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_spanish2_ci",
 		id:           0xce,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_spanish_ci,
 		contract:     contractor_utf16_spanish2_ci{},
@@ -4029,7 +4029,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_roman_ci",
 		id:           0xcf,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_roman_ci,
 		maxCodepoint: 0xFFFF,
@@ -4037,7 +4037,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_persian_ci",
 		id:           0xd0,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_persian_ci,
 		maxCodepoint: 0xFFFF,
@@ -4045,7 +4045,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_esperanto_ci",
 		id:           0xd1,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_esperanto_ci,
 		maxCodepoint: 0xFFFF,
@@ -4053,7 +4053,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_hungarian_ci",
 		id:           0xd2,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_hungarian_ci,
 		maxCodepoint: 0xFFFF,
@@ -4061,7 +4061,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_sinhala_ci",
 		id:           0xd3,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_sinhala_ci,
 		maxCodepoint: 0xFFFF,
@@ -4069,7 +4069,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_german2_ci",
 		id:           0xd4,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_german2_ci,
 		maxCodepoint: 0xFFFF,
@@ -4077,7 +4077,7 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_croatian_ci",
 		id:           0xd5,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_croatian_ci,
 		contract:     contractor_utf16_croatian_ci{},
@@ -4086,14 +4086,14 @@ func init() {
 	register(&Collation_uca_legacy{
 		name:         "utf8_unicode_520_ci",
 		id:           0xd6,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca520,
 		maxCodepoint: 0x10FFFF,
 	})
 	register(&Collation_uca_legacy{
 		name:         "utf8_vietnamese_ci",
 		id:           0xd7,
-		charset:      charset.Charset_utf8{},
+		charset:      charset.Charset_utf8mb3{},
 		weights:      weightTable_uca400,
 		tailoring:    weightTailoring_utf16_vietnamese_ci,
 		maxCodepoint: 0xFFFF,
diff --git a/go/mysql/collations/mysqlversion.go b/go/mysql/collations/mysqlversion.go
index 6c955551fb..e76ac9110f 100644
--- a/go/mysql/collations/mysqlversion.go
+++ b/go/mysql/collations/mysqlversion.go
@@ -3,6 +3,10 @@
 package collations
 
 type collver byte
+type collalias struct {
+	mask collver
+	name string
+}
 
 const (
 	collverInvalid    collver = 0
@@ -37,384 +41,385 @@ func (v collver) String() string {
 		panic("invalid version identifier")
 	}
 }
+func (v collver) charsetAliases() map[string]string { return map[string]string{"utf8": "utf8mb3"} }
 
 var globalVersionInfo = map[ID]struct {
-	alias     map[collver]string
+	alias     []collalias
 	isdefault collver
 }{
-	1:    {alias: map[collver]string{0b01111111: "big5_chinese_ci"}, isdefault: 0b01111111},
-	2:    {alias: map[collver]string{0b01111111: "latin2_czech_cs"}, isdefault: 0b00000000},
-	3:    {alias: map[collver]string{0b01111111: "dec8_swedish_ci"}, isdefault: 0b01111111},
-	4:    {alias: map[collver]string{0b01111111: "cp850_general_ci"}, isdefault: 0b01111111},
-	5:    {alias: map[collver]string{0b01111111: "latin1_german1_ci"}, isdefault: 0b00000000},
-	6:    {alias: map[collver]string{0b01111111: "hp8_english_ci"}, isdefault: 0b01111111},
-	7:    {alias: map[collver]string{0b01111111: "koi8r_general_ci"}, isdefault: 0b01111111},
-	8:    {alias: map[collver]string{0b01111111: "latin1_swedish_ci"}, isdefault: 0b01111111},
-	9:    {alias: map[collver]string{0b01111111: "latin2_general_ci"}, isdefault: 0b01111111},
-	10:   {alias: map[collver]string{0b01111111: "swe7_swedish_ci"}, isdefault: 0b01111111},
-	11:   {alias: map[collver]string{0b01111111: "ascii_general_ci"}, isdefault: 0b01111111},
-	12:   {alias: map[collver]string{0b01111111: "ujis_japanese_ci"}, isdefault: 0b01111111},
-	13:   {alias: map[collver]string{0b01111111: "sjis_japanese_ci"}, isdefault: 0b01111111},
-	14:   {alias: map[collver]string{0b01111111: "cp1251_bulgarian_ci"}, isdefault: 0b00000000},
-	15:   {alias: map[collver]string{0b01111111: "latin1_danish_ci"}, isdefault: 0b00000000},
-	16:   {alias: map[collver]string{0b01111111: "hebrew_general_ci"}, isdefault: 0b01111111},
-	18:   {alias: map[collver]string{0b01111111: "tis620_thai_ci"}, isdefault: 0b01111111},
-	19:   {alias: map[collver]string{0b01111111: "euckr_korean_ci"}, isdefault: 0b01111111},
-	20:   {alias: map[collver]string{0b01111111: "latin7_estonian_cs"}, isdefault: 0b00000000},
-	21:   {alias: map[collver]string{0b01111111: "latin2_hungarian_ci"}, isdefault: 0b00000000},
-	22:   {alias: map[collver]string{0b01111111: "koi8u_general_ci"}, isdefault: 0b01111111},
-	23:   {alias: map[collver]string{0b01111111: "cp1251_ukrainian_ci"}, isdefault: 0b00000000},
-	24:   {alias: map[collver]string{0b01111111: "gb2312_chinese_ci"}, isdefault: 0b01111111},
-	25:   {alias: map[collver]string{0b01111111: "greek_general_ci"}, isdefault: 0b01111111},
-	26:   {alias: map[collver]string{0b01111111: "cp1250_general_ci"}, isdefault: 0b01111111},
-	27:   {alias: map[collver]string{0b01111111: "latin2_croatian_ci"}, isdefault: 0b00000000},
-	28:   {alias: map[collver]string{0b01111111: "gbk_chinese_ci"}, isdefault: 0b01111111},
-	29:   {alias: map[collver]string{0b01111111: "cp1257_lithuanian_ci"}, isdefault: 0b00000000},
-	30:   {alias: map[collver]string{0b01111111: "latin5_turkish_ci"}, isdefault: 0b01111111},
-	31:   {alias: map[collver]string{0b01111111: "latin1_german2_ci"}, isdefault: 0b00000000},
-	32:   {alias: map[collver]string{0b01111111: "armscii8_general_ci"}, isdefault: 0b01111111},
-	33:   {alias: map[collver]string{0b01111111: "utf8_general_ci"}, isdefault: 0b01111111},
-	34:   {alias: map[collver]string{0b01111111: "cp1250_czech_cs"}, isdefault: 0b00000000},
-	35:   {alias: map[collver]string{0b01111111: "ucs2_general_ci"}, isdefault: 0b01111111},
-	36:   {alias: map[collver]string{0b01111111: "cp866_general_ci"}, isdefault: 0b01111111},
-	37:   {alias: map[collver]string{0b01111111: "keybcs2_general_ci"}, isdefault: 0b01111111},
-	38:   {alias: map[collver]string{0b01111111: "macce_general_ci"}, isdefault: 0b01111111},
-	39:   {alias: map[collver]string{0b01111111: "macroman_general_ci"}, isdefault: 0b01111111},
-	40:   {alias: map[collver]string{0b01111111: "cp852_general_ci"}, isdefault: 0b01111111},
-	41:   {alias: map[collver]string{0b01111111: "latin7_general_ci"}, isdefault: 0b01111111},
-	42:   {alias: map[collver]string{0b01111111: "latin7_general_cs"}, isdefault: 0b00000000},
-	43:   {alias: map[collver]string{0b01111111: "macce_bin"}, isdefault: 0b00000000},
-	44:   {alias: map[collver]string{0b01111111: "cp1250_croatian_ci"}, isdefault: 0b00000000},
-	45:   {alias: map[collver]string{0b01111111: "utf8mb4_general_ci"}, isdefault: 0b00111111},
-	46:   {alias: map[collver]string{0b01111111: "utf8mb4_bin"}, isdefault: 0b00000000},
-	47:   {alias: map[collver]string{0b01111111: "latin1_bin"}, isdefault: 0b00000000},
-	48:   {alias: map[collver]string{0b01111111: "latin1_general_ci"}, isdefault: 0b00000000},
-	49:   {alias: map[collver]string{0b01111111: "latin1_general_cs"}, isdefault: 0b00000000},
-	50:   {alias: map[collver]string{0b01111111: "cp1251_bin"}, isdefault: 0b00000000},
-	51:   {alias: map[collver]string{0b01111111: "cp1251_general_ci"}, isdefault: 0b01111111},
-	52:   {alias: map[collver]string{0b01111111: "cp1251_general_cs"}, isdefault: 0b00000000},
-	53:   {alias: map[collver]string{0b01111111: "macroman_bin"}, isdefault: 0b00000000},
-	54:   {alias: map[collver]string{0b01111111: "utf16_general_ci"}, isdefault: 0b01111111},
-	55:   {alias: map[collver]string{0b01111111: "utf16_bin"}, isdefault: 0b00000000},
-	56:   {alias: map[collver]string{0b01111111: "utf16le_general_ci"}, isdefault: 0b01111111},
-	57:   {alias: map[collver]string{0b01111111: "cp1256_general_ci"}, isdefault: 0b01111111},
-	58:   {alias: map[collver]string{0b01111111: "cp1257_bin"}, isdefault: 0b00000000},
-	59:   {alias: map[collver]string{0b01111111: "cp1257_general_ci"}, isdefault: 0b01111111},
-	60:   {alias: map[collver]string{0b01111111: "utf32_general_ci"}, isdefault: 0b01111111},
-	61:   {alias: map[collver]string{0b01111111: "utf32_bin"}, isdefault: 0b00000000},
-	62:   {alias: map[collver]string{0b01111111: "utf16le_bin"}, isdefault: 0b00000000},
-	63:   {alias: map[collver]string{0b01111111: "binary"}, isdefault: 0b01111111},
-	64:   {alias: map[collver]string{0b01111111: "armscii8_bin"}, isdefault: 0b00000000},
-	65:   {alias: map[collver]string{0b01111111: "ascii_bin"}, isdefault: 0b00000000},
-	66:   {alias: map[collver]string{0b01111111: "cp1250_bin"}, isdefault: 0b00000000},
-	67:   {alias: map[collver]string{0b01111111: "cp1256_bin"}, isdefault: 0b00000000},
-	68:   {alias: map[collver]string{0b01111111: "cp866_bin"}, isdefault: 0b00000000},
-	69:   {alias: map[collver]string{0b01111111: "dec8_bin"}, isdefault: 0b00000000},
-	70:   {alias: map[collver]string{0b01111111: "greek_bin"}, isdefault: 0b00000000},
-	71:   {alias: map[collver]string{0b01111111: "hebrew_bin"}, isdefault: 0b00000000},
-	72:   {alias: map[collver]string{0b01111111: "hp8_bin"}, isdefault: 0b00000000},
-	73:   {alias: map[collver]string{0b01111111: "keybcs2_bin"}, isdefault: 0b00000000},
-	74:   {alias: map[collver]string{0b01111111: "koi8r_bin"}, isdefault: 0b00000000},
-	75:   {alias: map[collver]string{0b01111111: "koi8u_bin"}, isdefault: 0b00000000},
-	76:   {alias: map[collver]string{0b01000000: "utf8_tolower_ci"}, isdefault: 0b00000000},
-	77:   {alias: map[collver]string{0b01111111: "latin2_bin"}, isdefault: 0b00000000},
-	78:   {alias: map[collver]string{0b01111111: "latin5_bin"}, isdefault: 0b00000000},
-	79:   {alias: map[collver]string{0b01111111: "latin7_bin"}, isdefault: 0b00000000},
-	80:   {alias: map[collver]string{0b01111111: "cp850_bin"}, isdefault: 0b00000000},
-	81:   {alias: map[collver]string{0b01111111: "cp852_bin"}, isdefault: 0b00000000},
-	82:   {alias: map[collver]string{0b01111111: "swe7_bin"}, isdefault: 0b00000000},
-	83:   {alias: map[collver]string{0b01111111: "utf8_bin"}, isdefault: 0b00000000},
-	84:   {alias: map[collver]string{0b01111111: "big5_bin"}, isdefault: 0b00000000},
-	85:   {alias: map[collver]string{0b01111111: "euckr_bin"}, isdefault: 0b00000000},
-	86:   {alias: map[collver]string{0b01111111: "gb2312_bin"}, isdefault: 0b00000000},
-	87:   {alias: map[collver]string{0b01111111: "gbk_bin"}, isdefault: 0b00000000},
-	88:   {alias: map[collver]string{0b01111111: "sjis_bin"}, isdefault: 0b00000000},
-	89:   {alias: map[collver]string{0b01111111: "tis620_bin"}, isdefault: 0b00000000},
-	90:   {alias: map[collver]string{0b01111111: "ucs2_bin"}, isdefault: 0b00000000},
-	91:   {alias: map[collver]string{0b01111111: "ujis_bin"}, isdefault: 0b00000000},
-	92:   {alias: map[collver]string{0b01111111: "geostd8_general_ci"}, isdefault: 0b01111111},
-	93:   {alias: map[collver]string{0b01111111: "geostd8_bin"}, isdefault: 0b00000000},
-	94:   {alias: map[collver]string{0b01111111: "latin1_spanish_ci"}, isdefault: 0b00000000},
-	95:   {alias: map[collver]string{0b01111111: "cp932_japanese_ci"}, isdefault: 0b01111111},
-	96:   {alias: map[collver]string{0b01111111: "cp932_bin"}, isdefault: 0b00000000},
-	97:   {alias: map[collver]string{0b01111111: "eucjpms_japanese_ci"}, isdefault: 0b01111111},
-	98:   {alias: map[collver]string{0b01111111: "eucjpms_bin"}, isdefault: 0b00000000},
-	99:   {alias: map[collver]string{0b01111111: "cp1250_polish_ci"}, isdefault: 0b00000000},
-	101:  {alias: map[collver]string{0b01111111: "utf16_unicode_ci"}, isdefault: 0b00000000},
-	102:  {alias: map[collver]string{0b01111111: "utf16_icelandic_ci"}, isdefault: 0b00000000},
-	103:  {alias: map[collver]string{0b01111111: "utf16_latvian_ci"}, isdefault: 0b00000000},
-	104:  {alias: map[collver]string{0b01111111: "utf16_romanian_ci"}, isdefault: 0b00000000},
-	105:  {alias: map[collver]string{0b01111111: "utf16_slovenian_ci"}, isdefault: 0b00000000},
-	106:  {alias: map[collver]string{0b01111111: "utf16_polish_ci"}, isdefault: 0b00000000},
-	107:  {alias: map[collver]string{0b01111111: "utf16_estonian_ci"}, isdefault: 0b00000000},
-	108:  {alias: map[collver]string{0b01111111: "utf16_spanish_ci"}, isdefault: 0b00000000},
-	109:  {alias: map[collver]string{0b01111111: "utf16_swedish_ci"}, isdefault: 0b00000000},
-	110:  {alias: map[collver]string{0b01111111: "utf16_turkish_ci"}, isdefault: 0b00000000},
-	111:  {alias: map[collver]string{0b01111111: "utf16_czech_ci"}, isdefault: 0b00000000},
-	112:  {alias: map[collver]string{0b01111111: "utf16_danish_ci"}, isdefault: 0b00000000},
-	113:  {alias: map[collver]string{0b01111111: "utf16_lithuanian_ci"}, isdefault: 0b00000000},
-	114:  {alias: map[collver]string{0b01111111: "utf16_slovak_ci"}, isdefault: 0b00000000},
-	115:  {alias: map[collver]string{0b01111111: "utf16_spanish2_ci"}, isdefault: 0b00000000},
-	116:  {alias: map[collver]string{0b01111111: "utf16_roman_ci"}, isdefault: 0b00000000},
-	117:  {alias: map[collver]string{0b01111111: "utf16_persian_ci"}, isdefault: 0b00000000},
-	118:  {alias: map[collver]string{0b01111111: "utf16_esperanto_ci"}, isdefault: 0b00000000},
-	119:  {alias: map[collver]string{0b01111111: "utf16_hungarian_ci"}, isdefault: 0b00000000},
-	120:  {alias: map[collver]string{0b01111111: "utf16_sinhala_ci"}, isdefault: 0b00000000},
-	121:  {alias: map[collver]string{0b01111111: "utf16_german2_ci"}, isdefault: 0b00000000},
-	122:  {alias: map[collver]string{0b01110000: "utf16_croatian_ci", 0b00001111: "utf16_croatian_mysql561_ci"}, isdefault: 0b00000000},
-	123:  {alias: map[collver]string{0b01111111: "utf16_unicode_520_ci"}, isdefault: 0b00000000},
-	124:  {alias: map[collver]string{0b01111111: "utf16_vietnamese_ci"}, isdefault: 0b00000000},
-	128:  {alias: map[collver]string{0b01111111: "ucs2_unicode_ci"}, isdefault: 0b00000000},
-	129:  {alias: map[collver]string{0b01111111: "ucs2_icelandic_ci"}, isdefault: 0b00000000},
-	130:  {alias: map[collver]string{0b01111111: "ucs2_latvian_ci"}, isdefault: 0b00000000},
-	131:  {alias: map[collver]string{0b01111111: "ucs2_romanian_ci"}, isdefault: 0b00000000},
-	132:  {alias: map[collver]string{0b01111111: "ucs2_slovenian_ci"}, isdefault: 0b00000000},
-	133:  {alias: map[collver]string{0b01111111: "ucs2_polish_ci"}, isdefault: 0b00000000},
-	134:  {alias: map[collver]string{0b01111111: "ucs2_estonian_ci"}, isdefault: 0b00000000},
-	135:  {alias: map[collver]string{0b01111111: "ucs2_spanish_ci"}, isdefault: 0b00000000},
-	136:  {alias: map[collver]string{0b01111111: "ucs2_swedish_ci"}, isdefault: 0b00000000},
-	137:  {alias: map[collver]string{0b01111111: "ucs2_turkish_ci"}, isdefault: 0b00000000},
-	138:  {alias: map[collver]string{0b01111111: "ucs2_czech_ci"}, isdefault: 0b00000000},
-	139:  {alias: map[collver]string{0b01111111: "ucs2_danish_ci"}, isdefault: 0b00000000},
-	140:  {alias: map[collver]string{0b01111111: "ucs2_lithuanian_ci"}, isdefault: 0b00000000},
-	141:  {alias: map[collver]string{0b01111111: "ucs2_slovak_ci"}, isdefault: 0b00000000},
-	142:  {alias: map[collver]string{0b01111111: "ucs2_spanish2_ci"}, isdefault: 0b00000000},
-	143:  {alias: map[collver]string{0b01111111: "ucs2_roman_ci"}, isdefault: 0b00000000},
-	144:  {alias: map[collver]string{0b01111111: "ucs2_persian_ci"}, isdefault: 0b00000000},
-	145:  {alias: map[collver]string{0b01111111: "ucs2_esperanto_ci"}, isdefault: 0b00000000},
-	146:  {alias: map[collver]string{0b01111111: "ucs2_hungarian_ci"}, isdefault: 0b00000000},
-	147:  {alias: map[collver]string{0b01111111: "ucs2_sinhala_ci"}, isdefault: 0b00000000},
-	148:  {alias: map[collver]string{0b01111111: "ucs2_german2_ci"}, isdefault: 0b00000000},
-	149:  {alias: map[collver]string{0b01110000: "ucs2_croatian_ci", 0b00001111: "ucs2_croatian_mysql561_ci"}, isdefault: 0b00000000},
-	150:  {alias: map[collver]string{0b01111111: "ucs2_unicode_520_ci"}, isdefault: 0b00000000},
-	151:  {alias: map[collver]string{0b01111111: "ucs2_vietnamese_ci"}, isdefault: 0b00000000},
-	159:  {alias: map[collver]string{0b01111111: "ucs2_general_mysql500_ci"}, isdefault: 0b00000000},
-	160:  {alias: map[collver]string{0b01111111: "utf32_unicode_ci"}, isdefault: 0b00000000},
-	161:  {alias: map[collver]string{0b01111111: "utf32_icelandic_ci"}, isdefault: 0b00000000},
-	162:  {alias: map[collver]string{0b01111111: "utf32_latvian_ci"}, isdefault: 0b00000000},
-	163:  {alias: map[collver]string{0b01111111: "utf32_romanian_ci"}, isdefault: 0b00000000},
-	164:  {alias: map[collver]string{0b01111111: "utf32_slovenian_ci"}, isdefault: 0b00000000},
-	165:  {alias: map[collver]string{0b01111111: "utf32_polish_ci"}, isdefault: 0b00000000},
-	166:  {alias: map[collver]string{0b01111111: "utf32_estonian_ci"}, isdefault: 0b00000000},
-	167:  {alias: map[collver]string{0b01111111: "utf32_spanish_ci"}, isdefault: 0b00000000},
-	168:  {alias: map[collver]string{0b01111111: "utf32_swedish_ci"}, isdefault: 0b00000000},
-	169:  {alias: map[collver]string{0b01111111: "utf32_turkish_ci"}, isdefault: 0b00000000},
-	170:  {alias: map[collver]string{0b01111111: "utf32_czech_ci"}, isdefault: 0b00000000},
-	171:  {alias: map[collver]string{0b01111111: "utf32_danish_ci"}, isdefault: 0b00000000},
-	172:  {alias: map[collver]string{0b01111111: "utf32_lithuanian_ci"}, isdefault: 0b00000000},
-	173:  {alias: map[collver]string{0b01111111: "utf32_slovak_ci"}, isdefault: 0b00000000},
-	174:  {alias: map[collver]string{0b01111111: "utf32_spanish2_ci"}, isdefault: 0b00000000},
-	175:  {alias: map[collver]string{0b01111111: "utf32_roman_ci"}, isdefault: 0b00000000},
-	176:  {alias: map[collver]string{0b01111111: "utf32_persian_ci"}, isdefault: 0b00000000},
-	177:  {alias: map[collver]string{0b01111111: "utf32_esperanto_ci"}, isdefault: 0b00000000},
-	178:  {alias: map[collver]string{0b01111111: "utf32_hungarian_ci"}, isdefault: 0b00000000},
-	179:  {alias: map[collver]string{0b01111111: "utf32_sinhala_ci"}, isdefault: 0b00000000},
-	180:  {alias: map[collver]string{0b01111111: "utf32_german2_ci"}, isdefault: 0b00000000},
-	181:  {alias: map[collver]string{0b01110000: "utf32_croatian_ci", 0b00001111: "utf32_croatian_mysql561_ci"}, isdefault: 0b00000000},
-	182:  {alias: map[collver]string{0b01111111: "utf32_unicode_520_ci"}, isdefault: 0b00000000},
-	183:  {alias: map[collver]string{0b01111111: "utf32_vietnamese_ci"}, isdefault: 0b00000000},
-	192:  {alias: map[collver]string{0b01111111: "utf8_unicode_ci"}, isdefault: 0b00000000},
-	193:  {alias: map[collver]string{0b01111111: "utf8_icelandic_ci"}, isdefault: 0b00000000},
-	194:  {alias: map[collver]string{0b01111111: "utf8_latvian_ci"}, isdefault: 0b00000000},
-	195:  {alias: map[collver]string{0b01111111: "utf8_romanian_ci"}, isdefault: 0b00000000},
-	196:  {alias: map[collver]string{0b01111111: "utf8_slovenian_ci"}, isdefault: 0b00000000},
-	197:  {alias: map[collver]string{0b01111111: "utf8_polish_ci"}, isdefault: 0b00000000},
-	198:  {alias: map[collver]string{0b01111111: "utf8_estonian_ci"}, isdefault: 0b00000000},
-	199:  {alias: map[collver]string{0b01111111: "utf8_spanish_ci"}, isdefault: 0b00000000},
-	200:  {alias: map[collver]string{0b01111111: "utf8_swedish_ci"}, isdefault: 0b00000000},
-	201:  {alias: map[collver]string{0b01111111: "utf8_turkish_ci"}, isdefault: 0b00000000},
-	202:  {alias: map[collver]string{0b01111111: "utf8_czech_ci"}, isdefault: 0b00000000},
-	203:  {alias: map[collver]string{0b01111111: "utf8_danish_ci"}, isdefault: 0b00000000},
-	204:  {alias: map[collver]string{0b01111111: "utf8_lithuanian_ci"}, isdefault: 0b00000000},
-	205:  {alias: map[collver]string{0b01111111: "utf8_slovak_ci"}, isdefault: 0b00000000},
-	206:  {alias: map[collver]string{0b01111111: "utf8_spanish2_ci"}, isdefault: 0b00000000},
-	207:  {alias: map[collver]string{0b01111111: "utf8_roman_ci"}, isdefault: 0b00000000},
-	208:  {alias: map[collver]string{0b01111111: "utf8_persian_ci"}, isdefault: 0b00000000},
-	209:  {alias: map[collver]string{0b01111111: "utf8_esperanto_ci"}, isdefault: 0b00000000},
-	210:  {alias: map[collver]string{0b01111111: "utf8_hungarian_ci"}, isdefault: 0b00000000},
-	211:  {alias: map[collver]string{0b01111111: "utf8_sinhala_ci"}, isdefault: 0b00000000},
-	212:  {alias: map[collver]string{0b01111111: "utf8_german2_ci"}, isdefault: 0b00000000},
-	213:  {alias: map[collver]string{0b01110000: "utf8_croatian_ci", 0b00001111: "utf8_croatian_mysql561_ci"}, isdefault: 0b00000000},
-	214:  {alias: map[collver]string{0b01111111: "utf8_unicode_520_ci"}, isdefault: 0b00000000},
-	215:  {alias: map[collver]string{0b01111111: "utf8_vietnamese_ci"}, isdefault: 0b00000000},
-	223:  {alias: map[collver]string{0b01111111: "utf8_general_mysql500_ci"}, isdefault: 0b00000000},
-	224:  {alias: map[collver]string{0b01111111: "utf8mb4_unicode_ci"}, isdefault: 0b00000000},
-	225:  {alias: map[collver]string{0b01111111: "utf8mb4_icelandic_ci"}, isdefault: 0b00000000},
-	226:  {alias: map[collver]string{0b01111111: "utf8mb4_latvian_ci"}, isdefault: 0b00000000},
-	227:  {alias: map[collver]string{0b01111111: "utf8mb4_romanian_ci"}, isdefault: 0b00000000},
-	228:  {alias: map[collver]string{0b01111111: "utf8mb4_slovenian_ci"}, isdefault: 0b00000000},
-	229:  {alias: map[collver]string{0b01111111: "utf8mb4_polish_ci"}, isdefault: 0b00000000},
-	230:  {alias: map[collver]string{0b01111111: "utf8mb4_estonian_ci"}, isdefault: 0b00000000},
-	231:  {alias: map[collver]string{0b01111111: "utf8mb4_spanish_ci"}, isdefault: 0b00000000},
-	232:  {alias: map[collver]string{0b01111111: "utf8mb4_swedish_ci"}, isdefault: 0b00000000},
-	233:  {alias: map[collver]string{0b01111111: "utf8mb4_turkish_ci"}, isdefault: 0b00000000},
-	234:  {alias: map[collver]string{0b01111111: "utf8mb4_czech_ci"}, isdefault: 0b00000000},
-	235:  {alias: map[collver]string{0b01111111: "utf8mb4_danish_ci"}, isdefault: 0b00000000},
-	236:  {alias: map[collver]string{0b01111111: "utf8mb4_lithuanian_ci"}, isdefault: 0b00000000},
-	237:  {alias: map[collver]string{0b01111111: "utf8mb4_slovak_ci"}, isdefault: 0b00000000},
-	238:  {alias: map[collver]string{0b01111111: "utf8mb4_spanish2_ci"}, isdefault: 0b00000000},
-	239:  {alias: map[collver]string{0b01111111: "utf8mb4_roman_ci"}, isdefault: 0b00000000},
-	240:  {alias: map[collver]string{0b01111111: "utf8mb4_persian_ci"}, isdefault: 0b00000000},
-	241:  {alias: map[collver]string{0b01111111: "utf8mb4_esperanto_ci"}, isdefault: 0b00000000},
-	242:  {alias: map[collver]string{0b01111111: "utf8mb4_hungarian_ci"}, isdefault: 0b00000000},
-	243:  {alias: map[collver]string{0b01111111: "utf8mb4_sinhala_ci"}, isdefault: 0b00000000},
-	244:  {alias: map[collver]string{0b01111111: "utf8mb4_german2_ci"}, isdefault: 0b00000000},
-	245:  {alias: map[collver]string{0b01110000: "utf8mb4_croatian_ci", 0b00001111: "utf8mb4_croatian_mysql561_ci"}, isdefault: 0b00000000},
-	246:  {alias: map[collver]string{0b01111111: "utf8mb4_unicode_520_ci"}, isdefault: 0b00000000},
-	247:  {alias: map[collver]string{0b01111111: "utf8mb4_vietnamese_ci"}, isdefault: 0b00000000},
-	248:  {alias: map[collver]string{0b01100000: "gb18030_chinese_ci"}, isdefault: 0b01100000},
-	249:  {alias: map[collver]string{0b01100000: "gb18030_bin"}, isdefault: 0b00000000},
-	250:  {alias: map[collver]string{0b01100000: "gb18030_unicode_520_ci"}, isdefault: 0b00000000},
-	255:  {alias: map[collver]string{0b01000000: "utf8mb4_0900_ai_ci"}, isdefault: 0b01000000},
-	256:  {alias: map[collver]string{0b01000000: "utf8mb4_de_pb_0900_ai_ci"}, isdefault: 0b00000000},
-	257:  {alias: map[collver]string{0b01000000: "utf8mb4_is_0900_ai_ci"}, isdefault: 0b00000000},
-	258:  {alias: map[collver]string{0b01000000: "utf8mb4_lv_0900_ai_ci"}, isdefault: 0b00000000},
-	259:  {alias: map[collver]string{0b01000000: "utf8mb4_ro_0900_ai_ci"}, isdefault: 0b00000000},
-	260:  {alias: map[collver]string{0b01000000: "utf8mb4_sl_0900_ai_ci"}, isdefault: 0b00000000},
-	261:  {alias: map[collver]string{0b01000000: "utf8mb4_pl_0900_ai_ci"}, isdefault: 0b00000000},
-	262:  {alias: map[collver]string{0b01000000: "utf8mb4_et_0900_ai_ci"}, isdefault: 0b00000000},
-	263:  {alias: map[collver]string{0b01000000: "utf8mb4_es_0900_ai_ci"}, isdefault: 0b00000000},
-	264:  {alias: map[collver]string{0b01000000: "utf8mb4_sv_0900_ai_ci"}, isdefault: 0b00000000},
-	265:  {alias: map[collver]string{0b01000000: "utf8mb4_tr_0900_ai_ci"}, isdefault: 0b00000000},
-	266:  {alias: map[collver]string{0b01000000: "utf8mb4_cs_0900_ai_ci"}, isdefault: 0b00000000},
-	267:  {alias: map[collver]string{0b01000000: "utf8mb4_da_0900_ai_ci"}, isdefault: 0b00000000},
-	268:  {alias: map[collver]string{0b01000000: "utf8mb4_lt_0900_ai_ci"}, isdefault: 0b00000000},
-	269:  {alias: map[collver]string{0b01000000: "utf8mb4_sk_0900_ai_ci"}, isdefault: 0b00000000},
-	270:  {alias: map[collver]string{0b01000000: "utf8mb4_es_trad_0900_ai_ci"}, isdefault: 0b00000000},
-	271:  {alias: map[collver]string{0b01000000: "utf8mb4_la_0900_ai_ci"}, isdefault: 0b00000000},
-	273:  {alias: map[collver]string{0b01000000: "utf8mb4_eo_0900_ai_ci"}, isdefault: 0b00000000},
-	274:  {alias: map[collver]string{0b01000000: "utf8mb4_hu_0900_ai_ci"}, isdefault: 0b00000000},
-	275:  {alias: map[collver]string{0b01000000: "utf8mb4_hr_0900_ai_ci"}, isdefault: 0b00000000},
-	277:  {alias: map[collver]string{0b01000000: "utf8mb4_vi_0900_ai_ci"}, isdefault: 0b00000000},
-	278:  {alias: map[collver]string{0b01000000: "utf8mb4_0900_as_cs"}, isdefault: 0b00000000},
-	279:  {alias: map[collver]string{0b01000000: "utf8mb4_de_pb_0900_as_cs"}, isdefault: 0b00000000},
-	280:  {alias: map[collver]string{0b01000000: "utf8mb4_is_0900_as_cs"}, isdefault: 0b00000000},
-	281:  {alias: map[collver]string{0b01000000: "utf8mb4_lv_0900_as_cs"}, isdefault: 0b00000000},
-	282:  {alias: map[collver]string{0b01000000: "utf8mb4_ro_0900_as_cs"}, isdefault: 0b00000000},
-	283:  {alias: map[collver]string{0b01000000: "utf8mb4_sl_0900_as_cs"}, isdefault: 0b00000000},
-	284:  {alias: map[collver]string{0b01000000: "utf8mb4_pl_0900_as_cs"}, isdefault: 0b00000000},
-	285:  {alias: map[collver]string{0b01000000: "utf8mb4_et_0900_as_cs"}, isdefault: 0b00000000},
-	286:  {alias: map[collver]string{0b01000000: "utf8mb4_es_0900_as_cs"}, isdefault: 0b00000000},
-	287:  {alias: map[collver]string{0b01000000: "utf8mb4_sv_0900_as_cs"}, isdefault: 0b00000000},
-	288:  {alias: map[collver]string{0b01000000: "utf8mb4_tr_0900_as_cs"}, isdefault: 0b00000000},
-	289:  {alias: map[collver]string{0b01000000: "utf8mb4_cs_0900_as_cs"}, isdefault: 0b00000000},
-	290:  {alias: map[collver]string{0b01000000: "utf8mb4_da_0900_as_cs"}, isdefault: 0b00000000},
-	291:  {alias: map[collver]string{0b01000000: "utf8mb4_lt_0900_as_cs"}, isdefault: 0b00000000},
-	292:  {alias: map[collver]string{0b01000000: "utf8mb4_sk_0900_as_cs"}, isdefault: 0b00000000},
-	293:  {alias: map[collver]string{0b01000000: "utf8mb4_es_trad_0900_as_cs"}, isdefault: 0b00000000},
-	294:  {alias: map[collver]string{0b01000000: "utf8mb4_la_0900_as_cs"}, isdefault: 0b00000000},
-	296:  {alias: map[collver]string{0b01000000: "utf8mb4_eo_0900_as_cs"}, isdefault: 0b00000000},
-	297:  {alias: map[collver]string{0b01000000: "utf8mb4_hu_0900_as_cs"}, isdefault: 0b00000000},
-	298:  {alias: map[collver]string{0b01000000: "utf8mb4_hr_0900_as_cs"}, isdefault: 0b00000000},
-	300:  {alias: map[collver]string{0b01000000: "utf8mb4_vi_0900_as_cs"}, isdefault: 0b00000000},
-	303:  {alias: map[collver]string{0b01000000: "utf8mb4_ja_0900_as_cs"}, isdefault: 0b00000000},
-	304:  {alias: map[collver]string{0b01000000: "utf8mb4_ja_0900_as_cs_ks"}, isdefault: 0b00000000},
-	305:  {alias: map[collver]string{0b01000000: "utf8mb4_0900_as_ci"}, isdefault: 0b00000000},
-	306:  {alias: map[collver]string{0b01000000: "utf8mb4_ru_0900_ai_ci"}, isdefault: 0b00000000},
-	307:  {alias: map[collver]string{0b01000000: "utf8mb4_ru_0900_as_cs"}, isdefault: 0b00000000},
-	308:  {alias: map[collver]string{0b01000000: "utf8mb4_zh_0900_as_cs"}, isdefault: 0b00000000},
-	309:  {alias: map[collver]string{0b01000000: "utf8mb4_0900_bin"}, isdefault: 0b00000000},
-	576:  {alias: map[collver]string{0b00001111: "utf8_croatian_ci"}, isdefault: 0b00000000},
-	577:  {alias: map[collver]string{0b00001111: "utf8_myanmar_ci"}, isdefault: 0b00000000},
-	578:  {alias: map[collver]string{0b00001110: "utf8_thai_520_w2"}, isdefault: 0b00000000},
-	608:  {alias: map[collver]string{0b00001111: "utf8mb4_croatian_ci"}, isdefault: 0b00000000},
-	609:  {alias: map[collver]string{0b00001111: "utf8mb4_myanmar_ci"}, isdefault: 0b00000000},
-	610:  {alias: map[collver]string{0b00001110: "utf8mb4_thai_520_w2"}, isdefault: 0b00000000},
-	640:  {alias: map[collver]string{0b00001111: "ucs2_croatian_ci"}, isdefault: 0b00000000},
-	641:  {alias: map[collver]string{0b00001111: "ucs2_myanmar_ci"}, isdefault: 0b00000000},
-	642:  {alias: map[collver]string{0b00001110: "ucs2_thai_520_w2"}, isdefault: 0b00000000},
-	672:  {alias: map[collver]string{0b00001111: "utf16_croatian_ci"}, isdefault: 0b00000000},
-	673:  {alias: map[collver]string{0b00001111: "utf16_myanmar_ci"}, isdefault: 0b00000000},
-	674:  {alias: map[collver]string{0b00001110: "utf16_thai_520_w2"}, isdefault: 0b00000000},
-	736:  {alias: map[collver]string{0b00001111: "utf32_croatian_ci"}, isdefault: 0b00000000},
-	737:  {alias: map[collver]string{0b00001111: "utf32_myanmar_ci"}, isdefault: 0b00000000},
-	738:  {alias: map[collver]string{0b00001110: "utf32_thai_520_w2"}, isdefault: 0b00000000},
-	1025: {alias: map[collver]string{0b00001100: "big5_chinese_nopad_ci"}, isdefault: 0b00000000},
-	1027: {alias: map[collver]string{0b00001100: "dec8_swedish_nopad_ci"}, isdefault: 0b00000000},
-	1028: {alias: map[collver]string{0b00001100: "cp850_general_nopad_ci"}, isdefault: 0b00000000},
-	1030: {alias: map[collver]string{0b00001100: "hp8_english_nopad_ci"}, isdefault: 0b00000000},
-	1031: {alias: map[collver]string{0b00001100: "koi8r_general_nopad_ci"}, isdefault: 0b00000000},
-	1032: {alias: map[collver]string{0b00001100: "latin1_swedish_nopad_ci"}, isdefault: 0b00000000},
-	1033: {alias: map[collver]string{0b00001100: "latin2_general_nopad_ci"}, isdefault: 0b00000000},
-	1034: {alias: map[collver]string{0b00001100: "swe7_swedish_nopad_ci"}, isdefault: 0b00000000},
-	1035: {alias: map[collver]string{0b00001100: "ascii_general_nopad_ci"}, isdefault: 0b00000000},
-	1036: {alias: map[collver]string{0b00001100: "ujis_japanese_nopad_ci"}, isdefault: 0b00000000},
-	1037: {alias: map[collver]string{0b00001100: "sjis_japanese_nopad_ci"}, isdefault: 0b00000000},
-	1040: {alias: map[collver]string{0b00001100: "hebrew_general_nopad_ci"}, isdefault: 0b00000000},
-	1042: {alias: map[collver]string{0b00001100: "tis620_thai_nopad_ci"}, isdefault: 0b00000000},
-	1043: {alias: map[collver]string{0b00001100: "euckr_korean_nopad_ci"}, isdefault: 0b00000000},
-	1046: {alias: map[collver]string{0b00001100: "koi8u_general_nopad_ci"}, isdefault: 0b00000000},
-	1048: {alias: map[collver]string{0b00001100: "gb2312_chinese_nopad_ci"}, isdefault: 0b00000000},
-	1049: {alias: map[collver]string{0b00001100: "greek_general_nopad_ci"}, isdefault: 0b00000000},
-	1050: {alias: map[collver]string{0b00001100: "cp1250_general_nopad_ci"}, isdefault: 0b00000000},
-	1052: {alias: map[collver]string{0b00001100: "gbk_chinese_nopad_ci"}, isdefault: 0b00000000},
-	1054: {alias: map[collver]string{0b00001100: "latin5_turkish_nopad_ci"}, isdefault: 0b00000000},
-	1056: {alias: map[collver]string{0b00001100: "armscii8_general_nopad_ci"}, isdefault: 0b00000000},
-	1057: {alias: map[collver]string{0b00001100: "utf8_general_nopad_ci"}, isdefault: 0b00000000},
-	1059: {alias: map[collver]string{0b00001100: "ucs2_general_nopad_ci"}, isdefault: 0b00000000},
-	1060: {alias: map[collver]string{0b00001100: "cp866_general_nopad_ci"}, isdefault: 0b00000000},
-	1061: {alias: map[collver]string{0b00001100: "keybcs2_general_nopad_ci"}, isdefault: 0b00000000},
-	1062: {alias: map[collver]string{0b00001100: "macce_general_nopad_ci"}, isdefault: 0b00000000},
-	1063: {alias: map[collver]string{0b00001100: "macroman_general_nopad_ci"}, isdefault: 0b00000000},
-	1064: {alias: map[collver]string{0b00001100: "cp852_general_nopad_ci"}, isdefault: 0b00000000},
-	1065: {alias: map[collver]string{0b00001100: "latin7_general_nopad_ci"}, isdefault: 0b00000000},
-	1067: {alias: map[collver]string{0b00001100: "macce_nopad_bin"}, isdefault: 0b00000000},
-	1069: {alias: map[collver]string{0b00001100: "utf8mb4_general_nopad_ci"}, isdefault: 0b00000000},
-	1070: {alias: map[collver]string{0b00001100: "utf8mb4_nopad_bin"}, isdefault: 0b00000000},
-	1071: {alias: map[collver]string{0b00001100: "latin1_nopad_bin"}, isdefault: 0b00000000},
-	1074: {alias: map[collver]string{0b00001100: "cp1251_nopad_bin"}, isdefault: 0b00000000},
-	1075: {alias: map[collver]string{0b00001100: "cp1251_general_nopad_ci"}, isdefault: 0b00000000},
-	1077: {alias: map[collver]string{0b00001100: "macroman_nopad_bin"}, isdefault: 0b00000000},
-	1078: {alias: map[collver]string{0b00001100: "utf16_general_nopad_ci"}, isdefault: 0b00000000},
-	1079: {alias: map[collver]string{0b00001100: "utf16_nopad_bin"}, isdefault: 0b00000000},
-	1080: {alias: map[collver]string{0b00001100: "utf16le_general_nopad_ci"}, isdefault: 0b00000000},
-	1081: {alias: map[collver]string{0b00001100: "cp1256_general_nopad_ci"}, isdefault: 0b00000000},
-	1082: {alias: map[collver]string{0b00001100: "cp1257_nopad_bin"}, isdefault: 0b00000000},
-	1083: {alias: map[collver]string{0b00001100: "cp1257_general_nopad_ci"}, isdefault: 0b00000000},
-	1084: {alias: map[collver]string{0b00001100: "utf32_general_nopad_ci"}, isdefault: 0b00000000},
-	1085: {alias: map[collver]string{0b00001100: "utf32_nopad_bin"}, isdefault: 0b00000000},
-	1086: {alias: map[collver]string{0b00001100: "utf16le_nopad_bin"}, isdefault: 0b00000000},
-	1088: {alias: map[collver]string{0b00001100: "armscii8_nopad_bin"}, isdefault: 0b00000000},
-	1089: {alias: map[collver]string{0b00001100: "ascii_nopad_bin"}, isdefault: 0b00000000},
-	1090: {alias: map[collver]string{0b00001100: "cp1250_nopad_bin"}, isdefault: 0b00000000},
-	1091: {alias: map[collver]string{0b00001100: "cp1256_nopad_bin"}, isdefault: 0b00000000},
-	1092: {alias: map[collver]string{0b00001100: "cp866_nopad_bin"}, isdefault: 0b00000000},
-	1093: {alias: map[collver]string{0b00001100: "dec8_nopad_bin"}, isdefault: 0b00000000},
-	1094: {alias: map[collver]string{0b00001100: "greek_nopad_bin"}, isdefault: 0b00000000},
-	1095: {alias: map[collver]string{0b00001100: "hebrew_nopad_bin"}, isdefault: 0b00000000},
-	1096: {alias: map[collver]string{0b00001100: "hp8_nopad_bin"}, isdefault: 0b00000000},
-	1097: {alias: map[collver]string{0b00001100: "keybcs2_nopad_bin"}, isdefault: 0b00000000},
-	1098: {alias: map[collver]string{0b00001100: "koi8r_nopad_bin"}, isdefault: 0b00000000},
-	1099: {alias: map[collver]string{0b00001100: "koi8u_nopad_bin"}, isdefault: 0b00000000},
-	1101: {alias: map[collver]string{0b00001100: "latin2_nopad_bin"}, isdefault: 0b00000000},
-	1102: {alias: map[collver]string{0b00001100: "latin5_nopad_bin"}, isdefault: 0b00000000},
-	1103: {alias: map[collver]string{0b00001100: "latin7_nopad_bin"}, isdefault: 0b00000000},
-	1104: {alias: map[collver]string{0b00001100: "cp850_nopad_bin"}, isdefault: 0b00000000},
-	1105: {alias: map[collver]string{0b00001100: "cp852_nopad_bin"}, isdefault: 0b00000000},
-	1106: {alias: map[collver]string{0b00001100: "swe7_nopad_bin"}, isdefault: 0b00000000},
-	1107: {alias: map[collver]string{0b00001100: "utf8_nopad_bin"}, isdefault: 0b00000000},
-	1108: {alias: map[collver]string{0b00001100: "big5_nopad_bin"}, isdefault: 0b00000000},
-	1109: {alias: map[collver]string{0b00001100: "euckr_nopad_bin"}, isdefault: 0b00000000},
-	1110: {alias: map[collver]string{0b00001100: "gb2312_nopad_bin"}, isdefault: 0b00000000},
-	1111: {alias: map[collver]string{0b00001100: "gbk_nopad_bin"}, isdefault: 0b00000000},
-	1112: {alias: map[collver]string{0b00001100: "sjis_nopad_bin"}, isdefault: 0b00000000},
-	1113: {alias: map[collver]string{0b00001100: "tis620_nopad_bin"}, isdefault: 0b00000000},
-	1114: {alias: map[collver]string{0b00001100: "ucs2_nopad_bin"}, isdefault: 0b00000000},
-	1115: {alias: map[collver]string{0b00001100: "ujis_nopad_bin"}, isdefault: 0b00000000},
-	1116: {alias: map[collver]string{0b00001100: "geostd8_general_nopad_ci"}, isdefault: 0b00000000},
-	1117: {alias: map[collver]string{0b00001100: "geostd8_nopad_bin"}, isdefault: 0b00000000},
-	1119: {alias: map[collver]string{0b00001100: "cp932_japanese_nopad_ci"}, isdefault: 0b00000000},
-	1120: {alias: map[collver]string{0b00001100: "cp932_nopad_bin"}, isdefault: 0b00000000},
-	1121: {alias: map[collver]string{0b00001100: "eucjpms_japanese_nopad_ci"}, isdefault: 0b00000000},
-	1122: {alias: map[collver]string{0b00001100: "eucjpms_nopad_bin"}, isdefault: 0b00000000},
-	1125: {alias: map[collver]string{0b00001100: "utf16_unicode_nopad_ci"}, isdefault: 0b00000000},
-	1147: {alias: map[collver]string{0b00001100: "utf16_unicode_520_nopad_ci"}, isdefault: 0b00000000},
-	1152: {alias: map[collver]string{0b00001100: "ucs2_unicode_nopad_ci"}, isdefault: 0b00000000},
-	1174: {alias: map[collver]string{0b00001100: "ucs2_unicode_520_nopad_ci"}, isdefault: 0b00000000},
-	1184: {alias: map[collver]string{0b00001100: "utf32_unicode_nopad_ci"}, isdefault: 0b00000000},
-	1206: {alias: map[collver]string{0b00001100: "utf32_unicode_520_nopad_ci"}, isdefault: 0b00000000},
-	1216: {alias: map[collver]string{0b00001100: "utf8_unicode_nopad_ci"}, isdefault: 0b00000000},
-	1238: {alias: map[collver]string{0b00001100: "utf8_unicode_520_nopad_ci"}, isdefault: 0b00000000},
-	1248: {alias: map[collver]string{0b00001100: "utf8mb4_unicode_nopad_ci"}, isdefault: 0b00000000},
-	1270: {alias: map[collver]string{0b00001100: "utf8mb4_unicode_520_nopad_ci"}, isdefault: 0b00000000},
+	1:    {alias: []collalias{{0b01111111, "big5_chinese_ci"}}, isdefault: 0b01111111},
+	2:    {alias: []collalias{{0b01111111, "latin2_czech_cs"}}, isdefault: 0b00000000},
+	3:    {alias: []collalias{{0b01111111, "dec8_swedish_ci"}}, isdefault: 0b01111111},
+	4:    {alias: []collalias{{0b01111111, "cp850_general_ci"}}, isdefault: 0b01111111},
+	5:    {alias: []collalias{{0b01111111, "latin1_german1_ci"}}, isdefault: 0b00000000},
+	6:    {alias: []collalias{{0b01111111, "hp8_english_ci"}}, isdefault: 0b01111111},
+	7:    {alias: []collalias{{0b01111111, "koi8r_general_ci"}}, isdefault: 0b01111111},
+	8:    {alias: []collalias{{0b01111111, "latin1_swedish_ci"}}, isdefault: 0b01111111},
+	9:    {alias: []collalias{{0b01111111, "latin2_general_ci"}}, isdefault: 0b01111111},
+	10:   {alias: []collalias{{0b01111111, "swe7_swedish_ci"}}, isdefault: 0b01111111},
+	11:   {alias: []collalias{{0b01111111, "ascii_general_ci"}}, isdefault: 0b01111111},
+	12:   {alias: []collalias{{0b01111111, "ujis_japanese_ci"}}, isdefault: 0b01111111},
+	13:   {alias: []collalias{{0b01111111, "sjis_japanese_ci"}}, isdefault: 0b01111111},
+	14:   {alias: []collalias{{0b01111111, "cp1251_bulgarian_ci"}}, isdefault: 0b00000000},
+	15:   {alias: []collalias{{0b01111111, "latin1_danish_ci"}}, isdefault: 0b00000000},
+	16:   {alias: []collalias{{0b01111111, "hebrew_general_ci"}}, isdefault: 0b01111111},
+	18:   {alias: []collalias{{0b01111111, "tis620_thai_ci"}}, isdefault: 0b01111111},
+	19:   {alias: []collalias{{0b01111111, "euckr_korean_ci"}}, isdefault: 0b01111111},
+	20:   {alias: []collalias{{0b01111111, "latin7_estonian_cs"}}, isdefault: 0b00000000},
+	21:   {alias: []collalias{{0b01111111, "latin2_hungarian_ci"}}, isdefault: 0b00000000},
+	22:   {alias: []collalias{{0b01111111, "koi8u_general_ci"}}, isdefault: 0b01111111},
+	23:   {alias: []collalias{{0b01111111, "cp1251_ukrainian_ci"}}, isdefault: 0b00000000},
+	24:   {alias: []collalias{{0b01111111, "gb2312_chinese_ci"}}, isdefault: 0b01111111},
+	25:   {alias: []collalias{{0b01111111, "greek_general_ci"}}, isdefault: 0b01111111},
+	26:   {alias: []collalias{{0b01111111, "cp1250_general_ci"}}, isdefault: 0b01111111},
+	27:   {alias: []collalias{{0b01111111, "latin2_croatian_ci"}}, isdefault: 0b00000000},
+	28:   {alias: []collalias{{0b01111111, "gbk_chinese_ci"}}, isdefault: 0b01111111},
+	29:   {alias: []collalias{{0b01111111, "cp1257_lithuanian_ci"}}, isdefault: 0b00000000},
+	30:   {alias: []collalias{{0b01111111, "latin5_turkish_ci"}}, isdefault: 0b01111111},
+	31:   {alias: []collalias{{0b01111111, "latin1_german2_ci"}}, isdefault: 0b00000000},
+	32:   {alias: []collalias{{0b01111111, "armscii8_general_ci"}}, isdefault: 0b01111111},
+	33:   {alias: []collalias{{0b01111111, "utf8_general_ci"}, {0b01111111, "utf8mb3_general_ci"}}, isdefault: 0b01111111},
+	34:   {alias: []collalias{{0b01111111, "cp1250_czech_cs"}}, isdefault: 0b00000000},
+	35:   {alias: []collalias{{0b01111111, "ucs2_general_ci"}}, isdefault: 0b01111111},
+	36:   {alias: []collalias{{0b01111111, "cp866_general_ci"}}, isdefault: 0b01111111},
+	37:   {alias: []collalias{{0b01111111, "keybcs2_general_ci"}}, isdefault: 0b01111111},
+	38:   {alias: []collalias{{0b01111111, "macce_general_ci"}}, isdefault: 0b01111111},
+	39:   {alias: []collalias{{0b01111111, "macroman_general_ci"}}, isdefault: 0b01111111},
+	40:   {alias: []collalias{{0b01111111, "cp852_general_ci"}}, isdefault: 0b01111111},
+	41:   {alias: []collalias{{0b01111111, "latin7_general_ci"}}, isdefault: 0b01111111},
+	42:   {alias: []collalias{{0b01111111, "latin7_general_cs"}}, isdefault: 0b00000000},
+	43:   {alias: []collalias{{0b01111111, "macce_bin"}}, isdefault: 0b00000000},
+	44:   {alias: []collalias{{0b01111111, "cp1250_croatian_ci"}}, isdefault: 0b00000000},
+	45:   {alias: []collalias{{0b01111111, "utf8mb4_general_ci"}}, isdefault: 0b00111111},
+	46:   {alias: []collalias{{0b01111111, "utf8mb4_bin"}}, isdefault: 0b00000000},
+	47:   {alias: []collalias{{0b01111111, "latin1_bin"}}, isdefault: 0b00000000},
+	48:   {alias: []collalias{{0b01111111, "latin1_general_ci"}}, isdefault: 0b00000000},
+	49:   {alias: []collalias{{0b01111111, "latin1_general_cs"}}, isdefault: 0b00000000},
+	50:   {alias: []collalias{{0b01111111, "cp1251_bin"}}, isdefault: 0b00000000},
+	51:   {alias: []collalias{{0b01111111, "cp1251_general_ci"}}, isdefault: 0b01111111},
+	52:   {alias: []collalias{{0b01111111, "cp1251_general_cs"}}, isdefault: 0b00000000},
+	53:   {alias: []collalias{{0b01111111, "macroman_bin"}}, isdefault: 0b00000000},
+	54:   {alias: []collalias{{0b01111111, "utf16_general_ci"}}, isdefault: 0b01111111},
+	55:   {alias: []collalias{{0b01111111, "utf16_bin"}}, isdefault: 0b00000000},
+	56:   {alias: []collalias{{0b01111111, "utf16le_general_ci"}}, isdefault: 0b01111111},
+	57:   {alias: []collalias{{0b01111111, "cp1256_general_ci"}}, isdefault: 0b01111111},
+	58:   {alias: []collalias{{0b01111111, "cp1257_bin"}}, isdefault: 0b00000000},
+	59:   {alias: []collalias{{0b01111111, "cp1257_general_ci"}}, isdefault: 0b01111111},
+	60:   {alias: []collalias{{0b01111111, "utf32_general_ci"}}, isdefault: 0b01111111},
+	61:   {alias: []collalias{{0b01111111, "utf32_bin"}}, isdefault: 0b00000000},
+	62:   {alias: []collalias{{0b01111111, "utf16le_bin"}}, isdefault: 0b00000000},
+	63:   {alias: []collalias{{0b01111111, "binary"}}, isdefault: 0b01111111},
+	64:   {alias: []collalias{{0b01111111, "armscii8_bin"}}, isdefault: 0b00000000},
+	65:   {alias: []collalias{{0b01111111, "ascii_bin"}}, isdefault: 0b00000000},
+	66:   {alias: []collalias{{0b01111111, "cp1250_bin"}}, isdefault: 0b00000000},
+	67:   {alias: []collalias{{0b01111111, "cp1256_bin"}}, isdefault: 0b00000000},
+	68:   {alias: []collalias{{0b01111111, "cp866_bin"}}, isdefault: 0b00000000},
+	69:   {alias: []collalias{{0b01111111, "dec8_bin"}}, isdefault: 0b00000000},
+	70:   {alias: []collalias{{0b01111111, "greek_bin"}}, isdefault: 0b00000000},
+	71:   {alias: []collalias{{0b01111111, "hebrew_bin"}}, isdefault: 0b00000000},
+	72:   {alias: []collalias{{0b01111111, "hp8_bin"}}, isdefault: 0b00000000},
+	73:   {alias: []collalias{{0b01111111, "keybcs2_bin"}}, isdefault: 0b00000000},
+	74:   {alias: []collalias{{0b01111111, "koi8r_bin"}}, isdefault: 0b00000000},
+	75:   {alias: []collalias{{0b01111111, "koi8u_bin"}}, isdefault: 0b00000000},
+	76:   {alias: []collalias{{0b01000000, "utf8_tolower_ci"}, {0b01000000, "utf8mb3_tolower_ci"}}, isdefault: 0b00000000},
+	77:   {alias: []collalias{{0b01111111, "latin2_bin"}}, isdefault: 0b00000000},
+	78:   {alias: []collalias{{0b01111111, "latin5_bin"}}, isdefault: 0b00000000},
+	79:   {alias: []collalias{{0b01111111, "latin7_bin"}}, isdefault: 0b00000000},
+	80:   {alias: []collalias{{0b01111111, "cp850_bin"}}, isdefault: 0b00000000},
+	81:   {alias: []collalias{{0b01111111, "cp852_bin"}}, isdefault: 0b00000000},
+	82:   {alias: []collalias{{0b01111111, "swe7_bin"}}, isdefault: 0b00000000},
+	83:   {alias: []collalias{{0b01111111, "utf8_bin"}, {0b01111111, "utf8mb3_bin"}}, isdefault: 0b00000000},
+	84:   {alias: []collalias{{0b01111111, "big5_bin"}}, isdefault: 0b00000000},
+	85:   {alias: []collalias{{0b01111111, "euckr_bin"}}, isdefault: 0b00000000},
+	86:   {alias: []collalias{{0b01111111, "gb2312_bin"}}, isdefault: 0b00000000},
+	87:   {alias: []collalias{{0b01111111, "gbk_bin"}}, isdefault: 0b00000000},
+	88:   {alias: []collalias{{0b01111111, "sjis_bin"}}, isdefault: 0b00000000},
+	89:   {alias: []collalias{{0b01111111, "tis620_bin"}}, isdefault: 0b00000000},
+	90:   {alias: []collalias{{0b01111111, "ucs2_bin"}}, isdefault: 0b00000000},
+	91:   {alias: []collalias{{0b01111111, "ujis_bin"}}, isdefault: 0b00000000},
+	92:   {alias: []collalias{{0b01111111, "geostd8_general_ci"}}, isdefault: 0b01111111},
+	93:   {alias: []collalias{{0b01111111, "geostd8_bin"}}, isdefault: 0b00000000},
+	94:   {alias: []collalias{{0b01111111, "latin1_spanish_ci"}}, isdefault: 0b00000000},
+	95:   {alias: []collalias{{0b01111111, "cp932_japanese_ci"}}, isdefault: 0b01111111},
+	96:   {alias: []collalias{{0b01111111, "cp932_bin"}}, isdefault: 0b00000000},
+	97:   {alias: []collalias{{0b01111111, "eucjpms_japanese_ci"}}, isdefault: 0b01111111},
+	98:   {alias: []collalias{{0b01111111, "eucjpms_bin"}}, isdefault: 0b00000000},
+	99:   {alias: []collalias{{0b01111111, "cp1250_polish_ci"}}, isdefault: 0b00000000},
+	101:  {alias: []collalias{{0b01111111, "utf16_unicode_ci"}}, isdefault: 0b00000000},
+	102:  {alias: []collalias{{0b01111111, "utf16_icelandic_ci"}}, isdefault: 0b00000000},
+	103:  {alias: []collalias{{0b01111111, "utf16_latvian_ci"}}, isdefault: 0b00000000},
+	104:  {alias: []collalias{{0b01111111, "utf16_romanian_ci"}}, isdefault: 0b00000000},
+	105:  {alias: []collalias{{0b01111111, "utf16_slovenian_ci"}}, isdefault: 0b00000000},
+	106:  {alias: []collalias{{0b01111111, "utf16_polish_ci"}}, isdefault: 0b00000000},
+	107:  {alias: []collalias{{0b01111111, "utf16_estonian_ci"}}, isdefault: 0b00000000},
+	108:  {alias: []collalias{{0b01111111, "utf16_spanish_ci"}}, isdefault: 0b00000000},
+	109:  {alias: []collalias{{0b01111111, "utf16_swedish_ci"}}, isdefault: 0b00000000},
+	110:  {alias: []collalias{{0b01111111, "utf16_turkish_ci"}}, isdefault: 0b00000000},
+	111:  {alias: []collalias{{0b01111111, "utf16_czech_ci"}}, isdefault: 0b00000000},
+	112:  {alias: []collalias{{0b01111111, "utf16_danish_ci"}}, isdefault: 0b00000000},
+	113:  {alias: []collalias{{0b01111111, "utf16_lithuanian_ci"}}, isdefault: 0b00000000},
+	114:  {alias: []collalias{{0b01111111, "utf16_slovak_ci"}}, isdefault: 0b00000000},
+	115:  {alias: []collalias{{0b01111111, "utf16_spanish2_ci"}}, isdefault: 0b00000000},
+	116:  {alias: []collalias{{0b01111111, "utf16_roman_ci"}}, isdefault: 0b00000000},
+	117:  {alias: []collalias{{0b01111111, "utf16_persian_ci"}}, isdefault: 0b00000000},
+	118:  {alias: []collalias{{0b01111111, "utf16_esperanto_ci"}}, isdefault: 0b00000000},
+	119:  {alias: []collalias{{0b01111111, "utf16_hungarian_ci"}}, isdefault: 0b00000000},
+	120:  {alias: []collalias{{0b01111111, "utf16_sinhala_ci"}}, isdefault: 0b00000000},
+	121:  {alias: []collalias{{0b01111111, "utf16_german2_ci"}}, isdefault: 0b00000000},
+	122:  {alias: []collalias{{0b01110000, "utf16_croatian_ci"}, {0b00001111, "utf16_croatian_mysql561_ci"}}, isdefault: 0b00000000},
+	123:  {alias: []collalias{{0b01111111, "utf16_unicode_520_ci"}}, isdefault: 0b00000000},
+	124:  {alias: []collalias{{0b01111111, "utf16_vietnamese_ci"}}, isdefault: 0b00000000},
+	128:  {alias: []collalias{{0b01111111, "ucs2_unicode_ci"}}, isdefault: 0b00000000},
+	129:  {alias: []collalias{{0b01111111, "ucs2_icelandic_ci"}}, isdefault: 0b00000000},
+	130:  {alias: []collalias{{0b01111111, "ucs2_latvian_ci"}}, isdefault: 0b00000000},
+	131:  {alias: []collalias{{0b01111111, "ucs2_romanian_ci"}}, isdefault: 0b00000000},
+	132:  {alias: []collalias{{0b01111111, "ucs2_slovenian_ci"}}, isdefault: 0b00000000},
+	133:  {alias: []collalias{{0b01111111, "ucs2_polish_ci"}}, isdefault: 0b00000000},
+	134:  {alias: []collalias{{0b01111111, "ucs2_estonian_ci"}}, isdefault: 0b00000000},
+	135:  {alias: []collalias{{0b01111111, "ucs2_spanish_ci"}}, isdefault: 0b00000000},
+	136:  {alias: []collalias{{0b01111111, "ucs2_swedish_ci"}}, isdefault: 0b00000000},
+	137:  {alias: []collalias{{0b01111111, "ucs2_turkish_ci"}}, isdefault: 0b00000000},
+	138:  {alias: []collalias{{0b01111111, "ucs2_czech_ci"}}, isdefault: 0b00000000},
+	139:  {alias: []collalias{{0b01111111, "ucs2_danish_ci"}}, isdefault: 0b00000000},
+	140:  {alias: []collalias{{0b01111111, "ucs2_lithuanian_ci"}}, isdefault: 0b00000000},
+	141:  {alias: []collalias{{0b01111111, "ucs2_slovak_ci"}}, isdefault: 0b00000000},
+	142:  {alias: []collalias{{0b01111111, "ucs2_spanish2_ci"}}, isdefault: 0b00000000},
+	143:  {alias: []collalias{{0b01111111, "ucs2_roman_ci"}}, isdefault: 0b00000000},
+	144:  {alias: []collalias{{0b01111111, "ucs2_persian_ci"}}, isdefault: 0b00000000},
+	145:  {alias: []collalias{{0b01111111, "ucs2_esperanto_ci"}}, isdefault: 0b00000000},
+	146:  {alias: []collalias{{0b01111111, "ucs2_hungarian_ci"}}, isdefault: 0b00000000},
+	147:  {alias: []collalias{{0b01111111, "ucs2_sinhala_ci"}}, isdefault: 0b00000000},
+	148:  {alias: []collalias{{0b01111111, "ucs2_german2_ci"}}, isdefault: 0b00000000},
+	149:  {alias: []collalias{{0b01110000, "ucs2_croatian_ci"}, {0b00001111, "ucs2_croatian_mysql561_ci"}}, isdefault: 0b00000000},
+	150:  {alias: []collalias{{0b01111111, "ucs2_unicode_520_ci"}}, isdefault: 0b00000000},
+	151:  {alias: []collalias{{0b01111111, "ucs2_vietnamese_ci"}}, isdefault: 0b00000000},
+	159:  {alias: []collalias{{0b01111111, "ucs2_general_mysql500_ci"}}, isdefault: 0b00000000},
+	160:  {alias: []collalias{{0b01111111, "utf32_unicode_ci"}}, isdefault: 0b00000000},
+	161:  {alias: []collalias{{0b01111111, "utf32_icelandic_ci"}}, isdefault: 0b00000000},
+	162:  {alias: []collalias{{0b01111111, "utf32_latvian_ci"}}, isdefault: 0b00000000},
+	163:  {alias: []collalias{{0b01111111, "utf32_romanian_ci"}}, isdefault: 0b00000000},
+	164:  {alias: []collalias{{0b01111111, "utf32_slovenian_ci"}}, isdefault: 0b00000000},
+	165:  {alias: []collalias{{0b01111111, "utf32_polish_ci"}}, isdefault: 0b00000000},
+	166:  {alias: []collalias{{0b01111111, "utf32_estonian_ci"}}, isdefault: 0b00000000},
+	167:  {alias: []collalias{{0b01111111, "utf32_spanish_ci"}}, isdefault: 0b00000000},
+	168:  {alias: []collalias{{0b01111111, "utf32_swedish_ci"}}, isdefault: 0b00000000},
+	169:  {alias: []collalias{{0b01111111, "utf32_turkish_ci"}}, isdefault: 0b00000000},
+	170:  {alias: []collalias{{0b01111111, "utf32_czech_ci"}}, isdefault: 0b00000000},
+	171:  {alias: []collalias{{0b01111111, "utf32_danish_ci"}}, isdefault: 0b00000000},
+	172:  {alias: []collalias{{0b01111111, "utf32_lithuanian_ci"}}, isdefault: 0b00000000},
+	173:  {alias: []collalias{{0b01111111, "utf32_slovak_ci"}}, isdefault: 0b00000000},
+	174:  {alias: []collalias{{0b01111111, "utf32_spanish2_ci"}}, isdefault: 0b00000000},
+	175:  {alias: []collalias{{0b01111111, "utf32_roman_ci"}}, isdefault: 0b00000000},
+	176:  {alias: []collalias{{0b01111111, "utf32_persian_ci"}}, isdefault: 0b00000000},
+	177:  {alias: []collalias{{0b01111111, "utf32_esperanto_ci"}}, isdefault: 0b00000000},
+	178:  {alias: []collalias{{0b01111111, "utf32_hungarian_ci"}}, isdefault: 0b00000000},
+	179:  {alias: []collalias{{0b01111111, "utf32_sinhala_ci"}}, isdefault: 0b00000000},
+	180:  {alias: []collalias{{0b01111111, "utf32_german2_ci"}}, isdefault: 0b00000000},
+	181:  {alias: []collalias{{0b01110000, "utf32_croatian_ci"}, {0b00001111, "utf32_croatian_mysql561_ci"}}, isdefault: 0b00000000},
+	182:  {alias: []collalias{{0b01111111, "utf32_unicode_520_ci"}}, isdefault: 0b00000000},
+	183:  {alias: []collalias{{0b01111111, "utf32_vietnamese_ci"}}, isdefault: 0b00000000},
+	192:  {alias: []collalias{{0b01111111, "utf8_unicode_ci"}, {0b01111111, "utf8mb3_unicode_ci"}}, isdefault: 0b00000000},
+	193:  {alias: []collalias{{0b01111111, "utf8_icelandic_ci"}, {0b01111111, "utf8mb3_icelandic_ci"}}, isdefault: 0b00000000},
+	194:  {alias: []collalias{{0b01111111, "utf8_latvian_ci"}, {0b01111111, "utf8mb3_latvian_ci"}}, isdefault: 0b00000000},
+	195:  {alias: []collalias{{0b01111111, "utf8_romanian_ci"}, {0b01111111, "utf8mb3_romanian_ci"}}, isdefault: 0b00000000},
+	196:  {alias: []collalias{{0b01111111, "utf8_slovenian_ci"}, {0b01111111, "utf8mb3_slovenian_ci"}}, isdefault: 0b00000000},
+	197:  {alias: []collalias{{0b01111111, "utf8_polish_ci"}, {0b01111111, "utf8mb3_polish_ci"}}, isdefault: 0b00000000},
+	198:  {alias: []collalias{{0b01111111, "utf8_estonian_ci"}, {0b01111111, "utf8mb3_estonian_ci"}}, isdefault: 0b00000000},
+	199:  {alias: []collalias{{0b01111111, "utf8_spanish_ci"}, {0b01111111, "utf8mb3_spanish_ci"}}, isdefault: 0b00000000},
+	200:  {alias: []collalias{{0b01111111, "utf8_swedish_ci"}, {0b01111111, "utf8mb3_swedish_ci"}}, isdefault: 0b00000000},
+	201:  {alias: []collalias{{0b01111111, "utf8_turkish_ci"}, {0b01111111, "utf8mb3_turkish_ci"}}, isdefault: 0b00000000},
+	202:  {alias: []collalias{{0b01111111, "utf8_czech_ci"}, {0b01111111, "utf8mb3_czech_ci"}}, isdefault: 0b00000000},
+	203:  {alias: []collalias{{0b01111111, "utf8_danish_ci"}, {0b01111111, "utf8mb3_danish_ci"}}, isdefault: 0b00000000},
+	204:  {alias: []collalias{{0b01111111, "utf8_lithuanian_ci"}, {0b01111111, "utf8mb3_lithuanian_ci"}}, isdefault: 0b00000000},
+	205:  {alias: []collalias{{0b01111111, "utf8_slovak_ci"}, {0b01111111, "utf8mb3_slovak_ci"}}, isdefault: 0b00000000},
+	206:  {alias: []collalias{{0b01111111, "utf8_spanish2_ci"}, {0b01111111, "utf8mb3_spanish2_ci"}}, isdefault: 0b00000000},
+	207:  {alias: []collalias{{0b01111111, "utf8_roman_ci"}, {0b01111111, "utf8mb3_roman_ci"}}, isdefault: 0b00000000},
+	208:  {alias: []collalias{{0b01111111, "utf8_persian_ci"}, {0b01111111, "utf8mb3_persian_ci"}}, isdefault: 0b00000000},
+	209:  {alias: []collalias{{0b01111111, "utf8_esperanto_ci"}, {0b01111111, "utf8mb3_esperanto_ci"}}, isdefault: 0b00000000},
+	210:  {alias: []collalias{{0b01111111, "utf8_hungarian_ci"}, {0b01111111, "utf8mb3_hungarian_ci"}}, isdefault: 0b00000000},
+	211:  {alias: []collalias{{0b01111111, "utf8_sinhala_ci"}, {0b01111111, "utf8mb3_sinhala_ci"}}, isdefault: 0b00000000},
+	212:  {alias: []collalias{{0b01111111, "utf8_german2_ci"}, {0b01111111, "utf8mb3_german2_ci"}}, isdefault: 0b00000000},
+	213:  {alias: []collalias{{0b01110000, "utf8_croatian_ci"}, {0b00001111, "utf8_croatian_mysql561_ci"}, {0b01110000, "utf8mb3_croatian_ci"}, {0b00001111, "utf8mb3_croatian_mysql561_ci"}}, isdefault: 0b00000000},
+	214:  {alias: []collalias{{0b01111111, "utf8_unicode_520_ci"}, {0b01111111, "utf8mb3_unicode_520_ci"}}, isdefault: 0b00000000},
+	215:  {alias: []collalias{{0b01111111, "utf8_vietnamese_ci"}, {0b01111111, "utf8mb3_vietnamese_ci"}}, isdefault: 0b00000000},
+	223:  {alias: []collalias{{0b01111111, "utf8_general_mysql500_ci"}, {0b01111111, "utf8mb3_general_mysql500_ci"}}, isdefault: 0b00000000},
+	224:  {alias: []collalias{{0b01111111, "utf8mb4_unicode_ci"}}, isdefault: 0b00000000},
+	225:  {alias: []collalias{{0b01111111, "utf8mb4_icelandic_ci"}}, isdefault: 0b00000000},
+	226:  {alias: []collalias{{0b01111111, "utf8mb4_latvian_ci"}}, isdefault: 0b00000000},
+	227:  {alias: []collalias{{0b01111111, "utf8mb4_romanian_ci"}}, isdefault: 0b00000000},
+	228:  {alias: []collalias{{0b01111111, "utf8mb4_slovenian_ci"}}, isdefault: 0b00000000},
+	229:  {alias: []collalias{{0b01111111, "utf8mb4_polish_ci"}}, isdefault: 0b00000000},
+	230:  {alias: []collalias{{0b01111111, "utf8mb4_estonian_ci"}}, isdefault: 0b00000000},
+	231:  {alias: []collalias{{0b01111111, "utf8mb4_spanish_ci"}}, isdefault: 0b00000000},
+	232:  {alias: []collalias{{0b01111111, "utf8mb4_swedish_ci"}}, isdefault: 0b00000000},
+	233:  {alias: []collalias{{0b01111111, "utf8mb4_turkish_ci"}}, isdefault: 0b00000000},
+	234:  {alias: []collalias{{0b01111111, "utf8mb4_czech_ci"}}, isdefault: 0b00000000},
+	235:  {alias: []collalias{{0b01111111, "utf8mb4_danish_ci"}}, isdefault: 0b00000000},
+	236:  {alias: []collalias{{0b01111111, "utf8mb4_lithuanian_ci"}}, isdefault: 0b00000000},
+	237:  {alias: []collalias{{0b01111111, "utf8mb4_slovak_ci"}}, isdefault: 0b00000000},
+	238:  {alias: []collalias{{0b01111111, "utf8mb4_spanish2_ci"}}, isdefault: 0b00000000},
+	239:  {alias: []collalias{{0b01111111, "utf8mb4_roman_ci"}}, isdefault: 0b00000000},
+	240:  {alias: []collalias{{0b01111111, "utf8mb4_persian_ci"}}, isdefault: 0b00000000},
+	241:  {alias: []collalias{{0b01111111, "utf8mb4_esperanto_ci"}}, isdefault: 0b00000000},
+	242:  {alias: []collalias{{0b01111111, "utf8mb4_hungarian_ci"}}, isdefault: 0b00000000},
+	243:  {alias: []collalias{{0b01111111, "utf8mb4_sinhala_ci"}}, isdefault: 0b00000000},
+	244:  {alias: []collalias{{0b01111111, "utf8mb4_german2_ci"}}, isdefault: 0b00000000},
+	245:  {alias: []collalias{{0b01110000, "utf8mb4_croatian_ci"}, {0b00001111, "utf8mb4_croatian_mysql561_ci"}}, isdefault: 0b00000000},
+	246:  {alias: []collalias{{0b01111111, "utf8mb4_unicode_520_ci"}}, isdefault: 0b00000000},
+	247:  {alias: []collalias{{0b01111111, "utf8mb4_vietnamese_ci"}}, isdefault: 0b00000000},
+	248:  {alias: []collalias{{0b01100000, "gb18030_chinese_ci"}}, isdefault: 0b01100000},
+	249:  {alias: []collalias{{0b01100000, "gb18030_bin"}}, isdefault: 0b00000000},
+	250:  {alias: []collalias{{0b01100000, "gb18030_unicode_520_ci"}}, isdefault: 0b00000000},
+	255:  {alias: []collalias{{0b01000000, "utf8mb4_0900_ai_ci"}}, isdefault: 0b01000000},
+	256:  {alias: []collalias{{0b01000000, "utf8mb4_de_pb_0900_ai_ci"}}, isdefault: 0b00000000},
+	257:  {alias: []collalias{{0b01000000, "utf8mb4_is_0900_ai_ci"}}, isdefault: 0b00000000},
+	258:  {alias: []collalias{{0b01000000, "utf8mb4_lv_0900_ai_ci"}}, isdefault: 0b00000000},
+	259:  {alias: []collalias{{0b01000000, "utf8mb4_ro_0900_ai_ci"}}, isdefault: 0b00000000},
+	260:  {alias: []collalias{{0b01000000, "utf8mb4_sl_0900_ai_ci"}}, isdefault: 0b00000000},
+	261:  {alias: []collalias{{0b01000000, "utf8mb4_pl_0900_ai_ci"}}, isdefault: 0b00000000},
+	262:  {alias: []collalias{{0b01000000, "utf8mb4_et_0900_ai_ci"}}, isdefault: 0b00000000},
+	263:  {alias: []collalias{{0b01000000, "utf8mb4_es_0900_ai_ci"}}, isdefault: 0b00000000},
+	264:  {alias: []collalias{{0b01000000, "utf8mb4_sv_0900_ai_ci"}}, isdefault: 0b00000000},
+	265:  {alias: []collalias{{0b01000000, "utf8mb4_tr_0900_ai_ci"}}, isdefault: 0b00000000},
+	266:  {alias: []collalias{{0b01000000, "utf8mb4_cs_0900_ai_ci"}}, isdefault: 0b00000000},
+	267:  {alias: []collalias{{0b01000000, "utf8mb4_da_0900_ai_ci"}}, isdefault: 0b00000000},
+	268:  {alias: []collalias{{0b01000000, "utf8mb4_lt_0900_ai_ci"}}, isdefault: 0b00000000},
+	269:  {alias: []collalias{{0b01000000, "utf8mb4_sk_0900_ai_ci"}}, isdefault: 0b00000000},
+	270:  {alias: []collalias{{0b01000000, "utf8mb4_es_trad_0900_ai_ci"}}, isdefault: 0b00000000},
+	271:  {alias: []collalias{{0b01000000, "utf8mb4_la_0900_ai_ci"}}, isdefault: 0b00000000},
+	273:  {alias: []collalias{{0b01000000, "utf8mb4_eo_0900_ai_ci"}}, isdefault: 0b00000000},
+	274:  {alias: []collalias{{0b01000000, "utf8mb4_hu_0900_ai_ci"}}, isdefault: 0b00000000},
+	275:  {alias: []collalias{{0b01000000, "utf8mb4_hr_0900_ai_ci"}}, isdefault: 0b00000000},
+	277:  {alias: []collalias{{0b01000000, "utf8mb4_vi_0900_ai_ci"}}, isdefault: 0b00000000},
+	278:  {alias: []collalias{{0b01000000, "utf8mb4_0900_as_cs"}}, isdefault: 0b00000000},
+	279:  {alias: []collalias{{0b01000000, "utf8mb4_de_pb_0900_as_cs"}}, isdefault: 0b00000000},
+	280:  {alias: []collalias{{0b01000000, "utf8mb4_is_0900_as_cs"}}, isdefault: 0b00000000},
+	281:  {alias: []collalias{{0b01000000, "utf8mb4_lv_0900_as_cs"}}, isdefault: 0b00000000},
+	282:  {alias: []collalias{{0b01000000, "utf8mb4_ro_0900_as_cs"}}, isdefault: 0b00000000},
+	283:  {alias: []collalias{{0b01000000, "utf8mb4_sl_0900_as_cs"}}, isdefault: 0b00000000},
+	284:  {alias: []collalias{{0b01000000, "utf8mb4_pl_0900_as_cs"}}, isdefault: 0b00000000},
+	285:  {alias: []collalias{{0b01000000, "utf8mb4_et_0900_as_cs"}}, isdefault: 0b00000000},
+	286:  {alias: []collalias{{0b01000000, "utf8mb4_es_0900_as_cs"}}, isdefault: 0b00000000},
+	287:  {alias: []collalias{{0b01000000, "utf8mb4_sv_0900_as_cs"}}, isdefault: 0b00000000},
+	288:  {alias: []collalias{{0b01000000, "utf8mb4_tr_0900_as_cs"}}, isdefault: 0b00000000},
+	289:  {alias: []collalias{{0b01000000, "utf8mb4_cs_0900_as_cs"}}, isdefault: 0b00000000},
+	290:  {alias: []collalias{{0b01000000, "utf8mb4_da_0900_as_cs"}}, isdefault: 0b00000000},
+	291:  {alias: []collalias{{0b01000000, "utf8mb4_lt_0900_as_cs"}}, isdefault: 0b00000000},
+	292:  {alias: []collalias{{0b01000000, "utf8mb4_sk_0900_as_cs"}}, isdefault: 0b00000000},
+	293:  {alias: []collalias{{0b01000000, "utf8mb4_es_trad_0900_as_cs"}}, isdefault: 0b00000000},
+	294:  {alias: []collalias{{0b01000000, "utf8mb4_la_0900_as_cs"}}, isdefault: 0b00000000},
+	296:  {alias: []collalias{{0b01000000, "utf8mb4_eo_0900_as_cs"}}, isdefault: 0b00000000},
+	297:  {alias: []collalias{{0b01000000, "utf8mb4_hu_0900_as_cs"}}, isdefault: 0b00000000},
+	298:  {alias: []collalias{{0b01000000, "utf8mb4_hr_0900_as_cs"}}, isdefault: 0b00000000},
+	300:  {alias: []collalias{{0b01000000, "utf8mb4_vi_0900_as_cs"}}, isdefault: 0b00000000},
+	303:  {alias: []collalias{{0b01000000, "utf8mb4_ja_0900_as_cs"}}, isdefault: 0b00000000},
+	304:  {alias: []collalias{{0b01000000, "utf8mb4_ja_0900_as_cs_ks"}}, isdefault: 0b00000000},
+	305:  {alias: []collalias{{0b01000000, "utf8mb4_0900_as_ci"}}, isdefault: 0b00000000},
+	306:  {alias: []collalias{{0b01000000, "utf8mb4_ru_0900_ai_ci"}}, isdefault: 0b00000000},
+	307:  {alias: []collalias{{0b01000000, "utf8mb4_ru_0900_as_cs"}}, isdefault: 0b00000000},
+	308:  {alias: []collalias{{0b01000000, "utf8mb4_zh_0900_as_cs"}}, isdefault: 0b00000000},
+	309:  {alias: []collalias{{0b01000000, "utf8mb4_0900_bin"}}, isdefault: 0b00000000},
+	576:  {alias: []collalias{{0b00001111, "utf8_croatian_ci"}, {0b00001111, "utf8mb3_croatian_ci"}}, isdefault: 0b00000000},
+	577:  {alias: []collalias{{0b00001111, "utf8_myanmar_ci"}, {0b00001111, "utf8mb3_myanmar_ci"}}, isdefault: 0b00000000},
+	578:  {alias: []collalias{{0b00001110, "utf8_thai_520_w2"}, {0b00001110, "utf8mb3_thai_520_w2"}}, isdefault: 0b00000000},
+	608:  {alias: []collalias{{0b00001111, "utf8mb4_croatian_ci"}}, isdefault: 0b00000000},
+	609:  {alias: []collalias{{0b00001111, "utf8mb4_myanmar_ci"}}, isdefault: 0b00000000},
+	610:  {alias: []collalias{{0b00001110, "utf8mb4_thai_520_w2"}}, isdefault: 0b00000000},
+	640:  {alias: []collalias{{0b00001111, "ucs2_croatian_ci"}}, isdefault: 0b00000000},
+	641:  {alias: []collalias{{0b00001111, "ucs2_myanmar_ci"}}, isdefault: 0b00000000},
+	642:  {alias: []collalias{{0b00001110, "ucs2_thai_520_w2"}}, isdefault: 0b00000000},
+	672:  {alias: []collalias{{0b00001111, "utf16_croatian_ci"}}, isdefault: 0b00000000},
+	673:  {alias: []collalias{{0b00001111, "utf16_myanmar_ci"}}, isdefault: 0b00000000},
+	674:  {alias: []collalias{{0b00001110, "utf16_thai_520_w2"}}, isdefault: 0b00000000},
+	736:  {alias: []collalias{{0b00001111, "utf32_croatian_ci"}}, isdefault: 0b00000000},
+	737:  {alias: []collalias{{0b00001111, "utf32_myanmar_ci"}}, isdefault: 0b00000000},
+	738:  {alias: []collalias{{0b00001110, "utf32_thai_520_w2"}}, isdefault: 0b00000000},
+	1025: {alias: []collalias{{0b00001100, "big5_chinese_nopad_ci"}}, isdefault: 0b00000000},
+	1027: {alias: []collalias{{0b00001100, "dec8_swedish_nopad_ci"}}, isdefault: 0b00000000},
+	1028: {alias: []collalias{{0b00001100, "cp850_general_nopad_ci"}}, isdefault: 0b00000000},
+	1030: {alias: []collalias{{0b00001100, "hp8_english_nopad_ci"}}, isdefault: 0b00000000},
+	1031: {alias: []collalias{{0b00001100, "koi8r_general_nopad_ci"}}, isdefault: 0b00000000},
+	1032: {alias: []collalias{{0b00001100, "latin1_swedish_nopad_ci"}}, isdefault: 0b00000000},
+	1033: {alias: []collalias{{0b00001100, "latin2_general_nopad_ci"}}, isdefault: 0b00000000},
+	1034: {alias: []collalias{{0b00001100, "swe7_swedish_nopad_ci"}}, isdefault: 0b00000000},
+	1035: {alias: []collalias{{0b00001100, "ascii_general_nopad_ci"}}, isdefault: 0b00000000},
+	1036: {alias: []collalias{{0b00001100, "ujis_japanese_nopad_ci"}}, isdefault: 0b00000000},
+	1037: {alias: []collalias{{0b00001100, "sjis_japanese_nopad_ci"}}, isdefault: 0b00000000},
+	1040: {alias: []collalias{{0b00001100, "hebrew_general_nopad_ci"}}, isdefault: 0b00000000},
+	1042: {alias: []collalias{{0b00001100, "tis620_thai_nopad_ci"}}, isdefault: 0b00000000},
+	1043: {alias: []collalias{{0b00001100, "euckr_korean_nopad_ci"}}, isdefault: 0b00000000},
+	1046: {alias: []collalias{{0b00001100, "koi8u_general_nopad_ci"}}, isdefault: 0b00000000},
+	1048: {alias: []collalias{{0b00001100, "gb2312_chinese_nopad_ci"}}, isdefault: 0b00000000},
+	1049: {alias: []collalias{{0b00001100, "greek_general_nopad_ci"}}, isdefault: 0b00000000},
+	1050: {alias: []collalias{{0b00001100, "cp1250_general_nopad_ci"}}, isdefault: 0b00000000},
+	1052: {alias: []collalias{{0b00001100, "gbk_chinese_nopad_ci"}}, isdefault: 0b00000000},
+	1054: {alias: []collalias{{0b00001100, "latin5_turkish_nopad_ci"}}, isdefault: 0b00000000},
+	1056: {alias: []collalias{{0b00001100, "armscii8_general_nopad_ci"}}, isdefault: 0b00000000},
+	1057: {alias: []collalias{{0b00001100, "utf8_general_nopad_ci"}, {0b00001100, "utf8mb3_general_nopad_ci"}}, isdefault: 0b00000000},
+	1059: {alias: []collalias{{0b00001100, "ucs2_general_nopad_ci"}}, isdefault: 0b00000000},
+	1060: {alias: []collalias{{0b00001100, "cp866_general_nopad_ci"}}, isdefault: 0b00000000},
+	1061: {alias: []collalias{{0b00001100, "keybcs2_general_nopad_ci"}}, isdefault: 0b00000000},
+	1062: {alias: []collalias{{0b00001100, "macce_general_nopad_ci"}}, isdefault: 0b00000000},
+	1063: {alias: []collalias{{0b00001100, "macroman_general_nopad_ci"}}, isdefault: 0b00000000},
+	1064: {alias: []collalias{{0b00001100, "cp852_general_nopad_ci"}}, isdefault: 0b00000000},
+	1065: {alias: []collalias{{0b00001100, "latin7_general_nopad_ci"}}, isdefault: 0b00000000},
+	1067: {alias: []collalias{{0b00001100, "macce_nopad_bin"}}, isdefault: 0b00000000},
+	1069: {alias: []collalias{{0b00001100, "utf8mb4_general_nopad_ci"}}, isdefault: 0b00000000},
+	1070: {alias: []collalias{{0b00001100, "utf8mb4_nopad_bin"}}, isdefault: 0b00000000},
+	1071: {alias: []collalias{{0b00001100, "latin1_nopad_bin"}}, isdefault: 0b00000000},
+	1074: {alias: []collalias{{0b00001100, "cp1251_nopad_bin"}}, isdefault: 0b00000000},
+	1075: {alias: []collalias{{0b00001100, "cp1251_general_nopad_ci"}}, isdefault: 0b00000000},
+	1077: {alias: []collalias{{0b00001100, "macroman_nopad_bin"}}, isdefault: 0b00000000},
+	1078: {alias: []collalias{{0b00001100, "utf16_general_nopad_ci"}}, isdefault: 0b00000000},
+	1079: {alias: []collalias{{0b00001100, "utf16_nopad_bin"}}, isdefault: 0b00000000},
+	1080: {alias: []collalias{{0b00001100, "utf16le_general_nopad_ci"}}, isdefault: 0b00000000},
+	1081: {alias: []collalias{{0b00001100, "cp1256_general_nopad_ci"}}, isdefault: 0b00000000},
+	1082: {alias: []collalias{{0b00001100, "cp1257_nopad_bin"}}, isdefault: 0b00000000},
+	1083: {alias: []collalias{{0b00001100, "cp1257_general_nopad_ci"}}, isdefault: 0b00000000},
+	1084: {alias: []collalias{{0b00001100, "utf32_general_nopad_ci"}}, isdefault: 0b00000000},
+	1085: {alias: []collalias{{0b00001100, "utf32_nopad_bin"}}, isdefault: 0b00000000},
+	1086: {alias: []collalias{{0b00001100, "utf16le_nopad_bin"}}, isdefault: 0b00000000},
+	1088: {alias: []collalias{{0b00001100, "armscii8_nopad_bin"}}, isdefault: 0b00000000},
+	1089: {alias: []collalias{{0b00001100, "ascii_nopad_bin"}}, isdefault: 0b00000000},
+	1090: {alias: []collalias{{0b00001100, "cp1250_nopad_bin"}}, isdefault: 0b00000000},
+	1091: {alias: []collalias{{0b00001100, "cp1256_nopad_bin"}}, isdefault: 0b00000000},
+	1092: {alias: []collalias{{0b00001100, "cp866_nopad_bin"}}, isdefault: 0b00000000},
+	1093: {alias: []collalias{{0b00001100, "dec8_nopad_bin"}}, isdefault: 0b00000000},
+	1094: {alias: []collalias{{0b00001100, "greek_nopad_bin"}}, isdefault: 0b00000000},
+	1095: {alias: []collalias{{0b00001100, "hebrew_nopad_bin"}}, isdefault: 0b00000000},
+	1096: {alias: []collalias{{0b00001100, "hp8_nopad_bin"}}, isdefault: 0b00000000},
+	1097: {alias: []collalias{{0b00001100, "keybcs2_nopad_bin"}}, isdefault: 0b00000000},
+	1098: {alias: []collalias{{0b00001100, "koi8r_nopad_bin"}}, isdefault: 0b00000000},
+	1099: {alias: []collalias{{0b00001100, "koi8u_nopad_bin"}}, isdefault: 0b00000000},
+	1101: {alias: []collalias{{0b00001100, "latin2_nopad_bin"}}, isdefault: 0b00000000},
+	1102: {alias: []collalias{{0b00001100, "latin5_nopad_bin"}}, isdefault: 0b00000000},
+	1103: {alias: []collalias{{0b00001100, "latin7_nopad_bin"}}, isdefault: 0b00000000},
+	1104: {alias: []collalias{{0b00001100, "cp850_nopad_bin"}}, isdefault: 0b00000000},
+	1105: {alias: []collalias{{0b00001100, "cp852_nopad_bin"}}, isdefault: 0b00000000},
+	1106: {alias: []collalias{{0b00001100, "swe7_nopad_bin"}}, isdefault: 0b00000000},
+	1107: {alias: []collalias{{0b00001100, "utf8_nopad_bin"}, {0b00001100, "utf8mb3_nopad_bin"}}, isdefault: 0b00000000},
+	1108: {alias: []collalias{{0b00001100, "big5_nopad_bin"}}, isdefault: 0b00000000},
+	1109: {alias: []collalias{{0b00001100, "euckr_nopad_bin"}}, isdefault: 0b00000000},
+	1110: {alias: []collalias{{0b00001100, "gb2312_nopad_bin"}}, isdefault: 0b00000000},
+	1111: {alias: []collalias{{0b00001100, "gbk_nopad_bin"}}, isdefault: 0b00000000},
+	1112: {alias: []collalias{{0b00001100, "sjis_nopad_bin"}}, isdefault: 0b00000000},
+	1113: {alias: []collalias{{0b00001100, "tis620_nopad_bin"}}, isdefault: 0b00000000},
+	1114: {alias: []collalias{{0b00001100, "ucs2_nopad_bin"}}, isdefault: 0b00000000},
+	1115: {alias: []collalias{{0b00001100, "ujis_nopad_bin"}}, isdefault: 0b00000000},
+	1116: {alias: []collalias{{0b00001100, "geostd8_general_nopad_ci"}}, isdefault: 0b00000000},
+	1117: {alias: []collalias{{0b00001100, "geostd8_nopad_bin"}}, isdefault: 0b00000000},
+	1119: {alias: []collalias{{0b00001100, "cp932_japanese_nopad_ci"}}, isdefault: 0b00000000},
+	1120: {alias: []collalias{{0b00001100, "cp932_nopad_bin"}}, isdefault: 0b00000000},
+	1121: {alias: []collalias{{0b00001100, "eucjpms_japanese_nopad_ci"}}, isdefault: 0b00000000},
+	1122: {alias: []collalias{{0b00001100, "eucjpms_nopad_bin"}}, isdefault: 0b00000000},
+	1125: {alias: []collalias{{0b00001100, "utf16_unicode_nopad_ci"}}, isdefault: 0b00000000},
+	1147: {alias: []collalias{{0b00001100, "utf16_unicode_520_nopad_ci"}}, isdefault: 0b00000000},
+	1152: {alias: []collalias{{0b00001100, "ucs2_unicode_nopad_ci"}}, isdefault: 0b00000000},
+	1174: {alias: []collalias{{0b00001100, "ucs2_unicode_520_nopad_ci"}}, isdefault: 0b00000000},
+	1184: {alias: []collalias{{0b00001100, "utf32_unicode_nopad_ci"}}, isdefault: 0b00000000},
+	1206: {alias: []collalias{{0b00001100, "utf32_unicode_520_nopad_ci"}}, isdefault: 0b00000000},
+	1216: {alias: []collalias{{0b00001100, "utf8_unicode_nopad_ci"}, {0b00001100, "utf8mb3_unicode_nopad_ci"}}, isdefault: 0b00000000},
+	1238: {alias: []collalias{{0b00001100, "utf8_unicode_520_nopad_ci"}, {0b00001100, "utf8mb3_unicode_520_nopad_ci"}}, isdefault: 0b00000000},
+	1248: {alias: []collalias{{0b00001100, "utf8mb4_unicode_nopad_ci"}}, isdefault: 0b00000000},
+	1270: {alias: []collalias{{0b00001100, "utf8mb4_unicode_520_nopad_ci"}}, isdefault: 0b00000000},
 }
diff --git a/go/mysql/collations/tools/makecolldata/main.go b/go/mysql/collations/tools/makecolldata/main.go
index 93caacd0ff..212fc5d73a 100644
--- a/go/mysql/collations/tools/makecolldata/main.go
+++ b/go/mysql/collations/tools/makecolldata/main.go
@@ -82,6 +82,10 @@ func loadMysqlMetadata() (all AllMetadata) {
 		}
 		_ = rf.Close()
 
+		if _, aliased := CharsetAliases[meta.Charset]; aliased {
+			meta.Charset = CharsetAliases[meta.Charset]
+		}
+
 		all = append(all, &meta)
 	}
 
diff --git a/go/mysql/collations/tools/makecolldata/mysqlversions.go b/go/mysql/collations/tools/makecolldata/mysqlversions.go
index d3e509e1ec..7bd61862d6 100644
--- a/go/mysql/collations/tools/makecolldata/mysqlversions.go
+++ b/go/mysql/collations/tools/makecolldata/mysqlversions.go
@@ -43,6 +43,13 @@ type alias struct {
 	name string
 }
 
+// CharsetAliases is a list of all aliases that MySQL uses to refer to charsets.
+// As of MySQL 8, all versions of MySQL map the utf8 charset to utf8mb3;
+// this will be changed sometime in the future so it maps to utf8mb4.
+var CharsetAliases = map[string]string{
+	"utf8": "utf8mb3",
+}
+
 func makeversions(output string) {
 	flag.Parse()
 
@@ -79,7 +86,16 @@ func makeversions(output string) {
 				versioninfo[uint(collid)] = vi
 			}
 
-			vi.alias[cols[0]] |= 1 << v
+			collname := cols[0]
+			vi.alias[collname] |= 1 << v
+
+			for from, to := range CharsetAliases {
+				if strings.HasPrefix(collname, from+"_") {
+					aliased := strings.Replace(collname, from+"_", to+"_", 1)
+					vi.alias[aliased] |= 1 << v
+				}
+			}
+
 			switch cols[3] {
 			case "Yes":
 				vi.isdefault |= 1 << v
@@ -102,6 +118,7 @@ func makeversions(output string) {
 
 	var g = codegen.NewGenerator("vitess.io/vitess/go/mysql/collations")
 	g.P("type collver byte")
+	g.P("type collalias struct { mask collver; name string }")
 	g.P("const (")
 	g.P("collverInvalid collver = 0")
 	for n, version := range versions {
@@ -123,8 +140,14 @@ func makeversions(output string) {
 	g.P("default: panic(\"invalid version identifier\")")
 	g.P("}")
 	g.P("}")
+
+	// These are the default aliases for charsets; right now they're common between
+	// all MySQL versions, but this is implemented as a method on `collver` so when
+	// MySQL maps utf8 to utfmb4, we can perform the mapping only for the specific
+	// MySQL version onwards.
+	g.P("func (v collver) charsetAliases() map[string]string { return ", fmt.Sprintf("%#v", CharsetAliases), "}")
 	g.P()
-	g.P("var globalVersionInfo = map[ID]struct{alias map[collver]string; isdefault collver}{")
+	g.P("var globalVersionInfo = map[ID]struct{alias []collalias; isdefault collver}{")
 
 	var sorted []*versionInfo
 	for _, vi := range versioninfo {
@@ -141,9 +164,9 @@ func makeversions(output string) {
 		sort.Slice(reverse, func(i, j int) bool {
 			return reverse[i].name < reverse[j].name
 		})
-		fmt.Fprintf(g, "%d: {alias: map[collver]string{", vi.id)
+		fmt.Fprintf(g, "%d: {alias: []collalias{", vi.id)
 		for _, a := range reverse {
-			fmt.Fprintf(g, "0b%08b: %q,", a.mask, a.name)
+			fmt.Fprintf(g, "{0b%08b, %q},", a.mask, a.name)
 		}
 		fmt.Fprintf(g, "}, isdefault: 0b%08b},\n", vi.isdefault)
 	}
diff --git a/go/vt/schemadiff/mysql.go b/go/vt/schemadiff/mysql.go
index 74c6a11622..768714af7a 100644
--- a/go/vt/schemadiff/mysql.go
+++ b/go/vt/schemadiff/mysql.go
@@ -21,10 +21,6 @@ var engineCasing = map[string]string{
 	"MYISAM": "MyISAM",
 }
 
-var charsetAliases = map[string]string{
-	"utf8": "utf8mb3",
-}
-
 var integralTypes = map[string]bool{
 	"tinyint":   true,
 	"smallint":  true,
diff --git a/go/vt/schemadiff/table.go b/go/vt/schemadiff/table.go
index 7b30d3d1e1..ee0d79123d 100644
--- a/go/vt/schemadiff/table.go
+++ b/go/vt/schemadiff/table.go
@@ -239,7 +239,7 @@ func NewCreateTableEntity(c *sqlparser.CreateTable) *CreateTableEntity {
 // - table option case (upper/lower/special)
 // The function returns this receiver as courtesy
 func (c *CreateTableEntity) normalize() *CreateTableEntity {
-	c.normalizeUnnamedKeys()
+	c.normalizeKeys()
 	c.normalizeUnnamedConstraints()
 	c.normalizeTableOptions()
 	c.normalizeColumnOptions()
@@ -252,7 +252,7 @@ func (c *CreateTableEntity) normalizeTableOptions() {
 		switch strings.ToUpper(opt.Name) {
 		case "CHARSET", "COLLATE":
 			opt.String = strings.ToLower(opt.String)
-			if charset, ok := charsetAliases[opt.String]; ok {
+			if charset, ok := collationEnv.CharsetAlias(opt.String); ok {
 				opt.String = charset
 			}
 		case "ENGINE":
@@ -280,12 +280,6 @@ func defaultCharset() string {
 }
 
 func defaultCharsetCollation(charset string) string {
-	// The collation tables are based on utf8, not the utf8mb3 alias.
-	// We already normalize to utf8mb3 to be explicit, so we have to
-	// map it back here to find the default collation for utf8mb3.
-	if charset == "utf8mb3" {
-		charset = "utf8"
-	}
 	collation := collationEnv.DefaultCollationForCharset(charset)
 	if collation == nil {
 		return ""
@@ -346,9 +340,14 @@ func (c *CreateTableEntity) normalizeColumnOptions() {
 			}
 		}
 
+		if col.Type.Options.Invisible != nil && !*col.Type.Options.Invisible {
+			// If a column is marked `VISIBLE`, that's the same as the default.
+			col.Type.Options.Invisible = nil
+		}
+
 		// Map any charset aliases to the real charset. This applies mainly right
 		// now to utf8 being an alias for utf8mb3.
-		if charset, ok := charsetAliases[col.Type.Charset]; ok {
+		if charset, ok := collationEnv.CharsetAlias(col.Type.Charset); ok {
 			col.Type.Charset = charset
 		}
 
@@ -421,7 +420,7 @@ func (c *CreateTableEntity) normalizePartitionOptions() {
 	}
 }
 
-func (c *CreateTableEntity) normalizeUnnamedKeys() {
+func (c *CreateTableEntity) normalizeKeys() {
 	// let's ensure all keys have names
 	keyNameExists := map[string]bool{}
 	// first, we iterate and take note for all keys that do already have names
@@ -430,8 +429,8 @@ func (c *CreateTableEntity) normalizeUnnamedKeys() {
 			keyNameExists[name] = true
 		}
 	}
-	// now, let's look at keys that do not have names, and assign them new names
 	for _, key := range c.CreateTable.TableSpec.Indexes {
+		// now, let's look at keys that do not have names, and assign them new names
 		if name := key.Info.Name.String(); name == "" {
 			// we know there must be at least one column covered by this key
 			var colName string
@@ -459,6 +458,21 @@ func (c *CreateTableEntity) normalizeUnnamedKeys() {
 			key.Info.Name = sqlparser.NewColIdent(suggestedKeyName)
 			keyNameExists[suggestedKeyName] = true
 		}
+
+		// Drop options that are the same as the default.
+		keptOptions := make([]*sqlparser.IndexOption, 0, len(key.Options))
+		for _, option := range key.Options {
+			switch strings.ToUpper(option.Name) {
+			case "USING":
+				if strings.EqualFold(option.String, "BTREE") {
+					continue
+				}
+			case "VISIBLE":
+				continue
+			}
+			keptOptions = append(keptOptions, option)
+		}
+		key.Options = keptOptions
 	}
 }
 
diff --git a/go/vt/schemadiff/view.go b/go/vt/schemadiff/view.go
index 4394533079..c310ab6601 100644
--- a/go/vt/schemadiff/view.go
+++ b/go/vt/schemadiff/view.go
@@ -17,6 +17,8 @@ limitations under the License.
 package schemadiff
 
 import (
+	"strings"
+
 	"vitess.io/vitess/go/vt/sqlparser"
 )
 
@@ -195,7 +197,20 @@ type CreateViewEntity struct {
 }
 
 func NewCreateViewEntity(c *sqlparser.CreateView) *CreateViewEntity {
-	return &CreateViewEntity{CreateView: *c}
+	entity := &CreateViewEntity{CreateView: *c}
+	entity.normalize()
+	return entity
+}
+
+func (c *CreateViewEntity) normalize() {
+	// Drop the default algorithm
+	if strings.EqualFold(c.CreateView.Algorithm, "undefined") {
+		c.CreateView.Algorithm = ""
+	}
+	// Drop the default security model
+	if strings.EqualFold(c.CreateView.Security, "definer") {
+		c.CreateView.Security = ""
+	}
 }
 
 // Name implements Entity interface
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/mysql/collations/golden_test.go b/go/mysql/collations/golden_test.go
index f6323f1216..9d38e690da 100644
--- a/go/mysql/collations/golden_test.go
+++ b/go/mysql/collations/golden_test.go
@@ -124,18 +124,19 @@ func XTestSupportTables(t *testing.T) {
 		if coll == nil {
 			vdata := globalVersionInfo[id]
 
-			var collname string
-			for _, name := range vdata.alias {
-				collname = name
+			var collnames []string
+			for _, alias := range vdata.alias {
+				collnames = append(collnames, alias.name)
 				break
 			}
+			collname := strings.Join(collnames, ",")
 			parts := strings.Split(collname, "_")
 
 			fmt.Fprintf(out, "| %s | %s", collname, parts[0])
 			for _, env := range envs {
 				var supported bool
-				for v := range vdata.alias {
-					if v&env.version != 0 {
+				for _, alias := range vdata.alias {
+					if alias.mask&env.version != 0 {
 						supported = true
 						break
 					}
diff --git a/go/mysql/endtoend/query_test.go b/go/mysql/endtoend/query_test.go
index adcab4dbeb..1118c46f85 100644
--- a/go/mysql/endtoend/query_test.go
+++ b/go/mysql/endtoend/query_test.go
@@ -35,7 +35,7 @@ import (
 
 func columnSize(cs collations.ID, size uint32) uint32 {
 	// utf8_general_ci results in smaller max column sizes because MySQL 5.7 is silly
-	if collations.Local().LookupByID(cs).Charset().Name() == "utf8" {
+	if collations.Local().LookupByID(cs).Charset().Name() == "utf8mb3" {
 		return size * 3 / 4
 	}
 	return size
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/schemadiff/table_test.go b/go/vt/schemadiff/table_test.go
index 87f8134470..c54f363de9 100644
--- a/go/vt/schemadiff/table_test.go
+++ b/go/vt/schemadiff/table_test.go
@@ -1229,6 +1229,36 @@ func TestNormalize(t *testing.T) {
 			from: "create table t1 (id int primary key, i int, foreign key (i) references parent(id))",
 			to:   "CREATE TABLE `t1` (\n\t`id` int PRIMARY KEY,\n\t`i` int,\n\tCONSTRAINT `t1_ibfk_1` FOREIGN KEY (`i`) REFERENCES `parent` (`id`)\n)",
 		},
+		{
+			name: "drops default index type",
+			from: "create table t (id int primary key, i1 int, key i1_idx(i1) using btree)",
+			to:   "CREATE TABLE `t` (\n\t`id` int PRIMARY KEY,\n\t`i1` int,\n\tKEY `i1_idx` (`i1`)\n)",
+		},
+		{
+			name: "does not drop non-default index type",
+			from: "create table t (id int primary key, i1 int, key i1_idx(i1) using hash)",
+			to:   "CREATE TABLE `t` (\n\t`id` int PRIMARY KEY,\n\t`i1` int,\n\tKEY `i1_idx` (`i1`) USING HASH\n)",
+		},
+		{
+			name: "drops default index visibility",
+			from: "create table t (id int primary key, i1 int, key i1_idx(i1) visible)",
+			to:   "CREATE TABLE `t` (\n\t`id` int PRIMARY KEY,\n\t`i1` int,\n\tKEY `i1_idx` (`i1`)\n)",
+		},
+		{
+			name: "drops non-default index visibility",
+			from: "create table t (id int primary key, i1 int, key i1_idx(i1) invisible)",
+			to:   "CREATE TABLE `t` (\n\t`id` int PRIMARY KEY,\n\t`i1` int,\n\tKEY `i1_idx` (`i1`) INVISIBLE\n)",
+		},
+		{
+			name: "drops default column visibility",
+			from: "create table t (id int primary key, i1 int visible)",
+			to:   "CREATE TABLE `t` (\n\t`id` int PRIMARY KEY,\n\t`i1` int\n)",
+		},
+		{
+			name: "drops non-default column visibility",
+			from: "create table t (id int primary key, i1 int invisible)",
+			to:   "CREATE TABLE `t` (\n\t`id` int PRIMARY KEY,\n\t`i1` int INVISIBLE\n)",
+		},
 	}
 	for _, ts := range tt {
 		t.Run(ts.name, func(t *testing.T) {
diff --git a/go/vt/schemadiff/view_test.go b/go/vt/schemadiff/view_test.go
index c38e179cc6..60f1de4683 100644
--- a/go/vt/schemadiff/view_test.go
+++ b/go/vt/schemadiff/view_test.go
@@ -204,3 +204,48 @@ func TestCreateViewDiff(t *testing.T) {
 		})
 	}
 }
+
+func TestNormalizeView(t *testing.T) {
+	tt := []struct {
+		name string
+		from string
+		to   string
+	}{
+		{
+			name: "basic view",
+			from: "create view v1 as select a, b, c from t",
+			to:   "CREATE VIEW `v1` AS SELECT `a`, `b`, `c` FROM `t`",
+		},
+		{
+			name: "default algorithm",
+			from: "create algorithm=undefined view v1 as select a, b, c from t",
+			to:   "CREATE VIEW `v1` AS SELECT `a`, `b`, `c` FROM `t`",
+		},
+		{
+			name: "non-default algorithm",
+			from: "create algorithm=merge view v1 as select a, b, c from t",
+			to:   "CREATE ALGORITHM = merge VIEW `v1` AS SELECT `a`, `b`, `c` FROM `t`",
+		},
+		{
+			name: "default security model",
+			from: "create sql security DEFINER view v1 as select a, b, c from t",
+			to:   "CREATE VIEW `v1` AS SELECT `a`, `b`, `c` FROM `t`",
+		},
+		{
+			name: "non-default security model",
+			from: "create sql security invoker view v1 as select a, b, c from t",
+			to:   "CREATE SQL SECURITY INVOKER VIEW `v1` AS SELECT `a`, `b`, `c` FROM `t`",
+		},
+	}
+	for _, ts := range tt {
+		t.Run(ts.name, func(t *testing.T) {
+			stmt, err := sqlparser.ParseStrictDDL(ts.from)
+			require.NoError(t, err)
+			fromCreateView, ok := stmt.(*sqlparser.CreateView)
+			require.True(t, ok)
+
+			from := NewCreateViewEntity(fromCreateView)
+			assert.Equal(t, ts.to, sqlparser.CanonicalString(from))
+		})
+	}
+}
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"