1# Copyright (c) 2021, Oracle and/or its affiliates. 2# 3# This program is free software; you can redistribute it and/or modify 4# it under the terms of the GNU General Public License, version 2.0, as 5# published by the Free Software Foundation. 6# 7# This program is also distributed with certain software (including 8# but not limited to OpenSSL) that is licensed under separate terms, 9# as designated in a particular file or component or in included license 10# documentation. The authors of MySQL hereby grant you an 11# additional permission to link the program and your derivative works 12# with the separately licensed software that they have included with 13# MySQL. 14# 15# Without limiting anything contained in the foregoing, this file, 16# which is part of MySQL Connector/Python, is also subject to the 17# Universal FOSS Exception, version 1.0, a copy of which can be found at 18# http://oss.oracle.com/licenses/universal-foss-exception. 19# 20# This program is distributed in the hope that it will be useful, but 21# WITHOUT ANY WARRANTY; without even the implied warranty of 22# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 23# See the GNU General Public License, version 2.0, for more details. 24# 25# You should have received a copy of the GNU General Public License 26# along with this program; if not, write to the Free Software Foundation, Inc., 27# 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA 28 29import threading 30import time 31import unittest 32 33import mysqlx 34import tests 35 36 37@unittest.skipIf(tests.MYSQL_VERSION < (8, 0, 25), "XPlugin not compatible") 38class TableSelectTests(tests.MySQLxTests): 39 """Tests for table.select(). """ 40 41 @tests.foreach_session() 42 def test_table_select1(self): 43 self.session.sql("drop table if exists t1").execute() 44 """Test the table.select with where.""" 45 self.session.sql("create table t1(a int , b int)").execute() 46 table = self.schema.get_table("t1") 47 table.insert().values(1, 1).values(2, 1).values(3, 2).execute() 48 result = table.select("a").where("b== 1").execute() 49 row = result.fetch_all() 50 self.assertEqual(len(row), 2) 51 self.assertEqual(row[1]["a"], 2) 52 self.session.sql("drop table if exists t1").execute() 53 54 @tests.foreach_session() 55 def test_table_select2(self): 56 """Test the table.select with sort and limit.""" 57 self.session.sql("drop table if exists t2").execute() 58 self.session.sql("create table t2(a int , b varchar(32))").execute() 59 table = self.schema.get_table("t2") 60 table.insert().values(1, "a").values(2, "c").values(3, "b").values( 61 4, "d" 62 ).execute() 63 result = ( 64 table.select("a").sort("b ASC").limit(2).execute() 65 ) # sort() is deprecated since 8.0.12, use order_by() 66 row = result.fetch_all() 67 self.assertEqual(len(row), 2) 68 self.assertEqual(row[1]["a"], 3) 69 self.session.sql("drop table if exists t2").execute() 70 71 @tests.foreach_session() 72 def test_table_select3(self): 73 """Test the table.select with bind.""" 74 self.session.sql("drop table if exists t3").execute() 75 self.session.sql("create table t3(a int , b int)").execute() 76 table = self.schema.get_table("t3") 77 table.insert().values(1, 1).values(2, 1).values(3, 2).execute() 78 result = table.select("a").where("b== :b").bind("b", 1).execute() 79 row = result.fetch_all() 80 self.assertEqual(len(row), 2) 81 self.assertEqual(row[1]["a"], 2) 82 self.session.sql("drop table if exists t3").execute() 83 84 @tests.foreach_session() 85 def test_table_select4(self): 86 """Test the table.select with no data.""" 87 self.session.sql("drop table if exists t4").execute() 88 self.session.sql("create table t4(a int , b int)").execute() 89 table = self.schema.get_table("t4") 90 result = table.select("a").where("b== 1").execute() 91 row = result.fetch_all() 92 self.assertEqual(row, []) 93 self.session.sql("drop table if exists t4").execute() 94 95 @tests.foreach_session() 96 def test_table_select5(self): 97 """Test the table.select on a invalid column.""" 98 self.session.sql("drop table if exists t5").execute() 99 self.session.sql("create table t5(a int , b int)").execute() 100 table = self.schema.get_table("t5") 101 table.insert().values(1, 1).values(2, 1).values(3, 2).execute() 102 self.assertRaises( 103 mysqlx.OperationalError, 104 table.select("abc").execute, 105 ) 106 self.session.sql("drop table if exists t5").execute() 107 108 @tests.foreach_session() 109 def test_table_select6(self): 110 """Test the table.select.""" 111 self.session.sql("drop table if exists t6").execute() 112 self.session.sql( 113 "create table t6(a int , name varchar(32),age int)" 114 ).execute() 115 table = self.schema.get_table("t6") 116 table.insert().values(1, "a", 20).values(2, "a", 21).values( 117 3, "b", 34 118 ).values(4, "b", 35).execute() 119 result = table.select().where("a > 1 and a < 4").execute() 120 row = result.fetch_all() 121 self.assertEqual(len(row), 2) 122 self.assertEqual(row[0]["name"], "a") 123 self.assertEqual(row[1]["name"], "b") 124 self.session.sql("drop table if exists t6").execute() 125 126 @tests.foreach_session() 127 def test_table_select7(self): 128 """Test the table.select with group by.""" 129 self.session.sql("drop table if exists t7").execute() 130 self.session.sql("set sql_mode=''").execute() 131 self.session.sql( 132 "create table t7(a int , name varchar(32),age int)" 133 ).execute() 134 table = self.schema.get_table("t7") 135 table.insert().values(1, "a", 20).values(2, "a", 21).values( 136 3, "b", 34 137 ).values(4, "b", 35).execute() 138 result = ( 139 table.select() 140 .group_by("name") 141 .sort("age ASC") 142 .having("age > 30") 143 .execute() 144 ) 145 row = result.fetch_all() 146 self.assertEqual(len(row), 1) 147 self.assertEqual(row[0]["age"], 34) 148 self.session.sql("drop table if exists t7").execute() 149 150 @tests.foreach_session() 151 def test_table_select8(self): 152 """Test the table.select with param list.""" 153 self.session.sql("drop table if exists t8").execute() 154 self.session.sql("create table t8(a int , b int, c int)").execute() 155 table = self.schema.get_table("t8") 156 table.insert().values(1, 3, 1).values(2, 1, 2).values( 157 3, 2, 3 158 ).execute() 159 result = table.select("a", "b").where("c== 1").execute() 160 row = result.fetch_all() 161 self.assertEqual(row[0]["a"], 1) 162 self.assertEqual(row[0]["b"], 3) 163 self.session.sql("drop table if exists t8").execute() 164 165 @tests.foreach_session() 166 def test_table_select9(self): 167 """Test the table.select with group by and param.""" 168 self.session.sql("drop table if exists t9").execute() 169 self.session.sql("set sql_mode=''").execute() 170 self.session.sql("create table t9(a int , b int)").execute() 171 table = self.schema.get_table("t9") 172 table.insert().values(1, 10).values(1, 10).values(2, 20).values( 173 2, 30 174 ).execute() 175 result = table.select("a", "b").group_by("a", "b").execute() 176 row = result.fetch_all() 177 self.assertEqual(row[0]["b"], 10) 178 self.assertEqual(row[2]["b"], 30) 179 self.session.sql("drop table if exists t9").execute() 180 181 @tests.foreach_session() 182 def test_table_select10(self): 183 self.session.sql("drop table if exists t10").execute() 184 self.session.sql("create table t10(a int , b int)").execute() 185 table = self.schema.get_table("t10") 186 table.insert().values(1, 10).values(1, 11).values(2, 10).values( 187 2, 11 188 ).execute() 189 result = table.select("a", "b").sort("a ASC", "b DESC").execute() 190 row = result.fetch_all() 191 self.assertEqual(row[0]["b"], 11) 192 self.assertEqual(row[3]["b"], 10) 193 self.session.sql("drop table if exists t10").execute() 194 195 @tests.foreach_session() 196 def test_table_select11(self): 197 """Test for bug25519251.""" 198 self.session.sql("drop table if exists t11").execute() 199 self.session.sql( 200 "create table t11 (age INT, name VARCHAR(50))" 201 ).execute() 202 self.session.sql("INSERT INTO t11 VALUES (21, 'Fred')").execute() 203 self.session.sql("INSERT INTO t11 VALUES (28, 'Barney')").execute() 204 self.session.sql("INSERT INTO t11 VALUES (42, 'Wilma')").execute() 205 self.session.sql("INSERT INTO t11 VALUES (67, 'Betty')").execute() 206 table = self.schema.get_table("t11") 207 208 result = table.select().order_by("age DESC").execute() 209 row = result.fetch_all() 210 self.assertEqual(row[0]["name"], "Betty") 211 self.assertEqual(row[3]["name"], "Fred") 212 self.session.sql("drop table if exists t11").execute() 213 214 @tests.foreach_session() 215 def test_table_select12(self): 216 """Test table.select with limit() and offset() methods.""" 217 self.session.sql("drop table if exists t12").execute() 218 self.session.sql("create table t12(a int , b int)").execute() 219 table = self.schema.get_table("t12") 220 table.insert().values(1, 10).values(1, 11).values(2, 10).values( 221 2, 11 222 ).execute() 223 result = ( 224 table.select("a", "b").limit(1, 1).execute() 225 ) # limit(x,y) is deprecated since 8.0.12 226 row = result.fetch_all() 227 self.assertEqual(len(row), 1) 228 self.assertEqual(row[0]["b"], 11) 229 self.session.sql("drop table if exists t12").execute() 230 231 @tests.foreach_session() 232 def test_table_select13(self): 233 """Test table.select with negative value to limit() method.""" 234 self.session.sql("drop table if exists t13").execute() 235 self.session.sql("create table t13(a int , b int)").execute() 236 table = self.schema.get_table("t13") 237 table.insert().values(1, 10).values(1, 11).values(2, 10).values( 238 2, 11 239 ).execute() 240 try: 241 table.select("a", "b").limit(-1).execute() 242 except ValueError: 243 # Expected a ValueError 244 pass 245 self.session.sql("drop table if exists t13").execute() 246 247 @tests.foreach_session() 248 def test_table_select14(self): 249 """Test table.select with negative value to offset() method.""" 250 self.session.sql("drop table if exists t14").execute() 251 self.session.sql("create table t14(a int , b int)").execute() 252 table = self.schema.get_table("t14") 253 table.insert().values(1, 10).values(1, 11).values(2, 10).values( 254 2, 11 255 ).execute() 256 try: 257 result = table.select("a", "b").limit(2).offset(-1).execute() 258 except ValueError: 259 # Expected a ValueError 260 pass 261 self.session.sql("drop table if exists t14").execute() 262 263 @tests.foreach_session() 264 def test_table_select15(self): 265 """Test get_columns().""" 266 self.session.sql("drop table if exists t15").execute() 267 self.session.sql( 268 "create table t15(a int , name varchar(32),age int)" 269 ).execute() 270 table = self.schema.get_table("t15") 271 table.insert().values(1, "a", 20).values(2, "a", 21).values( 272 3, "b", 34 273 ).values(4, "b", 35).execute() 274 result = table.select().where("a > 1 and a < 4").execute() 275 cols = result.get_columns() 276 self.session.sql("drop table if exists t15").execute() 277 278 @tests.foreach_session() 279 def test_table_select16(self): 280 """Test new properties of Column.""" 281 self.session.sql("drop table if exists t16").execute() 282 config = tests.get_mysqlx_config() 283 schema_name = config["schema"] 284 self.session.sql( 285 "create table t16(a int , name varchar(32),age int)" 286 ).execute() 287 table = self.schema.get_table("t16") 288 table.insert().values(1, "a", 20).values(2, "a", 21).values( 289 3, "b", 34 290 ).values(4, "b", 35).execute() 291 result = table.select().where("a > 1 and a < 4").execute() 292 cols = result.get_columns() 293 col0 = cols[0] 294 self.assertEqual(col0.schema_name, schema_name) 295 self.assertEqual(col0.table_name, "t16") 296 self.assertEqual(col0.table_label, "t16") 297 self.assertEqual(col0.column_name, "a") 298 self.assertEqual(col0.column_label, "a") 299 self.assertEqual(col0.type, mysqlx.ColumnType.INT) 300 self.assertEqual(col0.length, 11) 301 self.assertEqual(col0.fractional_digits, 0) 302 col1 = cols[1] 303 self.assertEqual(col1.schema_name, schema_name) 304 self.assertEqual(col1.table_name, "t16") 305 self.assertEqual(col1.table_label, "t16") 306 self.assertEqual(col1.column_name, "name") 307 self.assertEqual(col1.column_label, "name") 308 self.assertEqual(col1.type, mysqlx.ColumnType.STRING) 309 self.assertEqual(col1.collation_name, "utf8mb4_0900_ai_ci") 310 self.assertEqual(col1.character_set_name, "utf8mb4") 311 self.session.sql("drop table if exists t16").execute() 312 313 @tests.foreach_session() 314 def test_operator2(self): 315 """Test unary operator not.""" 316 self.session.sql("drop table if exists t1").execute() 317 self.session.sql("create table t1(a int, b boolean)").execute() 318 table = self.schema.get_table("t1") 319 table.insert().values(1, True).values(2, False).execute() 320 result = table.select("not b as b").where("a == 1").execute() 321 row = result.fetch_all() 322 self.assertEqual(row[0]["b"], 0) 323 self.session.sql("drop table if exists t1").execute() 324 325 @tests.foreach_session() 326 def test_operator3(self): 327 """Test binary operator in.""" 328 self.session.sql("drop table if exists t2").execute() 329 self.session.sql("create table t2(a int , b int)").execute() 330 table = self.schema.get_table("t2") 331 table.insert().values(1, 1).values(2, 2).execute() 332 result = table.select("2 IN (0,2,4,6)").execute() 333 result.fetch_all() 334 self.session.sql("drop table if exists t2").execute() 335 336 @tests.foreach_session() 337 def test_operator5(self): 338 self.session.sql("drop table if exists t3").execute() 339 self.session.sql("create table t3(data json)").execute() 340 table = self.schema.get_table("t3") 341 table.insert().values('{"_id":1,"age":20}').execute() 342 result = table.select("data->'$.age' as age").execute() 343 result.fetch_all() 344 self.session.sql("drop table if exists t3").execute() 345 346 # Testing the contains operator with single operand on both sides 347 348 @tests.foreach_session() 349 def test_contains_operator_select1(self): 350 """Test IN operator with string on both sides - With LHS in RHS.""" 351 self.session.sql("drop table if exists t1").execute() 352 self.session.sql("create table t1(a int , b int, c JSON)").execute() 353 table = self.schema.get_table("t1") 354 table.insert().values(1, 1, '{"comp":"abc"}').values( 355 2, 1, '{"comp":"pqr"}' 356 ).values(3, 2, '{"comp":"xyz"}').execute() 357 result = table.select("a").where("'pqr' IN c->'$.comp'").execute() 358 row = result.fetch_all() 359 self.assertEqual(len(row), 1) 360 self.assertEqual(row[0]["a"], 2) 361 self.session.sql("drop table if exists t1").execute() 362 363 @tests.foreach_session() 364 def test_contains_operator_select2(self): 365 """Test IN operator with int as operand - With LHS in RHS.""" 366 self.session.sql("drop table if exists t2").execute() 367 self.session.sql( 368 "create table t2(name varchar(20) , a JSON, c varchar(20))" 369 ).execute() 370 table = self.schema.get_table("t2") 371 table.insert().values("a", '{"age":21}', "abc").values( 372 "b", '{"age":31}', "pqr" 373 ).values("hell", '{"age":22}', "xyz").execute() 374 result = table.select().where("a->$.age IN [21,31]").execute() 375 self.assertEqual(len(result.fetch_all()), 2) 376 self.session.sql("drop table if exists t2").execute() 377 378 @tests.foreach_session() 379 def test_contains_operator_select3(self): 380 """Test IN operator with boolean as operand - With LHS in RHS.""" 381 self.session.sql("drop table if exists t3").execute() 382 self.session.sql("create table t3(n JSON, a json, c JSON)").execute() 383 table = self.schema.get_table("t3") 384 table.insert().values( 385 '{"name":"joy"}', '{"age":18}', '{"comp":"abc"}' 386 ).values('{"name":"happy"}', '{"age":21}', '{"comp":"pqr"}').values( 387 '{"name":"sad"}', '{"age":32}', '{"comp":"xyz"}' 388 ).execute() 389 result = table.select().where("21 IN a->'$.age'").execute() 390 self.assertEqual(len(result.fetch_all()), 1) 391 self.session.sql("drop table if exists t3").execute() 392 393 @tests.foreach_session() 394 def test_contains_operator_select4(self): 395 """Test NOT IN operator with string operand - With LHS not in RHS.""" 396 self.session.sql("drop table if exists t4").execute() 397 self.session.sql("create table t4(n JSON, age int, c JSON)").execute() 398 table = self.schema.get_table("t4") 399 table.insert().values('{"name":"joy"}', 18, '{"comp":"abc"}').values( 400 '{"name":"happy"}', 21, '{"comp":"pqr"}' 401 ).values('{"name":"sad"}', 32, '{"comp":"xyz"}').execute() 402 result = table.select().where("'happy' NOT IN n->'$.name'").execute() 403 self.assertEqual(len(result.fetch_all()), 2) 404 self.session.sql("drop table if exists t4").execute() 405 406 @tests.foreach_session() 407 def test_contains_operator_select5(self): 408 """Test NOT IN operator with int as operand - With LHS not in RHS.""" 409 self.session.sql("drop table if exists t5").execute() 410 self.session.sql("create table t5(n JSON, a JSON, c JSON)").execute() 411 table = self.schema.get_table("t5") 412 table.insert().values( 413 '{"name":"joy"}', '{"age":18}', '{"comp":"abc"}' 414 ).values('{"name":"happy"}', '{"age":21}', '{"comp":"pqr"}').values( 415 '{"name":"sad"}', '{"age":32}', '{"comp":"xyz"}' 416 ).execute() 417 result = table.select().where("21 NOT IN a->'$.age'").execute() 418 self.assertEqual(len(result.fetch_all()), 2) 419 self.session.sql("drop table if exists t5").execute() 420 421 @tests.foreach_session() 422 def test_contains_operator_select6(self): 423 """Test NOT IN operator with boolean as operand - With LHS not in RHS.""" 424 self.session.sql("drop table if exists t6").execute() 425 self.session.sql( 426 "create table t6(name varchar(20) , age int, c json)" 427 ).execute() 428 table = self.schema.get_table("t6") 429 table.insert().values("a", 21, '{"comp":"pqr"}').values( 430 "b", 31, '{"comp":"xyz"}' 431 ).values("e", 22, '{"comp":"xyz"}').execute() 432 result = table.select().where("c->'$.comp' IN ['pqr','abc']").execute() 433 self.assertEqual(len(result.fetch_all()), 1) 434 self.session.sql("drop table if exists t6").execute() 435 436 @tests.foreach_session() 437 def test_contains_operator_select7(self): 438 """Test IN operator with different datatypes as operands.""" 439 self.session.sql("drop table if exists t7").execute() 440 self.session.sql("create table t7(n JSON, a json, c JSON)").execute() 441 table = self.schema.get_table("t7") 442 table.insert().values( 443 '{"name":"joy"}', '{"age":18}', '{"comp":"abc"}' 444 ).values('{"name":"happy"}', '{"age":21}', '{"comp":"pqr"}').values( 445 '{"name":"sad"}', '{"age":32}', '{"comp":"xyz"}' 446 ).execute() 447 result = table.select().where("21 IN n->'$.name'").execute() 448 result1 = table.select().where("'b' IN a->$.age").limit(1).execute() 449 self.assertEqual(len(result.fetch_all()), 0) 450 self.assertEqual(len(result1.fetch_all()), 0) 451 self.session.sql("drop table if exists t7").execute() 452 453 @tests.foreach_session() 454 def test_contains_operator_select8(self): 455 """Test IN operator with single element on LHS and array/list on RHS 456 and vice versa.""" 457 self.session.sql("drop table if exists t8").execute() 458 self.session.sql( 459 "create table t8(id int, n JSON, a JSON, p JSON)" 460 ).execute() 461 table = self.schema.get_table("t8") 462 table.insert().values( 463 1, '{"name":"a"}', '{"age":21}', '{"prof":["x","y"]}' 464 ).values(2, '{"name":"b"}', '{"age":24}', '{"prof":["p","q"]}').values( 465 3, '{"name":"c"}', '{"age":26}', '{"prof":["l","m"]}' 466 ).execute() 467 result = table.select().where("a->$.age IN [21,23,24,28]").execute() 468 result1 = ( 469 table.select() 470 .where("n->'$.name' IN ['a','b','c','d','e']") 471 .execute() 472 ) 473 result2 = table.select().where("a->$.age IN (21,23)").execute() 474 result3 = table.select().where("21 IN (22,23)").limit(1).execute() 475 result4 = table.select().where("['p','q'] IN p->'$.prof'").execute() 476 self.assertEqual(len(result.fetch_all()), 2) 477 self.assertEqual(len(result1.fetch_all()), 3) 478 self.assertEqual(len(result2.fetch_all()), 1) 479 self.assertEqual(len(result3.fetch_all()), 0) 480 self.assertEqual(len(result4.fetch_all()), 1) 481 self.session.sql("drop table if exists t8").execute() 482 483 @tests.foreach_session() 484 def test_contains_operator_select9(self): 485 """Test IN operator with single element on LHS and dict on RHS and 486 vice versa.""" 487 self.session.sql("drop table if exists t9").execute() 488 self.session.sql( 489 "create table t9(id int, name varchar(20), a JSON, ai JSON)" 490 ).execute() 491 table = self.schema.get_table("t9") 492 table.insert().values( 493 1, 494 "a", 495 '{"age":23}', 496 '{"additionalinfo":["reading","music","playing"]}', 497 ).values( 498 2, "b", '{"age":21}', '{"additionalinfo":["boxing","music"]}' 499 ).execute() 500 result = ( 501 table.select().where("'reading' IN ai->$.additionalinfo").execute() 502 ) 503 result1 = table.select().where("'music' IN a->$.age").execute() 504 result2 = ( 505 table.select().where("'music' IN ai->$.additionalinfo").execute() 506 ) 507 self.assertEqual(len(result.fetch_all()), 1) 508 self.assertEqual(len(result1.fetch_all()), 0) 509 self.assertEqual(len(result2.fetch_all()), 2) 510 self.session.sql("drop table if exists t9").execute() 511 512 @tests.foreach_session() 513 def test_contains_operator_select10(self): 514 """Test IN operator with array/list operand on LHS and array/list on 515 RHS.""" 516 self.session.sql("drop table if exists t10").execute() 517 self.session.sql( 518 "create table t10(i JSON, n JSON, a JSON, ai JSON)" 519 ).execute() 520 table = self.schema.get_table("t10") 521 table.insert().values( 522 '{"id":1}', 523 '{"name":"joy"}', 524 '{"age":21}', 525 '{"additionalinfo":["reading","music","playing"]}', 526 ).values( 527 '{"id":2}', 528 '{"name":"happy"}', 529 '{"age":24}', 530 '{"additionalinfo":["playing","painting","boxing"]}', 531 ).execute() 532 result = ( 533 table.select() 534 .where("['playing','painting','boxing'] IN ai->'$.additionalinfo'") 535 .execute() 536 ) 537 result1 = ( 538 table.select() 539 .where('["happy","joy"] IN n->$.name') 540 .limit(1) 541 .execute() 542 ) 543 result2 = ( 544 table.select() 545 .where('["reading"] NOT IN ai->$.additionalinfo') 546 .limit(1) 547 .execute() 548 ) 549 self.assertEqual(len(result.fetch_all()), 1) 550 self.assertEqual(len(result1.fetch_all()), 0) 551 self.assertEqual(len(result2.fetch_all()), 1) 552 self.session.sql("drop table if exists t10").execute() 553 554 @tests.foreach_session() 555 def test_contains_operator_select11(self): 556 """Test IN operator with dict on LHS and dict on RHS.""" 557 self.session.sql("drop table if exists t11").execute() 558 self.session.sql( 559 "create table t11(i JSON, n JSON, a JSON, ai JSON)" 560 ).execute() 561 table = self.schema.get_table("t11") 562 table.insert().values( 563 '{"id":1}', 564 '{"name":"joy"}', 565 '{"age":21}', 566 '{"additionalinfo":[{"company":"xyz","vehicle":"bike"},{"company":"abc","vehicle":"car"},{"company":"mno","vehicle":"zeep"}]}', 567 ).values( 568 '{"id":2}', 569 '{"name":"happy"}', 570 '{"age":24}', 571 '{"additionalinfo":[{"company":"abc","vehicle":"car"},{"company":"pqr","vehicle":"bicycle"}]}', 572 ).execute() 573 result = table.select( 574 '{"company":"abc","vehicle":"car"} IN ai->"$.additionalinfo"' 575 ).execute() 576 result1 = ( 577 table.select() 578 .where('{"vehicle":"car"} NOT IN ai->"$.additionalinfo"') 579 .execute() 580 ) 581 result2 = ( 582 table.select() 583 .where('{"company":"mno"} IN ai->"$.additionalinfo"') 584 .execute() 585 ) 586 self.assertEqual(len(result.fetch_all()), 2) 587 self.assertEqual(len(result1.fetch_all()), 0) 588 self.assertEqual(len(result2.fetch_all()), 1) 589 self.session.sql("drop table if exists t11").execute() 590 591 @tests.foreach_session() 592 def test_contains_operator_select12(self): 593 """Test IN operator with operands having expressions.""" 594 self.session.sql("drop table if exists t12").execute() 595 self.session.sql( 596 "create table t12(id int, name varchar(20), age int)" 597 ).execute() 598 table = self.schema.get_table("t12") 599 table.insert().values(1, "a", 21).values(2, "b", 22).values( 600 3, "c", 32 601 ).execute() 602 result = ( 603 table.select().where("(1>5) IN (true, false)").limit(1).execute() 604 ) 605 result1 = ( 606 table.select() 607 .where("('a'>'b') in (true, false)") 608 .limit(1) 609 .execute() 610 ) 611 result2 = ( 612 table.select() 613 .where( 614 "true IN [(1>5), !(false), (true || false), (false && true)]" 615 ) 616 .limit(1) 617 .execute() 618 ) 619 self.assertEqual(len(result.fetch_all()), 1) 620 self.assertEqual(len(result1.fetch_all()), 1) 621 self.assertEqual(len(result2.fetch_all()), 1) 622 self.session.sql("drop table if exists t12").execute() 623 624 @tests.foreach_session() 625 def test_contains_operator_select13(self): 626 """Test IN operator with operands having expressions.""" 627 self.session.sql("drop table if exists t13").execute() 628 self.session.sql("create table t13(i json, n json, a json)").execute() 629 table = self.schema.get_table("t13") 630 table.insert().values('{"id":1}', '{"name":"a"}', '{"age":21}').values( 631 '{"id":2}', '{"name":"b"}', '{"age":22}' 632 ).execute() 633 self.assertRaises( 634 mysqlx.OperationalError, 635 table.select().where("(1+5) IN [1,2,3,4,5,6]").execute, 636 ) 637 table.select().where("(2+3) IN (1,2,3,4)").limit(1).execute() 638 self.session.sql("drop table if exists t13").execute() 639 640 @tests.foreach_session() 641 def test_contains_operator_select14(self): 642 """Test IN operator: search for empty string in a field and field in 643 empty string.""" 644 self.session.sql("drop table if exists t14").execute() 645 self.session.sql("create table t14(id int, n JSON, age int)").execute() 646 table = self.schema.get_table("t14") 647 table.insert().values(1, '{"name":"a"}', 21).values( 648 2, '{"name":"b"}', 22 649 ).values(3, '{"name":"c"}', 32).execute() 650 result = table.select().where("'' IN n->'$.name'").execute() 651 result1 = table.select().where("n->'$.name' IN ['', ' ']").execute() 652 result2 = table.select().where("n->'$.name' IN ('', ' ')").execute() 653 self.assertEqual(len(result.fetch_all()), 0) 654 self.assertEqual(len(result1.fetch_all()), 0) 655 self.assertEqual(len(result2.fetch_all()), 0) 656 self.session.sql("drop table if exists t14").execute() 657 658 @tests.foreach_session() 659 def test_table_s_s_lock(self): 660 """Test shared-shared lock.""" 661 config = tests.get_mysqlx_config() 662 schema_name = config["schema"] 663 self.session.sql("drop table if exists t1").execute() 664 self.session.sql( 665 "create table t1(name varchar(20), age int)" 666 ).execute() 667 table = self.schema.get_table("t1") 668 table.insert().values("Joe", 21).values("James", 23).execute() 669 670 locking = threading.Event() 671 waiting = threading.Event() 672 673 def thread_a(locking, waiting): 674 session1 = mysqlx.get_session(config) 675 schema1 = session1.get_schema(schema_name) 676 table = schema1.get_table("t1") 677 678 session1.start_transaction() 679 table.select().where("name = 'James'").lock_shared().execute() 680 locking.set() 681 time.sleep(2) 682 locking.clear() 683 if waiting.is_set(): 684 session1.commit() 685 self.fail( 686 "Table_S_S_Lock_test IS NOT OK. Other thread is waiting " 687 "while it is not expected to!" 688 ) 689 session1.commit() 690 691 def thread_b(locking, waiting): 692 session2 = mysqlx.get_session(config) 693 schema2 = session2.get_schema(schema_name) 694 table = schema2.get_table("t1") 695 696 if not locking.wait(2): 697 self.fail( 698 "Table_S_S_Lock_test IS NOT OK. Other thread has not set " 699 "the lock!" 700 ) 701 session2.start_transaction() 702 703 waiting.set() 704 table.select().where("name = 'James'").lock_shared().execute() 705 waiting.clear() 706 707 session2.commit() 708 709 client1 = threading.Thread( 710 target=thread_a, 711 args=( 712 locking, 713 waiting, 714 ), 715 ) 716 client2 = threading.Thread( 717 target=thread_b, 718 args=( 719 locking, 720 waiting, 721 ), 722 ) 723 724 client1.start() 725 client2.start() 726 727 client1.join() 728 client2.join() 729 self.session.sql("drop table if exists t1").execute() 730 731 @tests.foreach_session() 732 def test_table_s_x_lock(self): 733 """Test shared-exclusive lock.""" 734 config = tests.get_mysqlx_config() 735 schema_name = config["schema"] 736 self.session.sql("drop table if exists t2").execute() 737 self.session.sql( 738 "create table t2(name varchar(20), age int)" 739 ).execute() 740 table = self.schema.get_table("t2") 741 table.insert().values("Joe", 21).values("James", 23).execute() 742 743 locking = threading.Event() 744 waiting = threading.Event() 745 746 def thread_a(locking, waiting): 747 session1 = mysqlx.get_session(config) 748 schema1 = session1.get_schema(schema_name) 749 table = schema1.get_table("t2") 750 751 session1.start_transaction() 752 table.select().where("name = 'James'").lock_shared().execute() 753 locking.set() 754 time.sleep(2) 755 locking.clear() 756 if not waiting.is_set(): 757 session1.commit() 758 self.fail( 759 "Table_S_X_Lock_test IS NOT OK. Other thread is not " 760 "waiting while it is expected to!" 761 ) 762 session1.commit() 763 764 def thread_b(locking, waiting): 765 session2 = mysqlx.get_session(config) 766 schema2 = session2.get_schema(schema_name) 767 table = schema2.get_table("t2") 768 769 if not locking.wait(2): 770 self.fail( 771 "Table_S_X_Lock_test IS NOT OK. Other thread has not set " 772 "the lock!" 773 ) 774 session2.start_transaction() 775 776 waiting.set() 777 table.select().where("name = 'James'").lock_exclusive().execute() 778 waiting.clear() 779 780 session2.commit() 781 782 client1 = threading.Thread( 783 target=thread_a, 784 args=( 785 locking, 786 waiting, 787 ), 788 ) 789 client2 = threading.Thread( 790 target=thread_b, 791 args=( 792 locking, 793 waiting, 794 ), 795 ) 796 797 client1.start() 798 client2.start() 799 800 client1.join() 801 client2.join() 802 self.session.sql("drop table if exists t2").execute() 803 804 @tests.foreach_session() 805 def test_table_x_x_lock(self): 806 """Test clusive-exclusive lock.""" 807 config = tests.get_mysqlx_config() 808 schema_name = config["schema"] 809 self.session.sql("drop table if exists t3").execute() 810 self.session.sql( 811 "create table t3(name varchar(20), age int)" 812 ).execute() 813 table = self.schema.get_table("t3") 814 table.insert().values("Joe", 21).values("James", 23).execute() 815 816 locking = threading.Event() 817 waiting = threading.Event() 818 819 def thread_a(locking, waiting): 820 session1 = mysqlx.get_session(config) 821 schema1 = session1.get_schema(schema_name) 822 table = schema1.get_table("t3") 823 824 session1.start_transaction() 825 table.select().where("name = 'James'").lock_exclusive().execute() 826 locking.set() 827 time.sleep(2) 828 locking.clear() 829 if not waiting.is_set(): 830 session1.commit() 831 self.fail( 832 "Table_X_X_Lock_test IS NOT OK. Other thread is not " 833 "waiting while it is expected to!" 834 ) 835 session1.commit() 836 837 def thread_b(locking, waiting): 838 session2 = mysqlx.get_session(config) 839 schema2 = session2.get_schema(schema_name) 840 table = schema2.get_table("t3") 841 842 if not locking.wait(2): 843 self.fail( 844 "Table_X_X_Lock_test IS NOT OK. Other thread has not set " 845 "the lock!" 846 ) 847 session2.start_transaction() 848 849 waiting.set() 850 table.select().where("name = 'James'").lock_exclusive().execute() 851 waiting.clear() 852 853 session2.commit() 854 855 client1 = threading.Thread( 856 target=thread_a, 857 args=( 858 locking, 859 waiting, 860 ), 861 ) 862 client2 = threading.Thread( 863 target=thread_b, 864 args=( 865 locking, 866 waiting, 867 ), 868 ) 869 870 client1.start() 871 client2.start() 872 873 client1.join() 874 client2.join() 875 self.session.sql("drop table if exists t3").execute() 876 877 @tests.foreach_session() 878 def test_table_x_s_lock(self): 879 """Test exclusive-exclusive lock.""" 880 config = tests.get_mysqlx_config() 881 schema_name = config["schema"] 882 self.session.sql("drop table if exists t4").execute() 883 self.session.sql( 884 "create table t4(name varchar(20), age int)" 885 ).execute() 886 table = self.schema.get_table("t4") 887 table.insert().values("Joe", 21).values("James", 23).execute() 888 889 locking = threading.Event() 890 waiting = threading.Event() 891 892 def thread_a(locking, waiting): 893 session1 = mysqlx.get_session(config) 894 schema1 = session1.get_schema(schema_name) 895 table = schema1.get_table("t4") 896 897 session1.start_transaction() 898 table.select().where("name = 'James'").lock_exclusive().execute() 899 locking.set() 900 time.sleep(2) 901 locking.clear() 902 if not waiting.is_set(): 903 session1.commit() 904 self.fail( 905 "Table_X_S_Lock_test IS NOT OK. Other thread is not " 906 "waiting while it is expected to!" 907 ) 908 session1.commit() 909 910 def thread_b(locking, waiting): 911 session2 = mysqlx.get_session(config) 912 schema2 = session2.get_schema(schema_name) 913 table = schema2.get_table("t4") 914 915 if not locking.wait(2): 916 self.fail( 917 "Table_X_S_Lock_test IS NOT OK. Other thread has not set " 918 "the lock!" 919 ) 920 session2.start_transaction() 921 922 waiting.set() 923 table.select().where("name = 'James'").lock_shared().execute() 924 waiting.clear() 925 926 session2.commit() 927 928 client1 = threading.Thread( 929 target=thread_a, 930 args=( 931 locking, 932 waiting, 933 ), 934 ) 935 client2 = threading.Thread( 936 target=thread_b, 937 args=( 938 locking, 939 waiting, 940 ), 941 ) 942 943 client1.start() 944 client2.start() 945 946 client1.join() 947 client2.join() 948 self.session.sql("drop table if exists t4").execute() 949 950 @tests.foreach_session() 951 def test_table_multiple_lock_calls(self): 952 """Test multiple lock calls.""" 953 config = tests.get_mysqlx_config() 954 schema_name = config["schema"] 955 self.session.sql("drop table if exists t5").execute() 956 self.session.sql( 957 "create table t5(name varchar(20), age int)" 958 ).execute() 959 table = self.schema.get_table("t5") 960 table.insert().values("Joe", 21).values("James", 23).execute() 961 962 locking = threading.Event() 963 waiting = threading.Event() 964 965 def thread_a(locking, waiting): 966 session1 = mysqlx.get_session(config) 967 schema1 = session1.get_schema(schema_name) 968 table = schema1.get_table("t5") 969 970 session1.start_transaction() 971 table.select().where( 972 "name = 'James'" 973 ).lock_exclusive().lock_shared().lock_exclusive().execute() 974 locking.set() 975 time.sleep(2) 976 locking.clear() 977 if not waiting.is_set(): 978 session1.commit() 979 self.fail( 980 "Table_Multiple_Lock_calls_test IS NOT OK. Other thread " 981 "is not waiting while it is expected to!" 982 ) 983 session1.commit() 984 985 def thread_b(locking, waiting): 986 session2 = mysqlx.get_session(config) 987 schema2 = session2.get_schema(schema_name) 988 table = schema2.get_table("t5") 989 990 if not locking.wait(2): 991 self.fail( 992 "Table_Multiple_Lock_calls_test IS NOT OK. Other thread " 993 "has not set the lock!" 994 ) 995 session2.start_transaction() 996 997 waiting.set() 998 table.select().where( 999 "name = 'James'" 1000 ).lock_shared().lock_exclusive().lock_exclusive().lock_shared().execute() 1001 waiting.clear() 1002 1003 session2.commit() 1004 1005 client1 = threading.Thread( 1006 target=thread_a, 1007 args=( 1008 locking, 1009 waiting, 1010 ), 1011 ) 1012 client2 = threading.Thread( 1013 target=thread_b, 1014 args=( 1015 locking, 1016 waiting, 1017 ), 1018 ) 1019 1020 client1.start() 1021 client2.start() 1022 1023 client1.join() 1024 client2.join() 1025 self.session.sql("drop table if exists t5").execute() 1026 1027 @tests.foreach_session() 1028 def test_table_x_lock_update(self): 1029 """Test lock exclusive and update().where - update().where will be 1030 blocked until the lock is released.""" 1031 config = tests.get_mysqlx_config() 1032 schema_name = config["schema"] 1033 self.session.sql("drop table if exists t6").execute() 1034 self.session.sql( 1035 "create table t6(name varchar(20), age int)" 1036 ).execute() 1037 table = self.schema.get_table("t6") 1038 table.insert().values("Joe", 21).values("James", 23).execute() 1039 1040 locking = threading.Event() 1041 waiting = threading.Event() 1042 1043 def thread_a(locking, waiting): 1044 session1 = mysqlx.get_session(config) 1045 schema1 = session1.get_schema(schema_name) 1046 table = schema1.get_table("t6") 1047 1048 session1.start_transaction() 1049 table.select().where( 1050 "name = 'James'" 1051 ).lock_exclusive().lock_shared().lock_exclusive().execute() 1052 locking.set() 1053 time.sleep(2) 1054 locking.clear() 1055 if not waiting.is_set(): 1056 session1.commit() 1057 self.fail( 1058 "Table_X_Lock_Update_test IS NOT OK. Other thread is not " 1059 "waiting while it is expected to!" 1060 ) 1061 session1.commit() 1062 1063 def thread_b(locking, waiting): 1064 session2 = mysqlx.get_session(config) 1065 schema2 = session2.get_schema(schema_name) 1066 table = schema2.get_table("t6") 1067 1068 if not locking.wait(2): 1069 self.fail( 1070 "Table_X_Lock_Update_test IS NOT OK. Other thread has not " 1071 "set the lock!" 1072 ) 1073 session2.start_transaction() 1074 1075 waiting.set() 1076 table.update().where("name == 'James'").set("age", 30).execute() 1077 waiting.clear() 1078 1079 session2.commit() 1080 1081 client1 = threading.Thread( 1082 target=thread_a, 1083 args=( 1084 locking, 1085 waiting, 1086 ), 1087 ) 1088 client2 = threading.Thread( 1089 target=thread_b, 1090 args=( 1091 locking, 1092 waiting, 1093 ), 1094 ) 1095 1096 client1.start() 1097 client2.start() 1098 1099 client1.join() 1100 client2.join() 1101 self.session.sql("drop table if exists t6").execute() 1102 1103 @tests.foreach_session() 1104 def test_table_s_lock_update(self): 1105 """Test lock shared and update().where - update().where will be blocked 1106 until the lock is released, but will be able to read.""" 1107 config = tests.get_mysqlx_config() 1108 schema_name = config["schema"] 1109 self.session.sql("drop table if exists t7").execute() 1110 self.session.sql( 1111 "create table t7(name varchar(20), age int)" 1112 ).execute() 1113 table = self.schema.get_table("t7") 1114 table.insert().values("Joe", 21).values("James", 23).execute() 1115 1116 locking = threading.Event() 1117 waiting = threading.Event() 1118 1119 def thread_a(locking, waiting): 1120 session1 = mysqlx.get_session(config) 1121 schema1 = session1.get_schema(schema_name) 1122 table = schema1.get_table("t7") 1123 1124 session1.start_transaction() 1125 table.select().where( 1126 "name = 'James'" 1127 ).lock_exclusive().lock_shared().execute() 1128 locking.set() 1129 time.sleep(2) 1130 locking.clear() 1131 if not waiting.is_set(): 1132 session1.commit() 1133 self.fail( 1134 "Table_S_Lock_Update_test IS NOT OK. Other thread is not " 1135 "waiting while it is expected to!" 1136 ) 1137 session1.commit() 1138 1139 def thread_b(locking, waiting): 1140 session2 = mysqlx.get_session(config) 1141 schema2 = session2.get_schema(schema_name) 1142 table = schema2.get_table("t7") 1143 1144 if not locking.wait(2): 1145 self.fail( 1146 "Table_S_Lock_Update_test IS NOT OK. Other thread has not " 1147 "set the lock!" 1148 ) 1149 session2.start_transaction() 1150 result = table.select().where("name == 'James'").execute() 1151 self.assertEqual(result.fetch_all()[0]["age"], 23) 1152 waiting.set() 1153 table.update().where("name == 'James'").set("age", 30).execute() 1154 waiting.clear() 1155 1156 session2.commit() 1157 1158 client1 = threading.Thread( 1159 target=thread_a, 1160 args=( 1161 locking, 1162 waiting, 1163 ), 1164 ) 1165 client2 = threading.Thread( 1166 target=thread_b, 1167 args=( 1168 locking, 1169 waiting, 1170 ), 1171 ) 1172 1173 client1.start() 1174 client2.start() 1175 1176 client1.join() 1177 client2.join() 1178 self.session.sql("drop table if exists t7").execute() 1179 1180 @tests.foreach_session() 1181 def test_table_s_s_nowait(self): 1182 """Test shared-shared with NOWAIT lockcontention.""" 1183 config = tests.get_mysqlx_config() 1184 schema_name = config["schema"] 1185 self.session.sql("drop table if exists t").execute() 1186 self.session.sql("create table t(name varchar(20), age int)").execute() 1187 table = self.schema.get_table("t") 1188 table.insert().values("Joe", 21).values("James", 23).execute() 1189 1190 # `session2.lock_shared(NOWAIT) returns data immediately. 1191 session1 = mysqlx.get_session(config) 1192 schema1 = session1.get_schema(schema_name) 1193 table1 = schema1.get_table("t") 1194 session1.start_transaction() 1195 table1.select().where("name = 'James'").lock_shared().execute() 1196 1197 session2 = mysqlx.get_session(config) 1198 schema2 = session2.get_schema(schema_name) 1199 table2 = schema2.get_table("t") 1200 session2.start_transaction() 1201 result = ( 1202 table2.select() 1203 .where("name = 'James'") 1204 .lock_shared(mysqlx.LockContention.NOWAIT) 1205 .execute() 1206 ) 1207 res = result.fetch_all() 1208 self.assertEqual(len(res), 1) 1209 self.assertEqual(res[0]["age"], 23) 1210 session2.rollback() 1211 1212 session1.rollback() 1213 1214 self.session.sql("drop table if exists t").execute() 1215 session2.close() 1216 session1.close() 1217 1218 @tests.foreach_session() 1219 def test_table_s_x_nowait(self): 1220 """Test shared-exclusive with NOWAIT lockcontention.""" 1221 config = tests.get_mysqlx_config() 1222 schema_name = config["schema"] 1223 self.session.sql("drop table if exists t").execute() 1224 self.session.sql("create table t(name varchar(20), age int)").execute() 1225 table = self.schema.get_table("t") 1226 table.insert().values("Joe", 21).values("James", 23).execute() 1227 1228 # `session2.lock_exclusive(NOWAIT) returns data immediately. 1229 session1 = mysqlx.get_session(config) 1230 schema1 = session1.get_schema(schema_name) 1231 table1 = schema1.get_table("t") 1232 session1.start_transaction() 1233 table1.select().where("name = 'James'").lock_shared().execute() 1234 1235 session2 = mysqlx.get_session(config) 1236 schema2 = session2.get_schema(schema_name) 1237 table2 = schema2.get_table("t") 1238 session2.start_transaction() 1239 try: 1240 table2.select().where("name = 'James'").lock_exclusive( 1241 mysqlx.LockContention.NOWAIT 1242 ).execute() 1243 except mysqlx.OperationalError: 1244 # Expected OperationalError 1245 pass 1246 session2.rollback() 1247 session1.rollback() 1248 self.session.sql("drop table if exists t").execute() 1249 session2.close() 1250 session1.close() 1251 1252 @tests.foreach_session() 1253 def test_table_x_x_nowait(self): 1254 """Test exclusive-exclusive with NOWAIT lockcontention.""" 1255 config = tests.get_mysqlx_config() 1256 schema_name = config["schema"] 1257 self.session.sql("drop table if exists t").execute() 1258 self.session.sql("create table t(name varchar(20), age int)").execute() 1259 table = self.schema.get_table("t") 1260 table.insert().values("Joe", 21).values("James", 23).execute() 1261 1262 # `session2.lock_exclusive(NOWAIT) returns data immediately. 1263 session1 = mysqlx.get_session(config) 1264 schema1 = session1.get_schema(schema_name) 1265 table1 = schema1.get_table("t") 1266 session1.start_transaction() 1267 table1.select().where("name = 'James'").lock_exclusive().execute() 1268 1269 session2 = mysqlx.get_session(config) 1270 schema2 = session2.get_schema(schema_name) 1271 table2 = schema2.get_table("t") 1272 session2.start_transaction() 1273 try: 1274 table2.select().where("name = 'James'").lock_exclusive( 1275 mysqlx.LockContention.NOWAIT 1276 ).execute() 1277 except mysqlx.OperationalError: 1278 # Expected OperationalError 1279 pass 1280 session2.rollback() 1281 session1.rollback() 1282 self.session.sql("drop table if exists t").execute() 1283 session2.close() 1284 session1.close() 1285 1286 @tests.foreach_session() 1287 def test_table_x_s_nowait(self): 1288 """Test exclusive-shared with NOWAIT lockcontention.""" 1289 config = tests.get_mysqlx_config() 1290 schema_name = config["schema"] 1291 self.session.sql("drop table if exists t").execute() 1292 self.session.sql("create table t(name varchar(20), age int)").execute() 1293 table = self.schema.get_table("t") 1294 table.insert().values("Joe", 21).values("James", 23).execute() 1295 1296 # `session2.lock_exclusive(NOWAIT) returns data immediately. 1297 session1 = mysqlx.get_session(config) 1298 schema1 = session1.get_schema(schema_name) 1299 table1 = schema1.get_table("t") 1300 session1.start_transaction() 1301 table1.select().where("name = 'James'").lock_exclusive().execute() 1302 1303 session2 = mysqlx.get_session(config) 1304 schema2 = session2.get_schema(schema_name) 1305 table2 = schema2.get_table("t") 1306 session2.start_transaction() 1307 try: 1308 table2.select().where("name = 'James'").lock_shared( 1309 mysqlx.LockContention.NOWAIT 1310 ).execute() 1311 except mysqlx.OperationalError: 1312 # Expected OperationalError 1313 pass 1314 session2.rollback() 1315 session1.rollback() 1316 self.session.sql("drop table if exists t").execute() 1317 session2.close() 1318 session1.close() 1319 1320 @tests.foreach_session() 1321 def test_table_s_s_skip_locked(self): 1322 """Test shared-shared with SKIP LOCKED lockcontention.""" 1323 config = tests.get_mysqlx_config() 1324 schema_name = config["schema"] 1325 self.session.sql("drop table if exists t").execute() 1326 self.session.sql("create table t(name varchar(20), age int)").execute() 1327 table = self.schema.get_table("t") 1328 table.insert().values("Joe", 21).values("James", 23).execute() 1329 1330 # `session2.lock_exclusive(SKIP_LOCKED) returns data immediately. 1331 session1 = mysqlx.get_session(config) 1332 schema1 = session1.get_schema(schema_name) 1333 table1 = schema1.get_table("t") 1334 session1.start_transaction() 1335 table1.select().where("name = 'James'").lock_shared().execute() 1336 1337 session2 = mysqlx.get_session(config) 1338 schema2 = session2.get_schema(schema_name) 1339 table2 = schema2.get_table("t") 1340 session2.start_transaction() 1341 result = ( 1342 table2.select() 1343 .where("name = 'James'") 1344 .lock_shared(mysqlx.LockContention.SKIP_LOCKED) 1345 .execute() 1346 ) 1347 res = result.fetch_all() 1348 self.assertEqual(len(res), 1) 1349 self.assertEqual(res[0]["age"], 23) 1350 session2.rollback() 1351 1352 session1.rollback() 1353 self.session.sql("drop table if exists t").execute() 1354 session2.close() 1355 session1.close() 1356 1357 @tests.foreach_session() 1358 def test_table_s_x_skip_locked(self): 1359 """Test shared-exclusive with SKIP LOCKED lockcontention.""" 1360 config = tests.get_mysqlx_config() 1361 schema_name = config["schema"] 1362 self.session.sql("drop table if exists t").execute() 1363 self.session.sql("create table t(name varchar(20), age int)").execute() 1364 table = self.schema.get_table("t") 1365 table.insert().values("Joe", 21).values("James", 23).execute() 1366 1367 # `session2.lock_exclusive(SKIP_LOCKED) returns data immediately. 1368 session1 = mysqlx.get_session(config) 1369 schema1 = session1.get_schema(schema_name) 1370 table1 = schema1.get_table("t") 1371 session1.start_transaction() 1372 table1.select().where("name = 'James'").lock_shared().execute() 1373 1374 session2 = mysqlx.get_session(config) 1375 schema2 = session2.get_schema(schema_name) 1376 table2 = schema2.get_table("t") 1377 session2.start_transaction() 1378 result = ( 1379 table2.select() 1380 .where("name = 'James'") 1381 .lock_exclusive(mysqlx.LockContention.SKIP_LOCKED) 1382 .execute() 1383 ) 1384 res = result.fetch_all() 1385 self.assertEqual(len(res), 0) 1386 session2.rollback() 1387 session1.rollback() 1388 self.session.sql("drop table if exists t").execute() 1389 session2.close() 1390 session1.close() 1391 1392 @tests.foreach_session() 1393 def test_table_x_x_skip_locked(self): 1394 """Test exclusive-exclusive with SKIP LOCKED lockcontention.""" 1395 config = tests.get_mysqlx_config() 1396 schema_name = config["schema"] 1397 self.session.sql("drop table if exists t").execute() 1398 self.session.sql("create table t(name varchar(20), age int)").execute() 1399 table = self.schema.get_table("t") 1400 table.insert().values("Joe", 21).values("James", 23).execute() 1401 1402 # `session2.lock_exclusive(SKIP_LOCKED) returns data immediately. 1403 session1 = mysqlx.get_session(config) 1404 schema1 = session1.get_schema(schema_name) 1405 table1 = schema1.get_table("t") 1406 session1.start_transaction() 1407 table1.select().where("name = 'James'").lock_exclusive().execute() 1408 1409 session2 = mysqlx.get_session(config) 1410 schema2 = session2.get_schema(schema_name) 1411 table2 = schema2.get_table("t") 1412 session2.start_transaction() 1413 result = ( 1414 table2.select() 1415 .where("name = 'James'") 1416 .lock_exclusive(mysqlx.LockContention.SKIP_LOCKED) 1417 .execute() 1418 ) 1419 res = result.fetch_all() 1420 self.assertEqual(len(res), 0) 1421 session2.rollback() 1422 session1.rollback() 1423 self.session.sql("drop table if exists t").execute() 1424 session2.close() 1425 session1.close() 1426 1427 @tests.foreach_session() 1428 def test_table_x_s_skip_locked(self): 1429 """Test exclusive-shared with SKIP LOCKED lockcontention.""" 1430 config = tests.get_mysqlx_config() 1431 schema_name = config["schema"] 1432 self.session.sql("drop table if exists t").execute() 1433 self.session.sql("create table t(name varchar(20), age int)").execute() 1434 table = self.schema.get_table("t") 1435 table.insert().values("Joe", 21).values("James", 23).execute() 1436 1437 # `session2.lock_exclusive(SKIP_LOCKED) returns data immediately. 1438 session1 = mysqlx.get_session(config) 1439 schema1 = session1.get_schema(schema_name) 1440 table1 = schema1.get_table("t") 1441 session1.start_transaction() 1442 table1.select().where("name = 'James'").lock_exclusive().execute() 1443 1444 session2 = mysqlx.get_session(config) 1445 schema2 = session2.get_schema(schema_name) 1446 table2 = schema2.get_table("t") 1447 session2.start_transaction() 1448 result = ( 1449 table2.select() 1450 .where("name = 'James'") 1451 .lock_shared(mysqlx.LockContention.SKIP_LOCKED) 1452 .execute() 1453 ) 1454 res = result.fetch_all() 1455 self.assertEqual(len(res), 0) 1456 session2.rollback() 1457 session1.rollback() 1458 self.session.sql("drop table if exists t").execute() 1459 session2.close() 1460 session1.close() 1461 1462 @tests.foreach_session() 1463 def test_table_s_s_default(self): 1464 """Test shared-shared lock.""" 1465 config = tests.get_mysqlx_config() 1466 schema_name = config["schema"] 1467 self.session.sql("drop table if exists t1").execute() 1468 self.session.sql( 1469 "create table t1(name varchar(20), age int)" 1470 ).execute() 1471 table = self.schema.get_table("t1") 1472 table.insert().values("Joe", 21).values("James", 23).execute() 1473 1474 locking = threading.Event() 1475 waiting = threading.Event() 1476 1477 def thread_a(locking, waiting): 1478 session1 = mysqlx.get_session(config) 1479 schema1 = session1.get_schema(schema_name) 1480 table = schema1.get_table("t1") 1481 1482 session1.start_transaction() 1483 table.select().where("name = 'James'").lock_shared().execute() 1484 locking.set() 1485 time.sleep(2) 1486 locking.clear() 1487 if waiting.is_set(): 1488 session1.commit() 1489 self.fail( 1490 "Table_S_S_DEFAULT_test IS NOT OK. Other thread is " 1491 "waiting while it is not expected to!" 1492 ) 1493 session1.commit() 1494 1495 def thread_b(locking, waiting): 1496 session2 = mysqlx.get_session(config) 1497 schema2 = session2.get_schema(schema_name) 1498 table = schema2.get_table("t1") 1499 1500 if not locking.wait(2): 1501 self.fail( 1502 "Table_S_S_DEFAULT_test IS NOT OK. Other thread has not " 1503 "set the lock!" 1504 ) 1505 session2.start_transaction() 1506 1507 waiting.set() 1508 table.select().where("name = 'James'").lock_shared( 1509 mysqlx.LockContention.DEFAULT 1510 ).execute() 1511 waiting.clear() 1512 1513 session2.commit() 1514 1515 client1 = threading.Thread( 1516 target=thread_a, 1517 args=( 1518 locking, 1519 waiting, 1520 ), 1521 ) 1522 client2 = threading.Thread( 1523 target=thread_b, 1524 args=( 1525 locking, 1526 waiting, 1527 ), 1528 ) 1529 1530 client1.start() 1531 client2.start() 1532 1533 client1.join() 1534 client2.join() 1535 self.session.sql("drop table if exists t1").execute() 1536 1537 @tests.foreach_session() 1538 def test_table_s_x_default(self): 1539 """Test shared-exclusive lock.""" 1540 config = tests.get_mysqlx_config() 1541 schema_name = config["schema"] 1542 self.session.sql("drop table if exists t2").execute() 1543 self.session.sql( 1544 "create table t2(name varchar(20), age int)" 1545 ).execute() 1546 table = self.schema.get_table("t2") 1547 table.insert().values("Joe", 21).values("James", 23).execute() 1548 1549 locking = threading.Event() 1550 waiting = threading.Event() 1551 1552 def thread_a(locking, waiting): 1553 session1 = mysqlx.get_session(config) 1554 schema1 = session1.get_schema(schema_name) 1555 table = schema1.get_table("t2") 1556 1557 session1.start_transaction() 1558 table.select().where("name = 'James'").lock_shared().execute() 1559 locking.set() 1560 time.sleep(2) 1561 locking.clear() 1562 if not waiting.is_set(): 1563 session1.commit() 1564 self.fail( 1565 "Table_S_X_DEFAULT_test IS NOT OK. Other thread is not " 1566 "waiting while it is expected to!" 1567 ) 1568 session1.commit() 1569 1570 def thread_b(locking, waiting): 1571 session2 = mysqlx.get_session(config) 1572 schema2 = session2.get_schema(schema_name) 1573 table = schema2.get_table("t2") 1574 1575 if not locking.wait(2): 1576 self.fail( 1577 "Table_S_X_DEFAULT_test IS NOT OK. Other thread has not " 1578 "set the lock!" 1579 ) 1580 session2.start_transaction() 1581 1582 waiting.set() 1583 table.select().where("name = 'James'").lock_exclusive( 1584 mysqlx.LockContention.DEFAULT 1585 ).execute() 1586 waiting.clear() 1587 1588 session2.commit() 1589 1590 client1 = threading.Thread( 1591 target=thread_a, 1592 args=( 1593 locking, 1594 waiting, 1595 ), 1596 ) 1597 client2 = threading.Thread( 1598 target=thread_b, 1599 args=( 1600 locking, 1601 waiting, 1602 ), 1603 ) 1604 1605 client1.start() 1606 client2.start() 1607 1608 client1.join() 1609 client2.join() 1610 self.session.sql("drop table if exists t2").execute() 1611 1612 @tests.foreach_session() 1613 def test_table_x_x_default(self): 1614 """Test exclusive-exclusive lock.""" 1615 config = tests.get_mysqlx_config() 1616 schema_name = config["schema"] 1617 self.session.sql("drop table if exists t3").execute() 1618 self.session.sql( 1619 "create table t3(name varchar(20), age int)" 1620 ).execute() 1621 table = self.schema.get_table("t3") 1622 table.insert().values("Joe", 21).values("James", 23).execute() 1623 1624 locking = threading.Event() 1625 waiting = threading.Event() 1626 1627 def thread_a(locking, waiting): 1628 session1 = mysqlx.get_session(config) 1629 schema1 = session1.get_schema(schema_name) 1630 table = schema1.get_table("t3") 1631 1632 session1.start_transaction() 1633 table.select().where("name = 'James'").lock_exclusive().execute() 1634 locking.set() 1635 time.sleep(2) 1636 locking.clear() 1637 if not waiting.is_set(): 1638 session1.commit() 1639 self.fail( 1640 "Table_X_X_DEFAULT_test IS NOT OK. Other thread is not " 1641 "waiting while it is expected to!" 1642 ) 1643 session1.commit() 1644 1645 def thread_b(locking, waiting): 1646 session2 = mysqlx.get_session(config) 1647 schema2 = session2.get_schema(schema_name) 1648 table = schema2.get_table("t3") 1649 1650 if not locking.wait(2): 1651 self.fail( 1652 "Table_X_X_DEFAULT_test IS NOT OK. Other thread has not " 1653 "set the lock!" 1654 ) 1655 session2.start_transaction() 1656 1657 waiting.set() 1658 table.select().where("name = 'James'").lock_exclusive( 1659 mysqlx.LockContention.DEFAULT 1660 ).execute() 1661 waiting.clear() 1662 1663 session2.commit() 1664 1665 client1 = threading.Thread( 1666 target=thread_a, 1667 args=( 1668 locking, 1669 waiting, 1670 ), 1671 ) 1672 client2 = threading.Thread( 1673 target=thread_b, 1674 args=( 1675 locking, 1676 waiting, 1677 ), 1678 ) 1679 1680 client1.start() 1681 client2.start() 1682 1683 client1.join() 1684 client2.join() 1685 self.session.sql("drop table if exists t3").execute() 1686 1687 @tests.foreach_session() 1688 def test_table_x_s_default(self): 1689 """Test exclusive-exclusive lock.""" 1690 config = tests.get_mysqlx_config() 1691 schema_name = config["schema"] 1692 self.session.sql("drop table if exists t4").execute() 1693 self.session.sql( 1694 "create table t4(name varchar(20), age int)" 1695 ).execute() 1696 table = self.schema.get_table("t4") 1697 table.insert().values("Joe", 21).values("James", 23).execute() 1698 1699 locking = threading.Event() 1700 waiting = threading.Event() 1701 1702 def thread_a(locking, waiting): 1703 session1 = mysqlx.get_session(config) 1704 schema1 = session1.get_schema(schema_name) 1705 table = schema1.get_table("t4") 1706 1707 session1.start_transaction() 1708 table.select().where("name = 'James'").lock_exclusive().execute() 1709 locking.set() 1710 time.sleep(2) 1711 locking.clear() 1712 if not waiting.is_set(): 1713 session1.commit() 1714 self.fail( 1715 "Table_X_S_DEFAULT_test IS NOT OK. Other thread is not " 1716 "waiting while it is expected to!" 1717 ) 1718 session1.commit() 1719 1720 def thread_b(locking, waiting): 1721 session2 = mysqlx.get_session(config) 1722 schema2 = session2.get_schema(schema_name) 1723 table = schema2.get_table("t4") 1724 1725 if not locking.wait(2): 1726 self.fail( 1727 "Table_X_S_DEFAULT_test IS NOT OK. Other thread has not " 1728 "set the lock!" 1729 ) 1730 session2.start_transaction() 1731 1732 waiting.set() 1733 table.select().where("name = 'James'").lock_shared( 1734 mysqlx.LockContention.DEFAULT 1735 ).execute() 1736 waiting.clear() 1737 1738 session2.commit() 1739 1740 client1 = threading.Thread( 1741 target=thread_a, 1742 args=( 1743 locking, 1744 waiting, 1745 ), 1746 ) 1747 client2 = threading.Thread( 1748 target=thread_b, 1749 args=( 1750 locking, 1751 waiting, 1752 ), 1753 ) 1754 1755 client1.start() 1756 client2.start() 1757 1758 client1.join() 1759 client2.join() 1760 self.session.sql("drop table if exists t4").execute() 1761 1762 @tests.foreach_session() 1763 def test_table_multiple_lock_contention_calls(self): 1764 """Test multiple lock calls.""" 1765 config = tests.get_mysqlx_config() 1766 schema_name = config["schema"] 1767 self.session.sql("drop table if exists t5").execute() 1768 self.session.sql( 1769 "create table t5(name varchar(20), age int)" 1770 ).execute() 1771 table = self.schema.get_table("t5") 1772 table.insert().values("Joe", 21).values("James", 23).execute() 1773 1774 locking = threading.Event() 1775 waiting = threading.Event() 1776 1777 def thread_a(locking, waiting): 1778 session1 = mysqlx.get_session(config) 1779 schema1 = session1.get_schema(schema_name) 1780 table = schema1.get_table("t5") 1781 1782 session1.start_transaction() 1783 table.select().where( 1784 "name = 'James'" 1785 ).lock_exclusive().lock_shared().lock_exclusive().execute() 1786 locking.set() 1787 time.sleep(2) 1788 locking.clear() 1789 if waiting.is_set(): 1790 session1.commit() 1791 self.fail( 1792 "Table_Multiple_Lock_calls_test IS NOT OK. Other thread " 1793 "is waiting while it is not expected to!" 1794 ) 1795 session1.commit() 1796 1797 def thread_b(locking, waiting): 1798 session2 = mysqlx.get_session(config) 1799 schema2 = session2.get_schema(schema_name) 1800 table = schema2.get_table("t5") 1801 1802 if not locking.wait(2): 1803 self.fail( 1804 "Table_Multiple_Lock_calls_test IS NOT OK. Other thread " 1805 "has not set the lock!" 1806 ) 1807 session2.start_transaction() 1808 1809 waiting.set() 1810 result = ( 1811 table.select() 1812 .where("name = 'James'") 1813 .lock_shared(mysqlx.LockContention.DEFAULT) 1814 .lock_exclusive(mysqlx.LockContention.SKIP_LOCKED) 1815 .lock_exclusive(mysqlx.LockContention.NOWAIT) 1816 .lock_shared(mysqlx.LockContention.SKIP_LOCKED) 1817 .execute() 1818 ) 1819 res = result.fetch_all() 1820 assert len(res) == 0 1821 waiting.clear() 1822 1823 session2.commit() 1824 1825 client1 = threading.Thread( 1826 target=thread_a, 1827 args=( 1828 locking, 1829 waiting, 1830 ), 1831 ) 1832 client2 = threading.Thread( 1833 target=thread_b, 1834 args=( 1835 locking, 1836 waiting, 1837 ), 1838 ) 1839 1840 client1.start() 1841 client2.start() 1842 1843 client1.join() 1844 client2.join() 1845 1846 self.session.sql("drop table if exists t5").execute() 1847 1848 @tests.foreach_session() 1849 def test_table_update_x_nowait(self): 1850 """Test lock exclusive and update().where - update().where will be 1851 blocked until the lock is released.""" 1852 config = tests.get_mysqlx_config() 1853 schema_name = config["schema"] 1854 self.session.sql("drop table if exists t6").execute() 1855 self.session.sql( 1856 "create table t6(name varchar(20), age int)" 1857 ).execute() 1858 table = self.schema.get_table("t6") 1859 table.insert().values("Joe", 21).values("James", 23).execute() 1860 1861 session1 = mysqlx.get_session(config) 1862 schema1 = session1.get_schema(schema_name) 1863 table = schema1.get_table("t6") 1864 session1.start_transaction() 1865 table.update().where("name == 'James'").set("age", 30).execute() 1866 1867 session2 = mysqlx.get_session(config) 1868 schema2 = session2.get_schema(schema_name) 1869 table = schema2.get_table("t6") 1870 session2.start_transaction() 1871 try: 1872 table.select().where("name = 'James'").lock_exclusive( 1873 mysqlx.LockContention.DEFAULT 1874 ).lock_shared(mysqlx.LockContention.SKIP_LOCKED).lock_exclusive( 1875 mysqlx.LockContention.NOWAIT 1876 ).execute() 1877 except mysqlx.OperationalError: 1878 # Expected OperationalError 1879 pass 1880 session2.rollback() 1881 session1.rollback() 1882 self.session.sql("drop table if exists t6").execute() 1883 session2.close() 1884 session1.close() 1885 1886 @tests.foreach_session() 1887 def test_table_update_s_skip_locked(self): 1888 """Test lock shared and update().where - update().where will be 1889 blocked until the lock is released, but will be able to read.""" 1890 config = tests.get_mysqlx_config() 1891 schema_name = config["schema"] 1892 self.session.sql("drop table if exists t7").execute() 1893 self.session.sql( 1894 "create table t7(name varchar(20), age int)" 1895 ).execute() 1896 table = self.schema.get_table("t7") 1897 table.insert().values("Joe", 21).values("James", 23).execute() 1898 1899 locking = threading.Event() 1900 waiting = threading.Event() 1901 1902 def thread_a(locking, waiting): 1903 session1 = mysqlx.get_session(config) 1904 schema1 = session1.get_schema(schema_name) 1905 table = schema1.get_table("t7") 1906 1907 session1.start_transaction() 1908 result = table.select().where("name == 'James'").execute() 1909 assert result.fetch_all()[0]["age"] == 23 1910 table.update().where("name == 'James'").set("age", 30).execute() 1911 locking.set() 1912 time.sleep(2) 1913 locking.clear() 1914 if waiting.is_set(): 1915 session1.commit() 1916 self.fail( 1917 "Table_Update_S_SKIP_LOCKED_test IS NOT OK. Other thread " 1918 "is not waiting while it is expected to!" 1919 ) 1920 session1.commit() 1921 1922 def thread_b(locking, waiting): 1923 session2 = mysqlx.get_session(config) 1924 schema2 = session2.get_schema(schema_name) 1925 table = schema2.get_table("t7") 1926 1927 if not locking.wait(2): 1928 self.fail( 1929 "Table_Update_S_SKIP_LOCKED_test IS NOT OK. Other thread " 1930 "has not set the lock!" 1931 ) 1932 session2.start_transaction() 1933 waiting.set() 1934 result = ( 1935 table.select() 1936 .where("name = 'James'") 1937 .lock_exclusive(mysqlx.LockContention.NOWAIT) 1938 .lock_shared(mysqlx.LockContention.SKIP_LOCKED) 1939 .execute() 1940 ) 1941 waiting.clear() 1942 1943 session2.commit() 1944 1945 client1 = threading.Thread( 1946 target=thread_a, 1947 args=( 1948 locking, 1949 waiting, 1950 ), 1951 ) 1952 client2 = threading.Thread( 1953 target=thread_b, 1954 args=( 1955 locking, 1956 waiting, 1957 ), 1958 ) 1959 1960 client1.start() 1961 client2.start() 1962 1963 client1.join() 1964 client2.join() 1965 self.session.sql("drop table if exists t7").execute() 1966 1967 @tests.foreach_session() 1968 def test_overlaps_table_select1(self): 1969 """OVERLAPS operator with string on both sides - With LHS in RHS.""" 1970 self.session.sql("drop table if exists t1").execute() 1971 self.session.sql("create table t1(a int , b int, c JSON)").execute() 1972 table = self.schema.get_table("t1") 1973 table.insert().values(1, 1, '{"comp":"abc"}').values( 1974 2, 1, '{"comp":"pqr"}' 1975 ).values(3, 2, '{"comp":"xyz"}').execute() 1976 result = ( 1977 table.select("a").where("'pqr' OVERLAPS c->'$.comp'").execute() 1978 ) 1979 row = result.fetch_all() 1980 self.assertEqual(len(row), 1) 1981 self.assertEqual(row[0]["a"], 2) 1982 self.session.sql("drop table if exists t1").execute() 1983 1984 @tests.foreach_session() 1985 def test_overlaps_table_select2(self): 1986 """OVERLAPS operator with int as operand - With LHS in RHS.""" 1987 self.session.sql("drop table if exists t2").execute() 1988 self.session.sql( 1989 "create table t2(name varchar(20) , a JSON, c varchar(20))" 1990 ).execute() 1991 table = self.schema.get_table("t2") 1992 table.insert().values("a", '{"age":21}', "abc").values( 1993 "b", '{"age":31}', "pqr" 1994 ).values("hell", '{"age":22}', "xyz").execute() 1995 result = table.select().where("a->$.age OVERLAPS [21,31]").execute() 1996 self.assertEqual(len(result.fetch_all()), 2) 1997 self.session.sql("drop table if exists t2").execute() 1998 1999 @tests.foreach_session() 2000 def test_overlaps_table_select3(self): 2001 """OVERLAPS operator with boolean as operand - With LHS in RHS.""" 2002 self.session.sql("create table t3(n JSON, a json, c JSON)").execute() 2003 table = self.schema.get_table("t3") 2004 table.insert().values( 2005 '{"name":"joy"}', '{"age":18}', '{"comp":"abc"}' 2006 ).values('{"name":"happy"}', '{"age":21}', '{"comp":"pqr"}').values( 2007 '{"name":"sad"}', '{"age":32}', '{"comp":"xyz"}' 2008 ).execute() 2009 result = table.select().where("21 OVERLAPS a->'$.age'").execute() 2010 self.assertEqual(len(result.fetch_all()), 1) 2011 self.session.sql("drop table if exists t3").execute() 2012 2013 @tests.foreach_session() 2014 def test_overlaps_table_select4(self): 2015 """NOT OVERLAPS operator with string operand - With LHS not in RHS.""" 2016 self.session.sql("create table t4(n JSON, age int, c JSON)").execute() 2017 table = self.schema.get_table("t4") 2018 table.insert().values('{"name":"joy"}', 18, '{"comp":"abc"}').values( 2019 '{"name":"happy"}', 21, '{"comp":"pqr"}' 2020 ).values('{"name":"sad"}', 32, '{"comp":"xyz"}').execute() 2021 result = ( 2022 table.select().where("'happy' NOT OVERLAPS n->'$.name'").execute() 2023 ) 2024 self.assertEqual(len(result.fetch_all()), 2) 2025 self.session.sql("drop table if exists t4").execute() 2026 2027 @tests.foreach_session() 2028 def test_overlaps_table_select5(self): 2029 """NOT OVERLAPS operator with int as operand - With LHS not in RHS.""" 2030 self.session.sql("create table t5(n JSON, a JSON, c JSON)").execute() 2031 table = self.schema.get_table("t5") 2032 table.insert().values( 2033 '{"name":"joy"}', '{"age":18}', '{"comp":"abc"}' 2034 ).values('{"name":"happy"}', '{"age":21}', '{"comp":"pqr"}').values( 2035 '{"name":"sad"}', '{"age":32}', '{"comp":"xyz"}' 2036 ).execute() 2037 result = ( 2038 table.select().where("[21,32] NOT OVERLAPS a->'$.age'").execute() 2039 ) 2040 self.assertEqual(len(result.fetch_all()), 1) 2041 self.session.sql("drop table if exists t5").execute() 2042 2043 @tests.foreach_session() 2044 def test_overlaps_table_select6(self): 2045 """NOT OVERLAPS operator with boolean as operand - With LHS not 2046 in RHS.""" 2047 self.session.sql( 2048 "create table t6(name varchar(20) , age int, c json)" 2049 ).execute() 2050 table = self.schema.get_table("t6") 2051 table.insert().values("a", 21, '{"comp":"pqr"}').values( 2052 "b", 31, '{"comp":"xyz"}' 2053 ).values("e", 22, '{"comp":"xyz"}').execute() 2054 result = ( 2055 table.select() 2056 .where("c->'$.comp' OVERLAPS ['pqr','abc']") 2057 .execute() 2058 ) 2059 self.assertEqual(len(result.fetch_all()), 1) 2060 self.session.sql("drop table if exists t6").execute() 2061 2062 @tests.foreach_session() 2063 def test_overlaps_table_select7(self): 2064 """OVERLAPS operator with different datatypes as operands.""" 2065 self.session.sql("create table t7(n JSON, a json, c JSON)").execute() 2066 table = self.schema.get_table("t7") 2067 table.insert().values( 2068 '{"name":"joy"}', '{"age":18}', '{"comp":"abc"}' 2069 ).values('{"name":"happy"}', '{"age":21}', '{"comp":"pqr"}').values( 2070 '{"name":"sad"}', '{"age":32}', '{"comp":"xyz"}' 2071 ).execute() 2072 result = table.select().where("21 OVERLAPS n->'$.name'").execute() 2073 result1 = ( 2074 table.select().where("'b' OVERLAPS a->$.age").limit(1).execute() 2075 ) 2076 self.assertEqual(len(result.fetch_all()), 0) 2077 self.assertEqual(len(result1.fetch_all()), 0) 2078 self.session.sql("drop table if exists t7").execute() 2079 2080 @tests.foreach_session() 2081 def test_overlaps_table_select8(self): 2082 """OVERLAPS operator with single element on LHS and array/list on RHS 2083 and vice versa.""" 2084 self.session.sql( 2085 "create table t8(id int, n JSON, a JSON, p JSON)" 2086 ).execute() 2087 table = self.schema.get_table("t8") 2088 table.insert().values( 2089 1, '{"name":"a"}', '{"age":21}', '{"prof":["x","y"]}' 2090 ).values(2, '{"name":"b"}', '{"age":24}', '{"prof":["p","q"]}').values( 2091 3, '{"name":"c"}', '{"age":26}', '{"prof":["l","m"]}' 2092 ).execute() 2093 result = ( 2094 table.select().where("a->$.age OVERLAPS [21,23,24,28]").execute() 2095 ) 2096 result1 = ( 2097 table.select() 2098 .where("n->'$.name' OVERLAPS ['a','b','c','d','e']") 2099 .execute() 2100 ) 2101 result2 = table.select().where("a->$.age OVERLAPS [(10+11)]").execute() 2102 result3 = ( 2103 table.select().where("21 OVERLAPS [22,23]").limit(1).execute() 2104 ) 2105 result4 = ( 2106 table.select().where("['p','q'] OVERLAPS p->'$.prof'").execute() 2107 ) 2108 self.assertEqual(len(result.fetch_all()), 2) 2109 self.assertEqual(len(result1.fetch_all()), 3) 2110 self.assertEqual(len(result2.fetch_all()), 1) 2111 self.assertEqual(len(result3.fetch_all()), 0) 2112 self.assertEqual(len(result4.fetch_all()), 1) 2113 self.session.sql("drop table if exists t8").execute() 2114 2115 @tests.foreach_session() 2116 def test_overlaps_table_select9(self): 2117 """OVERLAPS operator with single element on LHS and dict on RHS 2118 and vice versa.""" 2119 self.session.sql("drop table if exists t9").execute() 2120 self.session.sql( 2121 "create table t9(id int, name varchar(20), a JSON, ai JSON)" 2122 ).execute() 2123 table = self.schema.get_table("t9") 2124 table.insert().values( 2125 1, 2126 "a", 2127 '{"age":23}', 2128 '{"additionalinfo":["reading","music","playing"]}', 2129 ).values( 2130 2, "b", '{"age":21}', '{"additionalinfo":["boxing","music"]}' 2131 ).execute() 2132 result = ( 2133 table.select() 2134 .where("'reading' OVERLAPS ai->$.additionalinfo") 2135 .execute() 2136 ) 2137 result1 = table.select().where("'music' OVERLAPS a->$.age").execute() 2138 result2 = ( 2139 table.select() 2140 .where("'music' OVERLAPS ai->$.additionalinfo") 2141 .execute() 2142 ) 2143 self.assertEqual(len(result.fetch_all()), 1) 2144 self.assertEqual(len(result1.fetch_all()), 0) 2145 self.assertEqual(len(result2.fetch_all()), 2) 2146 self.session.sql("drop table if exists t9").execute() 2147 2148 @tests.foreach_session() 2149 def test_overlaps_table_select10(self): 2150 """OVERLAPS operator with array/list operand on LHS and array/list 2151 on RHS.""" 2152 self.session.sql("drop table if exists t10").execute() 2153 self.session.sql( 2154 "create table t10(i JSON, n JSON, a JSON, ai JSON)" 2155 ).execute() 2156 table = self.schema.get_table("t10") 2157 table.insert().values( 2158 '{"id":1}', 2159 '{"name":"joy"}', 2160 '{"age":21}', 2161 '{"additionalinfo":["reading","music","playing"]}', 2162 ).values( 2163 '{"id":2}', 2164 '{"name":"happy"}', 2165 '{"age":24}', 2166 '{"additionalinfo":["playing","painting","boxing"]}', 2167 ).execute() 2168 result = ( 2169 table.select() 2170 .where( 2171 "['playing','painting','boxing'] OVERLAPS ai->'$.additionalinfo'" 2172 ) 2173 .execute() 2174 ) 2175 result1 = ( 2176 table.select() 2177 .where('["happy","joy"] OVERLAPS n->$.name') 2178 .limit(1) 2179 .execute() 2180 ) 2181 result2 = ( 2182 table.select() 2183 .where('["reading"] NOT OVERLAPS ai->$.additionalinfo') 2184 .limit(1) 2185 .execute() 2186 ) 2187 self.assertEqual(len(result.fetch_all()), 2) 2188 self.assertEqual(len(result1.fetch_all()), 1) 2189 self.assertEqual(len(result2.fetch_all()), 1) 2190 self.session.sql("drop table if exists t10").execute() 2191 2192 @tests.foreach_session() 2193 def test_overlaps_table_select11(self): 2194 """OVERLAPS operator with dict on LHS and dict on RHS.""" 2195 self.session.sql("drop table if exists t11").execute() 2196 self.session.sql( 2197 "create table t11(i JSON, n JSON, a JSON, ai JSON)" 2198 ).execute() 2199 table = self.schema.get_table("t11") 2200 table.insert().values( 2201 '{"id":1}', 2202 '{"name":"joy"}', 2203 '{"age":21}', 2204 '{"additionalinfo":[{"company":"xyz","vehicle":"bike"},{"company":"abc","vehicle":"car"},{"company":"mno","vehicle":"zeep"}]}', 2205 ).values( 2206 '{"id":2}', 2207 '{"name":"happy"}', 2208 '{"age":24}', 2209 '{"additionalinfo":[{"company":"abc","vehicle":"car"},{"company":"pqr","vehicle":"bicycle"}]}', 2210 ).execute() 2211 result = table.select( 2212 '{"company":"abc","vehicle":"car"} OVERLAPS ai->"$.additionalinfo"' 2213 ).execute() 2214 result1 = ( 2215 table.select() 2216 .where('{"vehicle":"car"} NOT OVERLAPS ai->"$.additionalinfo"') 2217 .execute() 2218 ) 2219 result2 = ( 2220 table.select() 2221 .where('{"company":"mno"} OVERLAPS ai->"$.additionalinfo"') 2222 .execute() 2223 ) 2224 self.assertEqual(len(result.fetch_all()), 2) 2225 self.assertEqual(len(result1.fetch_all()), 2) 2226 self.assertEqual(len(result2.fetch_all()), 0) 2227 self.session.sql("drop table if exists t11").execute() 2228 2229 @tests.foreach_session() 2230 def test_overlaps_table_select12(self): 2231 """OVERLAPS operator with operands having expressions.""" 2232 self.session.sql("drop table if exists t12").execute() 2233 self.session.sql( 2234 "create table t12(id int, name varchar(20), age int)" 2235 ).execute() 2236 table = self.schema.get_table("t12") 2237 table.insert().values(1, "a", 21).values(2, "b", 22).values( 2238 3, "c", 32 2239 ).execute() 2240 result = ( 2241 table.select() 2242 .where("[(1>5)] OVERLAPS [true, false]") 2243 .limit(1) 2244 .execute() 2245 ) 2246 result1 = ( 2247 table.select() 2248 .where("[('a'>'b')] overlaps [true, false]") 2249 .limit(1) 2250 .execute() 2251 ) 2252 result2 = ( 2253 table.select() 2254 .where( 2255 "true OVERLAPS [(1>5), !(false), (true || false), (false && true)]" 2256 ) 2257 .limit(1) 2258 .execute() 2259 ) 2260 result3 = ( 2261 table.select() 2262 .where("cast((2+3) as JSON) OVERLAPS [1,2,3,4,5]") 2263 .limit(1) 2264 .execute() 2265 ) 2266 self.assertEqual(len(result.fetch_all()), 1) 2267 self.assertEqual(len(result1.fetch_all()), 1) 2268 self.assertEqual(len(result2.fetch_all()), 1) 2269 self.assertEqual(len(result3.fetch_all()), 1) 2270 self.session.sql("drop table if exists t12").execute() 2271 2272 @tests.foreach_session() 2273 def test_overlaps_table_select13(self): 2274 """OVERLAPS operator with operands having expressions.""" 2275 self.session.sql("drop table if exists t13").execute() 2276 self.session.sql("create table t13(i json, n json, a json)").execute() 2277 table = self.schema.get_table("t13") 2278 table.insert().values('{"id":1}', '{"name":"a"}', '{"age":21}').values( 2279 '{"id":2}', '{"name":"b"}', '{"age":22}' 2280 ).execute() 2281 self.assertRaises( 2282 mysqlx.OperationalError, 2283 table.select().where("(1+5) OVERLAPS [1,2,3,4,5,6]").execute, 2284 ) 2285 # self.assertEqual(len(result.fetch_all()), 2) 2286 self.session.sql("drop table if exists t13").execute() 2287 2288 @tests.foreach_session() 2289 def test_overlaps_table_select14(self): 2290 """OVERLAPS operator: search for empty string in a field and field 2291 in empty string.""" 2292 self.session.sql("drop table if exists t14").execute() 2293 self.session.sql("create table t14(id int, n JSON, age int)").execute() 2294 table = self.schema.get_table("t14") 2295 table.insert().values(1, '{"name":"a"}', 21).values( 2296 2, '{"name":"b"}', 22 2297 ).values(3, '{"name":"c"}', 32).execute() 2298 result = table.select().where("'' OVERLAPS n->'$.name'").execute() 2299 result1 = ( 2300 table.select().where("n->'$.name' OVERLAPS ['', ' ']").execute() 2301 ) 2302 self.assertEqual(len(result.fetch_all()), 0) 2303 self.assertEqual(len(result1.fetch_all()), 0) 2304 self.session.sql("drop table if exists t14").execute() 2305