Repository: vitessio/vitess
Pull request #9183 (2021-11-10 10:30:42)

Gen4: Improve merging of DBA queries


## Description
This PR adds support to merge DBA queries in Gen4 if they are being routed to the same keyspace

## Related Issue(s)
<!-- List related issues and pull requests. If this PR fixes an issue, please add it using Fixes #????  -->


## Checklist
- [ ] Should this PR be backported?
- [x] Tests were added or are not required
- [x] Documentation was added or is not required

## Deployment Notes
<!-- Notes regarding deployment of the contained body of work. These should note any db migrations, etc. -->


Patch:
diff --git a/GITHUB_SELF_HOSTED_RUNNERS.md b/GITHUB_SELF_HOSTED_RUNNERS.md
index 46cd87e5a6..9de22613b3 100644
--- a/GITHUB_SELF_HOSTED_RUNNERS.md
+++ b/GITHUB_SELF_HOSTED_RUNNERS.md
@@ -28,13 +28,12 @@ access to Vitess.
 
 ### Using a self-hosted runner to debug a flaky test
 You will need access to the self-hosted runner machine to be able to connect to it via SSH.
-1. From the output of the run on GitHub Actions, find the `Machine name` in the `Set up job` step 
+1. From the output of the run on GitHub Actions, find the `Machine name`
 2. Find that machine on the Equinix dashboard and connect to it via ssh
 3. From the output of the `Print Volume Used` step find the volume used
 4. From the output of the `Build Docker Image` step find the docker image built for this workflow
 5. On the machine run `docker run -d -v <volume-name>:/vt/vtdataroot <image-name> /bin/bash -c "sleep 600000000000"`
 6. On the terminal copy the docker id of the newly created container
-7. Now execute `docker exec -it <docker-id> /bin/bash` to go into the container and use the `/vt/vtdataroot` directory to find the output of the run along with the debug files
-8. Alternately, execute `docker cp <docker-id>:/vt/vtdataroot ./debugFiles/` to copy the files from the docker container to the servers local file system
-9. You can browse the files there or go a step further and download them locally via `scp`.
-10. Please remember to cleanup the folders created and remove the docker container via `docker stop <docker-id>`.
+7. Now execute `docker exec -it <docker-id> /bin/bash`
+8. Use the `/vt/vtdataroot` directory to find the output of the run along with the debug files
+
diff --git a/MAINTAINERS.md b/MAINTAINERS.md
index 7e19c348d0..460d6b251d 100644
--- a/MAINTAINERS.md
+++ b/MAINTAINERS.md
@@ -17,7 +17,7 @@ The following is the full list, alphabetically ordered.
 * Michael Demmer ([demmer](https://github.com/demmer)) mdemmer@slack-corp.com
 * Michael Pawliszyn ([mpawliszyn](https://github.com/mpawliszyn)) mikepaw@squareup.com
 * Paul Hemberger ([pH14](https://github.com/pH14)) phemberger@hubspot.com
-* Rafael Chacon ([rafael](https://github.com/rafael)) rchacon@figma.com
+* Rafael Chacon ([rafael](https://github.com/rafael)) rchacon@slack-corp.com
 * Rohit Nayak ([rohit-nayak-ps](https://github.com/rohit-nayak-ps)) rohit@planetscale.com
 * Sara Bee ([doeg](https://github.com/doeg)) sarab@slack-corp.com
 * Shlomi Noach ([shlomi-noach](https://github.com/shlomi-noach)) shlomi@planetscale.com
diff --git a/go/vt/vtgate/planbuilder/horizon_planning.go b/go/vt/vtgate/planbuilder/horizon_planning.go
index bc9809ca50..51380a9d32 100644
--- a/go/vt/vtgate/planbuilder/horizon_planning.go
+++ b/go/vt/vtgate/planbuilder/horizon_planning.go
@@ -316,6 +316,18 @@ func pushProjection(expr *sqlparser.AliasedExpr, plan logicalPlan, semTable *sem
 		}
 		node.cols = append(node.cols, column)
 		return len(node.cols) - 1, true, nil
+	case *concatenateGen4:
+		if hasAggregation {
+			return 0, false, vterrors.New(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: aggregation on unions")
+		}
+		offset, added, err := pushProjection(expr, node.sources[0], semTable, inner, reuseCol, hasAggregation)
+		if err != nil {
+			return 0, false, err
+		}
+		if added {
+			return 0, false, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "pushing projection %v on concatenate should reference an existing column", sqlparser.String(expr))
+		}
+		return offset, false, nil
 	default:
 		return 0, false, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "[BUG] push projection does not yet support: %T", node)
 	}
diff --git a/go/vt/vtgate/planbuilder/querytree_transformers.go b/go/vt/vtgate/planbuilder/querytree_transformers.go
index 2d066097fe..23cb752bf0 100644
--- a/go/vt/vtgate/planbuilder/querytree_transformers.go
+++ b/go/vt/vtgate/planbuilder/querytree_transformers.go
@@ -143,12 +143,23 @@ func mergeSubQueryPlan(ctx *planningContext, inner, outer logicalPlan, n *subque
 		// Instead of looking for it in the AST, we have a copy in the subquery tree that we can update
 		n.extracted.NeedsRewrite = true
 		replaceSubQuery(ctx, oroute.Select)
-
-		return oroute
+		return mergeSystemTableInformation(oroute, iroute)
 	}
 	return nil
 }
 
+// mergeSystemTableInformation copies over information from the second route to the first and appends to it
+func mergeSystemTableInformation(a *route, b *route) logicalPlan {
+	// safe to append system table schema and system table names, since either the routing will match or either side would be throwing an error
+	// during run-time which we want to preserve. For example outer side has User in sys table schema and inner side has User and Main in sys table schema
+	// Inner might end up throwing an error at runtime, but if it doesn't then it is safe to merge.
+	a.eroute.SysTableTableSchema = append(a.eroute.SysTableTableSchema, b.eroute.SysTableTableSchema...)
+	for k, v := range b.eroute.SysTableTableName {
+		a.eroute.SysTableTableName[k] = v
+	}
+	return a
+}
+
 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
@@ -311,7 +322,7 @@ func mergeUnionLogicalPlans(ctx *planningContext, left logicalPlan, right logica
 
 	if canMergeUnionPlans(ctx, lroute, rroute) {
 		lroute.Select = &sqlparser.Union{Left: lroute.Select, Distinct: false, Right: rroute.Select}
-		return lroute
+		return mergeSystemTableInformation(lroute, rroute)
 	}
 	return nil
 }
diff --git a/go/vt/vtgate/planbuilder/route_planning.go b/go/vt/vtgate/planbuilder/route_planning.go
index 28573ece7f..dcb104af3f 100644
--- a/go/vt/vtgate/planbuilder/route_planning.go
+++ b/go/vt/vtgate/planbuilder/route_planning.go
@@ -971,11 +971,7 @@ func canMergeUnionPlans(ctx *planningContext, a, b *route) bool {
 	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
+		return canSelectDBAMerge(a, b)
 	case engine.SelectEqualUnique:
 		// Check if they target the same shard.
 		if b.eroute.Opcode == engine.SelectEqualUnique &&
@@ -992,6 +988,7 @@ func canMergeUnionPlans(ctx *planningContext, a, b *route) bool {
 	}
 	return false
 }
+
 func canMergeSubqueryPlans(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 {
@@ -1001,11 +998,7 @@ func canMergeSubqueryPlans(ctx *planningContext, a, b *route) bool {
 	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
+		return canSelectDBAMerge(a, b)
 	case engine.SelectEqualUnique:
 		// Check if they target the same shard.
 		if b.eroute.Opcode == engine.SelectEqualUnique &&
@@ -1019,6 +1012,37 @@ func canMergeSubqueryPlans(ctx *planningContext, a, b *route) bool {
 	return false
 }
 
+func canSelectDBAMerge(a, b *route) bool {
+	if a.eroute.Opcode != engine.SelectDBA {
+		return false
+	}
+	if b.eroute.Opcode != engine.SelectDBA {
+		return false
+	}
+
+	// safe to merge when any 1 table name or schema matches, since either the routing will match or either side would be throwing an error
+	// during run-time which we want to preserve. For example outer side has User in sys table schema and inner side has User and Main in sys table schema
+	// Inner might end up throwing an error at runtime, but if it doesn't then it is safe to merge.
+	for _, aExpr := range a.eroute.SysTableTableSchema {
+		for _, bExpr := range b.eroute.SysTableTableSchema {
+			if aExpr.String() == bExpr.String() {
+				return true
+			}
+		}
+	}
+	for _, aExpr := range a.eroute.SysTableTableName {
+		for _, bExpr := range b.eroute.SysTableTableName {
+			if aExpr.String() == bExpr.String() {
+				return true
+			}
+		}
+	}
+
+	// if either/both of the side does not have any routing information, then they can be merged.
+	return (len(a.eroute.SysTableTableSchema) == 0 && len(a.eroute.SysTableTableName) == 0) ||
+		(len(b.eroute.SysTableTableSchema) == 0 && len(b.eroute.SysTableTableName) == 0)
+}
+
 func tryMerge(ctx *planningContext, a, b queryTree, joinPredicates []sqlparser.Expr, merger mergeFunc) (queryTree, error) {
 	aRoute, bRoute := queryTreesToRoutes(a.clone(), b.clone())
 	if aRoute == nil || bRoute == nil {
diff --git a/go/vt/vttablet/onlineddl/executor.go b/go/vt/vttablet/onlineddl/executor.go
index e294c428ec..98fd10acdb 100644
--- a/go/vt/vttablet/onlineddl/executor.go
+++ b/go/vt/vttablet/onlineddl/executor.go
@@ -761,9 +761,6 @@ func (e *Executor) initVreplicationOriginalMigration(ctx context.Context, online
 	}
 
 	vreplTableName := fmt.Sprintf("_%s_%s_vrepl", onlineDDL.UUID, ReadableTimestamp())
-	if err := e.updateArtifacts(ctx, onlineDDL.UUID, vreplTableName); err != nil {
-		return v, err
-	}
 	{
 		// Apply CREATE TABLE for materialized table
 		parsed := sqlparser.BuildParsedQuery(sqlCreateTableLike, vreplTableName, onlineDDL.Table)
@@ -830,9 +827,6 @@ func (e *Executor) initVreplicationRevertMigration(ctx context.Context, onlineDD
 		return nil, err
 	}
 
-	if err := e.updateArtifacts(ctx, onlineDDL.UUID, vreplTableName); err != nil {
-		return v, err
-	}
 	v = NewVRepl(onlineDDL.UUID, e.keyspace, e.shard, e.dbName, onlineDDL.Table, vreplTableName, "")
 	v.pos = revertStream.pos
 	return v, nil
@@ -891,6 +885,9 @@ func (e *Executor) ExecuteWithVReplication(ctx context.Context, onlineDDL *schem
 			return err
 		}
 	}
+	if err := e.updateArtifacts(ctx, onlineDDL.UUID, v.targetTable); err != nil {
+		return err
+	}
 
 	{
 		// We need to talk to tabletmanager's VREngine. But we're on TabletServer. While we live in the same


Test patch:
diff --git a/go/test/endtoend/onlineddl/vrepl/onlineddl_vrepl_test.go b/go/test/endtoend/onlineddl/vrepl/onlineddl_vrepl_test.go
index 9d517c1f94..253a5510b5 100644
--- a/go/test/endtoend/onlineddl/vrepl/onlineddl_vrepl_test.go
+++ b/go/test/endtoend/onlineddl/vrepl/onlineddl_vrepl_test.go
@@ -259,7 +259,6 @@ func TestSchemaChange(t *testing.T) {
 		testMigrationRowCount(t, uuid)
 		onlineddl.CheckCancelMigration(t, &vtParams, shards, uuid, false)
 		onlineddl.CheckRetryMigration(t, &vtParams, shards, uuid, false)
-		onlineddl.CheckMigrationArtifacts(t, &vtParams, shards, uuid, true)
 
 		rs := onlineddl.ReadMigrations(t, &vtParams, uuid)
 		require.NotNil(t, rs)
@@ -276,7 +275,6 @@ func TestSchemaChange(t *testing.T) {
 		testMigrationRowCount(t, uuid)
 		onlineddl.CheckCancelMigration(t, &vtParams, shards, uuid, false)
 		onlineddl.CheckRetryMigration(t, &vtParams, shards, uuid, false)
-		onlineddl.CheckMigrationArtifacts(t, &vtParams, shards, uuid, true)
 	})
 	t.Run("throttled migration", func(t *testing.T) {
 		insertRows(t, 2)
@@ -321,7 +319,6 @@ func TestSchemaChange(t *testing.T) {
 		testRows(t)
 		onlineddl.CheckCancelMigration(t, &vtParams, shards, uuid, false)
 		onlineddl.CheckRetryMigration(t, &vtParams, shards, uuid, true)
-		onlineddl.CheckMigrationArtifacts(t, &vtParams, shards, uuid, true)
 		// migration will fail again
 	})
 	t.Run("cancel all migrations: nothing to cancel", func(t *testing.T) {
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases.txt b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
index 55aa77d7ed..d2ad9dfddc 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
@@ -2730,3 +2730,46 @@ Gen4 plan same as above
 "select 1 from user u where u.col = 6 or exists (select 1 from user_extra ue where ue.col = u.col and u.col = ue.col2)"
 "unsupported: cross-shard correlated subquery"
 Gen4 error: exists sub-queries are only supported with AND clause
+
+# union as a derived table
+"select found from (select id as found from user union all (select id from unsharded)) as t"
+{
+  "QueryType": "SELECT",
+  "Original": "select found from (select id as found from user union all (select id from unsharded)) as t",
+  "Instructions": {
+    "OperatorType": "SimpleProjection",
+    "Columns": [
+      0
+    ],
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectScatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select id as found from `user` where 1 != 1",
+            "Query": "select id as found from `user`",
+            "Table": "`user`"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectUnsharded",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select id from unsharded where 1 != 1",
+            "Query": "select id from unsharded",
+            "Table": "unsharded"
+          }
+        ]
+      }
+    ]
+  }
+}
+Gen4 plan same as above
diff --git a/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt b/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
index 3dd81c4a03..886b653508 100644
--- a/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/systemtables_cases.txt
@@ -1013,3 +1013,401 @@ Gen4 plan same as above
     "Table": "information_schema.key_column_usage, information_schema.referential_constraints"
   }
 }
+
+"select sum(found) from (select 1 as found from information_schema.`tables` where table_schema = 'music' union all (select 1 as found from information_schema.views where table_schema = 'music' limit 1)) as t"
+"unsupported: cross-shard query with aggregates"
+{
+  "QueryType": "SELECT",
+  "Original": "select sum(found) from (select 1 as found from information_schema.`tables` where table_schema = 'music' union all (select 1 as found from information_schema.views where table_schema = 'music' limit 1)) as t",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectDBA",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select sum(found) from (select 1 as found from information_schema.`tables` where 1 != 1 union all (select 1 as found from information_schema.views where 1 != 1)) as t where 1 != 1",
+    "Query": "select sum(found) from (select 1 as found from information_schema.`tables` where table_schema = :__vtschemaname union all (select 1 as found from information_schema.views where table_schema = :__vtschemaname limit 1)) as t",
+    "SysTableTableSchema": "[VARBINARY(\"music\"), VARBINARY(\"music\")]",
+    "Table": "information_schema.`tables`"
+  }
+}
+
+# union as a derived table
+"select found from (select 1 as found from information_schema.`tables` where table_schema = 'music' union all (select 1 as found from information_schema.views where table_schema = 'music' limit 1)) as t"
+{
+  "QueryType": "SELECT",
+  "Original": "select found from (select 1 as found from information_schema.`tables` where table_schema = 'music' union all (select 1 as found from information_schema.views where table_schema = 'music' limit 1)) as t",
+  "Instructions": {
+    "OperatorType": "SimpleProjection",
+    "Columns": [
+      0
+    ],
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select 1 as found from information_schema.`tables` where 1 != 1",
+            "Query": "select 1 as found from information_schema.`tables` where table_schema = :__vtschemaname",
+            "SysTableTableSchema": "[VARBINARY(\"music\")]",
+            "Table": "information_schema.`tables`"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select 1 as found from information_schema.views where 1 != 1",
+            "Query": "select 1 as found from information_schema.views where table_schema = :__vtschemaname limit 1",
+            "SysTableTableSchema": "[VARBINARY(\"music\")]",
+            "Table": "information_schema.views"
+          }
+        ]
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select found from (select 1 as found from information_schema.`tables` where table_schema = 'music' union all (select 1 as found from information_schema.views where table_schema = 'music' limit 1)) as t",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectDBA",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select found from (select 1 as found from information_schema.`tables` where 1 != 1 union all (select 1 as found from information_schema.views where 1 != 1)) as t where 1 != 1",
+    "Query": "select found from (select 1 as found from information_schema.`tables` where table_schema = :__vtschemaname union all (select 1 as found from information_schema.views where table_schema = :__vtschemaname limit 1)) as t",
+    "SysTableTableSchema": "[VARBINARY(\"music\"), VARBINARY(\"music\")]",
+    "Table": "information_schema.`tables`"
+  }
+}
+
+# merge system schema queries as long as they have any same table_schema
+"select 1 as found from information_schema.`tables` where table_schema = 'music' and table_schema = 'Music' union all (select 1 as found from information_schema.views where table_schema = 'music' and table_schema = 'user' limit 1)"
+{
+  "QueryType": "SELECT",
+  "Original": "select 1 as found from information_schema.`tables` where table_schema = 'music' and table_schema = 'Music' union all (select 1 as found from information_schema.views where table_schema = 'music' and table_schema = 'user' limit 1)",
+  "Instructions": {
+    "OperatorType": "Concatenate",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectDBA",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select 1 as found from information_schema.`tables` where 1 != 1",
+        "Query": "select 1 as found from information_schema.`tables` where table_schema = :__vtschemaname and table_schema = :__vtschemaname",
+        "SysTableTableSchema": "[VARBINARY(\"music\"), VARBINARY(\"Music\")]",
+        "Table": "information_schema.`tables`"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectDBA",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select 1 as found from information_schema.views where 1 != 1",
+        "Query": "select 1 as found from information_schema.views where table_schema = :__vtschemaname and table_schema = :__vtschemaname limit 1",
+        "SysTableTableSchema": "[VARBINARY(\"music\"), VARBINARY(\"user\")]",
+        "Table": "information_schema.views"
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select 1 as found from information_schema.`tables` where table_schema = 'music' and table_schema = 'Music' union all (select 1 as found from information_schema.views where table_schema = 'music' and table_schema = 'user' limit 1)",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectDBA",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select 1 as found from information_schema.`tables` where 1 != 1 union all (select 1 as found from information_schema.views where 1 != 1)",
+    "Query": "select 1 as found from information_schema.`tables` where table_schema = :__vtschemaname union all (select 1 as found from information_schema.views where table_schema = :__vtschemaname limit 1)",
+    "SysTableTableSchema": "[VARBINARY(\"music\"), VARBINARY(\"Music\"), VARBINARY(\"music\"), VARBINARY(\"user\")]",
+    "Table": "information_schema.`tables`"
+  }
+}
+
+# merge system schema queries as long as they have any same table_name
+"select 1 as found from information_schema.`tables` where table_schema = 'music' and table_schema = 'Music' union all (select 1 as found from information_schema.views where table_schema = 'music' and table_schema = 'user' limit 1)"
+{
+  "QueryType": "SELECT",
+  "Original": "select 1 as found from information_schema.`tables` where table_schema = 'music' and table_schema = 'Music' union all (select 1 as found from information_schema.views where table_schema = 'music' and table_schema = 'user' limit 1)",
+  "Instructions": {
+    "OperatorType": "Concatenate",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectDBA",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select 1 as found from information_schema.`tables` where 1 != 1",
+        "Query": "select 1 as found from information_schema.`tables` where table_schema = :__vtschemaname and table_schema = :__vtschemaname",
+        "SysTableTableSchema": "[VARBINARY(\"music\"), VARBINARY(\"Music\")]",
+        "Table": "information_schema.`tables`"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectDBA",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select 1 as found from information_schema.views where 1 != 1",
+        "Query": "select 1 as found from information_schema.views where table_schema = :__vtschemaname and table_schema = :__vtschemaname limit 1",
+        "SysTableTableSchema": "[VARBINARY(\"music\"), VARBINARY(\"user\")]",
+        "Table": "information_schema.views"
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select 1 as found from information_schema.`tables` where table_schema = 'music' and table_schema = 'Music' union all (select 1 as found from information_schema.views where table_schema = 'music' and table_schema = 'user' limit 1)",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectDBA",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select 1 as found from information_schema.`tables` where 1 != 1 union all (select 1 as found from information_schema.views where 1 != 1)",
+    "Query": "select 1 as found from information_schema.`tables` where table_schema = :__vtschemaname union all (select 1 as found from information_schema.views where table_schema = :__vtschemaname limit 1)",
+    "SysTableTableSchema": "[VARBINARY(\"music\"), VARBINARY(\"Music\"), VARBINARY(\"music\"), VARBINARY(\"user\")]",
+    "Table": "information_schema.`tables`"
+  }
+}
+
+# merge union subquery with outer query referencing the same system schemas
+"select 1 as found from information_schema.`tables` where table_name = 'music' and table_name = 'Music' and exists (select 1 as found from information_schema.`tables` where table_name = 'music' and table_name = 'Music' union all (select 1 as found from information_schema.views where table_name = 'music' and table_name = 'user' limit 1))"
+{
+  "QueryType": "SELECT",
+  "Original": "select 1 as found from information_schema.`tables` where table_name = 'music' and table_name = 'Music' and exists (select 1 as found from information_schema.`tables` where table_name = 'music' and table_name = 'Music' union all (select 1 as found from information_schema.views where table_name = 'music' and table_name = 'user' limit 1))",
+  "Instructions": {
+    "OperatorType": "Subquery",
+    "Variant": "PulloutExists",
+    "PulloutVars": [
+      "__sq_has_values1",
+      "__sq1"
+    ],
+    "Inputs": [
+      {
+        "OperatorType": "Concatenate",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select 1 as found from information_schema.`tables` where 1 != 1",
+            "Query": "select 1 as found from information_schema.`tables` where table_name = :table_name2 and table_name = :table_name3",
+            "SysTableTableName": "[table_name2:VARBINARY(\"music\"), table_name3:VARBINARY(\"Music\")]",
+            "Table": "information_schema.`tables`"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "SelectDBA",
+            "Keyspace": {
+              "Name": "main",
+              "Sharded": false
+            },
+            "FieldQuery": "select 1 as found from information_schema.views where 1 != 1",
+            "Query": "select 1 as found from information_schema.views where table_name = :table_name4 and table_name = :table_name5 limit 1",
+            "SysTableTableName": "[table_name4:VARBINARY(\"music\"), table_name5:VARBINARY(\"user\")]",
+            "Table": "information_schema.views"
+          }
+        ]
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectDBA",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select 1 as found from information_schema.`tables` where 1 != 1",
+        "Query": "select 1 as found from information_schema.`tables` where table_name = :table_name and table_name = :table_name1 and :__sq_has_values1",
+        "SysTableTableName": "[table_name1:VARBINARY(\"Music\"), table_name:VARBINARY(\"music\")]",
+        "Table": "information_schema.`tables`"
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select 1 as found from information_schema.`tables` where table_name = 'music' and table_name = 'Music' and exists (select 1 as found from information_schema.`tables` where table_name = 'music' and table_name = 'Music' union all (select 1 as found from information_schema.views where table_name = 'music' and table_name = 'user' limit 1))",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectDBA",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select 1 as found from information_schema.`tables` where 1 != 1",
+    "Query": "select 1 as found from information_schema.`tables` where table_name = :table_name and table_name = :table_name1 and exists (select 1 as found from information_schema.`tables` where table_name = :table_name2 and table_name = :table_name3 union all (select 1 as found from information_schema.views where table_name = :table_name4 and table_name = :table_name5 limit 1))",
+    "SysTableTableName": "[table_name1:VARBINARY(\"Music\"), table_name2:VARBINARY(\"music\"), table_name3:VARBINARY(\"Music\"), table_name4:VARBINARY(\"music\"), table_name5:VARBINARY(\"user\"), table_name:VARBINARY(\"music\")]",
+    "Table": "information_schema.`tables`"
+  }
+}
+
+# merge even one side have schema name in derived table
+"select id from (select id from information_schema.table t where t.schema_name = 'a' union select id from information_schema.columns) dt"
+{
+  "QueryType": "SELECT",
+  "Original": "select id from (select id from information_schema.table t where t.schema_name = 'a' union select id from information_schema.columns) dt",
+  "Instructions": {
+    "OperatorType": "SimpleProjection",
+    "Columns": [
+      0
+    ],
+    "Inputs": [
+      {
+        "OperatorType": "Distinct",
+        "Inputs": [
+          {
+            "OperatorType": "Concatenate",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectDBA",
+                "Keyspace": {
+                  "Name": "main",
+                  "Sharded": false
+                },
+                "FieldQuery": "select id from information_schema.`table` as t where 1 != 1",
+                "Query": "select id from information_schema.`table` as t where t.schema_name = :__vtschemaname",
+                "SysTableTableSchema": "[VARBINARY(\"a\")]",
+                "Table": "information_schema.`table`"
+              },
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectDBA",
+                "Keyspace": {
+                  "Name": "main",
+                  "Sharded": false
+                },
+                "FieldQuery": "select id from information_schema.`columns` where 1 != 1",
+                "Query": "select id from information_schema.`columns`",
+                "Table": "information_schema.`columns`"
+              }
+            ]
+          }
+        ]
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select id from (select id from information_schema.table t where t.schema_name = 'a' union select id from information_schema.columns) dt",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectDBA",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select id from (select id from information_schema.`table` as t where 1 != 1 union select id from information_schema.`columns` where 1 != 1) as dt where 1 != 1",
+    "Query": "select id from (select id from information_schema.`table` as t where t.schema_name = :__vtschemaname union select id from information_schema.`columns`) as dt",
+    "SysTableTableSchema": "[VARBINARY(\"a\")]",
+    "Table": "information_schema.`table`"
+  }
+}
+
+# merge even one side have schema name in subquery
+"select id from information_schema.random t where t.col in (select id from information_schema.table t where t.schema_name = 'a' union select id from information_schema.columns)"
+{
+  "QueryType": "SELECT",
+  "Original": "select id from information_schema.random t where t.col in (select id from information_schema.table t where t.schema_name = 'a' union select id from information_schema.columns)",
+  "Instructions": {
+    "OperatorType": "Subquery",
+    "Variant": "PulloutIn",
+    "PulloutVars": [
+      "__sq_has_values1",
+      "__sq1"
+    ],
+    "Inputs": [
+      {
+        "OperatorType": "Distinct",
+        "Inputs": [
+          {
+            "OperatorType": "Concatenate",
+            "Inputs": [
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectDBA",
+                "Keyspace": {
+                  "Name": "main",
+                  "Sharded": false
+                },
+                "FieldQuery": "select id from information_schema.`table` as t where 1 != 1",
+                "Query": "select id from information_schema.`table` as t where t.schema_name = :__vtschemaname",
+                "SysTableTableSchema": "[VARBINARY(\"a\")]",
+                "Table": "information_schema.`table`"
+              },
+              {
+                "OperatorType": "Route",
+                "Variant": "SelectDBA",
+                "Keyspace": {
+                  "Name": "main",
+                  "Sharded": false
+                },
+                "FieldQuery": "select id from information_schema.`columns` where 1 != 1",
+                "Query": "select id from information_schema.`columns`",
+                "Table": "information_schema.`columns`"
+              }
+            ]
+          }
+        ]
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectDBA",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select id from information_schema.random as t where 1 != 1",
+        "Query": "select id from information_schema.random as t where :__sq_has_values1 = 1 and t.col in ::__sq1",
+        "Table": "information_schema.random"
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select id from information_schema.random t where t.col in (select id from information_schema.table t where t.schema_name = 'a' union select id from information_schema.columns)",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectDBA",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select id from information_schema.random as t where 1 != 1",
+    "Query": "select id from information_schema.random as t where t.col in (select id from information_schema.`table` as t where t.schema_name = :__vtschemaname union select id from information_schema.`columns`)",
+    "SysTableTableSchema": "[VARBINARY(\"a\")]",
+    "Table": "information_schema.random"
+  }
+}
diff --git a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
index 8a558850ac..cde4f7c374 100644
--- a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
@@ -632,3 +632,8 @@ Gen4 error: expr cannot be converted, not supported: hexadecimal value: 0x01
 "select distinct a+1 from user"
 "generating order by clause: cannot reference a complex expression"
 Gen4 error: unsupported: in scatter query: complex order by expression: a + 1
+
+# aggregation on union
+"select sum(col) from (select col from user union all select col from unsharded) t"
+"unsupported: cross-shard query with aggregates"
+Gen4 error: unsupported: aggregation on unions