Repository: vitessio/vitess
Pull request #7683 (2021-03-15 13:05:47)

[9.0] fix regression - should be able to plan subquery on top of subquery
## Description

During a refactoring of the planner code in the V9.0.0 release, this query type was broken. This restored the capability to plan IN sub queries on top of other IN sub queries.

## Checklist
- [x] Should this PR be backported? `No, this is the backport`
- [x] Tests were added or are not required  `Added`

## Impacted Areas in Vitess
Components that this PR will affect:

- [x]  Query Serving

(This is a backport of #7682)


Patch:
diff --git a/.github/workflows/check_formatting.yml b/.github/workflows/check_formatting.yml
index 3f2334dd4b..9c64b18ced 100644
--- a/.github/workflows/check_formatting.yml
+++ b/.github/workflows/check_formatting.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: Check Formatting
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     steps:
 
     - name: Set up Go
diff --git a/.github/workflows/check_make_parser.yml b/.github/workflows/check_make_parser.yml
index 805cf2fe49..c856da3e58 100644
--- a/.github/workflows/check_make_parser.yml
+++ b/.github/workflows/check_make_parser.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: Check Make Parser
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     steps:
 
     - name: Set up Go
diff --git a/.github/workflows/check_make_visitor.yml b/.github/workflows/check_make_visitor.yml
index 219e1ece2d..b72fbbaf51 100644
--- a/.github/workflows/check_make_visitor.yml
+++ b/.github/workflows/check_make_visitor.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: Check Make Visitor
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     steps:
 
     - name: Set up Go
diff --git a/.github/workflows/cluster_endtoend.yml b/.github/workflows/cluster_endtoend.yml
index 60edd6e340..4ec4671d62 100644
--- a/.github/workflows/cluster_endtoend.yml
+++ b/.github/workflows/cluster_endtoend.yml
@@ -3,7 +3,7 @@ on: [push, pull_request]
 jobs:
 
   build:
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     strategy:
       matrix:
         name: [11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 26, 27]
diff --git a/.github/workflows/cluster_endtoend_upgrade.yml b/.github/workflows/cluster_endtoend_upgrade.yml
index 185b9fbbe0..e7ad865383 100644
--- a/.github/workflows/cluster_endtoend_upgrade.yml
+++ b/.github/workflows/cluster_endtoend_upgrade.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: Run endtoend tests on Cluster (upgrade)
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
 
     steps:
     - name: Set up Go
diff --git a/.github/workflows/cluster_initial_sharding_multi.yml b/.github/workflows/cluster_initial_sharding_multi.yml
index 147b8e7e1c..494dfca2bb 100644
--- a/.github/workflows/cluster_initial_sharding_multi.yml
+++ b/.github/workflows/cluster_initial_sharding_multi.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: cluster initial sharding multi
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     steps:
 
     - name: Set up Go
diff --git a/.github/workflows/create_release.yml b/.github/workflows/create_release.yml
index 85a4d098d5..f126dd76e3 100644
--- a/.github/workflows/create_release.yml
+++ b/.github/workflows/create_release.yml
@@ -10,7 +10,7 @@ on:
 jobs:
   build:
     name: Create Release
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     steps:
 
     - name: Set up Go
diff --git a/.github/workflows/endtoend.yml b/.github/workflows/endtoend.yml
index 026e8f1f4e..8be2dbb317 100644
--- a/.github/workflows/endtoend.yml
+++ b/.github/workflows/endtoend.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: End-to-End Test
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     steps:
 
     - name: Set up Go
diff --git a/.github/workflows/ensure_bootstrap_updated.yml b/.github/workflows/ensure_bootstrap_updated.yml
index 3ec8261c9b..6bb1af2eb7 100644
--- a/.github/workflows/ensure_bootstrap_updated.yml
+++ b/.github/workflows/ensure_bootstrap_updated.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: Check Bootstrap Updated
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     steps:
 
     - name: Set up Go
diff --git a/.github/workflows/golangci-linter.yml b/.github/workflows/golangci-linter.yml
index b91b8d06a4..bc6799331f 100644
--- a/.github/workflows/golangci-linter.yml
+++ b/.github/workflows/golangci-linter.yml
@@ -3,7 +3,7 @@ on: [push, pull_request]
 jobs:
   build:
     name: Build
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     steps:
       - name: Set up Go 1.15
         uses: actions/setup-go@v1
diff --git a/.github/workflows/sonar_analysis.yml b/.github/workflows/sonar_analysis.yml
index 3381d93b22..af59daa03b 100644
--- a/.github/workflows/sonar_analysis.yml
+++ b/.github/workflows/sonar_analysis.yml
@@ -6,7 +6,7 @@ on:
 jobs:
 
   build:
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
 
     steps:
       - name: Set up Go
diff --git a/.github/workflows/unit.yml b/.github/workflows/unit.yml
index ba58c5af40..e40b4921ab 100644
--- a/.github/workflows/unit.yml
+++ b/.github/workflows/unit.yml
@@ -3,7 +3,7 @@ on: [push, pull_request]
 jobs:
 
   build:
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     strategy:
       matrix:
         name: [percona56, mysql57, mysql80, mariadb101, mariadb102, mariadb103]
diff --git a/.github/workflows/unit_race.yml b/.github/workflows/unit_race.yml
index 7e71449de8..d280afe62a 100644
--- a/.github/workflows/unit_race.yml
+++ b/.github/workflows/unit_race.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: Unit Test (Race)
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     steps:
 
     - name: Set up Go
diff --git a/go/vt/vtgate/planbuilder/filtering.go b/go/vt/vtgate/planbuilder/filtering.go
index 6193f66b5e..4e22a3cdbc 100644
--- a/go/vt/vtgate/planbuilder/filtering.go
+++ b/go/vt/vtgate/planbuilder/filtering.go
@@ -63,6 +63,8 @@ func planFilter(pb *primitiveBuilder, input logicalPlan, filter sqlparser.Expr,
 		}
 		node.UpdatePlan(pb, filter)
 		return node, nil
+	case *pulloutSubquery:
+		return planFilter(pb, node.underlying, filter, whereType, origin)
 	case *vindexFunc:
 		return filterVindexFunc(node, filter)
 	case *subquery:


Test patch:
diff --git a/.github/workflows/docker_test_1.yml b/.github/workflows/docker_test_1.yml
index f15e3a3f9b..11a53e1a48 100644
--- a/.github/workflows/docker_test_1.yml
+++ b/.github/workflows/docker_test_1.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: Docker Test 1
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
 
     steps:
 
@@ -18,4 +18,4 @@ jobs:
 
     - name: Run tests which require docker - 1
       run: |
-        go run test.go -docker=true --follow -shard 10
\ No newline at end of file
+        go run test.go -docker=true --follow -shard 10
diff --git a/.github/workflows/docker_test_2.yml b/.github/workflows/docker_test_2.yml
index 84ea569d5d..46d04889bb 100644
--- a/.github/workflows/docker_test_2.yml
+++ b/.github/workflows/docker_test_2.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: Docker Test 2
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
 
     steps:
 
@@ -18,4 +18,4 @@ jobs:
 
     - name: Run tests which require docker - 2
       run: |
-        go run test.go -docker=true --follow -shard 25
\ No newline at end of file
+        go run test.go -docker=true --follow -shard 25
diff --git a/.github/workflows/docker_test_3.yml b/.github/workflows/docker_test_3.yml
index 92c9e0cbe5..6cc880e992 100644
--- a/.github/workflows/docker_test_3.yml
+++ b/.github/workflows/docker_test_3.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: Docker Test 3
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
 
     steps:
 
diff --git a/.github/workflows/e2e_race.yml b/.github/workflows/e2e_race.yml
index b6ce479397..1b66cdcbe1 100644
--- a/.github/workflows/e2e_race.yml
+++ b/.github/workflows/e2e_race.yml
@@ -4,7 +4,7 @@ jobs:
 
   build:
     name: End-to-End Test (Race)
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-18.04
     steps:
 
     - name: Set up Go
diff --git a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
index 48682ad7d6..038e498bc6 100644
--- a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
@@ -1773,3 +1773,46 @@
     "SysTableTableSchema": "VARBINARY(\"ks\")"
   }
 }
+
+# pullout sq after pullout sq
+"select id from user where not id in (select user_extra.col from user_extra where user_extra.user_id = 42) and id in (select user_extra.col from user_extra where user_extra.user_id = 411)"
+{
+  "QueryType": "SELECT",
+  "Original": "select id from user where not id in (select user_extra.col from user_extra where user_extra.user_id = 42) and id in (select user_extra.col from user_extra where user_extra.user_id = 411)",
+  "Instructions": {
+    "OperatorType": "Subquery",
+    "Variant": "PulloutIn",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectEqualUnique",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select user_extra.col from user_extra where 1 != 1",
+        "Query": "select user_extra.col from user_extra where user_extra.user_id = 42",
+        "Table": "user_extra",
+        "Values": [
+          42
+        ],
+        "Vindex": "user_index"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectIN",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select id from user where 1 != 1",
+        "Query": "select id from user where :__sq_has_values1 = 1 and id in ::__vals and not (:__sq_has_values2 = 1 and id in ::__sq2)",
+        "Table": "user",
+        "Values": [
+          "::__sq1"
+        ],
+        "Vindex": "user_index"
+      }
+    ]
+  }
+}