Repository: vitessio/vitess
Pull request #8565 (2021-07-29 10:04:06)

Adds support for resolving columns using column vindexes in Gen4
## Description
Adds support for resolving columns using column vindexes in Gen4


## Related Issue(s)
#7280 

## Checklist
- [x] Tests were added or are not required
- [x] Documentation was added or is not required


Patch:
diff --git a/go/vt/vtgate/engine/join.go b/go/vt/vtgate/engine/join.go
index cbdb8d5815..a909dddef3 100644
--- a/go/vt/vtgate/engine/join.go
+++ b/go/vt/vtgate/engine/join.go
@@ -262,6 +262,9 @@ func (jn *Join) description() PrimitiveDescription {
 		"TableName":         jn.GetTableName(),
 		"JoinColumnIndexes": strings.Trim(strings.Join(strings.Fields(fmt.Sprint(jn.Cols)), ","), "[]"),
 	}
+	if len(jn.Vars) > 0 {
+		other["JoinVars"] = jn.Vars
+	}
 	return PrimitiveDescription{
 		OperatorType: "Join",
 		Variant:      jn.Opcode.String(),
diff --git a/go/vt/vtgate/engine/ordered_aggregate.go b/go/vt/vtgate/engine/ordered_aggregate.go
index e78db30f25..f2045c85c5 100644
--- a/go/vt/vtgate/engine/ordered_aggregate.go
+++ b/go/vt/vtgate/engine/ordered_aggregate.go
@@ -205,10 +205,10 @@ func (oa *OrderedAggregate) execute(vcursor VCursor, bindVars map[string]*queryp
 	}
 	// This code is similar to the one in StreamExecute.
 	var current []sqltypes.Value
-	var curDistinct sqltypes.Value
+	var curDistincts []sqltypes.Value
 	for _, row := range result.Rows {
 		if current == nil {
-			current, curDistinct = oa.convertRow(row)
+			current, curDistincts = oa.convertRow(row)
 			continue
 		}
 
@@ -218,14 +218,14 @@ func (oa *OrderedAggregate) execute(vcursor VCursor, bindVars map[string]*queryp
 		}
 
 		if equal {
-			current, curDistinct, err = oa.merge(result.Fields, current, row, curDistinct)
+			current, curDistincts, err = oa.merge(result.Fields, current, row, curDistincts)
 			if err != nil {
 				return nil, err
 			}
 			continue
 		}
 		out.Rows = append(out.Rows, current)
-		current, curDistinct = oa.convertRow(row)
+		current, curDistincts = oa.convertRow(row)
 	}
 
 	if len(result.Rows) == 0 && len(oa.GroupByKeys) == 0 {
@@ -251,7 +251,7 @@ func (oa *OrderedAggregate) execute(vcursor VCursor, bindVars map[string]*queryp
 // StreamExecute is a Primitive function.
 func (oa *OrderedAggregate) StreamExecute(vcursor VCursor, bindVars map[string]*querypb.BindVariable, wantfields bool, callback func(*sqltypes.Result) error) error {
 	var current []sqltypes.Value
-	var curDistinct sqltypes.Value
+	var curDistincts []sqltypes.Value
 	var fields []*querypb.Field
 
 	cb := func(qr *sqltypes.Result) error {
@@ -268,7 +268,7 @@ func (oa *OrderedAggregate) StreamExecute(vcursor VCursor, bindVars map[string]*
 		// This code is similar to the one in Execute.
 		for _, row := range qr.Rows {
 			if current == nil {
-				current, curDistinct = oa.convertRow(row)
+				current, curDistincts = oa.convertRow(row)
 				continue
 			}
 
@@ -278,7 +278,7 @@ func (oa *OrderedAggregate) StreamExecute(vcursor VCursor, bindVars map[string]*
 			}
 
 			if equal {
-				current, curDistinct, err = oa.merge(fields, current, row, curDistinct)
+				current, curDistincts, err = oa.merge(fields, current, row, curDistincts)
 				if err != nil {
 					return err
 				}
@@ -287,7 +287,7 @@ func (oa *OrderedAggregate) StreamExecute(vcursor VCursor, bindVars map[string]*
 			if err := cb(&sqltypes.Result{Rows: [][]sqltypes.Value{current}}); err != nil {
 				return err
 			}
-			current, curDistinct = oa.convertRow(row)
+			current, curDistincts = oa.convertRow(row)
 		}
 		return nil
 	})
@@ -322,15 +322,16 @@ func (oa *OrderedAggregate) convertFields(fields []*querypb.Field) []*querypb.Fi
 	return fields
 }
 
-func (oa *OrderedAggregate) convertRow(row []sqltypes.Value) (newRow []sqltypes.Value, curDistinct sqltypes.Value) {
+func (oa *OrderedAggregate) convertRow(row []sqltypes.Value) (newRow []sqltypes.Value, curDistincts []sqltypes.Value) {
 	if !oa.PreProcess {
-		return row, sqltypes.NULL
+		return row, nil
 	}
 	newRow = append(newRow, row...)
-	for _, aggr := range oa.Aggregates {
+	curDistincts = make([]sqltypes.Value, len(oa.Aggregates))
+	for index, aggr := range oa.Aggregates {
 		switch aggr.Opcode {
 		case AggregateCountDistinct:
-			curDistinct = findComparableCurrentDistinct(row, aggr)
+			curDistincts[index] = findComparableCurrentDistinct(row, aggr)
 			// Type is int64. Ok to call MakeTrusted.
 			if row[aggr.KeyCol].IsNull() {
 				newRow[aggr.Col] = countZero
@@ -338,7 +339,7 @@ func (oa *OrderedAggregate) convertRow(row []sqltypes.Value) (newRow []sqltypes.
 				newRow[aggr.Col] = countOne
 			}
 		case AggregateSumDistinct:
-			curDistinct = findComparableCurrentDistinct(row, aggr)
+			curDistincts[index] = findComparableCurrentDistinct(row, aggr)
 			var err error
 			newRow[aggr.Col], err = evalengine.Cast(row[aggr.Col], opcodeType[aggr.Opcode])
 			if err != nil {
@@ -356,7 +357,7 @@ func (oa *OrderedAggregate) convertRow(row []sqltypes.Value) (newRow []sqltypes.
 			newRow[aggr.Col] = val
 		}
 	}
-	return newRow, curDistinct
+	return newRow, curDistincts
 }
 
 func findComparableCurrentDistinct(row []sqltypes.Value, aggr *AggregateParams) sqltypes.Value {
@@ -409,21 +410,21 @@ func (oa *OrderedAggregate) keysEqual(row1, row2 []sqltypes.Value) (bool, error)
 	return true, nil
 }
 
-func (oa *OrderedAggregate) merge(fields []*querypb.Field, row1, row2 []sqltypes.Value, curDistinct sqltypes.Value) ([]sqltypes.Value, sqltypes.Value, error) {
+func (oa *OrderedAggregate) merge(fields []*querypb.Field, row1, row2 []sqltypes.Value, curDistincts []sqltypes.Value) ([]sqltypes.Value, []sqltypes.Value, error) {
 	result := sqltypes.CopyRow(row1)
-	for _, aggr := range oa.Aggregates {
+	for index, aggr := range oa.Aggregates {
 		if aggr.isDistinct() {
 			if row2[aggr.KeyCol].IsNull() {
 				continue
 			}
-			cmp, err := evalengine.NullsafeCompare(curDistinct, row2[aggr.KeyCol])
+			cmp, err := evalengine.NullsafeCompare(curDistincts[index], row2[aggr.KeyCol])
 			if err != nil {
-				return nil, sqltypes.NULL, err
+				return nil, nil, err
 			}
 			if cmp == 0 {
 				continue
 			}
-			curDistinct = findComparableCurrentDistinct(row2, aggr)
+			curDistincts[index] = findComparableCurrentDistinct(row2, aggr)
 		}
 		var err error
 		switch aggr.Opcode {
@@ -443,7 +444,7 @@ func (oa *OrderedAggregate) merge(fields []*querypb.Field, row1, row2 []sqltypes
 			vgtid := &binlogdatapb.VGtid{}
 			err = proto.Unmarshal(row1[aggr.Col].ToBytes(), vgtid)
 			if err != nil {
-				return nil, sqltypes.NULL, err
+				return nil, nil, err
 			}
 			vgtid.ShardGtids = append(vgtid.ShardGtids, &binlogdatapb.ShardGtid{
 				Keyspace: row2[aggr.Col-1].ToString(),
@@ -454,13 +455,13 @@ func (oa *OrderedAggregate) merge(fields []*querypb.Field, row1, row2 []sqltypes
 			val, _ := sqltypes.NewValue(sqltypes.VarBinary, data)
 			result[aggr.Col] = val
 		default:
-			return nil, sqltypes.NULL, fmt.Errorf("BUG: Unexpected opcode: %v", aggr.Opcode)
+			return nil, nil, fmt.Errorf("BUG: Unexpected opcode: %v", aggr.Opcode)
 		}
 		if err != nil {
-			return nil, sqltypes.NULL, err
+			return nil, nil, err
 		}
 	}
-	return result, curDistinct, nil
+	return result, curDistincts, nil
 }
 
 // creates the empty row for the case when we are missing grouping keys and have empty input table
diff --git a/go/vt/vtgate/planbuilder/abstract/join.go b/go/vt/vtgate/planbuilder/abstract/join.go
index dd49dca8ed..056e2281c6 100644
--- a/go/vt/vtgate/planbuilder/abstract/join.go
+++ b/go/vt/vtgate/planbuilder/abstract/join.go
@@ -31,7 +31,7 @@ var _ Operator = (*Join)(nil)
 
 // PushPredicate implements the Operator interface
 func (j *Join) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error {
-	deps := semTable.GetBaseTableDependencies(expr)
+	deps := semTable.BaseTableDependencies(expr)
 	switch {
 	case deps.IsSolvedBy(j.LHS.TableID()):
 		return j.LHS.PushPredicate(expr, semTable)
diff --git a/go/vt/vtgate/planbuilder/abstract/left_join.go b/go/vt/vtgate/planbuilder/abstract/left_join.go
index 886e9691be..70a916a5cc 100644
--- a/go/vt/vtgate/planbuilder/abstract/left_join.go
+++ b/go/vt/vtgate/planbuilder/abstract/left_join.go
@@ -31,7 +31,7 @@ var _ Operator = (*LeftJoin)(nil)
 
 // PushPredicate implements the Operator interface
 func (oj *LeftJoin) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error {
-	deps := semTable.GetBaseTableDependencies(expr)
+	deps := semTable.BaseTableDependencies(expr)
 	if deps.IsSolvedBy(oj.Left.TableID()) {
 		return oj.Left.PushPredicate(expr, semTable)
 	}
diff --git a/go/vt/vtgate/planbuilder/abstract/querygraph.go b/go/vt/vtgate/planbuilder/abstract/querygraph.go
index 42c5f1c959..d1a1fa63df 100644
--- a/go/vt/vtgate/planbuilder/abstract/querygraph.go
+++ b/go/vt/vtgate/planbuilder/abstract/querygraph.go
@@ -106,7 +106,7 @@ func (qg *QueryGraph) collectPredicates(sel *sqlparser.Select, semTable *semanti
 }
 
 func (qg *QueryGraph) collectPredicateTable(t sqlparser.TableExpr, predicate sqlparser.Expr, semTable *semantics.SemTable) error {
-	deps := semTable.GetBaseTableDependencies(predicate)
+	deps := semTable.BaseTableDependencies(predicate)
 	switch deps.NumberOfTables() {
 	case 0:
 		qg.addNoDepsPredicate(predicate)
@@ -137,7 +137,7 @@ func (qg *QueryGraph) collectPredicateTable(t sqlparser.TableExpr, predicate sql
 }
 
 func (qg *QueryGraph) collectPredicate(predicate sqlparser.Expr, semTable *semantics.SemTable) error {
-	deps := semTable.GetBaseTableDependencies(predicate)
+	deps := semTable.BaseTableDependencies(predicate)
 	switch deps.NumberOfTables() {
 	case 0:
 		qg.addNoDepsPredicate(predicate)
diff --git a/go/vt/vtgate/planbuilder/abstract/queryprojection.go b/go/vt/vtgate/planbuilder/abstract/queryprojection.go
index 9311645a12..0bbc37b4c9 100644
--- a/go/vt/vtgate/planbuilder/abstract/queryprojection.go
+++ b/go/vt/vtgate/planbuilder/abstract/queryprojection.go
@@ -164,7 +164,8 @@ func (qp *QueryProjection) getNonAggrExprNotMatchingGroupByExprs() sqlparser.Exp
 		}
 	}
 	for _, order := range qp.OrderExprs {
-		if sqlparser.IsAggregation(order.WeightStrExpr) {
+		// ORDER BY NULL or Aggregation functions need not be present in group by
+		if sqlparser.IsNull(order.Inner.Expr) || sqlparser.IsAggregation(order.WeightStrExpr) {
 			continue
 		}
 		isGroupByOk := false
@@ -190,7 +191,7 @@ func (qp *QueryProjection) getSimplifiedExpr(e sqlparser.Expr, caller string) (e
 	if isLiteral && literalExpr.Type == sqlparser.IntVal {
 		num, _ := strconv.Atoi(literalExpr.Val)
 		if num > len(qp.SelectExprs) {
-			return nil, nil, vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.BadFieldError, "Unknown column '%d' in '%s'", num, caller)
+			return nil, nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "column offset does not exist")
 		}
 		aliasedExpr := qp.SelectExprs[num-1].Col
 		expr = aliasedExpr.Expr
@@ -217,6 +218,10 @@ func (qp *QueryProjection) getSimplifiedExpr(e sqlparser.Expr, caller string) (e
 		}
 	}
 
+	if sqlparser.IsNull(e) {
+		return e, nil, nil
+	}
+
 	return e, e, nil
 }
 
diff --git a/go/vt/vtgate/planbuilder/horizon_planning.go b/go/vt/vtgate/planbuilder/horizon_planning.go
index 3aa99fc2f6..a9df10b6cd 100644
--- a/go/vt/vtgate/planbuilder/horizon_planning.go
+++ b/go/vt/vtgate/planbuilder/horizon_planning.go
@@ -55,7 +55,7 @@ func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *sem
 	case *joinGen4:
 		lhsSolves := node.Left.ContainsTables()
 		rhsSolves := node.Right.ContainsTables()
-		deps := semTable.GetBaseTableDependencies(expr.Expr)
+		deps := semTable.BaseTableDependencies(expr.Expr)
 		var column int
 		var appended bool
 		switch {
@@ -172,14 +172,8 @@ func (hp *horizonPlanning) planAggregations() error {
 			return err
 		}
 
-		// Currently the OA engine primitive is able to handle only one distinct aggregation function.
-		// PreProcess being true tells that it is already handling it.
-		if oa.eaggr.PreProcess && handleDistinct {
-			return vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "multiple distinct aggregation function")
-		}
-
 		pushExpr, alias, opcode := hp.createPushExprAndAlias(e, handleDistinct, innerAliased, opcode, oa)
-		offset, _, err := pushProjection(pushExpr, oa.input, hp.semTable, true, true)
+		offset, _, err := pushProjection(pushExpr, oa.input, hp.semTable, true, false)
 		if err != nil {
 			return err
 		}
@@ -353,6 +347,16 @@ func (hp *horizonPlanning) planOrderBy(orderExprs []abstract.OrderBy, plan logic
 
 		return newPlan, nil
 	case *orderedAggregate:
+		// remove ORDER BY NULL from the list of order by expressions since we will be doing the ordering on vtgate level so NULL is not useful
+		var orderExprsWithoutNils []abstract.OrderBy
+		for _, expr := range orderExprs {
+			if sqlparser.IsNull(expr.Inner.Expr) {
+				continue
+			}
+			orderExprsWithoutNils = append(orderExprsWithoutNils, expr)
+		}
+		orderExprs = orderExprsWithoutNils
+
 		for _, order := range orderExprs {
 			if sqlparser.ContainsAggregation(order.WeightStrExpr) {
 				ms, err := createMemorySortPlanOnAggregation(plan, orderExprs)
@@ -378,6 +382,10 @@ func (hp *horizonPlanning) planOrderBy(orderExprs []abstract.OrderBy, plan logic
 func planOrderByForRoute(orderExprs []abstract.OrderBy, plan *route, semTable *semantics.SemTable) (logicalPlan, bool, error) {
 	origColCount := plan.Select.GetColumnCount()
 	for _, order := range orderExprs {
+		plan.Select.AddOrder(order.Inner)
+		if sqlparser.IsNull(order.Inner.Expr) {
+			continue
+		}
 		offset, weightStringOffset, _, err := wrapAndPushExpr(order.Inner.Expr, order.WeightStrExpr, plan, semTable)
 		if err != nil {
 			return nil, false, err
@@ -388,7 +396,6 @@ func planOrderByForRoute(orderExprs []abstract.OrderBy, plan *route, semTable *s
 			WeightStringCol: weightStringOffset,
 			Desc:            order.Inner.Direction == sqlparser.DescOrder,
 		})
-		plan.Select.AddOrder(order.Inner)
 	}
 	return plan, origColCount != plan.Select.GetColumnCount(), nil
 }
@@ -521,7 +528,7 @@ func (hp *horizonPlanning) createMemorySortPlan(plan logicalPlan, orderExprs []a
 
 func allLeft(orderExprs []abstract.OrderBy, semTable *semantics.SemTable, lhsTables semantics.TableSet) bool {
 	for _, expr := range orderExprs {
-		exprDependencies := semTable.GetBaseTableDependencies(expr.Inner.Expr)
+		exprDependencies := semTable.BaseTableDependencies(expr.Inner.Expr)
 		if !exprDependencies.IsSolvedBy(lhsTables) {
 			return false
 		}
diff --git a/go/vt/vtgate/planbuilder/jointree.go b/go/vt/vtgate/planbuilder/jointree.go
index ca58d507ec..585d43da72 100644
--- a/go/vt/vtgate/planbuilder/jointree.go
+++ b/go/vt/vtgate/planbuilder/jointree.go
@@ -726,21 +726,17 @@ func (jp *joinTree) clone() queryTree {
 		lhs:   jp.lhs.clone(),
 		rhs:   jp.rhs.clone(),
 		outer: jp.outer,
+		vars:  jp.vars,
 	}
 	return result
 }
 
-/*
-
-select id, t2.b from t1 , (select b from t2) t2 where t.id = 1
-*/
-
 func (jp *joinTree) pushOutputColumns(columns []*sqlparser.ColName, semTable *semantics.SemTable) ([]int, error) {
 	var toTheLeft []bool
 	var lhs, rhs []*sqlparser.ColName
 	for _, col := range columns {
 		col.Qualifier.Qualifier = sqlparser.NewTableIdent("")
-		if semTable.GetBaseTableDependencies(col).IsSolvedBy(jp.lhs.tableID()) {
+		if semTable.BaseTableDependencies(col).IsSolvedBy(jp.lhs.tableID()) {
 			lhs = append(lhs, col)
 			toTheLeft = append(toTheLeft, true)
 		} else {
diff --git a/go/vt/vtgate/planbuilder/route_planning.go b/go/vt/vtgate/planbuilder/route_planning.go
index fb40d1064c..682c610725 100644
--- a/go/vt/vtgate/planbuilder/route_planning.go
+++ b/go/vt/vtgate/planbuilder/route_planning.go
@@ -321,7 +321,7 @@ func exprHasUniqueVindex(vschema ContextVSchema, semTable *semantics.SemTable, e
 	if !isCol {
 		return false
 	}
-	ts := semTable.GetBaseTableDependencies(expr)
+	ts := semTable.BaseTableDependencies(expr)
 	tableInfo, err := semTable.TableInfoFor(ts)
 	if err != nil {
 		return false
@@ -382,16 +382,27 @@ func pushJoinPredicate(exprs []sqlparser.Expr, tree queryTree, semTable *semanti
 		// we break up the predicates so that colnames from the LHS are replaced by arguments
 		var rhsPreds []sqlparser.Expr
 		var lhsColumns []*sqlparser.ColName
+		var lhsVarsName []string
 		lhsSolves := node.lhs.tableID()
 		for _, expr := range exprs {
-			cols, predicate, err := breakPredicateInLHSandRHS(expr, semTable, lhsSolves)
+			bvName, cols, predicate, err := breakPredicateInLHSandRHS(expr, semTable, lhsSolves)
 			if err != nil {
 				return nil, err
 			}
 			lhsColumns = append(lhsColumns, cols...)
+			lhsVarsName = append(lhsVarsName, bvName...)
 			rhsPreds = append(rhsPreds, predicate)
 		}
-		node.pushOutputColumns(lhsColumns, semTable)
+		if lhsColumns != nil && lhsVarsName != nil {
+			idxs, err := node.pushOutputColumns(lhsColumns, semTable)
+			if err != nil {
+				return nil, err
+			}
+			for i, idx := range idxs {
+				node.vars[lhsVarsName[i]] = idx
+			}
+		}
+
 		rhsPlan, err := pushJoinPredicate(rhsPreds, node.rhs, semTable)
 		if err != nil {
 			return nil, err
@@ -401,6 +412,7 @@ func pushJoinPredicate(exprs []sqlparser.Expr, tree queryTree, semTable *semanti
 			lhs:   node.lhs,
 			rhs:   rhsPlan,
 			outer: node.outer,
+			vars:  node.vars,
 		}, nil
 	case *derivedTree:
 		plan := node.clone().(*derivedTree)
@@ -430,12 +442,12 @@ func pushJoinPredicate(exprs []sqlparser.Expr, tree queryTree, semTable *semanti
 	}
 }
 
-func breakPredicateInLHSandRHS(expr sqlparser.Expr, semTable *semantics.SemTable, lhs semantics.TableSet) (columns []*sqlparser.ColName, predicate sqlparser.Expr, err error) {
+func breakPredicateInLHSandRHS(expr sqlparser.Expr, semTable *semantics.SemTable, lhs semantics.TableSet) (bvNames []string, columns []*sqlparser.ColName, predicate sqlparser.Expr, err error) {
 	predicate = sqlparser.CloneExpr(expr)
 	_ = sqlparser.Rewrite(predicate, nil, func(cursor *sqlparser.Cursor) bool {
 		switch node := cursor.Node().(type) {
 		case *sqlparser.ColName:
-			deps := semTable.GetBaseTableDependencies(node)
+			deps := semTable.BaseTableDependencies(node)
 			if deps == 0 {
 				err = vterrors.Errorf(vtrpcpb.Code_INTERNAL, "unknown column. has the AST been copied?")
 				return false
@@ -443,14 +455,16 @@ func breakPredicateInLHSandRHS(expr sqlparser.Expr, semTable *semantics.SemTable
 			if deps.IsSolvedBy(lhs) {
 				node.Qualifier.Qualifier = sqlparser.NewTableIdent("")
 				columns = append(columns, node)
-				arg := sqlparser.NewArgument(node.CompliantName())
+				bvName := node.CompliantName()
+				bvNames = append(bvNames, bvName)
+				arg := sqlparser.NewArgument(bvName)
 				cursor.Replace(arg)
 			}
 		}
 		return true
 	})
 	if err != nil {
-		return nil, nil, err
+		return nil, nil, nil, err
 	}
 	return
 }
@@ -465,7 +479,7 @@ func mergeOrJoin(lhs, rhs queryTree, joinPredicates []sqlparser.Expr, semTable *
 		return newPlan, nil
 	}
 
-	tree := &joinTree{lhs: lhs.clone(), rhs: rhs.clone(), outer: !inner}
+	tree := &joinTree{lhs: lhs.clone(), rhs: rhs.clone(), outer: !inner, vars: map[string]int{}}
 	return pushJoinPredicate(joinPredicates, tree, semTable)
 }
 
@@ -707,7 +721,7 @@ func findColumnVindex(a *routeTree, exp sqlparser.Expr, sem *semantics.SemTable)
 	if !isCol {
 		return nil
 	}
-	leftDep := sem.GetBaseTableDependencies(left)
+	leftDep := sem.BaseTableDependencies(left)
 
 	var singCol vindexes.SingleColumn
 
@@ -915,7 +929,7 @@ func createRoutePlanForOuter(aRoute, bRoute *routeTree, semTable *semantics.SemT
 	tables := bRoute.tables
 	// we are doing an outer join where the outer part contains multiple tables - we have to turn the outer part into a join or two
 	for _, predicate := range bRoute.predicates {
-		deps := semTable.GetBaseTableDependencies(predicate)
+		deps := semTable.BaseTableDependencies(predicate)
 		aTbl, bTbl, newTables := findTables(deps, tables)
 		tables = newTables
 		if aTbl != nil && bTbl != nil {
diff --git a/go/vt/vtgate/semantics/analyzer.go b/go/vt/vtgate/semantics/analyzer.go
index 9664c6f530..605484ae98 100644
--- a/go/vt/vtgate/semantics/analyzer.go
+++ b/go/vt/vtgate/semantics/analyzer.go
@@ -200,16 +200,7 @@ func (a *analyzer) analyzeOrderByGroupByExprForLiteral(input sqlparser.Expr, cal
 		return
 	}
 
-	var deps TableSet
-	_ = sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {
-		expr, ok := node.(sqlparser.Expr)
-		if ok {
-			deps = deps.Merge(a.exprRecursiveDeps[expr])
-		}
-		return true, nil
-	}, expr.Expr)
-
-	a.exprRecursiveDeps[input] = deps
+	a.exprRecursiveDeps[input] = a.exprDeps.Dependencies(expr.Expr)
 }
 
 func (a *analyzer) changeScopeForOrderBy(cursor *sqlparser.Cursor) {
@@ -334,6 +325,7 @@ func (a *analyzer) resolveUnQualifiedColumn(current *scope, expr *sqlparser.ColN
 				typp = typ
 			}
 			if tbl.IsActualTable() {
+				tsp = tspRecursive
 				continue
 			}
 			ts, err := tbl.DepsFor(expr, a, len(current.tables) == 1)
@@ -349,6 +341,7 @@ func (a *analyzer) resolveUnQualifiedColumn(current *scope, expr *sqlparser.ColN
 	}
 
 	if tspRecursive == nil {
+		a.projErr = vterrors.NewErrorf(vtrpcpb.Code_INVALID_ARGUMENT, vterrors.NonUniqError, fmt.Sprintf("Column '%s' in field list is ambiguous", sqlparser.String(expr)))
 		return 0, 0, nil, nil
 	}
 	if tsp == nil {
diff --git a/go/vt/vtgate/semantics/semantic_state.go b/go/vt/vtgate/semantics/semantic_state.go
index d93726efff..15b4b7382e 100644
--- a/go/vt/vtgate/semantics/semantic_state.go
+++ b/go/vt/vtgate/semantics/semantic_state.go
@@ -17,6 +17,8 @@ limitations under the License.
 package semantics
 
 import (
+	"reflect"
+
 	"vitess.io/vitess/go/vt/key"
 	querypb "vitess.io/vitess/go/vt/proto/query"
 	topodatapb "vitess.io/vitess/go/vt/proto/topodata"
@@ -156,7 +158,7 @@ func (v *vTableInfo) RecursiveDepsFor(col *sqlparser.ColName, org originable, si
 }
 
 // DepsFor implements the TableInfo interface
-func (v *vTableInfo) DepsFor(col *sqlparser.ColName, org originable, single bool) (*TableSet, error) {
+func (v *vTableInfo) DepsFor(col *sqlparser.ColName, org originable, _ bool) (*TableSet, error) {
 	if v.ASTNode == nil {
 		return nil, nil
 	}
@@ -209,7 +211,7 @@ func depsFor(
 		ts := org.tableSetFor(astNode)
 
 		for _, info := range cols {
-			if col.Name.String() == info.Name {
+			if col.Name.EqualString(info.Name) {
 				return &ts, &info.Type, nil
 			}
 		}
@@ -224,7 +226,7 @@ func depsFor(
 	}
 
 	for _, info := range cols {
-		if col.Name.String() == info.Name {
+		if col.Name.EqualString(info.Name) {
 			ts := org.tableSetFor(astNode)
 			return &ts, &info.Type, nil
 		}
@@ -296,12 +298,30 @@ func vindexTableToColumnInfo(tbl *vindexes.Table) []ColumnInfo {
 	if tbl == nil {
 		return nil
 	}
+	nameMap := map[string]interface{}{}
 	cols := make([]ColumnInfo, 0, len(tbl.Columns))
 	for _, col := range tbl.Columns {
 		cols = append(cols, ColumnInfo{
 			Name: col.Name.String(),
 			Type: col.Type,
 		})
+		nameMap[col.Name.String()] = nil
+	}
+	// If table is authoritative, we do not need ColumnVindexes to help in resolving the unqualified columns.
+	if tbl.ColumnListAuthoritative {
+		return cols
+	}
+	for _, vindex := range tbl.ColumnVindexes {
+		for _, column := range vindex.Columns {
+			name := column.String()
+			if _, exists := nameMap[name]; exists {
+				continue
+			}
+			cols = append(cols, ColumnInfo{
+				Name: name,
+			})
+			nameMap[name] = nil
+		}
 	}
 	return cols
 }
@@ -384,8 +404,8 @@ func (st *SemTable) TableInfoFor(id TableSet) (TableInfo, error) {
 	return st.Tables[id.TableOffset()], nil
 }
 
-// GetBaseTableDependencies return the table dependencies of the expression.
-func (st *SemTable) GetBaseTableDependencies(expr sqlparser.Expr) TableSet {
+// BaseTableDependencies return the table dependencies of the expression.
+func (st *SemTable) BaseTableDependencies(expr sqlparser.Expr) TableSet {
 	return st.ExprBaseTableDeps.Dependencies(expr)
 }
 
@@ -430,14 +450,26 @@ func (d ExprDependencies) Dependencies(expr sqlparser.Expr) TableSet {
 		return deps
 	}
 
+	// During the original semantic analysis, all ColName:s were found and bound the the corresponding tables
+	// Here, we'll walk the expression tree and look to see if we can found any sub-expressions
+	// that have already set dependencies.
 	_ = sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {
-		colName, ok := node.(*sqlparser.ColName)
-		if ok {
-			set := d[colName]
+		expr, ok := node.(sqlparser.Expr)
+		if !ok || !reflect.TypeOf(expr).Comparable() {
+			// if this is not an expression, or it is an expression we can't use as a map-key,
+			// just carry on down the tree
+			return true, nil
+		}
+
+		set, found := d[expr]
+		if found {
 			deps |= set
 		}
-		return true, nil
+
+		// if we found a cached value, there is no need to continue down to visit children
+		return !found, nil
 	}, expr)
+
 	d[expr] = deps
 	return deps
 }


Test patch:
diff --git a/go/test/endtoend/vtgate/gen4/gen4_test.go b/go/test/endtoend/vtgate/gen4/gen4_test.go
index 8359044210..3a7cbc20c6 100644
--- a/go/test/endtoend/vtgate/gen4/gen4_test.go
+++ b/go/test/endtoend/vtgate/gen4/gen4_test.go
@@ -86,6 +86,23 @@ func TestGroupBy(t *testing.T) {
 			`[INT64(2) VARCHAR("B") VARCHAR("C") VARCHAR("abc")]]`)
 }
 
+func TestJoinBindVars(t *testing.T) {
+	ctx := context.Background()
+	conn, err := mysql.Connect(ctx, &vtParams)
+	require.NoError(t, err)
+	defer conn.Close()
+
+	defer func() {
+		_, _ = exec(t, conn, `delete from t2`)
+		_, _ = exec(t, conn, `delete from t3`)
+	}()
+
+	checkedExec(t, conn, `insert into t2(id, tcol1, tcol2) values (1, 'A', 'A'),(2, 'B', 'C'),(3, 'A', 'C'),(4, 'C', 'A'),(5, 'A', 'A'),(6, 'B', 'C'),(7, 'B', 'A'),(8, 'C', 'B')`)
+	checkedExec(t, conn, `insert into t3(id, tcol1, tcol2) values (1, 'A', 'A'),(2, 'B', 'C'),(3, 'A', 'C'),(4, 'C', 'A'),(5, 'A', 'A'),(6, 'B', 'C'),(7, 'B', 'A'),(8, 'C', 'B')`)
+
+	assertMatches(t, conn, `select t2.tcol1 from t2 join t3 on t2.tcol2 = t3.tcol2 where t2.tcol1 = 'A'`, `[[VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")]]`)
+}
+
 func TestDistinctAggregationFunc(t *testing.T) {
 	ctx := context.Background()
 	conn, err := mysql.Connect(ctx, &vtParams)
@@ -116,6 +133,13 @@ func TestDistinctAggregationFunc(t *testing.T) {
 	// sum on any column
 	assertMatches(t, conn, `select tcol1, sum(distinct tcol2) from t2 group by tcol1`,
 		`[[VARCHAR("A") DECIMAL(0)] [VARCHAR("B") DECIMAL(0)] [VARCHAR("C") DECIMAL(0)]]`)
+
+	// insert more data to get values on sum
+	checkedExec(t, conn, `insert into t2(id, tcol1, tcol2) values (9, 'AA', null),(10, 'AA', '4'),(11, 'AA', '4'),(12, null, '5'),(13, null, '6'),(14, 'BB', '10'),(15, 'BB', '20'),(16, 'BB', 'X')`)
+
+	// multi distinct
+	assertMatches(t, conn, `select tcol1, count(distinct tcol2), sum(distinct tcol2) from t2 group by tcol1`,
+		`[[NULL INT64(2) DECIMAL(11)] [VARCHAR("A") INT64(2) DECIMAL(0)] [VARCHAR("AA") INT64(1) DECIMAL(4)] [VARCHAR("B") INT64(2) DECIMAL(0)] [VARCHAR("BB") INT64(3) DECIMAL(30)] [VARCHAR("C") INT64(1) DECIMAL(0)]]`)
 }
 
 func assertMatches(t *testing.T, conn *mysql.Conn, query, expected string) {
diff --git a/go/test/endtoend/vtgate/gen4/main_test.go b/go/test/endtoend/vtgate/gen4/main_test.go
index 4969d5d8e5..01b46f8af4 100644
--- a/go/test/endtoend/vtgate/gen4/main_test.go
+++ b/go/test/endtoend/vtgate/gen4/main_test.go
@@ -42,6 +42,13 @@ create table t2(
 	tcol2 varchar(50),
 	primary key(id)
 ) Engine=InnoDB;
+
+create table t3(
+	id bigint,
+	tcol1 varchar(50),
+	tcol2 varchar(50),
+	primary key(id)
+) Engine=InnoDB;
 `
 
 	VSchema = `
@@ -74,6 +81,20 @@ create table t2(
           "type": "VARCHAR"
         }
       ]
+    },
+    "t3": {
+      "column_vindexes": [
+        {
+          "column": "id",
+          "name": "xxhash"
+        }
+      ],
+      "columns": [
+        {
+          "name": "tcol1",
+          "type": "VARCHAR"
+        }
+      ]
     }
   }
 }`
diff --git a/go/vt/vtgate/engine/ordered_aggregate_test.go b/go/vt/vtgate/engine/ordered_aggregate_test.go
index a32f50b5e2..744006f14e 100644
--- a/go/vt/vtgate/engine/ordered_aggregate_test.go
+++ b/go/vt/vtgate/engine/ordered_aggregate_test.go
@@ -652,13 +652,13 @@ func TestMerge(t *testing.T) {
 		"1|3|2.8|2|bc",
 	)
 
-	merged, _, err := oa.merge(fields, r.Rows[0], r.Rows[1], sqltypes.NULL)
+	merged, _, err := oa.merge(fields, r.Rows[0], r.Rows[1], nil)
 	assert.NoError(err)
 	want := sqltypes.MakeTestResult(fields, "1|5|6|2|bc").Rows[0]
 	assert.Equal(want, merged)
 
 	// swap and retry
-	merged, _, err = oa.merge(fields, r.Rows[1], r.Rows[0], sqltypes.NULL)
+	merged, _, err = oa.merge(fields, r.Rows[1], r.Rows[0], nil)
 	assert.NoError(err)
 	assert.Equal(want, merged)
 }
@@ -979,3 +979,74 @@ func TestSumDistinctOnVarcharWithNulls(t *testing.T) {
 	require.NoError(t, err)
 	assert.Equal(t, want, results)
 }
+
+func TestMultiDistinct(t *testing.T) {
+	fields := sqltypes.MakeTestFields(
+		"c1|c2|c3",
+		"int64|int64|int64",
+	)
+	fp := &fakePrimitive{
+		results: []*sqltypes.Result{sqltypes.MakeTestResult(
+			fields,
+			"null|null|null",
+			"null|1|2",
+			"null|2|2",
+			"10|null|null",
+			"10|2|null",
+			"10|2|1",
+			"10|2|3",
+			"10|3|3",
+			"20|null|null",
+			"20|null|null",
+			"30|1|1",
+			"30|1|2",
+			"30|1|3",
+			"40|1|1",
+			"40|2|1",
+			"40|3|1",
+		)},
+	}
+
+	oa := &OrderedAggregate{
+		PreProcess: true,
+		Aggregates: []*AggregateParams{{
+			Opcode: AggregateCountDistinct,
+			Col:    1,
+			Alias:  "count(distinct c2)",
+		}, {
+			Opcode: AggregateSumDistinct,
+			Col:    2,
+			Alias:  "sum(distinct c3)",
+		}},
+		GroupByKeys: []*GroupByParams{{KeyCol: 0}},
+		Input:       fp,
+	}
+
+	want := sqltypes.MakeTestResult(
+		sqltypes.MakeTestFields(
+			"c1|count(distinct c2)|sum(distinct c3)",
+			"int64|int64|decimal",
+		),
+		`null|2|2`,
+		`10|2|4`,
+		`20|0|null`,
+		`30|1|6`,
+		`40|3|1`,
+	)
+
+	qr, err := oa.Execute(nil, nil, false)
+	require.NoError(t, err)
+	assert.Equal(t, want, qr)
+
+	fp.rewind()
+	results := &sqltypes.Result{}
+	err = oa.StreamExecute(nil, nil, false, func(qr *sqltypes.Result) error {
+		if qr.Fields != nil {
+			results.Fields = qr.Fields
+		}
+		results.Rows = append(results.Rows, qr.Rows...)
+		return nil
+	})
+	require.NoError(t, err)
+	assert.Equal(t, want, results)
+}
diff --git a/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go b/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
index 0d433586f3..04f5ce5c57 100644
--- a/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
+++ b/go/vt/vtgate/planbuilder/abstract/queryprojection_test.go
@@ -19,6 +19,8 @@ package abstract
 import (
 	"testing"
 
+	"vitess.io/vitess/go/vt/vtgate/semantics"
+
 	"github.com/stretchr/testify/assert"
 
 	"github.com/stretchr/testify/require"
@@ -41,9 +43,6 @@ func TestQP(t *testing.T) {
 			sql:    "select next value from user_seq",
 			expErr: "gen4 does not yet support: *sqlparser.Nextval in select list",
 		},
-		{
-			sql: "select (select 1) from user",
-		},
 		{
 			sql:    "select 1, count(1) from user",
 			expErr: "In aggregated query without GROUP BY, expression of SELECT list contains nonaggregated column '1'; this is incompatible with sql_mode=only_full_group_by",
@@ -68,11 +67,7 @@ func TestQP(t *testing.T) {
 			},
 		},
 		{
-			sql:    "select id from user order by 2", // positional order not supported
-			expErr: "Unknown column '2' in 'order clause'",
-		},
-		{
-			sql: "SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable, tbl2 ORDER BY full_name", // alias in order not supported
+			sql: "SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name", // alias in order not supported
 			expOrder: []OrderBy{
 				{
 					Inner: &sqlparser.Order{Expr: sqlparser.NewColName("full_name")},
@@ -98,6 +93,9 @@ func TestQP(t *testing.T) {
 			require.NoError(t, err)
 
 			sel := stmt.(*sqlparser.Select)
+			_, err = semantics.Analyze(sel, "", &semantics.FakeSI{})
+			require.NoError(t, err)
+
 			qp, err := CreateQPFromSelect(sel)
 			if tcase.expErr != "" {
 				require.Error(t, err)
@@ -105,6 +103,7 @@ func TestQP(t *testing.T) {
 			} else {
 				require.NoError(t, err)
 				assert.Equal(t, len(sel.SelectExprs), len(qp.SelectExprs))
+				require.Equal(t, len(tcase.expOrder), len(qp.OrderExprs), "not enough order expressions in QP")
 				for index, expOrder := range tcase.expOrder {
 					assert.True(t, sqlparser.EqualsSQLNode(expOrder.Inner, qp.OrderExprs[index].Inner), "want: %+v, got %+v", sqlparser.String(expOrder.Inner), sqlparser.String(qp.OrderExprs[index].Inner))
 					assert.True(t, sqlparser.EqualsSQLNode(expOrder.WeightStrExpr, qp.OrderExprs[index].WeightStrExpr), "want: %v, got %v", sqlparser.String(expOrder.WeightStrExpr), sqlparser.String(qp.OrderExprs[index].WeightStrExpr))
@@ -201,6 +200,9 @@ func TestQPSimplifiedExpr(t *testing.T) {
 			ast, err := sqlparser.Parse(tc.query)
 			require.NoError(t, err)
 			sel := ast.(*sqlparser.Select)
+			_, err = semantics.Analyze(sel, "", &semantics.FakeSI{})
+			require.NoError(t, err)
+
 			qp, err := CreateQPFromSelect(sel)
 			require.NoError(t, err)
 			require.Equal(t, tc.expected[1:], qp.toString())
diff --git a/go/vt/vtgate/planbuilder/expand_star_test.go b/go/vt/vtgate/planbuilder/expand_star_test.go
index 01b5b331b4..c29fe7e0a7 100644
--- a/go/vt/vtgate/planbuilder/expand_star_test.go
+++ b/go/vt/vtgate/planbuilder/expand_star_test.go
@@ -169,14 +169,14 @@ func TestSemTableDependenciesAfterExpandStar(t *testing.T) {
 			assert.Equal(t, tcase.expSQL, sqlparser.String(expandedSelect))
 			if tcase.otherTbl != -1 {
 				assert.NotEqual(t,
-					semTable.GetBaseTableDependencies(expandedSelect.SelectExprs[tcase.otherTbl].(*sqlparser.AliasedExpr).Expr),
-					semTable.GetBaseTableDependencies(expandedSelect.SelectExprs[tcase.expandedCol].(*sqlparser.AliasedExpr).Expr),
+					semTable.BaseTableDependencies(expandedSelect.SelectExprs[tcase.otherTbl].(*sqlparser.AliasedExpr).Expr),
+					semTable.BaseTableDependencies(expandedSelect.SelectExprs[tcase.expandedCol].(*sqlparser.AliasedExpr).Expr),
 				)
 			}
 			if tcase.sameTbl != -1 {
 				assert.Equal(t,
-					semTable.GetBaseTableDependencies(expandedSelect.SelectExprs[tcase.sameTbl].(*sqlparser.AliasedExpr).Expr),
-					semTable.GetBaseTableDependencies(expandedSelect.SelectExprs[tcase.expandedCol].(*sqlparser.AliasedExpr).Expr),
+					semTable.BaseTableDependencies(expandedSelect.SelectExprs[tcase.sameTbl].(*sqlparser.AliasedExpr).Expr),
+					semTable.BaseTableDependencies(expandedSelect.SelectExprs[tcase.expandedCol].(*sqlparser.AliasedExpr).Expr),
 				)
 			}
 		})
diff --git a/go/vt/vtgate/planbuilder/plan_test.go b/go/vt/vtgate/planbuilder/plan_test.go
index e02c8870b5..77b7825217 100644
--- a/go/vt/vtgate/planbuilder/plan_test.go
+++ b/go/vt/vtgate/planbuilder/plan_test.go
@@ -534,7 +534,12 @@ func testFile(t *testing.T, filename, tempDir string, vschema *vschemaWrapper, c
 					if outFirstPlanner == out {
 						expected.WriteString(samePlanMarker)
 					} else {
-						expected.WriteString(fmt.Sprintf("%s\n", out))
+						if err != nil {
+							out = out[1 : len(out)-1] // remove the double quotes
+							expected.WriteString(fmt.Sprintf("Gen4 error: %s\n", out))
+						} else {
+							expected.WriteString(fmt.Sprintf("%s\n", out))
+						}
 					}
 				})
 			} else {
diff --git a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
index e226ded615..34de59996c 100644
--- a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
@@ -1495,12 +1495,12 @@ Gen4 plan same as above
     ]
   }
 }
+Gen4 plan same as above
 
 # scatter aggregate with complex select list (can't build order by)
 "select distinct a+1 from user"
 "generating order by clause: cannot reference a complex expression"
 
-
 # scatter aggregate with numbered order by columns
 "select a, b, c, d, count(*) from user group by 1, 2, 3 order by 1, 2, 3"
 {
@@ -2252,3 +2252,150 @@ Gen4 plan same as above
     ]
   }
 }
+
+# Cannot have more than one aggr(distinct...
+"select count(distinct a), count(distinct b) from user"
+"unsupported: only one distinct aggregation allowed in a select: count(distinct b)"
+{
+  "QueryType": "SELECT",
+  "Original": "select count(distinct a), count(distinct b) from user",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count_distinct(0|2) AS count(distinct a), count_distinct(1|3) AS count(distinct b)",
+    "ResultColumns": 2,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select a, b, weight_string(a), weight_string(b) from `user` where 1 != 1 group by a, b",
+        "OrderBy": "(0|2) ASC, (1|3) ASC",
+        "Query": "select a, b, weight_string(a), weight_string(b) from `user` group by a, b order by a asc, b asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
+
+# multiple distinct functions with grouping.
+"select col1, count(distinct col2), sum(distinct col2) from user group by col1"
+"unsupported: only one distinct aggregation allowed in a select: sum(distinct col2)"
+{
+  "QueryType": "SELECT",
+  "Original": "select col1, count(distinct col2), sum(distinct col2) from user group by col1",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count_distinct(1|4) AS count(distinct col2), sum_distinct(2|4) AS sum(distinct col2)",
+    "GroupBy": "(0|3)",
+    "ResultColumns": 3,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select col1, col2, col2, weight_string(col1), weight_string(col2) from `user` where 1 != 1 group by col1, col2, col2",
+        "OrderBy": "(0|3) ASC, (1|4) ASC, (1|4) ASC",
+        "Query": "select col1, col2, col2, weight_string(col1), weight_string(col2) from `user` group by col1, col2, col2 order by col1 asc, col2 asc, col2 asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
+
+# aggregate query with order by aggregate column along with NULL
+"select col, count(*) k from user group by col order by null, k"
+"unsupported: in scatter query: complex order by expression: null"
+{
+  "QueryType": "SELECT",
+  "Original": "select col, count(*) k from user group by col order by null, k",
+  "Instructions": {
+    "OperatorType": "Sort",
+    "Variant": "Memory",
+    "OrderBy": "1 ASC",
+    "ResultColumns": 2,
+    "Inputs": [
+      {
+        "OperatorType": "Aggregate",
+        "Variant": "Ordered",
+        "Aggregates": "count(1)",
+        "GroupBy": "(0|2)",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select col, count(*) as k, weight_string(col) from `user` where 1 != 1 group by col",
+            "OrderBy": "(0|2) ASC",
+            "Query": "select col, count(*) as k, weight_string(col) from `user` group by col order by col asc",
+            "Table": "`user`"
+          }
+        ]
+      }
+    ]
+  }
+}
+
+# aggregate query with order by NULL
+"select col, count(*) k from user group by col order by null"
+{
+  "QueryType": "SELECT",
+  "Original": "select col, count(*) k from user group by col order by null",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(1)",
+    "GroupBy": "0",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select col, count(*) as k, weight_string(col) from `user` where 1 != 1 group by col",
+        "OrderBy": "(0|2) ASC",
+        "Query": "select col, count(*) as k, weight_string(col) from `user` group by col order by col asc",
+        "ResultColumns": 2,
+        "Table": "`user`"
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select col, count(*) k from user group by col order by null",
+  "Instructions": {
+    "OperatorType": "Aggregate",
+    "Variant": "Ordered",
+    "Aggregates": "count(1)",
+    "GroupBy": "(0|2)",
+    "ResultColumns": 2,
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select col, count(*) as k, weight_string(col) from `user` where 1 != 1 group by col",
+        "OrderBy": "(0|2) ASC",
+        "Query": "select col, count(*) as k, weight_string(col) from `user` group by col order by col asc",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
+
diff --git a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
index 7ad39a0391..da63bd2338 100644
--- a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
@@ -679,6 +679,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_col": 0
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -721,6 +724,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_col": 0
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -763,6 +769,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_col": 0
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -808,6 +817,50 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_col": 0
+    },
+    "TableName": "`user`_user_extra",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `user`.col from `user` where 1 != 1",
+        "Query": "select `user`.col from `user`",
+        "Table": "`user`"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectEqualUnique",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select user_extra.id from user_extra where 1 != 1",
+        "Query": "select user_extra.id from user_extra where user_extra.col = :user_col and user_extra.user_id = :user_col",
+        "Table": "user_extra",
+        "Values": [
+          ":user_col"
+        ],
+        "Vindex": "user_index"
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select user_extra.id from user join user_extra on user.col = user_extra.col where user_extra.user_id = user.col",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_col": 1
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -839,7 +892,6 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
 
 # Multi-route with non-route constraint, should use first route.
 "select user_extra.id from user join user_extra on user.col = user_extra.col where 1 = 1"
@@ -850,6 +902,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_col": 0
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -884,6 +939,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_col": 0
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -1083,6 +1141,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_id": 0
+    },
     "TableName": "`user`_unsharded",
     "Inputs": [
       {
@@ -1117,6 +1178,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "unsharded_id": 0
+    },
     "TableName": "unsharded_`user`",
     "Inputs": [
       {
@@ -1181,6 +1245,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_extra_col": 0
+    },
     "TableName": "user_extra_`user`",
     "Inputs": [
       {
@@ -1266,6 +1333,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_extra_col": 0
+    },
     "TableName": "user_extra_`user`",
     "Inputs": [
       {
diff --git a/go/vt/vtgate/planbuilder/testdata/from_cases.txt b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
index 21ea09210f..1953d14ffe 100644
--- a/go/vt/vtgate/planbuilder/testdata/from_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
@@ -349,6 +349,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "LeftJoin",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "u_a": 1
+    },
     "TableName": "`user`_unsharded",
     "Inputs": [
       {
@@ -383,6 +386,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "LeftJoin",
     "JoinColumnIndexes": "-2",
+    "JoinVars": {
+      "u_a": 0
+    },
     "TableName": "`user`_unsharded",
     "Inputs": [
       {
@@ -420,12 +426,18 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "LeftJoin",
     "JoinColumnIndexes": "-1,1",
+    "JoinVars": {
+      "m1_col": 1
+    },
     "TableName": "`user`_unsharded_unsharded",
     "Inputs": [
       {
         "OperatorType": "Join",
         "Variant": "LeftJoin",
         "JoinColumnIndexes": "-1,1",
+        "JoinVars": {
+          "user_col": 0
+        },
         "TableName": "`user`_unsharded",
         "Inputs": [
           {
@@ -473,12 +485,18 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "LeftJoin",
     "JoinColumnIndexes": "-2,1",
+    "JoinVars": {
+      "m1_col": 0
+    },
     "TableName": "`user`_unsharded_unsharded",
     "Inputs": [
       {
         "OperatorType": "Join",
         "Variant": "LeftJoin",
         "JoinColumnIndexes": "1,-1",
+        "JoinVars": {
+          "user_col": 0
+        },
         "TableName": "`user`_unsharded",
         "Inputs": [
           {
@@ -529,6 +547,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "LeftJoin",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "user_col": 0
+    },
     "TableName": "`user`_user_extra_unsharded",
     "Inputs": [
       {
@@ -545,6 +566,9 @@ Gen4 plan same as above
       {
         "OperatorType": "Join",
         "Variant": "LeftJoin",
+        "JoinVars": {
+          "e_col": 0
+        },
         "TableName": "user_extra_unsharded",
         "Inputs": [
           {
@@ -581,6 +605,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "LeftJoin",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "user_col": 0
+    },
     "TableName": "`user`_user_extra_unsharded",
     "Inputs": [
       {
@@ -597,6 +624,9 @@ Gen4 plan same as above
       {
         "OperatorType": "Join",
         "Variant": "LeftJoin",
+        "JoinVars": {
+          "e_col": 0
+        },
         "TableName": "user_extra_unsharded",
         "Inputs": [
           {
@@ -1167,6 +1197,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "user_id": 1
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -1288,6 +1321,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "user_id": 1
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -1322,6 +1358,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_extra_col": 0
+    },
     "TableName": "user_extra_`user`",
     "Inputs": [
       {
@@ -1363,6 +1402,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_extra_user_id": 0
+    },
     "TableName": "user_extra_`user`",
     "Inputs": [
       {
@@ -1401,6 +1443,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-2",
+    "JoinVars": {
+      "user_name": 0
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -1734,6 +1779,9 @@ Gen4 error: Duplicate column name 'id'
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "t_id": 0
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -2097,6 +2145,10 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "t_col1": 0,
+      "t_id": 1
+    },
     "TableName": "`user`_user_extra_unsharded",
     "Inputs": [
       {
@@ -2168,6 +2220,9 @@ Gen4 plan same as above
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-1,-2",
+        "JoinVars": {
+          "user_col": 2
+        },
         "TableName": "`user`_user_extra",
         "Inputs": [
           {
@@ -2325,6 +2380,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "2",
+    "JoinVars": {
+      "ua_id": 0
+    },
     "TableName": "unsharded_a_`user`_user_extra",
     "Inputs": [
       {
@@ -2342,6 +2400,9 @@ Gen4 plan same as above
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-1,-2",
+        "JoinVars": {
+          "user_id": 0
+        },
         "TableName": "`user`_user_extra",
         "Inputs": [
           {
@@ -2669,6 +2730,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,1",
+    "JoinVars": {
+      "user_col2": 1
+    },
     "TableName": "`user`_unsharded",
     "Inputs": [
       {
@@ -2703,6 +2767,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-2,1",
+    "JoinVars": {
+      "user_col2": 0
+    },
     "TableName": "`user`_unsharded",
     "Inputs": [
       {
@@ -2931,6 +2998,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "user_id": 0
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -2965,6 +3035,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_extra_id": 0
+    },
     "TableName": "user_extra_`user`",
     "Inputs": [
       {
@@ -2997,6 +3070,95 @@ Gen4 plan same as above
   }
 }
 
+# join with bindvariables
+"SELECT `user`.`id` FROM `user` INNER JOIN `user_extra` ON `user`.`id` = `user_extra`.`assembly_id` WHERE `user_extra`.`user_id` = 2"
+{
+  "QueryType": "SELECT",
+  "Original": "SELECT `user`.`id` FROM `user` INNER JOIN `user_extra` ON `user`.`id` = `user_extra`.`assembly_id` WHERE `user_extra`.`user_id` = 2",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "user_id": 0
+    },
+    "TableName": "`user`_user_extra",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `user`.id from `user` where 1 != 1",
+        "Query": "select `user`.id from `user`",
+        "Table": "`user`"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectEqualUnique",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select 1 from user_extra where 1 != 1",
+        "Query": "select 1 from user_extra where user_extra.assembly_id = :user_id and user_extra.user_id = 2",
+        "Table": "user_extra",
+        "Values": [
+          2
+        ],
+        "Vindex": "user_index"
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "SELECT `user`.`id` FROM `user` INNER JOIN `user_extra` ON `user`.`id` = `user_extra`.`assembly_id` WHERE `user_extra`.`user_id` = 2",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "user_extra_assembly_id": 0
+    },
+    "TableName": "user_extra_`user`",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectEqualUnique",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select user_extra.assembly_id from user_extra where 1 != 1",
+        "Query": "select user_extra.assembly_id from user_extra where user_extra.user_id = 2",
+        "Table": "user_extra",
+        "Values": [
+          2
+        ],
+        "Vindex": "user_index"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectEqualUnique",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `user`.id from `user` where 1 != 1",
+        "Query": "select `user`.id from `user` where `user`.id = :user_extra_assembly_id",
+        "Table": "`user`",
+        "Values": [
+          ":user_extra_assembly_id"
+        ],
+        "Vindex": "user_index"
+      }
+    ]
+  }
+}
+
 # verify ',' vs JOIN precedence
 "select u1.a from unsharded u1, unsharded u2 join unsharded u3 on u1.a = u2.a"
 "symbol u1.a not found"
diff --git a/go/vt/vtgate/planbuilder/testdata/large_cases.txt b/go/vt/vtgate/planbuilder/testdata/large_cases.txt
index 6db381e46c..1a3974a70a 100644
--- a/go/vt/vtgate/planbuilder/testdata/large_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/large_cases.txt
@@ -6,6 +6,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "user_id": 0
+    },
     "TableName": "`user`_user_extra_user_metadata_music_unsharded_unsharded_a_unsharded_b_unsharded_auto_music_extra",
     "Inputs": [
       {
@@ -22,6 +25,9 @@
       {
         "OperatorType": "Join",
         "Variant": "Join",
+        "JoinVars": {
+          "user_extra_user_id": 0
+        },
         "TableName": "user_extra_user_metadata_music_unsharded_unsharded_a_unsharded_b_unsharded_auto_music_extra",
         "Inputs": [
           {
@@ -62,6 +68,9 @@
               {
                 "OperatorType": "Join",
                 "Variant": "Join",
+                "JoinVars": {
+                  "music_id": 0
+                },
                 "TableName": "music_unsharded_unsharded_a_unsharded_b_unsharded_auto_music_extra",
                 "Inputs": [
                   {
@@ -78,6 +87,9 @@
                   {
                     "OperatorType": "Join",
                     "Variant": "Join",
+                    "JoinVars": {
+                      "unsharded_x": 0
+                    },
                     "TableName": "unsharded_unsharded_a_unsharded_b_unsharded_auto_music_extra",
                     "Inputs": [
                       {
diff --git a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
index e11eba36c5..068829ac7b 100644
--- a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
@@ -329,6 +329,9 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-1,-2,1,2",
+        "JoinVars": {
+          "user_id": 2
+        },
         "TableName": "`user`_music",
         "Inputs": [
           {
@@ -379,6 +382,9 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-2,-3,1,2",
+        "JoinVars": {
+          "user_id": 0
+        },
         "TableName": "`user`_music",
         "Inputs": [
           {
@@ -432,6 +438,9 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-1,-2,1,-3,2,-4",
+        "JoinVars": {
+          "user_id": 4
+        },
         "TableName": "`user`_music",
         "Inputs": [
           {
@@ -482,6 +491,9 @@ Gen4 error: Expression of SELECT list is not in GROUP BY clause and contains non
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-2,-3,1,-4,2,-5",
+        "JoinVars": {
+          "user_id": 0
+        },
         "TableName": "`user`_music",
         "Inputs": [
           {
@@ -673,6 +685,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "u_a": 0
+    },
     "TableName": "`user`_music",
     "Inputs": [
       {
diff --git a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
index c9f459ad8a..6d7e40f2e6 100644
--- a/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/postprocess_cases.txt
@@ -492,6 +492,7 @@ Gen4 plan same as above
     "Table": "`user`"
   }
 }
+Gen4 plan same as above
 
 # ORDER BY after pull-out subquery
 "select col from user where col in (select col2 from user) order by col"
@@ -539,6 +540,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,-2,1",
+    "JoinVars": {
+      "user_id": 2
+    },
     "TableName": "`user`_music",
     "Inputs": [
       {
@@ -584,6 +588,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,-2,1",
+    "JoinVars": {
+      "user_id": 2
+    },
     "TableName": "`user`_music",
     "Inputs": [
       {
@@ -626,6 +633,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-2,-3,1",
+    "JoinVars": {
+      "user_id": 0
+    },
     "TableName": "`user`_music",
     "Inputs": [
       {
@@ -672,6 +682,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,-2,1",
+    "JoinVars": {
+      "user_id": 2
+    },
     "TableName": "`user`_music",
     "Inputs": [
       {
@@ -714,6 +727,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-2,-3,1",
+    "JoinVars": {
+      "user_id": 0
+    },
     "TableName": "`user`_music",
     "Inputs": [
       {
@@ -813,6 +829,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,-2,1",
+    "JoinVars": {
+      "user_id": 2
+    },
     "TableName": "`user`_music",
     "Inputs": [
       {
@@ -962,6 +981,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,1",
+    "JoinVars": {
+      "u_col": 1
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases.txt b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
index a3899c346e..612f729257 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
@@ -446,40 +446,7 @@ Gen4 error: Unknown table 'a'
 # Cannot auto-resolve for cross-shard joins
 "select col from user join user_extra"
 "symbol col not found"
-{
-  "QueryType": "SELECT",
-  "Original": "select col from user join user_extra",
-  "Instructions": {
-    "OperatorType": "Join",
-    "Variant": "Join",
-    "JoinColumnIndexes": "-1",
-    "TableName": "`user`_user_extra",
-    "Inputs": [
-      {
-        "OperatorType": "Route",
-        "Variant": "SelectScatter",
-        "Keyspace": {
-          "Name": "user",
-          "Sharded": true
-        },
-        "FieldQuery": "select col from `user` where 1 != 1",
-        "Query": "select col 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"
-      }
-    ]
-  }
-}
+Gen4 error: Column 'col' in field list is ambiguous
 
 # Auto-resolve should work if unique vindex columns are referenced
 "select id, user_id from user join user_extra"
@@ -517,6 +484,7 @@ Gen4 error: Unknown table 'a'
     ]
   }
 }
+Gen4 plan same as above
 
 # database calls should be substituted
 "select database() from dual"
@@ -858,6 +826,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,1",
+    "JoinVars": {
+      "user_id": 0
+    },
     "TableName": "`user`_unsharded",
     "Inputs": [
       {
diff --git a/go/vt/vtgate/planbuilder/testdata/symtab_cases.txt b/go/vt/vtgate/planbuilder/testdata/symtab_cases.txt
index bb40820a79..f31ab8742c 100644
--- a/go/vt/vtgate/planbuilder/testdata/symtab_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/symtab_cases.txt
@@ -9,6 +9,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,1",
+    "JoinVars": {
+      "predef2": 0
+    },
     "TableName": "`user`_unsharded",
     "Inputs": [
       {
diff --git a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
index 2586b87cd0..a93bb6585c 100644
--- a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
@@ -108,10 +108,6 @@ Gen4 plan same as above
 "unsupported: only one expression allowed inside aggregates: count(a, b)"
 Gen4 error: aggregate functions take a single argument 'count(a, b)'
 
-# Cannot have more than one aggr(distinct...
-"select count(distinct a), count(distinct b) from user"
-"unsupported: only one distinct aggregation allowed in a select: count(distinct b)"
-
 # scatter aggregate symtab lookup error
 "select id, b as id, count(*) from user order by id"
 "ambiguous symbol reference: id"
@@ -409,7 +405,7 @@ Gen4 plan same as above
 # create view with Cannot auto-resolve for cross-shard joins
 "create view user.view_a as select col from user join user_extra"
 "symbol col not found"
-Gen4 error: Complex select queries are not supported in create or alter view statements
+Gen4 error: Column 'col' in field list is ambiguous
 
 # create view with join that cannot be served in each shard separately
 "create view user.view_a as select user_extra.id from user join user_extra"
diff --git a/go/vt/vtgate/planbuilder/testdata/vindex_func_cases.txt b/go/vt/vtgate/planbuilder/testdata/vindex_func_cases.txt
index c81faa8c7c..f420659bea 100644
--- a/go/vt/vtgate/planbuilder/testdata/vindex_func_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/vindex_func_cases.txt
@@ -190,6 +190,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,-2,1",
+    "JoinVars": {
+      "user_index_id": 0
+    },
     "TableName": "_unsharded",
     "Inputs": [
       {
@@ -230,6 +233,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,1",
+    "JoinVars": {
+      "user_index_id": 1
+    },
     "TableName": "_unsharded",
     "Inputs": [
       {
@@ -270,6 +276,9 @@ Gen4 plan same as above
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,1",
+    "JoinVars": {
+      "ui_id": 1
+    },
     "TableName": "_unsharded",
     "Inputs": [
       {
diff --git a/go/vt/vtgate/planbuilder/testdata/wireup_cases.txt b/go/vt/vtgate/planbuilder/testdata/wireup_cases.txt
index 0671c868a2..1706396ca6 100644
--- a/go/vt/vtgate/planbuilder/testdata/wireup_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/wireup_cases.txt
@@ -7,6 +7,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1,-1,2",
+    "JoinVars": {
+      "uid": 0
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -44,6 +47,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1,-1,2",
+    "JoinVars": {
+      "uid1": 0
+    },
     "TableName": "`user`_user_extra",
     "Inputs": [
       {
@@ -81,6 +87,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "u1_col": 1
+    },
     "TableName": "`user`_`user`_`user`",
     "Inputs": [
       {
@@ -151,6 +160,9 @@
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-2",
+        "JoinVars": {
+          "u1_col": 0
+        },
         "TableName": "`user`_`user`",
         "Inputs": [
           {
@@ -190,6 +202,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "u2_col": 1
+    },
     "TableName": "`user`_`user`_`user`",
     "Inputs": [
       {
@@ -259,6 +274,9 @@
       {
         "OperatorType": "Join",
         "Variant": "Join",
+        "JoinVars": {
+          "u2_col": 0
+        },
         "TableName": "`user`_`user`",
         "Inputs": [
           {
@@ -298,12 +316,18 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "u1_col": 1
+    },
     "TableName": "`user`_`user`_`user`",
     "Inputs": [
       {
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-1,-2",
+        "JoinVars": {
+          "u1_col": 1
+        },
         "TableName": "`user`_`user`",
         "Inputs": [
           {
@@ -351,6 +375,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "u3_col": 0
+    },
     "TableName": "`user`_`user`_`user`",
     "Inputs": [
       {
@@ -368,6 +395,9 @@
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-2",
+        "JoinVars": {
+          "u1_col": 0
+        },
         "TableName": "`user`_`user`",
         "Inputs": [
           {
@@ -410,12 +440,18 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "u1_col": 1
+    },
     "TableName": "`user`_`user`_`user`_`user`",
     "Inputs": [
       {
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-1,-2",
+        "JoinVars": {
+          "u1_col": 1
+        },
         "TableName": "`user`_`user`_`user`",
         "Inputs": [
           {
@@ -503,6 +539,9 @@
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "1",
+        "JoinVars": {
+          "u4_col": 0
+        },
         "TableName": "`user`_`user`_`user`",
         "Inputs": [
           {
@@ -520,6 +559,9 @@
             "OperatorType": "Join",
             "Variant": "Join",
             "JoinColumnIndexes": "-2",
+            "JoinVars": {
+              "u1_col": 0
+            },
             "TableName": "`user`_`user`",
             "Inputs": [
               {
@@ -565,6 +607,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1",
+    "JoinVars": {
+      "u1_col": 1
+    },
     "TableName": "`user`_`user`_`user`",
     "Inputs": [
       {
@@ -625,12 +670,18 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-2",
+    "JoinVars": {
+      "u1_col": 0
+    },
     "TableName": "`user`_`user`_`user`",
     "Inputs": [
       {
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-1,-2",
+        "JoinVars": {
+          "u1_col": 0
+        },
         "TableName": "`user`_`user`",
         "Inputs": [
           {
@@ -689,6 +740,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-1,1",
+    "JoinVars": {
+      "weird_name_a_b_c": 1
+    },
     "TableName": "`weird``name`_unsharded",
     "Inputs": [
       {
@@ -723,6 +777,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1,-2",
+    "JoinVars": {
+      "unsharded_id": 0
+    },
     "TableName": "unsharded_`weird``name`",
     "Inputs": [
       {
@@ -764,6 +821,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "1",
+    "JoinVars": {
+      "weird_name_a_b_c": 0
+    },
     "TableName": "`weird``name`_unsharded",
     "Inputs": [
       {
@@ -798,6 +858,9 @@
     "OperatorType": "Join",
     "Variant": "Join",
     "JoinColumnIndexes": "-2",
+    "JoinVars": {
+      "unsharded_id": 0
+    },
     "TableName": "unsharded_`weird``name`",
     "Inputs": [
       {
@@ -843,6 +906,9 @@
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-1,1",
+        "JoinVars": {
+          "u_col": 1
+        },
         "TableName": "`user`_user_extra",
         "Inputs": [
           {
@@ -883,6 +949,9 @@
         "OperatorType": "Join",
         "Variant": "Join",
         "JoinColumnIndexes": "-2,1",
+        "JoinVars": {
+          "u_col": 0
+        },
         "TableName": "`user`_user_extra",
         "Inputs": [
           {
@@ -912,6 +981,7 @@
     ]
   }
 }
+
 # Wire-up in subquery
 "select 1 from user where id in (select u.id, e.id from user u join user_extra e where e.id = u.col limit 10)"
 {
@@ -929,6 +999,9 @@
             "OperatorType": "Join",
             "Variant": "Join",
             "JoinColumnIndexes": "-1,1",
+            "JoinVars": {
+              "u_col": 1
+            },
             "TableName": "`user`_user_extra",
             "Inputs": [
               {
@@ -1004,6 +1077,9 @@
             "OperatorType": "Join",
             "Variant": "Join",
             "JoinColumnIndexes": "-1,1,-2",
+            "JoinVars": {
+              "u_col": 2
+            },
             "TableName": "`user`_user_extra",
             "Inputs": [
               {
diff --git a/go/vt/vtgate/semantics/analyzer_test.go b/go/vt/vtgate/semantics/analyzer_test.go
index 2eb6191b75..b0e193f4fa 100644
--- a/go/vt/vtgate/semantics/analyzer_test.go
+++ b/go/vt/vtgate/semantics/analyzer_test.go
@@ -58,52 +58,51 @@ from x as t`
 
 	// extract the `t.col2` expression from the subquery
 	sel2 := sel.SelectExprs[1].(*sqlparser.AliasedExpr).Expr.(*sqlparser.Subquery).Select.(*sqlparser.Select)
-	s1 := semTable.GetBaseTableDependencies(extract(sel2, 0))
+	s1 := semTable.BaseTableDependencies(extract(sel2, 0))
 
 	// if scoping works as expected, we should be able to see the inner table being used by the inner expression
 	assert.Equal(t, T2, s1)
 }
 
-func TestBindingSingleTable(t *testing.T) {
-	t.Run("positive tests", func(t *testing.T) {
-		queries := []string{
-			"select col from tabl",
-			"select tabl.col from tabl",
-			"select d.tabl.col from tabl",
-			"select col from d.tabl",
-			"select tabl.col from d.tabl",
-			"select d.tabl.col from d.tabl",
-		}
-		for _, query := range queries {
-			t.Run(query, func(t *testing.T) {
-				stmt, semTable := parseAndAnalyze(t, query, "d")
-				sel, _ := stmt.(*sqlparser.Select)
-				t1 := sel.From[0].(*sqlparser.AliasedTableExpr)
-				ts := semTable.TableSetFor(t1)
-				assert.EqualValues(t, 1, ts)
+func TestBindingSingleTablePositive(t *testing.T) {
+	queries := []string{
+		"select col from tabl",
+		"select tabl.col from tabl",
+		"select d.tabl.col from tabl",
+		"select col from d.tabl",
+		"select tabl.col from d.tabl",
+		"select d.tabl.col from d.tabl",
+	}
+	for _, query := range queries {
+		t.Run(query, func(t *testing.T) {
+			stmt, semTable := parseAndAnalyze(t, query, "d")
+			sel, _ := stmt.(*sqlparser.Select)
+			t1 := sel.From[0].(*sqlparser.AliasedTableExpr)
+			ts := semTable.TableSetFor(t1)
+			assert.EqualValues(t, 1, ts)
 
-				d := semTable.GetBaseTableDependencies(extract(sel, 0))
-				require.Equal(t, T1, d, query)
-			})
-		}
-	})
-	t.Run("negative tests", func(t *testing.T) {
-		queries := []string{
-			"select foo.col from tabl",
-			"select ks.tabl.col from tabl",
-			"select ks.tabl.col from d.tabl",
-			"select d.tabl.col from ks.tabl",
-			"select foo.col from d.tabl",
-		}
-		for _, query := range queries {
-			t.Run(query, func(t *testing.T) {
-				parse, err := sqlparser.Parse(query)
-				require.NoError(t, err)
-				_, err = Analyze(parse.(sqlparser.SelectStatement), "d", &FakeSI{})
-				require.Error(t, err)
-			})
-		}
-	})
+			assert.Equal(t, T1, semTable.BaseTableDependencies(extract(sel, 0)), query)
+			assert.Equal(t, T1, semTable.Dependencies(extract(sel, 0)), query)
+		})
+	}
+}
+
+func TestBindingSingleTableNegative(t *testing.T) {
+	queries := []string{
+		"select foo.col from tabl",
+		"select ks.tabl.col from tabl",
+		"select ks.tabl.col from d.tabl",
+		"select d.tabl.col from ks.tabl",
+		"select foo.col from d.tabl",
+	}
+	for _, query := range queries {
+		t.Run(query, func(t *testing.T) {
+			parse, err := sqlparser.Parse(query)
+			require.NoError(t, err)
+			_, err = Analyze(parse.(sqlparser.SelectStatement), "d", &FakeSI{})
+			require.Error(t, err)
+		})
+	}
 }
 
 func TestOrderByBindingSingleTable(t *testing.T) {
@@ -136,7 +135,28 @@ func TestOrderByBindingSingleTable(t *testing.T) {
 				stmt, semTable := parseAndAnalyze(t, tc.sql, "d")
 				sel, _ := stmt.(*sqlparser.Select)
 				order := sel.OrderBy[0].Expr
-				d := semTable.GetBaseTableDependencies(order)
+				d := semTable.BaseTableDependencies(order)
+				require.Equal(t, tc.deps, d, tc.sql)
+			})
+		}
+	})
+}
+
+func TestOrderByBindingMultiTable(t *testing.T) {
+	t.Run("positive tests", func(t *testing.T) {
+		tcases := []struct {
+			sql  string
+			deps TableSet
+		}{{
+			"select name, name from t1, t2 order by name",
+			T2,
+		}}
+		for _, tc := range tcases {
+			t.Run(tc.sql, func(t *testing.T) {
+				stmt, semTable := parseAndAnalyze(t, tc.sql, "d")
+				sel, _ := stmt.(*sqlparser.Select)
+				order := sel.OrderBy[0].Expr
+				d := semTable.BaseTableDependencies(order)
 				require.Equal(t, tc.deps, d, tc.sql)
 			})
 		}
@@ -183,7 +203,7 @@ func TestGroupByBindingSingleTable(t *testing.T) {
 			stmt, semTable := parseAndAnalyze(t, tc.sql, "d")
 			sel, _ := stmt.(*sqlparser.Select)
 			grp := sel.GroupBy[0]
-			d := semTable.GetBaseTableDependencies(grp)
+			d := semTable.BaseTableDependencies(grp)
 			require.Equal(t, tc.deps, d, tc.sql)
 		})
 	}
@@ -205,7 +225,7 @@ func TestBindingSingleAliasedTable(t *testing.T) {
 				ts := semTable.TableSetFor(t1)
 				assert.EqualValues(t, 1, ts)
 
-				d := semTable.GetBaseTableDependencies(extract(sel, 0))
+				d := semTable.BaseTableDependencies(extract(sel, 0))
 				require.Equal(t, T1, d, query)
 			})
 		}
@@ -248,8 +268,8 @@ func TestUnion(t *testing.T) {
 	assert.EqualValues(t, 1, ts1)
 	assert.EqualValues(t, 2, ts2)
 
-	d1 := semTable.GetBaseTableDependencies(extract(sel1, 0))
-	d2 := semTable.GetBaseTableDependencies(extract(sel2, 0))
+	d1 := semTable.BaseTableDependencies(extract(sel1, 0))
+	d2 := semTable.BaseTableDependencies(extract(sel2, 0))
 	assert.Equal(t, T1, d1)
 	assert.Equal(t, T2, d2)
 }
@@ -306,7 +326,7 @@ func TestBindingMultiTable(t *testing.T) {
 			t.Run(query.query, func(t *testing.T) {
 				stmt, semTable := parseAndAnalyze(t, query.query, "user")
 				sel, _ := stmt.(*sqlparser.Select)
-				assert.Equal(t, query.deps, semTable.GetBaseTableDependencies(extract(sel, 0)), query.query)
+				assert.Equal(t, query.deps, semTable.BaseTableDependencies(extract(sel, 0)), query.query)
 			})
 		}
 	})
@@ -338,7 +358,7 @@ func TestBindingSingleDepPerTable(t *testing.T) {
 	stmt, semTable := parseAndAnalyze(t, query, "")
 	sel, _ := stmt.(*sqlparser.Select)
 
-	d := semTable.GetBaseTableDependencies(extract(sel, 0))
+	d := semTable.BaseTableDependencies(extract(sel, 0))
 	assert.Equal(t, 1, d.NumberOfTables(), "size wrong")
 	assert.Equal(t, T1, d)
 }
@@ -432,12 +452,12 @@ func TestUnknownColumnMap2(t *testing.T) {
 		{
 			name:   "no info about tables",
 			schema: map[string]*vindexes.Table{"a": {}, "b": {}},
-			err:    false,
+			err:    true,
 		},
 		{
 			name:   "non authoritative columns",
 			schema: map[string]*vindexes.Table{"a": &nonAuthoritativeTblA, "b": &nonAuthoritativeTblA},
-			err:    false,
+			err:    true,
 		},
 		{
 			name:   "non authoritative columns - one authoritative and one not",
@@ -609,7 +629,7 @@ func TestScopingWDerivedTables(t *testing.T) {
 			} else {
 				require.NoError(t, err)
 				sel := parse.(*sqlparser.Select)
-				assert.Equal(t, query.recursiveExpectation, st.GetBaseTableDependencies(extract(sel, 0)))
+				assert.Equal(t, query.recursiveExpectation, st.BaseTableDependencies(extract(sel, 0)))
 				assert.Equal(t, query.expectation, st.Dependencies(extract(sel, 0)))
 			}
 		})