Repository: vitessio/vitess
Pull request #8802 (2021-09-13 14:03:41)

Gen4: Merge duals
## Description

This pull request allows merging duals with other routes.

## Related Issue(s)

- #7280 

## Checklist
- [x] Tests were added or are not required
- [x] Documentation was added or is not required



Patch:
diff --git a/go/vt/vtgate/planbuilder/route_planning.go b/go/vt/vtgate/planbuilder/route_planning.go
index 75570d2944..4f65f38682 100644
--- a/go/vt/vtgate/planbuilder/route_planning.go
+++ b/go/vt/vtgate/planbuilder/route_planning.go
@@ -885,7 +885,7 @@ func tryMerge(ctx planningContext, a, b queryTree, joinPredicates []sqlparser.Ex
 
 	sameKeyspace := aRoute.keyspace == bRoute.keyspace
 
-	if sameKeyspace {
+	if sameKeyspace || (isDualTable(aRoute) || isDualTable(bRoute)) {
 		tree, err := tryMergeReferenceTable(aRoute, bRoute, merger)
 		if tree != nil || err != nil {
 			return tree, err
@@ -954,6 +954,21 @@ func tryMergeReferenceTable(aRoute *routeTree, bRoute *routeTree, merger mergeFu
 	return r, nil
 }
 
+func isDualTable(route *routeTree) bool {
+	if len(route.tables) != 1 || route.tables.tableNames()[0] != "dual" {
+		return false
+	}
+	table := route.tables[0]
+	routeTable, ok := table.(*routeTable)
+	if !ok {
+		return false
+	}
+	if routeTable.qtable.Table.Qualifier.IsEmpty() {
+		return true
+	}
+	return false
+}
+
 func makeRoute(j queryTree) *routeTree {
 	rb, ok := j.(*routeTree)
 	if ok {


Test patch:
diff --git a/go/vt/vtgate/planbuilder/testdata/select_cases.txt b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
index a24cb92e26..5ca1c8f295 100644
--- a/go/vt/vtgate/planbuilder/testdata/select_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/select_cases.txt
@@ -1890,3 +1890,126 @@ Gen4 plan same as above
     "Table": "dual"
   }
 }
+
+# Merging dual with user
+"select 42, id from dual, user"
+{
+  "QueryType": "SELECT",
+  "Original": "select 42, id from dual, user",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-1,1",
+    "TableName": "dual_`user`",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectReference",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select 42 from dual where 1 != 1",
+        "Query": "select 42 from dual",
+        "Table": "dual"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select id from `user` where 1 != 1",
+        "Query": "select id from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select 42, id from dual, user",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectScatter",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select 42, id from dual, `user` where 1 != 1",
+    "Query": "select 42, id from dual, `user`",
+    "Table": "`user`, dual"
+  }
+}
+
+# Table named "dual" with a qualifier joined on user should not be merged
+"select 42, user.id from main.dual, user"
+{
+  "QueryType": "SELECT",
+  "Original": "select 42, user.id from main.dual, user",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-1,1",
+    "TableName": "dual_`user`",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectReference",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select 42 from dual where 1 != 1",
+        "Query": "select 42 from dual",
+        "Table": "dual"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `user`.id from `user` where 1 != 1",
+        "Query": "select `user`.id from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}
+{
+  "QueryType": "SELECT",
+  "Original": "select 42, user.id from main.dual, user",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-1,1",
+    "TableName": "dual_`user`",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectReference",
+        "Keyspace": {
+          "Name": "main",
+          "Sharded": false
+        },
+        "FieldQuery": "select 42 from dual where 1 != 1",
+        "Query": "select 42 from dual",
+        "Table": "dual"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `user`.id from `user` where 1 != 1",
+        "Query": "select `user`.id from `user`",
+        "Table": "`user`"
+      }
+    ]
+  }
+}