Repository: vitessio/vitess
Pull request #8771 (2021-09-06 12:41:31)

Proper merge of the SysTableTableName fields when joining two routes
## Description

This pull request fixes an issue that is encountered when v3 joins two routes that have `SysTableTableName` fields. v3 was not merging the two maps resulting in errors like the one below:

```
ERROR 1105 (HY000) at line 1: target: keyspace1.0.primary: vttablet: rpc error: code = InvalidArgument desc = missing bind var table_name (CallerID: userData1)
```

A plan test was added through this pull request to reproduce the issue.

## Related Issue(s)

- https://github.com/planetscale/beta/discussions/91

## 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/vtgate/planbuilder/from.go b/go/vt/vtgate/planbuilder/from.go
index be78964a31..a9b9af044b 100644
--- a/go/vt/vtgate/planbuilder/from.go
+++ b/go/vt/vtgate/planbuilder/from.go
@@ -350,6 +350,14 @@ func (pb *primitiveBuilder) join(rpb *primitiveBuilder, ajoin *sqlparser.JoinTab
 		lRoute.eroute.TableName = strings.Join([]string{lRoute.eroute.TableName, rRoute.eroute.TableName}, ", ")
 	}
 
+	// join sysTableNames
+	for tableName, expr := range rRoute.eroute.SysTableTableName {
+		_, ok := lRoute.eroute.SysTableTableName[tableName]
+		if !ok {
+			lRoute.eroute.SysTableTableName[tableName] = expr
+		}
+	}
+
 	// Since the routes have merged, set st.singleRoute to point at
 	// the merged route.
 	pb.st.singleRoute = lRoute


Test patch:
diff --git a/go/vt/vtgate/planbuilder/testdata/from_cases.txt b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
index 7ab61ece1d..0dbd2316ad 100644
--- a/go/vt/vtgate/planbuilder/testdata/from_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
@@ -3954,3 +3954,22 @@ Gen4 plan same as above
     ]
   }
 }
+
+# join two routes with SysTableTableName entries in LHS and RHS
+"select a.table_name from (select * from information_schema.key_column_usage a where a.table_name = 'users') a join (select * from information_schema.referential_constraints where table_name = 'users') b"
+{
+  "QueryType": "SELECT",
+  "Original": "select a.table_name from (select * from information_schema.key_column_usage a where a.table_name = 'users') a join (select * from information_schema.referential_constraints where table_name = 'users') b",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectDBA",
+    "Keyspace": {
+      "Name": "main",
+      "Sharded": false
+    },
+    "FieldQuery": "select a.table_name from (select * from information_schema.key_column_usage as a where 1 != 1) as a join (select * from information_schema.referential_constraints where 1 != 1) as b where 1 != 1",
+    "Query": "select a.table_name from (select * from information_schema.key_column_usage as a where a.table_name = :a_table_name) as a join (select * from information_schema.referential_constraints where table_name = :table_name) as b",
+    "SysTableTableName": "[a_table_name:VARBINARY(\"users\"), table_name:VARBINARY(\"users\")]",
+    "Table": "information_schema.key_column_usage, information_schema.referential_constraints"
+  }
+}