vitess.io/vitess@v0.16.2/go/test/endtoend/vtgate/queries/reference/reference_test.go (about)

     1  /*
     2  Copyright 2022 The Vitess Authors.
     3  
     4  Licensed under the Apache License, Version 2.0 (the "License");
     5  you may not use this file except in compliance with the License.
     6  You may obtain a copy of the License at
     7  
     8      http://www.apache.org/licenses/LICENSE-2.0
     9  
    10  Unless required by applicable law or agreed to in writing, software
    11  distributed under the License is distributed on an "AS IS" BASIS,
    12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    13  See the License for the specific language governing permissions and
    14  limitations under the License.
    15  */
    16  
    17  package reference
    18  
    19  import (
    20  	"context"
    21  	"testing"
    22  
    23  	"github.com/stretchr/testify/require"
    24  
    25  	"vitess.io/vitess/go/mysql"
    26  	"vitess.io/vitess/go/test/endtoend/utils"
    27  
    28  	"vitess.io/vitess/go/test/endtoend/cluster"
    29  )
    30  
    31  func start(t *testing.T) (*mysql.Conn, func()) {
    32  	ctx := context.Background()
    33  	vtConn, err := mysql.Connect(ctx, &vtParams)
    34  	require.NoError(t, err)
    35  
    36  	return vtConn, func() {
    37  		vtConn.Close()
    38  		cluster.PanicHandler(t)
    39  	}
    40  }
    41  
    42  // TestGlobalReferenceRouting tests that unqualified queries for reference
    43  // tables go to the right place.
    44  //
    45  // Given:
    46  //   - Unsharded keyspace `uks` and sharded keyspace `sks`.
    47  //   - Source table `uks.zip_detail` and a reference table `sks.zip_detail`,
    48  //     initially with the same rows.
    49  //   - Unsharded table `uks.zip` and sharded table `sks.delivery_failure`.
    50  //
    51  // When: we execute `INSERT INTO zip_detail ...`,
    52  // Then: `zip_detail` should be routed to `uks`.
    53  //
    54  // When: we execute `UPDATE zip_detail ...`,
    55  // Then: `zip_detail` should be routed to `uks`.
    56  //
    57  // When: we execute `SELECT ... FROM zip JOIN zip_detail ...`,
    58  // Then: `zip_detail` should be routed to `uks`.
    59  //
    60  // When: we execute `SELECT ... FROM delivery_failure JOIN zip_detail ...`,
    61  // Then: `zip_detail` should be routed to `sks`.
    62  //
    63  // When: we execute `DELETE FROM zip_detail ...`,
    64  // Then: `zip_detail` should be routed to `uks`.
    65  func TestReferenceRouting(t *testing.T) {
    66  	conn, closer := start(t)
    67  	defer closer()
    68  
    69  	// INSERT should route an unqualified zip_detail to unsharded keyspace.
    70  	utils.Exec(t, conn, "INSERT INTO zip_detail(id, zip_id, discontinued_at) VALUES(3, 1, DATE('2022-12-03'))")
    71  	// Verify with qualified zip_detail queries to each keyspace. The unsharded
    72  	// keyspace should have an extra row.
    73  	utils.AssertMatches(
    74  		t,
    75  		conn,
    76  		"SELECT COUNT(zd.id) FROM "+unshardedKeyspaceName+".zip_detail zd WHERE id = 3",
    77  		`[[INT64(1)]]`,
    78  	)
    79  	utils.AssertMatches(
    80  		t,
    81  		conn,
    82  		"SELECT COUNT(zd.id) FROM "+shardedKeyspaceName+".zip_detail zd WHERE id = 3",
    83  		`[[INT64(0)]]`,
    84  	)
    85  
    86  	// UPDATE should route an unqualified zip_detail to unsharded keyspace.
    87  	utils.Exec(t, conn,
    88  		"UPDATE zip_detail SET discontinued_at = NULL WHERE id = 2")
    89  	// Verify with qualified zip_detail queries to each keyspace. The unsharded
    90  	// keyspace should have a matching row, but not the sharded keyspace.
    91  	utils.AssertMatches(
    92  		t,
    93  		conn,
    94  		"SELECT COUNT(id) FROM "+unshardedKeyspaceName+".zip_detail WHERE discontinued_at IS NULL",
    95  		`[[INT64(1)]]`,
    96  	)
    97  	utils.AssertMatches(
    98  		t,
    99  		conn,
   100  		"SELECT COUNT(id) FROM "+shardedKeyspaceName+".zip_detail WHERE discontinued_at IS NULL",
   101  		`[[INT64(0)]]`,
   102  	)
   103  
   104  	// SELECT a table in unsharded keyspace and JOIN unqualified zip_detail.
   105  	utils.AssertMatches(
   106  		t,
   107  		conn,
   108  		"SELECT COUNT(zd.id) FROM zip z JOIN zip_detail zd ON z.id = zd.zip_id WHERE zd.id = 3",
   109  		`[[INT64(1)]]`,
   110  	)
   111  
   112  	// SELECT a table in sharded keyspace and JOIN unqualified zip_detail.
   113  	// Use gen4 planner to avoid errors from gen3 planner.
   114  	utils.AssertMatches(
   115  		t,
   116  		conn,
   117  		`SELECT /*vt+ PLANNER=gen4 */ COUNT(zd.id)
   118  		 FROM delivery_failure df
   119  		 JOIN zip_detail zd ON zd.id = df.zip_detail_id WHERE zd.id = 3`,
   120  		`[[INT64(0)]]`,
   121  	)
   122  
   123  	// DELETE should route an unqualified zip_detail to unsharded keyspace.
   124  	utils.Exec(t, conn, "DELETE FROM zip_detail")
   125  	// Verify with qualified zip_detail queries to each keyspace. The unsharded
   126  	// keyspace should not have any rows; the sharded keyspace should.
   127  	utils.AssertMatches(
   128  		t,
   129  		conn,
   130  		"SELECT COUNT(id) FROM "+unshardedKeyspaceName+".zip_detail",
   131  		`[[INT64(0)]]`,
   132  	)
   133  	utils.AssertMatches(
   134  		t,
   135  		conn,
   136  		"SELECT COUNT(id) FROM "+shardedKeyspaceName+".zip_detail",
   137  		`[[INT64(2)]]`,
   138  	)
   139  }