syncbase: syncQL: support k not like "<wildcard-string>"
As pointed out by Ivan, since k(ey) <> "<string>" expressions
are now supported, there isn't a good reason to exclude k not
like expressions.
This change adds the support. Every k/v pair except the range
implied by the wildcard expression will be considered for
inclusion. Also, if the wildcard string contains no wildcards,
the expression is optimized to a k <> expression.
There are two bugfixes included in this change. There was a bug
in the resultstream implementation of the test in query/test.
There was also a bug in the actual syncbase implementation of
the same resultstream.
Change-Id: If44650509fc8f570c58f55424cba524c9dcd1220
diff --git a/v23/syncbase/nosql/exec_test/exec_test.go b/v23/syncbase/nosql/exec_test/exec_test.go
index 59ffde4..a826e08 100644
--- a/v23/syncbase/nosql/exec_test/exec_test.go
+++ b/v23/syncbase/nosql/exec_test/exec_test.go
@@ -142,6 +142,13 @@
t.Fatalf("customerTable.Put() failed: %v", err)
}
+ k = "003"
+ c = Customer{"John Steed", 3, true, AddressInfo{"100 Queen St.", "New London", "CT", "06320"}, CreditReport{Agency: CreditAgencyExperian, Report: AgencyReportExperianReport{ExperianCreditReport{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 := 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)})
@@ -217,6 +224,7 @@
[][]*vdl.Value{
[]*vdl.Value{customerEntries[0].value},
[]*vdl.Value{customerEntries[4].value},
+ []*vdl.Value{customerEntries[9].value},
},
},
{
@@ -228,6 +236,7 @@
[][]*vdl.Value{
[]*vdl.Value{customerEntries[0].value},
[]*vdl.Value{customerEntries[4].value},
+ []*vdl.Value{customerEntries[9].value},
},
},
{
@@ -247,6 +256,7 @@
[]*vdl.Value{customerEntries[6].value},
[]*vdl.Value{customerEntries[7].value},
[]*vdl.Value{customerEntries[8].value},
+ []*vdl.Value{customerEntries[9].value},
},
},
{
@@ -286,6 +296,7 @@
[]*vdl.Value{customerEntries[6].value},
[]*vdl.Value{customerEntries[7].value},
[]*vdl.Value{customerEntries[8].value},
+ []*vdl.Value{customerEntries[9].value},
},
},
{
@@ -296,6 +307,7 @@
[][]*vdl.Value{
[]*vdl.Value{customerEntries[0].value},
[]*vdl.Value{customerEntries[4].value},
+ []*vdl.Value{customerEntries[9].value},
},
},
{
@@ -312,6 +324,7 @@
[][]*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},
},
},
{
@@ -321,6 +334,7 @@
[][]*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")},
},
},
{
@@ -339,6 +353,7 @@
[]*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)},
},
},
{
@@ -376,7 +391,7 @@
},
},
{
- // Select keys & values for all records with a key prefix of "001".
+ // 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{
@@ -388,6 +403,35 @@
},
},
{
+ // 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%\"",
@@ -451,6 +495,7 @@
[]*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},
},
},
{
@@ -633,6 +678,7 @@
[]*vdl.Value{customerEntries[6].value},
[]*vdl.Value{customerEntries[7].value},
[]*vdl.Value{customerEntries[8].value},
+ []*vdl.Value{customerEntries[9].value},
},
},
{
@@ -742,6 +788,7 @@
[]*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)},
},
},
// DateTime function
@@ -758,6 +805,7 @@
[]*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
@@ -767,6 +815,7 @@
[][]*vdl.Value{
[]*vdl.Value{vdl.ValueOf("john smith")},
[]*vdl.Value{vdl.ValueOf("bat masterson")},
+ []*vdl.Value{vdl.ValueOf("john steed")},
},
},
// UpperCase function
@@ -776,6 +825,7 @@
[][]*vdl.Value{
[]*vdl.Value{vdl.ValueOf("JOHN SMITH")},
[]*vdl.Value{vdl.ValueOf("BAT MASTERSON")},
+ []*vdl.Value{vdl.ValueOf("JOHN STEED")},
},
},
// YMDHMS function
diff --git a/v23/syncbase/nosql/internal/query/query_checker/query_checker.go b/v23/syncbase/nosql/internal/query/query_checker/query_checker.go
index e2c6e1c..ef112c3 100644
--- a/v23/syncbase/nosql/internal/query/query_checker/query_checker.go
+++ b/v23/syncbase/nosql/internal/query/query_checker/query_checker.go
@@ -104,7 +104,7 @@
}
// Like expressions require operand2 to be a string literal that must be validated.
- if e.Operator.Type == query_parser.Like {
+ if e.Operator.Type == query_parser.Like || e.Operator.Type == query_parser.NotLike {
if e.Operand2.Type != query_parser.TypStr {
return syncql.NewErrLikeExpressionsRequireRhsString(db.GetContext(), e.Off)
}
@@ -119,9 +119,13 @@
if err != nil {
return err
}
- // Optimization: If like argument contains no wildcards, convert the expression to equals.
+ // Optimization: If like/not like argument contains no wildcards, convert the expression to equals/not equals.
if !foundWildcard {
- e.Operator.Type = query_parser.Equal
+ if e.Operator.Type == query_parser.Like {
+ e.Operator.Type = query_parser.Equal
+ } else { // not like
+ e.Operator.Type = query_parser.NotEqual
+ }
// Since this is no longer a like expression, we need to unescape
// any escaped chars (i.e., "\\", "\_" and "\%" become
// "\", "_" and "%", respectively).
@@ -147,7 +151,7 @@
}
// k as an operand must be the first operand, the operator must be
- // = | <> | > | >= | < | <= | like and the 2nd operand must be a string literal.
+ // = | <> | > | >= | < | <= | like | not like and the 2nd operand must be a string literal.
if (IsKey(e.Operand1) &&
((e.Operator.Type != query_parser.Equal &&
e.Operator.Type != query_parser.GreaterThan &&
@@ -155,7 +159,8 @@
e.Operator.Type != query_parser.LessThan &&
e.Operator.Type != query_parser.LessThanOrEqual &&
e.Operator.Type != query_parser.Like &&
- e.Operator.Type != query_parser.NotEqual) ||
+ e.Operator.Type != query_parser.NotEqual &&
+ e.Operator.Type != query_parser.NotLike) ||
e.Operand2.Type != query_parser.TypStr)) || IsKey(e.Operand2) {
return syncql.NewErrKeyExpressionForm(db.GetContext(), e.Off)
}
@@ -365,10 +370,7 @@
return o.Type == query_parser.TypExpr
}
-func computeKeyRangeForPrefix(prefix string) query_db.KeyRange {
- if prefix == "" {
- return KeyRangeAll
- }
+func afterPrefix(prefix string) string {
// Copied from syncbase.
limit := []byte(prefix)
for len(limit) > 0 {
@@ -379,7 +381,24 @@
break // no carry
}
}
- return query_db.KeyRange{prefix, string(limit)}
+ return string(limit)
+}
+
+func computeKeyRangeForLike(prefix string) query_db.KeyRange {
+ if prefix == "" {
+ return KeyRangeAll
+ }
+ return query_db.KeyRange{prefix, afterPrefix(prefix)}
+}
+
+func computeKeyRangesForNotLike(prefix string) *query_db.KeyRanges {
+ if prefix == "" {
+ return &query_db.KeyRanges{KeyRangeAll}
+ }
+ return &query_db.KeyRanges{
+ query_db.KeyRange{"", prefix},
+ query_db.KeyRange{afterPrefix(prefix), ""},
+ }
}
// The limit for a single value range is simply a zero byte appended.
@@ -466,7 +485,9 @@
case query_parser.GreaterThanOrEqual:
return &query_db.KeyRanges{query_db.KeyRange{expr.Operand2.Str, MaxRangeLimit}}
case query_parser.Like:
- return &query_db.KeyRanges{computeKeyRangeForPrefix(expr.Operand2.Prefix)}
+ return &query_db.KeyRanges{computeKeyRangeForLike(expr.Operand2.Prefix)}
+ case query_parser.NotLike:
+ return computeKeyRangesForNotLike(expr.Operand2.Prefix)
case query_parser.LessThan:
return &query_db.KeyRanges{query_db.KeyRange{"", expr.Operand2.Str}}
case query_parser.LessThanOrEqual:
diff --git a/v23/syncbase/nosql/internal/query/query_checker/query_checker_test.go b/v23/syncbase/nosql/internal/query/query_checker/query_checker_test.go
index 5537b48..dc7fa12 100644
--- a/v23/syncbase/nosql/internal/query/query_checker/query_checker_test.go
+++ b/v23/syncbase/nosql/internal/query/query_checker/query_checker_test.go
@@ -92,6 +92,8 @@
{"select k, v.name from Customer where k = \"foo\""},
{"select v from Customer where t = \"Foo.Bar\""},
{"select k, v from Customer where t = \"Foo.Bar\" and k like \"abc%\" limit 100 offset 200"},
+ {"select v.z from Customer where k not like \"foo\""},
+ {"select v.z from Customer where k not like \"foo%\""},
{"select v from Customer where v.A = true"},
{"select v from Customer where v.A <> true"},
{"select v from Customer where false = v.A"},
@@ -249,6 +251,13 @@
query_db.KeyRange{"abc", "abd"},
},
},
+ {
+ "select k, v from Customer where k not like \"002%\"",
+ &query_db.KeyRanges{
+ query_db.KeyRange{"", "002"},
+ query_db.KeyRange{"003", ""},
+ },
+ },
}
for _, test := range basic {
@@ -375,7 +384,6 @@
{"select v.z from Customer where k >= v.y", syncql.NewErrKeyExpressionForm(db.GetContext(), 31)},
{"select v.z from Customer where \"abc%\" = k", syncql.NewErrKeyExpressionForm(db.GetContext(), 31)},
{"select v.z from Customer where k like \"a\\bc%\"", syncql.NewErrInvalidEscapedChar(db.GetContext(), 38)},
- {"select v.z from Customer where k not like \"foo\"", syncql.NewErrKeyExpressionForm(db.GetContext(), 31)},
{"select v from Customer where v.A > false", syncql.NewErrBoolInvalidExpression(db.GetContext(), 33)},
{"select v from Customer where true <= v.A", syncql.NewErrBoolInvalidExpression(db.GetContext(), 34)},
{"select v from Customer where Foo(\"2015/07/22\", true, 3.14157) = true", syncql.NewErrFunctionNotFound(db.GetContext(), 29, "Foo")},
diff --git a/v23/syncbase/nosql/internal/query/test/query_test.go b/v23/syncbase/nosql/internal/query/test/query_test.go
index 7acf8ec..8f24388 100644
--- a/v23/syncbase/nosql/internal/query/test/query_test.go
+++ b/v23/syncbase/nosql/internal/query/test/query_test.go
@@ -64,7 +64,7 @@
}
// Keys and keyRanges are both sorted low to high, so we can increment
// keyRangesCursor if the keyRange.Limit is < the key.
- if kvs.keyRanges[kvs.keyRangesCursor].Limit < kvs.table.rows[kvs.cursor].key {
+ if compareKeyToLimit(kvs.table.rows[kvs.cursor].key, kvs.keyRanges[kvs.keyRangesCursor].Limit) > 0 {
kvs.keyRangesCursor++
if kvs.keyRangesCursor >= len(kvs.keyRanges) {
return false
@@ -608,7 +608,7 @@
},
},
{
- // Select keys & values for all records with a key prefix of "001".
+ // 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{
@@ -620,6 +620,35 @@
},
},
{
+ // Select keys & values for all records with a key prefix NOT LIKE "002%".
+ "select k, v from Customer where k not like \"002%\"",
+ []string{"k", "v"},
+ [][]*vdl.Value{
+ []*vdl.Value{vdl.ValueOf(custTable.rows[0].key), custTable.rows[0].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[1].key), custTable.rows[1].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[2].key), custTable.rows[2].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[3].key), custTable.rows[3].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[9].key), custTable.rows[9].value},
+ },
+ },
+ {
+ // Select keys & values for all records with a key prefix NOT LIKE "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(custTable.rows[0].key), custTable.rows[0].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[1].key), custTable.rows[1].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[2].key), custTable.rows[2].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[3].key), custTable.rows[3].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[5].key), custTable.rows[5].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[6].key), custTable.rows[6].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[7].key), custTable.rows[7].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[8].key), custTable.rows[8].value},
+ []*vdl.Value{vdl.ValueOf(custTable.rows[9].key), custTable.rows[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%\"",
diff --git a/v23/syncbase/nosql/syncql/syncql.vdl b/v23/syncbase/nosql/syncql/syncql.vdl
index a57e55a..75b58e2 100644
--- a/v23/syncbase/nosql/syncql/syncql.vdl
+++ b/v23/syncbase/nosql/syncql/syncql.vdl
@@ -71,7 +71,7 @@
"en": "[{off}]Select field must be 'k' or 'v[{.<ident>}...]'.",
}
KeyExpressionForm(off int64) {
- "en": "[{off}]Key (i.e., 'k') expressions must be of form 'k [=|<>|>|>=|<|<=|like] <string-literal>'.",
+ "en": "[{off}]Key (i.e., 'k') expressions must be of form 'k [=|<>|>|>=|<|<=|like|not like] <string-literal>'.",
}
KeyValueStreamError(off int64, err error) {
"en": "[{off}]KeyValueStream error: {err}.",
diff --git a/v23/syncbase/nosql/syncql/syncql.vdl.go b/v23/syncbase/nosql/syncql/syncql.vdl.go
index f49a3af..81531e6 100644
--- a/v23/syncbase/nosql/syncql/syncql.vdl.go
+++ b/v23/syncbase/nosql/syncql/syncql.vdl.go
@@ -36,7 +36,7 @@
ErrIsIsNotRequireRhsNil = verror.Register("v.io/syncbase/v23/syncbase/nosql/syncql.IsIsNotRequireRhsNil", verror.NoRetry, "{1:}{2:} [{3}]'Is/is not' expressions require right operand to be nil.")
ErrInvalidEscapedChar = verror.Register("v.io/syncbase/v23/syncbase/nosql/syncql.InvalidEscapedChar", verror.NoRetry, "{1:}{2:} [{3}Expected backslash, percent, or underscore after backslash.]")
ErrInvalidSelectField = verror.Register("v.io/syncbase/v23/syncbase/nosql/syncql.InvalidSelectField", verror.NoRetry, "{1:}{2:} [{3}]Select field must be 'k' or 'v[{.<ident>}...]'.")
- ErrKeyExpressionForm = verror.Register("v.io/syncbase/v23/syncbase/nosql/syncql.KeyExpressionForm", verror.NoRetry, "{1:}{2:} [{3}]Key (i.e., 'k') expressions must be of form 'k [=|<>|>|>=|<|<=|like] <string-literal>'.")
+ ErrKeyExpressionForm = verror.Register("v.io/syncbase/v23/syncbase/nosql/syncql.KeyExpressionForm", verror.NoRetry, "{1:}{2:} [{3}]Key (i.e., 'k') expressions must be of form 'k [=|<>|>|>=|<|<=|like|not like] <string-literal>'.")
ErrKeyValueStreamError = verror.Register("v.io/syncbase/v23/syncbase/nosql/syncql.KeyValueStreamError", verror.NoRetry, "{1:}{2:} [{3}]KeyValueStream error: {4}.")
ErrLikeExpressionsRequireRhsString = verror.Register("v.io/syncbase/v23/syncbase/nosql/syncql.LikeExpressionsRequireRhsString", verror.NoRetry, "{1:}{2:} [{3}]Like expressions require right operand of type <string-literal>.")
ErrLimitMustBeGe0 = verror.Register("v.io/syncbase/v23/syncbase/nosql/syncql.LimitMustBeGe0", verror.NoRetry, "{1:}{2:} [{3}]Limit must be > 0.")
@@ -72,7 +72,7 @@
i18n.Cat().SetWithBase(i18n.LangID("en"), i18n.MsgID(ErrIsIsNotRequireRhsNil.ID), "{1:}{2:} [{3}]'Is/is not' expressions require right operand to be nil.")
i18n.Cat().SetWithBase(i18n.LangID("en"), i18n.MsgID(ErrInvalidEscapedChar.ID), "{1:}{2:} [{3}Expected backslash, percent, or underscore after backslash.]")
i18n.Cat().SetWithBase(i18n.LangID("en"), i18n.MsgID(ErrInvalidSelectField.ID), "{1:}{2:} [{3}]Select field must be 'k' or 'v[{.<ident>}...]'.")
- i18n.Cat().SetWithBase(i18n.LangID("en"), i18n.MsgID(ErrKeyExpressionForm.ID), "{1:}{2:} [{3}]Key (i.e., 'k') expressions must be of form 'k [=|<>|>|>=|<|<=|like] <string-literal>'.")
+ i18n.Cat().SetWithBase(i18n.LangID("en"), i18n.MsgID(ErrKeyExpressionForm.ID), "{1:}{2:} [{3}]Key (i.e., 'k') expressions must be of form 'k [=|<>|>|>=|<|<=|like|not like] <string-literal>'.")
i18n.Cat().SetWithBase(i18n.LangID("en"), i18n.MsgID(ErrKeyValueStreamError.ID), "{1:}{2:} [{3}]KeyValueStream error: {4}.")
i18n.Cat().SetWithBase(i18n.LangID("en"), i18n.MsgID(ErrLikeExpressionsRequireRhsString.ID), "{1:}{2:} [{3}]Like expressions require right operand of type <string-literal>.")
i18n.Cat().SetWithBase(i18n.LangID("en"), i18n.MsgID(ErrLimitMustBeGe0.ID), "{1:}{2:} [{3}]Limit must be > 0.")
diff --git a/x/ref/services/syncbase/server/nosql/database.go b/x/ref/services/syncbase/server/nosql/database.go
index 2943a52..5443660 100644
--- a/x/ref/services/syncbase/server/nosql/database.go
+++ b/x/ref/services/syncbase/server/nosql/database.go
@@ -509,8 +509,8 @@
}
// We've reached the end of the iterator for this keyRange.
// Jump to the next one.
+ s.it[s.curr] = nil
s.curr++
- s.it = nil
s.validRow = false
}
// There are no more prefixes to scan.
