Repository: vitessio/vitess
Pull request #10180 (2022-04-29 19:33:51)

[vtadmin-web] Remove placeholder links from NavRail
## Description

- Removes the placeholder links in the NavRail, since they just 404. :) 

- Uses text instead of SVG icons for the single-character NavRail "hotkeys"; scare quotes since they aren't bound to any keyboard shortcuts (yet). I opted for this change as I moved "Workflows" under the other infrastructure links (i.e., those with counts) but the previous icon, a wrench, looked out of place... and exporting icons from Figma is sort of a pain. (Even if we switch to something like fontawesome for our icons, I think rendering these "icons" with CSS is still more lightweight + consistent than messing around with SVG!) 

- Deletes the `letter*.svg` files that we're no longer using. 

Before and after, for comparison. The new "icons" are noticeably lighter, so I'm definitely open to opinions. :) 

**Before:**
<img width="1904" alt="Screen Shot 2022-04-29 at 3 21 01 PM" src="https://user-images.githubusercontent.com/855595/166057236-ecae1bb5-f9cf-45fe-9365-89c5cbd1dbc7.png">

**After:**
<img width="1904" alt="Screen Shot 2022-04-29 at 3 20 54 PM" src="https://user-images.githubusercontent.com/855595/166057230-7b508fb0-8d8b-43e6-a353-7e8ba8dcf0f8.png">


## Related Issue(s)

N/A

## Checklist

-   [x] "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

## Deployment Notes

<!-- Notes regarding deployment of the contained body of work. These should note any db migrations, etc. -->



Patch:
diff --git a/.github/workflows/comment_pull_request.yml b/.github/workflows/comment_pull_request.yml
index 8abd279ce9..25b1ebfcb9 100644
--- a/.github/workflows/comment_pull_request.yml
+++ b/.github/workflows/comment_pull_request.yml
@@ -8,7 +8,7 @@ permissions:
   pull-requests: write
 
 jobs:
-    review_checklist:
+  review_checklist:
     if: ${{ !github.event.pull_request.draft }}
     runs-on: ubuntu-latest
     name: Comment Pull Request with the Review Checklist
diff --git a/go.mod b/go.mod
index 9e490ec693..61782c087d 100644
--- a/go.mod
+++ b/go.mod
@@ -86,6 +86,7 @@ require (
 	github.com/uber-go/atomic v1.4.0 // indirect
 	github.com/uber/jaeger-client-go v2.16.0+incompatible
 	github.com/uber/jaeger-lib v2.4.1+incompatible // indirect
+	github.com/yudai/golcs v0.0.0-20170316035057-ecda9a501e82
 	github.com/z-division/go-zookeeper v0.0.0-20190128072838-6d7457066b9b
 	go.etcd.io/etcd/api/v3 v3.5.0
 	go.etcd.io/etcd/client/pkg/v3 v3.5.0
diff --git a/go.sum b/go.sum
index fd975af86c..77d6b76715 100644
--- a/go.sum
+++ b/go.sum
@@ -759,6 +759,8 @@ github.com/valyala/fasttemplate v1.0.1/go.mod h1:UQGH1tvbgY+Nz5t2n7tXsz52dQxojPU
 github.com/vektah/gqlparser v1.1.2/go.mod h1:1ycwN7Ij5njmMkPPAOaRFY4rET2Enx7IkVv3vaXspKw=
 github.com/xiang90/probing v0.0.0-20190116061207-43a291ad63a2/go.mod h1:UETIi67q53MR2AWcXfiuqkDkRtnGDLqkBTpCHuJHxtU=
 github.com/xordataexchange/crypt v0.0.3-0.20170626215501-b2862e3d0a77/go.mod h1:aYKd//L2LvnjZzWKhF00oedf4jCCReLcmhLdhm1A27Q=
+github.com/yudai/golcs v0.0.0-20170316035057-ecda9a501e82 h1:BHyfKlQyqbsFN5p3IfnEUduWvb9is428/nNb5L3U01M=
+github.com/yudai/golcs v0.0.0-20170316035057-ecda9a501e82/go.mod h1:lgjkn3NuSvDfVJdfcVVdX+jpBxNmX4rDAzaS45IcYoM=
 github.com/yuin/goldmark v1.1.25/go.mod h1:3hX8gzYuyVAZsxl0MRgGTJEmQBFcNTphYh9decYSb74=
 github.com/yuin/goldmark v1.1.27/go.mod h1:3hX8gzYuyVAZsxl0MRgGTJEmQBFcNTphYh9decYSb74=
 github.com/yuin/goldmark v1.1.32/go.mod h1:3hX8gzYuyVAZsxl0MRgGTJEmQBFcNTphYh9decYSb74=
diff --git a/go/vt/schemadiff/schema.go b/go/vt/schemadiff/schema.go
index e5bd4116fc..916a1d37fc 100644
--- a/go/vt/schemadiff/schema.go
+++ b/go/vt/schemadiff/schema.go
@@ -17,6 +17,7 @@ limitations under the License.
 package schemadiff
 
 import (
+	"bytes"
 	"io"
 	"sort"
 	"strings"
@@ -134,6 +135,8 @@ func getViewDependentTableNames(createView *sqlparser.CreateView) (names []strin
 // normalize is called as part of Schema creation process. The user may only get a hold of normalized schema.
 // It validates some cross-entity constraints, and orders entity based on dependencies (e.g. tables, views that read from tables, 2nd level views, etc.)
 func (s *Schema) normalize() error {
+	s.named = map[string]Entity{}
+	s.sorted = []Entity{}
 	// Verify no two entities share same name
 	for _, t := range s.tables {
 		name := t.Name()
@@ -282,3 +285,156 @@ func (s *Schema) Diff(other *Schema, hints *DiffHints) (diffs []EntityDiff, err
 	}
 	return diffs, err
 }
+
+// Entity returns an entity by name, or nil if nonexistent
+func (s *Schema) Entity(name string) Entity {
+	return s.named[name]
+}
+
+// ToStatements returns an ordered list of statements which can be applied to create the schema
+func (s *Schema) ToStatements() []sqlparser.Statement {
+	stmts := []sqlparser.Statement{}
+	for _, e := range s.Entities() {
+		stmts = append(stmts, e.Create().Statement())
+	}
+	return stmts
+}
+
+// ToQueries returns an ordered list of queries which can be applied to create the schema
+func (s *Schema) ToQueries() []string {
+	queries := []string{}
+	for _, e := range s.Entities() {
+		queries = append(queries, e.Create().CanonicalStatementString())
+	}
+	return queries
+}
+
+// ToSQL returns a SQL blob with ordered sequence of queries which can be applied to create the schema
+func (s *Schema) ToSQL() string {
+	var buf bytes.Buffer
+	for _, query := range s.ToQueries() {
+		buf.WriteString(query)
+		buf.WriteString(";\n")
+	}
+	return buf.String()
+}
+
+// apply attempts to apply given list of diffs to this object.
+// These diffs are CREATE/DROP/ALTER TABLE/VIEW.
+func (s *Schema) apply(diffs []EntityDiff) error {
+	for _, diff := range diffs {
+		switch diff := diff.(type) {
+		case *CreateTableEntityDiff:
+			// We expect the table to not exist
+			name := diff.createTable.Table.Name.String()
+			if _, ok := s.named[name]; ok {
+				return ErrApplyDuplicateTableOrView
+			}
+			s.tables = append(s.tables, &CreateTableEntity{CreateTable: *diff.createTable})
+			_, s.named[name] = diff.Entities()
+		case *CreateViewEntityDiff:
+			// We expect the view to not exist
+			name := diff.createView.ViewName.Name.String()
+			if _, ok := s.named[name]; ok {
+				return ErrApplyDuplicateTableOrView
+			}
+			s.views = append(s.views, &CreateViewEntity{CreateView: *diff.createView})
+			_, s.named[name] = diff.Entities()
+		case *DropTableEntityDiff:
+			// We expect the table to exist
+			found := false
+			for i, t := range s.tables {
+				if name := t.Table.Name.String(); name == diff.from.Table.Name.String() {
+					s.tables = append(s.tables[0:i], s.tables[i+1:]...)
+					delete(s.named, name)
+					found = true
+					break
+				}
+			}
+			if !found {
+				return ErrApplyTableNotFound
+			}
+		case *DropViewEntityDiff:
+			// We expect the view to exist
+			found := false
+			for i, v := range s.views {
+				if name := v.ViewName.Name.String(); name == diff.from.ViewName.Name.String() {
+					s.views = append(s.views[0:i], s.views[i+1:]...)
+					delete(s.named, name)
+					found = true
+					break
+				}
+			}
+			if !found {
+				return ErrApplyViewNotFound
+			}
+		case *AlterTableEntityDiff:
+			// We expect the table to exist
+			found := false
+			for i, t := range s.tables {
+				if name := t.Table.Name.String(); name == diff.from.Table.Name.String() {
+					to, err := t.Apply(diff)
+					if err != nil {
+						return err
+					}
+					toCreateTableEntity, ok := to.(*CreateTableEntity)
+					if !ok {
+						return ErrEntityTypeMismatch
+					}
+					s.tables[i] = toCreateTableEntity
+					s.named[name] = toCreateTableEntity
+					found = true
+					break
+				}
+			}
+			if !found {
+				return ErrApplyTableNotFound
+			}
+		case *AlterViewEntityDiff:
+			// We expect the view to exist
+			found := false
+			for i, v := range s.views {
+				if name := v.ViewName.Name.String(); name == diff.from.ViewName.Name.String() {
+					to, err := v.Apply(diff)
+					if err != nil {
+						return err
+					}
+					toCreateViewEntity, ok := to.(*CreateViewEntity)
+					if !ok {
+						return ErrEntityTypeMismatch
+					}
+					s.views[i] = toCreateViewEntity
+					s.named[name] = toCreateViewEntity
+					found = true
+					break
+				}
+			}
+			if !found {
+				return ErrApplyViewNotFound
+			}
+		default:
+			return ErrUnsupportedApplyOperation
+		}
+	}
+	if err := s.normalize(); err != nil {
+		return err
+	}
+	return nil
+}
+
+// Apply attempts to apply given list of diffs to the schema described by this object.
+// These diffs are CREATE/DROP/ALTER TABLE/VIEW.
+// The operation does not modify this object. Instead, if successful, a new (modified) Schema is returned.
+func (s *Schema) Apply(diffs []EntityDiff) (*Schema, error) {
+	dup, err := NewSchemaFromStatements(s.ToStatements())
+	if err != nil {
+		return nil, err
+	}
+	for k, v := range s.named {
+		dup.named[k] = v
+	}
+	if err := dup.apply(diffs); err != nil {
+		return nil, err
+	}
+	return dup, nil
+}
diff --git a/go/vt/schemadiff/table.go b/go/vt/schemadiff/table.go
index 939b2dac1c..3528c651d1 100644
--- a/go/vt/schemadiff/table.go
+++ b/go/vt/schemadiff/table.go
@@ -17,9 +17,14 @@ limitations under the License.
 package schemadiff
 
 import (
+	"fmt"
 	"strconv"
 	"strings"
 
+	"github.com/pkg/errors"
+
+	golcs "github.com/yudai/golcs"
+
 	"vitess.io/vitess/go/vt/sqlparser"
 )
 
@@ -76,7 +81,7 @@ func (d *CreateTableEntityDiff) IsEmpty() bool {
 
 // IsEmpty implements EntityDiff
 func (d *CreateTableEntityDiff) Entities() (from Entity, to Entity) {
-	return nil, &CreateTableEntity{CreateTable: *d.createTable}
+	return nil, NewCreateTableEntity(d.createTable)
 }
 
 // Statement implements EntityDiff
@@ -149,7 +154,38 @@ type CreateTableEntity struct {
 }
 
 func NewCreateTableEntity(c *sqlparser.CreateTable) *CreateTableEntity {
-	return &CreateTableEntity{CreateTable: *c}
+	entity := &CreateTableEntity{CreateTable: *c}
+	entity.normalize()
+	return entity
+}
+
+// normalize normalizes table definition:
+// - setting names to all keys
+func (c *CreateTableEntity) normalize() {
+	// let's verify all keys have names
+	keyNameExists := map[string]bool{}
+	// first, we iterate and take note for all keys that do aleady have names
+	for _, key := range c.CreateTable.TableSpec.Indexes {
+		if name := key.Info.Name.String(); name != "" {
+			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 {
+		if name := key.Info.Name.String(); name == "" {
+			// we know there must be at least one column covered by this key
+			colName := key.Columns[0].Column.String()
+			// like MySQL, we first try to call our index by the name of the first column:
+			suggestedKeyName := colName
+			// now let's see if that name is taken; if it is, enumerate new news until we find a free name
+			for enumerate := 2; keyNameExists[suggestedKeyName]; enumerate++ {
+				suggestedKeyName = fmt.Sprintf("%s_%d", colName, enumerate)
+			}
+			// OK we found a free slot!
+			key.Info.Name = sqlparser.NewColIdent(suggestedKeyName)
+			keyNameExists[suggestedKeyName] = true
+		}
+	}
 }
 
 // Name implements Entity interface
@@ -275,6 +311,46 @@ func (c *CreateTableEntity) diffTableCharset(
 	return ""
 }
 
+// isDefaultTableOptionValue sees if the value for a TableOption is also its default value
+func isDefaultTableOptionValue(option *sqlparser.TableOption) bool {
+	switch strings.ToUpper(option.Name) {
+	case "CHECKSUM":
+		return sqlparser.String(option.Value) == "0"
+	case "COMMENT":
+		return option.String == ""
+	case "COMPRESSION":
+		return sqlparser.String(option.Value) == "" || sqlparser.String(option.Value) == "''"
+	case "CONNECTION":
+		return sqlparser.String(option.Value) == "" || sqlparser.String(option.Value) == "''"
+	case "DATA DIRECTORY":
+		return sqlparser.String(option.Value) == "" || sqlparser.String(option.Value) == "''"
+	case "DELAY_KEY_WRITE":
+		return sqlparser.String(option.Value) == "0"
+	case "ENCRYPTION":
+		return sqlparser.String(option.Value) == "N"
+	case "INDEX DIRECTORY":
+		return sqlparser.String(option.Value) == "" || sqlparser.String(option.Value) == "''"
+	case "KEY_BLOCK_SIZE":
+		return sqlparser.String(option.Value) == "0"
+	case "MAX_ROWS":
+		return sqlparser.String(option.Value) == "0"
+	case "MIN_ROWS":
+		return sqlparser.String(option.Value) == "0"
+	case "PACK_KEYS":
+		return option.String == "DEFAULT"
+	case "ROW_FORMAT":
+		return option.String == "DEFAULT"
+	case "STATS_AUTO_RECALC":
+		return option.String == "DEFAULT"
+	case "STATS_PERSISTENT":
+		return option.String == "DEFAULT"
+	case "STATS_SAMPLE_PAGES":
+		return option.String == "DEFAULT"
+	default:
+		return false
+	}
+}
+
 func (c *CreateTableEntity) diffOptions(alterTable *sqlparser.AlterTable,
 	t1Options sqlparser.TableOptions,
 	t2Options sqlparser.TableOptions,
@@ -630,44 +706,37 @@ func (c *CreateTableEntity) diffKeys(alterTable *sqlparser.AlterTable,
 	}
 }
 
-// evaluateColumnReordering produces a minimal uni-directional reordering set of columns. To elaborate:
+// evaluateColumnReordering produces a minimal reordering set of columns. To elaborate:
 // The function receives two sets of columns. the two must be permutations of one another. Specifically,
 // these are the columns shared between the from&to tables.
-// The function evaluates the minimal number of steps such that:
-// - each steps reorders a column, specificly moving it backwards (never forward)
-// - the final set of steps permutate the "from" columns order in to the "to" columns order
+// The function uses longest-common-subsequence (lcs) algorithm to compute which columns should not be moved.
+// any column not in the lcs need to be reordered.
+// The function a map of column names that need to be reordered, and the index into which they are reordered.
 func evaluateColumnReordering(t1SharedColumns, t2SharedColumns []*sqlparser.ColumnDefinition) map[string]int {
 	minimalColumnReordering := map[string]int{}
-	// buf is an ever changing space. It begins with the "from" set of columns.
-	// In neach step of th ealgorithm we will remove one element from the buffer. So each
-	// step the buffer will become smaller, with less work to be done
-	buf := t1SharedColumns[:]
-	colIndexInBuf := func(name string) int {
-		for i := range buf {
-			if buf[i].Name.String() == name {
-				return i
-			}
-		}
-		return -1
+
+	t1SharedColNames := []interface{}{}
+	for _, col := range t1SharedColumns {
+		t1SharedColNames = append(t1SharedColNames, col.Name.String())
+	}
+	t2SharedColNames := []interface{}{}
+	for _, col := range t2SharedColumns {
+		t2SharedColNames = append(t2SharedColNames, col.Name.String())
+	}
+
+	lcs := golcs.New(t1SharedColNames, t2SharedColNames)
+	lcsNames := map[string]bool{}
+	for _, v := range lcs.Values() {
+		lcsNames[v.(string)] = true
 	}
-	// t2Col is our desired state. It's the "to" table's ordered list of columns
 	for i, t2Col := range t2SharedColumns {
-		// for each position in the target list of columns, we check whether we need to
-		// reorder a column.
-		// t2ColName is the desired column name at this position:
 		t2ColName := t2Col.Name.String()
-		if t2ColName == buf[0].Name.String() {
-			// no need to reorder this column. continue
-			buf = buf[1:]
-			continue
+		// see if this column is in longest common subsequence. If so, no need to reorder it. If not, it must be reordered.
+		if _, ok := lcsNames[t2ColName]; !ok {
+			minimalColumnReordering[t2ColName] = i
 		}
-		// we already know where we want this column: in position i, and we know that
-		// because we iterate t2SharedColumns
-		minimalColumnReordering[t2ColName] = i
-		// Remove the column from buf. This makes buf one element shorter with less work for next steps
-		colIndex := colIndexInBuf(t2ColName)
-		buf = append(buf[0:colIndex], buf[colIndex+1:]...)
 	}
+
 	return minimalColumnReordering
 }
 
@@ -799,3 +868,225 @@ func (c *CreateTableEntity) Drop() EntityDiff {
 	}
 	return &DropTableEntityDiff{from: c, dropTable: dropTable}
 }
+
+// apply attempts to apply an ALTER TABLE diff onto this entity's table definition.
+// supported modifications are only those created by schemadiff's Diff() function.
+func (c *CreateTableEntity) apply(diff *AlterTableEntityDiff) error {
+	if spec := diff.alterTable.PartitionSpec; spec != nil {
+		switch {
+		case spec.Action == sqlparser.RemoveAction && spec.IsAll:
+			// Remove partitioning
+			c.TableSpec.PartitionOption = nil
+		default:
+			return errors.Wrap(ErrUnsupportedApplyOperation, sqlparser.String(spec))
+		}
+	}
+	if diff.alterTable.PartitionOption != nil {
+		// Specify new spec:
+		c.CreateTable.TableSpec.PartitionOption = diff.alterTable.PartitionOption
+	}
+	// reorderColumn attempts to reorder column that is right now in position 'colIndex',
+	// based on its FIRST or AFTER specs (if any)
+	reorderColumn := func(colIndex int, first bool, after *sqlparser.ColName) error {
+		var newCols []*sqlparser.ColumnDefinition // nil
+		col := c.TableSpec.Columns[colIndex]
+		switch {
+		case first:
+			newCols = append(newCols, col)
+			newCols = append(newCols, c.TableSpec.Columns[0:colIndex]...)
+			newCols = append(newCols, c.TableSpec.Columns[colIndex+1:]...)
+		case after != nil:
+			afterColFound := false
+			// look for the AFTER column; it has to exist!
+			for a, afterCol := range c.TableSpec.Columns {
+				if afterCol.Name.String() == after.Name.String() {
+					if colIndex < a {
+						// moving column i to the right
+						newCols = append(newCols, c.TableSpec.Columns[0:colIndex]...)
+						newCols = append(newCols, c.TableSpec.Columns[colIndex+1:a+1]...)
+						newCols = append(newCols, col)
+						newCols = append(newCols, c.TableSpec.Columns[a+1:]...)
+					} else {
+						// moving column i to the left
+						newCols = append(newCols, c.TableSpec.Columns[0:a+1]...)
+						newCols = append(newCols, col)
+						newCols = append(newCols, c.TableSpec.Columns[a+1:colIndex]...)
+						newCols = append(newCols, c.TableSpec.Columns[colIndex+1:]...)
+					}
+					afterColFound = true
+					break
+				}
+			}
+			if !afterColFound {
+				return errors.Wrap(ErrApplyColumnNotFound, after.Name.String())
+			}
+		default:
+			// no change in position
+		}
+
+		if newCols != nil {
+			c.TableSpec.Columns = newCols
+		}
+		return nil
+	}
+
+	// apply a single AlterOption; only supported types are those generated by Diff()
+	applyAlterOption := func(opt sqlparser.AlterOption) error {
+		switch opt := opt.(type) {
+		case *sqlparser.DropKey:
+			// applies to either indexes or FK constraints
+			// we expect the named key to be found
+			found := false
+			switch opt.Type {
+			case sqlparser.NormalKeyType, sqlparser.PrimaryKeyType:
+				for i, index := range c.TableSpec.Indexes {
+					if index.Info.Name.String() == opt.Name.String() {
+						found = true
+						c.TableSpec.Indexes = append(c.TableSpec.Indexes[0:i], c.TableSpec.Indexes[i+1:]...)
+						break
+					}
+				}
+			case sqlparser.ForeignKeyType:
+				for i, constraint := range c.TableSpec.Constraints {
+					if constraint.Name.String() == opt.Name.String() {
+						found = true
+						c.TableSpec.Constraints = append(c.TableSpec.Constraints[0:i], c.TableSpec.Constraints[i+1:]...)
+						break
+					}
+				}
+			default:
+				return errors.Wrap(ErrUnsupportedApplyOperation, sqlparser.String(opt))
+			}
+			if !found {
+				return errors.Wrap(ErrApplyKeyNotFound, opt.Name.String())
+			}
+		case *sqlparser.AddIndexDefinition:
+			// validate no existing key by same name
+			for _, index := range c.TableSpec.Indexes {
+				if index.Info.Name.String() == opt.IndexDefinition.Info.Name.String() {
+					return errors.Wrap(ErrApplyDuplicateKey, opt.IndexDefinition.Info.Name.String())
+				}
+			}
+			c.TableSpec.Indexes = append(c.TableSpec.Indexes, opt.IndexDefinition)
+		case *sqlparser.AddConstraintDefinition:
+			// validate no existing constraint by same name
+			for _, c := range c.TableSpec.Constraints {
+				if c.Name.String() == opt.ConstraintDefinition.Name.String() {
+					return errors.Wrap(ErrApplyDuplicateConstraint, opt.ConstraintDefinition.Name.String())
+				}
+			}
+			c.TableSpec.Constraints = append(c.TableSpec.Constraints, opt.ConstraintDefinition)
+		case *sqlparser.DropColumn:
+			// we expect the column to exist
+			found := false
+			for i, col := range c.TableSpec.Columns {
+				if col.Name.String() == opt.Name.Name.String() {
+					found = true
+					c.TableSpec.Columns = append(c.TableSpec.Columns[0:i], c.TableSpec.Columns[i+1:]...)
+					break
+				}
+			}
+			if !found {
+				return errors.Wrap(ErrApplyColumnNotFound, opt.Name.Name.String())
+			}
+		case *sqlparser.AddColumns:
+			if len(opt.Columns) != 1 {
+				// our Diff only ever generates a single column per AlterOption
+				return errors.Wrap(ErrUnsupportedApplyOperation, sqlparser.String(opt))
+			}
+			// validate no column by same name
+			addedCol := opt.Columns[0]
+			for _, col := range c.TableSpec.Columns {
+				if col.Name.String() == addedCol.Name.String() {
+					return errors.Wrap(ErrApplyDuplicateColumn, addedCol.Name.String())
+				}
+			}
+			c.TableSpec.Columns = append(c.TableSpec.Columns, addedCol)
+			// see if we need to position it anywhere other than end of table
+			if err := reorderColumn(len(c.TableSpec.Columns)-1, opt.First, opt.After); err != nil {
+				return err
+			}
+		case *sqlparser.ModifyColumn:
+			// we expect the column to exist
+			found := false
+			for i, col := range c.TableSpec.Columns {
+				if col.Name.String() == opt.NewColDefinition.Name.String() {
+					found = true
+					// redefine. see if we need to position it anywhere other than end of table
+					c.TableSpec.Columns[i] = opt.NewColDefinition
+					if err := reorderColumn(i, opt.First, opt.After); err != nil {
+						return err
+					}
+					break
+				}
+			}
+			if !found {
+				return errors.Wrap(ErrApplyColumnNotFound, opt.NewColDefinition.Name.String())
+			}
+		case sqlparser.TableOptions:
+			// Apply table options. Options that have their DEFAULT value are actually remvoed.
+			for _, option := range opt {
+				func() {
+					for i, existingOption := range c.TableSpec.Options {
+						if option.Name == existingOption.Name {
+							if isDefaultTableOptionValue(option) {
+								// remove the option
+								c.TableSpec.Options = append(c.TableSpec.Options[0:i], c.TableSpec.Options[i+1:]...)
+							} else {
+								c.TableSpec.Options[i] = option
+							}
+							// option found. No need for further iteration.
+							return
+						}
+					}
+					// option not found. We add it
+					c.TableSpec.Options = append(c.TableSpec.Options, option)
+				}()
+			}
+		default:
+			return errors.Wrap(ErrUnsupportedApplyOperation, sqlparser.String(opt))
+		}
+		return nil
+	}
+	for _, alterOption := range diff.alterTable.AlterOptions {
+		if err := applyAlterOption(alterOption); err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
+// Apply attempts to apply given ALTER TABLE diff onto the table defined by this entity.
+// This entity is unmodified. If successful, a new CREATE TABLE entity is returned.
+func (c *CreateTableEntity) Apply(diff EntityDiff) (Entity, error) {
+	alterDiff, ok := diff.(*AlterTableEntityDiff)
+	if !ok {
+		return nil, ErrEntityTypeMismatch
+	}
+	dupCreateTable := &sqlparser.CreateTable{
+		Temp:        c.Temp,
+		Table:       c.Table,
+		IfNotExists: c.IfNotExists,
+		TableSpec:   nil,
+		OptLike:     nil,
+		Comments:    nil,
+		FullyParsed: c.FullyParsed,
+	}
+	if c.TableSpec != nil {
+		d := *c.TableSpec
+		dupCreateTable.TableSpec = &d
+	}
+	if c.OptLike != nil {
+		d := *c.OptLike
+		dupCreateTable.OptLike = &d
+	}
+	if c.Comments != nil {
+		d := *c.Comments
+		dupCreateTable.Comments = &d
+	}
+	dup := &CreateTableEntity{CreateTable: *dupCreateTable}
+	if err := dup.apply(alterDiff); err != nil {
+		return nil, err
+	}
+	return dup, nil
+}
diff --git a/go/vt/schemadiff/types.go b/go/vt/schemadiff/types.go
index faa01e601f..cb904c80d5 100644
--- a/go/vt/schemadiff/types.go
+++ b/go/vt/schemadiff/types.go
@@ -36,6 +36,16 @@ var (
 	ErrUnsupportedStatement                        = errors.New("Unsupported statement")
 	ErrDuplicateName                               = errors.New("Duplicate name")
 	ErrViewDependencyUnresolved                    = errors.New("Views have unresolved/loop dependencies")
+
+	ErrUnsupportedApplyOperation = errors.New("unsupported Apply operation")
+	ErrApplyTableNotFound        = errors.New("table not found")
+	ErrApplyViewNotFound         = errors.New("view not found")
+	ErrApplyKeyNotFound          = errors.New("key not found")
+	ErrApplyColumnNotFound       = errors.New("column not found")
+	ErrApplyDuplicateTableOrView = errors.New("duplicate table or view")
+	ErrApplyDuplicateKey         = errors.New("duplicate key")
+	ErrApplyDuplicateColumn      = errors.New("duplicate column")
+	ErrApplyDuplicateConstraint  = errors.New("duplicate constraint")
 )
 
 // Entity stands for a database object we can diff:
diff --git a/go/vt/schemadiff/view.go b/go/vt/schemadiff/view.go
index e09ee2077e..78379f7f89 100644
--- a/go/vt/schemadiff/view.go
+++ b/go/vt/schemadiff/view.go
@@ -16,7 +16,9 @@ limitations under the License.
 
 package schemadiff
 
-import "vitess.io/vitess/go/vt/sqlparser"
+import (
+	"vitess.io/vitess/go/vt/sqlparser"
+)
 
 //
 type AlterViewEntityDiff struct {
@@ -206,3 +208,25 @@ func (c *CreateViewEntity) Drop() EntityDiff {
 	}
 	return &DropViewEntityDiff{from: c, dropView: dropView}
 }
+
+// apply attempts to apply an ALTER VIEW diff onto this entity's view definition.
+// supported modifications are only those created by schemadiff's Diff() function.
+func (c *CreateViewEntity) apply(diff *AlterViewEntityDiff) error {
+	c.CreateView = diff.to.CreateView
+	return nil
+}
+
+// Apply attempts to apply given ALTER VIEW diff onto the view defined by this entity.
+// This entity is unmodified. If successful, a new CREATE VIEW entity is returned.
+func (c *CreateViewEntity) Apply(diff EntityDiff) (Entity, error) {
+	alterDiff, ok := diff.(*AlterViewEntityDiff)
+	if !ok {
+		return nil, ErrEntityTypeMismatch
+	}
+	dupCreateView := &sqlparser.CreateView{}
+	dup := &CreateViewEntity{CreateView: *dupCreateView}
+	if err := dup.apply(alterDiff); err != nil {
+		return nil, err
+	}
+	return dup, nil
+}
diff --git a/go/vt/sqlparser/ast_rewriting.go b/go/vt/sqlparser/ast_rewriting.go
index ecf6f32298..5a2576eb78 100644
--- a/go/vt/sqlparser/ast_rewriting.go
+++ b/go/vt/sqlparser/ast_rewriting.go
@@ -406,6 +406,8 @@ func (er *astRewriter) rewrite(cursor *Cursor) bool {
 				er.bindVars.AddSysVar(sysVar)
 			}
 		}
+	case *ExistsExpr:
+		er.existsRewrite(cursor, node)
 	}
 	return true
 }
@@ -560,6 +562,46 @@ func (er *astRewriter) unnestSubQueries(cursor *Cursor, subquery *Subquery) {
 	cursor.Replace(rewritten)
 }
 
+func (er *astRewriter) existsRewrite(cursor *Cursor, node *ExistsExpr) {
+	switch node := node.Subquery.Select.(type) {
+	case *Select:
+		if node.Limit == nil {
+			node.Limit = &Limit{}
+		}
+		node.Limit.Rowcount = NewIntLiteral("1")
+
+		if node.Having != nil {
+			// If the query has HAVING, we can't take any shortcuts
+			return
+		}
+
+		allAggregation := forAll(node.SelectExprs, func(s SelectExpr) bool {
+			return ContainsAggregation(s)
+		})
+		if allAggregation && len(node.GroupBy) == 0 {
+			// in these situations, we are guaranteed to always get a non-empty result,
+			// so we can replace the EXISTS with a literal true
+			cursor.Replace(BoolVal(true))
+		}
+
+		// If we are not doing HAVING, we can safely replace all select expressions with a
+		// single `1` and remove any grouping
+		node.SelectExprs = SelectExprs{
+			&AliasedExpr{Expr: NewIntLiteral("1")},
+		}
+		node.GroupBy = nil
+	}
+}
+
+func forAll(coll SelectExprs, test func(SelectExpr) bool) bool {
+	for _, k := range coll {
+		if !test(k) {
+			return false
+		}
+	}
+	return true
+}
+
 func bindVarExpression(name string) Expr {
 	return NewArgument(name)
 }
diff --git a/web/vtadmin/src/components/Icon.tsx b/web/vtadmin/src/components/Icon.tsx
index 6a44ece4d2..6169944c6e 100644
--- a/web/vtadmin/src/components/Icon.tsx
+++ b/web/vtadmin/src/components/Icon.tsx
@@ -74,12 +74,6 @@ export enum Icons {
     gear = 'gear',
     history = 'history',
     info = 'info',
-    keyG = 'keyG',
-    keyK = 'keyK',
-    keyR = 'keyR',
-    keyS = 'keyS',
-    keyT = 'keyT',
-    keyV = 'keyV',
     keyboard = 'keyboard',
     link = 'link',
     menu = 'menu',
diff --git a/web/vtadmin/src/components/NavRail.module.scss b/web/vtadmin/src/components/NavRail.module.scss
index f9c864c67c..350b193a78 100644
--- a/web/vtadmin/src/components/NavRail.module.scss
+++ b/web/vtadmin/src/components/NavRail.module.scss
@@ -63,6 +63,7 @@ $navRailHoverTransition: background-color 0.1s ease-in-out;
 }
 
 a.navLink {
+    align-items: center;
     border-left: solid 4px transparent;
     color: var(--textColorPrimary);
     display: flex;
@@ -108,7 +109,6 @@ a.navLink:hover .badge {
 .icon {
     fill: var(--colorScaffoldingForeground);
     height: 2rem;
-    margin-right: 1.2rem;
     transition: $navRailHoverTransition;
 }
 
@@ -117,3 +117,24 @@ a.navLink:focus .icon,
 a.navLink:hover .icon {
     fill: var(--colorPrimary);
 }
+
+.hotkey::before {
+    @apply block;
+    @apply border;
+    @apply font-mono font-bold;
+    @apply p-0;
+    @apply rounded-sm;
+    @apply text-center;
+    @apply select-none;
+
+    $hotkeySize: 16px;
+
+    border-color: var(--colorScaffoldingForeground);
+    color: var(--colorScaffoldingForeground);
+    content: attr(data-hotkey);
+    font-size: 10px;
+    height: $hotkeySize;
+    line-height: 14px;
+    margin: 0 4px;
+    width: $hotkeySize;
+}
diff --git a/web/vtadmin/src/components/NavRail.tsx b/web/vtadmin/src/components/NavRail.tsx
index 879b7f4a86..b584d8d064 100644
--- a/web/vtadmin/src/components/NavRail.tsx
+++ b/web/vtadmin/src/components/NavRail.tsx
@@ -42,32 +42,25 @@ export const NavRail = () => {
             <div className={style.navLinks}>
                 <ul className={style.navList}>
                     <li>
-                        <NavRailLink icon={Icons.chart} text="Dashboard" to="/dashboard" count={0} />
+                        <NavRailLink hotkey="C" text="Clusters" to="/clusters" count={clusters.length} />
                     </li>
                     <li>
-                        <NavRailLink icon={Icons.wrench} text="Workflows" to="/workflows" count={workflows.length} />
-                    </li>
-                </ul>
-
-                <ul className={style.navList}>
-                    <li>
-                        {/* FIXME replace this with a C when we have one */}
-                        <NavRailLink icon={Icons.keyR} text="Clusters" to="/clusters" count={clusters.length} />
+                        <NavRailLink hotkey="G" text="Gates" to="/gates" count={gates.length} />
                     </li>
                     <li>
-                        <NavRailLink icon={Icons.keyG} text="Gates" to="/gates" count={gates.length} />
+                        <NavRailLink hotkey="K" text="Keyspaces" to="/keyspaces" count={keyspaces.length} />
                     </li>
                     <li>
-                        <NavRailLink icon={Icons.keyK} text="Keyspaces" to="/keyspaces" count={keyspaces.length} />
+                        <NavRailLink hotkey="S" text="Schemas" to="/schemas" count={tds.length} />
                     </li>
                     <li>
-                        <NavRailLink icon={Icons.keyS} text="Schemas" to="/schemas" count={tds.length} />
+                        <NavRailLink hotkey="T" text="Tablets" to="/tablets" count={tablets.length} />
                     </li>
                     <li>
-                        <NavRailLink icon={Icons.keyT} text="Tablets" to="/tablets" count={tablets.length} />
+                        <NavRailLink hotkey="V" text="vtctlds" to="/vtctlds" count={vtctlds.length} />
                     </li>
                     <li>
-                        <NavRailLink icon={Icons.keyV} text="vtctlds" to="/vtctlds" count={vtctlds.length} />
+                        <NavRailLink hotkey="W" text="Workflows" to="/workflows" count={workflows.length} />
                     </li>
                 </ul>
 
@@ -78,9 +71,6 @@ export const NavRail = () => {
                     <li>
                         <NavRailLink icon={Icons.runQuery} text="VTExplain" to="/vtexplain" />
                     </li>
-                    <li>
-                        <NavRailLink icon={Icons.gear} text="Settings" to="/settings" />
-                    </li>
                 </ul>
             </div>
 
@@ -89,20 +79,26 @@ export const NavRail = () => {
                     <li>
                         <NavRailLink icon={Icons.bug} text="Debug" to="/debug" />
                     </li>
-                    <li>
-                        <NavRailLink icon={Icons.keyboard} text="Shortcuts" to="/shortcuts" />
-                    </li>
                 </ul>
             </div>
         </div>
     );
 };
 
-const NavRailLink = ({ count, icon, text, to }: { count?: number; icon: Icons; text: string; to: string }) => {
+interface LinkProps {
+    count?: number;
+    hotkey?: string;
+    icon?: Icons;
+    text: string;
+    to: string;
+}
+
+const NavRailLink = ({ count, hotkey, icon, text, to }: LinkProps) => {
     return (
         <NavLink activeClassName={style.navLinkActive} className={style.navLink} to={to}>
-            <Icon className={style.icon} icon={icon} />
-            <span>{text}</span>
+            {icon && <Icon className={style.icon} icon={icon} />}
+            {hotkey && !icon && <div className={style.hotkey} data-hotkey={hotkey.toUpperCase()} />}
+            <div className="ml-4">{text}</div>
             {typeof count === 'number' && <div className={style.badge}>{count}</div>}
         </NavLink>
     );
diff --git a/web/vtadmin/src/icons/index.tsx b/web/vtadmin/src/icons/index.tsx
index d7998a7ec3..2cf385d1cf 100644
--- a/web/vtadmin/src/icons/index.tsx
+++ b/web/vtadmin/src/icons/index.tsx
@@ -35,12 +35,6 @@ export { ReactComponent as Ellipsis } from './ellipsis.svg';
 export { ReactComponent as Gear } from './gear.svg';
 export { ReactComponent as History } from './history.svg';
 export { ReactComponent as Info } from './info.svg';
-export { ReactComponent as KeyG } from './keyG.svg';
-export { ReactComponent as KeyK } from './keyK.svg';
-export { ReactComponent as KeyR } from './keyR.svg';
-export { ReactComponent as KeyS } from './keyS.svg';
-export { ReactComponent as KeyT } from './keyT.svg';
-export { ReactComponent as KeyV } from './keyV.svg';
 export { ReactComponent as Keyboard } from './keyboard.svg';
 export { ReactComponent as Link } from './link.svg';
 export { ReactComponent as Menu } from './menu.svg';
diff --git a/web/vtadmin/src/icons/keyG.svg b/web/vtadmin/src/icons/keyG.svg
deleted file mode 100644
index 7891b1cfc2..0000000000
--- a/web/vtadmin/src/icons/keyG.svg
+++ /dev/null
@@ -1,3 +0,0 @@
-<svg width="24" height="24" viewBox="0 0 24 24"  fill="#718096" xmlns="http://www.w3.org/2000/svg">
-<path fill-rule="evenodd" clip-rule="evenodd" d="M5 3H19C20.1 3 21 3.9 21 5V19C21 20.1 20.1 21 19 21H5C3.9 21 3 20.1 3 19V5C3 3.9 3.9 3 5 3ZM5 19H19V5H5V19ZM14.4983 16.7744C15.1728 16.5283 15.6946 16.1842 16.0637 15.7422V11.7705H12.1467V13.2812H14.0129V14.9971C13.6848 15.3252 13.1357 15.4893 12.3655 15.4893C11.5999 15.4893 11.0188 15.2227 10.6223 14.6895C10.2258 14.1562 10.0276 13.3678 10.0276 12.3242V11.6885C10.0321 10.654 10.2167 9.87467 10.5813 9.35059C10.9459 8.8265 11.4768 8.56445 12.1741 8.56445C12.7209 8.56445 13.1493 8.69661 13.4592 8.96094C13.7737 9.22526 13.9765 9.64225 14.0676 10.2119H16.0637C15.9407 9.13639 15.5465 8.31836 14.8811 7.75781C14.2157 7.19271 13.2929 6.91016 12.1125 6.91016C11.2649 6.91016 10.5243 7.10384 9.89087 7.49121C9.26196 7.87402 8.77889 8.43001 8.44165 9.15918C8.10897 9.88379 7.94263 10.7428 7.94263 11.7363V12.4131C7.95174 13.3792 8.13175 14.2201 8.48267 14.9355C8.83813 15.651 9.33944 16.1979 9.98657 16.5762C10.6383 16.9499 11.3948 17.1367 12.2561 17.1367C13.0764 17.1367 13.8238 17.016 14.4983 16.7744Z"/>
-</svg>
diff --git a/web/vtadmin/src/icons/keyK.svg b/web/vtadmin/src/icons/keyK.svg
deleted file mode 100644
index e4d7348828..0000000000
--- a/web/vtadmin/src/icons/keyK.svg
+++ /dev/null
@@ -1,3 +0,0 @@
-<svg width="24" height="24" viewBox="0 0 24 24"  fill="#718096" xmlns="http://www.w3.org/2000/svg">
-<path fill-rule="evenodd" clip-rule="evenodd" d="M5 3H19C20.1 3 21 3.9 21 5V19C21 20.1 20.1 21 19 21H5C3.9 21 3 20.1 3 19V5C3 3.9 3.9 3 5 3ZM5 19H19V5H5V19ZM10.2395 14.1562L11.3059 13.0078L13.8625 17H16.303L12.6663 11.4697L16.2004 7.04688H13.678L11.1418 10.3213L10.2395 11.5586V7.04688H8.18872V17H10.2395V14.1562Z"/>
-</svg>
diff --git a/web/vtadmin/src/icons/keyR.svg b/web/vtadmin/src/icons/keyR.svg
deleted file mode 100644
index 1159469425..0000000000
--- a/web/vtadmin/src/icons/keyR.svg
+++ /dev/null
@@ -1,3 +0,0 @@
-<svg width="24" height="24" viewBox="0 0 24 24"  fill="#718096" xmlns="http://www.w3.org/2000/svg">
-<path fill-rule="evenodd" clip-rule="evenodd" d="M5 3H19C20.1 3 21 3.9 21 5V19C21 20.1 20.1 21 19 21H5C3.9 21 3 20.1 3 19V5C3 3.9 3.9 3 5 3ZM5 19H19V5H5V19ZM10.4395 13.3564H12.0732L13.9395 17H16.1406V16.9043L13.9873 12.8369C14.5843 12.568 15.0286 12.208 15.3203 11.7568C15.6165 11.3011 15.7646 10.7337 15.7646 10.0547C15.7646 9.09766 15.4456 8.3571 14.8076 7.83301C14.1696 7.30892 13.2627 7.04688 12.0869 7.04688H8.38867V17H10.4395V13.3564ZM12.0938 11.6953H10.4395V8.70801H12.0869C12.6383 8.70801 13.0462 8.84245 13.3105 9.11133C13.5794 9.38021 13.7139 9.74935 13.7139 10.2188C13.7139 10.679 13.5726 11.0413 13.29 11.3057C13.0075 11.5654 12.6087 11.6953 12.0938 11.6953Z"/>
-</svg>
diff --git a/web/vtadmin/src/icons/keyS.svg b/web/vtadmin/src/icons/keyS.svg
deleted file mode 100644
index 2f133aef0d..0000000000
--- a/web/vtadmin/src/icons/keyS.svg
+++ /dev/null
@@ -1,3 +0,0 @@
-<svg width="24" height="24" viewBox="0 0 24 24"  fill="#718096" xmlns="http://www.w3.org/2000/svg">
-<path fill-rule="evenodd" clip-rule="evenodd" d="M5 3H19C20.1 3 21 3.9 21 5V19C21 20.1 20.1 21 19 21H5C3.9 21 3 20.1 3 19V5C3 3.9 3.9 3 5 3ZM5 19H19V5H5V19ZM13.1396 13.5C13.4131 13.7051 13.5498 14.0013 13.5498 14.3887C13.5498 14.7441 13.4131 15.0221 13.1396 15.2227C12.8662 15.4186 12.4857 15.5166 11.998 15.5166C10.6855 15.5166 10.0293 14.9674 10.0293 13.8691H7.97168C7.97168 14.5117 8.13574 15.0768 8.46387 15.5645C8.79655 16.0521 9.27734 16.4372 9.90625 16.7197C10.5352 16.9977 11.2324 17.1367 11.998 17.1367C13.1009 17.1367 13.9782 16.8929 14.6299 16.4053C15.2816 15.9131 15.6074 15.2363 15.6074 14.375C15.6074 13.6003 15.3408 12.9531 14.8076 12.4336C14.2744 11.9141 13.4245 11.4788 12.2578 11.1279C11.6243 10.9365 11.1436 10.7314 10.8154 10.5127C10.4919 10.2939 10.3301 10.0228 10.3301 9.69922C10.3301 9.35286 10.4691 9.07487 10.7471 8.86523C11.0251 8.65104 11.4147 8.54395 11.916 8.54395C12.4355 8.54395 12.8389 8.67155 13.126 8.92676C13.4131 9.17741 13.5566 9.5306 13.5566 9.98633H15.6074C15.6074 9.38932 15.4525 8.85612 15.1426 8.38672C14.8372 7.91732 14.4066 7.55501 13.8506 7.2998C13.2946 7.04004 12.6611 6.91016 11.9502 6.91016C11.2438 6.91016 10.6058 7.02865 10.0361 7.26562C9.47103 7.5026 9.03581 7.83301 8.73047 8.25684C8.42969 8.67611 8.2793 9.15462 8.2793 9.69238C8.2793 10.7269 8.8444 11.5495 9.97461 12.1602C10.3893 12.3835 10.9521 12.6113 11.6631 12.8438C12.374 13.0716 12.8662 13.2904 13.1396 13.5Z"/>
-</svg>
diff --git a/web/vtadmin/src/icons/keyT.svg b/web/vtadmin/src/icons/keyT.svg
deleted file mode 100644
index 9ec3641971..0000000000
--- a/web/vtadmin/src/icons/keyT.svg
+++ /dev/null
@@ -1,3 +0,0 @@
-<svg width="24" height="24" viewBox="0 0 24 24"  fill="#718096" xmlns="http://www.w3.org/2000/svg">
-<path fill-rule="evenodd" clip-rule="evenodd" d="M5 3H19C20.1 3 21 3.9 21 5V19C21 20.1 20.1 21 19 21H5C3.9 21 3 20.1 3 19V5C3 3.9 3.9 3 5 3ZM5 19H19V5H5V19ZM12.832 8.70801H15.8809V7.04688H7.77344V8.70801H10.7812V17H12.832V8.70801Z"/>
-</svg>
diff --git a/web/vtadmin/src/icons/keyV.svg b/web/vtadmin/src/icons/keyV.svg
deleted file mode 100644
index 07cc33d99e..0000000000
--- a/web/vtadmin/src/icons/keyV.svg
+++ /dev/null
@@ -1,4 +0,0 @@
-<svg width="24" height="24" viewBox="0 0 24 24" fill="none" xmlns="http://www.w3.org/2000/svg">
-<path d="M19 3H5C3.9 3 3 3.9 3 5V19C3 20.1 3.9 21 5 21H19C20.1 21 21 20.1 21 19V5C21 3.9 20.1 3 19 3ZM19 19H5V5H19V19Z" fill="#718096"/>
-<path d="M12.0186 14.4854L14.2744 7H16.5576L13.0918 16.9531H10.9521L7.5 7H9.77637L12.0186 14.4854Z" fill="#718096"/>
-</svg>


Test patch:
diff --git a/go/test/endtoend/vtgate/gen4/gen4_test.go b/go/test/endtoend/vtgate/gen4/gen4_test.go
index 83e8e2718f..62a6a56a55 100644
--- a/go/test/endtoend/vtgate/gen4/gen4_test.go
+++ b/go/test/endtoend/vtgate/gen4/gen4_test.go
@@ -66,12 +66,44 @@ func TestCorrelatedExistsSubquery(t *testing.T) {
 		_, _ = utils.ExecAllowError(t, conn, `delete from t2`)
 	}()
 	// insert some data.
-	utils.Exec(t, conn, `insert into t1(id, col) values (100, 123),(10, 12), (1, 13), (4, 13),(1000, 1234)`)
+	utils.Exec(t, conn, `insert into t1(id, col) values (100, 123), (10, 12), (1, 13), (4, 13), (1000, 1234)`)
 	utils.Exec(t, conn, `insert into t2(id, tcol1, tcol2) values (100, 13, 1),(9, 7, 15),(1, 123, 123),(1004, 134, 123)`)
 
-	utils.AssertMatches(t, conn, `select id from t1 where exists(select 1 from t2 where t1.col = t2.tcol2)`, `[[INT64(100)]]`)
-	utils.AssertMatches(t, conn, `select id from t1 where exists(select 1 from t2 where t1.col = t2.tcol1) order by id`, `[[INT64(1)] [INT64(4)] [INT64(100)]]`)
-	utils.AssertMatches(t, conn, `select id from t1 where id in (select id from t2) order by id`, `[[INT64(1)] [INT64(100)]]`)
+	utils.AssertMatches(t, conn, `select id from t1 where exists(select 1 from t2 where t1.col = t2.tcol2)`,
+		`[[INT64(100)]]`)
+	utils.AssertMatches(t, conn, `select id from t1 where exists(select 1 from t2 where t1.col = t2.tcol1) order by id`,
+		`[[INT64(1)] [INT64(4)] [INT64(100)]]`)
+	utils.AssertMatches(t, conn, `select id from t1 where id in (select id from t2) order by id`,
+		`[[INT64(1)] [INT64(100)]]`)
+
+	utils.AssertMatches(t, conn, `
+select id 
+from t1 
+where exists(
+	select t2.id, count(*) 
+	from t2 
+	where t1.col = t2.tcol2
+    having count(*) > 0
+)`,
+		`[[INT64(100)]]`)
+	utils.AssertMatches(t, conn, `
+select id 
+from t1 
+where exists(
+	select t2.id, count(*) 
+	from t2 
+	where t1.col = t2.tcol1
+) order by id`,
+		`[[INT64(1)] [INT64(4)] [INT64(100)]]`)
+	utils.AssertMatchesNoOrder(t, conn, `
+select id 
+from t1 
+where exists(
+	select count(*) 
+	from t2 
+	where t1.col = t2.tcol1
+) order by id`,
+		`[[INT64(1)] [INT64(4)] [INT64(100)] [INT64(1000)] [INT64(10)]]`)
 }
 
 func TestGroupBy(t *testing.T) {
diff --git a/go/vt/schemadiff/diff_test.go b/go/vt/schemadiff/diff_test.go
index 663062fc39..aa323cec79 100644
--- a/go/vt/schemadiff/diff_test.go
+++ b/go/vt/schemadiff/diff_test.go
@@ -297,7 +297,7 @@ func TestDiffSchemas(t *testing.T) {
 			to:   "create table t(id int primary key)",
 		},
 		{
-			name: "change of table columns",
+			name: "change of table columns, added",
 			from: "create table t(id int primary key)",
 			to:   "create table t(id int primary key, i int)",
 			diffs: []string{
@@ -307,6 +307,17 @@ func TestDiffSchemas(t *testing.T) {
 				"ALTER TABLE `t` ADD COLUMN `i` int",
 			},
 		},
+		{
+			name: "change of table columns, removed",
+			from: "create table t(id int primary key, i int)",
+			to:   "create table t(id int primary key)",
+			diffs: []string{
+				"alter table t drop column i",
+			},
+			cdiffs: []string{
+				"ALTER TABLE `t` DROP COLUMN `i`",
+			},
+		},
 		{
 			name: "create table",
 			to:   "create table t(id int primary key)",
@@ -485,6 +496,83 @@ func TestDiffSchemas(t *testing.T) {
 					_, err := sqlparser.Parse(s)
 					assert.NoError(t, err)
 				}
+
+				{
+					// Validate "apply()" on "from" converges with "to"
+					schema1, err := NewSchemaFromSQL(ts.from)
+					assert.NoError(t, err)
+					schema2, err := NewSchemaFromSQL(ts.to)
+					assert.NoError(t, err)
+					applied, err := schema1.Apply(diffs)
+					require.NoError(t, err)
+
+					appliedDiff, err := schema2.Diff(applied, hints)
+					require.NoError(t, err)
+					assert.Empty(t, appliedDiff)
+					assert.Equal(t, schema2.ToQueries(), applied.ToQueries())
+				}
+			}
+		})
+	}
+}
+
+func TestSchemaApplyError(t *testing.T) {
+	tt := []struct {
+		name string
+		from string
+		to   string
+	}{
+		{
+			name: "added table",
+			to:   "create table t2(id int primary key)",
+		},
+		{
+			name: "different tables",
+			from: "create table t1(id int primary key)",
+			to:   "create table t2(id int primary key)",
+		},
+		{
+			name: "added table 2",
+			from: "create table t1(id int primary key)",
+			to:   "create table t1(id int primary key); create table t2(id int primary key)",
+		},
+		{
+			name: "modified tables",
+			from: "create table t(id int primary key, i int)",
+			to:   "create table t(id int primary key)",
+		},
+		{
+			name: "added view",
+			from: "create table t(id int); create view v1 as select * from t",
+			to:   "create table t(id int); create view v1 as select * from t; create view v2 as select * from t",
+		},
+	}
+	hints := &DiffHints{}
+	for _, ts := range tt {
+		t.Run(ts.name, func(t *testing.T) {
+			// Validate "apply()" on "from" converges with "to"
+			schema1, err := NewSchemaFromSQL(ts.from)
+			assert.NoError(t, err)
+			schema2, err := NewSchemaFromSQL(ts.to)
+			assert.NoError(t, err)
+
+			{
+				diffs, err := schema1.Diff(schema2, hints)
+				assert.NoError(t, err)
+				assert.NotEmpty(t, diffs)
+				_, err = schema1.Apply(diffs)
+				require.NoError(t, err)
+				_, err = schema2.Apply(diffs)
+				require.Error(t, err, "expected error applying to schema2. diffs: %v", diffs)
+			}
+			{
+				diffs, err := schema2.Diff(schema1, hints)
+				assert.NoError(t, err)
+				assert.NotEmpty(t, diffs, "schema1: %v, schema2: %v", schema1.ToSQL(), schema2.ToSQL())
+				_, err = schema2.Apply(diffs)
+				require.NoError(t, err)
+				_, err = schema1.Apply(diffs)
+				require.Error(t, err, "applying diffs to schema1: %v", schema1.ToSQL())
 			}
 		})
 	}
diff --git a/go/vt/schemadiff/schema_test.go b/go/vt/schemadiff/schema_test.go
index 5a72b68c99..48d4888bbb 100644
--- a/go/vt/schemadiff/schema_test.go
+++ b/go/vt/schemadiff/schema_test.go
@@ -53,6 +53,8 @@ var expectSortedNames = []string{
 	"v6", // level 3
 }
 
+var toSQL = "CREATE TABLE `t1` (\n\t`id` int\n);\nCREATE TABLE `t2` (\n\t`id` int\n);\nCREATE TABLE `t3` (\n\t`id` int\n);\nCREATE TABLE `t5` (\n\t`id` int\n);\nCREATE VIEW `v0` AS SELECT 1 FROM `dual`;\nCREATE VIEW `v3` AS SELECT * FROM `t3` AS `t3`;\nCREATE VIEW `v9` AS SELECT 1 FROM `dual`;\nCREATE VIEW `v1` AS SELECT * FROM `v3`;\nCREATE VIEW `v2` AS SELECT * FROM `v3`, `t2`;\nCREATE VIEW `v4` AS SELECT * FROM `t2` AS `something_else`, `v3`;\nCREATE VIEW `v5` AS SELECT * FROM `t1`, (SELECT * FROM `v3`) AS `some_alias`;\nCREATE VIEW `v6` AS SELECT * FROM `v4`;\n"
+
 func TestNewSchemaFromQueries(t *testing.T) {
 	schema, err := NewSchemaFromQueries(createQueries)
 	assert.NoError(t, err)
@@ -109,3 +111,12 @@ func TestNewSchemaFromQueriesLoop(t *testing.T) {
 	assert.Error(t, err)
 	assert.ErrorIs(t, err, ErrViewDependencyUnresolved)
 }
+
+func TestToSQL(t *testing.T) {
+	schema, err := NewSchemaFromQueries(createQueries)
+	assert.NoError(t, err)
+	assert.NotNil(t, schema)
+
+	sql := schema.ToSQL()
+	assert.Equal(t, toSQL, sql)
+}
diff --git a/go/vt/schemadiff/table_test.go b/go/vt/schemadiff/table_test.go
index 9b79096fc2..c77aa5b741 100644
--- a/go/vt/schemadiff/table_test.go
+++ b/go/vt/schemadiff/table_test.go
@@ -44,6 +44,11 @@ func TestCreateTableDiff(t *testing.T) {
 			from: "create table t (id int primary key)",
 			to:   "create table t (id int primary key)",
 		},
+		{
+			name: "identical 2",
+			from: "create table t (id int, primary key(id))",
+			to:   "create table t (id int, primary key(id))",
+		},
 		{
 			name: "identical, spacing",
 			from: "create   table     t    (id int   primary  key)",
@@ -122,36 +127,37 @@ func TestCreateTableDiff(t *testing.T) {
 			name:  "reorder column, far jump",
 			from:  "create table t1 (id int primary key, a int, b int, c int, d int)",
 			to:    "create table t2 (a int, b int, c int, d int, id int primary key)",
-			diff:  "alter table t1 modify column a int first, modify column b int after a, modify column c int after b, modify column d int after c",
-			cdiff: "ALTER TABLE `t1` MODIFY COLUMN `a` int FIRST, MODIFY COLUMN `b` int AFTER `a`, MODIFY COLUMN `c` int AFTER `b`, MODIFY COLUMN `d` int AFTER `c`",
+			diff:  "alter table t1 modify column id int primary key after d",
+			cdiff: "ALTER TABLE `t1` MODIFY COLUMN `id` int PRIMARY KEY AFTER `d`",
 		},
 		{
 			name:  "reorder column, far jump, another reorder",
 			from:  "create table t1 (id int primary key, a int, b int, c int, d int)",
 			to:    "create table t2 (a int, c int, b int, d int, id int primary key)",
-			diff:  "alter table t1 modify column a int first, modify column c int after a, modify column b int after c, modify column d int after b",
-			cdiff: "ALTER TABLE `t1` MODIFY COLUMN `a` int FIRST, MODIFY COLUMN `c` int AFTER `a`, MODIFY COLUMN `b` int AFTER `c`, MODIFY COLUMN `d` int AFTER `b`",
+			diff:  "alter table t1 modify column c int after a, modify column id int primary key after d",
+			cdiff: "ALTER TABLE `t1` MODIFY COLUMN `c` int AFTER `a`, MODIFY COLUMN `id` int PRIMARY KEY AFTER `d`",
 		},
 		{
 			name:  "reorder column, far jump, another reorder 2",
 			from:  "create table t1 (id int primary key, a int, b int, c int, d int)",
 			to:    "create table t2 (c int, a int, b int, d int, id int primary key)",
-			diff:  "alter table t1 modify column c int first, modify column a int after c, modify column b int after a, modify column d int after b",
-			cdiff: "ALTER TABLE `t1` MODIFY COLUMN `c` int FIRST, MODIFY COLUMN `a` int AFTER `c`, MODIFY COLUMN `b` int AFTER `a`, MODIFY COLUMN `d` int AFTER `b`",
+			diff:  "alter table t1 modify column c int first, modify column id int primary key after d",
+			cdiff: "ALTER TABLE `t1` MODIFY COLUMN `c` int FIRST, MODIFY COLUMN `id` int PRIMARY KEY AFTER `d`",
 		},
 		{
-			name:  "reorder column, far jump, another reorder, extra columns",
+			name:  "reorder column, far jump, another reorder 3",
 			from:  "create table t1 (id int primary key, a int, b int, c int, d int, e int, f int)",
 			to:    "create table t2 (a int, c int, b int, d int, id int primary key, e int, f int)",
-			diff:  "alter table t1 modify column a int first, modify column c int after a, modify column b int after c, modify column d int after b",
-			cdiff: "ALTER TABLE `t1` MODIFY COLUMN `a` int FIRST, MODIFY COLUMN `c` int AFTER `a`, MODIFY COLUMN `b` int AFTER `c`, MODIFY COLUMN `d` int AFTER `b`",
+			diff:  "alter table t1 modify column c int after a, modify column id int primary key after d",
+			cdiff: "ALTER TABLE `t1` MODIFY COLUMN `c` int AFTER `a`, MODIFY COLUMN `id` int PRIMARY KEY AFTER `d`",
 		},
 		{
 			name:  "reorder column, far jump, another reorder, removed columns",
 			from:  "create table t1 (id int primary key, a int, b int, c int, d int, e int, f int, g int)",
 			to:    "create table t2 (a int, c int, f int, e int, id int primary key, g int)",
-			diff:  "alter table t1 drop column b, drop column d, modify column a int first, modify column c int after a, modify column f int after c, modify column e int after f",
-			cdiff: "ALTER TABLE `t1` DROP COLUMN `b`, DROP COLUMN `d`, MODIFY COLUMN `a` int FIRST, MODIFY COLUMN `c` int AFTER `a`, MODIFY COLUMN `f` int AFTER `c`, MODIFY COLUMN `e` int AFTER `f`"},
+			diff:  "alter table t1 drop column b, drop column d, modify column f int after c, modify column id int primary key after e",
+			cdiff: "ALTER TABLE `t1` DROP COLUMN `b`, DROP COLUMN `d`, MODIFY COLUMN `f` int AFTER `c`, MODIFY COLUMN `id` int PRIMARY KEY AFTER `e`",
+		},
 		{
 			name:  "two reorders",
 			from:  "create table t1 (id int primary key, a int, b int, c int, d int, e int, f int)",
@@ -159,6 +165,13 @@ func TestCreateTableDiff(t *testing.T) {
 			diff:  "alter table t1 modify column b int after id, modify column e int after c",
 			cdiff: "ALTER TABLE `t1` MODIFY COLUMN `b` int AFTER `id`, MODIFY COLUMN `e` int AFTER `c`",
 		},
+		{
+			name:  "two reorders, added and removed columns",
+			from:  "create table t1 (id int primary key, a int, b int, c int, d int, e int, f int)",
+			to:    "create table t2 (g int, id int primary key, h int, b int, a int, i int, e int, d int, j int, f int, k int)",
+			diff:  "alter table t1 drop column c, modify column b int after id, modify column e int after a, add column g int first, add column h int after id, add column i int after a, add column j int after d, add column k int",
+			cdiff: "ALTER TABLE `t1` DROP COLUMN `c`, MODIFY COLUMN `b` int AFTER `id`, MODIFY COLUMN `e` int AFTER `a`, ADD COLUMN `g` int FIRST, ADD COLUMN `h` int AFTER `id`, ADD COLUMN `i` int AFTER `a`, ADD COLUMN `j` int AFTER `d`, ADD COLUMN `k` int",
+		},
 		{
 			name:  "reorder column and change data type",
 			from:  "create table t1 (id int primary key, a int, b int, c int, d int)",
@@ -223,6 +236,27 @@ func TestCreateTableDiff(t *testing.T) {
 			diff:  "alter table t1 add key i_idx (i)",
 			cdiff: "ALTER TABLE `t1` ADD KEY `i_idx` (`i`)",
 		},
+		{
+			name:  "added key without name",
+			from:  "create table t1 (`id` int primary key, i int)",
+			to:    "create table t2 (id int primary key, `i` int, key (i))",
+			diff:  "alter table t1 add key i (i)",
+			cdiff: "ALTER TABLE `t1` ADD KEY `i` (`i`)",
+		},
+		{
+			name:  "added key without name, conflicting name",
+			from:  "create table t1 (`id` int primary key, i int, key i(i))",
+			to:    "create table t2 (id int primary key, `i` int, key i(i), key (i))",
+			diff:  "alter table t1 add key i_2 (i)",
+			cdiff: "ALTER TABLE `t1` ADD KEY `i_2` (`i`)",
+		},
+		{
+			name:  "added key without name, conflicting name 2",
+			from:  "create table t1 (`id` int primary key, i int, key i(i), key i_2(i))",
+			to:    "create table t2 (id int primary key, `i` int, key i(i), key i_2(i), key (i))",
+			diff:  "alter table t1 add key i_3 (i)",
+			cdiff: "ALTER TABLE `t1` ADD KEY `i_3` (`i`)",
+		},
 		{
 			name:  "added column and key",
 			from:  "create table t1 (`id` int primary key)",
@@ -244,6 +278,13 @@ func TestCreateTableDiff(t *testing.T) {
 			diff:  "alter table t1 add primary key (id)",
 			cdiff: "ALTER TABLE `t1` ADD PRIMARY KEY (`id`)",
 		},
+		{
+			name:  "dropped primary key",
+			from:  "create table t1 (id int, primary key(id))",
+			to:    "create table t2 (id int)",
+			diff:  "alter table t1 drop key `PRIMARY`",
+			cdiff: "ALTER TABLE `t1` DROP KEY `PRIMARY`",
+		},
 		{
 			name:  "dropped key",
 			from:  "create table t1 (`id` int primary key, i int, key i_idx(i))",
@@ -282,6 +323,16 @@ func TestCreateTableDiff(t *testing.T) {
 			from: "create table t1 (`id` int, i int, primary key(id), key i_idx(i), key i2_idx(i, `id`))",
 			to:   "create table t2 (`id` int, i int, key i2_idx (`i`, id), key i_idx ( i ), primary key(id) )",
 		},
+		{
+			name: "reordered key, no diff 3",
+			from: "CREATE TABLE `pets` (`id` int, `name` VARCHAR(255), `login` VARCHAR(255), PRIMARY KEY (`id`), KEY (`login`), KEY (`name`) )",
+			to:   "CREATE TABLE `pets` (`id` int, `name` VARCHAR(255), `login` VARCHAR(255), PRIMARY KEY (`id`), KEY (`name`), KEY (`login`) )",
+		},
+		{
+			name: "reordered key, no diff 4",
+			from: "CREATE TABLE `pets` (`id` int, `name` VARCHAR(255), `login` VARCHAR(255), PRIMARY KEY (`id`), KEY login (login, name), KEY (`login`), KEY (`name`) )",
+			to:   "CREATE TABLE `pets` (`id` int, `name` VARCHAR(255), `login` VARCHAR(255), PRIMARY KEY (`id`), KEY (`name`), KEY (`login`), KEY login (login, name) )",
+		},
 		{
 			name:  "reordered key, add key",
 			from:  "create table t1 (`id` int primary key, i int, key i_idx(i), key i2_idx(i, `id`))",
@@ -650,6 +701,7 @@ func TestCreateTableDiff(t *testing.T) {
 					_, err := sqlparser.Parse(diff)
 					assert.NoError(t, err)
 
+					// Validate "from/to" entities
 					eFrom, eTo := alter.Entities()
 					if ts.fromName != "" {
 						assert.Equal(t, ts.fromName, eFrom.Name())
@@ -657,6 +709,21 @@ func TestCreateTableDiff(t *testing.T) {
 					if ts.toName != "" {
 						assert.Equal(t, ts.toName, eTo.Name())
 					}
+
+					{ // Validate "apply()" on "from" converges with "to"
+						applied, err := c.Apply(alter)
+						assert.NoError(t, err)
+						require.NotNil(t, applied)
+						appliedDiff, err := eTo.Diff(applied, &hints)
+						require.NoError(t, err)
+						assert.True(t, appliedDiff.IsEmpty(), "expected empty diff, found changes: %v.\nc=%v\n,alter=%v\n,eTo=%v\napplied=%v\n",
+							appliedDiff.CanonicalStatementString(),
+							c.Create().CanonicalStatementString(),
+							alter.CanonicalStatementString(),
+							eTo.Create().CanonicalStatementString(),
+							applied.Create().CanonicalStatementString(),
+						)
+					}
 				}
 				{
 					cdiff := alter.CanonicalStatementString()
diff --git a/go/vt/schemadiff/view_test.go b/go/vt/schemadiff/view_test.go
index b506073884..b0761cf36b 100644
--- a/go/vt/schemadiff/view_test.go
+++ b/go/vt/schemadiff/view_test.go
@@ -185,6 +185,14 @@ func TestCreateViewDiff(t *testing.T) {
 					if ts.toName != "" {
 						assert.Equal(t, ts.toName, eTo.Name())
 					}
+					{ // Validate "apply()" on "from" converges with "to"
+						applied, err := c.Apply(alter)
+						assert.NoError(t, err)
+						require.NotNil(t, applied)
+						appliedDiff, err := eTo.Diff(applied, hints)
+						require.NoError(t, err)
+						assert.True(t, appliedDiff.IsEmpty(), "expected empty diff, found changes: %v", appliedDiff.CanonicalStatementString())
+					}
 				}
 				{
 					cdiff := alter.CanonicalStatementString()
diff --git a/go/vt/sqlparser/ast_rewriting_test.go b/go/vt/sqlparser/ast_rewriting_test.go
index d1d4c5f36a..8f63ebb776 100644
--- a/go/vt/sqlparser/ast_rewriting_test.go
+++ b/go/vt/sqlparser/ast_rewriting_test.go
@@ -165,7 +165,7 @@ func TestRewrites(in *testing.T) {
 		expected: "select 42 as `(select 42 from dual)` from dual",
 	}, {
 		in:       "select exists(select 1) from user",
-		expected: "select exists(select 1) from user",
+		expected: "select exists(select 1 limit 1) from user",
 	}, {
 		in:       "select * from user where col = (select 42)",
 		expected: "select * from user where col = 42",
@@ -259,6 +259,24 @@ func TestRewrites(in *testing.T) {
 	}, {
 		in:       "SELECT * FROM tbl WHERE not id not regexp '%foobar'",
 		expected: "SELECT * FROM tbl WHERE id regexp '%foobar'",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select col1, col2 from other_table where foo > bar)",
+		expected: "SELECT * FROM tbl WHERE exists(select 1 from other_table where foo > bar limit 1)",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select col1, col2 from other_table where foo > bar limit 100 offset 34)",
+		expected: "SELECT * FROM tbl WHERE exists(select 1 from other_table where foo > bar limit 1 offset 34)",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select col1, col2, count(*) from other_table where foo > bar group by col1, col2)",
+		expected: "SELECT * FROM tbl WHERE exists(select 1 from other_table where foo > bar limit 1)",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select col1, col2 from other_table where foo > bar group by col1, col2)",
+		expected: "SELECT * FROM tbl WHERE exists(select 1 from other_table where foo > bar limit 1)",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select count(*) from other_table where foo > bar)",
+		expected: "SELECT * FROM tbl WHERE true",
+	}, {
+		in:       "SELECT * FROM tbl WHERE exists(select col1, col2, count(*) from other_table where foo > bar group by col1, col2 having count(*) > 3)",
+		expected: "SELECT * FROM tbl WHERE exists(select col1, col2, count(*) from other_table where foo > bar group by col1, col2 having count(*) > 3 limit 1)",
 	}, {
 		in:                          "SHOW VARIABLES",
 		expected:                    "SHOW VARIABLES",
@@ -439,7 +457,7 @@ func TestRewritesWithDefaultKeyspace(in *testing.T) {
 		expected: "SELECT 1 from (select 2 from sys.test) t",
 	}, {
 		in:       "SELECT 1 from test where exists (select 2 from test)",
-		expected: "SELECT 1 from sys.test where exists (select 2 from sys.test)",
+		expected: "SELECT 1 from sys.test where exists (select 1 from sys.test limit 1)",
 	}, {
 		in:       "SELECT 1 from dual",
 		expected: "SELECT 1 from dual",
diff --git a/go/vt/vtexplain/testdata/multi-output/selectsharded-output.txt b/go/vt/vtexplain/testdata/multi-output/selectsharded-output.txt
index a14de3d77f..0fc37af57d 100644
--- a/go/vt/vtexplain/testdata/multi-output/selectsharded-output.txt
+++ b/go/vt/vtexplain/testdata/multi-output/selectsharded-output.txt
@@ -128,7 +128,7 @@ select name from user where id not in (select id from t1) /* non-correlated subq
 ----------------------------------------------------------------------
 select name from user where exists (select id from t1) /* non-correlated subquery as EXISTS */
 
-1 ks_unsharded/-: select id from t1 limit 10001 /* non-correlated subquery as EXISTS */
+1 ks_unsharded/-: select 1 from t1 limit 1 /* non-correlated subquery as EXISTS */
 2 ks_sharded/-40: select `name` from `user` where 1 limit 10001 /* non-correlated subquery as EXISTS */
 2 ks_sharded/40-80: select `name` from `user` where 1 limit 10001 /* non-correlated subquery as EXISTS */
 2 ks_sharded/80-c0: select `name` from `user` where 1 limit 10001 /* non-correlated subquery as EXISTS */
diff --git a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
index 3810378a82..d147c64730 100644
--- a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
@@ -2496,7 +2496,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select count(*) from `user` where 1 != 1",
-        "Query": "select count(*) from `user` where exists (select 1 from user_extra where user_id = `user`.id group by user_id having max(col) \u003e 10)",
+        "Query": "select count(*) from `user` where exists (select 1 from user_extra where user_id = `user`.id group by user_id having max(col) \u003e 10 limit 1)",
         "Table": "`user`"
       }
     ]
@@ -2518,7 +2518,7 @@ Gen4 plan same as above
           "Sharded": true
         },
         "FieldQuery": "select count(*) from `user` where 1 != 1",
-        "Query": "select count(*) from `user` where exists (select 1 from user_extra where user_id = `user`.id group by user_id having max(col) \u003e 10)",
+        "Query": "select count(*) from `user` where exists (select 1 from user_extra where user_id = `user`.id group by user_id having max(col) \u003e 10 limit 1)",
         "Table": "`user`"
       }
     ]
diff --git a/go/vt/vtgate/planbuilder/testdata/dml_cases.txt b/go/vt/vtgate/planbuilder/testdata/dml_cases.txt
index 4480b0af93..5e24a7e743 100644
--- a/go/vt/vtgate/planbuilder/testdata/dml_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/dml_cases.txt
@@ -2953,7 +2953,7 @@ Gen4 plan same as above
     },
     "TargetTabletType": "PRIMARY",
     "MultiShardAutocommit": false,
-    "Query": "insert into user_privacy_consents(user_id, accepted_at) select user_id, accepted_at from (select 1 as user_id, 1629194864 as accepted_at from dual) as tmp where not exists (select user_id from user_privacy_consents where user_id = 1) for update",
+    "Query": "insert into user_privacy_consents(user_id, accepted_at) select user_id, accepted_at from (select 1 as user_id, 1629194864 as accepted_at from dual) as tmp where not exists (select 1 from user_privacy_consents where user_id = 1 limit 1) for update",
     "TableName": "user_privacy_consents"
   }
 }
diff --git a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
index 685fa720dc..824d91d751 100644
--- a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
@@ -1722,15 +1722,21 @@ Gen4 plan same as above
     ],
     "Inputs": [
       {
-        "OperatorType": "Route",
-        "Variant": "Scatter",
-        "Keyspace": {
-          "Name": "user",
-          "Sharded": true
-        },
-        "FieldQuery": "select col from `user` where 1 != 1",
-        "Query": "select col from `user`",
-        "Table": "`user`"
+        "OperatorType": "Limit",
+        "Count": "INT64(1)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from `user` where 1 != 1",
+            "Query": "select 1 from `user` limit :__upper_limit",
+            "Table": "`user`"
+          }
+        ]
       },
       {
         "OperatorType": "Route",
@@ -1757,15 +1763,21 @@ Gen4 plan same as above
     ],
     "Inputs": [
       {
-        "OperatorType": "Route",
-        "Variant": "Scatter",
-        "Keyspace": {
-          "Name": "user",
-          "Sharded": true
-        },
-        "FieldQuery": "select col from `user` where 1 != 1",
-        "Query": "select col from `user`",
-        "Table": "`user`"
+        "OperatorType": "Limit",
+        "Count": "INT64(1)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from `user` where 1 != 1",
+            "Query": "select 1 from `user` limit :__upper_limit",
+            "Table": "`user`"
+          }
+        ]
       },
       {
         "OperatorType": "Route",
@@ -2614,7 +2626,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select u1.col from `user` as u1 where 1 != 1",
-    "Query": "select u1.col from `user` as u1 where u1.id = 5 and exists (select u2.`name` from `user` as u2 where u2.id = 5)",
+    "Query": "select u1.col from `user` as u1 where u1.id = 5 and exists (select 1 from `user` as u2 where u2.id = 5 limit 1)",
     "Table": "`user`",
     "Values": [
       "INT64(5)"
@@ -2637,7 +2649,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select u1.col from `user` as u1 where 1 != 1",
-    "Query": "select u1.col from `user` as u1 where u1.id = 5 and not exists (select u2.`name` from `user` as u2 where u2.id = 5)",
+    "Query": "select u1.col from `user` as u1 where u1.id = 5 and not exists (select 1 from `user` as u2 where u2.id = 5 limit 1)",
     "Table": "`user`",
     "Values": [
       "INT64(5)"
@@ -2667,8 +2679,8 @@ Gen4 plan same as above
           "Name": "user",
           "Sharded": true
         },
-        "FieldQuery": "select u2.`name` from `user` as u2 where 1 != 1",
-        "Query": "select u2.`name` from `user` as u2 where u2.id = 5",
+        "FieldQuery": "select 1 from `user` as u2 where 1 != 1",
+        "Query": "select 1 from `user` as u2 where u2.id = 5 limit 1",
         "Table": "`user`",
         "Values": [
           "INT64(5)"
@@ -2706,8 +2718,8 @@ Gen4 plan same as above
           "Name": "user",
           "Sharded": true
         },
-        "FieldQuery": "select u2.`name` from `user` as u2 where 1 != 1",
-        "Query": "select u2.`name` from `user` as u2 where u2.id = 5",
+        "FieldQuery": "select 1 from `user` as u2 where 1 != 1",
+        "Query": "select 1 from `user` as u2 where u2.id = 5 limit 1",
         "Table": "`user`",
         "Values": [
           "INT64(5)"
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases.txt b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
index 1a22f84b3e..5d5c4a0ccc 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
@@ -2979,7 +2979,7 @@ Gen4 plan same as above
       "Sharded": false
     },
     "FieldQuery": "select 1 from dual where 1 != 1",
-    "Query": "select 1 from dual where exists (select 1 from information_schema.`TABLES` where information_schema.`TABLES`.TABLE_NAME = :TABLES_TABLE_NAME and information_schema.`TABLES`.TABLE_SCHEMA = :__vtschemaname)",
+    "Query": "select 1 from dual where exists (select 1 from information_schema.`TABLES` where information_schema.`TABLES`.TABLE_NAME = :TABLES_TABLE_NAME and information_schema.`TABLES`.TABLE_SCHEMA = :__vtschemaname limit 1)",
     "SysTableTableName": "[TABLES_TABLE_NAME:VARCHAR(\"proc\")]",
     "SysTableTableSchema": "[VARCHAR(\"mysql\")]",
     "Table": "dual"
@@ -2996,7 +2996,7 @@ Gen4 plan same as above
       "Sharded": false
     },
     "FieldQuery": "select 1 from dual where 1 != 1",
-    "Query": "select 1 from dual where exists (select 1 from information_schema.`TABLES` where `TABLES`.TABLE_NAME = :TABLES_TABLE_NAME and `TABLES`.TABLE_SCHEMA = :__vtschemaname)",
+    "Query": "select 1 from dual where exists (select 1 from information_schema.`TABLES` where `TABLES`.TABLE_NAME = :TABLES_TABLE_NAME and `TABLES`.TABLE_SCHEMA = :__vtschemaname limit 1)",
     "SysTableTableName": "[TABLES_TABLE_NAME:VARCHAR(\"proc\")]",
     "SysTableTableSchema": "[VARCHAR(\"mysql\")]",
     "Table": "dual"
@@ -3132,7 +3132,7 @@ Gen4 plan same as above
           "Sharded": false
         },
         "FieldQuery": "select 1 from dual where 1 != 1",
-        "Query": "select 1 from dual",
+        "Query": "select 1 from dual limit 1",
         "Table": "dual"
       },
       {
@@ -3164,7 +3164,7 @@ Gen4 plan same as above
       "Sharded": true
     },
     "FieldQuery": "select exists (select 1 from dual where 1 != 1) from `user` where 1 != 1",
-    "Query": "select exists (select 1 from dual) from `user` where id = 5",
+    "Query": "select exists (select 1 from dual limit 1) from `user` where id = 5",
     "Table": "`user`",
     "Values": [
       "INT64(5)"
@@ -3345,8 +3345,8 @@ Gen4 plan same as above
           "Name": "user",
           "Sharded": true
         },
-        "FieldQuery": "select id from `user` where 1 != 1",
-        "Query": "select id from `user` where id = 4",
+        "FieldQuery": "select 1 from `user` where 1 != 1",
+        "Query": "select 1 from `user` where id = 4 limit 1",
         "Table": "`user`",
         "Values": [
           "INT64(4)"
@@ -3377,8 +3377,8 @@ Gen4 plan same as above
       "Name": "user",
       "Sharded": true
     },
-    "FieldQuery": "select exists (select id from `user` where 1 != 1) from dual where 1 != 1",
-    "Query": "select exists (select id from `user` where id = 4) from dual",
+    "FieldQuery": "select exists (select 1 from `user` where 1 != 1) from dual where 1 != 1",
+    "Query": "select exists (select 1 from `user` where id = 4 limit 1) from dual",
     "Table": "dual",
     "Values": [
       "INT64(4)"
@@ -3401,15 +3401,21 @@ Gen4 plan same as above
     ],
     "Inputs": [
       {
-        "OperatorType": "Route",
-        "Variant": "Scatter",
-        "Keyspace": {
-          "Name": "user",
-          "Sharded": true
-        },
-        "FieldQuery": "select * from `user` where 1 != 1",
-        "Query": "select * from `user`",
-        "Table": "`user`"
+        "OperatorType": "Limit",
+        "Count": "INT64(1)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from `user` where 1 != 1",
+            "Query": "select 1 from `user` limit :__upper_limit",
+            "Table": "`user`"
+          }
+        ]
       },
       {
         "OperatorType": "Route",
@@ -3436,15 +3442,21 @@ Gen4 plan same as above
     ],
     "Inputs": [
       {
-        "OperatorType": "Route",
-        "Variant": "Scatter",
-        "Keyspace": {
-          "Name": "user",
-          "Sharded": true
-        },
-        "FieldQuery": "select * from `user` where 1 != 1",
-        "Query": "select * from `user`",
-        "Table": "`user`"
+        "OperatorType": "Limit",
+        "Count": "INT64(1)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from `user` where 1 != 1",
+            "Query": "select 1 from `user` limit :__upper_limit",
+            "Table": "`user`"
+          }
+        ]
       },
       {
         "OperatorType": "Route",
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases_with_default.txt b/go/vt/vtgate/planbuilder/testdata/select_cases_with_default.txt
index 057916f9a6..6cba9e6182 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases_with_default.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases_with_default.txt
@@ -18,8 +18,8 @@
           "Name": "user",
           "Sharded": true
         },
-        "FieldQuery": "select * from `user` where 1 != 1",
-        "Query": "select * from `user` where id = 5",
+        "FieldQuery": "select 1 from `user` where 1 != 1",
+        "Query": "select 1 from `user` where id = 5 limit 1",
         "Table": "`user`",
         "Values": [
           "INT64(5)"
@@ -50,8 +50,8 @@
       "Name": "user",
       "Sharded": true
     },
-    "FieldQuery": "select exists (select * from `user` where 1 != 1) from dual where 1 != 1",
-    "Query": "select exists (select * from `user` where id = 5) from dual",
+    "FieldQuery": "select exists (select 1 from `user` where 1 != 1) from dual where 1 != 1",
+    "Query": "select exists (select 1 from `user` where id = 5 limit 1) from dual",
     "Table": "dual",
     "Values": [
       "INT64(5)"
diff --git a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
index 74b962f20a..c066ee6b57 100644
--- a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
@@ -1103,7 +1103,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                                   "Sharded": true
                                 },
                                 "FieldQuery": "select l1.l_orderkey, l1.l_suppkey, count(*) as numwait, weight_string(l1.l_orderkey), weight_string(l1.l_suppkey) from lineitem as l1 where 1 != 1 group by l1.l_orderkey, weight_string(l1.l_orderkey), l1.l_suppkey, weight_string(l1.l_suppkey)",
-                                "Query": "select l1.l_orderkey, l1.l_suppkey, count(*) as numwait, weight_string(l1.l_orderkey), weight_string(l1.l_suppkey) from lineitem as l1 where l1.l_receiptdate \u003e l1.l_commitdate and exists (select l2.l_orderkey, l2.l_partkey, l2.l_suppkey, l2.l_linenumber, l2.l_quantity, l2.l_extendedprice, l2.l_discount, l2.l_tax, l2.l_returnflag, l2.l_linestatus, l2.l_shipdate, l2.l_commitdate, l2.l_receiptdate, l2.l_shipinstruct, l2.l_shipmode, l2.l_comment from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey) and not exists (select l3.l_orderkey, l3.l_partkey, l3.l_suppkey, l3.l_linenumber, l3.l_quantity, l3.l_extendedprice, l3.l_discount, l3.l_tax, l3.l_returnflag, l3.l_linestatus, l3.l_shipdate, l3.l_commitdate, l3.l_receiptdate, l3.l_shipinstruct, l3.l_shipmode, l3.l_comment from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate) group by l1.l_orderkey, weight_string(l1.l_orderkey), l1.l_suppkey, weight_string(l1.l_suppkey)",
+                                "Query": "select l1.l_orderkey, l1.l_suppkey, count(*) as numwait, weight_string(l1.l_orderkey), weight_string(l1.l_suppkey) from lineitem as l1 where l1.l_receiptdate \u003e l1.l_commitdate and exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) group by l1.l_orderkey, weight_string(l1.l_orderkey), l1.l_suppkey, weight_string(l1.l_suppkey)",
                                 "Table": "lineitem"
                               },
                               {
@@ -1114,7 +1114,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                                   "Sharded": true
                                 },
                                 "FieldQuery": "select count(*) as numwait from orders where 1 != 1",
-                                "Query": "select count(*) as numwait from orders where o_orderstatus = 'F' and exists (select l2.l_orderkey, l2.l_partkey, l2.l_suppkey, l2.l_linenumber, l2.l_quantity, l2.l_extendedprice, l2.l_discount, l2.l_tax, l2.l_returnflag, l2.l_linestatus, l2.l_shipdate, l2.l_commitdate, l2.l_receiptdate, l2.l_shipinstruct, l2.l_shipmode, l2.l_comment from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey) and not exists (select l3.l_orderkey, l3.l_partkey, l3.l_suppkey, l3.l_linenumber, l3.l_quantity, l3.l_extendedprice, l3.l_discount, l3.l_tax, l3.l_returnflag, l3.l_linestatus, l3.l_shipdate, l3.l_commitdate, l3.l_receiptdate, l3.l_shipinstruct, l3.l_shipmode, l3.l_comment from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate) and o_orderkey = :l1_l_orderkey",
+                                "Query": "select count(*) as numwait from orders where o_orderstatus = 'F' and exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) and o_orderkey = :l1_l_orderkey",
                                 "Table": "orders",
                                 "Values": [
                                   ":l1_l_orderkey"
@@ -1140,7 +1140,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                                   "Sharded": true
                                 },
                                 "FieldQuery": "select s_nationkey, count(*) as numwait, weight_string(s_nationkey), s_name, weight_string(s_name) from supplier where 1 != 1 group by s_nationkey, weight_string(s_nationkey), s_name, weight_string(s_name)",
-                                "Query": "select s_nationkey, count(*) as numwait, weight_string(s_nationkey), s_name, weight_string(s_name) from supplier where exists (select l2.l_orderkey, l2.l_partkey, l2.l_suppkey, l2.l_linenumber, l2.l_quantity, l2.l_extendedprice, l2.l_discount, l2.l_tax, l2.l_returnflag, l2.l_linestatus, l2.l_shipdate, l2.l_commitdate, l2.l_receiptdate, l2.l_shipinstruct, l2.l_shipmode, l2.l_comment from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey) and not exists (select l3.l_orderkey, l3.l_partkey, l3.l_suppkey, l3.l_linenumber, l3.l_quantity, l3.l_extendedprice, l3.l_discount, l3.l_tax, l3.l_returnflag, l3.l_linestatus, l3.l_shipdate, l3.l_commitdate, l3.l_receiptdate, l3.l_shipinstruct, l3.l_shipmode, l3.l_comment from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate) and s_suppkey = :l1_l_suppkey group by s_nationkey, weight_string(s_nationkey), s_name, weight_string(s_name)",
+                                "Query": "select s_nationkey, count(*) as numwait, weight_string(s_nationkey), s_name, weight_string(s_name) from supplier where exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) and s_suppkey = :l1_l_suppkey group by s_nationkey, weight_string(s_nationkey), s_name, weight_string(s_name)",
                                 "Table": "supplier",
                                 "Values": [
                                   ":l1_l_suppkey"
@@ -1155,7 +1155,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                                   "Sharded": true
                                 },
                                 "FieldQuery": "select count(*) as numwait from nation where 1 != 1",
-                                "Query": "select count(*) as numwait from nation where n_name = 'SAUDI ARABIA' and exists (select l2.l_orderkey, l2.l_partkey, l2.l_suppkey, l2.l_linenumber, l2.l_quantity, l2.l_extendedprice, l2.l_discount, l2.l_tax, l2.l_returnflag, l2.l_linestatus, l2.l_shipdate, l2.l_commitdate, l2.l_receiptdate, l2.l_shipinstruct, l2.l_shipmode, l2.l_comment from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey) and not exists (select l3.l_orderkey, l3.l_partkey, l3.l_suppkey, l3.l_linenumber, l3.l_quantity, l3.l_extendedprice, l3.l_discount, l3.l_tax, l3.l_returnflag, l3.l_linestatus, l3.l_shipdate, l3.l_commitdate, l3.l_receiptdate, l3.l_shipinstruct, l3.l_shipmode, l3.l_comment from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate) and n_nationkey = :s_nationkey",
+                                "Query": "select count(*) as numwait from nation where n_name = 'SAUDI ARABIA' and exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) and n_nationkey = :s_nationkey",
                                 "Table": "nation",
                                 "Values": [
                                   ":s_nationkey"