Repository: vitessio/vitess
Pull request #8750 (2021-08-31 08:32:33)

Support for UNION in Gen4

## Description
Planning for Union statements in Gen4

## Related Issue(s)
<!-- List related issues and pull requests. If this PR fixes an issue, please add it using Fixes #????  -->
- #7280 

## Checklist
- [ ] Should this PR be backported?
- [x] Tests were added or are not required
- [x] Documentation was added or is not required



Patch:
diff --git a/go/vt/sqlparser/ast_funcs.go b/go/vt/sqlparser/ast_funcs.go
index f6ff720cab..39e01dec1d 100644
--- a/go/vt/sqlparser/ast_funcs.go
+++ b/go/vt/sqlparser/ast_funcs.go
@@ -1441,3 +1441,16 @@ func IsAggregation(node SQLNode) bool {
 	}
 	return false
 }
+
+// GetFirstSelect gets the first select statement
+func GetFirstSelect(selStmt SelectStatement) *Select {
+	switch node := selStmt.(type) {
+	case *Select:
+		return node
+	case *Union:
+		return GetFirstSelect(node.FirstStatement)
+	case *ParenSelect:
+		return GetFirstSelect(node.Select)
+	}
+	panic("[BUG]: unknown type for SelectStatement")
+}
diff --git a/go/vt/vtgate/planbuilder/abstract/concatenate.go b/go/vt/vtgate/planbuilder/abstract/concatenate.go
new file mode 100644
index 0000000000..149c12f81d
--- /dev/null
+++ b/go/vt/vtgate/planbuilder/abstract/concatenate.go
@@ -0,0 +1,57 @@
+/*
+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 abstract
+
+import (
+	"vitess.io/vitess/go/vt/sqlparser"
+	"vitess.io/vitess/go/vt/vtgate/semantics"
+)
+
+// Concatenate represents a UNION ALL.
+type Concatenate struct {
+	SelectStmts []*sqlparser.Select
+	Sources     []Operator
+}
+
+var _ Operator = (*Concatenate)(nil)
+
+// createConcatenateIfRequired creates a Concatenate operator on top of the sources if it is required
+func createConcatenateIfRequired(sources []Operator, selStmts []*sqlparser.Select) Operator {
+	if len(sources) == 1 {
+		return sources[0]
+	}
+	return &Concatenate{Sources: sources, SelectStmts: selStmts}
+}
+
+// TableID implements the Operator interface
+func (c *Concatenate) TableID() semantics.TableSet {
+	var tableSet semantics.TableSet
+	for _, source := range c.Sources {
+		tableSet |= source.TableID()
+	}
+	return tableSet
+}
+
+// PushPredicate implements the Operator interface
+func (c *Concatenate) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error {
+	panic("implement me")
+}
+
+// UnsolvedPredicates implements the Operator interface
+func (c *Concatenate) UnsolvedPredicates(semTable *semantics.SemTable) []sqlparser.Expr {
+	panic("implement me")
+}
diff --git a/go/vt/vtgate/planbuilder/abstract/derived.go b/go/vt/vtgate/planbuilder/abstract/derived.go
index 46dd2ea457..1ee993754a 100644
--- a/go/vt/vtgate/planbuilder/abstract/derived.go
+++ b/go/vt/vtgate/planbuilder/abstract/derived.go
@@ -23,7 +23,7 @@ import (
 
 // Derived represents a derived table in the query
 type Derived struct {
-	Sel   *sqlparser.Select
+	Sel   sqlparser.SelectStatement
 	Inner Operator
 	Alias string
 }
diff --git a/go/vt/vtgate/planbuilder/abstract/distinct.go b/go/vt/vtgate/planbuilder/abstract/distinct.go
new file mode 100644
index 0000000000..190f777916
--- /dev/null
+++ b/go/vt/vtgate/planbuilder/abstract/distinct.go
@@ -0,0 +1,44 @@
+/*
+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 abstract
+
+import (
+	"vitess.io/vitess/go/vt/sqlparser"
+	"vitess.io/vitess/go/vt/vtgate/semantics"
+)
+
+// Distinct eliminates duplicate rows.
+type Distinct struct {
+	Source Operator
+}
+
+var _ Operator = (*Distinct)(nil)
+
+// TableID implements the Operator interface
+func (d *Distinct) TableID() semantics.TableSet {
+	return d.Source.TableID()
+}
+
+// PushPredicate implements the Operator interface
+func (d *Distinct) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable) error {
+	return d.Source.PushPredicate(expr, semTable)
+}
+
+// UnsolvedPredicates implements the Operator interface
+func (d *Distinct) UnsolvedPredicates(semTable *semantics.SemTable) []sqlparser.Expr {
+	return d.Source.UnsolvedPredicates(semTable)
+}
diff --git a/go/vt/vtgate/planbuilder/abstract/fuzz.go b/go/vt/vtgate/planbuilder/abstract/fuzz.go
index 8e49ec3814..9315a0cfe9 100644
--- a/go/vt/vtgate/planbuilder/abstract/fuzz.go
+++ b/go/vt/vtgate/planbuilder/abstract/fuzz.go
@@ -56,7 +56,7 @@ func FuzzAnalyse(data []byte) int {
 		if err != nil {
 			return 0
 		}
-		_, _ = CreateOperatorFromSelect(stmt, semTable)
+		_, _ = createOperatorFromSelect(stmt, semTable)
 
 	default:
 		return 0
diff --git a/go/vt/vtgate/planbuilder/abstract/operator.go b/go/vt/vtgate/planbuilder/abstract/operator.go
index d589660a4c..eb75ebca9f 100644
--- a/go/vt/vtgate/planbuilder/abstract/operator.go
+++ b/go/vt/vtgate/planbuilder/abstract/operator.go
@@ -17,7 +17,9 @@ limitations under the License.
 package abstract
 
 import (
+	vtrpcpb "vitess.io/vitess/go/vt/proto/vtrpc"
 	"vitess.io/vitess/go/vt/sqlparser"
+	"vitess.io/vitess/go/vt/vterrors"
 	"vitess.io/vitess/go/vt/vtgate/semantics"
 )
 
@@ -30,6 +32,8 @@ type (
 	//	*  LeftJoin - A left join. These can't be evaluated in any order, so we keep them separate
 	//	*  Join - A join represents inner join.
 	//  *  SubQuery - Represents a query that encapsulates one or more sub-queries (SubQueryInner).
+	//  *  Concatenate - Represents concatenation of the outputs of all the input sources
+	//  *  Distinct - Represents elimination of duplicates from the output of the input source
 	Operator interface {
 		// TableID returns a TableSet of the tables contained within
 		TableID() semantics.TableSet
@@ -59,15 +63,11 @@ func getOperatorFromTableExpr(tableExpr sqlparser.TableExpr, semTable *semantics
 			qg.Tables = append(qg.Tables, qt)
 			return qg, nil
 		case *sqlparser.DerivedTable:
-			sel, isSel := tbl.Select.(*sqlparser.Select)
-			if !isSel {
-				return nil, semantics.Gen4NotSupportedF("UNION")
-			}
-			inner, err := CreateOperatorFromSelect(sel, semTable)
+			inner, err := CreateOperatorFromAST(tbl.Select, semTable)
 			if err != nil {
 				return nil, err
 			}
-			return &Derived{Alias: tableExpr.As.String(), Inner: inner, Sel: sel}, nil
+			return &Derived{Alias: tableExpr.As.String(), Inner: inner, Sel: tbl.Select}, nil
 		default:
 			return nil, semantics.Gen4NotSupportedF("%T", tbl)
 		}
@@ -132,14 +132,101 @@ func crossJoin(exprs sqlparser.TableExprs, semTable *semantics.SemTable) (Operat
 	return output, nil
 }
 
-// CreateOperatorFromSelect creates an operator tree that represents the input SELECT query
-func CreateOperatorFromSelect(sel *sqlparser.Select, semTable *semantics.SemTable) (Operator, error) {
+func getSelect(s sqlparser.SelectStatement) *sqlparser.Select {
+	switch s := s.(type) {
+	case *sqlparser.Select:
+		return s
+	case *sqlparser.ParenSelect:
+		return getSelect(s.Select)
+	default:
+		return nil
+	}
+}
+
+// CreateOperatorFromAST creates an operator tree that represents the input SELECT or UNION query
+func CreateOperatorFromAST(selStmt sqlparser.SelectStatement, semTable *semantics.SemTable) (Operator, error) {
+	switch node := selStmt.(type) {
+	case *sqlparser.Select:
+		return createOperatorFromSelect(node, semTable)
+	case *sqlparser.Union:
+		return createOperatorFromUnion(node, semTable)
+	case *sqlparser.ParenSelect:
+		return CreateOperatorFromAST(node.Select, semTable)
+	}
+	return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "%T: operator not yet supported", selStmt)
+}
+
+func createOperatorFromUnion(node *sqlparser.Union, semTable *semantics.SemTable) (Operator, error) {
+	op, err := CreateOperatorFromAST(node.FirstStatement, semTable)
+	if err != nil {
+		return nil, err
+	}
+	sources := []Operator{op}
+	sel := getSelect(node.FirstStatement)
+	selectStmts := []*sqlparser.Select{sel}
+
+	var addToSource func(op Operator, sel *sqlparser.Select)
+
+	switch op := op.(type) {
+	case *Distinct:
+		switch src := op.Source.(type) {
+		case *Concatenate:
+			addToSource = func(op Operator, sel *sqlparser.Select) {
+				src.Sources = append(src.Sources, op)
+				src.SelectStmts = append(src.SelectStmts, sel)
+			}
+		}
+	}
+
+	// we only need a single DISTINCT, so we'll go over the UNION to find the last DISTINCT, and that is the one we will keep.
+	// Example: S1 UNION S2 UNION ALL S3 UNION S4 UNION ALL S5
+	// To plan this query, we can do concatenate on S1, S2, S3, and S4, and then distinct, and lastly we concatenate S5
+
+	distinctAt := lastDistinctAt(node)
+
+	for i, unionSelect := range node.UnionSelects {
+		op, err = CreateOperatorFromAST(unionSelect.Statement, semTable)
+		if err != nil {
+			return nil, err
+		}
+
+		sel = getSelect(unionSelect.Statement)
+		if addToSource != nil && i <= distinctAt {
+			// if we can, let's add it to the input instead of building up a new UNION
+			addToSource(op, sel)
+		} else {
+			sources = append(sources, op)
+			selectStmts = append(selectStmts, sel)
+
+			if i == distinctAt {
+				sources = []Operator{&Distinct{Source: &Concatenate{Sources: sources, SelectStmts: selectStmts}}}
+				selectStmts = []*sqlparser.Select{nil}
+			}
+		}
+	}
+	return createConcatenateIfRequired(sources, selectStmts), nil
+}
+
+// lastDistinctAt finds the last DISTINCT in a list of queries UNIONed together
+func lastDistinctAt(node *sqlparser.Union) int {
+	distinctAt := -1
+	for i := len(node.UnionSelects) - 1; i >= 0; i-- {
+		if node.UnionSelects[i].Distinct {
+			distinctAt = i
+			break
+		}
+	}
+	return distinctAt
+}
+
+// createOperatorFromSelect creates an operator tree that represents the input SELECT query
+func createOperatorFromSelect(sel *sqlparser.Select, semTable *semantics.SemTable) (Operator, error) {
 	var resultantOp *SubQuery
 	if len(semTable.SubqueryMap[sel]) > 0 {
 		resultantOp = &SubQuery{}
 		for _, sq := range semTable.SubqueryMap[sel] {
 			subquerySelectStatement := sq.SubQuery.Select.(*sqlparser.Select)
-			opInner, err := CreateOperatorFromSelect(subquerySelectStatement, semTable)
+			opInner, err := createOperatorFromSelect(subquerySelectStatement, semTable)
 			if err != nil {
 				return nil, err
 			}
diff --git a/go/vt/vtgate/planbuilder/builder.go b/go/vt/vtgate/planbuilder/builder.go
index 715fa046c9..41298c5e8f 100644
--- a/go/vt/vtgate/planbuilder/builder.go
+++ b/go/vt/vtgate/planbuilder/builder.go
@@ -124,19 +124,19 @@ func BuildFromStmt(query string, stmt sqlparser.Statement, reservedVars *sqlpars
 	return plan, nil
 }
 
-func getConfiguredPlanner(vschema ContextVSchema) (selectPlanner, error) {
+func getConfiguredPlanner(vschema ContextVSchema, v3planner selectPlanner) (selectPlanner, error) {
 	switch vschema.Planner() {
 	case Gen4, Gen4Left2Right, Gen4GreedyOnly:
 		return gen4Planner, nil
 	case Gen4WithFallback:
 		fp := &fallbackPlanner{
 			primary:  gen4Planner,
-			fallback: buildSelectPlan,
+			fallback: v3planner,
 		}
 		return fp.plan, nil
 	default:
 		// default is v3 plan
-		return buildSelectPlan, nil
+		return v3planner, nil
 	}
 }
 
@@ -152,7 +152,7 @@ type selectPlanner func(query string) func(sqlparser.Statement, *sqlparser.Reser
 func createInstructionFor(query string, stmt sqlparser.Statement, reservedVars *sqlparser.ReservedVars, vschema ContextVSchema, enableOnlineDDL, enableDirectDDL bool) (engine.Primitive, error) {
 	switch stmt := stmt.(type) {
 	case *sqlparser.Select:
-		configuredPlanner, err := getConfiguredPlanner(vschema)
+		configuredPlanner, err := getConfiguredPlanner(vschema, buildSelectPlan)
 		if err != nil {
 			return nil, err
 		}
@@ -164,7 +164,11 @@ func createInstructionFor(query string, stmt sqlparser.Statement, reservedVars *
 	case *sqlparser.Delete:
 		return buildRoutePlan(stmt, reservedVars, vschema, buildDeletePlan)
 	case *sqlparser.Union:
-		return buildRoutePlan(stmt, reservedVars, vschema, buildUnionPlan)
+		configuredPlanner, err := getConfiguredPlanner(vschema, buildUnionPlan)
+		if err != nil {
+			return nil, err
+		}
+		return buildRoutePlan(stmt, reservedVars, vschema, configuredPlanner(query))
 	case sqlparser.DDLStatement:
 		return buildGeneralDDLPlan(query, stmt, reservedVars, vschema, enableOnlineDDL, enableDirectDDL)
 	case *sqlparser.AlterMigration:
diff --git a/go/vt/vtgate/planbuilder/concantenatetree.go b/go/vt/vtgate/planbuilder/concantenatetree.go
new file mode 100644
index 0000000000..160ad1a00f
--- /dev/null
+++ b/go/vt/vtgate/planbuilder/concantenatetree.go
@@ -0,0 +1,61 @@
+/*
+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 planbuilder
+
+import (
+	"vitess.io/vitess/go/vt/proto/vtrpc"
+	"vitess.io/vitess/go/vt/sqlparser"
+	"vitess.io/vitess/go/vt/vterrors"
+	"vitess.io/vitess/go/vt/vtgate/semantics"
+)
+
+type (
+	concatenateTree struct {
+		selectStmts []*sqlparser.Select
+		sources     []queryTree
+	}
+)
+
+var _ queryTree = (*concatenateTree)(nil)
+
+func (c *concatenateTree) tableID() semantics.TableSet {
+	var tableSet semantics.TableSet
+	for _, source := range c.sources {
+		tableSet |= source.tableID()
+	}
+	return tableSet
+}
+
+func (c *concatenateTree) clone() queryTree {
+	var sourcesCopy []queryTree
+	for _, source := range c.sources {
+		sourcesCopy = append(sourcesCopy, source.clone())
+	}
+	return &concatenateTree{sources: sourcesCopy, selectStmts: c.selectStmts}
+}
+
+func (c *concatenateTree) cost() int {
+	var totalCost int
+	for _, source := range c.sources {
+		totalCost += source.cost()
+	}
+	return totalCost
+}
+
+func (c *concatenateTree) pushOutputColumns(columns []*sqlparser.ColName, semTable *semantics.SemTable) ([]int, error) {
+	return nil, vterrors.New(vtrpc.Code_INTERNAL, "pushOutputColumns should not be called on this struct")
+}
diff --git a/go/vt/vtgate/planbuilder/concatenateGen4.go b/go/vt/vtgate/planbuilder/concatenateGen4.go
new file mode 100644
index 0000000000..70741d96ad
--- /dev/null
+++ b/go/vt/vtgate/planbuilder/concatenateGen4.go
@@ -0,0 +1,111 @@
+/*
+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 planbuilder
+
+import (
+	vtrpcpb "vitess.io/vitess/go/vt/proto/vtrpc"
+	"vitess.io/vitess/go/vt/sqlparser"
+	"vitess.io/vitess/go/vt/vterrors"
+	"vitess.io/vitess/go/vt/vtgate/engine"
+	"vitess.io/vitess/go/vt/vtgate/semantics"
+)
+
+type concatenateGen4 struct {
+	sources []logicalPlan
+}
+
+var _ logicalPlan = (*concatenateGen4)(nil)
+
+// Order implements the logicalPlan interface
+func (c *concatenateGen4) Order() int {
+	panic("implement me")
+}
+
+// ResultColumns implements the logicalPlan interface
+func (c *concatenateGen4) ResultColumns() []*resultColumn {
+	panic("implement me")
+}
+
+// Reorder implements the logicalPlan interface
+func (c *concatenateGen4) Reorder(order int) {
+	panic("implement me")
+}
+
+// Wireup implements the logicalPlan interface
+func (c *concatenateGen4) Wireup(plan logicalPlan, jt *jointab) error {
+	panic("implement me")
+}
+
+// WireupGen4 implements the logicalPlan interface
+func (c *concatenateGen4) WireupGen4(semTable *semantics.SemTable) error {
+	for _, source := range c.sources {
+		err := source.WireupGen4(semTable)
+		if err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
+// SupplyVar implements the logicalPlan interface
+func (c *concatenateGen4) SupplyVar(from, to int, col *sqlparser.ColName, varname string) {
+	panic("implement me")
+}
+
+// SupplyCol implements the logicalPlan interface
+func (c *concatenateGen4) SupplyCol(col *sqlparser.ColName) (rc *resultColumn, colNumber int) {
+	panic("implement me")
+}
+
+// SupplyWeightString implements the logicalPlan interface
+func (c *concatenateGen4) SupplyWeightString(colNumber int) (weightcolNumber int, err error) {
+	panic("implement me")
+}
+
+// Primitive implements the logicalPlan interface
+func (c *concatenateGen4) Primitive() engine.Primitive {
+	var sources []engine.Primitive
+	for _, source := range c.sources {
+		sources = append(sources, source.Primitive())
+	}
+	return &engine.Concatenate{
+		Sources: sources,
+	}
+}
+
+// Rewrite implements the logicalPlan interface
+func (c *concatenateGen4) Rewrite(inputs ...logicalPlan) error {
+	if len(inputs) != len(c.sources) {
+		return vterrors.Errorf(vtrpcpb.Code_INTERNAL, "concatenateGen4: wrong number of inputs")
+	}
+	c.sources = inputs
+	return nil
+}
+
+// ContainsTables implements the logicalPlan interface
+func (c *concatenateGen4) ContainsTables() semantics.TableSet {
+	var tableSet semantics.TableSet
+	for _, source := range c.sources {
+		tableSet |= source.ContainsTables()
+	}
+	return tableSet
+}
+
+// Inputs implements the logicalPlan interface
+func (c *concatenateGen4) Inputs() []logicalPlan {
+	return c.sources
+}
diff --git a/go/vt/vtgate/planbuilder/derivedtree.go b/go/vt/vtgate/planbuilder/derivedtree.go
index 6f00866f82..32ae9ab085 100644
--- a/go/vt/vtgate/planbuilder/derivedtree.go
+++ b/go/vt/vtgate/planbuilder/derivedtree.go
@@ -24,7 +24,7 @@ import (
 )
 
 type derivedTree struct {
-	query *sqlparser.Select
+	query sqlparser.SelectStatement
 	inner queryTree
 	alias string
 }
@@ -57,7 +57,7 @@ func (d *derivedTree) pushOutputColumns(names []*sqlparser.ColName, _ *semantics
 }
 
 func (d *derivedTree) findOutputColumn(name *sqlparser.ColName) (int, error) {
-	for j, exp := range d.query.SelectExprs {
+	for j, exp := range sqlparser.GetFirstSelect(d.query).SelectExprs {
 		ae, ok := exp.(*sqlparser.AliasedExpr)
 		if !ok {
 			return 0, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "expected AliasedExpr")
diff --git a/go/vt/vtgate/planbuilder/distincttree.go b/go/vt/vtgate/planbuilder/distincttree.go
new file mode 100644
index 0000000000..e989904efe
--- /dev/null
+++ b/go/vt/vtgate/planbuilder/distincttree.go
@@ -0,0 +1,48 @@
+/*
+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 planbuilder
+
+import (
+	"vitess.io/vitess/go/vt/sqlparser"
+	"vitess.io/vitess/go/vt/vtgate/semantics"
+)
+
+type (
+	distinctTree struct {
+		source queryTree
+	}
+)
+
+var _ queryTree = (*distinctTree)(nil)
+
+func (d *distinctTree) tableID() semantics.TableSet {
+	return d.source.tableID()
+}
+
+func (d *distinctTree) clone() queryTree {
+	return &distinctTree{
+		source: d.source.clone(),
+	}
+}
+
+func (d *distinctTree) cost() int {
+	return d.source.cost()
+}
+
+func (d *distinctTree) pushOutputColumns(columns []*sqlparser.ColName, semTable *semantics.SemTable) ([]int, error) {
+	return d.source.pushOutputColumns(columns, semTable)
+}
diff --git a/go/vt/vtgate/planbuilder/gen4_planner.go b/go/vt/vtgate/planbuilder/gen4_planner.go
index 146cbb7d0c..3ad7b8b210 100644
--- a/go/vt/vtgate/planbuilder/gen4_planner.go
+++ b/go/vt/vtgate/planbuilder/gen4_planner.go
@@ -29,32 +29,32 @@ var _ selectPlanner = gen4Planner
 
 func gen4Planner(_ string) func(sqlparser.Statement, *sqlparser.ReservedVars, ContextVSchema) (engine.Primitive, error) {
 	return func(stmt sqlparser.Statement, reservedVars *sqlparser.ReservedVars, vschema ContextVSchema) (engine.Primitive, error) {
-		sel, ok := stmt.(*sqlparser.Select)
+		selStatement, ok := stmt.(sqlparser.SelectStatement)
 		if !ok {
 			return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "%T not yet supported", stmt)
 		}
 
-		// handle dual table for processing at vtgate.
-		p, err := handleDualSelects(sel, vschema)
-		if err != nil {
-			return nil, err
-		}
-		if p != nil {
-			return p, nil
+		sel, isSel := selStatement.(*sqlparser.Select)
+		if isSel {
+			// handle dual table for processing at vtgate.
+			p, err := handleDualSelects(sel, vschema)
+			if err != nil || p != nil {
+				return p, err
+			}
 		}
 
-		getPlan := func(sel *sqlparser.Select) (logicalPlan, error) {
-			return newBuildSelectPlan(sel, reservedVars, vschema)
+		getPlan := func(selStatement sqlparser.SelectStatement) (logicalPlan, error) {
+			return newBuildSelectPlan(selStatement, reservedVars, vschema)
 		}
 
-		plan, err := getPlan(sel)
+		plan, err := getPlan(selStatement)
 		if err != nil {
 			return nil, err
 		}
 
 		if shouldRetryWithCNFRewriting(plan) {
 			// by transforming the predicates to CNF, the planner will sometimes find better plans
-			primitive := rewriteToCNFAndReplan(stmt, getPlan)
+			primitive := gen4CNFRewrite(stmt, getPlan)
 			if primitive != nil {
 				return primitive, nil
 			}
@@ -63,23 +63,37 @@ func gen4Planner(_ string) func(sqlparser.Statement, *sqlparser.ReservedVars, Co
 	}
 }
 
-func newBuildSelectPlan(sel *sqlparser.Select, reservedVars *sqlparser.ReservedVars, vschema ContextVSchema) (logicalPlan, error) {
+func gen4CNFRewrite(stmt sqlparser.Statement, getPlan func(selStatement sqlparser.SelectStatement) (logicalPlan, error)) engine.Primitive {
+	rewritten, isSel := sqlparser.RewriteToCNF(stmt).(sqlparser.SelectStatement)
+	if !isSel {
+		// Fail-safe code, should never happen
+		return nil
+	}
+	plan2, err := getPlan(rewritten)
+	if err == nil && !shouldRetryWithCNFRewriting(plan2) {
+		// we only use this new plan if it's better than the old one we got
+		return plan2.Primitive()
+	}
+	return nil
+}
+
+func newBuildSelectPlan(selStmt sqlparser.SelectStatement, reservedVars *sqlparser.ReservedVars, vschema ContextVSchema) (logicalPlan, error) {
 	ksName := ""
 	if ks, _ := vschema.DefaultKeyspace(); ks != nil {
 		ksName = ks.Name
 	}
-	semTable, err := semantics.Analyze(sel, ksName, vschema, starRewrite)
+	semTable, err := semantics.Analyze(selStmt, ksName, vschema, starRewrite)
 	if err != nil {
 		return nil, err
 	}
 
 	ctx := newPlanningContext(reservedVars, semTable, vschema)
-	err = queryRewrite(ctx, sel)
+	err = queryRewrite(ctx, selStmt)
 	if err != nil {
 		return nil, err
 	}
 
-	opTree, err := abstract.CreateOperatorFromSelect(sel, semTable)
+	opTree, err := abstract.CreateOperatorFromAST(selStmt, semTable)
 	if err != nil {
 		return nil, err
 	}
@@ -89,25 +103,30 @@ func newBuildSelectPlan(sel *sqlparser.Select, reservedVars *sqlparser.ReservedV
 		return nil, err
 	}
 
-	plan, err := transformToLogicalPlan(ctx, tree, semTable)
+	plan, err := transformToLogicalPlan(ctx, tree)
 	if err != nil {
 		return nil, err
 	}
 
-	plan, err = planHorizon(ctx, plan, sel)
-	if err != nil {
-		return nil, err
-	}
+	sel, isSel := selStmt.(*sqlparser.Select)
+	// We do not need to call planHorizon for union queries.
+	// We would have already executed this for the select statements while transforming to logical plans.
+	if isSel {
+		plan, err = planHorizon(ctx, plan, sel)
+		if err != nil {
+			return nil, err
+		}
 
-	if err := setMiscFunc(plan, sel); err != nil {
-		return nil, err
+		if err := setMiscFunc(plan, sel); err != nil {
+			return nil, err
+		}
 	}
 
 	if err := plan.WireupGen4(semTable); err != nil {
 		return nil, err
 	}
 
-	directives := sqlparser.ExtractCommentDirectives(sel.Comments)
+	directives := sqlparser.ExtractCommentDirectives(sqlparser.GetFirstSelect(selStmt).Comments)
 	if directives.IsSet(sqlparser.DirectiveScatterErrorsAsWarnings) {
 		_, _ = visit(plan, func(logicalPlan logicalPlan) (bool, logicalPlan, error) {
 			switch plan := logicalPlan.(type) {
@@ -161,22 +180,25 @@ func checkUnsupportedConstructs(sel *sqlparser.Select) error {
 	return nil
 }
 
-func planHorizon(ctx planningContext, plan logicalPlan, sel *sqlparser.Select) (logicalPlan, error) {
-	hp := horizonPlanning{
-		sel: sel,
-	}
+func planHorizon(ctx planningContext, plan logicalPlan, in sqlparser.SelectStatement) (logicalPlan, error) {
+	sel, isSel := in.(*sqlparser.Select)
+	if isSel {
+		hp := horizonPlanning{
+			sel: sel,
+		}
 
-	replaceSubQuery(ctx.sqToReplace, sel)
-	var err error
-	plan, err = hp.planHorizon(ctx, plan)
-	if err != nil {
-		return nil, err
+		replaceSubQuery(ctx.sqToReplace, sel)
+		var err error
+		plan, err = hp.planHorizon(ctx, plan)
+		if err != nil {
+			return nil, err
+		}
+		plan, err = planLimit(sel.Limit, plan)
+		if err != nil {
+			return nil, err
+		}
 	}
 
-	plan, err = planLimit(sel.Limit, plan)
-	if err != nil {
-		return nil, err
-	}
 	return plan, nil
 
 }
diff --git a/go/vt/vtgate/planbuilder/horizon_planning.go b/go/vt/vtgate/planbuilder/horizon_planning.go
index 250b9616d1..73eb03d672 100644
--- a/go/vt/vtgate/planbuilder/horizon_planning.go
+++ b/go/vt/vtgate/planbuilder/horizon_planning.go
@@ -148,7 +148,7 @@ func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *sem
 				return i, false, nil
 			}
 		}
-		expr = removeQualifierFromColName(expr)
+		expr = removeKeyspaceFromColName(expr)
 
 		offset := len(sel.SelectExprs)
 		sel.SelectExprs = append(sel.SelectExprs, expr)
@@ -213,12 +213,14 @@ func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *sem
 			}
 		}
 		return 0, false, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "cannot push projections in ordered aggregates")
+	case *limit:
+		return pushProjection(expr, node.input, semTable, inner, reuseCol)
 	default:
 		return 0, false, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "[BUG] push projection does not yet support: %T", node)
 	}
 }
 
-func removeQualifierFromColName(expr *sqlparser.AliasedExpr) *sqlparser.AliasedExpr {
+func removeKeyspaceFromColName(expr *sqlparser.AliasedExpr) *sqlparser.AliasedExpr {
 	if _, ok := expr.Expr.(*sqlparser.ColName); ok {
 		expr = sqlparser.CloneRefOfAliasedExpr(expr)
 		col := expr.Expr.(*sqlparser.ColName)
@@ -526,6 +528,13 @@ func (hp *horizonPlanning) planOrderBy(ctx planningContext, orderExprs []abstrac
 		}
 		plan.underlying = newUnderlyingPlan
 		return plan, nil
+	case *limit:
+		newUnderlyingPlan, err := hp.planOrderBy(ctx, orderExprs, plan.input)
+		if err != nil {
+			return nil, err
+		}
+		plan.input = newUnderlyingPlan
+		return plan, nil
 	default:
 		return nil, semantics.Gen4NotSupportedF("ordering on complex query %T", plan)
 	}
diff --git a/go/vt/vtgate/planbuilder/querytree.go b/go/vt/vtgate/planbuilder/querytree.go
index de2c859983..d877eb73d1 100644
--- a/go/vt/vtgate/planbuilder/querytree.go
+++ b/go/vt/vtgate/planbuilder/querytree.go
@@ -74,7 +74,7 @@ type (
 
 		alias string
 
-		query *sqlparser.Select
+		query sqlparser.SelectStatement
 	}
 )
 
diff --git a/go/vt/vtgate/planbuilder/querytree_transformers.go b/go/vt/vtgate/planbuilder/querytree_transformers.go
index c22f2e3c56..4e7f31e943 100644
--- a/go/vt/vtgate/planbuilder/querytree_transformers.go
+++ b/go/vt/vtgate/planbuilder/querytree_transformers.go
@@ -25,29 +25,47 @@ import (
 	vtrpcpb "vitess.io/vitess/go/vt/proto/vtrpc"
 	"vitess.io/vitess/go/vt/sqlparser"
 	"vitess.io/vitess/go/vt/vtgate/engine"
-	"vitess.io/vitess/go/vt/vtgate/semantics"
 	"vitess.io/vitess/go/vt/vtgate/vindexes"
 
 	"vitess.io/vitess/go/vt/vterrors"
 )
 
-func transformToLogicalPlan(ctx planningContext, tree queryTree, semTable *semantics.SemTable) (logicalPlan, error) {
+func transformToLogicalPlan(ctx planningContext, tree queryTree) (logicalPlan, error) {
 	switch n := tree.(type) {
 	case *routeTree:
-		return transformRoutePlan(n, semTable, ctx.sqToReplace)
+		return transformRoutePlan(ctx, n)
 	case *joinTree:
-		return transformJoinPlan(ctx, n, semTable)
+		return transformJoinPlan(ctx, n)
 	case *derivedTree:
-		return transformDerivedPlan(ctx, n, semTable)
+		return transformDerivedPlan(ctx, n)
 	case *subqueryTree:
-		return transformSubqueryTree(ctx, n, semTable)
+		return transformSubqueryTree(ctx, n)
+	case *concatenateTree:
+		return transformConcatenatePlan(ctx, n)
+	case *distinctTree:
+		return transformDistinctPlan(ctx, n)
 	}
 
 	return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "[BUG] unknown query tree encountered: %T", tree)
 }
 
-func transformSubqueryTree(ctx planningContext, n *subqueryTree, semTable *semantics.SemTable) (logicalPlan, error) {
-	innerPlan, err := transformToLogicalPlan(ctx, n.inner, semTable)
+func pushDistinct(plan logicalPlan) error {
+	switch n := plan.(type) {
+	case *route:
+		n.Select.MakeDistinct()
+	case *concatenateGen4:
+		for _, source := range n.sources {
+			err := pushDistinct(source)
+			if err != nil {
+				return err
+			}
+		}
+	}
+	return nil
+}
+
+func transformSubqueryTree(ctx planningContext, n *subqueryTree) (logicalPlan, error) {
+	innerPlan, err := transformToLogicalPlan(ctx, n.inner)
 	if err != nil {
 		return nil, err
 	}
@@ -57,7 +75,7 @@ func transformSubqueryTree(ctx planningContext, n *subqueryTree, semTable *seman
 	}
 
 	plan := newPulloutSubquery(n.opcode, n.argName, "", innerPlan)
-	outerPlan, err := transformToLogicalPlan(ctx, n.outer, semTable)
+	outerPlan, err := transformToLogicalPlan(ctx, n.outer)
 	if err != nil {
 		return nil, err
 	}
@@ -65,14 +83,14 @@ func transformSubqueryTree(ctx planningContext, n *subqueryTree, semTable *seman
 	return plan, err
 }
 
-func transformDerivedPlan(ctx planningContext, n *derivedTree, semTable *semantics.SemTable) (logicalPlan, error) {
+func transformDerivedPlan(ctx planningContext, n *derivedTree) (logicalPlan, error) {
 	// transforming the inner part of the derived table into a logical plan
 	// so that we can do horizon planning on the inner. If the logical plan
 	// we've produced is a Route, we set its Select.From field to be an aliased
 	// expression containing our derived table's inner select and the derived
 	// table's alias.
 
-	plan, err := transformToLogicalPlan(ctx, n.inner, semTable)
+	plan, err := transformToLogicalPlan(ctx, n.inner)
 	if err != nil {
 		return nil, err
 	}
@@ -100,7 +118,81 @@ func transformDerivedPlan(ctx planningContext, n *derivedTree, semTable *semanti
 	return plan, nil
 }
 
-func transformRoutePlan(n *routeTree, semTable *semantics.SemTable, sqToReplace map[string]*sqlparser.Select) (*route, error) {
+func transformConcatenatePlan(ctx planningContext, n *concatenateTree) (logicalPlan, error) {
+	var sources []logicalPlan
+
+	for i, source := range n.sources {
+		plan, err := createLogicalPlan(ctx, source, n.selectStmts[i])
+		if err != nil {
+			return nil, err
+		}
+		if i == 0 {
+			sources = append(sources, plan)
+			continue
+		}
+		last := sources[len(sources)-1]
+		newPlan := mergeUnionLogicalPlans(ctx, last, plan)
+		if newPlan != nil {
+			sources[len(sources)-1] = newPlan
+			continue
+		}
+		sources = append(sources, plan)
+	}
+
+	if len(sources) == 1 {
+		return sources[0], nil
+	}
+
+	return &concatenateGen4{
+		sources: sources,
+	}, nil
+}
+
+func mergeUnionLogicalPlans(ctx planningContext, left logicalPlan, right logicalPlan) logicalPlan {
+	lroute, ok := left.(*route)
+	if !ok {
+		return nil
+	}
+	rroute, ok := right.(*route)
+	if !ok {
+		return nil
+	}
+
+	if canMergePlans(ctx, lroute, rroute) {
+		elem := &sqlparser.UnionSelect{
+			Distinct:  false,
+			Statement: rroute.Select,
+		}
+		switch n := lroute.Select.(type) {
+		case *sqlparser.Union:
+			n.UnionSelects = append(n.UnionSelects, elem)
+		default:
+			lroute.Select = &sqlparser.Union{FirstStatement: lroute.Select, UnionSelects: []*sqlparser.UnionSelect{elem}}
+		}
+
+		return lroute
+	}
+	return nil
+}
+
+func createLogicalPlan(ctx planningContext, source queryTree, selStmt *sqlparser.Select) (logicalPlan, error) {
+	plan, err := transformToLogicalPlan(ctx, source)
+	if err != nil {
+		return nil, err
+	}
+	if selStmt != nil {
+		plan, err = planHorizon(ctx, plan, selStmt)
+		if err != nil {
+			return nil, err
+		}
+		if err := setMiscFunc(plan, selStmt); err != nil {
+			return nil, err
+		}
+	}
+	return plan, nil
+}
+
+func transformRoutePlan(ctx planningContext, n *routeTree) (*route, error) {
 	var tablesForSelect sqlparser.TableExprs
 	tableNameMap := map[string]interface{}{}
 
@@ -187,11 +279,16 @@ func transformRoutePlan(n *routeTree, semTable *semantics.SemTable, sqToReplace
 		SelectExprs: expressions,
 		From:        tablesForSelect,
 		Where:       where,
-		Comments:    semTable.Comments,
+		Comments:    ctx.semTable.Comments,
 	}
 
-	replaceSubQuery(sqToReplace, sel)
+	replaceSubQuery(ctx.sqToReplace, sel)
 
+	// TODO clean up when gen4 is the only planner
+	var condition sqlparser.Expr
+	if n.selected != nil && len(n.selected.valueExprs) > 0 {
+		condition = n.selected.valueExprs[0]
+	}
 	return &route{
 		eroute: &engine.Route{
 			Opcode:              n.routeOpCode,
@@ -202,17 +299,34 @@ func transformRoutePlan(n *routeTree, semTable *semantics.SemTable, sqToReplace
 			SysTableTableName:   n.SysTableTableName,
 			SysTableTableSchema: n.SysTableTableSchema,
 		},
-		Select: sel,
-		tables: n.solved,
+		Select:    sel,
+		tables:    n.solved,
+		condition: condition,
 	}, nil
 }
 
-func transformJoinPlan(ctx planningContext, n *joinTree, semTable *semantics.SemTable) (logicalPlan, error) {
-	lhs, err := transformToLogicalPlan(ctx, n.lhs, semTable)
+func transformDistinctPlan(ctx planningContext, n *distinctTree) (logicalPlan, error) {
+	innerPlan, err := transformToLogicalPlan(ctx, n.source)
+	if err != nil {
+		return nil, err
+	}
+	err = pushDistinct(innerPlan)
+	if err != nil {
+		return nil, err
+	}
+	if rb, isRoute := innerPlan.(*route); isRoute && rb.isSingleShard() {
+		// if we have a single shard route, we don't need to do anything to make it distinct
+		return innerPlan, nil
+	}
+	return newDistinct(innerPlan), nil
+}
+
+func transformJoinPlan(ctx planningContext, n *joinTree) (logicalPlan, error) {
+	lhs, err := transformToLogicalPlan(ctx, n.lhs)
 	if err != nil {
 		return nil, err
 	}
-	rhs, err := transformToLogicalPlan(ctx, n.rhs, semTable)
+	rhs, err := transformToLogicalPlan(ctx, n.rhs)
 	if err != nil {
 		return nil, err
 	}
@@ -281,7 +395,7 @@ func relToTableExpr(t relation) (sqlparser.TableExpr, error) {
 		tbls := innerTables.(*sqlparser.ParenTableExpr)
 
 		sel := &sqlparser.Select{
-			SelectExprs: t.query.SelectExprs,
+			SelectExprs: sqlparser.GetFirstSelect(t.query).SelectExprs,
 			From:        tbls.Exprs,
 			Where:       &sqlparser.Where{Expr: sqlparser.AndExpressions(t.predicates...)},
 		}
diff --git a/go/vt/vtgate/planbuilder/route.go b/go/vt/vtgate/planbuilder/route.go
index 5b9c0d78d9..55dcbe2c44 100644
--- a/go/vt/vtgate/planbuilder/route.go
+++ b/go/vt/vtgate/planbuilder/route.go
@@ -490,8 +490,14 @@ func (rb *route) unionCanMerge(other *route, distinct bool) bool {
 		return false
 	}
 	switch rb.eroute.Opcode {
-	case engine.SelectUnsharded, engine.SelectDBA, engine.SelectReference:
+	case engine.SelectUnsharded, engine.SelectReference:
 		return rb.eroute.Opcode == other.eroute.Opcode
+	case engine.SelectDBA:
+		return other.eroute.Opcode == engine.SelectDBA &&
+			len(rb.eroute.SysTableTableSchema) == 0 &&
+			len(rb.eroute.SysTableTableName) == 0 &&
+			len(other.eroute.SysTableTableSchema) == 0 &&
+			len(other.eroute.SysTableTableName) == 0
 	case engine.SelectEqualUnique:
 		// Check if they target the same shard.
 		if other.eroute.Opcode == engine.SelectEqualUnique && rb.eroute.Vindex == other.eroute.Vindex && valEqual(rb.condition, other.condition) {
diff --git a/go/vt/vtgate/planbuilder/route_planning.go b/go/vt/vtgate/planbuilder/route_planning.go
index 7f6d4f60c5..a0fca0d3d5 100644
--- a/go/vt/vtgate/planbuilder/route_planning.go
+++ b/go/vt/vtgate/planbuilder/route_planning.go
@@ -87,11 +87,38 @@ func optimizeQuery(ctx planningContext, opTree abstract.Operator) (queryTree, er
 		}, nil
 	case *abstract.SubQuery:
 		return optimizeSubQuery(ctx, op)
+	case *abstract.Concatenate:
+		return optimizeUnion(ctx, op)
+	case *abstract.Distinct:
+		qt, err := optimizeQuery(ctx, op.Source)
+		if err != nil {
+			return nil, err
+		}
+		return &distinctTree{
+			source: qt,
+		}, nil
 	default:
 		return nil, semantics.Gen4NotSupportedF("optimizeQuery")
 	}
 }
 
+func optimizeUnion(ctx planningContext, op *abstract.Concatenate) (queryTree, error) {
+	var sources []queryTree
+	for _, source := range op.Sources {
+		qt, err := optimizeQuery(ctx, source)
+		if err != nil {
+			return nil, err
+		}
+
+		sources = append(sources, qt)
+	}
+
+	return &concatenateTree{
+		selectStmts: op.SelectStmts,
+		sources:     sources,
+	}, nil
+}
+
 func optimizeSubQuery(ctx planningContext, op *abstract.SubQuery) (queryTree, error) {
 	outerTree, err := optimizeQuery(ctx, op.Outer)
 	if err != nil {
@@ -200,7 +227,7 @@ func createSingleShardRoutePlan(sel *sqlparser.Select, rb *route) {
 	ast.SelectExprs = sel.SelectExprs
 	for i, expr := range ast.SelectExprs {
 		if aliasedExpr, ok := expr.(*sqlparser.AliasedExpr); ok {
-			ast.SelectExprs[i] = removeQualifierFromColName(aliasedExpr)
+			ast.SelectExprs[i] = removeKeyspaceFromColName(aliasedExpr)
 		}
 	}
 }
@@ -645,6 +672,37 @@ func canMergeOnFilters(ctx planningContext, a, b *routeTree, joinPredicates []sq
 
 type mergeFunc func(a, b *routeTree) *routeTree
 
+func canMergePlans(ctx planningContext, a, b *route) bool {
+	// this method should be close to tryMerge below. it does the same thing, but on logicalPlans instead of queryTrees
+	if a.eroute.Keyspace.Name != b.eroute.Keyspace.Name {
+		return false
+	}
+	switch a.eroute.Opcode {
+	case engine.SelectUnsharded, engine.SelectReference:
+		return a.eroute.Opcode == b.eroute.Opcode
+	case engine.SelectDBA:
+		return b.eroute.Opcode == engine.SelectDBA &&
+			len(a.eroute.SysTableTableSchema) == 0 &&
+			len(a.eroute.SysTableTableName) == 0 &&
+			len(b.eroute.SysTableTableSchema) == 0 &&
+			len(b.eroute.SysTableTableName) == 0
+	case engine.SelectEqualUnique:
+		// Check if they target the same shard.
+		if b.eroute.Opcode == engine.SelectEqualUnique &&
+			a.eroute.Vindex == b.eroute.Vindex &&
+			a.condition != nil &&
+			b.condition != nil &&
+			gen4ValuesEqual(ctx, []sqlparser.Expr{a.condition}, []sqlparser.Expr{b.condition}) {
+			return true
+		}
+	case engine.SelectScatter:
+		return b.eroute.Opcode == engine.SelectScatter
+	case engine.SelectNext:
+		return false
+	}
+	return false
+}
+
 func tryMerge(ctx planningContext, a, b queryTree, joinPredicates []sqlparser.Expr, merger mergeFunc) (queryTree, error) {
 	aRoute, bRoute := joinTreesToRoutes(a.clone(), b.clone())
 	if aRoute == nil || bRoute == nil {
diff --git a/go/vt/vtgate/planbuilder/union.go b/go/vt/vtgate/planbuilder/union.go
index 82292340d1..0f0065d792 100644
--- a/go/vt/vtgate/planbuilder/union.go
+++ b/go/vt/vtgate/planbuilder/union.go
@@ -29,17 +29,19 @@ import (
 	"vitess.io/vitess/go/vt/vtgate/engine"
 )
 
-func buildUnionPlan(stmt sqlparser.Statement, reservedVars *sqlparser.ReservedVars, vschema ContextVSchema) (engine.Primitive, error) {
-	union := stmt.(*sqlparser.Union)
-	// For unions, create a pb with anonymous scope.
-	pb := newPrimitiveBuilder(vschema, newJointab(reservedVars))
-	if err := pb.processUnion(union, reservedVars, nil); err != nil {
-		return nil, err
-	}
-	if err := pb.plan.Wireup(pb.plan, pb.jt); err != nil {
-		return nil, err
+func buildUnionPlan(string) func(sqlparser.Statement, *sqlparser.ReservedVars, ContextVSchema) (engine.Primitive, error) {
+	return func(stmt sqlparser.Statement, reservedVars *sqlparser.ReservedVars, vschema ContextVSchema) (engine.Primitive, error) {
+		union := stmt.(*sqlparser.Union)
+		// For unions, create a pb with anonymous scope.
+		pb := newPrimitiveBuilder(vschema, newJointab(reservedVars))
+		if err := pb.processUnion(union, reservedVars, nil); err != nil {
+			return nil, err
+		}
+		if err := pb.plan.Wireup(pb.plan, pb.jt); err != nil {
+			return nil, err
+		}
+		return pb.plan.Primitive(), nil
 	}
-	return pb.plan.Primitive(), nil
 }
 
 func (pb *primitiveBuilder) processUnion(union *sqlparser.Union, reservedVars *sqlparser.ReservedVars, outer *symtab) error {
diff --git a/go/vt/vtgate/semantics/scoper.go b/go/vt/vtgate/semantics/scoper.go
index ff5069143e..6c737e4258 100644
--- a/go/vt/vtgate/semantics/scoper.go
+++ b/go/vt/vtgate/semantics/scoper.go
@@ -76,10 +76,6 @@ func (s *scoper) down(cursor *sqlparser.Cursor) {
 			s.push(nScope)
 			s.sqlNodeScope[scopeKey{node: node}] = nScope
 		}
-	case *sqlparser.Union:
-		scope := newScope(s.currentScope())
-		s.push(scope)
-		s.sqlNodeScope[scopeKey{node: node}] = scope
 	case sqlparser.SelectExprs:
 		sel, parentIsSelect := cursor.Parent().(*sqlparser.Select)
 		if !parentIsSelect {
@@ -106,7 +102,7 @@ func (s *scoper) down(cursor *sqlparser.Cursor) {
 
 func (s *scoper) up(cursor *sqlparser.Cursor) error {
 	switch node := cursor.Node().(type) {
-	case sqlparser.SelectStatement, sqlparser.OrderBy, sqlparser.GroupBy:
+	case *sqlparser.Select, sqlparser.OrderBy, sqlparser.GroupBy:
 		s.popScope()
 	case *sqlparser.Where:
 		if node.Type != sqlparser.HavingClause {
@@ -185,23 +181,29 @@ func (s *scoper) upPost(cursor *sqlparser.Cursor) error {
 }
 
 func (s *scoper) changeScopeForNode(cursor *sqlparser.Cursor, k scopeKey) {
-	sel, ok := cursor.Parent().(*sqlparser.Select)
-	if !ok {
+	switch parent := cursor.Parent().(type) {
+	case *sqlparser.Select:
+		// In ORDER BY, GROUP BY and HAVING, we can see both the scope in the FROM part of the query, and the SELECT columns created
+		// so before walking the rest of the tree, we change the scope to match this behaviour
+		incomingScope := s.currentScope()
+		nScope := newScope(incomingScope)
+		s.push(nScope)
+		s.sqlNodeScope[k] = nScope
+		wScope := s.wScope[parent]
+		nScope.tables = append(nScope.tables, wScope.tables...)
+		nScope.selectStmt = incomingScope.selectStmt
+
+		if s.rScope[parent] != incomingScope {
+			panic("BUG: scope counts did not match")
+		}
+	case *sqlparser.Union:
+		nScope := newScope(nil)
+		nScope.selectStmt = sqlparser.GetFirstSelect(parent)
+		s.push(nScope)
+		s.sqlNodeScope[k] = nScope
+	default:
 		return
 	}
-	// In ORDER BY, GROUP BY and HAVING, we can see both the scope in the FROM part of the query, and the SELECT columns created
-	// so before walking the rest of the tree, we change the scope to match this behaviour
-	incomingScope := s.currentScope()
-	nScope := newScope(incomingScope)
-	s.push(nScope)
-	s.sqlNodeScope[k] = nScope
-	wScope := s.wScope[sel]
-	nScope.tables = append(nScope.tables, wScope.tables...)
-	nScope.selectStmt = incomingScope.selectStmt
-
-	if s.rScope[sel] != incomingScope {
-		panic("BUG: scope counts did not match")
-	}
 }
 
 func (s *scoper) currentScope() *scope {
diff --git a/go/vt/vtgate/semantics/table_collector.go b/go/vt/vtgate/semantics/table_collector.go
index ca4a4a5ebf..b46adf5221 100644
--- a/go/vt/vtgate/semantics/table_collector.go
+++ b/go/vt/vtgate/semantics/table_collector.go
@@ -46,22 +46,36 @@ func (tc *tableCollector) up(cursor *sqlparser.Cursor) error {
 
 	switch t := node.Expr.(type) {
 	case *sqlparser.DerivedTable:
-		sel, isSelect := t.Select.(*sqlparser.Select)
-		if !isSelect {
-			return Gen4NotSupportedF("union in derived table")
-		}
+		switch sel := t.Select.(type) {
+		case *sqlparser.Select:
+			tableInfo := createVTableInfoForExpressions(sel.SelectExprs)
+			if err := tableInfo.checkForDuplicates(); err != nil {
+				return err
+			}
 
-		tableInfo := createVTableInfoForExpressions(sel.SelectExprs)
-		if err := tableInfo.checkForDuplicates(); err != nil {
-			return err
-		}
+			tableInfo.ASTNode = node
+			tableInfo.tableName = node.As.String()
 
-		tableInfo.ASTNode = node
-		tableInfo.tableName = node.As.String()
+			tc.Tables = append(tc.Tables, tableInfo)
+			scope := tc.scoper.currentScope()
+			return scope.addTable(tableInfo)
+
+		case *sqlparser.Union:
+			tableInfo := createVTableInfoForExpressions(sqlparser.GetFirstSelect(sel).SelectExprs)
+			if err := tableInfo.checkForDuplicates(); err != nil {
+				return err
+			}
+			tableInfo.ASTNode = node
+			tableInfo.tableName = node.As.String()
+
+			tc.Tables = append(tc.Tables, tableInfo)
+			scope := tc.scoper.currentScope()
+			return scope.addTable(tableInfo)
+
+		default:
+			return Gen4NotSupportedF("union in derived table")
+		}
 
-		tc.Tables = append(tc.Tables, tableInfo)
-		scope := tc.scoper.currentScope()
-		return scope.addTable(tableInfo)
 	case sqlparser.TableName:
 		var tbl *vindexes.Table
 		var isInfSchema bool


Test patch:
diff --git a/go/vt/vtgate/planbuilder/abstract/operator_test.go b/go/vt/vtgate/planbuilder/abstract/operator_test.go
index 810b9b7c27..8afed7a311 100644
--- a/go/vt/vtgate/planbuilder/abstract/operator_test.go
+++ b/go/vt/vtgate/planbuilder/abstract/operator_test.go
@@ -237,6 +237,62 @@ JoinPredicates:
 	JoinPredicates:
 		1:2 - u.id = (select id from user_extra where id = u.id)
 	}
+}`,
+	}, {
+		input: "select 1 from a union select 2 from b",
+		output: `Distinct {
+	Concatenate {
+		QueryGraph: {
+		Tables:
+			1:a
+		},
+		QueryGraph: {
+		Tables:
+			2:b
+		}
+	}
+}`,
+	}, {
+		input: "select 1 from a union select 2 from b union select 3 from c",
+		output: `Distinct {
+	Concatenate {
+		QueryGraph: {
+		Tables:
+			1:a
+		},
+		QueryGraph: {
+		Tables:
+			2:b
+		},
+		QueryGraph: {
+		Tables:
+			4:c
+		}
+	}
+}`,
+	}, {
+		input: "select 1 from a union select 2 from b union select 3 from c union all select 4 from d",
+		output: `Concatenate {
+	Distinct {
+		Concatenate {
+			QueryGraph: {
+			Tables:
+				1:a
+			},
+			QueryGraph: {
+			Tables:
+				2:b
+			},
+			QueryGraph: {
+			Tables:
+				4:c
+			}
+		}
+	},
+	QueryGraph: {
+	Tables:
+		8:d
+	}
 }`,
 	}}
 
@@ -245,9 +301,10 @@ JoinPredicates:
 		t.Run(fmt.Sprintf("%d %s", i, sql), func(t *testing.T) {
 			tree, err := sqlparser.Parse(sql)
 			require.NoError(t, err)
-			semTable, err := semantics.Analyze(tree.(sqlparser.SelectStatement), "", &semantics.FakeSI{}, semantics.NoRewrite)
+			stmt := tree.(sqlparser.SelectStatement)
+			semTable, err := semantics.Analyze(stmt, "", &semantics.FakeSI{}, semantics.NoRewrite)
 			require.NoError(t, err)
-			optree, err := CreateOperatorFromSelect(tree.(*sqlparser.Select), semTable)
+			optree, err := CreateOperatorFromAST(stmt, semTable)
 			require.NoError(t, err)
 			assert.Equal(t, tc.output, testString(optree))
 			if t.Failed() {
@@ -281,8 +338,17 @@ func testString(op Operator) string {
 		}
 		outer := indent(testString(op.Outer))
 		return fmt.Sprintf("SubQuery: {\n\tSubQueries: %s\n\tOuter: %s\n}", inners, outer)
+	case *Distinct:
+		inner := indent(testString(op.Source))
+		return fmt.Sprintf("Distinct {\n%s\n}", inner)
+	case *Concatenate:
+		var inners []string
+		for _, source := range op.Sources {
+			inners = append(inners, indent(testString(source)))
+		}
+		return fmt.Sprintf("Concatenate {\n%s\n}", strings.Join(inners, ",\n"))
 	}
-	return "implement me"
+	return fmt.Sprintf("implement me: %T", op)
 }
 
 func indent(s string) string {
diff --git a/go/vt/vtgate/planbuilder/plan_test.go b/go/vt/vtgate/planbuilder/plan_test.go
index 0b3c400c37..75ba90b938 100644
--- a/go/vt/vtgate/planbuilder/plan_test.go
+++ b/go/vt/vtgate/planbuilder/plan_test.go
@@ -195,7 +195,7 @@ func TestPlan(t *testing.T) {
 	testFile(t, "flush_cases_no_default_keyspace.txt", testOutputTempDir, vschemaWrapper, false)
 	testFile(t, "show_cases_no_default_keyspace.txt", testOutputTempDir, vschemaWrapper, false)
 	testFile(t, "stream_cases.txt", testOutputTempDir, vschemaWrapper, false)
-	testFile(t, "systemtables_cases.txt", testOutputTempDir, vschemaWrapper, false)
+	testFile(t, "systemtables_cases.txt", testOutputTempDir, vschemaWrapper, true)
 }
 
 func TestSysVarSetDisabled(t *testing.T) {
diff --git a/go/vt/vtgate/planbuilder/testdata/ddl_cases.txt b/go/vt/vtgate/planbuilder/testdata/ddl_cases.txt
index ba1090f2f9..c78ae6a352 100644
--- a/go/vt/vtgate/planbuilder/testdata/ddl_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/ddl_cases.txt
@@ -267,7 +267,6 @@ Gen4 plan same as above
     "Query": "create view view_a as (select id from unsharded) union (select id from unsharded_auto) order by id asc limit 5"
   }
 }
-Gen4 plan same as above
 
 # create view with subquery in unsharded keyspace with multiple UNION clauses
 "create view view_a as select id from unsharded union select id from unsharded_auto union select id from unsharded_auto where id in (132)"
@@ -283,7 +282,6 @@ Gen4 plan same as above
     "Query": "create view view_a as select id from unsharded union select id from unsharded_auto union select id from unsharded_auto where id in (132)"
   }
 }
-Gen4 plan same as above
 
 # create view with subquery in unsharded keyspace with UNION clauses in subqueries
 "create view view_a as (select id from unsharded union select id from unsharded_auto) union (select id from unsharded_auto union select name from unsharded)"
@@ -299,7 +297,6 @@ Gen4 plan same as above
     "Query": "create view view_a as (select id from unsharded union select id from unsharded_auto) union (select id from unsharded_auto union select `name` from unsharded)"
   }
 }
-Gen4 plan same as above
 
 # Alter View
 "alter view user.user_extra as select* from user.user"
diff --git a/go/vt/vtgate/planbuilder/testdata/ddl_cases_no_default_keyspace.txt b/go/vt/vtgate/planbuilder/testdata/ddl_cases_no_default_keyspace.txt
index 35034721d7..1c5e7a17d4 100644
--- a/go/vt/vtgate/planbuilder/testdata/ddl_cases_no_default_keyspace.txt
+++ b/go/vt/vtgate/planbuilder/testdata/ddl_cases_no_default_keyspace.txt
@@ -469,7 +469,6 @@ Gen4 plan same as above
     "Query": "create view view_a as select * from music where user_id = 1 union select * from `user` where id = 1"
   }
 }
-Gen4 plan same as above
 
 # create view with testing SingleRow Projection
 "create view user.view_a as select 42 from user"
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases.txt b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
index f2254c6814..cf97d1c8cb 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
@@ -1392,7 +1392,6 @@ Gen4 plan same as above
     "Table": "unsharded"
   }
 }
-Gen4 plan same as above
 
 "select id from unsharded union select id from unsharded_auto union select id from unsharded_auto where id in (132)"
 {
@@ -1410,7 +1409,6 @@ Gen4 plan same as above
     "Table": "unsharded"
   }
 }
-Gen4 plan same as above
 
 "(select id from unsharded union select id from unsharded_auto) union (select id from unsharded_auto union select name from unsharded)"
 {
@@ -1428,7 +1426,6 @@ Gen4 plan same as above
     "Table": "unsharded"
   }
 }
-Gen4 plan same as above
 
 "(select id from unsharded order by id asc limit 1) union (select id from unsharded order by id desc limit 1) order by id asc limit 1"
 {
@@ -1446,7 +1443,6 @@ Gen4 plan same as above
     "Table": "unsharded"
   }
 }
-Gen4 plan same as above
 
 # routing rules: ensure directives are not lost
 "select /*vt+ QUERY_TIMEOUT_MS=1000 */ * from route2"
@@ -1759,7 +1755,6 @@ Gen4 plan same as above
 # Union after into outfile is incorrect
 "select id from user into outfile 'out_file_name' union all select id from music"
 "Incorrect usage/placement of 'INTO'"
-Gen4 plan same as above
 
 # Into outfile s3 in sub-query is incorrect
 "select id from (select id from user into outfile s3 'inner_outfile') as t2"
@@ -1859,4 +1854,34 @@ Gen4 plan same as above
   }
 }
 
-
+"((((select 1))))"
+{
+  "QueryType": "SELECT",
+  "Original": "((((select 1))))",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectReference",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "((((select 1 from dual where 1 != 1))))",
+    "Query": "((((select 1 from dual))))",
+    "Table": "dual"
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "((((select 1))))",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectReference",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select 1 from dual where 1 != 1",
+    "Query": "select 1 from dual",
+    "Table": "dual"
+  }
+}
diff --git a/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt b/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
index 20efcd45f2..1a145b2bfb 100644
--- a/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
@@ -145,6 +145,85 @@ Gen4 plan same as above
 }
 Gen4 plan same as above
 
+# union between information_schema tables that should not be merged
+"select * from information_schema.tables where table_schema = 'user' union select * from information_schema.tables where table_schema = 'main'"
+{
+  "QueryType": "SELECT",
+  "Original": "select * from information_schema.tables where table_schema = 'user' union select * from information_schema.tables where table_schema = 'main'",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from information_schema.`tables` where 1 != 1",
+            "Query": "select * from information_schema.`tables` where table_schema = :__vtschemaname",
+            "SysTableTableSchema": "[VARBINARY(\"user\")]",
+            "Table": "information_schema.`tables`"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from information_schema.`tables` where 1 != 1",
+            "Query": "select * from information_schema.`tables` where table_schema = :__vtschemaname",
+            "SysTableTableSchema": "[VARBINARY(\"main\")]",
+            "Table": "information_schema.`tables`"
+          }
+        ]
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select * from information_schema.tables where table_schema = 'user' union select * from information_schema.tables where table_schema = 'main'",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from information_schema.`tables` where 1 != 1",
+            "Query": "select distinct * from information_schema.`tables` where table_schema = :__vtschemaname",
+            "SysTableTableSchema": "[VARBINARY(\"user\")]",
+            "Table": "information_schema.`tables`"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from information_schema.`tables` where 1 != 1",
+            "Query": "select distinct * from information_schema.`tables` where table_schema = :__vtschemaname",
+            "SysTableTableSchema": "[VARBINARY(\"main\")]",
+            "Table": "information_schema.`tables`"
+          }
+        ]
+      }
+    ]
+  }
+}
+
 # Select from information schema query with two tables that route should be merged
 "SELECT DELETE_RULE, UPDATE_RULE FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC ON KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME WHERE KCU.TABLE_SCHEMA = 'test' AND KCU.TABLE_NAME = 'data_type_table' AND KCU.COLUMN_NAME = 'id' AND KCU.REFERENCED_TABLE_SCHEMA = 'test' AND KCU.CONSTRAINT_NAME = 'data_type_table_id_fkey' ORDER BY KCU.CONSTRAINT_NAME, KCU.COLUMN_NAME"
 {
diff --git a/go/vt/vtgate/planbuilder/testdata/union_cases.txt b/go/vt/vtgate/planbuilder/testdata/union_cases.txt
index a746ecba78..f2aefbad3e 100644
--- a/go/vt/vtgate/planbuilder/testdata/union_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/union_cases.txt
@@ -55,7 +55,26 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select id from user union select id from music",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select id from `user` where 1 != 1 union select id from music where 1 != 1",
+        "Query": "select id from `user` union select id from music",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
 
 # union all between two SelectEqualUnique
 "select id from user where id = 1 union all select id from user where id = 5"
@@ -188,6 +207,25 @@ Gen4 plan same as above
   }
 }
 
+# union operations in derived table, without star expression (FROM)¡
+"select col1,col2 from (select col1, col2 from user union all select col1, col2 from user_extra) as t"
+{
+  "QueryType": "SELECT",
+  "Original": "select col1,col2 from (select col1, col2 from user union all select col1, col2 from user_extra) as t",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectScatter",
+    "Keyspace": {
+      "Name": "user",
+      "Sharded": true
+    },
+    "FieldQuery": "select col1, col2 from (select col1, col2 from `user` where 1 != 1 union all select col1, col2 from user_extra where 1 != 1) as t where 1 != 1",
+    "Query": "select col1, col2 from (select col1, col2 from `user` union all select col1, col2 from user_extra) as t",
+    "Table": "`user`"
+  }
+}
+Gen4 plan same as above
+
 # union all between two scatter selects, with order by
 "(select id from user order by id limit 5) union all (select id from music order by id desc limit 5)"
 {
@@ -316,7 +354,42 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select * from information_schema.a union select * from unsharded",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from information_schema.a where 1 != 1",
+            "Query": "select distinct * from information_schema.a",
+            "Table": "information_schema.a"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectUnsharded",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from unsharded where 1 != 1",
+            "Query": "select distinct * from unsharded",
+            "Table": "unsharded"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # union of information_schema with normal table
 "select * from unsharded union select * from information_schema.a"
@@ -356,7 +429,42 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select * from unsharded union select * from information_schema.a",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectUnsharded",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from unsharded where 1 != 1",
+            "Query": "select distinct * from unsharded",
+            "Table": "unsharded"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select * from information_schema.a where 1 != 1",
+            "Query": "select distinct * from information_schema.a",
+            "Table": "information_schema.a"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # multi-shard union
 "(select id from user union select id from music) union select 1 from dual"
@@ -417,7 +525,42 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "(select id from user union select id from music) union select 1 from dual",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select id from `user` where 1 != 1 union select id from music where 1 != 1",
+            "Query": "select id from `user` union select id from music",
+            "Table": "`user`"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectReference",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select 1 from dual where 1 != 1",
+            "Query": "select distinct 1 from dual",
+            "Table": "dual"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # multi-shard union
 "select 1 from music union (select id from user union all select name from unsharded)"
@@ -473,7 +616,58 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select 1 from music union (select id from user union all select name from unsharded)",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from music where 1 != 1",
+            "Query": "select distinct 1 from music",
+            "Table": "music"
+          },
+          {
+            "OperatorType": "Concatenate",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectScatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select id from `user` where 1 != 1",
+                "Query": "select distinct id from `user`",
+                "Table": "`user`"
+              },
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectUnsharded",
+                "Keyspace": {
+                  "Name": "main",
+                  "Sharded": false
+                },
+                "FieldQuery": "select `name` from unsharded where 1 != 1",
+                "Query": "select distinct `name` from unsharded",
+                "Table": "unsharded"
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # multi-shard union
 "select 1 from music union (select id from user union select name from unsharded)"
@@ -534,7 +728,63 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select 1 from music union (select id from user union select name from unsharded)",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from music where 1 != 1",
+            "Query": "select distinct 1 from music",
+            "Table": "music"
+          },
+          {
+            "OperatorType": "Distinct",
+            "Inputs": [
+              {
+                "OperatorType": "Concatenate",
+                "Inputs": [
+                  {
+                    "OperatorType": "Route",
+                    "Variant": "SelectScatter",
+                    "Keyspace": {
+                      "Name": "user",
+                      "Sharded": true
+                    },
+                    "FieldQuery": "select id from `user` where 1 != 1",
+                    "Query": "select distinct id from `user`",
+                    "Table": "`user`"
+                  },
+                  {
+                    "OperatorType": "Route",
+                    "Variant": "SelectUnsharded",
+                    "Keyspace": {
+                      "Name": "main",
+                      "Sharded": false
+                    },
+                    "FieldQuery": "select `name` from unsharded where 1 != 1",
+                    "Query": "select distinct `name` from unsharded",
+                    "Table": "unsharded"
+                  }
+                ]
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # union with the same target shard because of vindex
 "select * from music where id = 1 union select * from user where id = 1"
@@ -582,13 +832,9 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
-
-# union with different target shards
-"select 1 from music where id = 1 union select 1 from music where id = 2"
 {
   "QueryType": "SELECT",
-  "Original": "select 1 from music where id = 1 union select 1 from music where id = 2",
+  "Original": "select * from music where id = 1 union select * from user where id = 1",
   "Instructions": {
     "OperatorType": "Distinct",
     "Inputs": [
@@ -602,8 +848,8 @@ Gen4 plan same as above
               "Name": "user",
               "Sharded": true
             },
-            "FieldQuery": "select 1 from music where 1 != 1",
-            "Query": "select 1 from music where id = 1",
+            "FieldQuery": "select * from music where 1 != 1",
+            "Query": "select distinct * from music where id = 1",
             "Table": "music",
             "Values": [
               1
@@ -617,9 +863,100 @@ Gen4 plan same as above
               "Name": "user",
               "Sharded": true
             },
-            "FieldQuery": "select 1 from music where 1 != 1",
-            "Query": "select 1 from music where id = 2",
-            "Table": "music",
+            "FieldQuery": "select * from `user` where 1 != 1",
+            "Query": "select distinct * from `user` where id = 1",
+            "Table": "`user`",
+            "Values": [
+              1
+            ],
+            "Vindex": "user_index"
+          }
+        ]
+      }
+    ]
+  }
+}
+
+# union with different target shards
+"select 1 from music where id = 1 union select 1 from music where id = 2"
+{
+  "QueryType": "SELECT",
+  "Original": "select 1 from music where id = 1 union select 1 from music where id = 2",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectEqualUnique",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from music where 1 != 1",
+            "Query": "select 1 from music where id = 1",
+            "Table": "music",
+            "Values": [
+              1
+            ],
+            "Vindex": "music_user_map"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectEqualUnique",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from music where 1 != 1",
+            "Query": "select 1 from music where id = 2",
+            "Table": "music",
+            "Values": [
+              2
+            ],
+            "Vindex": "music_user_map"
+          }
+        ]
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select 1 from music where id = 1 union select 1 from music where id = 2",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectEqualUnique",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from music where 1 != 1",
+            "Query": "select distinct 1 from music where id = 1",
+            "Table": "music",
+            "Values": [
+              1
+            ],
+            "Vindex": "music_user_map"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectEqualUnique",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 1 from music where 1 != 1",
+            "Query": "select distinct 1 from music where id = 2",
+            "Table": "music",
             "Values": [
               2
             ],
@@ -630,7 +967,6 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
 
 # multiple select statement have inner order by with union - TODO (systay) no need to send down ORDER BY if we are going to loose it with UNION DISTINCT
 "(select 1 from user order by 1 desc) union (select 1 from user order by 1 asc)"
@@ -674,7 +1010,6 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
 
 # multiple unions
 "select 1 union select null union select 1.0 union select '1' union select 2 union select 2.0 from user"
@@ -714,7 +1049,6 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
 
 # union distinct between a scatter query and a join (other side)
 "(select user.id, user.name from user join user_extra where user_extra.extra = 'asdf') union select 'b','c' from user"
@@ -773,7 +1107,61 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "(select user.id, user.name from user join user_extra where user_extra.extra = 'asdf') union select 'b','c' from user",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "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`.`name` from `user` where 1 != 1",
+                "Query": "select `user`.id, `user`.`name` 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 where user_extra.extra = 'asdf'",
+                "Table": "user_extra"
+              }
+            ]
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 'b', 'c' from `user` where 1 != 1",
+            "Query": "select distinct 'b', 'c' from `user`",
+            "Table": "`user`"
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # union distinct between a scatter query and a join (other side)
 "select 'b','c' from user union (select user.id, user.name from user join user_extra where user_extra.extra = 'asdf')"
@@ -832,14 +1220,169 @@ Gen4 plan same as above
     ]
   }
 }
-Gen4 plan same as above
+{
+  "QueryType": "SELECT",
+  "Original": "select 'b','c' from user union (select user.id, user.name from user join user_extra where user_extra.extra = 'asdf')",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select 'b', 'c' from `user` where 1 != 1",
+            "Query": "select distinct 'b', 'c' from `user`",
+            "Table": "`user`"
+          },
+          {
+            "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`.`name` from `user` where 1 != 1",
+                "Query": "select `user`.id, `user`.`name` 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 where user_extra.extra = 'asdf'",
+                "Table": "user_extra"
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
+
+# unmergable because we are using aggregation
+"select count(*) as s from user union select count(*) as s from music"
+{
+  "QueryType": "SELECT",
+  "Original": "select count(*) as s from user union select count(*) as s from music",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Aggregate",
+            "Variant": "Ordered",
+            "Aggregates": "count(0)",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectScatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select count(*) as s from `user` where 1 != 1",
+                "Query": "select count(*) as s from `user`",
+                "Table": "`user`"
+              }
+            ]
+          },
+          {
+            "OperatorType": "Aggregate",
+            "Variant": "Ordered",
+            "Aggregates": "count(0)",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectScatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select count(*) as s from music where 1 != 1",
+                "Query": "select count(*) as s from music",
+                "Table": "music"
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select count(*) as s from user union select count(*) as s from music",
+  "Instructions": {
+    "OperatorType": "Distinct",
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Aggregate",
+            "Variant": "Ordered",
+            "Aggregates": "count(0) AS s",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectScatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select count(*) as s from `user` where 1 != 1",
+                "Query": "select count(*) as s from `user`",
+                "Table": "`user`"
+              }
+            ]
+          },
+          {
+            "OperatorType": "Aggregate",
+            "Variant": "Ordered",
+            "Aggregates": "count(0) AS s",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectScatter",
+                "Keyspace": {
+                  "Name": "user",
+                  "Sharded": true
+                },
+                "FieldQuery": "select count(*) as s from music where 1 != 1",
+                "Query": "select count(*) as s from music",
+                "Table": "music"
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
 
 # ambiguous LIMIT
 "select id from user limit 1 union all select id from music limit 1"
 "Incorrect usage of UNION and LIMIT - add parens to disambiguate your query (errno 1221) (sqlstate 21000)"
-Gen4 plan same as above
 
 # different number of columns
 "select id, 42 from user where id = 1 union all select id from user where id = 5"
 "The used SELECT statements have a different number of columns (errno 1222) (sqlstate 21000) during query: select id, 42 from `user` where id = 1 union all select id from `user` where id = 5"
-Gen4 plan same as above
diff --git a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
index 1cdf561574..0e2ee7b306 100644
--- a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
@@ -360,12 +360,10 @@ Gen4 plan same as above
 # order by inside and outside parenthesis select
 "(select 1 from user order by 1 desc) order by 1 asc limit 2"
 "can't do ORDER BY on top of ORDER BY"
-Gen4 plan same as above
 
 # ambiguous ORDER BY
 "select id from user order by id union all select id from music order by id desc"
 "Incorrect usage of UNION and ORDER BY - add parens to disambiguate your query (errno 1221) (sqlstate 21000)"
-Gen4 plan same as above
 
 # select get_lock with non-dual table
 "select get_lock('xyz', 10) from user"
@@ -385,7 +383,6 @@ Gen4 plan same as above
 # union with SQL_CALC_FOUND_ROWS 
 "(select sql_calc_found_rows id from user where id = 1 limit 1) union select id from user where id = 1"
 "SQL_CALC_FOUND_ROWS not supported with union"
-Gen4 plan same as above
 
 # set with DEFAULT - vitess aware
 "set workload = default"
diff --git a/go/vt/vtgate/semantics/analyzer_test.go b/go/vt/vtgate/semantics/analyzer_test.go
index 0164cd7d01..1a33793f94 100644
--- a/go/vt/vtgate/semantics/analyzer_test.go
+++ b/go/vt/vtgate/semantics/analyzer_test.go
@@ -351,6 +351,27 @@ func TestUnion(t *testing.T) {
 	assert.Equal(t, T2, d2)
 }
 
+func TestUnionWithOrderBy(t *testing.T) {
+	query := "select col1 from tabl1 union (select col2 from tabl2) order by 1"
+
+	stmt, semTable := parseAndAnalyze(t, query, "")
+	union, _ := stmt.(*sqlparser.Union)
+	sel1 := union.FirstStatement.(*sqlparser.Select)
+	sel2 := union.UnionSelects[0].Statement.(*sqlparser.ParenSelect).Select.(*sqlparser.Select)
+
+	t1 := sel1.From[0].(*sqlparser.AliasedTableExpr)
+	t2 := sel2.From[0].(*sqlparser.AliasedTableExpr)
+	ts1 := semTable.TableSetFor(t1)
+	ts2 := semTable.TableSetFor(t2)
+	assert.EqualValues(t, 1, ts1)
+	assert.EqualValues(t, 2, ts2)
+
+	d1 := semTable.BaseTableDependencies(extract(sel1, 0))
+	d2 := semTable.BaseTableDependencies(extract(sel2, 0))
+	assert.Equal(t, T1, d1)
+	assert.Equal(t, T2, d2)
+}
+
 func TestBindingMultiTable(t *testing.T) {
 	t.Run("positive tests", func(t *testing.T) {