Repository: vitessio/vitess
Pull request #8913 (2021-10-01 05:14:05)

Gen4: derived table with aliased column names
## Description

The changes introduced by https://github.com/vitessio/vitess/pull/8884 allow us to parse derived tables' aliased column names, like: `select t.i from (select id from user) t (i)`, here the `id` inner column becomes `i` on the outside.

This pull request adds supports for this kind of queries in Gen4.

## Related Issue(s)

- #7280 
- https://github.com/vitessio/vitess/pull/8884

## Checklist
- [ ] Tests were added or are not required
- [x] Documentation was added or is not required



Patch:
diff --git a/go/mysql/sql_error.go b/go/mysql/sql_error.go
index 73a18d5ae3..41a5af4a70 100644
--- a/go/mysql/sql_error.go
+++ b/go/mysql/sql_error.go
@@ -214,7 +214,7 @@ func convertToMysqlError(err error) error {
 	return NewSQLError(mysqlCode.num, mysqlCode.state, err.Error())
 }
 
-var isGRPCOverflowRE = regexp.MustCompile(`.*?grpc: (received|trying to send) message larger than max \(\d+ vs. \d+\)`)
+var isGRPCOverflowRE = regexp.MustCompile(`.*grpc: received message larger than max \(\d+ vs. \d+\)`)
 
 func demuxResourceExhaustedErrors(msg string) int {
 	switch {
diff --git a/go/vt/vtgate/engine/gen4_compare_v3.go b/go/vt/vtgate/engine/gen4_compare_v3.go
index 1a68b51b46..1792e9de05 100644
--- a/go/vt/vtgate/engine/gen4_compare_v3.go
+++ b/go/vt/vtgate/engine/gen4_compare_v3.go
@@ -71,8 +71,14 @@ func (gc *Gen4CompareV3) TryExecute(
 	bindVars map[string]*querypb.BindVariable,
 	wantfields bool,
 ) (*sqltypes.Result, error) {
-	gen4Result, gen4Err := gc.Gen4.TryExecute(vcursor, bindVars, wantfields)
-	v3Result, v3Err := gc.V3.TryExecute(vcursor, bindVars, wantfields)
+	var v3Err, gen4Err error
+	v3Result, gen4Result := &sqltypes.Result{}, &sqltypes.Result{}
+	if gc.Gen4 != nil {
+		gen4Result, gen4Err = gc.Gen4.TryExecute(vcursor, bindVars, wantfields)
+	}
+	if gc.V3 != nil {
+		v3Result, v3Err = gc.V3.TryExecute(vcursor, bindVars, wantfields)
+	}
 
 	if err := CompareV3AndGen4Errors(v3Err, gen4Err); err != nil {
 		return nil, err
@@ -91,16 +97,21 @@ func (gc *Gen4CompareV3) TryStreamExecute(
 	wantfields bool,
 	callback func(*sqltypes.Result) error,
 ) error {
+	var v3Err, gen4Err error
 	v3Result, gen4Result := &sqltypes.Result{}, &sqltypes.Result{}
 
-	gen4Err := gc.Gen4.TryStreamExecute(vcursor, bindVars, wantfields, func(result *sqltypes.Result) error {
-		gen4Result.AppendResult(result)
-		return nil
-	})
-	v3Err := gc.V3.TryStreamExecute(vcursor, bindVars, wantfields, func(result *sqltypes.Result) error {
-		v3Result.AppendResult(result)
-		return nil
-	})
+	if gc.Gen4 != nil {
+		gen4Err = gc.Gen4.TryStreamExecute(vcursor, bindVars, wantfields, func(result *sqltypes.Result) error {
+			gen4Result.AppendResult(result)
+			return nil
+		})
+	}
+	if gc.V3 != nil {
+		v3Err = gc.V3.TryStreamExecute(vcursor, bindVars, wantfields, func(result *sqltypes.Result) error {
+			v3Result.AppendResult(result)
+			return nil
+		})
+	}
 
 	if err := CompareV3AndGen4Errors(v3Err, gen4Err); err != nil {
 		return err
diff --git a/go/vt/vtgate/grpcvtgateservice/server.go b/go/vt/vtgate/grpcvtgateservice/server.go
index 7fb6ec1f39..115936bfac 100644
--- a/go/vt/vtgate/grpcvtgateservice/server.go
+++ b/go/vt/vtgate/grpcvtgateservice/server.go
@@ -163,9 +163,7 @@ func (vtg *VTGate) StreamExecute(request *vtgatepb.StreamExecuteRequest, stream
 	if session == nil {
 		session = &vtgatepb.Session{Autocommit: true}
 	}
-
-	// Do not set target if the tablet_type is not set correctly.
-	if session.TargetString == "" && request.TabletType != topodatapb.TabletType_UNKNOWN {
+	if session.TargetString == "" {
 		session.TargetString = request.KeyspaceShard + "@" + topoproto.TabletTypeLString(request.TabletType)
 	}
 	if session.Options == nil {
diff --git a/go/vt/vtgate/planbuilder/abstract/derived.go b/go/vt/vtgate/planbuilder/abstract/derived.go
index 394ec0245a..54db0e189e 100644
--- a/go/vt/vtgate/planbuilder/abstract/derived.go
+++ b/go/vt/vtgate/planbuilder/abstract/derived.go
@@ -25,9 +25,10 @@ import (
 
 // Derived represents a derived table in the query
 type Derived struct {
-	Sel   sqlparser.SelectStatement
-	Inner Operator
-	Alias string
+	Sel           sqlparser.SelectStatement
+	Inner         Operator
+	Alias         string
+	ColumnAliases sqlparser.Columns
 }
 
 var _ Operator = (*Derived)(nil)
diff --git a/go/vt/vtgate/planbuilder/abstract/operator.go b/go/vt/vtgate/planbuilder/abstract/operator.go
index e56cb9f6dc..f491ac1121 100644
--- a/go/vt/vtgate/planbuilder/abstract/operator.go
+++ b/go/vt/vtgate/planbuilder/abstract/operator.go
@@ -82,7 +82,7 @@ func getOperatorFromTableExpr(tableExpr sqlparser.TableExpr, semTable *semantics
 			if err != nil {
 				return nil, err
 			}
-			return &Derived{Alias: tableExpr.As.String(), Inner: inner, Sel: tbl.Select}, nil
+			return &Derived{Alias: tableExpr.As.String(), Inner: inner, Sel: tbl.Select, ColumnAliases: tableExpr.Columns}, nil
 		default:
 			return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "unable to use: %T", tbl)
 		}
diff --git a/go/vt/vtgate/planbuilder/derivedtree.go b/go/vt/vtgate/planbuilder/derivedtree.go
index c77bdb8258..8beba21b66 100644
--- a/go/vt/vtgate/planbuilder/derivedtree.go
+++ b/go/vt/vtgate/planbuilder/derivedtree.go
@@ -24,9 +24,10 @@ import (
 )
 
 type derivedTree struct {
-	query sqlparser.SelectStatement
-	inner queryTree
-	alias string
+	query         sqlparser.SelectStatement
+	inner         queryTree
+	alias         string
+	columnAliases sqlparser.Columns
 
 	// columns needed to feed other plans
 	columns       []*sqlparser.ColName
diff --git a/go/vt/vtgate/planbuilder/gen4_compare_v3_planner.go b/go/vt/vtgate/planbuilder/gen4_compare_v3_planner.go
index 59b5bc0327..0a4f4551c0 100644
--- a/go/vt/vtgate/planbuilder/gen4_compare_v3_planner.go
+++ b/go/vt/vtgate/planbuilder/gen4_compare_v3_planner.go
@@ -17,10 +17,16 @@ limitations under the License.
 package planbuilder
 
 import (
+	"strings"
+
 	"vitess.io/vitess/go/vt/sqlparser"
 	"vitess.io/vitess/go/vt/vtgate/engine"
 )
 
+type commentDirective struct {
+	onlyV3, onlyGen4 bool
+}
+
 func gen4CompareV3Planner(query string) func(sqlparser.Statement, *sqlparser.ReservedVars, ContextVSchema) (engine.Primitive, error) {
 	return func(statement sqlparser.Statement, vars *sqlparser.ReservedVars, ctxVSchema ContextVSchema) (engine.Primitive, error) {
 		// we will be switching the planner version to Gen4 and V3 in order to
@@ -29,32 +35,37 @@ func gen4CompareV3Planner(query string) func(sqlparser.Statement, *sqlparser.Res
 		defer ctxVSchema.SetPlannerVersion(Gen4CompareV3)
 
 		// preliminary checks on the given statement
-		onlyGen4, hasOrderBy, err := preliminaryChecks(statement)
+		onlyGen4, hasOrderBy, comments, err := preliminaryChecks(statement)
 		if err != nil {
 			return nil, err
 		}
+		cd := parseComments(comments)
 
 		// plan statement using Gen4
 		gen4Primitive, gen4Err := planWithPlannerVersion(statement, vars, ctxVSchema, query, Gen4)
 
+		// if onlyGen4 is set to true or Gen4's instruction contain a lock primitive,
+		// we use only Gen4's primitive and exit early without using V3's.
+		// since lock primitives can imply the creation or deletion of locks,
+		// we want to execute them once using Gen4 to avoid the duplicated locks
+		// or double lock-releases.
+		if !cd.onlyV3 && (onlyGen4 || cd.onlyGen4) || (gen4Primitive != nil && hasLockPrimitive(gen4Primitive)) {
+			return gen4Primitive, gen4Err
+		}
+
 		// get V3's plan
 		v3Primitive, v3Err := planWithPlannerVersion(statement, vars, ctxVSchema, query, V3)
 
+		if cd.onlyV3 && !cd.onlyGen4 && !onlyGen4 {
+			return v3Primitive, v3Err
+		}
+
 		// check potential errors from Gen4 and V3
 		err = engine.CompareV3AndGen4Errors(v3Err, gen4Err)
 		if err != nil {
 			return nil, err
 		}
 
-		// if onlyGen4 is set to true or Gen4's instruction contain a lock primitive,
-		// we use only Gen4's primitive and exit early without using V3's.
-		// since lock primitives can imply the creation or deletion of locks,
-		// we want to execute them once using Gen4 to avoid the duplicated locks
-		// or double lock-releases.
-		if onlyGen4 || hasLockPrimitive(gen4Primitive) {
-			return gen4Primitive, gen4Err
-		}
-
 		return &engine.Gen4CompareV3{
 			V3:         v3Primitive,
 			Gen4:       gen4Primitive,
@@ -63,11 +74,26 @@ func gen4CompareV3Planner(query string) func(sqlparser.Statement, *sqlparser.Res
 	}
 }
 
-func preliminaryChecks(statement sqlparser.Statement) (bool, bool, error) {
+func parseComments(comments []string) commentDirective {
+	cd := commentDirective{}
+	for _, comment := range comments {
+		if strings.Contains(comment, "GEN4_COMPARE_ONLY_V3") {
+			cd.onlyV3 = true
+		}
+		if strings.Contains(comment, "GEN4_COMPARE_ONLY_GEN4") {
+			cd.onlyGen4 = true
+		}
+	}
+	return cd
+}
+
+func preliminaryChecks(statement sqlparser.Statement) (bool, bool, []string, error) {
 	var onlyGen4, hasOrderBy bool
+	var comments []string
 	switch s := statement.(type) {
 	case *sqlparser.Union:
 		hasOrderBy = len(s.OrderBy) > 0
+		comments = s.GetComments()
 
 		// walk through the union and search for select statements that have
 		// a next val select expression, in which case we need to only use
@@ -81,10 +107,12 @@ func preliminaryChecks(statement sqlparser.Statement) (bool, bool, error) {
 			return true, nil
 		}, s)
 		if err != nil {
-			return false, false, err
+			return false, false, nil, err
 		}
 	case *sqlparser.Select:
 		hasOrderBy = len(s.OrderBy) > 0
+		comments = s.GetComments()
+
 		for _, expr := range s.SelectExprs {
 			// we are not executing the plan a second time if the query is a select next val,
 			// since the first execution might increment the `next` value, results will almost
@@ -95,7 +123,7 @@ func preliminaryChecks(statement sqlparser.Statement) (bool, bool, error) {
 			}
 		}
 	}
-	return onlyGen4, hasOrderBy, nil
+	return onlyGen4, hasOrderBy, comments, nil
 }
 
 func planWithPlannerVersion(statement sqlparser.Statement, vars *sqlparser.ReservedVars, ctxVSchema ContextVSchema, query string, version PlannerVersion) (engine.Primitive, error) {
diff --git a/go/vt/vtgate/planbuilder/horizon_planning.go b/go/vt/vtgate/planbuilder/horizon_planning.go
index 0cba5c4a72..064d0f92a8 100644
--- a/go/vt/vtgate/planbuilder/horizon_planning.go
+++ b/go/vt/vtgate/planbuilder/horizon_planning.go
@@ -180,6 +180,14 @@ func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *sem
 			return 0, false, vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.BadFieldError, "Unknown column '%s' in 'order clause'", sqlparser.String(expr))
 		}
 
+		// if we are trying to push a projection that belongs to a DerivedTable
+		// we rewrite that expression, so it matches the column name used inside
+		// that derived table.
+		err = rewriteProjectionOfDerivedTable(expr, semTable)
+		if err != nil {
+			return 0, false, err
+		}
+
 		offset := len(sel.SelectExprs)
 		sel.SelectExprs = append(sel.SelectExprs, expr)
 		return offset, true, nil
@@ -265,6 +273,22 @@ func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *sem
 	}
 }
 
+func rewriteProjectionOfDerivedTable(expr *sqlparser.AliasedExpr, semTable *semantics.SemTable) error {
+	var err error
+	ti, _ := semTable.TableInfoForExpr(expr.Expr)
+	if ti == nil {
+		return nil
+	}
+	_, isDerivedTable := ti.(*semantics.DerivedTable)
+	if isDerivedTable {
+		expr.Expr, err = semantics.RewriteDerivedExpression(expr.Expr, ti)
+		if err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
 func removeKeyspaceFromColName(expr *sqlparser.AliasedExpr) *sqlparser.AliasedExpr {
 	if _, ok := expr.Expr.(*sqlparser.ColName); ok {
 		expr = sqlparser.CloneRefOfAliasedExpr(expr)
diff --git a/go/vt/vtgate/planbuilder/querytree_transformers.go b/go/vt/vtgate/planbuilder/querytree_transformers.go
index c7c4c7c5d8..570af070f9 100644
--- a/go/vt/vtgate/planbuilder/querytree_transformers.go
+++ b/go/vt/vtgate/planbuilder/querytree_transformers.go
@@ -135,8 +135,9 @@ func transformDerivedPlan(ctx *planningContext, n *derivedTree) (logicalPlan, er
 	innerSelect := rb.Select
 	derivedTable := &sqlparser.DerivedTable{Select: innerSelect}
 	tblExpr := &sqlparser.AliasedTableExpr{
-		Expr: derivedTable,
-		As:   sqlparser.NewTableIdent(n.alias),
+		Expr:    derivedTable,
+		As:      sqlparser.NewTableIdent(n.alias),
+		Columns: n.columnAliases,
 	}
 	selectExprs := sqlparser.SelectExprs{}
 	for _, colName := range n.columns {
diff --git a/go/vt/vtgate/planbuilder/route_planning.go b/go/vt/vtgate/planbuilder/route_planning.go
index 4cdfe5aa6c..00efccc580 100644
--- a/go/vt/vtgate/planbuilder/route_planning.go
+++ b/go/vt/vtgate/planbuilder/route_planning.go
@@ -74,9 +74,10 @@ func optimizeQuery(ctx *planningContext, opTree abstract.Operator) (queryTree, e
 			return nil, err
 		}
 		return &derivedTree{
-			query: op.Sel,
-			inner: treeInner,
-			alias: op.Alias,
+			query:         op.Sel,
+			inner:         treeInner,
+			alias:         op.Alias,
+			columnAliases: op.ColumnAliases,
 		}, nil
 	case *abstract.SubQuery:
 		return optimizeSubQuery(ctx, op)
diff --git a/go/vt/vtgate/semantics/derived_table.go b/go/vt/vtgate/semantics/derived_table.go
index 560d4789e5..4f9a32f140 100644
--- a/go/vt/vtgate/semantics/derived_table.go
+++ b/go/vt/vtgate/semantics/derived_table.go
@@ -23,7 +23,7 @@ import (
 	"vitess.io/vitess/go/vt/vtgate/vindexes"
 )
 
-type derivedTable struct {
+type DerivedTable struct {
 	tableName   string
 	ASTNode     *sqlparser.AliasedTableExpr
 	columnNames []string
@@ -31,15 +31,17 @@ type derivedTable struct {
 	tables      TableSet
 }
 
-var _ TableInfo = (*derivedTable)(nil)
+var _ TableInfo = (*DerivedTable)(nil)
 
-func createDerivedTableForExpressions(expressions sqlparser.SelectExprs, tables []TableInfo, org originable) *derivedTable {
-	vTbl := &derivedTable{}
-	for _, selectExpr := range expressions {
+func createDerivedTableForExpressions(expressions sqlparser.SelectExprs, cols sqlparser.Columns, tables []TableInfo, org originable) *DerivedTable {
+	vTbl := &DerivedTable{}
+	for i, selectExpr := range expressions {
 		switch expr := selectExpr.(type) {
 		case *sqlparser.AliasedExpr:
 			vTbl.cols = append(vTbl.cols, expr.Expr)
-			if expr.As.IsEmpty() {
+			if len(cols) > 0 {
+				vTbl.columnNames = append(vTbl.columnNames, cols[i].String())
+			} else if expr.As.IsEmpty() {
 				switch expr := expr.Expr.(type) {
 				case *sqlparser.ColName:
 					// for projections, we strip out the qualifier and keep only the column name
@@ -60,7 +62,7 @@ func createDerivedTableForExpressions(expressions sqlparser.SelectExprs, tables
 }
 
 // Dependencies implements the TableInfo interface
-func (dt *derivedTable) dependencies(colName string, org originable) (dependencies, error) {
+func (dt *DerivedTable) dependencies(colName string, org originable) (dependencies, error) {
 	for i, name := range dt.columnNames {
 		if name != colName {
 			continue
@@ -85,32 +87,32 @@ func (dt *derivedTable) dependencies(colName string, org originable) (dependenci
 }
 
 // IsInfSchema implements the TableInfo interface
-func (dt *derivedTable) IsInfSchema() bool {
+func (dt *DerivedTable) IsInfSchema() bool {
 	return false
 }
 
-func (dt *derivedTable) matches(name sqlparser.TableName) bool {
+func (dt *DerivedTable) matches(name sqlparser.TableName) bool {
 	return dt.tableName == name.Name.String() && name.Qualifier.IsEmpty()
 }
 
-func (dt *derivedTable) authoritative() bool {
+func (dt *DerivedTable) authoritative() bool {
 	return true
 }
 
-func (dt *derivedTable) Name() (sqlparser.TableName, error) {
+func (dt *DerivedTable) Name() (sqlparser.TableName, error) {
 	return dt.ASTNode.TableName()
 }
 
-func (dt *derivedTable) getExpr() *sqlparser.AliasedTableExpr {
+func (dt *DerivedTable) getExpr() *sqlparser.AliasedTableExpr {
 	return dt.ASTNode
 }
 
 // GetVindexTable implements the TableInfo interface
-func (dt *derivedTable) GetVindexTable() *vindexes.Table {
+func (dt *DerivedTable) GetVindexTable() *vindexes.Table {
 	return nil
 }
 
-func (dt *derivedTable) getColumns() []ColumnInfo {
+func (dt *DerivedTable) getColumns() []ColumnInfo {
 	cols := make([]ColumnInfo, 0, len(dt.columnNames))
 	for _, col := range dt.columnNames {
 		cols = append(cols, ColumnInfo{
@@ -120,17 +122,17 @@ func (dt *derivedTable) getColumns() []ColumnInfo {
 	return cols
 }
 
-func (dt *derivedTable) hasStar() bool {
+func (dt *DerivedTable) hasStar() bool {
 	return dt.tables.NumberOfTables() > 0
 }
 
 // GetTables implements the TableInfo interface
-func (dt *derivedTable) getTableSet(_ originable) TableSet {
+func (dt *DerivedTable) getTableSet(_ originable) TableSet {
 	return dt.tables
 }
 
 // GetExprFor implements the TableInfo interface
-func (dt *derivedTable) getExprFor(s string) (sqlparser.Expr, error) {
+func (dt *DerivedTable) getExprFor(s string) (sqlparser.Expr, error) {
 	for i, colName := range dt.columnNames {
 		if colName == s {
 			return dt.cols[i], nil
@@ -139,7 +141,7 @@ func (dt *derivedTable) getExprFor(s string) (sqlparser.Expr, error) {
 	return nil, vterrors.NewErrorf(vtrpcpb.Code_NOT_FOUND, vterrors.BadFieldError, "Unknown column '%s' in 'field list'", s)
 }
 
-func (dt *derivedTable) checkForDuplicates() error {
+func (dt *DerivedTable) checkForDuplicates() error {
 	for i, name := range dt.columnNames {
 		for j, name2 := range dt.columnNames {
 			if i == j {
diff --git a/go/vt/vtgate/semantics/table_collector.go b/go/vt/vtgate/semantics/table_collector.go
index bf91457c47..bc8c648838 100644
--- a/go/vt/vtgate/semantics/table_collector.go
+++ b/go/vt/vtgate/semantics/table_collector.go
@@ -47,15 +47,12 @@ func (tc *tableCollector) up(cursor *sqlparser.Cursor) error {
 	if !ok {
 		return nil
 	}
-	if node.Columns != nil {
-		return Gen4NotSupportedF("column aliases in derived table")
-	}
 	switch t := node.Expr.(type) {
 	case *sqlparser.DerivedTable:
 		switch sel := t.Select.(type) {
 		case *sqlparser.Select:
 			tables := tc.scoper.wScope[sel]
-			tableInfo := createDerivedTableForExpressions(sqlparser.GetFirstSelect(sel).SelectExprs, tables.tables, tc.org)
+			tableInfo := createDerivedTableForExpressions(sqlparser.GetFirstSelect(sel).SelectExprs, node.Columns, tables.tables, tc.org)
 			if err := tableInfo.checkForDuplicates(); err != nil {
 				return err
 			}
@@ -70,7 +67,7 @@ func (tc *tableCollector) up(cursor *sqlparser.Cursor) error {
 		case *sqlparser.Union:
 			firstSelect := sqlparser.GetFirstSelect(sel)
 			tables := tc.scoper.wScope[firstSelect]
-			tableInfo := createDerivedTableForExpressions(firstSelect.SelectExprs, tables.tables, tc.org)
+			tableInfo := createDerivedTableForExpressions(firstSelect.SelectExprs, node.Columns, tables.tables, tc.org)
 			if err := tableInfo.checkForDuplicates(); err != nil {
 				return err
 			}


Test patch:
diff --git a/go/mysql/sql_error_test.go b/go/mysql/sql_error_test.go
index c6fe2f6525..7efb166953 100644
--- a/go/mysql/sql_error_test.go
+++ b/go/mysql/sql_error_test.go
@@ -36,9 +36,6 @@ func TestDumuxResourceExhaustedErrors(t *testing.T) {
 		{"grpc: received message larger than max (99282 vs. 1234): trailer", ERNetPacketTooLarge},
 		{"grpc: received message larger than max (1234 vs. 1234)", ERNetPacketTooLarge},
 		{"header: grpc: received message larger than max (1234 vs. 1234)", ERNetPacketTooLarge},
-		{"grpc: trying to send message larger than max (18015277 vs. 16777216)", ERNetPacketTooLarge},
-		{"grpc: trying to send message larger than max (18015277 vs. 16777216): trailer", ERNetPacketTooLarge},
-		{"header: grpc: trying to send message larger than max (18015277 vs. 16777216)", ERNetPacketTooLarge},
 		// This should be explicitly handled by returning ERNetPacketTooLarge from the execturo directly
 		// and therefore shouldn't need to be teased out of another error.
 		{"in-memory row count exceeded allowed limit of 13", ERTooManyUserConnections},
diff --git a/go/test/endtoend/cluster/cluster_process.go b/go/test/endtoend/cluster/cluster_process.go
index 801c62a69f..64ba79b485 100644
--- a/go/test/endtoend/cluster/cluster_process.go
+++ b/go/test/endtoend/cluster/cluster_process.go
@@ -446,11 +446,11 @@ func (cluster *LocalProcessCluster) StartVtgate() (err error) {
 // NewVtgateInstance returns an instance of vtgateprocess
 func (cluster *LocalProcessCluster) NewVtgateInstance() *VtgateProcess {
 	vtgateHTTPPort := cluster.GetAndReservePort()
-	cluster.VtgateGrpcPort = cluster.GetAndReservePort()
+	vtgateGrpcPort := cluster.GetAndReservePort()
 	cluster.VtgateMySQLPort = cluster.GetAndReservePort()
 	vtgateProcInstance := VtgateProcessInstance(
 		vtgateHTTPPort,
-		cluster.VtgateGrpcPort,
+		vtgateGrpcPort,
 		cluster.VtgateMySQLPort,
 		cluster.Cell,
 		cluster.Cell,
diff --git a/go/test/endtoend/vtgate/godriver/main_test.go b/go/test/endtoend/vtgate/godriver/main_test.go
deleted file mode 100644
index 0bf81e75bd..0000000000
--- a/go/test/endtoend/vtgate/godriver/main_test.go
+++ /dev/null
@@ -1,155 +0,0 @@
-/*
-Copyright 2020 The Vitess Authors.
-
-Licensed under the Apache License, Version 2.0 (the "License");
-you may not use this file except in compliance with the License.
-You may obtain a copy of the License at
-
-    http://www.apache.org/licenses/LICENSE-2.0
-
-Unless required by applicable law or agreed to in writing, software
-distributed under the License is distributed on an "AS IS" BASIS,
-WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-See the License for the specific language governing permissions and
-limitations under the License.
-*/
-
-package godriver
-
-import (
-	"database/sql"
-	"flag"
-	"os"
-	"strconv"
-	"testing"
-	"time"
-
-	"github.com/stretchr/testify/assert"
-	"github.com/stretchr/testify/require"
-	"google.golang.org/grpc"
-	"google.golang.org/grpc/keepalive"
-
-	"vitess.io/vitess/go/vt/log"
-	"vitess.io/vitess/go/vt/vitessdriver"
-
-	"vitess.io/vitess/go/test/endtoend/cluster"
-)
-
-var (
-	clusterInstance *cluster.LocalProcessCluster
-	cell            = "zone1"
-	hostname        = "localhost"
-	KeyspaceName    = "customer"
-	SchemaSQL       = `
-create table my_message(
-  time_scheduled bigint,
-  id bigint,
-  time_next bigint,
-  epoch bigint,
-  time_created bigint,
-  time_acked bigint,
-  message varchar(128),
-  priority tinyint NOT NULL DEFAULT '0',
-  primary key(time_scheduled, id),
-  unique index id_idx(id),
-  index next_idx(priority, time_next)
-) comment 'vitess_message,vt_ack_wait=30,vt_purge_after=86400,vt_batch_size=10,vt_cache_size=10000,vt_poller_interval=30';
-`
-	VSchema = `
-{
-  "sharded": true,
-  "vindexes": {
-    "hash": {
-      "type": "hash"
-    }
-  },
-  "tables": {
-    "my_message": {
-      "column_vindexes": [
-        {
-          "column": "id",
-          "name": "hash"
-        }
-      ]
-    }
-  }
-}
-`
-)
-
-func TestMain(m *testing.M) {
-	defer cluster.PanicHandler(nil)
-	flag.Parse()
-
-	exitCode := func() int {
-		clusterInstance = cluster.NewCluster(cell, hostname)
-		defer clusterInstance.Teardown()
-
-		// Start topo server
-		if err := clusterInstance.StartTopo(); err != nil {
-			return 1
-		}
-
-		// Start keyspace
-		Keyspace := &cluster.Keyspace{
-			Name:      KeyspaceName,
-			SchemaSQL: SchemaSQL,
-			VSchema:   VSchema,
-		}
-		clusterInstance.VtTabletExtraArgs = []string{"-queryserver-config-transaction-timeout", "3"}
-		if err := clusterInstance.StartKeyspace(*Keyspace, []string{"-80", "80-"}, 1, false); err != nil {
-			log.Fatal(err.Error())
-			return 1
-		}
-
-		// Start vtgate
-		clusterInstance.VtGateExtraArgs = []string{"-warn_sharded_only=true"}
-		if err := clusterInstance.StartVtgate(); err != nil {
-			log.Fatal(err.Error())
-			return 1
-		}
-
-		return m.Run()
-	}()
-	os.Exit(exitCode)
-}
-
-func TestStreamMessaging(t *testing.T) {
-	defer cluster.PanicHandler(t)
-
-	cnf := vitessdriver.Configuration{
-		Protocol: "grpc",
-		Address:  clusterInstance.Hostname + ":" + strconv.Itoa(clusterInstance.VtgateGrpcPort),
-		GRPCDialOptions: []grpc.DialOption{
-			grpc.WithDefaultCallOptions(),
-			grpc.WithKeepaliveParams(keepalive.ClientParameters{
-				Time:                300 * time.Second,
-				Timeout:             600 * time.Second,
-				PermitWithoutStream: true,
-			}),
-		},
-	}
-
-	// for inserting data
-	db, err := vitessdriver.OpenWithConfiguration(cnf)
-	require.NoError(t, err)
-	defer db.Close()
-
-	// Exec not allowed in streaming
-	timenow := time.Now().Add(time.Second * 60).UnixNano()
-	_, err = db.Exec("insert into my_message(id, message, time_scheduled) values(1, 'hello world', :curr_time)", sql.Named("curr_time", timenow))
-	require.NoError(t, err)
-
-	// for streaming messages
-	cnf.Streaming = true
-	streamDB, err := vitessdriver.OpenWithConfiguration(cnf)
-	require.NoError(t, err)
-	defer streamDB.Close()
-
-	// Exec not allowed in streaming
-	_, err = streamDB.Exec("stream * from my_message")
-	assert.EqualError(t, err, "Exec not allowed for streaming connections")
-
-	row := streamDB.QueryRow("stream * from my_message")
-	require.NoError(t, row.Err())
-}
diff --git a/go/test/endtoend/vtgate/mysql80/derived/derived_test.go b/go/test/endtoend/vtgate/mysql80/derived/derived_test.go
new file mode 100644
index 0000000000..0b6acdc338
--- /dev/null
+++ b/go/test/endtoend/vtgate/mysql80/derived/derived_test.go
@@ -0,0 +1,42 @@
+/*
+Copyright 2021 The Vitess Authors.
+
+Licensed under the Apache License, Version 2.0 (the "License");
+you may not use this file except in compliance with the License.
+You may obtain a copy of the License at
+
+    http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package derived
+
+import (
+	"context"
+	"testing"
+
+	"github.com/stretchr/testify/require"
+
+	"vitess.io/vitess/go/mysql"
+	"vitess.io/vitess/go/test/endtoend/cluster"
+	"vitess.io/vitess/go/test/endtoend/vtgate/utils"
+)
+
+func TestDerivedTableColumns(t *testing.T) {
+	defer cluster.PanicHandler(t)
+	ctx := context.Background()
+	conn, err := mysql.Connect(ctx, &vtParams)
+	require.NoError(t, err)
+	defer conn.Close()
+
+	utils.Exec(t, conn, `delete from t1`)
+	defer utils.Exec(t, conn, `delete from t1`)
+
+	utils.Exec(t, conn, "insert into t1(id1, id2) values (0,10),(1,9),(2,8),(3,7),(4,6),(5,5)")
+	utils.AssertMatches(t, conn, `SELECT /* GEN4_COMPARE_ONLY_GEN4 */ t.id FROM (SELECT id2 FROM t1) AS t(id) ORDER BY t.id DESC`, `[[INT64(10)] [INT64(9)] [INT64(8)] [INT64(7)] [INT64(6)] [INT64(5)]]`)
+}
diff --git a/go/test/endtoend/vtgate/mysql80/derived/main_test.go b/go/test/endtoend/vtgate/mysql80/derived/main_test.go
new file mode 100644
index 0000000000..31bc35c91b
--- /dev/null
+++ b/go/test/endtoend/vtgate/mysql80/derived/main_test.go
@@ -0,0 +1,129 @@
+/*
+Copyright 2021 The Vitess Authors.
+
+Licensed under the Apache License, Version 2.0 (the "License");
+you may not use this file except in compliance with the License.
+You may obtain a copy of the License at
+
+    http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package derived
+
+import (
+	"flag"
+	"os"
+	"testing"
+
+	"vitess.io/vitess/go/mysql"
+	"vitess.io/vitess/go/test/endtoend/cluster"
+)
+
+var (
+	clusterInstance *cluster.LocalProcessCluster
+	vtParams        mysql.ConnParams
+	KeyspaceName    = "ks_union"
+	Cell            = "test_union"
+	SchemaSQL       = `create table t1(
+	id1 bigint,
+	id2 bigint,
+	primary key(id1)
+) Engine=InnoDB;
+
+create table t1_id2_idx(
+	id2 bigint,
+	keyspace_id varbinary(10),
+	primary key(id2)
+) Engine=InnoDB;
+`
+
+	VSchema = `
+{
+  "sharded": true,
+  "vindexes": {
+    "hash": {
+      "type": "hash"
+    },
+    "t1_id2_vdx": {
+      "type": "consistent_lookup_unique",
+      "params": {
+        "table": "t1_id2_idx",
+        "from": "id2",
+        "to": "keyspace_id"
+      },
+      "owner": "t1"
+    }
+  },
+  "tables": {
+    "t1": {
+      "column_vindexes": [
+        {
+          "column": "id1",
+          "name": "hash"
+        },
+        {
+          "column": "id2",
+          "name": "t1_id2_vdx"
+        }
+      ]
+    },
+    "t1_id2_idx": {
+      "column_vindexes": [
+        {
+          "column": "id2",
+          "name": "hash"
+        }
+      ]
+    }
+  }
+}`
+)
+
+func TestMain(m *testing.M) {
+	defer cluster.PanicHandler(nil)
+	flag.Parse()
+
+	exitCode := func() int {
+		clusterInstance = cluster.NewCluster(Cell, "localhost")
+		defer clusterInstance.Teardown()
+
+		// Start topo server
+		err := clusterInstance.StartTopo()
+		if err != nil {
+			return 1
+		}
+
+		// Start keyspace
+		keyspace := &cluster.Keyspace{
+			Name:      KeyspaceName,
+			SchemaSQL: SchemaSQL,
+			VSchema:   VSchema,
+		}
+		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)
+		if err != nil {
+			return 1
+		}
+
+		clusterInstance.VtGateExtraArgs = append(clusterInstance.VtGateExtraArgs, "-enable_system_settings=true")
+		// Start vtgate
+		err = clusterInstance.StartVtgate()
+		if err != nil {
+			return 1
+		}
+
+		vtParams = mysql.ConnParams{
+			Host: clusterInstance.Hostname,
+			Port: clusterInstance.VtgateMySQLPort,
+		}
+		return m.Run()
+	}()
+	os.Exit(exitCode)
+}
diff --git a/go/test/endtoend/vtgate/queries/informationschema/informationschema_test.go b/go/test/endtoend/vtgate/queries/informationschema/informationschema_test.go
index 041ce870bc..53c228b907 100644
--- a/go/test/endtoend/vtgate/queries/informationschema/informationschema_test.go
+++ b/go/test/endtoend/vtgate/queries/informationschema/informationschema_test.go
@@ -86,8 +86,8 @@ func TestInformationSchemaQueryGetsRoutedToTheRightTableAndKeyspace(t *testing.T
 
 	utils.Exec(t, conn, "insert into t1(id1, id2) values (1, 1), (2, 2), (3,3), (4,4)")
 
-	_ = utils.Exec(t, conn, "SELECT * FROM t1000") // test that the routed table is available to us
-	result := utils.Exec(t, conn, "SELECT * FROM information_schema.tables WHERE table_schema = database() and table_name='t1000'")
+	_ = utils.Exec(t, conn, "SELECT /* GEN4_COMPARE_ONLY_GEN4 */ * FROM t1000") // test that the routed table is available to us
+	result := utils.Exec(t, conn, "SELECT /* GEN4_COMPARE_ONLY_GEN4 */ * FROM information_schema.tables WHERE table_schema = database() and table_name='t1000'")
 	assert.NotEmpty(t, result.Rows)
 }
 
diff --git a/go/vt/vtgate/planbuilder/testdata/from_cases.txt b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
index 467f9b7582..3f540ef02f 100644
--- a/go/vt/vtgate/planbuilder/testdata/from_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
@@ -4161,8 +4161,100 @@ Gen4 plan same as above
               "Name": "user",
               "Sharded": true
             },
-            "FieldQuery": "select `user`.id, `user`.col, id + 1 from `user` where 1 != 1",
-            "Query": "select `user`.id, `user`.col, id + 1 from `user`",
+            "FieldQuery": "select `user`.id, `user`.col, `user`.id + 1 from `user` where 1 != 1",
+            "Query": "select `user`.id, `user`.col, `user`.id + 1 from `user`",
+            "Table": "`user`"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from user_extra where 1 != 1",
+            "Query": "select 1 from user_extra",
+            "Table": "user_extra"
+          }
+        ]
+      }
+    ]
+  }
+}
+
+# derived table with aliased columns and outer predicate pushed in derived table
+"select u.a from (select id as b, name from user) u(a, n) where u.n = 1"
+"unsupported: column aliases in derived table"
+{
+  "QueryType": "SELECT",
+  "Original": "select u.a from (select id as b, name from user) u(a, n) where u.n = 1",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectEqual",
+    "Keyspace": {
+      "Name": "user",
+      "Sharded": true
+    },
+    "FieldQuery": "select u.a from (select id as b, `name` from `user` where 1 != 1) as u(a, n) where 1 != 1",
+    "Query": "select u.a from (select id as b, `name` from `user` where `name` = 1) as u(a, n)",
+    "Table": "`user`",
+    "Values": [
+      1
+    ],
+    "Vindex": "name_user_map"
+  }
+}
+
+# derived table with aliased columns predicate in both the outer and inner
+"select u.a from (select id as b, name from user where b = 1) u(a, n) where u.n = 1"
+"unsupported: column aliases in derived table"
+{
+  "QueryType": "SELECT",
+  "Original": "select u.a from (select id as b, name from user where b = 1) u(a, n) where u.n = 1",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectEqual",
+    "Keyspace": {
+      "Name": "user",
+      "Sharded": true
+    },
+    "FieldQuery": "select u.a from (select id as b, `name` from `user` where 1 != 1) as u(a, n) where 1 != 1",
+    "Query": "select u.a from (select id as b, `name` from `user` where b = 1 and `name` = 1) as u(a, n)",
+    "Table": "`user`",
+    "Values": [
+      1
+    ],
+    "Vindex": "name_user_map"
+  }
+}
+
+# derived table with aliased columns and a join that requires pushProjection
+"select i+1 from (select user.id from user join user_extra) t(i)"
+"unsupported: column aliases in derived table"
+{
+  "QueryType": "SELECT",
+  "Original": "select i+1 from (select user.id from user join user_extra) t(i)",
+  "Instructions": {
+    "OperatorType": "SimpleProjection",
+    "Columns": [
+      1
+    ],
+    "Inputs": [
+      {
+        "OperatorType": "Join",
+        "Variant": "Join",
+        "JoinColumnIndexes": "-1,-2",
+        "TableName": "`user`_user_extra",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select `user`.id, `user`.id + 1 from `user` where 1 != 1",
+            "Query": "select `user`.id, `user`.id + 1 from `user`",
             "Table": "`user`"
           },
           {
diff --git a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
index 1f405def6a..25a7e29c63 100644
--- a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
@@ -542,7 +542,7 @@ Gen4 error: unsupported: in scatter query: complex aggregate expression
 # TPC-H query 13
 "select c_count, count(*) as custdist from ( select c_custkey, count(o_orderkey) from customer left outer join orders on c_custkey = o_custkey and o_comment not like '%special%requests%' group by c_custkey ) as c_orders(c_custkey, c_count) group by c_count order by custdist desc, c_count desc"
 "unsupported: column aliases in derived table"
-gen4 does not yet support: column aliases in derived table
+Gen4 error: unsupported: cross-shard left join and column expressions
 
 # TPC-H query 14
 "select 100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount) else 0 end) /  sum(l_extendedprice * (1 - l_discount)) as promo_revenue from lineitem, part where l_partkey = p_partkey and l_shipdate >= date('1995-09-01') and l_shipdate < date('1995-09-01') + interval '1' month"
diff --git a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
index 9fa7325fe8..b14b439ea6 100644
--- a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
@@ -581,5 +581,18 @@ Gen4 error: Column 'id' in field list is ambiguous
 # Column aliases in Derived Table
 "select id2 from (select id from user) as x (id2)"
 "unsupported: column aliases in derived table"
-gen4 does not yet support: column aliases in derived table
-
+{
+  "QueryType": "SELECT",
+  "Original": "select id2 from (select id from user) as x (id2)",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectScatter",
+    "Keyspace": {
+      "Name": "user",
+      "Sharded": true
+    },
+    "FieldQuery": "select id2 from (select id from `user` where 1 != 1) as x(id2) where 1 != 1",
+    "Query": "select id2 from (select id from `user`) as x(id2)",
+    "Table": "`user`"
+  }
+}
diff --git a/test/config.json b/test/config.json
index b94f8c432d..696beb0a60 100644
--- a/test/config.json
+++ b/test/config.json
@@ -732,6 +732,15 @@
 			"RetryMax": 1,
 			"Tags": []
 		},
+		"vtgate_mysql80_derived": {
+			"File": "unused.go",
+			"Args": ["vitess.io/vitess/go/test/endtoend/vtgate/mysql80/derived"],
+			"Command": [],
+			"Manual": false,
+			"Shard": "mysql80",
+			"RetryMax": 1,
+			"Tags": []
+		},
 		"vtgate_sequence": {
 			"File": "unused.go",
 			"Args": ["vitess.io/vitess/go/test/endtoend/vtgate/sequence"],