Skip to content

Commit 81443e3

Browse files
isrgomezisrgomez
committed
WL12737: DevAPI: Add overlaps and not_overlaps as operator
This worklog adds support to the JSON_OVERLAPS() function by the overlaps and not_overlaps operators, available through the following new expression infix operators: OVERLAPS NOT OVERLAPS
1 parent adde64e commit 81443e3

File tree

2 files changed

+146
-13
lines changed

2 files changed

+146
-13
lines changed

‎lib/mysqlx/expr.py

Lines changed: 33 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -124,6 +124,7 @@ class TokenType(object):
124124
DAY_MINUTE = 86
125125
DAY_HOUR = 87
126126
YEAR_MONTH = 88
127+
OVERLAPS = 89
127128
# pylint: enable=C0103
128129

129130
_INTERVAL_UNITS = set([
@@ -157,6 +158,7 @@ class TokenType(object):
157158
"not": TokenType.NOT,
158159
"like": TokenType.LIKE,
159160
"in": TokenType.IN,
161+
"overlaps": TokenType.OVERLAPS,
160162
"regexp": TokenType.REGEXP,
161163
"between": TokenType.BETWEEN,
162164
"interval": TokenType.INTERVAL,
@@ -235,6 +237,7 @@ class TokenType(object):
235237
"<": "<",
236238
"<=": "<=",
237239
"&": "&",
240+
"&&": "&&",
238241
"|": "|",
239242
"<<": "<<",
240243
">>": ">>",
@@ -245,7 +248,8 @@ class TokenType(object):
245248
"~": "~",
246249
"%": "%",
247250
"cast": "cast",
248-
"cont_in": "cont_in"
251+
"cont_in": "cont_in",
252+
"overlaps": "overlaps"
249253
}
250254

251255
_UNARY_OPERATORS = {
@@ -262,7 +266,8 @@ class TokenType(object):
262266
"regexp": "not_regexp",
263267
"like": "not_like",
264268
"in": "not_in",
265-
"cont_in": "not_cont_in"
269+
"cont_in": "not_cont_in",
270+
"overlaps": "not_overlaps",
266271
}
267272

268273

@@ -549,7 +554,7 @@ def lex(self):
549554
token = Token(TokenType.EQ, "==", 1)
550555
elif char == "&":
551556
if self.next_char_is(i, "&"):
552-
token = Token(TokenType.ANDAND, char, 2)
557+
token = Token(TokenType.ANDAND, "&&", 2)
553558
else:
554559
token = Token(TokenType.BITAND, char)
555560
elif char == "^":
@@ -653,10 +658,10 @@ def paren_expr_list(self):
653658
exprs = []
654659
self.consume_token(TokenType.LPAREN)
655660
if not self.cur_token_type_is(TokenType.RPAREN):
656-
exprs.append(self.expr().get_message())
661+
exprs.append(self._expr().get_message())
657662
while self.cur_token_type_is(TokenType.COMMA):
658663
self.pos += 1
659-
exprs.append(self.expr().get_message())
664+
exprs.append(self._expr().get_message())
660665
self.consume_token(TokenType.RPAREN)
661666
return exprs
662667

@@ -848,7 +853,7 @@ def parse_json_array(self):
848853
msg = Message("Mysqlx.Expr.Array")
849854
while self.pos < len(self.tokens) and \
850855
not self.cur_token_type_is(TokenType.RSQBRACKET):
851-
msg["value"].extend([self.expr().get_message()])
856+
msg["value"].extend([self._expr().get_message()])
852857
if not self.cur_token_type_is(TokenType.COMMA):
853858
break
854859
self.consume_token(TokenType.COMMA)
@@ -870,7 +875,7 @@ def parse_json_doc(self):
870875
item = Message("Mysqlx.Expr.Object.ObjectField")
871876
item["key"] = self.consume_token(TokenType.LSTRING)
872877
self.consume_token(TokenType.COLON)
873-
item["value"] = self.expr().get_message()
878+
item["value"] = self._expr().get_message()
874879
msg["fld"].extend([item.get_message()])
875880
if not self.cur_token_type_is(TokenType.COMMA):
876881
break
@@ -912,7 +917,7 @@ def cast(self):
912917
"""
913918
operator = Message("Mysqlx.Expr.Operator", name="cast")
914919
self.consume_token(TokenType.LPAREN)
915-
operator["param"].extend([self.expr().get_message()])
920+
operator["param"].extend([self._expr().get_message()])
916921
self.consume_token(TokenType.AS)
917922

918923
type_scalar = build_bytes_scalar(str.encode(self.cast_data_type()))
@@ -991,7 +996,7 @@ def atomic_expr(self):
991996
elif token.token_type == TokenType.CAST:
992997
return self.cast()
993998
elif token.token_type == TokenType.LPAREN:
994-
expr = self.expr()
999+
expr = self._expr()
9951000
self.expect_token(TokenType.RPAREN)
9961001
return expr
9971002
elif token.token_type in [TokenType.PLUS, TokenType.MINUS]:
@@ -1126,6 +1131,10 @@ def ilri_expr(self):
11261131
else:
11271132
op_name = "cont_in"
11281133
params.append(self.comp_expr().get_message())
1134+
elif self.cur_token_type_is(TokenType.OVERLAPS):
1135+
self.consume_token(TokenType.OVERLAPS)
1136+
params.append(self.comp_expr().get_message())
1137+
11291138
elif self.cur_token_type_is(TokenType.LIKE):
11301139
self.consume_token(TokenType.LIKE)
11311140
params.append(self.comp_expr().get_message())
@@ -1168,7 +1177,7 @@ def or_expr(self):
11681177
return self.parse_left_assoc_binary_op_expr(
11691178
set([TokenType.OR, TokenType.OROR]), self.xor_expr)
11701179

1171-
def expr(self, reparse=False):
1180+
def _expr(self, reparse=False):
11721181
if reparse:
11731182
self.tokens = []
11741183
self.pos = 0
@@ -1177,6 +1186,17 @@ def expr(self, reparse=False):
11771186
self.lex()
11781187
return self.or_expr()
11791188

1189+
def expr(self, reparse=False):
1190+
expression = self._expr(reparse)
1191+
used_tokens = self.pos
1192+
if self.pos_token_type_is(len(self.tokens) - 2, TokenType.AS):
1193+
used_tokens += 2
1194+
if used_tokens < len(self.tokens):
1195+
raise ValueError("Unused token types {} found in expression at "
1196+
"position: {}".format(self.tokens[self.pos:],
1197+
self.pos))
1198+
return expression
1199+
11801200
def parse_table_insert_field(self):
11811201
return Message("Mysqlx.Crud.Column",
11821202
name=self.consume_token(TokenType.IDENT))
@@ -1205,7 +1225,7 @@ def parse_table_select_projection(self):
12051225
if not first:
12061226
self.consume_token(TokenType.COMMA)
12071227
first = False
1208-
projection = Message("Mysqlx.Crud.Projection", source=self.expr())
1228+
projection = Message("Mysqlx.Crud.Projection", source=self._expr())
12091229
if self.cur_token_type_is(TokenType.AS):
12101230
self.consume_token(TokenType.AS)
12111231
projection["alias"] = self.consume_token(TokenType.IDENT)
@@ -1222,7 +1242,7 @@ def parse_order_spec(self):
12221242
if not first:
12231243
self.consume_token(TokenType.COMMA)
12241244
first = False
1225-
order = Message("Mysqlx.Crud.Order", expr=self.expr())
1245+
order = Message("Mysqlx.Crud.Order", expr=self._expr())
12261246
if self.cur_token_type_is(TokenType.ORDERBY_ASC):
12271247
order["direction"] = mysqlxpb_enum(
12281248
"Mysqlx.Crud.Order.Direction.ASC")
@@ -1241,5 +1261,5 @@ def parse_expr_list(self):
12411261
if not first:
12421262
self.consume_token(TokenType.COMMA)
12431263
first = False
1244-
expr_list.append(self.expr().get_message())
1264+
expr_list.append(self._expr().get_message())
12451265
return expr_list

‎tests/test_mysqlx_crud.py

Lines changed: 113 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -762,6 +762,119 @@ def test_cont_in_operator(self):
762762
"result was {}".format(test, result))
763763
self.schema.drop_collection(collection_name)
764764

765+
@unittest.skipIf(tests.MYSQL_VERSION < (8, 0, 17),
766+
"OVERLAPS operator unavailable")
767+
def test_overlaps_operator(self):
768+
collection_name = "{0}.test".format(self.schema_name)
769+
collection = self.schema.create_collection(collection_name)
770+
collection.add({
771+
"_id": "a6f4b93e1a264a108393524f29546a8c",
772+
"title": "AFRICAN EGG",
773+
"description": "A Fast-Paced Documentary of a Pastry Chef And a "
774+
"Dentist who must Pursue a Forensic Psychologist in "
775+
"The Gulf of Mexico",
776+
"releaseyear": 2006,
777+
"language": "English",
778+
"duration": 130,
779+
"rating": "G",
780+
"genre": "Science fiction",
781+
"actors": [{
782+
"name": "MILLA PECK",
783+
"country": "Mexico",
784+
"birthdate": "12 Jan 1984"
785+
}, {
786+
"name": "VAL BOLGER",
787+
"country": "Botswana",
788+
"birthdate": "26 Jul 1975"
789+
}, {
790+
"name": "SCARLETT BENING",
791+
"country": "Syria",
792+
"birthdate": "16 Mar 1978"
793+
}],
794+
"additionalinfo": {
795+
"director": "Sharice Legaspi",
796+
"writers": ["Rusty Couturier", "Angelic Orduno", "Carin Postell"],
797+
"productioncompanies": ["Qvodrill", "Indigoholdings"]
798+
}
799+
}).execute()
800+
801+
test_cases = [
802+
("(1+5) overlaps (1, 2, 3, 4, 5)", None),
803+
("(1>5) overlaps (true, false)", None),
804+
("('a'>'b') overlaps (true, false)", None),
805+
("(1>5) overlaps [true, false]", None),
806+
("[1>5] overlaps [true, false]", True),
807+
("[(1+5)] overlaps [1, 2, 3, 4, 5]", False),
808+
("[(1+4)] overlaps [1, 2, 3, 4, 5]", True),
809+
("('a'>'b') overlaps [true, false]", None),
810+
("true overlaps [(1>5), !(false), (true || false), (false && true)]",
811+
True),
812+
("true overlaps ((1>5), !(false), (true || false), (false && true))",
813+
None),
814+
("{ 'name' : 'MILLA PECK' } overlaps actors", False),
815+
("{\"field\":true} overlaps (\"mystring\", 124, myvar, othervar.jsonobj)",
816+
None),
817+
("actor.name overlaps ['a name', null, (1<5-4), myvar.jsonobj.name]",
818+
None),
819+
("!false && true overlaps [true]", True),
820+
("1-5/2*2 > 3-2/1*2 overlaps [true, false]", None),
821+
("true IN [1-5/2*2 > 3-2/1*2]", False),
822+
("'African Egg' overlaps ('African Egg', 1, true, NULL, [0,1,2], "
823+
"{ 'title' : 'Atomic Firefighter' })", None),
824+
("1 overlaps ('African Egg', 1, true, NULL, [0,1,2], "
825+
"{ 'title' : 'Atomic Firefighter' })", None),
826+
("true overlaps ('African Egg', 1, false, NULL, [0,1,2], "
827+
"{ 'title' : 'Atomic Firefighter' })", None),
828+
("false overlaps ('African Egg', 1, true, NULL, [0,1,2], "
829+
"{ 'title' : 'Atomic Firefighter' })", None),
830+
("false overlaps ('African Egg', 1, true, 'No null', [0,1,2], "
831+
"{ 'title' : 'Atomic Firefighter' })", None),
832+
("[0,1,2] overlaps ('African Egg', 1, true, NULL, [0,1,2], "
833+
"{ 'title' : 'Atomic Firefighter' })", None),
834+
("{ 'title' : 'Atomic Firefighter' } overlaps ('African Egg', 1, true, "
835+
"NULL, [0,1,2], { 'title' : 'Atomic Firefighter' })", None),
836+
("title overlaps ('African Egg', 'The Witcher', 'Jurassic Perk')", None),
837+
("releaseyear overlaps (2006, 2010, 2017)", None),
838+
("'African overlaps' in movietitle", None),
839+
("0 NOT overlaps [1,2,3]", True),
840+
("1 NOT overlaps [1,2,3]", False),
841+
("[0] NOT overlaps [1,2,3]", True),
842+
("[1] NOT overlaps [1,2,3]", False),
843+
("[!false && true] OVERLAPS [true]", True),
844+
("[!false AND true] OVERLAPS [true]", True),
845+
("[!false & true] OVERLAPS [true]", False),
846+
("'' IN title", False),
847+
("title overlaps ('', ' ')", None),
848+
("title overlaps ['', ' ']", False),
849+
("[\"Rusty Couturier\", \"Angelic Orduno\", \"Carin Postell\"] IN "
850+
"additionalinfo.writers", True),
851+
("{ \"name\" : \"MILLA PECK\", \"country\" : \"Mexico\", "
852+
"\"birthdate\": \"12 Jan 1984\"} IN actors", True),
853+
("releaseyear IN [2006, 2007, 2008]", True),
854+
("true overlaps title", False),
855+
("false overlaps genre", False),
856+
("'Sharice Legaspi' overlaps additionalinfo.director", True),
857+
("'Mexico' overlaps actors[*].country", True),
858+
("'Angelic Orduno' overlaps additionalinfo.writers", True),
859+
("[([1,2] overlaps [1,2])] overlaps [false] invalid [true]", None),
860+
("[([1] overlaps [2])] overlaps [3] invalid [true] as res", None),
861+
("[] []", None),
862+
("[] TRUE as res", None)
863+
]
864+
865+
for test in test_cases:
866+
try:
867+
result = collection.find() \
868+
.fields("{0} as res".format(test[0])) \
869+
.execute().fetch_one()
870+
except:
871+
self.assertEqual(None, test[1], "For test case {} "
872+
"exeption was not expected.".format(test))
873+
else:
874+
self.assertEqual(result['res'], test[1], "For test case {} "
875+
"result was {}".format(test, result))
876+
self.schema.drop_collection(collection_name)
877+
765878
def test_ilri_expressions(self):
766879
collection_name = "{0}.test".format(self.schema_name)
767880
collection = self.schema.create_collection(collection_name)

0 commit comments

Comments
 (0)