| // Copyright 2015 The Vanadium Authors. All rights reserved. |
| // Use of this source code is governed by a BSD-style |
| // license that can be found in the LICENSE file. |
| |
| package nosql_test |
| |
| import ( |
| "errors" |
| "fmt" |
| "reflect" |
| "strings" |
| "testing" |
| "time" |
| |
| "v.io/v23/context" |
| "v.io/v23/query/syncql" |
| "v.io/v23/syncbase" |
| "v.io/v23/syncbase/nosql" |
| "v.io/v23/syncbase/nosql/testdata" |
| "v.io/v23/vdl" |
| "v.io/v23/verror" |
| _ "v.io/x/ref/runtime/factories/generic" |
| tu "v.io/x/ref/services/syncbase/testutil" |
| ) |
| |
| var ctx *context.T |
| var db nosql.Database |
| var cleanup func() |
| |
| // In addition to populating store, populate these arrays to make |
| // specifying the wanted values in the tests easier. |
| type kv struct { |
| key string |
| value *vdl.Value |
| } |
| |
| var customerEntries []kv |
| var numbersEntries []kv |
| var fooEntries []kv |
| var keyIndexDataEntries []kv |
| |
| var t2015 time.Time |
| |
| var t2015_04 time.Time |
| var t2015_04_12 time.Time |
| var t2015_04_12_22 time.Time |
| var t2015_04_12_22_16 time.Time |
| var t2015_04_12_22_16_06 time.Time |
| |
| var t2015_07 time.Time |
| var t2015_07_01 time.Time |
| var t2015_07_01_01_23_45 time.Time |
| |
| var customerTable nosql.Table |
| var numbersTable nosql.Table |
| var fooTable nosql.Table |
| var keyIndexDataTable nosql.Table |
| var bigTable nosql.Table |
| |
| func setup(t *testing.T) { |
| var sName string |
| ctx, sName, cleanup = tu.SetupOrDie(nil) |
| a := tu.CreateApp(t, ctx, syncbase.NewService(sName), "a") |
| db = tu.CreateNoSQLDatabase(t, ctx, a, "db") |
| customerTable = tu.CreateTable(t, ctx, db, "Customer") |
| numbersTable = tu.CreateTable(t, ctx, db, "Numbers") |
| fooTable = tu.CreateTable(t, ctx, db, "Foo") |
| keyIndexDataTable = tu.CreateTable(t, ctx, db, "KeyIndexData") |
| bigTable = tu.CreateTable(t, ctx, db, "BigTable") |
| } |
| |
| func initTables(t *testing.T) { |
| t20150122131101, _ := time.Parse("Jan 2 2006 15:04:05 -0700 MST", "Jan 22 2015 13:11:01 -0800 PST") |
| t20150210161202, _ := time.Parse("Jan 2 2006 15:04:05 -0700 MST", "Feb 10 2015 16:12:02 -0800 PST") |
| t20150311101303, _ := time.Parse("Jan 2 2006 15:04:05 -0700 MST", "Mar 11 2015 10:13:03 -0700 PDT") |
| t20150317111404, _ := time.Parse("Jan 2 2006 15:04:05 -0700 MST", "Mar 17 2015 11:14:04 -0700 PDT") |
| t20150317131505, _ := time.Parse("Jan 2 2006 15:04:05 -0700 MST", "Mar 17 2015 13:15:05 -0700 PDT") |
| t20150412221606, _ := time.Parse("Jan 2 2006 15:04:05 -0700 MST", "Apr 12 2015 22:16:06 -0700 PDT") |
| t20150413141707, _ := time.Parse("Jan 2 2006 15:04:05 -0700 MST", "Apr 13 2015 14:17:07 -0700 PDT") |
| |
| t2015, _ = time.Parse("2006 MST", "2015 PST") |
| |
| t2015_04, _ = time.Parse("Jan 2006 MST", "Apr 2015 PDT") |
| t2015_07, _ = time.Parse("Jan 2006 MST", "Jul 2015 PDT") |
| |
| t2015_04_12, _ = time.Parse("Jan 2 2006 MST", "Apr 12 2015 PDT") |
| t2015_07_01, _ = time.Parse("Jan 2 2006 MST", "Jul 01 2015 PDT") |
| |
| t2015_04_12_22, _ = time.Parse("Jan 2 2006 15 MST", "Apr 12 2015 22 PDT") |
| t2015_04_12_22_16, _ = time.Parse("Jan 2 2006 15:04 MST", "Apr 12 2015 22:16 PDT") |
| t2015_04_12_22_16_06, _ = time.Parse("Jan 2 2006 15:04:05 MST", "Apr 12 2015 22:16:06 PDT") |
| t2015_07_01_01_23_45, _ = time.Parse("Jan 2 2006 15:04:05 MST", "Jul 01 2015 01:23:45 PDT") |
| |
| k := "001" |
| c := testdata.Customer{"John Smith", 1, true, testdata.AddressInfo{"1 Main St.", "Palo Alto", "CA", "94303"}, testdata.CreditReport{Agency: testdata.CreditAgencyEquifax, Report: testdata.AgencyReportEquifaxReport{testdata.EquifaxCreditReport{'A'}}}} |
| customerEntries = append(customerEntries, kv{k, vdl.ValueOf(c)}) |
| if err := customerTable.Put(ctx, k, c); err != nil { |
| t.Fatalf("customerTable.Put() failed: %v", err) |
| } |
| k = "001001" |
| i := testdata.Invoice{1, 1000, t20150122131101, 42, testdata.AddressInfo{"1 Main St.", "Palo Alto", "CA", "94303"}} |
| customerEntries = append(customerEntries, kv{k, vdl.ValueOf(i)}) |
| if err := customerTable.Put(ctx, k, i); err != nil { |
| t.Fatalf("customerTable.Put() failed: %v", err) |
| } |
| |
| k = "001002" |
| i = testdata.Invoice{1, 1003, t20150210161202, 7, testdata.AddressInfo{"2 Main St.", "Palo Alto", "CA", "94303"}} |
| customerEntries = append(customerEntries, kv{k, vdl.ValueOf(i)}) |
| if err := customerTable.Put(ctx, k, i); err != nil { |
| t.Fatalf("customerTable.Put() failed: %v", err) |
| } |
| |
| k = "001003" |
| i = testdata.Invoice{1, 1005, t20150311101303, 88, testdata.AddressInfo{"3 Main St.", "Palo Alto", "CA", "94303"}} |
| customerEntries = append(customerEntries, kv{k, vdl.ValueOf(i)}) |
| if err := customerTable.Put(ctx, k, i); err != nil { |
| t.Fatalf("customerTable.Put() failed: %v", err) |
| } |
| |
| k = "002" |
| c = testdata.Customer{"Bat Masterson", 2, true, testdata.AddressInfo{"777 Any St.", "Collins", "IA", "50055"}, testdata.CreditReport{Agency: testdata.CreditAgencyTransUnion, Report: testdata.AgencyReportTransUnionReport{testdata.TransUnionCreditReport{80}}}} |
| customerEntries = append(customerEntries, kv{k, vdl.ValueOf(c)}) |
| if err := customerTable.Put(ctx, k, c); err != nil { |
| t.Fatalf("customerTable.Put() failed: %v", err) |
| } |
| |
| k = "002001" |
| i = testdata.Invoice{2, 1001, t20150317111404, 166, testdata.AddressInfo{"777 Any St.", "collins", "IA", "50055"}} |
| customerEntries = append(customerEntries, kv{k, vdl.ValueOf(i)}) |
| if err := customerTable.Put(ctx, k, i); err != nil { |
| t.Fatalf("customerTable.Put() failed: %v", err) |
| } |
| |
| k = "002002" |
| i = testdata.Invoice{2, 1002, t20150317131505, 243, testdata.AddressInfo{"888 Any St.", "collins", "IA", "50055"}} |
| customerEntries = append(customerEntries, kv{k, vdl.ValueOf(i)}) |
| if err := customerTable.Put(ctx, k, i); err != nil { |
| t.Fatalf("customerTable.Put() failed: %v", err) |
| } |
| |
| k = "002003" |
| i = testdata.Invoice{2, 1004, t20150412221606, 787, testdata.AddressInfo{"999 Any St.", "collins", "IA", "50055"}} |
| customerEntries = append(customerEntries, kv{k, vdl.ValueOf(i)}) |
| if err := customerTable.Put(ctx, k, i); err != nil { |
| t.Fatalf("customerTable.Put() failed: %v", err) |
| } |
| |
| k = "002004" |
| i = testdata.Invoice{2, 1006, t20150413141707, 88, testdata.AddressInfo{"101010 Any St.", "collins", "IA", "50055"}} |
| customerEntries = append(customerEntries, kv{k, vdl.ValueOf(i)}) |
| if err := customerTable.Put(ctx, k, i); err != nil { |
| t.Fatalf("customerTable.Put() failed: %v", err) |
| } |
| |
| k = "003" |
| c = testdata.Customer{"John Steed", 3, true, testdata.AddressInfo{"100 Queen St.", "New London", "CT", "06320"}, testdata.CreditReport{Agency: testdata.CreditAgencyExperian, Report: testdata.AgencyReportExperianReport{testdata.ExperianCreditReport{testdata.ExperianRatingGood}}}} |
| customerEntries = append(customerEntries, kv{k, vdl.ValueOf(c)}) |
| if err := customerTable.Put(ctx, k, c); err != nil { |
| t.Fatalf("customerTable.Put() failed: %v", err) |
| } |
| |
| k = "001" |
| n := testdata.Numbers{byte(12), uint16(1234), uint32(5678), uint64(999888777666), int16(9876), int32(876543), int64(128), float32(3.14159), float64(2.71828182846), complex64(123.0 + 7.0i), complex128(456.789 + 10.1112i)} |
| numbersEntries = append(numbersEntries, kv{k, vdl.ValueOf(n)}) |
| if err := numbersTable.Put(ctx, k, n); err != nil { |
| t.Fatalf("numbersTable.Put() failed: %v", err) |
| } |
| |
| k = "002" |
| n = testdata.Numbers{byte(9), uint16(99), uint32(999), uint64(9999999), int16(9), int32(99), int64(88), float32(1.41421356237), float64(1.73205080757), complex64(9.87 + 7.65i), complex128(4.32 + 1.0i)} |
| numbersEntries = append(numbersEntries, kv{k, vdl.ValueOf(n)}) |
| if err := numbersTable.Put(ctx, k, n); err != nil { |
| t.Fatalf("numbersTable.Put() failed: %v", err) |
| } |
| |
| k = "003" |
| n = testdata.Numbers{byte(210), uint16(210), uint32(210), uint64(210), int16(210), int32(210), int64(210), float32(210.0), float64(210.0), complex64(210.0 + 0.0i), complex128(210.0 + 0.0i)} |
| numbersEntries = append(numbersEntries, kv{k, vdl.ValueOf(n)}) |
| if err := numbersTable.Put(ctx, k, n); err != nil { |
| t.Fatalf("numbersTable.Put() failed: %v", err) |
| } |
| |
| k = "001" |
| f := testdata.FooType{testdata.BarType{testdata.BazType{"FooBarBaz", testdata.TitleOrValueTypeTitle{"Vice President"}}}} |
| fooEntries = append(fooEntries, kv{k, vdl.ValueOf(f)}) |
| if err := fooTable.Put(ctx, k, f); err != nil { |
| t.Fatalf("fooTable.Put() failed: %v", err) |
| } |
| |
| k = "002" |
| f = testdata.FooType{testdata.BarType{testdata.BazType{"BazBarFoo", testdata.TitleOrValueTypeValue{42}}}} |
| fooEntries = append(fooEntries, kv{k, vdl.ValueOf(f)}) |
| if err := fooTable.Put(ctx, k, f); err != nil { |
| t.Fatalf("fooTable.Put() failed: %v", err) |
| } |
| |
| k = "aaa" |
| kid := testdata.KeyIndexData{ |
| [4]string{"Fee", "Fi", "Fo", "Fum"}, |
| []string{"I", "smell", "the", "blood", "of", "an", "Englishman"}, |
| map[complex128]string{complex(1.1, 2.2): "Be he living, or be he dead"}, |
| map[string]struct{}{"I’ll grind his bones to mix my bread": {}}, |
| } |
| keyIndexDataEntries = append(keyIndexDataEntries, kv{k, vdl.ValueOf(kid)}) |
| if err := keyIndexDataTable.Put(ctx, k, kid); err != nil { |
| t.Fatalf("keyIndexDataTable.Put() failed: %v", err) |
| } |
| |
| for i := 100; i < 301; i++ { |
| k = fmt.Sprintf("%d", i) |
| b := testdata.BigData{k} |
| if err := bigTable.Put(ctx, k, b); err != nil { |
| t.Fatalf("bigTable.Put() failed: %v", err) |
| } |
| } |
| } |
| |
| type execSelectTest struct { |
| query string |
| headers []string |
| r [][]*vdl.Value |
| } |
| |
| type execDeleteTest struct { |
| delQuery string |
| delHeaders []string |
| delResults [][]*vdl.Value |
| selQuery string |
| selHeaders []string |
| selResults [][]*vdl.Value |
| } |
| |
| type preExecFunctionTest struct { |
| query string |
| headers []string |
| } |
| |
| type execSelectErrorTest struct { |
| query string |
| err error |
| } |
| |
| func TestExecSelect(t *testing.T) { |
| setup(t) |
| defer cleanup() |
| initTables(t) |
| basic := []execSelectTest{ |
| { |
| // Select values for all customer records. |
| "select v from Customer where Type(v) like \"%.Customer\"", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[0].value}, |
| []*vdl.Value{customerEntries[4].value}, |
| []*vdl.Value{customerEntries[9].value}, |
| }, |
| }, |
| { |
| // Select values where v.InvoiceNum is nil |
| // Since InvoiceNum does not exists for Customers, |
| // this will return just customers. |
| "select v from Customer where v.InvoiceNum is nil", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[0].value}, |
| []*vdl.Value{customerEntries[4].value}, |
| []*vdl.Value{customerEntries[9].value}, |
| }, |
| }, |
| { |
| // Select values where v.InvoiceNum is nil |
| // or v.Name is nil This will select all customers |
| // with the former and all invoices with the latter. |
| // Hence, all records are returned. |
| "select v from Customer where v.InvoiceNum is nil or v.Name is nil", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[0].value}, |
| []*vdl.Value{customerEntries[1].value}, |
| []*vdl.Value{customerEntries[2].value}, |
| []*vdl.Value{customerEntries[3].value}, |
| []*vdl.Value{customerEntries[4].value}, |
| []*vdl.Value{customerEntries[5].value}, |
| []*vdl.Value{customerEntries[6].value}, |
| []*vdl.Value{customerEntries[7].value}, |
| []*vdl.Value{customerEntries[8].value}, |
| []*vdl.Value{customerEntries[9].value}, |
| }, |
| }, |
| { |
| // Select values where v.InvoiceNum is nil |
| // and v.Name is nil. Expect nothing returned. |
| "select v from Customer where v.InvoiceNum is nil and v.Name is nil", |
| []string{"v"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| // Select values where v.InvoiceNum is not nil |
| // This will return just invoices. |
| "select v from Customer where v.InvoiceNum is not nil", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[1].value}, |
| []*vdl.Value{customerEntries[2].value}, |
| []*vdl.Value{customerEntries[3].value}, |
| []*vdl.Value{customerEntries[5].value}, |
| []*vdl.Value{customerEntries[6].value}, |
| []*vdl.Value{customerEntries[7].value}, |
| []*vdl.Value{customerEntries[8].value}, |
| }, |
| }, |
| { |
| // Select values where v.InvoiceNum is not nil |
| // or v.Name is not nil. All records are returned. |
| "select v from Customer where v.InvoiceNum is not nil or v.Name is not nil", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[0].value}, |
| []*vdl.Value{customerEntries[1].value}, |
| []*vdl.Value{customerEntries[2].value}, |
| []*vdl.Value{customerEntries[3].value}, |
| []*vdl.Value{customerEntries[4].value}, |
| []*vdl.Value{customerEntries[5].value}, |
| []*vdl.Value{customerEntries[6].value}, |
| []*vdl.Value{customerEntries[7].value}, |
| []*vdl.Value{customerEntries[8].value}, |
| []*vdl.Value{customerEntries[9].value}, |
| }, |
| }, |
| { |
| // Select values where v.InvoiceNum is nil and v.Name is not nil. |
| // All customers are returned. |
| "select v from Customer where v.InvoiceNum is nil and v.Name is not nil", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[0].value}, |
| []*vdl.Value{customerEntries[4].value}, |
| []*vdl.Value{customerEntries[9].value}, |
| }, |
| }, |
| { |
| // Select values where v.InvoiceNum is not nil |
| // and v.Name is not nil. Expect nothing returned. |
| "select v from Customer where v.InvoiceNum is not nil and v.Name is not nil", |
| []string{"v"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| // Select keys & values for all customer records. |
| "select k, v from Customer where Type(v) like \"%.Customer\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[0].key), customerEntries[0].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[4].key), customerEntries[4].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[9].key), customerEntries[9].value}, |
| }, |
| }, |
| { |
| // Select keys & names for all customer records. |
| "select k, v.Name from Customer where Type(v) like \"%.Customer\"", |
| []string{"k", "v.Name"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[0].key), vdl.ValueOf("John Smith")}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[4].key), vdl.ValueOf("Bat Masterson")}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[9].key), vdl.ValueOf("John Steed")}, |
| }, |
| }, |
| { |
| // Select both customer and invoice records. |
| // Customer records have Id. |
| // Invoice records have CustId. |
| "select v.Id, v.CustId from Customer", |
| []string{"v.Id", "v.CustId"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(int64(1)), vdl.ValueOf(nil)}, |
| []*vdl.Value{vdl.ValueOf(nil), vdl.ValueOf(int64(1))}, |
| []*vdl.Value{vdl.ValueOf(nil), vdl.ValueOf(int64(1))}, |
| []*vdl.Value{vdl.ValueOf(nil), vdl.ValueOf(int64(1))}, |
| []*vdl.Value{vdl.ValueOf(int64(2)), vdl.ValueOf(nil)}, |
| []*vdl.Value{vdl.ValueOf(nil), vdl.ValueOf(int64(2))}, |
| []*vdl.Value{vdl.ValueOf(nil), vdl.ValueOf(int64(2))}, |
| []*vdl.Value{vdl.ValueOf(nil), vdl.ValueOf(int64(2))}, |
| []*vdl.Value{vdl.ValueOf(nil), vdl.ValueOf(int64(2))}, |
| []*vdl.Value{vdl.ValueOf(int64(3)), vdl.ValueOf(nil)}, |
| }, |
| }, |
| { |
| // Select keys & values fo all invoice records. |
| "select k, v from Customer where Type(v) like \"%.Invoice\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[1].key), customerEntries[1].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[2].key), customerEntries[2].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[3].key), customerEntries[3].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key), customerEntries[5].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key), customerEntries[6].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[7].key), customerEntries[7].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key), customerEntries[8].value}, |
| }, |
| }, |
| { |
| // Select key, cust id, invoice number and amount for $88 invoices. |
| "select k, v.CustId as ID, v.InvoiceNum as InvoiceNumber, v.Amount as Amt from Customer where Type(v) like \"%.Invoice\" and v.Amount = 88", |
| []string{"k", "ID", "InvoiceNumber", "Amt"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[3].key), vdl.ValueOf(int64(1)), vdl.ValueOf(int64(1005)), vdl.ValueOf(int64(88))}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key), vdl.ValueOf(int64(2)), vdl.ValueOf(int64(1006)), vdl.ValueOf(int64(88))}, |
| }, |
| }, |
| { |
| // Select keys & values for all records with a key prefix of "001". |
| "select k, v from Customer where k like \"001%\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[0].key), customerEntries[0].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[1].key), customerEntries[1].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[2].key), customerEntries[2].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[3].key), customerEntries[3].value}, |
| }, |
| }, |
| { |
| // Select keys & values for all records with a key prefix of "002". |
| "select k, v from Customer where k like \"002%\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[4].key), customerEntries[4].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key), customerEntries[5].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key), customerEntries[6].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[7].key), customerEntries[7].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key), customerEntries[8].value}, |
| }, |
| }, |
| { |
| // Select keys & values for all records with NOT key prefix "002%". |
| "select k, v from Customer where k not like \"002%\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[0].key), customerEntries[0].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[1].key), customerEntries[1].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[2].key), customerEntries[2].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[3].key), customerEntries[3].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[9].key), customerEntries[9].value}, |
| }, |
| }, |
| { |
| // Select keys & values for all records with NOT key prefix "002". |
| // Will be optimized to k <> "002" |
| "select k, v from Customer where k not like \"002\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[0].key), customerEntries[0].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[1].key), customerEntries[1].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[2].key), customerEntries[2].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[3].key), customerEntries[3].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key), customerEntries[5].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key), customerEntries[6].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[7].key), customerEntries[7].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key), customerEntries[8].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[9].key), customerEntries[9].value}, |
| }, |
| }, |
| { |
| // Select keys & values for all records with a key prefix of "001". |
| // or a key prefix of "002". |
| "select k, v from Customer where k like \"001%\" or k like \"002%\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[0].key), customerEntries[0].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[1].key), customerEntries[1].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[2].key), customerEntries[2].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[3].key), customerEntries[3].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[4].key), customerEntries[4].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key), customerEntries[5].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key), customerEntries[6].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[7].key), customerEntries[7].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key), customerEntries[8].value}, |
| }, |
| }, |
| { |
| // Select keys & values for all records with a key prefix of "001". |
| // or a key prefix of "002". |
| "select k, v from Customer where k like \"002%\" or k like \"001%\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[0].key), customerEntries[0].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[1].key), customerEntries[1].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[2].key), customerEntries[2].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[3].key), customerEntries[3].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[4].key), customerEntries[4].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key), customerEntries[5].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key), customerEntries[6].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[7].key), customerEntries[7].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key), customerEntries[8].value}, |
| }, |
| }, |
| { |
| // Let's play with whitespace and mixed case. |
| " sElEcT k, v from \n Customer WhErE k lIkE \"002%\" oR k LiKe \"001%\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[0].key), customerEntries[0].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[1].key), customerEntries[1].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[2].key), customerEntries[2].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[3].key), customerEntries[3].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[4].key), customerEntries[4].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key), customerEntries[5].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key), customerEntries[6].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[7].key), customerEntries[7].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key), customerEntries[8].value}, |
| }, |
| }, |
| { |
| // Add in a like clause that accepts all strings. |
| " sElEcT k, v from \n Customer WhErE k lIkE \"002%\" oR k LiKe \"001%\" or k lIkE \"%\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[0].key), customerEntries[0].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[1].key), customerEntries[1].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[2].key), customerEntries[2].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[3].key), customerEntries[3].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[4].key), customerEntries[4].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key), customerEntries[5].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key), customerEntries[6].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[7].key), customerEntries[7].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key), customerEntries[8].value}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[9].key), customerEntries[9].value}, |
| }, |
| }, |
| { |
| // Select id, name for customers whose last name is Masterson. |
| "select v.Id as ID, v.Name as Name from Customer where Type(v) like \"%.Customer\" and v.Name like \"%Masterson\"", |
| []string{"ID", "Name"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(int64(2)), vdl.ValueOf("Bat Masterson")}, |
| }, |
| }, |
| { |
| // Select records where v.Address.City is "Collins" or type is Invoice. |
| "select v from Customer where v.Address.City = \"Collins\" or Type(v) like \"%.Invoice\"", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[1].value}, |
| []*vdl.Value{customerEntries[2].value}, |
| []*vdl.Value{customerEntries[3].value}, |
| []*vdl.Value{customerEntries[4].value}, |
| []*vdl.Value{customerEntries[5].value}, |
| []*vdl.Value{customerEntries[6].value}, |
| []*vdl.Value{customerEntries[7].value}, |
| []*vdl.Value{customerEntries[8].value}, |
| }, |
| }, |
| { |
| // Select records where v.Address.City is "Collins" and v.InvoiceNum is not nil. |
| "select v from Customer where v.Address.City = \"Collins\" and v.InvoiceNum is not nil", |
| []string{"v"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| // Select records where v.Address.City is "Collins" and v.InvoiceNum is nil. |
| "select v from Customer where v.Address.City = \"Collins\" and v.InvoiceNum is nil", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[4].value}, |
| }, |
| }, |
| { |
| // Select customer name for customer Id (i.e., key) "001". |
| "select v.Name as Name from Customer where Type(v) like \"%.Customer\" and k = \"001\"", |
| []string{"Name"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("John Smith")}, |
| }, |
| }, |
| { |
| // Select v where v.Credit.Report.EquifaxReport.Rating = 'A' |
| "select v from Customer where v.Credit.Report.EquifaxReport.Rating = 'A'", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[0].value}, |
| }, |
| }, |
| { |
| // Select v where v.AgencyRating = "Bad" |
| "select v from Customer where v.Credit.Report.EquifaxReport.Rating < 'A' or v.Credit.Report.ExperianReport.Rating = \"Bad\" or v.Credit.Report.TransUnionReport.Rating < 90", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[4].value}, |
| }, |
| }, |
| { |
| // Select records where v.Bar.Baz.Name = "FooBarBaz" |
| "select v from Foo where v.Bar.Baz.Name = \"FooBarBaz\"", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{fooEntries[0].value}, |
| }, |
| }, |
| { |
| // Select records where v.Bar.Baz.TitleOrValue.Value = 42 |
| "select v from Foo where v.Bar.Baz.TitleOrValue.Value = 42", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{fooEntries[1].value}, |
| }, |
| }, |
| { |
| // Select records where v.Bar.Baz.TitleOrValue.Title = "Vice President" |
| "select v from Foo where v.Bar.Baz.TitleOrValue.Title = \"Vice President\"", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{fooEntries[0].value}, |
| }, |
| }, |
| { |
| // Select records where v.Address.City = "Collins" or type is Invoice. |
| // Limit 3 |
| "select v from Customer where v.Address.City = \"Collins\" or Type(v) like \"%.Invoice\" limit 3", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[1].value}, |
| []*vdl.Value{customerEntries[2].value}, |
| []*vdl.Value{customerEntries[3].value}, |
| }, |
| }, |
| { |
| // Select records where v.Address.City = "Collins" or type is Invoice. |
| // Offset 5 |
| "select v from Customer where v.Address.City = \"Collins\" or Type(v) like \"%.Invoice\" offset 5", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[6].value}, |
| []*vdl.Value{customerEntries[7].value}, |
| []*vdl.Value{customerEntries[8].value}, |
| }, |
| }, |
| { |
| // Select records where v.Address.City = "Collins" is "Mountain View". |
| "select v from Customer where v.Address.City = \"Mountain View\"", |
| []string{"v"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| // Select records where v.Address.City = "Collins" or type is Invoice. |
| // Offset 8 |
| "select v from Customer where v.Address.City = \"Collins\" or Type(v) like \"%.Invoice\" offset 8", |
| []string{"v"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| // Select records where v.Address.City = "Collins" or type is Invoice. |
| // Offset 23 |
| "select v from Customer where v.Address.City = \"Collins\" or Type(v) like \"%.Invoice\" offset 23", |
| []string{"v"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| // Select records where v.Address.City = "Collins" is 84 or type is Invoice. |
| // Limit 3 Offset 2 |
| "select v from Customer where v.Address.City = \"Collins\" or Type(v) like \"%.Invoice\" limit 3 offset 2", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[3].value}, |
| []*vdl.Value{customerEntries[4].value}, |
| []*vdl.Value{customerEntries[5].value}, |
| }, |
| }, |
| { |
| // Select records where v.Address.City = "Collins" or (type is Invoice and v.InvoiceNum is not nil). |
| // Limit 3 Offset 2 |
| "select v from Customer where v.Address.City = \"Collins\" or (Type(v) like \"%.Invoice\" and v.InvoiceNum is not nil) limit 3 offset 2", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[3].value}, |
| []*vdl.Value{customerEntries[4].value}, |
| []*vdl.Value{customerEntries[5].value}, |
| }, |
| }, |
| { |
| // Select records where v.Address.City = "Collins" or (type is Invoice and v.InvoiceNum is nil). |
| // Limit 3 Offset 2 |
| "select v from Customer where v.Address.City = \"Collins\" or (Type(v) like \"%.Invoice\" and v.InvoiceNum is nil) limit 3 offset 2", |
| []string{"v"}, |
| [][]*vdl.Value{}, |
| }, |
| // Test functions. |
| { |
| // Select invoice records where date is 2015-03-17 |
| "select v from Customer where Type(v) like \"%.Invoice\" and Year(v.InvoiceDate, \"America/Los_Angeles\") = 2015 and Month(v.InvoiceDate, \"America/Los_Angeles\") = 3 and Day(v.InvoiceDate, \"America/Los_Angeles\") = 17", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[5].value}, |
| []*vdl.Value{customerEntries[6].value}, |
| }, |
| }, |
| { |
| // Now will always be > 2012, so all customer records will be returned. |
| "select v from Customer where Now() > Time(\"2006-01-02 MST\", \"2012-03-17 PDT\")", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{customerEntries[0].value}, |
| []*vdl.Value{customerEntries[1].value}, |
| []*vdl.Value{customerEntries[2].value}, |
| []*vdl.Value{customerEntries[3].value}, |
| []*vdl.Value{customerEntries[4].value}, |
| []*vdl.Value{customerEntries[5].value}, |
| []*vdl.Value{customerEntries[6].value}, |
| []*vdl.Value{customerEntries[7].value}, |
| []*vdl.Value{customerEntries[8].value}, |
| []*vdl.Value{customerEntries[9].value}, |
| }, |
| }, |
| { |
| // Select April 2015 PT invoices. |
| // Note: this wouldn't work for March as daylight saving occurs March 8 |
| // and causes comparisons for those days to be off 1 hour. |
| // It would work to use UTC -- see next test. |
| "select k from Customer where Year(v.InvoiceDate, \"America/Los_Angeles\") = 2015 and Month(v.InvoiceDate, \"America/Los_Angeles\") = 4", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[7].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key)}, |
| }, |
| }, |
| { |
| // Select March 2015 UTC invoices. |
| "select k from Customer where Year(v.InvoiceDate, \"UTC\") = 2015 and Month(v.InvoiceDate, \"UTC\") = 3", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[3].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key)}, |
| }, |
| }, |
| { |
| // Select 2015 UTC invoices. |
| "select k from Customer where Year(v.InvoiceDate, \"UTC\") = 2015", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[1].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[2].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[3].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[7].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key)}, |
| }, |
| }, |
| { |
| // Select the Mar 17 2015 11:14:04 America/Los_Angeles invoice. |
| "select k from Customer where v.InvoiceDate = Time(\"2006-01-02 15:04:05 MST\", \"2015-03-17 11:14:04 PDT\")", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key)}, |
| }, |
| }, |
| { |
| // Select invoices in the minute Mar 17 2015 11:14 America/Los_Angeles invoice. |
| "select k from Customer where Year(v.InvoiceDate, \"America/Los_Angeles\") = 2015 and Month(v.InvoiceDate, \"America/Los_Angeles\") = 3 and Day(v.InvoiceDate, \"America/Los_Angeles\") = 17 and Hour(v.InvoiceDate, \"America/Los_Angeles\") = 11 and Minute(v.InvoiceDate, \"America/Los_Angeles\") = 14", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key)}, |
| }, |
| }, |
| { |
| // Select invoices in the hour Mar 17 2015 11 hundred America/Los_Angeles invoice. |
| "select k from Customer where Year(v.InvoiceDate, \"America/Los_Angeles\") = 2015 and Month(v.InvoiceDate, \"America/Los_Angeles\") = 3 and Day(v.InvoiceDate, \"America/Los_Angeles\") = 17 and Hour(v.InvoiceDate, \"America/Los_Angeles\") = 11", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key)}, |
| }, |
| }, |
| { |
| // Select invoices on the day Mar 17 2015 America/Los_Angeles invoice. |
| "select k from Customer where Year(v.InvoiceDate, \"America/Los_Angeles\") = 2015 and Month(v.InvoiceDate, \"America/Los_Angeles\") = 3 and Day(v.InvoiceDate, \"America/Los_Angeles\") = 17", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key)}, |
| }, |
| }, |
| // Test string functions in where clause. |
| { |
| // Select invoices shipped to Any street -- using Lowercase. |
| "select k from Customer where Type(v) like \"%.Invoice\" and Lowercase(v.ShipTo.Street) like \"%any%\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[7].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key)}, |
| }, |
| }, |
| { |
| // Select invoices shipped to Any street -- using Uppercase. |
| "select k from Customer where Type(v) like \"%.Invoice\" and Uppercase(v.ShipTo.Street) like \"%ANY%\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(customerEntries[5].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[6].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[7].key)}, |
| []*vdl.Value{vdl.ValueOf(customerEntries[8].key)}, |
| }, |
| }, |
| // Select clause functions. |
| // Time function |
| { |
| "select Time(\"2006-01-02 MST\", \"2015-07-01 PDT\") from Customer", |
| []string{"Time"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(t2015_07_01)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01)}, |
| }, |
| }, |
| // Time function |
| { |
| "select Time(\"2006-01-02 15:04:05 MST\", \"2015-07-01 01:23:45 PDT\") from Customer", |
| []string{"Time"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(t2015_07_01_01_23_45)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01_01_23_45)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01_01_23_45)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01_01_23_45)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01_01_23_45)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01_01_23_45)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01_01_23_45)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01_01_23_45)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01_01_23_45)}, |
| []*vdl.Value{vdl.ValueOf(t2015_07_01_01_23_45)}, |
| }, |
| }, |
| // Lowercase function |
| { |
| "select Lowercase(v.Name) as name from Customer where Type(v) like \"%.Customer\"", |
| []string{"name"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("john smith")}, |
| []*vdl.Value{vdl.ValueOf("bat masterson")}, |
| []*vdl.Value{vdl.ValueOf("john steed")}, |
| }, |
| }, |
| // Uppercase function |
| { |
| "select Uppercase(v.Name) as NAME from Customer where Type(v) like \"%.Customer\"", |
| []string{"NAME"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("JOHN SMITH")}, |
| []*vdl.Value{vdl.ValueOf("BAT MASTERSON")}, |
| []*vdl.Value{vdl.ValueOf("JOHN STEED")}, |
| }, |
| }, |
| // Second function |
| { |
| "select k, Second(v.InvoiceDate, \"America/Los_Angeles\") from Customer where Type(v) like \"%.Invoice\" and k = \"002003\"", |
| []string{ |
| "k", |
| "Second", |
| }, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002003"), vdl.ValueOf(int64(6))}, |
| }, |
| }, |
| // Minute function |
| { |
| "select k, Minute(v.InvoiceDate, \"America/Los_Angeles\") from Customer where Type(v) like \"%.Invoice\" and k = \"002003\"", |
| []string{ |
| "k", |
| "Minute", |
| }, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002003"), vdl.ValueOf(int64(16))}, |
| }, |
| }, |
| // Hour function |
| { |
| "select k, Hour(v.InvoiceDate, \"America/Los_Angeles\") from Customer where Type(v) like \"%.Invoice\" and k = \"002003\"", |
| []string{ |
| "k", |
| "Hour", |
| }, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002003"), vdl.ValueOf(int64(22))}, |
| }, |
| }, |
| // Day function |
| { |
| "select k, Day(v.InvoiceDate, \"America/Los_Angeles\") from Customer where Type(v) like \"%.Invoice\" and k = \"002003\"", |
| []string{ |
| "k", |
| "Day", |
| }, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002003"), vdl.ValueOf(int64(12))}, |
| }, |
| }, |
| // Month function |
| { |
| "select k, Month(v.InvoiceDate, \"America/Los_Angeles\") from Customer where Type(v) like \"%.Invoice\" and k = \"002003\"", |
| []string{ |
| "k", |
| "Month", |
| }, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002003"), vdl.ValueOf(int64(4))}, |
| }, |
| }, |
| // Year function |
| { |
| "select k, Year(v.InvoiceDate, \"America/Los_Angeles\") from Customer where Type(v) like \"%.Invoice\" and k = \"001001\"", |
| []string{ |
| "k", |
| "Year", |
| }, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001001"), vdl.ValueOf(int64(2015))}, |
| }, |
| }, |
| // Nested functions |
| { |
| "select Year(Time(\"2006-01-02 15:04:05 MST\", \"2015-07-01 01:23:45 PDT\"), \"America/Los_Angeles\") from Customer where Type(v) like \"%.Invoice\" and k = \"001001\"", |
| []string{"Year"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(int64(2015))}, |
| }, |
| }, |
| // Bad arg to function. Expression is false. |
| { |
| "select v from Customer where Type(v) like \"%.Invoice\" and Day(v.InvoiceDate, v.Foo) = v.InvoiceDate", |
| []string{"v"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| // Test that all numeric types can compare to an uint64 |
| "select v from Numbers where v.Ui64 = v.B and v.Ui64 = v.Ui16 and v.Ui64 = v.Ui32 and v.Ui64 = v.F64 and v.Ui64 = v.I16 and v.Ui64 = v.I32 and v.Ui64 = v.I64 and v.Ui64 = v.F32 and v.Ui64 = v.C64 and v.Ui64 = v.C128", |
| []string{"v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{numbersEntries[2].value}, |
| }, |
| }, |
| { |
| // array, list, map, set |
| "select v.A[2], v.L[6], v.M[Complex(1.1, 2.2)], v.S[\"I’ll grind his bones to mix my bread\"] from KeyIndexData", |
| []string{"v.A[2]", "v.L[6]", "v.M[Complex]", "v.S[I’ll grind his bones to mix my bread]"}, |
| [][]*vdl.Value{[]*vdl.Value{ |
| vdl.ValueOf("Fo"), |
| vdl.ValueOf("Englishman"), |
| vdl.ValueOf("Be he living, or be he dead"), |
| vdl.ValueOf(true), |
| }}, |
| }, |
| { |
| "select k, v.Key from BigTable where k < \"101\" or k = \"200\" or k like \"300%\"", |
| []string{"k", "v.Key"}, |
| [][]*vdl.Value{svPair("100"), svPair("200"), svPair("300")}, |
| }, |
| { |
| "select k, v.Key from BigTable where k like \"10_\" or k like \"20_\"", |
| []string{"k", "v.Key"}, |
| [][]*vdl.Value{ |
| svPair("100"), |
| svPair("101"), |
| svPair("102"), |
| svPair("103"), |
| svPair("104"), |
| svPair("105"), |
| svPair("106"), |
| svPair("107"), |
| svPair("108"), |
| svPair("109"), |
| svPair("200"), |
| svPair("201"), |
| svPair("202"), |
| svPair("203"), |
| svPair("204"), |
| svPair("205"), |
| svPair("206"), |
| svPair("207"), |
| svPair("208"), |
| svPair("209"), |
| }, |
| }, |
| { |
| "select k, v.Key from BigTable where k like \"_%9\"", |
| []string{"k", "v.Key"}, |
| [][]*vdl.Value{ |
| svPair("109"), |
| svPair("119"), |
| svPair("129"), |
| svPair("139"), |
| svPair("149"), |
| svPair("159"), |
| svPair("169"), |
| svPair("179"), |
| svPair("189"), |
| svPair("199"), |
| svPair("209"), |
| svPair("219"), |
| svPair("229"), |
| svPair("239"), |
| svPair("249"), |
| svPair("259"), |
| svPair("269"), |
| svPair("279"), |
| svPair("289"), |
| svPair("299"), |
| }, |
| }, |
| { |
| "select k, v.Key from BigTable where k like \"__0\"", |
| []string{"k", "v.Key"}, |
| [][]*vdl.Value{ |
| svPair("100"), |
| svPair("110"), |
| svPair("120"), |
| svPair("130"), |
| svPair("140"), |
| svPair("150"), |
| svPair("160"), |
| svPair("170"), |
| svPair("180"), |
| svPair("190"), |
| svPair("200"), |
| svPair("210"), |
| svPair("220"), |
| svPair("230"), |
| svPair("240"), |
| svPair("250"), |
| svPair("260"), |
| svPair("270"), |
| svPair("280"), |
| svPair("290"), |
| svPair("300"), |
| }, |
| }, |
| { |
| "select k, v.Key from BigTable where k like \"10%\" or k like \"20%\" or k like \"30%\"", |
| []string{"k", "v.Key"}, |
| [][]*vdl.Value{ |
| svPair("100"), |
| svPair("101"), |
| svPair("102"), |
| svPair("103"), |
| svPair("104"), |
| svPair("105"), |
| svPair("106"), |
| svPair("107"), |
| svPair("108"), |
| svPair("109"), |
| svPair("200"), |
| svPair("201"), |
| svPair("202"), |
| svPair("203"), |
| svPair("204"), |
| svPair("205"), |
| svPair("206"), |
| svPair("207"), |
| svPair("208"), |
| svPair("209"), |
| svPair("300"), |
| }, |
| }, |
| { |
| "select k, v.Key from BigTable where k like \"1__\" and k like \"_2_\" and k like \"__3\"", |
| []string{"k", "v.Key"}, |
| [][]*vdl.Value{svPair("123")}, |
| }, |
| { |
| "select k, v.Key from BigTable where (k > \"100\" and k < \"103\") or (k > \"205\" and k < \"208\")", |
| []string{"k", "v.Key"}, |
| [][]*vdl.Value{ |
| svPair("101"), |
| svPair("102"), |
| svPair("206"), |
| svPair("207"), |
| }, |
| }, |
| { |
| "select k, v.Key from BigTable where k <= \"100\" or k = \"101\" or k >= \"300\" or (k <> \"299\" and k not like \"300\" and k >= \"298\")", |
| []string{"k", "v.Key"}, |
| [][]*vdl.Value{ |
| svPair("100"), |
| svPair("101"), |
| svPair("298"), |
| svPair("300"), |
| }, |
| }, |
| { |
| "select k, v.Key from BigTable where k like \"1%\" and k like \"%9\"", |
| []string{"k", "v.Key"}, |
| [][]*vdl.Value{ |
| svPair("109"), |
| svPair("119"), |
| svPair("129"), |
| svPair("139"), |
| svPair("149"), |
| svPair("159"), |
| svPair("169"), |
| svPair("179"), |
| svPair("189"), |
| svPair("199"), |
| }, |
| }, |
| { |
| "select k, v.Key from BigTable where k like \"3%\" and k like \"30%\" and k like \"300%\"", |
| []string{"k", "v.Key"}, |
| [][]*vdl.Value{svPair("300")}, |
| }, |
| } |
| |
| for _, test := range basic { |
| headers, rs, err := db.Exec(ctx, test.query) |
| if err != nil { |
| t.Errorf("query: %s; got %v, want nil", test.query, err) |
| } else { |
| // Collect results. |
| r := [][]*vdl.Value{} |
| for rs.Advance() { |
| r = append(r, rs.Result()) |
| } |
| if !reflect.DeepEqual(test.r, r) { |
| t.Errorf("query: %s; got %v, want %v", test.query, r, test.r) |
| } |
| if !reflect.DeepEqual(test.headers, headers) { |
| t.Errorf("query: %s; got %v, want %v", test.query, headers, test.headers) |
| } |
| } |
| } |
| } |
| |
| func TestExecDelete(t *testing.T) { |
| basic := []execDeleteTest{ |
| { |
| // Delete all k/v pairs in the customer table. |
| "delete from Customer", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(10)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| // Delete Customer type k/v pairs. |
| "delete from Customer where Type(v) like \"%.Customer\"", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(3)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| }, |
| }, |
| { |
| // Delete non-existant k/v pair. |
| "delete from Customer where k = \"foo\"", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(0)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // Delete k/v pairs where v.InvoiceNum is nil |
| // Since InvoiceNum does not exist for Customers, |
| // this will delete all customer records. |
| "delete from Customer where v.InvoiceNum is nil", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(3)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| }, |
| }, |
| { |
| // Delete values where v.InvoiceNum is nil |
| // or v.Name is nil. This will deleteall customers |
| // with the former and all invoices with the latter. |
| // Hence, all k/v paris will be deleted. |
| "delete from Customer where v.InvoiceNum is nil or v.Name is nil", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(10)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| // Delete where v.InvoiceNum is nil AND v.Name is nil. |
| // Nothing should be deleted. |
| "delete from Customer where v.InvoiceNum is nil and v.Name is nil", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(0)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // Delete where v.InvoiceNum is not nil |
| // This will delete all invoices. |
| "delete from Customer where v.InvoiceNum is not nil", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(7)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // Delete where v.InvoiceNum is not nil |
| // or v.Name is not nil. All records are deleted. |
| "delete from Customer where v.InvoiceNum is not nil or v.Name is not nil", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(10)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| // Delete where v.InvoiceNum is nil and v.Name is not nil. |
| // All customers are deleted. |
| "delete from Customer where v.InvoiceNum is nil and v.Name is not nil", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(3)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| }, |
| }, |
| { |
| // Delete where v.InvoiceNum is not nil |
| // and v.Name is not nil. Expect nothing deleted. |
| "delete from Customer where v.InvoiceNum is not nil and v.Name is not nil", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(0)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // Delete all $88 invoices. |
| "delete from Customer where Type(v) like \"%.Invoice\" and v.Amount = 88", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(2)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // Delete all with a key prefix of "001". |
| "delete from Customer where k like \"001%\"", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(4)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // Delete all with a key prefix of "002". |
| "delete from Customer where k like \"002%\"", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(5)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // Delete all with key prefix NOT like "002%". |
| "delete from Customer where k not like \"002%\"", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(5)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| }, |
| }, |
| { |
| // delete all with a key prefix of "001" or a key prefix of "002". |
| "delete from Customer where k like \"001%\" or k like \"002%\"", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(9)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // Delete 002% or 001% |
| "delete from Customer where k like \"002%\" or k like \"001%\"", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(9)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // Let's play with whitespace and mixed case. |
| " dElEtE from \n Customer WhErE k lIkE \"002%\" oR k LiKe \"001%\"", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(9)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // Add in a like clause that accepts all strings. |
| " dElEtE from \n Customer WhErE k lIkE \"002%\" oR k LiKe \"001%\" or k lIkE \"%\"", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(10)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| // delete customers whose last name is Masterson. |
| "delete from Customer where Type(v) like \"%.Customer\" and v.Name like \"%Masterson\"", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(1)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // delete where v.Address.City is "Collins" or type is Invoice. |
| "delete from Customer where v.Address.City = \"Collins\" or Type(v) like \"%.Invoice\"", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(8)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // delete where v.Bar.Baz.TitleOrValue.Value = 42 |
| "delete from Foo where v.Bar.Baz.TitleOrValue.Value = 42", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(1)}, |
| }, |
| "select k from Foo", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| }, |
| }, |
| { |
| // delete where v.Address.City = "Collins" or (type is Invoice and v.InvoiceNum is not nil). |
| // Limit 3 |
| "delete from Customer where v.Address.City = \"Collins\" or (Type(v) like \"%.Invoice\" and v.InvoiceNum is not nil) limit 3", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(3)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // delete invoices where date is 2015-03-17 |
| "delete from Customer where Type(v) like \"%.Invoice\" and Year(v.InvoiceDate, \"America/Los_Angeles\") = 2015 and Month(v.InvoiceDate, \"America/Los_Angeles\") = 3 and Day(v.InvoiceDate, \"America/Los_Angeles\") = 17", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(2)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| // Now will always be > 2012, so all customer records will be deleted. |
| "delete from Customer where Now() > Time(\"2006-01-02 MST\", \"2012-03-17 PDT\")", |
| []string{"Count"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf(10)}, |
| }, |
| "select k from Customer", |
| []string{"k"}, |
| [][]*vdl.Value{}, |
| }, |
| } |
| |
| setup(t) |
| defer cleanup() |
| for _, test := range basic { |
| initTables(t) |
| headers, rs, err := db.Exec(ctx, test.delQuery) |
| if err != nil { |
| t.Errorf("delQuery: %s; got %v, want nil", test.delQuery, err) |
| } else { |
| // Collect results. |
| r := [][]*vdl.Value{} |
| for rs.Advance() { |
| r = append(r, rs.Result()) |
| } |
| if !reflect.DeepEqual(test.delResults, r) { |
| t.Errorf("delQuery: %s; got %v, want %v", test.delQuery, r, test.delResults) |
| } |
| if !reflect.DeepEqual(test.delHeaders, headers) { |
| t.Errorf("delQuery: %s; got %v, want %v", test.delQuery, headers, test.delHeaders) |
| } |
| } |
| headers, rs, err = db.Exec(ctx, test.selQuery) |
| if err != nil { |
| t.Errorf("delQuery: %s; got %v, want nil", test.delQuery, err) |
| } else { |
| // Collect results. |
| r := [][]*vdl.Value{} |
| for rs.Advance() { |
| r = append(r, rs.Result()) |
| } |
| if !reflect.DeepEqual(test.selResults, r) { |
| t.Errorf("delQuery: %s; got %v, want %v", test.delQuery, r, test.selResults) |
| } |
| if !reflect.DeepEqual(test.selHeaders, headers) { |
| t.Errorf("delQuery: %s; got %v, want %v", test.delQuery, headers, test.selHeaders) |
| } |
| } |
| } |
| } |
| |
| func TestQuerySelectClause(t *testing.T) { |
| setup(t) |
| defer cleanup() |
| initTables(t) |
| basic := []execSelectTest{ |
| { |
| // Select numeric types |
| "select v.B, v.Ui16, v.Ui32, v.Ui64, v.I16, v.I32, v.I64, v.F32, v.F64, v.C64, v.C128 from Numbers where k = \"001\"", |
| []string{"v.B", "v.Ui16", "v.Ui32", "v.Ui64", "v.I16", "v.I32", "v.I64", "v.F32", "v.F64", "v.C64", "v.C128"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf(byte(12)), |
| vdl.ValueOf(uint16(1234)), |
| vdl.ValueOf(uint32(5678)), |
| vdl.ValueOf(uint64(999888777666)), |
| vdl.ValueOf(int16(9876)), |
| vdl.ValueOf(int32(876543)), |
| vdl.ValueOf(int64(128)), |
| vdl.ValueOf(float32(3.14159)), |
| vdl.ValueOf(float64(2.71828182846)), |
| vdl.ValueOf(complex64(123.0 + 7.0i)), |
| vdl.ValueOf(complex128(456.789 + 10.1112i)), |
| }, |
| }, |
| }, |
| { |
| // Select struct, bool, string, enum, union |
| "select v, v.Active, v.Address.State, v.Credit.Report.ExperianReport.Rating, v.Credit.Report from Customer where k = \"003\"", |
| []string{"v", "v.Active", "v.Address.State", "v.Credit.Report.ExperianReport.Rating", "v.Credit.Report"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf(customerEntries[9].value), |
| vdl.ValueOf(true), |
| vdl.ValueOf("CT"), |
| vdl.ValueOf(testdata.ExperianRatingGood), |
| vdl.ValueOf(testdata.AgencyReportExperianReport{testdata.ExperianCreditReport{testdata.ExperianRatingGood}}), |
| }, |
| }, |
| }, |
| { |
| // Select array, list, set, map |
| "select v.A[1], v.L[6], v.M[Complex(1.1,2.2)], v.S[\"I’ll grind his bones to mix my bread\"] from KeyIndexData where k = \"aaa\"", |
| []string{"v.A[1]", "v.L[6]", "v.M[Complex]", "v.S[I’ll grind his bones to mix my bread]"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf("Fi"), |
| vdl.ValueOf("Englishman"), |
| vdl.ValueOf("Be he living, or be he dead"), |
| vdl.ValueOf(true), |
| }, |
| }, |
| }, |
| { |
| // Date functions. Now is not included since its return value varies. |
| // Also note, cannot parse a time with nanoseconds, so Nanosecond() returns |
| // zero. |
| "select Time(\"Jan 2 2006 15:04:05 MST\", \"Apr 12 2015 22:16:06 PDT\"), Year(Time(\"Jan 2 2006 15:04:05 MST\", \"Apr 12 2015 22:16:06 PDT\"), \"America/Los_Angeles\"), Month(Time(\"Jan 2 2006 15:04:05 MST\", \"Apr 12 2015 22:16:06 PDT\"), \"America/Los_Angeles\"), Day(Time(\"Jan 2 2006 15:04:05 MST\", \"Apr 12 2015 22:16:06 PDT\"), \"America/Los_Angeles\"), Hour(Time(\"Jan 2 2006 15:04:05 MST\", \"Apr 12 2015 22:16:06 PDT\"), \"America/Los_Angeles\"), Minute(Time(\"Jan 2 2006 15:04:05 MST\", \"Apr 12 2015 22:16:06 PDT\"), \"America/Los_Angeles\"), Second(Time(\"Jan 2 2006 15:04:05 MST\", \"Apr 12 2015 22:16:06 PDT\"), \"America/Los_Angeles\"), Nanosecond(Time(\"Jan 2 2006 15:04:05 MST\", \"Apr 12 2015 22:16:06 PDT\"), \"America/Los_Angeles\"), Weekday(Time(\"Jan 2 2006 15:04:05 MST\", \"Apr 12 2015 22:16:06 PDT\"), \"America/Los_Angeles\"), YearDay(Time(\"Jan 2 2006 15:04:05 MST\", \"Apr 12 2015 22:16:06 PDT\"), \"America/Los_Angeles\") from Customer where k = \"001\"", |
| []string{"Time", "Year", "Month", "Day", "Hour", "Minute", "Second", "Nanosecond", "Weekday", "YearDay"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf(t2015_04_12_22_16_06), // Time |
| vdl.ValueOf(int64(2015)), // Year |
| vdl.ValueOf(int64(4)), // Month |
| vdl.ValueOf(int64(12)), // Day |
| vdl.ValueOf(int64(22)), // Hour |
| vdl.ValueOf(int64(16)), // Minute |
| vdl.ValueOf(int64(6)), // Second |
| vdl.ValueOf(int64(0)), // Nanosecond |
| vdl.ValueOf(int64(0)), // Weekday |
| vdl.ValueOf(int64(102)), // YearDay |
| }, |
| }, |
| }, |
| { |
| // String functions. |
| "select Atoi(\"123\"), Atof(\"12.34\"), HtmlEscape(\"<a img='foo'>Foo Image</a>\"), HtmlUnescape(\"<a img='foo'>Foo Image</a>\"), Lowercase(\"AbCd\"), Split(\"ab,cd\", \",\"), Type(v), Uppercase(\"AbCd\"), RuneCount(\"Hello, 世界\"), Sprintf(\"abc: %s\", \"def\"), Str(123), StrCat(\"abc\", \"def\"), StrIndex(\"abcdef\", \"de\"), StrRepeat(\"abc\", 3), StrReplace(\"abczzzdef\", \"zzz\", \"ZZZ\"), StrLastIndex(\"abcabc\", \"abc\"), Trim(\" abc \"), TrimLeft(\" abc \"), TrimRight(\" abc \") from Customer where k = \"001\"", |
| []string{"Atoi", "Atof", "HtmlEscape", "HtmlUnescape", "Lowercase", "Split", "Type", "Uppercase", "RuneCount", "Sprintf", "Str", "StrCat", "StrIndex", "StrRepeat", "StrReplace", "StrLastIndex", "Trim", "TrimLeft", "TrimRight"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf(int64(123)), // Atoi |
| vdl.ValueOf(float64(12.34)), // Atof |
| vdl.ValueOf("<a img='foo'>Foo Image</a>"), // HtmlEscape |
| vdl.ValueOf("<a img='foo'>Foo Image</a>"), // HtmlUnescape |
| vdl.ValueOf("abcd"), // Lowercase |
| vdl.ValueOf([]string{"ab", "cd"}), // Split |
| vdl.ValueOf("v.io/v23/syncbase/nosql/testdata.Customer"), // Type |
| vdl.ValueOf("ABCD"), // Uppercase |
| vdl.ValueOf(9), // RuneCount |
| vdl.ValueOf("abc: def"), // Sprintf |
| vdl.ValueOf("123"), // Str |
| vdl.ValueOf("abcdef"), // StrCat |
| vdl.ValueOf(3), // StrIndex |
| vdl.ValueOf("abcabcabc"), // StrRepeat |
| vdl.ValueOf("abcZZZdef"), // StrReplace |
| vdl.ValueOf(3), // StrLastIndex |
| vdl.ValueOf("abc"), // Trim |
| vdl.ValueOf("abc "), // TrimLeft |
| vdl.ValueOf(" abc"), // TrimRight |
| }, |
| }, |
| }, |
| { |
| // Math Functions |
| "select Ceiling(10.1), Ceiling(-10.1), Complex(1.1, 2.2), Floor(10.1), Floor(-10.1), IsInf(100, 1), IsInf(-100, -1), IsInf(Inf(1), 1), IsInf(Inf(-1), -1), IsNaN(100.0), IsNaN(NaN()), Log(2.3), Log10(3.1), Pow(4.3, 10.1), Pow10(12), Mod(12.6, 4.4), Real(Complex(1.1, 2.2)), Truncate(16.9), Truncate(-16.9), Remainder(16.9, 7.1) from Numbers where k = \"001\"", |
| []string{"Ceiling", "Ceiling", "Complex", "Floor", "Floor", "IsInf", "IsInf", "IsInf", "IsInf", "IsNaN", "IsNaN", "Log", "Log10", "Pow", "Pow10", "Mod", "Real", "Truncate", "Truncate", "Remainder"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf(float64(11)), // Ceiling(10.1) |
| vdl.ValueOf(float64(-10)), // Ceiling(-10.1) |
| vdl.ValueOf(complex128(1.1 + 2.2i)), // Complex(1.1, 1.2) |
| vdl.ValueOf(float64(10)), // Floor(10.1) |
| vdl.ValueOf(float64(-11)), // Floor(-10.1) |
| vdl.ValueOf(false), // IsInf(100, 1) |
| vdl.ValueOf(false), // IsInf(-100, -1) |
| vdl.ValueOf(true), // IsInf(Inf(1), 1) |
| vdl.ValueOf(true), // IsInf(Inf(-1), -1) |
| vdl.ValueOf(false), // IsNaN(100.0) |
| vdl.ValueOf(true), // IsNaN(NaN()) |
| vdl.ValueOf(float64(0.832909122935104)), // Log(2.3) |
| vdl.ValueOf(float64(0.4913616938342727)), // Log10(3.1) |
| vdl.ValueOf(float64(2.5005261539265467e+06)), // Pow(4.3, 10.1) |
| vdl.ValueOf(float64(1e+12)), // Pow10(12) |
| vdl.ValueOf(float64(3.799999999999999)), // Mod(12.6, 4.4) |
| vdl.ValueOf(float64(1.1)), // Real(Complex(1.1, 2.2)) |
| vdl.ValueOf(float64(16)), // Truncate((16.9) |
| vdl.ValueOf(float64(-16)), // Truncate((-16.9) |
| vdl.ValueOf(float64(2.6999999999999993)), // Remainder(16.9, 7.1) |
| }, |
| }, |
| }, |
| { |
| // Len function |
| "select RuneCount(\"Hello, 世界\"), Len(\"Hello, 世界\"), Len(v.A), Len(v.L), Len(v.M), Len(v.S) from KeyIndexData where k = \"aaa\"", |
| []string{"RuneCount", "Len", "Len", "Len", "Len", "Len"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf(int64(9)), // RuneCount("Hello, 世界") |
| vdl.ValueOf(int64(13)), // Len("Hello, 世界") |
| vdl.ValueOf(int64(4)), // Len(v.A) |
| vdl.ValueOf(int64(7)), // Len(v.L) |
| vdl.ValueOf(int64(1)), // Len(v.M) |
| vdl.ValueOf(int64(1)), // Len(v.S) |
| }, |
| }, |
| }, |
| { |
| // Nested Functions |
| "select Year(Time(\"Jan 2 2006 15:04:05 MST\", \"Apr 12 2015 22:16:06 PDT\"), Sprintf(\"%s/%s\", \"America\", \"Los_Angeles\")) from Customer where k = \"001\"", |
| []string{"Year"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf(int64(2015)), |
| }, |
| }, |
| }, |
| { |
| // Select with As Clause |
| "select v.B as Bee from Numbers where k = \"001\"", |
| []string{"Bee"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf(byte(12)), |
| }, |
| }, |
| }, |
| } |
| |
| for _, test := range basic { |
| headers, rs, err := db.Exec(ctx, test.query) |
| if err != nil { |
| t.Errorf("query: %s; got %v, want nil", test.query, err) |
| } else { |
| // Collect results. |
| r := [][]*vdl.Value{} |
| for rs.Advance() { |
| r = append(r, rs.Result()) |
| } |
| if !reflect.DeepEqual(test.r, r) { |
| t.Errorf("query: %s; got %v, want %v", test.query, r, test.r) |
| } |
| if !reflect.DeepEqual(test.headers, headers) { |
| t.Errorf("query: %s; got %v, want %v", test.query, headers, test.headers) |
| } |
| } |
| } |
| } |
| |
| func TestQueryWhereClause(t *testing.T) { |
| setup(t) |
| defer cleanup() |
| initTables(t) |
| basic := []execSelectTest{ |
| { |
| // Select on numeric comparisons with equals |
| // (except, allow a range for F32 as the literal is interpreted as a float64. |
| "select k, v from Numbers where v.B = 12 and v.Ui16 = 1234 and v.Ui32 = 5678 and v.Ui64 = 999888777666 and v.I16 = 9876 and v.I32 = 876543 and v.I64 = 128 and v.F32 > 3.14158 and v.F32 < 3.1416 and v.F64 = 2.71828182846 and v.C64 = Complex(123.0, 7.0) and v.C128 = Complex(456.789, 10.1112) and k = \"001\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf(numbersEntries[0].key), |
| numbersEntries[0].value, |
| }, |
| }, |
| }, |
| { |
| // Select on numeric comparisons with >= |
| // Note: Complex does not support >= |
| "select k, v from Numbers where v.B >= 12 and v.Ui16 >= 1234 and v.Ui32 >= 5678 and v.Ui64 >= 999888777666 and v.I16 >= 9876 and v.I32 >= 876543 and v.I64 >= 128 and v.F32 >= 3.14159 and v.F64 >= 2.71828182846 and k = \"001\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf(numbersEntries[0].key), |
| numbersEntries[0].value, |
| }, |
| }, |
| }, |
| { |
| // Select on numeric comparisons with <= |
| // Note: Complex does not support <= |
| "select k, v from Numbers where v.B <= 12 and v.Ui16 <= 1234 and v.Ui32 <= 5678 and v.Ui64 <= 999888777666 and v.I16 <= 9876 and v.I32 <= 876543 and v.I64 <= 128 and v.F32 <= 3.14160 and v.F64 <= 2.71828182846 and k = \"001\"", |
| []string{"k", "v"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{ |
| vdl.ValueOf(numbersEntries[0].key), |
| numbersEntries[0].value, |
| }, |
| }, |
| }, |
| // Lots of selects on numeric comparisons with <> |
| { |
| "select k from Numbers where v.B <> 12", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Numbers where v.Ui16 <> 1234", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Numbers where v.Ui32 <> 5678", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Numbers where v.Ui64 <> 999888777666", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Numbers where v.I16 <> 9876", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Numbers where v.I32 <> 876543", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Numbers where v.I64 <> 128", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Numbers where v.F32 <> 3.14159", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, // float32 <> float64 |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Numbers where v.F64 <> 2.71828182846", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Numbers where v.C64 <> Complex(123.0, 7.0)", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Numbers where v.C128 <> Complex(456.789, 10.1112)", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| // bool =, <> |
| { |
| "select k from Customer where v.Active = true", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Active <> false", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| // string =, <>, <, <=, >, >=, like, not like |
| { |
| "select k from Customer where v.Name = \"Bat Masterson\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Name <> \"Bat Masterson\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Name < \"Bat Masterson\"", |
| []string{"k"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| "select k from Customer where v.Name <= \"Bat Masterson\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Name > \"Bat Masterson\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Name >= \"Bat Masterson\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Name like \"John %\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Name not like \"John %\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| }, |
| }, |
| // enum =, <> |
| { |
| "select k from Customer where v.Credit.Agency = \"Equifax\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Credit.Agency <> \"Equifax\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| // inspect into array, list, set and map in where clause |
| { |
| "select k from KeyIndexData where v.A[1] = \"Fi\" and v.L[3] = \"blood\" and v.M[Complex(1.1,2.2)] = \"Be he living, or be he dead\" and v.S[\"I’ll grind his bones to mix my bread\"] = true", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("aaa")}, |
| }, |
| }, |
| { |
| // Use every date function in where clause. Note: Now() requires that |
| // clock not be set to year less than 2015. |
| "select k from Customer where v.InvoiceDate = Time(\"Jan 2 2006 15:04:05 -0700 MST\", \"Jan 22 2015 13:11:01 -0800 PST\") and Year(v.InvoiceDate, \"America/Los_Angeles\") = 2015 and Month(v.InvoiceDate, \"America/Los_Angeles\") = 1 and Day(v.InvoiceDate, \"America/Los_Angeles\") = 22 and Hour(v.InvoiceDate, \"America/Los_Angeles\") = 13 and Minute(v.InvoiceDate, \"America/Los_Angeles\") = 11 and Second(v.InvoiceDate, \"America/Los_Angeles\") = 1 and Nanosecond(v.InvoiceDate, \"America/Los_Angeles\") = 0 and Year(Now(), \"America/Los_Angeles\") >= 2015", |
| |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| }, |
| }, |
| { |
| // Use string functions in where clause. |
| "select k from Customer where Atoi(\"3\") = 3 and Atof(\"3.1\") = 3.1 and HtmlEscape(\"<a img='foo'>Foo Image</a>\") = \"<a img='foo'>Foo Image</a>\" and HtmlUnescape(\"<a img='foo'>Foo Image</a>\") = \"<a img='foo'>Foo Image</a>\" and Lowercase(v.Name) = \"bat masterson\" and Type(v) like \"%.Customer\" and Uppercase(v.Name) = \"BAT MASTERSON\" and RuneCount(v.Name) = 13 and Sprintf(\"Name: %s\", v.Name) = \"Name: Bat Masterson\" and Str(v.Id) = \"2\" and StrCat(v.Name, Str(v.Id)) = \"Bat Masterson2\" and StrIndex(v.Name, \"M\") = 4 and StrRepeat(v.Name, 2) = \"Bat MastersonBat Masterson\" and StrReplace(v.Name, \"Master\", \"Amateur\") = \"Bat Amateurson\" and Trim(\" xxx \") = \"xxx\" and TrimLeft(\" xxx \") = \"xxx \" and TrimRight(\" xxx \") = \" xxx\"", |
| |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| }, |
| }, |
| { |
| // Use math functions in where clause. |
| "select k from Numbers where Ceiling(v.F64) = 2.0 and v.C128 = Complex(4.32, 1.0) and Floor(v.F64) = 1.0 and IsInf(v.F64, 1) = false and IsInf(Inf(1), 1) = true and IsInf(Inf(-1), -1) = true and IsNaN(v.F64) = false and IsNaN(NaN()) = true and Log(v.F64) = 0.5493061443347032 and Log10(v.F64) = 0.23856062736011277 and Pow(v.F64, 3.2) = 5.799546134807319 and Pow10(v.B) = 1e+09 and Pow10(v.Ui32) = Inf(1) and Mod(v.F64, v.F32) = 0.31783726940013923 and Real(v.C128) = 4.32 and Truncate(v.F64) = 1.0 and Remainder(v.F64, v.F32) = 0.31783726940013923", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("002")}, |
| }, |
| }, |
| { |
| // Use Len on string, array, list and set-- in where clause. |
| "select k from KeyIndexData where Len(v.A[0]) = 3 and Len(v.A) = 4 and Len(v.L) = 7 and Len(v.M) = 1 and Len(v.S) = 1", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("aaa")}, |
| }, |
| }, |
| // The next six tests exercise and, or and parens for precedence. |
| { |
| "select k from Customer where v.Name like \"%Smith\" and v.Active = false or v.Address.City = \"Palo Alto\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| }, |
| }, |
| { |
| "select k from Customer where (v.Name like \"%Smith\" and v.Active = false) or v.Address.City = \"Palo Alto\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Name like \"%Smith\" and (v.Active = false or v.Address.City = \"Palo Alto\")", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Name like \"%Smith\" and v.Active = false or v.Address.City = \"Palo Alto\" or v.Address.City = \"Mountain View\"", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Name like \"%Smith\" and (v.Active = false or v.Address.City = \"Palo Alto\" or v.Address.City = \"Mountain View\")", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| }, |
| }, |
| { |
| "select k from Customer where v.Name like \"%Smith\" and v.Active = false and (v.Address.City = \"Palo Alto\" or v.Address.City = \"Mountain View\")", |
| []string{"k"}, |
| [][]*vdl.Value{}, |
| }, |
| } |
| |
| for _, test := range basic { |
| headers, rs, err := db.Exec(ctx, test.query) |
| if err != nil { |
| t.Errorf("query: %s; got %v, want nil", test.query, err) |
| } else { |
| // Collect results. |
| r := [][]*vdl.Value{} |
| for rs.Advance() { |
| r = append(r, rs.Result()) |
| } |
| if !reflect.DeepEqual(test.r, r) { |
| t.Errorf("query: %s; got %v, want %v", test.query, r, test.r) |
| } |
| if !reflect.DeepEqual(test.headers, headers) { |
| t.Errorf("query: %s; got %v, want %v", test.query, headers, test.headers) |
| } |
| } |
| } |
| } |
| |
| func TestQueryEscapeClause(t *testing.T) { |
| setup(t) |
| defer cleanup() |
| initTables(t) |
| basic := []execSelectTest{ |
| { |
| "select k from Customer where \"abc%\" like \"abc^%\" escape '^'", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Customer where \"abc_\" like \"abc$_\" escape '$'", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Customer where \"abc%defghi\" like \"abc^%%\" escape '^'", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| { |
| "select k from Customer where \"abc_defghi\" like \"abc^__efghi\" escape '^'", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| } |
| |
| for _, test := range basic { |
| headers, rs, err := db.Exec(ctx, test.query) |
| if err != nil { |
| t.Errorf("query: %s; got %v, want nil", test.query, err) |
| } else { |
| // Collect results. |
| r := [][]*vdl.Value{} |
| for rs.Advance() { |
| r = append(r, rs.Result()) |
| } |
| if !reflect.DeepEqual(test.r, r) { |
| t.Errorf("query: %s; got %v, want %v", test.query, r, test.r) |
| } |
| if !reflect.DeepEqual(test.headers, headers) { |
| t.Errorf("query: %s; got %v, want %v", test.query, headers, test.headers) |
| } |
| } |
| } |
| } |
| |
| func TestQueryLimitAndOffsetClauses(t *testing.T) { |
| setup(t) |
| defer cleanup() |
| initTables(t) |
| basic := []execSelectTest{ |
| { |
| "select k from Customer limit 2 offset 3", |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| }, |
| }, |
| { |
| "select k from Customer offset 9223372036854775807", // maxint64 |
| []string{"k"}, |
| [][]*vdl.Value{}, |
| }, |
| { |
| "select k from Customer limit 9223372036854775807", // maxint64 |
| []string{"k"}, |
| [][]*vdl.Value{ |
| []*vdl.Value{vdl.ValueOf("001")}, |
| []*vdl.Value{vdl.ValueOf("001001")}, |
| []*vdl.Value{vdl.ValueOf("001002")}, |
| []*vdl.Value{vdl.ValueOf("001003")}, |
| []*vdl.Value{vdl.ValueOf("002")}, |
| []*vdl.Value{vdl.ValueOf("002001")}, |
| []*vdl.Value{vdl.ValueOf("002002")}, |
| []*vdl.Value{vdl.ValueOf("002003")}, |
| []*vdl.Value{vdl.ValueOf("002004")}, |
| []*vdl.Value{vdl.ValueOf("003")}, |
| }, |
| }, |
| } |
| |
| for _, test := range basic { |
| headers, rs, err := db.Exec(ctx, test.query) |
| if err != nil { |
| t.Errorf("query: %s; got %v, want nil", test.query, err) |
| } else { |
| // Collect results. |
| r := [][]*vdl.Value{} |
| for rs.Advance() { |
| r = append(r, rs.Result()) |
| } |
| if !reflect.DeepEqual(test.r, r) { |
| t.Errorf("query: %s; got %v, want %v", test.query, r, test.r) |
| } |
| if !reflect.DeepEqual(test.headers, headers) { |
| t.Errorf("query: %s; got %v, want %v", test.query, headers, test.headers) |
| } |
| } |
| } |
| } |
| |
| func svPair(s string) []*vdl.Value { |
| v := vdl.ValueOf(s) |
| return []*vdl.Value{v, v} |
| } |
| |
| // Use Now to verify it is "pre" executed such that all the rows |
| // have the same time. |
| func TestPreExecFunctions(t *testing.T) { |
| setup(t) |
| defer cleanup() |
| initTables(t) |
| basic := []preExecFunctionTest{ |
| { |
| "select Now() from Customer", |
| []string{ |
| "Now", |
| }, |
| }, |
| } |
| |
| for _, test := range basic { |
| headers, rs, err := db.Exec(ctx, test.query) |
| if err != nil { |
| t.Errorf("query: %s; got %v, want nil", test.query, err) |
| } else { |
| // Check that all results are identical. |
| // Collect results. |
| var last []*vdl.Value |
| for rs.Advance() { |
| result := rs.Result() |
| if last != nil && !reflect.DeepEqual(last, result) { |
| t.Errorf("query: %s; got %v, want %v", test.query, result, last) |
| } |
| last = result |
| } |
| if !reflect.DeepEqual(test.headers, headers) { |
| t.Errorf("query: %s; got %v, want %v", test.query, headers, test.headers) |
| } |
| } |
| } |
| } |
| |
| // TODO(jkline): More negative tests here (even though they are tested elsewhere)? |
| func TestExecErrors(t *testing.T) { |
| setup(t) |
| defer cleanup() |
| initTables(t) |
| basic := []execSelectErrorTest{ |
| { |
| "select a from Customer", |
| syncql.NewErrInvalidSelectField(ctx, 7), |
| }, |
| { |
| "select v from Unknown", |
| // The following error text is dependent on the implementation of the query.Database interface. |
| // TODO(sadovsky): Error messages should never contain storage engine |
| // prefixes ("t") and delimiters ("\xfe"). |
| syncql.NewErrTableCantAccess(ctx, 14, "Unknown", errors.New("nosql.test:\"a/db\".Exec: Does not exist: t\xfeUnknown")), |
| }, |
| { |
| "select v from Customer offset -1", |
| syncql.NewErrExpected(ctx, 30, "positive integer literal"), |
| }, |
| { |
| "select v from Customer limit -1", |
| syncql.NewErrExpected(ctx, 29, "positive integer literal"), |
| }, |
| { |
| "select v from Customer offset 9223372036854775808", // maxint64 + 1 |
| syncql.NewErrCouldNotConvert(ctx, 30, "9223372036854775808", "int64"), |
| }, |
| { |
| "select v from Customer limit 9223372036854775808", // maxint64 + 1 |
| syncql.NewErrCouldNotConvert(ctx, 29, "9223372036854775808", "int64"), |
| }, |
| } |
| |
| for _, test := range basic { |
| _, rs, err := db.Exec(ctx, test.query) |
| if err == nil { |
| err = rs.Err() |
| } |
| // Test both that the IDs compare and the text compares (since the offset needs to match). |
| // Note: This is a little tricky because the actual error message will contain the calling |
| // module. |
| wantPrefix := test.err.Error()[:strings.Index(test.err.Error(), ":")] |
| wantSuffix := test.err.Error()[len(wantPrefix)+1:] |
| if verror.ErrorID(err) != verror.ErrorID(test.err) || !strings.HasPrefix(err.Error(), wantPrefix) || !strings.HasSuffix(err.Error(), wantSuffix) { |
| t.Errorf("query: %s; got %v, want %v", test.query, err, test.err) |
| } |
| } |
| } |
| |
| func TestQueryErrors(t *testing.T) { |
| setup(t) |
| defer cleanup() |
| initTables(t) |
| basic := []execSelectErrorTest{ |
| // Produce every error in the book (make that, every one that is possible to produce). |
| { |
| "select k from Customer where Amt = 100", |
| syncql.NewErrBadFieldInWhere(ctx, 29), |
| }, |
| { |
| "select k from Customer where v.Active > false", |
| syncql.NewErrBoolInvalidExpression(ctx, 38), |
| }, |
| // * CheckOfUnknownStatementType cannot be produced as the parser |
| // does not produce unknown statement types. |
| // * CouldNotConvert cannot be produced directly (it could be wrapped). The only |
| // unwrapped errors are produced by the parser and would only occur if golang's |
| // text/scanner liked to the parser. |
| { |
| "select k.a from Customer", |
| syncql.NewErrDotNotationDisallowedForKey(ctx, 9), |
| }, |
| // * ErrorCompilingRegularExpression cannot be produced unless there is a bug |
| // in query_checker which should be escaping regex characters (by calling |
| // a library function). |
| // * ExecOfUnknownStatementType cannot be produced as the parser |
| // does not produce unknown statement types. |
| { |
| "select k, v from Customer limit a", |
| syncql.NewErrExpected(ctx, 32, "positive integer literal"), |
| }, |
| { |
| "select k, v limit 100", |
| syncql.NewErrExpectedFrom(ctx, 12, "limit"), |
| }, |
| { |
| "select 100 from Customer", |
| syncql.NewErrExpectedIdentifier(ctx, 7, "100"), |
| }, |
| { |
| "select k from Customer where v.A ^ v.B", |
| syncql.NewErrExpectedOperator(ctx, 33, "^"), |
| }, |
| { |
| "select k from Customer where Mod(12.1) = 0.1", |
| syncql.NewErrFunctionArgCount(ctx, 29, "Mod", 2, 1), |
| }, |
| { |
| "select k from Customer where Sprintf() = \"abc\"", |
| syncql.NewErrFunctionAtLeastArgCount(ctx, 29, "Sprintf", 1, 0), |
| }, |
| { |
| "select k from Customer where Type(100) = \"abc\"", |
| syncql.NewErrFunctionTypeInvalidArg(ctx, 34), |
| }, |
| { |
| "select k from Customer where Len(100) = \"abc\"", |
| syncql.NewErrFunctionLenInvalidArg(ctx, 33), |
| }, |
| // * FunctionArgBad error does not make it back to the client. |
| { |
| "select What(100) from Customer", |
| syncql.NewErrFunctionNotFound(ctx, 7, "What"), |
| }, |
| { |
| "select Type(k) from Customer", |
| syncql.NewErrArgMustBeField(ctx, 12), |
| }, |
| // *BigIntConversionError isn't produced as vdl doesn't have big ints |
| // *BigRatConversionError isn't produced as vdl doesn't have big rats |
| // *BoolConversionError isn't currently produced as no functions take a bool arg. |
| { |
| "select Real(\"abc\") from Customer", |
| syncql.NewErrComplexConversionError(ctx, 12, errors.New("Cannot convert operand to Complex.")), |
| }, |
| // *UintConversionError isn't currently produced as no functions take a uint arg. |
| { |
| "select Year(\"abc\", \"America/Los_Angeles\") from Customer", |
| syncql.NewErrTimeConversionError(ctx, 12, errors.New("Cannot convert operand to time.")), |
| }, |
| // * LocationConversionError - see TestQueryErrorsPlatformDependentText below. |
| { |
| "select Lowercase(100) from Customer", |
| syncql.NewErrStringConversionError(ctx, 17, errors.New("Cannot convert operand to string.")), |
| }, |
| { |
| "select Complex(\"abc\", 3.1) from Customer", |
| syncql.NewErrFloatConversionError(ctx, 15, errors.New("Cannot convert operand to float64.")), |
| }, |
| { |
| "select Pow10(3.1) from Customer", |
| syncql.NewErrIntConversionError(ctx, 13, errors.New("Cannot convert operand to int64.")), |
| }, |
| { |
| "select k from Customer where nil is v.Name", |
| syncql.NewErrIsIsNotRequireLhsValue(ctx, 29), |
| }, |
| { |
| "select k from Customer where v.Name is \"John Smith\"", |
| syncql.NewErrIsIsNotRequireRhsNil(ctx, 39), |
| }, |
| { |
| "select k from Customer where v.Name like \"a^b%\" escape '^'", |
| syncql.NewErrInvalidEscapeSequence(ctx, 41), |
| }, |
| { |
| "select k from Customer where v.Name like \"John^Smith\" escape '^'", |
| syncql.NewErrInvalidEscapeSequence(ctx, 41), |
| }, |
| { |
| "select a from Customer", |
| syncql.NewErrInvalidSelectField(ctx, 7), |
| }, |
| { |
| "select k from Customer where k = 100", |
| syncql.NewErrKeyExpressionLiteral(ctx, 33), |
| }, |
| // *KeyValueStreamError Cannot produce a KeyValueStreamError. |
| { |
| "select k from Customer where v.Name like 100", |
| syncql.NewErrLikeExpressionsRequireRhsString(ctx, 41), |
| }, |
| { |
| "select k from Customer limit 0", |
| syncql.NewErrLimitMustBeGt0(ctx, 29), |
| }, |
| // *MaxStatementLenExceeded See TestQueryStatementSizeExceeded. |
| { |
| "", |
| syncql.NewErrNoStatementFound(ctx, 0), |
| }, |
| // *OffsetMustBeGe0 cannot be produced because the parser won't produce |
| // an offset < 0. |
| // *ScanError Cannot produce a [table].ScanError. |
| { |
| "select k from Blah", |
| // TODO(sadovsky): Error messages should never contain storage engine |
| syncql.NewErrTableCantAccess(ctx, 14, "Blah", errors.New("nosql.test:\"a/db\".Exec: Does not exist: t\xfeBlah")), |
| }, |
| { |
| "select k, v from Customer where a = b)", |
| syncql.NewErrUnexpected(ctx, 37, ")"), |
| }, |
| { |
| "select k from Customer where", |
| syncql.NewErrUnexpectedEndOfStatement(ctx, 28), |
| }, |
| { |
| "foo", |
| syncql.NewErrUnknownIdentifier(ctx, 0, "foo"), |
| }, |
| { |
| "select k from Customer escape ' '", |
| syncql.NewErrInvalidEscapeChar(ctx, 30), |
| }, |
| { |
| "select K from Customer", |
| syncql.NewErrDidYouMeanLowercaseK(ctx, 7), |
| }, |
| { |
| "select V from Customer", |
| syncql.NewErrDidYouMeanLowercaseV(ctx, 7), |
| }, |
| { |
| "select now() from Customer", |
| syncql.NewErrDidYouMeanFunction(ctx, 7, "Now"), |
| }, |
| // *NotEnoughParamValuesSpecified Can't test from syncbase as PreparedStatement not |
| // exposed to syncbase. |
| // *TooManyParamValuesSpecified Can't test from syncbase as PreparedStatement not |
| // exposed to syncbase. |
| // *PreparedStatementNotFound Can't test from syncbase as PreparedStatement not |
| // exposed to syncbase. |
| } |
| |
| for _, test := range basic { |
| _, rs, err := db.Exec(ctx, test.query) |
| if err == nil { |
| err = rs.Err() |
| } |
| // Test both that the IDs compare and the text compares (since the offset needs to match). |
| // Note: This is a little tricky because the actual error message will contain the calling |
| // module. |
| wantPrefix := test.err.Error()[:strings.Index(test.err.Error(), ":")] |
| wantSuffix := test.err.Error()[len(wantPrefix)+1:] |
| if verror.ErrorID(err) != verror.ErrorID(test.err) || !strings.HasPrefix(err.Error(), wantPrefix) || !strings.HasSuffix(err.Error(), wantSuffix) { |
| t.Errorf("query: %s; got %v, want %v", test.query, err, test.err) |
| } |
| } |
| } |
| |
| func TestQueryErrorsPlatformDependentText(t *testing.T) { |
| setup(t) |
| defer cleanup() |
| initTables(t) |
| basic := []execSelectErrorTest{ |
| // These errors contain installation dependent parts to the error. The test is |
| // more relaxed (it doesn't check the suffix) in order to account for this. |
| { |
| "select Year(v.InvoiceDate, \"DummyTimeZone\") from Customer", |
| syncql.NewErrLocationConversionError(ctx, 27, errors.New("unknown time zone DummyTimeZone")), |
| }, |
| } |
| |
| for _, test := range basic { |
| _, rs, err := db.Exec(ctx, test.query) |
| if err == nil { |
| err = rs.Err() |
| } |
| // Test both that the IDs compare and the text prefix compares (since the offset needs to match). |
| // Note: This is a little tricky because the actual error message will contain the calling |
| // module. |
| wantPrefix := test.err.Error()[:strings.Index(test.err.Error(), ":")] |
| if verror.ErrorID(err) != verror.ErrorID(test.err) || !strings.HasPrefix(err.Error(), wantPrefix) { |
| t.Errorf("query: %s; got %v, want %v", test.query, err, test.err) |
| } |
| } |
| } |
| |
| func TestQueryStatementSizeExceeded(t *testing.T) { |
| setup(t) |
| defer cleanup() |
| initTables(t) |
| q := fmt.Sprintf("select a from b where c = \"%s\"", strings.Repeat("x", 12000)) |
| |
| _, rs, err := db.Exec(ctx, q) |
| if err == nil { |
| err = rs.Err() |
| } |
| |
| expectedErr := syncql.NewErrMaxStatementLenExceeded(ctx, int64(0), int64(10000), int64(len(q))) |
| |
| wantPrefix := expectedErr.Error()[:strings.Index(expectedErr.Error(), ":")] |
| wantSuffix := expectedErr.Error()[len(wantPrefix)+1:] |
| if verror.ErrorID(err) != verror.ErrorID(expectedErr) || !strings.HasPrefix(err.Error(), wantPrefix) || !strings.HasSuffix(err.Error(), wantSuffix) { |
| t.Errorf("query: %s; got %v, want %v", q, err, expectedErr) |
| } |
| } |