Repository: vitessio/vitess
Pull request #11272 (2022-09-21 04:25:06)

Move go/mysql flags to pflags
## Description

This moves the `go/mysql` package flags to pflags and limits their usage to vtgate. 

## Related Issue(s)

  - Fixes: https://github.com/vitessio/vitess/issues/10740
  - Follow-up to: https://github.com/vitessio/vitess/pull/10752

## Checklist

-   [x] "Backport me!" label has been added if this change should be backported
-   [x] Tests are not required
-   [x] Documentation is not required

vitess-bot[bot]: ### Review Checklist
            
Hello reviewers! :wave: Please follow this checklist when reviewing this Pull Request.

#### General
- [ ] Ensure that the Pull Request has a descriptive title.
- [ ] If this is a change that users need to know about, please apply the `release notes (needs details)` label so that merging is blocked unless the summary release notes document is included.
- [ ] If a new flag is being introduced, review whether it is really needed. The flag names should be clear and intuitive (as far as possible), and the flag's help should be descriptive. Additionally, flag names should use dashes (`-`) as word separators rather than underscores (`_`).
- [ ] If a workflow is added or modified, each items in `Jobs` should be named in order to mark it as `required`. If the workflow should be required, the GitHub Admin should be notified.

#### Bug fixes
- [ ] There should be at least one unit or end-to-end test.
- [ ] The Pull Request description should either include a link to an issue that describes the bug OR an actual description of the bug and how to reproduce, along with a description of the fix.

#### Non-trivial changes
- [ ] There should be some code comments as to why things are implemented the way they are.

#### New/Existing features
- [ ] Should be documented, either by modifying the existing documentation or creating new documentation.
- [ ] New features should have a link to a feature request issue or an RFC that documents the use cases, corner cases and test cases.

#### Backward compatibility            
- [ ] Protobuf changes should be wire-compatible.
- [ ] Changes to `_vt` tables and RPCs need to be backward compatible.
- [ ] `vtctl` command output order should be stable and `awk`-able.


Patch:
diff --git a/examples/local/101_initial_cluster.sh b/examples/local/101_initial_cluster.sh
index cff2467a05..a14fa142e4 100755
--- a/examples/local/101_initial_cluster.sh
+++ b/examples/local/101_initial_cluster.sh
@@ -42,8 +42,22 @@ done
 # set the correct durability policy for the keyspace
 vtctldclient --server localhost:15999 SetKeyspaceDurabilityPolicy --durability-policy=semi_sync commerce
 
-# set one of the replicas to primary
-vtctldclient PlannedReparentShard commerce/0 --new-primary zone1-100
+# start vtorc
+./scripts/vtorc-up.sh
+
+# Wait for all the tablets to be up and registered in the topology server
+for _ in $(seq 0 200); do
+	vtctldclient GetTablets --keyspace commerce --shard 0 | wc -l | grep -q "3" && break
+	sleep 1
+done;
+vtctldclient GetTablets --keyspace commerce --shard 0 | wc -l | grep -q "3" || (echo "Timed out waiting for tablets to be up in commerce/0" && exit 1)
+
+# Wait for a primary tablet to be elected in the shard
+for _ in $(seq 0 200); do
+	vtctldclient GetTablets --keyspace commerce --shard 0 | grep -q "primary" && break
+	sleep 1
+done;
+vtctldclient GetTablets --keyspace commerce --shard 0 | grep "primary" || (echo "Timed out waiting for primary to be elected in commerce/0" && exit 1)
 
 # create the schema
 vtctldclient ApplySchema --sql-file create_commerce_schema.sql commerce
@@ -57,5 +71,3 @@ CELL=zone1 ./scripts/vtgate-up.sh
 # start vtadmin
 ./scripts/vtadmin-up.sh
 
-# start vtorc
-./scripts/vtorc-up.sh
diff --git a/examples/local/201_customer_tablets.sh b/examples/local/201_customer_tablets.sh
index 62db999d69..6a27bef6cf 100755
--- a/examples/local/201_customer_tablets.sh
+++ b/examples/local/201_customer_tablets.sh
@@ -28,4 +28,16 @@ done
 # set the correct durability policy for the keyspace
 vtctldclient --server localhost:15999 SetKeyspaceDurabilityPolicy --durability-policy=semi_sync customer
 
-vtctldclient InitShardPrimary --force customer/0 zone1-200
+# Wait for all the tablets to be up and registered in the topology server
+for _ in $(seq 0 200); do
+	vtctldclient GetTablets --keyspace customer --shard 0 | wc -l | grep -q "3" && break
+	sleep 1
+done;
+vtctldclient GetTablets --keyspace customer --shard 0 | wc -l | grep -q "3" || (echo "Timed out waiting for tablets to be up in customer/0" && exit 1)
+
+# Wait for a primary tablet to be elected in the shard
+for _ in $(seq 0 200); do
+	vtctldclient GetTablets --keyspace customer --shard 0 | grep -q "primary" && break
+	sleep 1
+done;
+vtctldclient GetTablets --keyspace customer --shard 0 | grep "primary" || (echo "Timed out waiting for primary to be elected in customer/0" && exit 1)
diff --git a/examples/local/201_newkeyspace_tablets.sh b/examples/local/201_newkeyspace_tablets.sh
index 0a62f0e6a9..6a77605fe2 100755
--- a/examples/local/201_newkeyspace_tablets.sh
+++ b/examples/local/201_newkeyspace_tablets.sh
@@ -32,7 +32,19 @@ for i in ${BASETABLETNUM}00 ${BASETABLETNUM}01 ${BASETABLETNUM}02; do
         SHARD=${SHARD} CELL=zone1 KEYSPACE=${KEYSPACE} TABLET_UID=$i ./scripts/vttablet-up.sh
 done
 
-vtctldclient InitShardPrimary --force "${KEYSPACE}/${SHARD}" "zone1-${BASETABLETNUM}00"
+# Wait for all the tablets to be up and registered in the topology server
+for _ in $(seq 0 200); do
+	vtctldclient GetTablets --keyspace "${KEYSPACE}" --shard "${SHARD}" | wc -l | grep -q "3" && break
+	sleep 1
+done;
+vtctldclient GetTablets --keyspace "${KEYSPACE}" --shard "${SHARD}" | wc -l | grep -q "3" || (echo "Timed out waiting for tablets to be up in ${KEYSPACE}/${SHARD}" && exit 1)
+
+# Wait for a primary tablet to be elected in the shard
+for _ in $(seq 0 200); do
+	vtctldclient GetTablets --keyspace "${KEYSPACE}" --shard "${SHARD}" | grep -q "primary" && break
+	sleep 1
+done;
+vtctldclient GetTablets --keyspace "${KEYSPACE}" --shard "${SHARD}" | grep "primary" || (echo "Timed out waiting for primary to be elected in ${KEYSPACE}/${SHARD}" && exit 1)
 
 # Go back to the original ${PWD} in the parent shell
 popd >/dev/null
diff --git a/examples/local/302_new_shards.sh b/examples/local/302_new_shards.sh
index 3aede2b3d6..40569e2c20 100755
--- a/examples/local/302_new_shards.sh
+++ b/examples/local/302_new_shards.sh
@@ -29,5 +29,18 @@ for i in 400 401 402; do
 	SHARD=80- CELL=zone1 KEYSPACE=customer TABLET_UID=$i ./scripts/vttablet-up.sh
 done
 
-vtctldclient InitShardPrimary --force customer/-80 zone1-300
-vtctldclient InitShardPrimary --force customer/80- zone1-400
+for shard in "-80" "80-"; do
+  # Wait for all the tablets to be up and registered in the topology server
+  for _ in $(seq 0 200); do
+  	vtctldclient GetTablets --keyspace customer --shard $shard | wc -l | grep -q "3" && break
+  	sleep 1
+  done;
+  vtctldclient GetTablets --keyspace customer --shard $shard | wc -l | grep -q "3" || (echo "Timed out waiting for tablets to be up in customer/$shard" && exit 1)
+
+  # Wait for a primary tablet to be elected in the shard
+  for _ in $(seq 0 200); do
+  	vtctldclient GetTablets --keyspace customer --shard $shard | grep -q "primary" && break
+  	sleep 1
+  done;
+  vtctldclient GetTablets --keyspace customer --shard $shard | grep "primary" || (echo "Timed out waiting for primary to be elected in customer/$shard" && exit 1)
+done;
diff --git a/examples/region_sharding/101_initial_cluster.sh b/examples/region_sharding/101_initial_cluster.sh
index 70dd1882ef..210334cf18 100755
--- a/examples/region_sharding/101_initial_cluster.sh
+++ b/examples/region_sharding/101_initial_cluster.sh
@@ -38,7 +38,15 @@ SHARD=0 CELL=zone1 KEYSPACE=main TABLET_UID=100 ./scripts/vttablet-up.sh
 # set the correct durability policy for the keyspace
 vtctldclient --server localhost:15999 SetKeyspaceDurabilityPolicy --durability-policy=none main
 
-vtctldclient InitShardPrimary --force main/0 zone1-100
+# start vtorc
+./scripts/vtorc-up.sh
+
+# Wait for a primary tablet to be elected in the shard
+for _ in $(seq 0 200); do
+	vtctldclient GetTablets --keyspace main --shard 0 | grep -q "primary" && break
+	sleep 1
+done;
+vtctldclient GetTablets --keyspace main --shard 0 | grep "primary" || (echo "Timed out waiting for primary to be elected in main/0" && exit 1)
 
 # create the schema
 vtctldclient ApplySchema --sql-file create_main_schema.sql main
@@ -52,5 +60,3 @@ CELL=zone1 ./scripts/vtgate-up.sh
 # start vtadmin
 ./scripts/vtadmin-up.sh
 
-# start vtorc
-./scripts/vtorc-up.sh
diff --git a/examples/region_sharding/202_new_tablets.sh b/examples/region_sharding/202_new_tablets.sh
index f5ddea14c0..e570ae3b3c 100755
--- a/examples/region_sharding/202_new_tablets.sh
+++ b/examples/region_sharding/202_new_tablets.sh
@@ -26,8 +26,11 @@ SHARD=80-c0 CELL=zone1 KEYSPACE=main TABLET_UID=400 ./scripts/vttablet-up.sh
 CELL=zone1 TABLET_UID=500 ./scripts/mysqlctl-up.sh
 SHARD=c0- CELL=zone1 KEYSPACE=main TABLET_UID=500 ./scripts/vttablet-up.sh
 
-# set primary
-vtctldclient InitShardPrimary --force main/-40 zone1-200
-vtctldclient InitShardPrimary --force main/40-80 zone1-300
-vtctldclient InitShardPrimary --force main/80-c0 zone1-400
-vtctldclient InitShardPrimary --force main/c0- zone1-500
+for shard in "-40" "40-80" "80-c0" "c0-"; do
+  # Wait for a primary tablet to be elected in the shard
+  for _ in $(seq 0 200); do
+  	vtctldclient GetTablets --keyspace main --shard $shard | grep -q "primary" && break
+  	sleep 1
+  done;
+  vtctldclient GetTablets --keyspace main --shard $shard | grep "primary" || (echo "Timed out waiting for primary to be elected in main/$shard" && exit 1)
+done;
diff --git a/go/flags/endtoend/vtctld.txt b/go/flags/endtoend/vtctld.txt
index 60367b9dbc..887a7496b4 100644
--- a/go/flags/endtoend/vtctld.txt
+++ b/go/flags/endtoend/vtctld.txt
@@ -87,7 +87,6 @@ Usage of vtctld:
       --log_rotate_max_size uint                                         size in bytes at which logs are rotated (glog.MaxSize) (default 1887436800)
       --logtostderr                                                      log to standard error instead of files
       --master_connect_retry duration                                    Deprecated, use -replication_connect_retry (default 10s)
-      --mysql_server_flush_delay duration                                Delay after which buffered response will be flushed to the client. (default 100ms)
       --mysql_server_version string                                      MySQL server version to advertise.
       --mysqlctl_client_protocol string                                  the protocol to use to talk to the mysqlctl server (default "grpc")
       --mysqlctl_mycnf_template string                                   template file to use for generating the my.cnf file during server init
diff --git a/go/flags/endtoend/vtexplain.txt b/go/flags/endtoend/vtexplain.txt
index 032a77ddf6..807cd0a74a 100644
--- a/go/flags/endtoend/vtexplain.txt
+++ b/go/flags/endtoend/vtexplain.txt
@@ -68,7 +68,6 @@ Usage of vtexplain:
       --mysql_auth_server_impl string                   Which auth server implementation to use. Options: none, ldap, clientcert, static, vault. (default "static")
       --mysql_default_workload string                   Default session workload (OLTP, OLAP, DBA) (default "OLTP")
       --mysql_server_bind_address string                Binds on this address when listening to MySQL binary protocol. Useful to restrict listening to 'localhost' only for instance.
-      --mysql_server_flush_delay duration               Delay after which buffered response will be flushed to the client. (default 100ms)
       --mysql_server_port int                           If set, also listen for MySQL binary protocol connections on this port. (default -1)
       --mysql_server_query_timeout duration             mysql query timeout (default 0s)
       --mysql_server_read_timeout duration              connection read timeout (default 0s)
diff --git a/go/flags/endtoend/vtgr.txt b/go/flags/endtoend/vtgr.txt
index 8ce42ff2b4..5829f6e028 100644
--- a/go/flags/endtoend/vtgr.txt
+++ b/go/flags/endtoend/vtgr.txt
@@ -27,7 +27,6 @@ Usage of vtgr:
       --log_err_stacks                             log stack traces for errors
       --log_rotate_max_size uint                   size in bytes at which logs are rotated (glog.MaxSize) (default 1887436800)
       --logtostderr                                log to standard error instead of files
-      --mysql_server_flush_delay duration          Delay after which buffered response will be flushed to the client. (default 100ms)
       --ping_tablet_timeout duration               time to wait when we ping a tablet (default 2s)
       --pprof strings                              enable profiling
       --purge_logs_interval duration               how often try to remove old logs (default 1h0m0s)
diff --git a/go/flags/endtoend/vtorc.txt b/go/flags/endtoend/vtorc.txt
index 49354958c2..c481bfea17 100644
--- a/go/flags/endtoend/vtorc.txt
+++ b/go/flags/endtoend/vtorc.txt
@@ -28,7 +28,6 @@ Usage of vtorc:
       --log_err_stacks                           log stack traces for errors
       --log_rotate_max_size uint                 size in bytes at which logs are rotated (glog.MaxSize) (default 1887436800)
       --logtostderr                              log to standard error instead of files
-      --mysql_server_flush_delay duration        Delay after which buffered response will be flushed to the client. (default 100ms)
       --noop                                     Dry run; do not perform destructing operations
       --onclose_timeout duration                 wait no more than this for OnClose handlers before stopping (default 1ns)
       --onterm_timeout duration                  wait no more than this for OnTermSync handlers before stopping (default 10s)
diff --git a/go/flags/endtoend/vttablet.txt b/go/flags/endtoend/vttablet.txt
index 429c404407..1b8265601e 100644
--- a/go/flags/endtoend/vttablet.txt
+++ b/go/flags/endtoend/vttablet.txt
@@ -284,7 +284,6 @@ Usage of vttablet:
       --mycnf_slow_log_path string                                       mysql slow query log path
       --mycnf_socket_file string                                         mysql socket file
       --mycnf_tmp_dir string                                             mysql tmp directory
-      --mysql_server_flush_delay duration                                Delay after which buffered response will be flushed to the client. (default 100ms)
       --mysql_server_version string                                      MySQL server version to advertise.
       --mysqlctl_client_protocol string                                  the protocol to use to talk to the mysqlctl server (default "grpc")
       --mysqlctl_mycnf_template string                                   template file to use for generating the my.cnf file during server init
diff --git a/go/mysql/auth_server_static.go b/go/mysql/auth_server_static.go
index bb0a966d7e..d01bab23cd 100644
--- a/go/mysql/auth_server_static.go
+++ b/go/mysql/auth_server_static.go
@@ -41,6 +41,7 @@ var (
 	mysqlAuthServerStaticFile           string
 	mysqlAuthServerStaticString         string
 	mysqlAuthServerStaticReloadInterval time.Duration
+	mysqlServerFlushDelay               = 100 * time.Millisecond
 )
 
 func init() {
@@ -48,6 +49,7 @@ func init() {
 		fs.StringVar(&mysqlAuthServerStaticFile, "mysql_auth_server_static_file", "", "JSON File to read the users/passwords from.")
 		fs.StringVar(&mysqlAuthServerStaticString, "mysql_auth_server_static_string", "", "JSON representation of the users/passwords config.")
 		fs.DurationVar(&mysqlAuthServerStaticReloadInterval, "mysql_auth_static_reload_interval", 0, "Ticker to reload credentials")
+		fs.DurationVar(&mysqlServerFlushDelay, "mysql_server_flush_delay", mysqlServerFlushDelay, "Delay after which buffered response will be flushed to the client.")
 	})
 }
 
diff --git a/go/mysql/conn.go b/go/mysql/conn.go
index f6d8734454..c1da4ef94d 100644
--- a/go/mysql/conn.go
+++ b/go/mysql/conn.go
@@ -21,7 +21,6 @@ import (
 	"crypto/tls"
 	"crypto/x509"
 	"errors"
-	"flag"
 	"fmt"
 	"io"
 	"net"
@@ -43,8 +42,6 @@ import (
 	"vitess.io/vitess/go/vt/vterrors"
 )
 
-var mysqlServerFlushDelay = flag.Duration("mysql_server_flush_delay", 100*time.Millisecond, "Delay after which buffered response will be flushed to the client.")
-
 const (
 	// connBufferSize is how much we buffer for reading and
 	// writing. It is also how much we allocate for ephemeral buffers.
@@ -309,7 +306,7 @@ func (c *Conn) getWriter() (w io.Writer, unget func()) {
 // startFlushTimer must be called while holding lock on bufMu.
 func (c *Conn) startFlushTimer() {
 	c.stopFlushTimer()
-	c.flushTimer = time.AfterFunc(*mysqlServerFlushDelay, func() {
+	c.flushTimer = time.AfterFunc(mysqlServerFlushDelay, func() {
 		c.bufMu.Lock()
 		defer c.bufMu.Unlock()
 
diff --git a/go/vt/vtgate/planbuilder/abstract/join.go b/go/vt/vtgate/planbuilder/abstract/join.go
index 55ad992f3e..b15c0b780d 100644
--- a/go/vt/vtgate/planbuilder/abstract/join.go
+++ b/go/vt/vtgate/planbuilder/abstract/join.go
@@ -46,22 +46,10 @@ func (j *Join) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable)
 		}
 		j.LHS = lhs
 		return j, nil
+
 	case deps.IsSolvedBy(j.RHS.TableID()):
-		// we are looking for predicates like `tbl.col = <>` or `<> = tbl.col`,
-		// where tbl is on the rhs of the left outer join
-		if cmp, isCmp := expr.(*sqlparser.ComparisonExpr); isCmp && cmp.Operator != sqlparser.NullSafeEqualOp &&
-			(sqlparser.IsColName(cmp.Left) && semTable.RecursiveDeps(cmp.Left).IsSolvedBy(j.RHS.TableID()) ||
-				sqlparser.IsColName(cmp.Right) && semTable.RecursiveDeps(cmp.Right).IsSolvedBy(j.RHS.TableID())) {
-			// When the predicate we are pushing is using information from an outer table, we can
-			// check whether the predicate is "null-intolerant" or not. Null-intolerant in this context means that
-			// the predicate will not return true if the table columns are null.
-			// Since an outer join is an inner join with the addition of all the rows from the left-hand side that
-			// matched no rows on the right-hand, if we are later going to remove all the rows where the right-hand
-			// side did not match, we might as well turn the join into an inner join.
-
-			// This is based on the paper "Canonical Abstraction for Outerjoin Optimization" by J Rao et al
-			j.LeftJoin = false
-		}
+		j.tryConvertToInnerJoin(expr, semTable)
+
 		if !j.LeftJoin {
 			rhs, err := j.RHS.PushPredicate(expr, semTable)
 			if err != nil {
@@ -70,19 +58,68 @@ func (j *Join) PushPredicate(expr sqlparser.Expr, semTable *semantics.SemTable)
 			j.RHS = rhs
 			return j, err
 		}
+
 		op := &Filter{
 			Source:     j,
 			Predicates: []sqlparser.Expr{expr},
 		}
 		return op, nil
+
 	case deps.IsSolvedBy(j.LHS.TableID().Merge(j.RHS.TableID())):
-		j.Predicate = sqlparser.AndExpressions(j.Predicate, expr)
-		return j, nil
+		j.tryConvertToInnerJoin(expr, semTable)
+
+		if !j.LeftJoin {
+			j.Predicate = sqlparser.AndExpressions(j.Predicate, expr)
+			return j, nil
+		}
+
+		op := &Filter{
+			Source:     j,
+			Predicates: []sqlparser.Expr{expr},
+		}
+		return op, nil
 	}
 
 	return nil, vterrors.Errorf(vtrpcpb.Code_INTERNAL, "Cannot push predicate: %s", sqlparser.String(expr))
 }
 
+// When a predicate uses information from an outer table, we can convert from an outer join to an inner join
+// if the predicate is "null-intolerant".
+//
+// Null-intolerant in this context means that the predicate will not be true if the table columns are null.
+//
+// Since an outer join is an inner join with the addition of all the rows from the left-hand side that
+// matched no rows on the right-hand, if we are later going to remove all the rows where the right-hand
+// side did not match, we might as well turn the join into an inner join.
+//
+// This is based on the paper "Canonical Abstraction for Outerjoin Optimization" by J Rao et al
+func (j *Join) tryConvertToInnerJoin(expr sqlparser.Expr, semTable *semantics.SemTable) {
+	if !j.LeftJoin {
+		return
+	}
+
+	switch expr := expr.(type) {
+	case *sqlparser.ComparisonExpr:
+		if expr.Operator == sqlparser.NullSafeEqualOp {
+			return
+		}
+
+		if sqlparser.IsColName(expr.Left) && semTable.RecursiveDeps(expr.Left).IsSolvedBy(j.RHS.TableID()) ||
+			sqlparser.IsColName(expr.Right) && semTable.RecursiveDeps(expr.Right).IsSolvedBy(j.RHS.TableID()) {
+			j.LeftJoin = false
+		}
+
+	case *sqlparser.IsExpr:
+		if expr.Right != sqlparser.IsNotNullOp {
+			return
+		}
+
+		if sqlparser.IsColName(expr.Left) && semTable.RecursiveDeps(expr.Left).IsSolvedBy(j.RHS.TableID()) {
+			j.LeftJoin = false
+		}
+	}
+}
+
 // TableID implements the Operator interface
 func (j *Join) TableID() semantics.TableSet {
 	return j.RHS.TableID().Merge(j.LHS.TableID())


Test patch:
diff --git a/go/mysql/query_benchmark_test.go b/go/mysql/query_benchmark_test.go
index db05639850..f90dfc9e89 100644
--- a/go/mysql/query_benchmark_test.go
+++ b/go/mysql/query_benchmark_test.go
@@ -17,21 +17,16 @@ limitations under the License.
 package mysql
 
 import (
-	"flag"
+	"context"
 	"math/rand"
 	"net"
 	"strings"
 	"testing"
-
-	"context"
 )
 
+// Override the default here to test with different values.
 var testReadConnBufferSize = connBufferSize
 
-func init() {
-	flag.IntVar(&testReadConnBufferSize, "test.read_conn_buffer_size", connBufferSize, "buffer size for reads from connections in tests")
-}
-
 const benchmarkQueryPrefix = "benchmark "
 
 func benchmarkQuery(b *testing.B, threads int, query string) {
diff --git a/go/mysql/server_flaky_test.go b/go/mysql/server_flaky_test.go
index 6ec95aef59..b89476a2a7 100644
--- a/go/mysql/server_flaky_test.go
+++ b/go/mysql/server_flaky_test.go
@@ -1459,8 +1459,8 @@ func TestParseConnAttrs(t *testing.T) {
 }
 
 func TestServerFlush(t *testing.T) {
-	defer func(saved time.Duration) { *mysqlServerFlushDelay = saved }(*mysqlServerFlushDelay)
-	*mysqlServerFlushDelay = 10 * time.Millisecond
+	defer func(saved time.Duration) { mysqlServerFlushDelay = saved }(mysqlServerFlushDelay)
+	mysqlServerFlushDelay = 10 * time.Millisecond
 
 	th := &testHandler{}
 
@@ -1485,8 +1485,8 @@ func TestServerFlush(t *testing.T) {
 
 	flds, err := c.Fields()
 	require.NoError(t, err)
-	if duration, want := time.Since(start), 20*time.Millisecond; duration < *mysqlServerFlushDelay || duration > want {
-		assert.Fail(t, "duration out of expected range", "duration: %v, want between %v and %v", duration.String(), (*mysqlServerFlushDelay).String(), want.String())
+	if duration, want := time.Since(start), 20*time.Millisecond; duration < mysqlServerFlushDelay || duration > want {
+		assert.Fail(t, "duration out of expected range", "duration: %v, want between %v and %v", duration.String(), (mysqlServerFlushDelay).String(), want.String())
 	}
 	want1 := []*querypb.Field{{
 		Name: "result",
diff --git a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
index 83edf5118f..75ef917849 100644
--- a/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/filter_cases.txt
@@ -5965,6 +5965,114 @@ Gen4 plan same as above
 }
 Gen4 plan same as above
 
+# For left joins, where conditions using both sides of the join are not pulled into the join conditions
+"SELECT music.id FROM music LEFT OUTER JOIN user ON music.user_id = user.id WHERE (user.name = 'Trent Reznor' OR music.genre = 'pop') AND music.user_id = 5"
+{
+  "QueryType": "SELECT",
+  "Original": "SELECT music.id FROM music LEFT OUTER JOIN user ON music.user_id = user.id WHERE (user.name = 'Trent Reznor' OR music.genre = 'pop') AND music.user_id = 5",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "EqualUnique",
+    "Keyspace": {
+      "Name": "user",
+      "Sharded": true
+    },
+    "FieldQuery": "select music.id from music left join `user` on music.user_id = `user`.id where 1 != 1",
+    "Query": "select music.id from music left join `user` on music.user_id = `user`.id where music.user_id = 5 and (`user`.`name` = 'Trent Reznor' or music.genre = 'pop')",
+    "Table": "`user`, music",
+    "Values": [
+      "INT64(5)"
+    ],
+    "Vindex": "user_index"
+  },
+  "TablesUsed": [
+    "user.music",
+    "user.user"
+  ]
+}
+Gen4 plan same as above
+
+# For left joins, where conditions using both sides of the join are not pulled into the join conditions (swapped order)
+"SELECT music.id FROM music LEFT OUTER JOIN user ON music.user_id = user.id WHERE music.user_id = 5 AND (user.name = 'Trent Reznor' OR music.genre = 'pop')"
+{
+  "QueryType": "SELECT",
+  "Original": "SELECT music.id FROM music LEFT OUTER JOIN user ON music.user_id = user.id WHERE music.user_id = 5 AND (user.name = 'Trent Reznor' OR music.genre = 'pop')",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "EqualUnique",
+    "Keyspace": {
+      "Name": "user",
+      "Sharded": true
+    },
+    "FieldQuery": "select music.id from music left join `user` on music.user_id = `user`.id where 1 != 1",
+    "Query": "select music.id from music left join `user` on music.user_id = `user`.id where music.user_id = 5 and (`user`.`name` = 'Trent Reznor' or music.genre = 'pop')",
+    "Table": "`user`, music",
+    "Values": [
+      "INT64(5)"
+    ],
+    "Vindex": "user_index"
+  },
+  "TablesUsed": [
+    "user.music",
+    "user.user"
+  ]
+}
+Gen4 plan same as above
+
+# For left joins, null intolerant where conditions using both sides of the join are transformed to inner joins
+"SELECT music.id FROM music LEFT OUTER JOIN user ON music.user_id = user.id WHERE music.user_id = 5 AND music.componist = user.name"
+{
+  "QueryType": "SELECT",
+  "Original": "SELECT music.id FROM music LEFT OUTER JOIN user ON music.user_id = user.id WHERE music.user_id = 5 AND music.componist = user.name",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "EqualUnique",
+    "Keyspace": {
+      "Name": "user",
+      "Sharded": true
+    },
+    "FieldQuery": "select music.id from music, `user` where 1 != 1",
+    "Query": "select music.id from music, `user` where music.user_id = 5 and music.user_id = `user`.id and music.componist = `user`.`name`",
+    "Table": "`user`, music",
+    "Values": [
+      "INT64(5)"
+    ],
+    "Vindex": "user_index"
+  },
+  "TablesUsed": [
+    "user.music",
+    "user.user"
+  ]
+}
+Gen4 plan same as above
+
+# For left joins, null intolerant where conditions using `IS NOT NULL` allow outer join simplification
+"SELECT music.id FROM music LEFT OUTER JOIN user ON user.id = music.user_id WHERE music.user_id = 5 AND user.id IS NOT NULL"
+{
+  "QueryType": "SELECT",
+  "Original": "SELECT music.id FROM music LEFT OUTER JOIN user ON user.id = music.user_id WHERE music.user_id = 5 AND user.id IS NOT NULL",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "EqualUnique",
+    "Keyspace": {
+      "Name": "user",
+      "Sharded": true
+    },
+    "FieldQuery": "select music.id from music, `user` where 1 != 1",
+    "Query": "select music.id from music, `user` where music.user_id = 5 and `user`.id is not null and `user`.id = music.user_id",
+    "Table": "`user`, music",
+    "Values": [
+      "INT64(5)"
+    ],
+    "Vindex": "user_index"
+  },
+  "TablesUsed": [
+    "user.music",
+    "user.user"
+  ]
+}
+Gen4 plan same as above
+
 # optimize ORs to IN route op codes #1
 "select col from user where id = 1 or id = 2"
 {