1/* 2Copyright 2019 Google LLC 3 4Licensed under the Apache License, Version 2.0 (the "License"); 5you may not use this file except in compliance with the License. 6You may obtain a copy of the License at 7 8 http://www.apache.org/licenses/LICENSE-2.0 9 10Unless required by applicable law or agreed to in writing, software 11distributed under the License is distributed on an "AS IS" BASIS, 12WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13See the License for the specific language governing permissions and 14limitations under the License. 15*/ 16 17/* 18This file holds tests for the in-memory fake for comparing it against a real Cloud Spanner. 19 20By default it uses the Spanner client against the in-memory fake; set the 21-test_db flag to "projects/P/instances/I/databases/D" to make it use a real 22Cloud Spanner database instead. You may need to provide -timeout=5m too. 23*/ 24 25package spannertest 26 27import ( 28 "context" 29 "flag" 30 "fmt" 31 "reflect" 32 "sort" 33 "testing" 34 "time" 35 36 "cloud.google.com/go/civil" 37 "cloud.google.com/go/spanner" 38 dbadmin "cloud.google.com/go/spanner/admin/database/apiv1" 39 "google.golang.org/api/iterator" 40 "google.golang.org/api/option" 41 "google.golang.org/grpc" 42 "google.golang.org/grpc/codes" 43 "google.golang.org/grpc/status" 44 45 dbadminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1" 46 spannerpb "google.golang.org/genproto/googleapis/spanner/v1" 47 structpb "google.golang.org/protobuf/types/known/structpb" 48) 49 50var testDBFlag = flag.String("test_db", "", "Fully-qualified database name to test against; empty means use an in-memory fake.") 51 52func dbName() string { 53 if *testDBFlag != "" { 54 return *testDBFlag 55 } 56 return "projects/fake-proj/instances/fake-instance/databases/fake-db" 57} 58 59func makeClient(t *testing.T) (*spanner.Client, *dbadmin.DatabaseAdminClient, func()) { 60 // Despite the docs, this context is also used for auth, 61 // so it needs to be long-lived. 62 ctx := context.Background() 63 64 if *testDBFlag != "" { 65 t.Logf("Using real Spanner DB %s", *testDBFlag) 66 dialOpt := option.WithGRPCDialOption(grpc.WithTimeout(5 * time.Second)) 67 client, err := spanner.NewClient(ctx, *testDBFlag, dialOpt) 68 if err != nil { 69 t.Fatalf("Connecting to %s: %v", *testDBFlag, err) 70 } 71 adminClient, err := dbadmin.NewDatabaseAdminClient(ctx, dialOpt) 72 if err != nil { 73 client.Close() 74 t.Fatalf("Connecting DB admin client: %v", err) 75 } 76 return client, adminClient, func() { client.Close(); adminClient.Close() } 77 } 78 79 // Don't use SPANNER_EMULATOR_HOST because we need the raw connection for 80 // the database admin client anyway. 81 82 t.Logf("Using in-memory fake Spanner DB") 83 srv, err := NewServer("localhost:0") 84 if err != nil { 85 t.Fatalf("Starting in-memory fake: %v", err) 86 } 87 srv.SetLogger(t.Logf) 88 dialCtx, cancel := context.WithTimeout(ctx, 1*time.Second) 89 defer cancel() 90 conn, err := grpc.DialContext(dialCtx, srv.Addr, grpc.WithInsecure()) 91 if err != nil { 92 srv.Close() 93 t.Fatalf("Dialing in-memory fake: %v", err) 94 } 95 client, err := spanner.NewClient(ctx, dbName(), option.WithGRPCConn(conn)) 96 if err != nil { 97 srv.Close() 98 t.Fatalf("Connecting to in-memory fake: %v", err) 99 } 100 adminClient, err := dbadmin.NewDatabaseAdminClient(ctx, option.WithGRPCConn(conn)) 101 if err != nil { 102 srv.Close() 103 t.Fatalf("Connecting to in-memory fake DB admin: %v", err) 104 } 105 return client, adminClient, func() { 106 client.Close() 107 adminClient.Close() 108 conn.Close() 109 srv.Close() 110 } 111} 112 113func TestIntegration_SpannerBasics(t *testing.T) { 114 client, adminClient, cleanup := makeClient(t) 115 defer cleanup() 116 117 ctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute) 118 defer cancel() 119 120 // Do a trivial query to verify the connection works. 121 it := client.Single().Query(ctx, spanner.NewStatement("SELECT 1")) 122 row, err := it.Next() 123 if err != nil { 124 t.Fatalf("Getting first row of trivial query: %v", err) 125 } 126 var value int64 127 if err := row.Column(0, &value); err != nil { 128 t.Fatalf("Decoding row data from trivial query: %v", err) 129 } 130 if value != 1 { 131 t.Errorf("Trivial query gave %d, want 1", value) 132 } 133 // There shouldn't be a next row. 134 _, err = it.Next() 135 if err != iterator.Done { 136 t.Errorf("Reading second row of trivial query gave %v, want iterator.Done", err) 137 } 138 it.Stop() 139 140 // Drop any previous test table/index, and make a fresh one in a few stages. 141 const tableName = "Characters" 142 err = updateDDL(t, adminClient, "DROP INDEX AgeIndex") 143 // NotFound is an acceptable failure mode here. 144 if st, _ := status.FromError(err); st.Code() == codes.NotFound { 145 err = nil 146 } 147 if err != nil { 148 t.Fatalf("Dropping old index: %v", err) 149 } 150 if err := dropTable(t, adminClient, tableName); err != nil { 151 t.Fatal(err) 152 } 153 err = updateDDL(t, adminClient, 154 `CREATE TABLE `+tableName+` ( 155 FirstName STRING(20) NOT NULL, 156 LastName STRING(20) NOT NULL, 157 Alias STRING(MAX), 158 ) PRIMARY KEY (FirstName, LastName)`) 159 if err != nil { 160 t.Fatalf("Setting up fresh table: %v", err) 161 } 162 err = updateDDL(t, adminClient, 163 `ALTER TABLE `+tableName+` ADD COLUMN Age INT64`, 164 `CREATE INDEX AgeIndex ON `+tableName+` (Age DESC)`) 165 if err != nil { 166 t.Fatalf("Adding new column: %v", err) 167 } 168 169 // Insert some data. 170 _, err = client.Apply(ctx, []*spanner.Mutation{ 171 spanner.Insert(tableName, 172 []string{"FirstName", "LastName", "Alias", "Age"}, 173 []interface{}{"Steve", "Rogers", "Captain America", 101}), 174 spanner.Insert(tableName, 175 []string{"LastName", "FirstName", "Age", "Alias"}, 176 []interface{}{"Romanoff", "Natasha", 35, "Black Widow"}), 177 spanner.Insert(tableName, 178 []string{"Age", "Alias", "FirstName", "LastName"}, 179 []interface{}{49, "Iron Man", "Tony", "Stark"}), 180 spanner.Insert(tableName, 181 []string{"FirstName", "Alias", "LastName"}, // no Age 182 []interface{}{"Clark", "Superman", "Kent"}), 183 // Two rows with the same value in one column, 184 // but with distinct primary keys. 185 spanner.Insert(tableName, 186 []string{"FirstName", "LastName", "Alias"}, 187 []interface{}{"Peter", "Parker", "Spider-Man"}), 188 spanner.Insert(tableName, 189 []string{"FirstName", "LastName", "Alias"}, 190 []interface{}{"Peter", "Quill", "Star-Lord"}), 191 }) 192 if err != nil { 193 t.Fatalf("Applying mutations: %v", err) 194 } 195 196 // Delete some data. 197 _, err = client.Apply(ctx, []*spanner.Mutation{ 198 // Whoops. DC, not MCU. 199 spanner.Delete(tableName, spanner.Key{"Clark", "Kent"}), 200 }) 201 if err != nil { 202 t.Fatalf("Applying mutations: %v", err) 203 } 204 205 // Read a single row. 206 row, err = client.Single().ReadRow(ctx, tableName, spanner.Key{"Tony", "Stark"}, []string{"Alias", "Age"}) 207 if err != nil { 208 t.Fatalf("Reading single row: %v", err) 209 } 210 var alias string 211 var age int64 212 if err := row.Columns(&alias, &age); err != nil { 213 t.Fatalf("Decoding single row: %v", err) 214 } 215 if alias != "Iron Man" || age != 49 { 216 t.Errorf(`Single row read gave (%q, %d), want ("Iron Man", 49)`, alias, age) 217 } 218 219 // Read all rows, and do a local age sum. 220 rows := client.Single().Read(ctx, tableName, spanner.AllKeys(), []string{"Age"}) 221 var ageSum int64 222 err = rows.Do(func(row *spanner.Row) error { 223 var age spanner.NullInt64 224 if err := row.Columns(&age); err != nil { 225 return err 226 } 227 if age.Valid { 228 ageSum += age.Int64 229 } 230 return nil 231 }) 232 if err != nil { 233 t.Fatalf("Iterating over all row read: %v", err) 234 } 235 if want := int64(101 + 35 + 49); ageSum != want { 236 t.Errorf("Age sum after iterating over all rows = %d, want %d", ageSum, want) 237 } 238 239 // Do a more complex query to find the aliases of the two oldest non-centenarian characters. 240 stmt := spanner.NewStatement(`SELECT Alias FROM ` + tableName + ` WHERE Age < @ageLimit AND Alias IS NOT NULL ORDER BY Age DESC LIMIT @limit`) 241 stmt.Params = map[string]interface{}{ 242 "ageLimit": 100, 243 "limit": 2, 244 } 245 rows = client.Single().Query(ctx, stmt) 246 var oldFolk []string 247 err = rows.Do(func(row *spanner.Row) error { 248 var alias string 249 if err := row.Columns(&alias); err != nil { 250 return err 251 } 252 oldFolk = append(oldFolk, alias) 253 return nil 254 }) 255 if err != nil { 256 t.Fatalf("Iterating over complex query: %v", err) 257 } 258 if want := []string{"Iron Man", "Black Widow"}; !reflect.DeepEqual(oldFolk, want) { 259 t.Errorf("Complex query results = %v, want %v", oldFolk, want) 260 } 261 262 // Apply an update. 263 _, err = client.Apply(ctx, []*spanner.Mutation{ 264 spanner.Update(tableName, 265 []string{"FirstName", "LastName", "Age"}, 266 []interface{}{"Steve", "Rogers", 102}), 267 }) 268 if err != nil { 269 t.Fatalf("Applying mutations: %v", err) 270 } 271 row, err = client.Single().ReadRow(ctx, tableName, spanner.Key{"Steve", "Rogers"}, []string{"Age"}) 272 if err != nil { 273 t.Fatalf("Reading single row: %v", err) 274 } 275 if err := row.Columns(&age); err != nil { 276 t.Fatalf("Decoding single row: %v", err) 277 } 278 if age != 102 { 279 t.Errorf("After updating Captain America, age = %d, want 102", age) 280 } 281 282 // Do a query where the result type isn't deducible from the first row. 283 stmt = spanner.NewStatement(`SELECT Age FROM ` + tableName + ` WHERE FirstName = "Peter"`) 284 rows = client.Single().Query(ctx, stmt) 285 var nullPeters int 286 err = rows.Do(func(row *spanner.Row) error { 287 var age spanner.NullInt64 288 if err := row.Column(0, &age); err != nil { 289 return err 290 } 291 if age.Valid { 292 t.Errorf("Got non-NULL Age %d for a Peter", age.Int64) 293 } else { 294 nullPeters++ 295 } 296 return nil 297 }) 298 if err != nil { 299 t.Fatalf("Counting Peters with NULL Ages: %v", err) 300 } 301 if nullPeters != 2 { 302 t.Errorf("Found %d Peters with NULL Ages, want 2", nullPeters) 303 } 304 305 // Check handling of array types. 306 err = updateDDL(t, adminClient, `ALTER TABLE `+tableName+` ADD COLUMN Allies ARRAY<STRING(20)>`) 307 if err != nil { 308 t.Fatalf("Adding new array-typed column: %v", err) 309 } 310 _, err = client.Apply(ctx, []*spanner.Mutation{ 311 spanner.Update(tableName, 312 []string{"FirstName", "LastName", "Allies"}, 313 []interface{}{"Steve", "Rogers", []string{}}), 314 spanner.Update(tableName, 315 []string{"FirstName", "LastName", "Allies"}, 316 []interface{}{"Tony", "Stark", []string{"Black Widow", "Spider-Man"}}), 317 }) 318 if err != nil { 319 t.Fatalf("Applying mutations: %v", err) 320 } 321 row, err = client.Single().ReadRow(ctx, tableName, spanner.Key{"Tony", "Stark"}, []string{"Allies"}) 322 if err != nil { 323 t.Fatalf("Reading row with array value: %v", err) 324 } 325 var names []string 326 if err := row.Column(0, &names); err != nil { 327 t.Fatalf("Unpacking array value: %v", err) 328 } 329 if want := []string{"Black Widow", "Spider-Man"}; !reflect.DeepEqual(names, want) { 330 t.Errorf("Read array value: got %q, want %q", names, want) 331 } 332 row, err = client.Single().ReadRow(ctx, tableName, spanner.Key{"Steve", "Rogers"}, []string{"Allies"}) 333 if err != nil { 334 t.Fatalf("Reading row with empty array value: %v", err) 335 } 336 if err := row.Column(0, &names); err != nil { 337 t.Fatalf("Unpacking empty array value: %v", err) 338 } 339 if len(names) > 0 { 340 t.Errorf("Read empty array value: got %q", names) 341 } 342 343 // Exercise commit timestamp. 344 err = updateDDL(t, adminClient, `ALTER TABLE `+tableName+` ADD COLUMN Updated TIMESTAMP OPTIONS (allow_commit_timestamp=true)`) 345 if err != nil { 346 t.Fatalf("Adding new timestamp column: %v", err) 347 } 348 cts, err := client.Apply(ctx, []*spanner.Mutation{ 349 // Update one row in place. 350 spanner.Update(tableName, 351 []string{"FirstName", "LastName", "Allies", "Updated"}, 352 []interface{}{"Tony", "Stark", []string{"Spider-Man", "Professor Hulk"}, spanner.CommitTimestamp}), 353 }) 354 if err != nil { 355 t.Fatalf("Applying mutations: %v", err) 356 } 357 cts = cts.In(time.UTC) 358 if d := time.Since(cts); d < 0 || d > 10*time.Second { 359 t.Errorf("Commit timestamp %v not in the last 10s", cts) 360 } 361 row, err = client.Single().ReadRow(ctx, tableName, spanner.Key{"Tony", "Stark"}, []string{"Allies", "Updated"}) 362 if err != nil { 363 t.Fatalf("Reading single row: %v", err) 364 } 365 var gotAllies []string 366 var gotUpdated time.Time 367 if err := row.Columns(&gotAllies, &gotUpdated); err != nil { 368 t.Fatalf("Decoding single row: %v", err) 369 } 370 if want := []string{"Spider-Man", "Professor Hulk"}; !reflect.DeepEqual(gotAllies, want) { 371 t.Errorf("After updating Iron Man, allies = %+v, want %+v", gotAllies, want) 372 } 373 if !gotUpdated.Equal(cts) { 374 t.Errorf("After updating Iron Man, updated = %v, want %v", gotUpdated, cts) 375 } 376 377 // Check if IN UNNEST works. 378 stmt = spanner.NewStatement(`SELECT Age FROM ` + tableName + ` WHERE FirstName IN UNNEST(@list)`) 379 stmt.Params = map[string]interface{}{ 380 "list": []string{"Peter", "Steve"}, 381 } 382 rows = client.Single().Query(ctx, stmt) 383 var ages []int64 384 err = rows.Do(func(row *spanner.Row) error { 385 var age spanner.NullInt64 386 if err := row.Column(0, &age); err != nil { 387 return err 388 } 389 ages = append(ages, age.Int64) // zero for NULL 390 return nil 391 }) 392 if err != nil { 393 t.Fatalf("Getting ages using IN UNNEST: %v", err) 394 } 395 sort.Slice(ages, func(i, j int) bool { return ages[i] < ages[j] }) 396 wantAges := []int64{0, 0, 102} // Peter Parker, Peter Quill, Steve Rogers (modified) 397 if !reflect.DeepEqual(ages, wantAges) { 398 t.Errorf("Query with IN UNNEST gave wrong ages: got %+v, want %+v", ages, wantAges) 399 } 400} 401 402func TestIntegration_ReadsAndQueries(t *testing.T) { 403 client, adminClient, cleanup := makeClient(t) 404 defer cleanup() 405 406 ctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute) 407 defer cancel() 408 409 // Drop any old tables. 410 // Do them all concurrently; this saves a lot of time. 411 allTables := []string{ 412 "Staff", 413 "PlayerStats", 414 "JoinA", "JoinB", "JoinC", "JoinD", "JoinE", "JoinF", 415 "SomeStrings", 416 } 417 errc := make(chan error) 418 for _, table := range allTables { 419 go func(table string) { 420 errc <- dropTable(t, adminClient, table) 421 }(table) 422 } 423 var bad bool 424 for range allTables { 425 if err := <-errc; err != nil { 426 t.Error(err) 427 bad = true 428 } 429 } 430 if bad { 431 t.FailNow() 432 } 433 434 err := updateDDL(t, adminClient, 435 `CREATE TABLE Staff ( 436 Tenure INT64, 437 ID INT64, 438 Name STRING(MAX), 439 Cool BOOL, 440 Height FLOAT64, 441 ) PRIMARY KEY (Name, ID)`) 442 if err != nil { 443 t.Fatal(err) 444 } 445 446 // Insert a subset of columns. 447 _, err = client.Apply(ctx, []*spanner.Mutation{ 448 spanner.Insert("Staff", []string{"ID", "Name", "Tenure", "Height"}, []interface{}{1, "Jack", 10, 1.85}), 449 spanner.Insert("Staff", []string{"ID", "Name", "Tenure", "Height"}, []interface{}{2, "Daniel", 11, 1.83}), 450 }) 451 if err != nil { 452 t.Fatalf("Inserting data: %v", err) 453 } 454 // Insert a different set of columns. 455 _, err = client.Apply(ctx, []*spanner.Mutation{ 456 spanner.Insert("Staff", []string{"Name", "ID", "Cool", "Tenure", "Height"}, []interface{}{"Sam", 3, false, 9, 1.75}), 457 spanner.Insert("Staff", []string{"Name", "ID", "Cool", "Tenure", "Height"}, []interface{}{"Teal'c", 4, true, 8, 1.91}), 458 spanner.Insert("Staff", []string{"Name", "ID", "Cool", "Tenure", "Height"}, []interface{}{"George", 5, nil, 6, 1.73}), 459 spanner.Insert("Staff", []string{"Name", "ID", "Cool", "Tenure", "Height"}, []interface{}{"Harry", 6, true, nil, nil}), 460 }) 461 if err != nil { 462 t.Fatalf("Inserting more data: %v", err) 463 } 464 // Delete that last one. 465 _, err = client.Apply(ctx, []*spanner.Mutation{ 466 spanner.Delete("Staff", spanner.Key{"Harry", 6}), 467 }) 468 if err != nil { 469 t.Fatalf("Deleting a row: %v", err) 470 } 471 // Turns out this guy isn't cool after all. 472 _, err = client.Apply(ctx, []*spanner.Mutation{ 473 // Missing columns should be left alone. 474 spanner.Update("Staff", []string{"Name", "ID", "Cool"}, []interface{}{"Daniel", 2, false}), 475 }) 476 if err != nil { 477 t.Fatalf("Updating a row: %v", err) 478 } 479 480 // Read some specific keys. 481 ri := client.Single().Read(ctx, "Staff", spanner.KeySets( 482 spanner.Key{"George", 5}, 483 spanner.Key{"Harry", 6}, // Missing key should be silently ignored. 484 spanner.Key{"Sam", 3}, 485 spanner.Key{"George", 5}, // Duplicate key should be silently ignored. 486 ), []string{"Name", "Tenure"}) 487 if err != nil { 488 t.Fatalf("Reading keys: %v", err) 489 } 490 all := mustSlurpRows(t, ri) 491 wantAll := [][]interface{}{ 492 {"George", int64(6)}, 493 {"Sam", int64(9)}, 494 } 495 if !reflect.DeepEqual(all, wantAll) { 496 t.Errorf("Read data by keys wrong.\n got %v\nwant %v", all, wantAll) 497 } 498 // Read the same, but by key range. 499 ri = client.Single().Read(ctx, "Staff", spanner.KeySets( 500 spanner.KeyRange{Start: spanner.Key{"Gabriel"}, End: spanner.Key{"Harpo"}, Kind: spanner.OpenOpen}, 501 spanner.KeyRange{Start: spanner.Key{"Sam", 3}, End: spanner.Key{"Teal'c", 4}, Kind: spanner.ClosedOpen}, 502 ), []string{"Name", "Tenure"}) 503 all = mustSlurpRows(t, ri) 504 if !reflect.DeepEqual(all, wantAll) { 505 t.Errorf("Read data by key ranges wrong.\n got %v\nwant %v", all, wantAll) 506 } 507 // Read a subset of all rows, with a limit. 508 ri = client.Single().ReadWithOptions(ctx, "Staff", spanner.AllKeys(), []string{"Tenure", "Name", "Height"}, 509 &spanner.ReadOptions{Limit: 4}) 510 all = mustSlurpRows(t, ri) 511 wantAll = [][]interface{}{ 512 // Primary key is (Name, ID), so results should come back sorted by Name then ID. 513 {int64(11), "Daniel", 1.83}, 514 {int64(6), "George", 1.73}, 515 {int64(10), "Jack", 1.85}, 516 {int64(9), "Sam", 1.75}, 517 } 518 if !reflect.DeepEqual(all, wantAll) { 519 t.Errorf("ReadAll data wrong.\n got %v\nwant %v", all, wantAll) 520 } 521 522 // Add DATE and TIMESTAMP columns, and populate them with some data. 523 err = updateDDL(t, adminClient, 524 `ALTER TABLE Staff ADD COLUMN FirstSeen DATE`, 525 "ALTER TABLE Staff ADD COLUMN `To` TIMESTAMP", // "TO" is a keyword; needs quoting 526 ) 527 if err != nil { 528 t.Fatalf("Adding columns: %v", err) 529 } 530 _, err = client.Apply(ctx, []*spanner.Mutation{ 531 spanner.Update("Staff", []string{"Name", "ID", "FirstSeen", "To"}, []interface{}{"Jack", 1, "1994-10-28", nil}), 532 spanner.Update("Staff", []string{"Name", "ID", "FirstSeen", "To"}, []interface{}{"Daniel", 2, "1994-10-28", nil}), 533 spanner.Update("Staff", []string{"Name", "ID", "FirstSeen", "To"}, []interface{}{"George", 5, "1997-07-27", "2008-07-29T11:22:43Z"}), 534 }) 535 if err != nil { 536 t.Fatalf("Updating rows: %v", err) 537 } 538 539 // Add some more data, then delete it with a KeyRange. 540 // The queries below ensure that this was all deleted. 541 _, err = client.Apply(ctx, []*spanner.Mutation{ 542 spanner.Insert("Staff", []string{"Name", "ID"}, []interface{}{"01", 1}), 543 spanner.Insert("Staff", []string{"Name", "ID"}, []interface{}{"03", 3}), 544 spanner.Insert("Staff", []string{"Name", "ID"}, []interface{}{"06", 6}), 545 }) 546 if err != nil { 547 t.Fatalf("Inserting data: %v", err) 548 } 549 _, err = client.Apply(ctx, []*spanner.Mutation{ 550 spanner.Delete("Staff", spanner.KeyRange{ 551 /* This should work: 552 Start: spanner.Key{"01", 1}, 553 End: spanner.Key{"9"}, 554 However, that is unimplemented in the production Cloud Spanner, which rejects 555 that: ""For delete ranges, start and limit keys may only differ in the final key part" 556 */ 557 Start: spanner.Key{"01"}, 558 End: spanner.Key{"9"}, 559 Kind: spanner.ClosedOpen, 560 }), 561 }) 562 if err != nil { 563 t.Fatalf("Deleting key range: %v", err) 564 } 565 // Re-add the data and delete with DML. 566 _, err = client.Apply(ctx, []*spanner.Mutation{ 567 spanner.Insert("Staff", []string{"Name", "ID"}, []interface{}{"01", 1}), 568 spanner.Insert("Staff", []string{"Name", "ID"}, []interface{}{"03", 3}), 569 spanner.Insert("Staff", []string{"Name", "ID"}, []interface{}{"06", 6}), 570 }) 571 if err != nil { 572 t.Fatalf("Inserting data: %v", err) 573 } 574 var n int64 575 _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, tx *spanner.ReadWriteTransaction) error { 576 stmt := spanner.NewStatement("DELETE FROM Staff WHERE Name >= @min AND Name < @max") 577 stmt.Params["min"] = "01" 578 stmt.Params["max"] = "07" 579 n, err = tx.Update(ctx, stmt) 580 return err 581 }) 582 if err != nil { 583 t.Fatalf("Deleting with DML: %v", err) 584 } 585 if n != 3 { 586 t.Errorf("Deleting with DML affected %d rows, want 3", n) 587 } 588 589 // Add a BYTES column, and populate it with some data. 590 err = updateDDL(t, adminClient, `ALTER TABLE Staff ADD COLUMN RawBytes BYTES(MAX)`) 591 if err != nil { 592 t.Fatalf("Adding column: %v", err) 593 } 594 _, err = client.Apply(ctx, []*spanner.Mutation{ 595 // bytes {0x01 0x00 0x01} encode as base-64 AQAB. 596 spanner.Update("Staff", []string{"Name", "ID", "RawBytes"}, []interface{}{"Jack", 1, []byte{0x01, 0x00, 0x01}}), 597 }) 598 if err != nil { 599 t.Fatalf("Updating rows: %v", err) 600 } 601 602 // Prepare the sample tables from the Cloud Spanner docs. 603 // https://cloud.google.com/spanner/docs/query-syntax#appendix-a-examples-with-sample-data 604 err = updateDDL(t, adminClient, 605 // TODO: Roster, TeamMascot when we implement JOINs. 606 `CREATE TABLE PlayerStats ( 607 LastName STRING(MAX), 608 OpponentID INT64, 609 PointsScored INT64, 610 ) PRIMARY KEY (LastName, OpponentID)`, // TODO: is this right? 611 // JoinFoo are from https://cloud.google.com/spanner/docs/query-syntax#join_types. 612 // They aren't consistently named in the docs. 613 `CREATE TABLE JoinA ( w INT64, x STRING(MAX) ) PRIMARY KEY (w, x)`, 614 `CREATE TABLE JoinB ( y INT64, z STRING(MAX) ) PRIMARY KEY (y, z)`, 615 `CREATE TABLE JoinC ( x INT64, y STRING(MAX) ) PRIMARY KEY (x, y)`, 616 `CREATE TABLE JoinD ( x INT64, z STRING(MAX) ) PRIMARY KEY (x, z)`, 617 `CREATE TABLE JoinE ( w INT64, x STRING(MAX) ) PRIMARY KEY (w, x)`, 618 `CREATE TABLE JoinF ( y INT64, z STRING(MAX) ) PRIMARY KEY (y, z)`, 619 // Some other test tables. 620 `CREATE TABLE SomeStrings ( i INT64, str STRING(MAX) ) PRIMARY KEY (i)`, 621 ) 622 if err != nil { 623 t.Fatalf("Creating sample tables: %v", err) 624 } 625 _, err = client.Apply(ctx, []*spanner.Mutation{ 626 spanner.Insert("PlayerStats", []string{"LastName", "OpponentID", "PointsScored"}, []interface{}{"Adams", 51, 3}), 627 spanner.Insert("PlayerStats", []string{"LastName", "OpponentID", "PointsScored"}, []interface{}{"Buchanan", 77, 0}), 628 spanner.Insert("PlayerStats", []string{"LastName", "OpponentID", "PointsScored"}, []interface{}{"Coolidge", 77, 1}), 629 spanner.Insert("PlayerStats", []string{"LastName", "OpponentID", "PointsScored"}, []interface{}{"Adams", 52, 4}), 630 spanner.Insert("PlayerStats", []string{"LastName", "OpponentID", "PointsScored"}, []interface{}{"Buchanan", 50, 13}), 631 632 spanner.Insert("JoinA", []string{"w", "x"}, []interface{}{1, "a"}), 633 spanner.Insert("JoinA", []string{"w", "x"}, []interface{}{2, "b"}), 634 spanner.Insert("JoinA", []string{"w", "x"}, []interface{}{3, "c"}), 635 spanner.Insert("JoinA", []string{"w", "x"}, []interface{}{3, "d"}), 636 637 spanner.Insert("JoinB", []string{"y", "z"}, []interface{}{2, "k"}), 638 spanner.Insert("JoinB", []string{"y", "z"}, []interface{}{3, "m"}), 639 spanner.Insert("JoinB", []string{"y", "z"}, []interface{}{3, "n"}), 640 spanner.Insert("JoinB", []string{"y", "z"}, []interface{}{4, "p"}), 641 642 // JoinC and JoinD have the same contents as JoinA and JoinB; they have different column names. 643 spanner.Insert("JoinC", []string{"x", "y"}, []interface{}{1, "a"}), 644 spanner.Insert("JoinC", []string{"x", "y"}, []interface{}{2, "b"}), 645 spanner.Insert("JoinC", []string{"x", "y"}, []interface{}{3, "c"}), 646 spanner.Insert("JoinC", []string{"x", "y"}, []interface{}{3, "d"}), 647 648 spanner.Insert("JoinD", []string{"x", "z"}, []interface{}{2, "k"}), 649 spanner.Insert("JoinD", []string{"x", "z"}, []interface{}{3, "m"}), 650 spanner.Insert("JoinD", []string{"x", "z"}, []interface{}{3, "n"}), 651 spanner.Insert("JoinD", []string{"x", "z"}, []interface{}{4, "p"}), 652 653 // JoinE and JoinF are used in the CROSS JOIN test. 654 spanner.Insert("JoinE", []string{"w", "x"}, []interface{}{1, "a"}), 655 spanner.Insert("JoinE", []string{"w", "x"}, []interface{}{2, "b"}), 656 657 spanner.Insert("JoinF", []string{"y", "z"}, []interface{}{2, "c"}), 658 spanner.Insert("JoinF", []string{"y", "z"}, []interface{}{3, "d"}), 659 660 spanner.Insert("SomeStrings", []string{"i", "str"}, []interface{}{0, "afoo"}), 661 spanner.Insert("SomeStrings", []string{"i", "str"}, []interface{}{1, "abar"}), 662 spanner.Insert("SomeStrings", []string{"i", "str"}, []interface{}{2, nil}), 663 spanner.Insert("SomeStrings", []string{"i", "str"}, []interface{}{3, "bbar"}), 664 }) 665 if err != nil { 666 t.Fatalf("Inserting sample data: %v", err) 667 } 668 669 // Do some complex queries. 670 tests := []struct { 671 q string 672 params map[string]interface{} 673 want [][]interface{} 674 }{ 675 { 676 `SELECT 17, "sweet", TRUE AND FALSE, NULL, B"hello"`, 677 nil, 678 [][]interface{}{{int64(17), "sweet", false, nil, []byte("hello")}}, 679 }, 680 // Check handling of NULL values for the IS operator. 681 // There was a bug that returned errors for some of these cases. 682 { 683 `SELECT @x IS TRUE, @x IS NOT TRUE, @x IS FALSE, @x IS NOT FALSE, @x IS NULL, @x IS NOT NULL`, 684 map[string]interface{}{"x": (*bool)(nil)}, 685 [][]interface{}{ 686 {false, true, false, true, true, false}, 687 }, 688 }, 689 // Check handling of bools that might be NULL. 690 // There was a bug where logical operators always returned true/false. 691 { 692 `SELECT @x, NOT @x, @x AND TRUE, @x AND FALSE, @x OR TRUE, @x OR FALSE`, 693 map[string]interface{}{"x": (*bool)(nil)}, 694 [][]interface{}{ 695 // At the time of writing (9 Oct 2020), the docs are wrong for `NULL AND FALSE`; 696 // the production Spanner returns FALSE, which is what we match. 697 {nil, nil, nil, false, true, nil}, 698 }, 699 }, 700 { 701 `SELECT Name FROM Staff WHERE Cool`, 702 nil, 703 [][]interface{}{{"Teal'c"}}, 704 }, 705 { 706 `SELECT ID FROM Staff WHERE Cool IS NOT NULL ORDER BY ID DESC`, 707 nil, 708 [][]interface{}{{int64(4)}, {int64(3)}, {int64(2)}}, 709 }, 710 { 711 `SELECT Name, Tenure FROM Staff WHERE Cool IS NULL OR Cool ORDER BY Name LIMIT 2`, 712 nil, 713 [][]interface{}{ 714 {"George", int64(6)}, 715 {"Jack", int64(10)}, 716 }, 717 }, 718 { 719 `SELECT Name, ID + 100 FROM Staff WHERE @min <= Tenure AND Tenure < @lim ORDER BY Cool, Name DESC LIMIT @numResults`, 720 map[string]interface{}{"min": 9, "lim": 11, "numResults": 100}, 721 [][]interface{}{ 722 {"Jack", int64(101)}, 723 {"Sam", int64(103)}, 724 }, 725 }, 726 { 727 // Expression in SELECT list. 728 `SELECT Name, Cool IS NOT NULL FROM Staff WHERE Tenure/2 > 4 ORDER BY NOT Cool, Name`, 729 nil, 730 [][]interface{}{ 731 {"Jack", false}, // Jack has NULL Cool (NULLs always come first in orderings) 732 {"Daniel", true}, // Daniel has Cool==true 733 {"Sam", true}, // Sam has Cool==false 734 }, 735 }, 736 { 737 `SELECT Name, Height FROM Staff ORDER BY Height DESC LIMIT 2`, 738 nil, 739 [][]interface{}{ 740 {"Teal'c", 1.91}, 741 {"Jack", 1.85}, 742 }, 743 }, 744 { 745 `SELECT Name FROM Staff WHERE Name LIKE "J%k" OR Name LIKE "_am"`, 746 nil, 747 [][]interface{}{ 748 {"Jack"}, 749 {"Sam"}, 750 }, 751 }, 752 { 753 `SELECT Name, Height FROM Staff WHERE Height BETWEEN @min AND @max ORDER BY Height DESC`, 754 map[string]interface{}{"min": 1.75, "max": 1.85}, 755 [][]interface{}{ 756 {"Jack", 1.85}, 757 {"Daniel", 1.83}, 758 {"Sam", 1.75}, 759 }, 760 }, 761 { 762 `SELECT COUNT(*) FROM Staff WHERE Name < "T"`, 763 nil, 764 [][]interface{}{ 765 {int64(4)}, 766 }, 767 }, 768 { 769 // Check that aggregation still works for the empty set. 770 `SELECT COUNT(*) FROM Staff WHERE Name = "Nobody"`, 771 nil, 772 [][]interface{}{ 773 {int64(0)}, 774 }, 775 }, 776 { 777 `SELECT * FROM Staff WHERE Name LIKE "S%"`, 778 nil, 779 [][]interface{}{ 780 // These are returned in table column order, based on the appearance in the DDL. 781 // Our internal implementation sorts the primary key columns first, 782 // but that should not become visible via SELECT *. 783 {int64(9), int64(3), "Sam", false, 1.75, nil, nil, nil}, 784 }, 785 }, 786 { 787 // Exactly the same as the previous, except with a redundant ORDER BY clause. 788 `SELECT * FROM Staff WHERE Name LIKE "S%" ORDER BY Name`, 789 nil, 790 [][]interface{}{ 791 {int64(9), int64(3), "Sam", false, 1.75, nil, nil, nil}, 792 }, 793 }, 794 { 795 `SELECT Name FROM Staff WHERE FirstSeen >= @min`, 796 map[string]interface{}{"min": civil.Date{Year: 1996, Month: 1, Day: 1}}, 797 [][]interface{}{ 798 {"George"}, 799 }, 800 }, 801 { 802 `SELECT RawBytes FROM Staff WHERE RawBytes IS NOT NULL`, 803 nil, 804 [][]interface{}{ 805 {[]byte("\x01\x00\x01")}, 806 }, 807 }, 808 { 809 // The keyword "To" needs quoting in queries. 810 // Check coercion of comparison operator literal args too. 811 "SELECT COUNT(*) FROM Staff WHERE `To` > '2000-01-01T00:00:00Z'", 812 nil, 813 [][]interface{}{ 814 {int64(1)}, 815 }, 816 }, 817 { 818 `SELECT DISTINCT Cool, Tenure > 8 FROM Staff ORDER BY Cool`, 819 nil, 820 [][]interface{}{ 821 // The non-distinct results are 822 // [[false true] [<nil> false] [<nil> true] [false true] [true false]] 823 {nil, false}, 824 {nil, true}, 825 {false, true}, 826 {true, false}, 827 }, 828 }, 829 { 830 `SELECT Name FROM Staff WHERE ID IN UNNEST(@ids)`, 831 map[string]interface{}{"ids": []int64{3, 1}}, 832 [][]interface{}{ 833 {"Jack"}, 834 {"Sam"}, 835 }, 836 }, 837 // From https://cloud.google.com/spanner/docs/query-syntax#group-by-clause_1: 838 { 839 // TODO: Ordering matters? Our implementation sorts by the GROUP BY key, 840 // but nothing documented seems to guarantee that. 841 `SELECT LastName, SUM(PointsScored) FROM PlayerStats GROUP BY LastName`, 842 nil, 843 [][]interface{}{ 844 {"Adams", int64(7)}, 845 {"Buchanan", int64(13)}, 846 {"Coolidge", int64(1)}, 847 }, 848 }, 849 { 850 // Another GROUP BY, but referring to an alias. 851 // Group by ID oddness, SUM over Tenure. 852 `SELECT ID&0x01 AS odd, SUM(Tenure) FROM Staff GROUP BY odd`, 853 nil, 854 [][]interface{}{ 855 {int64(0), int64(19)}, // Daniel(ID=2, Tenure=11), Teal'c(ID=4, Tenure=8) 856 {int64(1), int64(25)}, // Jack(ID=1, Tenure=10), Sam(ID=3, Tenure=9), George(ID=5, Tenure=6) 857 }, 858 }, 859 { 860 `SELECT MAX(Name) FROM Staff WHERE Name < @lim`, 861 map[string]interface{}{"lim": "Teal'c"}, 862 [][]interface{}{ 863 {"Sam"}, 864 }, 865 }, 866 { 867 `SELECT MAX(Name) FROM Staff WHERE Cool = @p1 LIMIT 1`, 868 map[string]interface{}{"p1": true}, 869 [][]interface{}{ 870 {"Teal'c"}, 871 }, 872 }, 873 { 874 `SELECT MIN(Name) FROM Staff`, 875 nil, 876 [][]interface{}{ 877 {"Daniel"}, 878 }, 879 }, 880 { 881 `SELECT ARRAY_AGG(Cool) FROM Staff`, 882 nil, 883 [][]interface{}{ 884 // Daniel, George (NULL), Jack (NULL), Sam, Teal'c 885 {[]interface{}{false, nil, nil, false, true}}, 886 }, 887 }, 888 // SELECT with aliases. 889 { 890 // Aliased table. 891 `SELECT s.Name FROM Staff AS s WHERE s.ID = 3 ORDER BY s.Tenure`, 892 nil, 893 [][]interface{}{ 894 {"Sam"}, 895 }, 896 }, 897 { 898 // Aliased expression. 899 `SELECT Name AS nom FROM Staff WHERE ID < 4 ORDER BY nom`, 900 nil, 901 [][]interface{}{ 902 {"Daniel"}, 903 {"Jack"}, 904 {"Sam"}, 905 }, 906 }, 907 // Joins. 908 { 909 `SELECT * FROM JoinA INNER JOIN JoinB ON JoinA.w = JoinB.y ORDER BY w, x, y, z`, 910 nil, 911 [][]interface{}{ 912 {int64(2), "b", int64(2), "k"}, 913 {int64(3), "c", int64(3), "m"}, 914 {int64(3), "c", int64(3), "n"}, 915 {int64(3), "d", int64(3), "m"}, 916 {int64(3), "d", int64(3), "n"}, 917 }, 918 }, 919 { 920 `SELECT * FROM JoinE CROSS JOIN JoinF ORDER BY w, x, y, z`, 921 nil, 922 [][]interface{}{ 923 {int64(1), "a", int64(2), "c"}, 924 {int64(1), "a", int64(3), "d"}, 925 {int64(2), "b", int64(2), "c"}, 926 {int64(2), "b", int64(3), "d"}, 927 }, 928 }, 929 { 930 `SELECT * FROM JoinA LEFT OUTER JOIN JoinB AS B ON JoinA.w = B.y ORDER BY w, x, y, z`, 931 nil, 932 [][]interface{}{ 933 {int64(1), "a", nil, nil}, 934 {int64(2), "b", int64(2), "k"}, 935 {int64(3), "c", int64(3), "m"}, 936 {int64(3), "c", int64(3), "n"}, 937 {int64(3), "d", int64(3), "m"}, 938 {int64(3), "d", int64(3), "n"}, 939 }, 940 }, 941 { 942 // Same as the previous, but using a USING clause instead of an ON clause. 943 `SELECT * FROM JoinC LEFT OUTER JOIN JoinD USING (x) ORDER BY x, y, z`, 944 nil, 945 [][]interface{}{ 946 {int64(1), "a", nil}, 947 {int64(2), "b", "k"}, 948 {int64(3), "c", "m"}, 949 {int64(3), "c", "n"}, 950 {int64(3), "d", "m"}, 951 {int64(3), "d", "n"}, 952 }, 953 }, 954 { 955 // Same as in docs, but with a weird ORDER BY clause to match the row ordering. 956 `SELECT * FROM JoinA RIGHT OUTER JOIN JoinB AS B ON JoinA.w = B.y ORDER BY w IS NULL, w, x, y, z`, 957 nil, 958 [][]interface{}{ 959 {int64(2), "b", int64(2), "k"}, 960 {int64(3), "c", int64(3), "m"}, 961 {int64(3), "c", int64(3), "n"}, 962 {int64(3), "d", int64(3), "m"}, 963 {int64(3), "d", int64(3), "n"}, 964 {nil, nil, int64(4), "p"}, 965 }, 966 }, 967 { 968 `SELECT * FROM JoinC RIGHT OUTER JOIN JoinD USING (x) ORDER BY x, y, z`, 969 nil, 970 [][]interface{}{ 971 {int64(2), "b", "k"}, 972 {int64(3), "c", "m"}, 973 {int64(3), "c", "n"}, 974 {int64(3), "d", "m"}, 975 {int64(3), "d", "n"}, 976 {int64(4), nil, "p"}, 977 }, 978 }, 979 // Regression test for aggregating no rows; it used to return an empty row. 980 // https://github.com/googleapis/google-cloud-go/issues/2793 981 { 982 `SELECT Cool, ARRAY_AGG(Name) FROM Staff WHERE Name > "zzz" GROUP BY Cool`, 983 nil, 984 nil, 985 }, 986 // Regression test for evaluating `IN` incorrectly using ==. 987 // https://github.com/googleapis/google-cloud-go/issues/2458 988 { 989 `SELECT COUNT(*) FROM Staff WHERE RawBytes IN UNNEST(@arg)`, 990 map[string]interface{}{ 991 "arg": [][]byte{ 992 {0x02}, 993 {0x01, 0x00, 0x01}, // only one present 994 }, 995 }, 996 [][]interface{}{ 997 {int64(1)}, 998 }, 999 }, 1000 // Regression test for mishandling NULLs with LIKE operator. 1001 { 1002 `SELECT i, str FROM SomeStrings WHERE str LIKE "%bar"`, 1003 nil, 1004 [][]interface{}{ 1005 // Does not include [0, "afoo"] or [2, nil]. 1006 {int64(1), "abar"}, 1007 {int64(3), "bbar"}, 1008 }, 1009 }, 1010 { 1011 `SELECT i, str FROM SomeStrings WHERE str NOT LIKE "%bar"`, 1012 nil, 1013 [][]interface{}{ 1014 // Does not include [1, "abar"], [2, nil] or [3, "bbar"]. 1015 {int64(0), "afoo"}, 1016 }, 1017 }, 1018 // Regression test for ORDER BY combined with SELECT aliases. 1019 { 1020 `SELECT Name AS nom FROM Staff ORDER BY ID LIMIT 2`, 1021 nil, 1022 [][]interface{}{ 1023 {"Jack"}, 1024 {"Daniel"}, 1025 }, 1026 }, 1027 } 1028 var failures int 1029 for _, test := range tests { 1030 t.Logf("Testing query: %s", test.q) 1031 stmt := spanner.NewStatement(test.q) 1032 stmt.Params = test.params 1033 1034 ri = client.Single().Query(ctx, stmt) 1035 all, err := slurpRows(t, ri) 1036 if err != nil { 1037 t.Errorf("Query(%q, %v): %v", test.q, test.params, err) 1038 failures++ 1039 continue 1040 } 1041 if !reflect.DeepEqual(all, test.want) { 1042 t.Errorf("Results from Query(%q, %v) are wrong.\n got %v\nwant %v", test.q, test.params, all, test.want) 1043 failures++ 1044 } 1045 } 1046 if failures > 0 { 1047 t.Logf("%d queries failed", failures) 1048 } 1049} 1050 1051func dropTable(t *testing.T, adminClient *dbadmin.DatabaseAdminClient, table string) error { 1052 t.Helper() 1053 err := updateDDL(t, adminClient, "DROP TABLE "+table) 1054 // NotFound is an acceptable failure mode here. 1055 if st, _ := status.FromError(err); st.Code() == codes.NotFound { 1056 err = nil 1057 } 1058 if err != nil { 1059 return fmt.Errorf("dropping old table %q: %v", table, err) 1060 } 1061 return nil 1062} 1063 1064func updateDDL(t *testing.T, adminClient *dbadmin.DatabaseAdminClient, statements ...string) error { 1065 t.Helper() 1066 ctx := context.Background() 1067 t.Logf("DDL update: %q", statements) 1068 op, err := adminClient.UpdateDatabaseDdl(ctx, &dbadminpb.UpdateDatabaseDdlRequest{ 1069 Database: dbName(), 1070 Statements: statements, 1071 }) 1072 if err != nil { 1073 t.Fatalf("Starting DDL update: %v", err) 1074 } 1075 return op.Wait(ctx) 1076} 1077 1078func mustSlurpRows(t *testing.T, ri *spanner.RowIterator) [][]interface{} { 1079 t.Helper() 1080 all, err := slurpRows(t, ri) 1081 if err != nil { 1082 t.Fatalf("Reading rows: %v", err) 1083 } 1084 return all 1085} 1086 1087func slurpRows(t *testing.T, ri *spanner.RowIterator) (all [][]interface{}, err error) { 1088 t.Helper() 1089 err = ri.Do(func(r *spanner.Row) error { 1090 var data []interface{} 1091 for i := 0; i < r.Size(); i++ { 1092 var gcv spanner.GenericColumnValue 1093 if err := r.Column(i, &gcv); err != nil { 1094 return err 1095 } 1096 data = append(data, genericValue(t, gcv)) 1097 } 1098 all = append(all, data) 1099 return nil 1100 }) 1101 return 1102} 1103 1104func genericValue(t *testing.T, gcv spanner.GenericColumnValue) interface{} { 1105 t.Helper() 1106 1107 if _, ok := gcv.Value.Kind.(*structpb.Value_NullValue); ok { 1108 return nil 1109 } 1110 if gcv.Type.Code == spannerpb.TypeCode_ARRAY { 1111 var arr []interface{} 1112 for _, v := range gcv.Value.GetListValue().Values { 1113 arr = append(arr, genericValue(t, spanner.GenericColumnValue{ 1114 Type: &spannerpb.Type{Code: gcv.Type.ArrayElementType.Code}, 1115 Value: v, 1116 })) 1117 } 1118 return arr 1119 } 1120 1121 var dst interface{} 1122 switch gcv.Type.Code { 1123 case spannerpb.TypeCode_BOOL: 1124 dst = new(bool) 1125 case spannerpb.TypeCode_INT64: 1126 dst = new(int64) 1127 case spannerpb.TypeCode_FLOAT64: 1128 dst = new(float64) 1129 case spannerpb.TypeCode_TIMESTAMP: 1130 dst = new(time.Time) // TODO: do we need to force to UTC? 1131 case spannerpb.TypeCode_DATE: 1132 dst = new(civil.Date) 1133 case spannerpb.TypeCode_STRING: 1134 dst = new(string) 1135 case spannerpb.TypeCode_BYTES: 1136 dst = new([]byte) 1137 } 1138 if dst == nil { 1139 t.Fatalf("Can't decode Spanner generic column value: %v", gcv.Type) 1140 } 1141 if err := gcv.Decode(dst); err != nil { 1142 t.Fatalf("Decoding %v into %T: %v", gcv, dst, err) 1143 } 1144 return reflect.ValueOf(dst).Elem().Interface() 1145} 1146