Repository: vitessio/vitess
Pull request #10034 (2022-04-04 17:42:43)

feat: allow more complex expressions to be used in outer queries
## Description
Complex expressions on top of outer joins were not allowed to pass through the planner. The runtime does not seem to have any issues with these queries, so the planner check was probably no longer valid. This PR adds a few tests and removes the limitation in the planner.

## Checklist
- [x] Should this PR be backported? *no!*
- [x] Tests were added or are not required
- [x] Documentation was added or is not required



Patch:
diff --git a/go/vt/vtgate/planbuilder/horizon_planning.go b/go/vt/vtgate/planbuilder/horizon_planning.go
index c331170c66..e86ebba596 100644
--- a/go/vt/vtgate/planbuilder/horizon_planning.go
+++ b/go/vt/vtgate/planbuilder/horizon_planning.go
@@ -18,7 +18,6 @@ package planbuilder
 
 import (
 	"vitess.io/vitess/go/sqltypes"
-	"vitess.io/vitess/go/vt/vtgate/evalengine"
 	"vitess.io/vitess/go/vt/vtgate/planbuilder/abstract"
 	"vitess.io/vitess/go/vt/vtgate/planbuilder/physical"
 	"vitess.io/vitess/go/vt/vtgate/planbuilder/plancontext"
@@ -163,17 +162,6 @@ func pushProjection(
 ) (offset int, added bool, err error) {
 	switch node := plan.(type) {
 	case *routeGen4:
-		_, isColName := expr.Expr.(*sqlparser.ColName)
-		if !isColName {
-			_, err := evalengine.Translate(expr.Expr, ctx.SemTable)
-			if err != nil {
-				if vterrors.Code(err) != vtrpcpb.Code_UNIMPLEMENTED {
-					return 0, false, err
-				} else if !inner {
-					return 0, false, vterrors.New(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: cross-shard left join and column expressions")
-				}
-			}
-		}
 		return addExpressionToRoute(ctx, node, expr, reuseCol)
 	case *hashJoin:
 		lhsSolves := node.Left.ContainsTables()


Test patch:
diff --git a/go/test/endtoend/vtgate/gen4/gen4_test.go b/go/test/endtoend/vtgate/gen4/gen4_test.go
index 97b74d3db9..83e8e2718f 100644
--- a/go/test/endtoend/vtgate/gen4/gen4_test.go
+++ b/go/test/endtoend/vtgate/gen4/gen4_test.go
@@ -443,3 +443,29 @@ func TestSubShardVindexDML(t *testing.T) {
 		})
 	}
 }
+
+func TestOuterJoin(t *testing.T) {
+	ctx := context.Background()
+	conn, err := mysql.Connect(ctx, &vtParams)
+	require.NoError(t, err)
+	defer conn.Close()
+
+	_, _ = utils.ExecAllowError(t, conn, `delete from t1`)
+	_, _ = utils.ExecAllowError(t, conn, `delete from t2`)
+	defer func() {
+		_, _ = utils.ExecAllowError(t, conn, `delete from t1`)
+		_, _ = utils.ExecAllowError(t, conn, `delete from t2`)
+	}()
+
+	// insert some data.
+	utils.Exec(t, conn, `insert into t1(id, col) values (100, 123), (10, 123), (1, 13), (1000, 1234)`)
+	utils.Exec(t, conn, `insert into t2(id, tcol1, tcol2) values (12, 13, 1),(123, 7, 15),(1, 123, 123),(1004, 134, 123)`)
+
+	// Gen4 only supported query.
+	utils.AssertMatchesNoOrder(t, conn, `select t1.id, t2.tcol1+t2.tcol2 from t1 left join t2 on t1.col = t2.id`, `[[INT64(10) FLOAT64(22)] [INT64(1) NULL] [INT64(100) FLOAT64(22)] [INT64(1000) NULL]]`)
+	utils.AssertMatchesNoOrder(t, conn, `select t1.id, t2.id, t2.tcol1+t1.col+t2.tcol2 from t1 left join t2 on t1.col = t2.id`,
+		`[[INT64(10) INT64(123) FLOAT64(145)]`+
+			` [INT64(1) NULL NULL]`+
+			` [INT64(100) INT64(123) FLOAT64(145)]`+
+			` [INT64(1000) NULL NULL]]`)
+}
diff --git a/go/vt/vtgate/planbuilder/testdata/from_cases.txt b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
index 85e63a7622..dfcec709b6 100644
--- a/go/vt/vtgate/planbuilder/testdata/from_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/from_cases.txt
@@ -4471,3 +4471,146 @@ Gen4 plan same as above
   }
 }
 Gen4 plan same as above
+
+# left join with expressions
+"select user_extra.col+1 from user left join user_extra on user.col = user_extra.col"
+{
+  "QueryType": "SELECT",
+  "Original": "select user_extra.col+1 from user left join user_extra on user.col = user_extra.col",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "LeftJoin",
+    "JoinColumnIndexes": "R:0",
+    "JoinVars": {
+      "user_col": 0
+    },
+    "TableName": "`user`_user_extra",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `user`.col from `user` where 1 != 1",
+        "Query": "select `user`.col from `user`",
+        "Table": "`user`"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select user_extra.col + 1 from user_extra where 1 != 1",
+        "Query": "select user_extra.col + 1 from user_extra where user_extra.col = :user_col",
+        "Table": "user_extra"
+      }
+    ]
+  }
+}
+Gen4 plan same as above
+
+# left join with expressions, with three-way join (different code path)
+"select user.id, user_extra.col+1 from user left join user_extra on user.col = user_extra.col join user_extra e"
+{
+  "QueryType": "SELECT",
+  "Original": "select user.id, user_extra.col+1 from user left join user_extra on user.col = user_extra.col join user_extra e",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "L:0,L:1",
+    "TableName": "`user`_user_extra_user_extra",
+    "Inputs": [
+      {
+        "OperatorType": "Join",
+        "Variant": "LeftJoin",
+        "JoinColumnIndexes": "L:1,R:0",
+        "JoinVars": {
+          "user_col": 0
+        },
+        "TableName": "`user`_user_extra",
+        "Inputs": [
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select `user`.col, `user`.id from `user` where 1 != 1",
+            "Query": "select `user`.col, `user`.id from `user`",
+            "Table": "`user`"
+          },
+          {
+            "OperatorType": "Route",
+            "Variant": "Scatter",
+            "Keyspace": {
+              "Name": "user",
+              "Sharded": true
+            },
+            "FieldQuery": "select user_extra.col + 1 from user_extra where 1 != 1",
+            "Query": "select user_extra.col + 1 from user_extra where user_extra.col = :user_col",
+            "Table": "user_extra"
+          }
+        ]
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select 1 from user_extra as e where 1 != 1",
+        "Query": "select 1 from user_extra as e",
+        "Table": "user_extra"
+      }
+    ]
+  }
+}
+Gen4 plan same as above
+
+# left join with expressions coming from both sides
+"select user.foo+user_extra.col+1 from user left join user_extra on user.col = user_extra.col"
+{
+  "QueryType": "SELECT",
+  "Original": "select user.foo+user_extra.col+1 from user left join user_extra on user.col = user_extra.col",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "LeftJoin",
+    "JoinColumnIndexes": "R:0",
+    "JoinVars": {
+      "user_col": 0,
+      "user_foo": 1
+    },
+    "TableName": "`user`_user_extra",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select `user`.col, `user`.foo from `user` where 1 != 1",
+        "Query": "select `user`.col, `user`.foo from `user`",
+        "Table": "`user`"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "Scatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select :user_foo + user_extra.col + 1 from user_extra where 1 != 1",
+        "Query": "select :user_foo + user_extra.col + 1 from user_extra where user_extra.col = :user_col",
+        "Table": "user_extra"
+      }
+    ]
+  }
+}
+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 e71fb4d6df..50457d0ef1 100644
--- a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
@@ -58,16 +58,6 @@ Gen4 plan same as above
 "unsupported: natural right join"
 Gen4 plan same as above
 
-# left join with expressions
-"select user_extra.col+1 from user left join user_extra on user.col = user_extra.col"
-"unsupported: cross-shard left join and column expressions"
-Gen4 plan same as above
-
-# left join with expressions, with three-way join (different code path)
-"select user.id, user_extra.col+1 from user left join user_extra on user.col = user_extra.col join user_extra e"
-"unsupported: cross-shard left join and column expressions"
-Gen4 plan same as above
-
 # * expresson not allowed for cross-shard joins
 "select * from user join user_extra"
 "unsupported: '*' expression in cross-shard query"