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 }