1import datetime 2import operator 3 4from sqlalchemy import and_ 5from sqlalchemy import between 6from sqlalchemy import exc 7from sqlalchemy import Integer 8from sqlalchemy import LargeBinary 9from sqlalchemy import literal_column 10from sqlalchemy import not_ 11from sqlalchemy import Numeric 12from sqlalchemy import or_ 13from sqlalchemy import String 14from sqlalchemy import testing 15from sqlalchemy import text 16from sqlalchemy import util 17from sqlalchemy.dialects import firebird 18from sqlalchemy.dialects import mssql 19from sqlalchemy.dialects import mysql 20from sqlalchemy.dialects import oracle 21from sqlalchemy.dialects import postgresql 22from sqlalchemy.dialects import sqlite 23from sqlalchemy.engine import default 24from sqlalchemy.schema import Column 25from sqlalchemy.schema import MetaData 26from sqlalchemy.schema import Table 27from sqlalchemy.sql import all_ 28from sqlalchemy.sql import any_ 29from sqlalchemy.sql import asc 30from sqlalchemy.sql import collate 31from sqlalchemy.sql import column 32from sqlalchemy.sql import compiler 33from sqlalchemy.sql import desc 34from sqlalchemy.sql import false 35from sqlalchemy.sql import literal 36from sqlalchemy.sql import null 37from sqlalchemy.sql import operators 38from sqlalchemy.sql import sqltypes 39from sqlalchemy.sql import table 40from sqlalchemy.sql import true 41from sqlalchemy.sql.elements import _literal_as_text 42from sqlalchemy.sql.elements import Label 43from sqlalchemy.sql.expression import BinaryExpression 44from sqlalchemy.sql.expression import ClauseList 45from sqlalchemy.sql.expression import func 46from sqlalchemy.sql.expression import Grouping 47from sqlalchemy.sql.expression import select 48from sqlalchemy.sql.expression import tuple_ 49from sqlalchemy.sql.expression import UnaryExpression 50from sqlalchemy.sql.expression import union 51from sqlalchemy.testing import assert_raises_message 52from sqlalchemy.testing import eq_ 53from sqlalchemy.testing import expect_warnings 54from sqlalchemy.testing import fixtures 55from sqlalchemy.testing import is_ 56from sqlalchemy.testing import is_not 57from sqlalchemy.types import ARRAY 58from sqlalchemy.types import Boolean 59from sqlalchemy.types import Concatenable 60from sqlalchemy.types import DateTime 61from sqlalchemy.types import Indexable 62from sqlalchemy.types import JSON 63from sqlalchemy.types import MatchType 64from sqlalchemy.types import TypeDecorator 65from sqlalchemy.types import TypeEngine 66from sqlalchemy.types import UserDefinedType 67 68 69class LoopOperate(operators.ColumnOperators): 70 def operate(self, op, *other, **kwargs): 71 return op 72 73 74class DefaultColumnComparatorTest(fixtures.TestBase): 75 @testing.combinations((operators.desc_op, desc), (operators.asc_op, asc)) 76 def test_scalar(self, operator, compare_to): 77 left = column("left") 78 assert left.comparator.operate(operator).compare(compare_to(left)) 79 self._loop_test(operator) 80 81 right_column = column("right") 82 83 @testing.combinations( 84 (operators.add, right_column), 85 (operators.is_, None), 86 (operators.isnot, None), 87 (operators.is_, null()), 88 (operators.is_, true()), 89 (operators.is_, false()), 90 (operators.eq, True), 91 (operators.ne, True), 92 (operators.is_distinct_from, True), 93 (operators.is_distinct_from, False), 94 (operators.is_distinct_from, None), 95 (operators.isnot_distinct_from, True), 96 (operators.is_, True), 97 (operators.isnot, True), 98 (operators.is_, False), 99 (operators.isnot, False), 100 (operators.like_op, right_column), 101 (operators.notlike_op, right_column), 102 (operators.ilike_op, right_column), 103 (operators.notilike_op, right_column), 104 (operators.is_, right_column), 105 (operators.isnot, right_column), 106 (operators.concat_op, right_column), 107 id_="ns", 108 ) 109 def test_operate(self, operator, right): 110 left = column("left") 111 112 assert left.comparator.operate(operator, right).compare( 113 BinaryExpression( 114 _literal_as_text(left), _literal_as_text(right), operator 115 ) 116 ) 117 118 assert operator(left, right).compare( 119 BinaryExpression( 120 _literal_as_text(left), _literal_as_text(right), operator 121 ) 122 ) 123 124 self._loop_test(operator, right) 125 126 if operators.is_comparison(operator): 127 is_( 128 left.comparator.operate(operator, right).type, 129 sqltypes.BOOLEANTYPE, 130 ) 131 132 def _loop_test(self, operator, *arg): 133 loop = LoopOperate() 134 is_(operator(loop, *arg), operator) 135 136 def test_no_getitem(self): 137 assert_raises_message( 138 NotImplementedError, 139 "Operator 'getitem' is not supported on this expression", 140 self.test_operate, 141 operators.getitem, 142 column("right"), 143 ) 144 assert_raises_message( 145 NotImplementedError, 146 "Operator 'getitem' is not supported on this expression", 147 lambda: column("left")[3], 148 ) 149 150 def test_in(self): 151 left = column("left") 152 assert left.comparator.operate(operators.in_op, [1, 2, 3]).compare( 153 BinaryExpression( 154 left, 155 Grouping(ClauseList(literal(1), literal(2), literal(3))), 156 operators.in_op, 157 ) 158 ) 159 self._loop_test(operators.in_op, [1, 2, 3]) 160 161 def test_notin(self): 162 left = column("left") 163 assert left.comparator.operate(operators.notin_op, [1, 2, 3]).compare( 164 BinaryExpression( 165 left, 166 Grouping(ClauseList(literal(1), literal(2), literal(3))), 167 operators.notin_op, 168 ) 169 ) 170 self._loop_test(operators.notin_op, [1, 2, 3]) 171 172 def test_in_no_accept_list_of_non_column_element(self): 173 left = column("left") 174 foo = ClauseList() 175 assert_raises_message( 176 exc.InvalidRequestError, 177 r"in_\(\) accepts either a list of expressions, a selectable", 178 left.in_, 179 [foo], 180 ) 181 182 def test_in_no_accept_non_list_non_selectable(self): 183 left = column("left") 184 right = column("right") 185 assert_raises_message( 186 exc.InvalidRequestError, 187 r"in_\(\) accepts either a list of expressions, a selectable", 188 left.in_, 189 right, 190 ) 191 192 def test_in_no_accept_non_list_thing_with_getitem(self): 193 # test [ticket:2726] 194 class HasGetitem(String): 195 class comparator_factory(String.Comparator): 196 def __getitem__(self, value): 197 return value 198 199 left = column("left") 200 right = column("right", HasGetitem) 201 assert_raises_message( 202 exc.InvalidRequestError, 203 r"in_\(\) accepts either a list of expressions, a selectable", 204 left.in_, 205 right, 206 ) 207 208 def test_collate(self): 209 left = column("left") 210 right = "some collation" 211 left.comparator.operate(operators.collate, right).compare( 212 collate(left, right) 213 ) 214 215 def test_default_adapt(self): 216 class TypeOne(TypeEngine): 217 pass 218 219 class TypeTwo(TypeEngine): 220 pass 221 222 expr = column("x", TypeOne()) - column("y", TypeTwo()) 223 is_(expr.type._type_affinity, TypeOne) 224 225 def test_concatenable_adapt(self): 226 class TypeOne(Concatenable, TypeEngine): 227 pass 228 229 class TypeTwo(Concatenable, TypeEngine): 230 pass 231 232 class TypeThree(TypeEngine): 233 pass 234 235 expr = column("x", TypeOne()) - column("y", TypeTwo()) 236 is_(expr.type._type_affinity, TypeOne) 237 is_(expr.operator, operator.sub) 238 239 expr = column("x", TypeOne()) + column("y", TypeTwo()) 240 is_(expr.type._type_affinity, TypeOne) 241 is_(expr.operator, operators.concat_op) 242 243 expr = column("x", TypeOne()) - column("y", TypeThree()) 244 is_(expr.type._type_affinity, TypeOne) 245 is_(expr.operator, operator.sub) 246 247 expr = column("x", TypeOne()) + column("y", TypeThree()) 248 is_(expr.type._type_affinity, TypeOne) 249 is_(expr.operator, operator.add) 250 251 def test_contains_override_raises(self): 252 for col in [ 253 Column("x", String), 254 Column("x", Integer), 255 Column("x", DateTime), 256 ]: 257 assert_raises_message( 258 NotImplementedError, 259 "Operator 'contains' is not supported on this expression", 260 lambda: "foo" in col, 261 ) 262 263 264class CustomUnaryOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL): 265 __dialect__ = "default" 266 267 @testing.fixture 268 def factorial(self): 269 class MyInteger(Integer): 270 class comparator_factory(Integer.Comparator): 271 def factorial(self): 272 return UnaryExpression( 273 self.expr, 274 modifier=operators.custom_op("!"), 275 type_=MyInteger, 276 ) 277 278 def factorial_prefix(self): 279 return UnaryExpression( 280 self.expr, 281 operator=operators.custom_op("!!"), 282 type_=MyInteger, 283 ) 284 285 def __invert__(self): 286 return UnaryExpression( 287 self.expr, 288 operator=operators.custom_op("!!!"), 289 type_=MyInteger, 290 ) 291 292 return MyInteger 293 294 def test_factorial(self, factorial): 295 col = column("somecol", factorial()) 296 self.assert_compile(col.factorial(), "somecol !") 297 298 def test_double_factorial(self, factorial): 299 col = column("somecol", factorial()) 300 self.assert_compile(col.factorial().factorial(), "somecol ! !") 301 302 def test_factorial_prefix(self, factorial): 303 col = column("somecol", factorial()) 304 self.assert_compile(col.factorial_prefix(), "!! somecol") 305 306 def test_factorial_invert(self, factorial): 307 col = column("somecol", factorial()) 308 self.assert_compile(~col, "!!! somecol") 309 310 def test_double_factorial_invert(self, factorial): 311 col = column("somecol", factorial()) 312 self.assert_compile(~(~col), "!!! (!!! somecol)") 313 314 def test_unary_no_ops(self): 315 assert_raises_message( 316 exc.CompileError, 317 "Unary expression has no operator or modifier", 318 UnaryExpression(literal("x")).compile, 319 ) 320 321 def test_unary_both_ops(self): 322 assert_raises_message( 323 exc.CompileError, 324 "Unary expression does not support operator and " 325 "modifier simultaneously", 326 UnaryExpression( 327 literal("x"), 328 operator=operators.custom_op("x"), 329 modifier=operators.custom_op("y"), 330 ).compile, 331 ) 332 333 334class _CustomComparatorTests(object): 335 def test_override_builtin(self): 336 c1 = Column("foo", self._add_override_factory()) 337 self._assert_add_override(c1) 338 339 def test_column_proxy(self): 340 t = Table("t", MetaData(), Column("foo", self._add_override_factory())) 341 proxied = t.select().c.foo 342 self._assert_add_override(proxied) 343 self._assert_and_override(proxied) 344 345 def test_alias_proxy(self): 346 t = Table("t", MetaData(), Column("foo", self._add_override_factory())) 347 proxied = t.alias().c.foo 348 self._assert_add_override(proxied) 349 self._assert_and_override(proxied) 350 351 def test_binary_propagate(self): 352 c1 = Column("foo", self._add_override_factory()) 353 self._assert_add_override(c1 - 6) 354 self._assert_and_override(c1 - 6) 355 356 def test_reverse_binary_propagate(self): 357 c1 = Column("foo", self._add_override_factory()) 358 self._assert_add_override(6 - c1) 359 self._assert_and_override(6 - c1) 360 361 def test_binary_multi_propagate(self): 362 c1 = Column("foo", self._add_override_factory()) 363 self._assert_add_override((c1 - 6) + 5) 364 self._assert_and_override((c1 - 6) + 5) 365 366 def test_no_boolean_propagate(self): 367 c1 = Column("foo", self._add_override_factory()) 368 self._assert_not_add_override(c1 == 56) 369 self._assert_not_and_override(c1 == 56) 370 371 def _assert_and_override(self, expr): 372 assert (expr & text("5")).compare(expr.op("goofy_and")(text("5"))) 373 374 def _assert_add_override(self, expr): 375 assert (expr + 5).compare(expr.op("goofy")(5)) 376 377 def _assert_not_add_override(self, expr): 378 assert not (expr + 5).compare(expr.op("goofy")(5)) 379 380 def _assert_not_and_override(self, expr): 381 assert not (expr & text("5")).compare(expr.op("goofy_and")(text("5"))) 382 383 384class CustomComparatorTest(_CustomComparatorTests, fixtures.TestBase): 385 def _add_override_factory(self): 386 class MyInteger(Integer): 387 class comparator_factory(TypeEngine.Comparator): 388 def __init__(self, expr): 389 super(MyInteger.comparator_factory, self).__init__(expr) 390 391 def __add__(self, other): 392 return self.expr.op("goofy")(other) 393 394 def __and__(self, other): 395 return self.expr.op("goofy_and")(other) 396 397 return MyInteger 398 399 400class TypeDecoratorComparatorTest(_CustomComparatorTests, fixtures.TestBase): 401 def _add_override_factory(self): 402 class MyInteger(TypeDecorator): 403 impl = Integer 404 405 class comparator_factory(TypeDecorator.Comparator): 406 def __init__(self, expr): 407 super(MyInteger.comparator_factory, self).__init__(expr) 408 409 def __add__(self, other): 410 return self.expr.op("goofy")(other) 411 412 def __and__(self, other): 413 return self.expr.op("goofy_and")(other) 414 415 return MyInteger 416 417 418class TypeDecoratorTypeDecoratorComparatorTest( 419 _CustomComparatorTests, fixtures.TestBase 420): 421 def _add_override_factory(self): 422 class MyIntegerOne(TypeDecorator): 423 impl = Integer 424 425 class comparator_factory(TypeDecorator.Comparator): 426 def __init__(self, expr): 427 super(MyIntegerOne.comparator_factory, self).__init__(expr) 428 429 def __add__(self, other): 430 return self.expr.op("goofy")(other) 431 432 def __and__(self, other): 433 return self.expr.op("goofy_and")(other) 434 435 class MyIntegerTwo(TypeDecorator): 436 impl = MyIntegerOne 437 438 return MyIntegerTwo 439 440 441class TypeDecoratorWVariantComparatorTest( 442 _CustomComparatorTests, fixtures.TestBase 443): 444 def _add_override_factory(self): 445 class SomeOtherInteger(Integer): 446 class comparator_factory(TypeEngine.Comparator): 447 def __init__(self, expr): 448 super(SomeOtherInteger.comparator_factory, self).__init__( 449 expr 450 ) 451 452 def __add__(self, other): 453 return self.expr.op("not goofy")(other) 454 455 def __and__(self, other): 456 return self.expr.op("not goofy_and")(other) 457 458 class MyInteger(TypeDecorator): 459 impl = Integer 460 461 class comparator_factory(TypeDecorator.Comparator): 462 def __init__(self, expr): 463 super(MyInteger.comparator_factory, self).__init__(expr) 464 465 def __add__(self, other): 466 return self.expr.op("goofy")(other) 467 468 def __and__(self, other): 469 return self.expr.op("goofy_and")(other) 470 471 return MyInteger().with_variant(SomeOtherInteger, "mysql") 472 473 474class CustomEmbeddedinTypeDecoratorTest( 475 _CustomComparatorTests, fixtures.TestBase 476): 477 def _add_override_factory(self): 478 class MyInteger(Integer): 479 class comparator_factory(TypeEngine.Comparator): 480 def __init__(self, expr): 481 super(MyInteger.comparator_factory, self).__init__(expr) 482 483 def __add__(self, other): 484 return self.expr.op("goofy")(other) 485 486 def __and__(self, other): 487 return self.expr.op("goofy_and")(other) 488 489 class MyDecInteger(TypeDecorator): 490 impl = MyInteger 491 492 return MyDecInteger 493 494 495class NewOperatorTest(_CustomComparatorTests, fixtures.TestBase): 496 def _add_override_factory(self): 497 class MyInteger(Integer): 498 class comparator_factory(TypeEngine.Comparator): 499 def __init__(self, expr): 500 super(MyInteger.comparator_factory, self).__init__(expr) 501 502 def foob(self, other): 503 return self.expr.op("foob")(other) 504 505 return MyInteger 506 507 def _assert_add_override(self, expr): 508 assert (expr.foob(5)).compare(expr.op("foob")(5)) 509 510 def _assert_not_add_override(self, expr): 511 assert not hasattr(expr, "foob") 512 513 def _assert_and_override(self, expr): 514 pass 515 516 def _assert_not_and_override(self, expr): 517 pass 518 519 520class ExtensionOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL): 521 __dialect__ = "default" 522 523 def test_contains(self): 524 class MyType(UserDefinedType): 525 class comparator_factory(UserDefinedType.Comparator): 526 def contains(self, other, **kw): 527 return self.op("->")(other) 528 529 self.assert_compile(Column("x", MyType()).contains(5), "x -> :x_1") 530 531 def test_getitem(self): 532 class MyType(UserDefinedType): 533 class comparator_factory(UserDefinedType.Comparator): 534 def __getitem__(self, index): 535 return self.op("->")(index) 536 537 self.assert_compile(Column("x", MyType())[5], "x -> :x_1") 538 539 def test_op_not_an_iterator(self): 540 # see [ticket:2726] 541 class MyType(UserDefinedType): 542 class comparator_factory(UserDefinedType.Comparator): 543 def __getitem__(self, index): 544 return self.op("->")(index) 545 546 col = Column("x", MyType()) 547 assert not isinstance(col, util.collections_abc.Iterable) 548 549 def test_lshift(self): 550 class MyType(UserDefinedType): 551 class comparator_factory(UserDefinedType.Comparator): 552 def __lshift__(self, other): 553 return self.op("->")(other) 554 555 self.assert_compile(Column("x", MyType()) << 5, "x -> :x_1") 556 557 def test_rshift(self): 558 class MyType(UserDefinedType): 559 class comparator_factory(UserDefinedType.Comparator): 560 def __rshift__(self, other): 561 return self.op("->")(other) 562 563 self.assert_compile(Column("x", MyType()) >> 5, "x -> :x_1") 564 565 566class JSONIndexOpTest(fixtures.TestBase, testing.AssertsCompiledSQL): 567 def setUp(self): 568 class MyTypeCompiler(compiler.GenericTypeCompiler): 569 def visit_mytype(self, type_, **kw): 570 return "MYTYPE" 571 572 def visit_myothertype(self, type_, **kw): 573 return "MYOTHERTYPE" 574 575 class MyCompiler(compiler.SQLCompiler): 576 def visit_json_getitem_op_binary(self, binary, operator, **kw): 577 return self._generate_generic_binary( 578 binary, " -> ", eager_grouping=True, **kw 579 ) 580 581 def visit_json_path_getitem_op_binary( 582 self, binary, operator, **kw 583 ): 584 return self._generate_generic_binary( 585 binary, " #> ", eager_grouping=True, **kw 586 ) 587 588 def visit_getitem_binary(self, binary, operator, **kw): 589 raise NotImplementedError() 590 591 class MyDialect(default.DefaultDialect): 592 statement_compiler = MyCompiler 593 type_compiler = MyTypeCompiler 594 595 class MyType(JSON): 596 __visit_name__ = "mytype" 597 598 pass 599 600 self.MyType = MyType 601 self.__dialect__ = MyDialect() 602 603 def test_setup_getitem(self): 604 col = Column("x", self.MyType()) 605 606 is_(col[5].type._type_affinity, JSON) 607 is_(col[5]["foo"].type._type_affinity, JSON) 608 is_(col[("a", "b", "c")].type._type_affinity, JSON) 609 610 def test_getindex_literal_integer(self): 611 612 col = Column("x", self.MyType()) 613 614 self.assert_compile(col[5], "x -> :x_1", checkparams={"x_1": 5}) 615 616 def test_getindex_literal_string(self): 617 618 col = Column("x", self.MyType()) 619 620 self.assert_compile( 621 col["foo"], "x -> :x_1", checkparams={"x_1": "foo"} 622 ) 623 624 def test_path_getindex_literal(self): 625 626 col = Column("x", self.MyType()) 627 628 self.assert_compile( 629 col[("a", "b", 3, 4, "d")], 630 "x #> :x_1", 631 checkparams={"x_1": ("a", "b", 3, 4, "d")}, 632 ) 633 634 def test_getindex_sqlexpr(self): 635 636 col = Column("x", self.MyType()) 637 col2 = Column("y", Integer()) 638 639 self.assert_compile(col[col2], "x -> y", checkparams={}) 640 641 def test_getindex_sqlexpr_right_grouping(self): 642 643 col = Column("x", self.MyType()) 644 col2 = Column("y", Integer()) 645 646 self.assert_compile( 647 col[col2 + 8], "x -> (y + :y_1)", checkparams={"y_1": 8} 648 ) 649 650 def test_getindex_sqlexpr_left_grouping(self): 651 652 col = Column("x", self.MyType()) 653 654 self.assert_compile(col[8] != None, "(x -> :x_1) IS NOT NULL") # noqa 655 656 def test_getindex_sqlexpr_both_grouping(self): 657 658 col = Column("x", self.MyType()) 659 col2 = Column("y", Integer()) 660 661 self.assert_compile( 662 col[col2 + 8] != None, # noqa 663 "(x -> (y + :y_1)) IS NOT NULL", 664 checkparams={"y_1": 8}, 665 ) 666 667 def test_override_operators(self): 668 special_index_op = operators.custom_op("$$>") 669 670 class MyOtherType(JSON, TypeEngine): 671 __visit_name__ = "myothertype" 672 673 class Comparator(TypeEngine.Comparator): 674 def _adapt_expression(self, op, other_comparator): 675 return special_index_op, MyOtherType() 676 677 comparator_factory = Comparator 678 679 col = Column("x", MyOtherType()) 680 self.assert_compile(col[5], "x $$> :x_1", checkparams={"x_1": 5}) 681 682 def _caster_combinations(fn): 683 return testing.combinations( 684 ("integer", Integer), 685 ("boolean", Boolean), 686 ("float", Numeric), 687 ("string", String), 688 )(fn) 689 690 @_caster_combinations 691 def test_cast_ops(self, caster, expected_type): 692 expr = Column("x", JSON)["foo"] 693 694 expr = getattr(expr, "as_%s" % caster)() 695 is_(expr.type._type_affinity, expected_type) 696 697 @_caster_combinations 698 def test_cast_ops_unsupported_on_non_binary(self, caster, expected_type): 699 expr = Column("x", JSON) 700 701 meth = getattr(expr, "as_%s" % caster) 702 703 assert_raises_message( 704 exc.InvalidRequestError, 705 r"The JSON cast operator JSON.as_%s\(\) only works" % caster, 706 meth, 707 ) 708 709 @_caster_combinations 710 def test_cast_ops_unsupported_on_non_json_binary( 711 self, caster, expected_type 712 ): 713 expr = Column("x", JSON) + {"foo": "bar"} 714 715 meth = getattr(expr, "as_%s" % caster) 716 717 assert_raises_message( 718 exc.InvalidRequestError, 719 r"The JSON cast operator JSON.as_%s\(\) only works" % caster, 720 meth, 721 ) 722 723 724class ArrayIndexOpTest(fixtures.TestBase, testing.AssertsCompiledSQL): 725 def setUp(self): 726 class MyTypeCompiler(compiler.GenericTypeCompiler): 727 def visit_mytype(self, type_, **kw): 728 return "MYTYPE" 729 730 def visit_myothertype(self, type_, **kw): 731 return "MYOTHERTYPE" 732 733 class MyCompiler(compiler.SQLCompiler): 734 def visit_slice(self, element, **kw): 735 return "%s:%s" % ( 736 self.process(element.start, **kw), 737 self.process(element.stop, **kw), 738 ) 739 740 def visit_getitem_binary(self, binary, operator, **kw): 741 return "%s[%s]" % ( 742 self.process(binary.left, **kw), 743 self.process(binary.right, **kw), 744 ) 745 746 class MyDialect(default.DefaultDialect): 747 statement_compiler = MyCompiler 748 type_compiler = MyTypeCompiler 749 750 class MyType(ARRAY): 751 __visit_name__ = "mytype" 752 753 def __init__(self, zero_indexes=False, dimensions=1): 754 if zero_indexes: 755 self.zero_indexes = zero_indexes 756 self.dimensions = dimensions 757 self.item_type = Integer() 758 759 self.MyType = MyType 760 self.__dialect__ = MyDialect() 761 762 def test_setup_getitem_w_dims(self): 763 """test the behavior of the _setup_getitem() method given a simple 764 'dimensions' scheme - this is identical to postgresql.ARRAY.""" 765 766 col = Column("x", self.MyType(dimensions=3)) 767 768 is_(col[5].type._type_affinity, ARRAY) 769 eq_(col[5].type.dimensions, 2) 770 is_(col[5][6].type._type_affinity, ARRAY) 771 eq_(col[5][6].type.dimensions, 1) 772 is_(col[5][6][7].type._type_affinity, Integer) 773 774 def test_getindex_literal(self): 775 776 col = Column("x", self.MyType()) 777 778 self.assert_compile(col[5], "x[:x_1]", checkparams={"x_1": 5}) 779 780 def test_contains_override_raises(self): 781 col = Column("x", self.MyType()) 782 783 assert_raises_message( 784 NotImplementedError, 785 "Operator 'contains' is not supported on this expression", 786 lambda: "foo" in col, 787 ) 788 789 def test_getindex_sqlexpr(self): 790 791 col = Column("x", self.MyType()) 792 col2 = Column("y", Integer()) 793 794 self.assert_compile(col[col2], "x[y]", checkparams={}) 795 796 self.assert_compile( 797 col[col2 + 8], "x[(y + :y_1)]", checkparams={"y_1": 8} 798 ) 799 800 def test_getslice_literal(self): 801 802 col = Column("x", self.MyType()) 803 804 self.assert_compile( 805 col[5:6], "x[:x_1::x_2]", checkparams={"x_1": 5, "x_2": 6} 806 ) 807 808 def test_getslice_sqlexpr(self): 809 810 col = Column("x", self.MyType()) 811 col2 = Column("y", Integer()) 812 813 self.assert_compile( 814 col[col2 : col2 + 5], "x[y:y + :y_1]", checkparams={"y_1": 5} 815 ) 816 817 def test_getindex_literal_zeroind(self): 818 819 col = Column("x", self.MyType(zero_indexes=True)) 820 821 self.assert_compile(col[5], "x[:x_1]", checkparams={"x_1": 6}) 822 823 def test_getindex_sqlexpr_zeroind(self): 824 825 col = Column("x", self.MyType(zero_indexes=True)) 826 col2 = Column("y", Integer()) 827 828 self.assert_compile(col[col2], "x[(y + :y_1)]", checkparams={"y_1": 1}) 829 830 self.assert_compile( 831 col[col2 + 8], 832 "x[(y + :y_1 + :param_1)]", 833 checkparams={"y_1": 8, "param_1": 1}, 834 ) 835 836 def test_getslice_literal_zeroind(self): 837 838 col = Column("x", self.MyType(zero_indexes=True)) 839 840 self.assert_compile( 841 col[5:6], "x[:x_1::x_2]", checkparams={"x_1": 6, "x_2": 7} 842 ) 843 844 def test_getslice_sqlexpr_zeroind(self): 845 846 col = Column("x", self.MyType(zero_indexes=True)) 847 col2 = Column("y", Integer()) 848 849 self.assert_compile( 850 col[col2 : col2 + 5], 851 "x[y + :y_1:y + :y_2 + :param_1]", 852 checkparams={"y_1": 1, "y_2": 5, "param_1": 1}, 853 ) 854 855 def test_override_operators(self): 856 special_index_op = operators.custom_op("->") 857 858 class MyOtherType(Indexable, TypeEngine): 859 __visit_name__ = "myothertype" 860 861 class Comparator(TypeEngine.Comparator): 862 def _adapt_expression(self, op, other_comparator): 863 return special_index_op, MyOtherType() 864 865 comparator_factory = Comparator 866 867 col = Column("x", MyOtherType()) 868 self.assert_compile(col[5], "x -> :x_1", checkparams={"x_1": 5}) 869 870 871class BooleanEvalTest(fixtures.TestBase, testing.AssertsCompiledSQL): 872 873 """test standalone booleans being wrapped in an AsBoolean, as well 874 as true/false compilation.""" 875 876 def _dialect(self, native_boolean): 877 d = default.DefaultDialect() 878 d.supports_native_boolean = native_boolean 879 return d 880 881 def test_one(self): 882 c = column("x", Boolean) 883 self.assert_compile( 884 select([c]).where(c), 885 "SELECT x WHERE x", 886 dialect=self._dialect(True), 887 ) 888 889 def test_two_a(self): 890 c = column("x", Boolean) 891 self.assert_compile( 892 select([c]).where(c), 893 "SELECT x WHERE x = 1", 894 dialect=self._dialect(False), 895 ) 896 897 def test_two_b(self): 898 c = column("x", Boolean) 899 self.assert_compile( 900 select([c], whereclause=c), 901 "SELECT x WHERE x = 1", 902 dialect=self._dialect(False), 903 ) 904 905 def test_three_a(self): 906 c = column("x", Boolean) 907 self.assert_compile( 908 select([c]).where(~c), 909 "SELECT x WHERE x = 0", 910 dialect=self._dialect(False), 911 ) 912 913 def test_three_a_double(self): 914 c = column("x", Boolean) 915 self.assert_compile( 916 select([c]).where(~~c), 917 "SELECT x WHERE x = 1", 918 dialect=self._dialect(False), 919 ) 920 921 def test_three_b(self): 922 c = column("x", Boolean) 923 self.assert_compile( 924 select([c], whereclause=~c), 925 "SELECT x WHERE x = 0", 926 dialect=self._dialect(False), 927 ) 928 929 def test_four(self): 930 c = column("x", Boolean) 931 self.assert_compile( 932 select([c]).where(~c), 933 "SELECT x WHERE NOT x", 934 dialect=self._dialect(True), 935 ) 936 937 def test_four_double(self): 938 c = column("x", Boolean) 939 self.assert_compile( 940 select([c]).where(~~c), 941 "SELECT x WHERE x", 942 dialect=self._dialect(True), 943 ) 944 945 def test_five_a(self): 946 c = column("x", Boolean) 947 self.assert_compile( 948 select([c]).having(c), 949 "SELECT x HAVING x = 1", 950 dialect=self._dialect(False), 951 ) 952 953 def test_five_b(self): 954 c = column("x", Boolean) 955 self.assert_compile( 956 select([c], having=c), 957 "SELECT x HAVING x = 1", 958 dialect=self._dialect(False), 959 ) 960 961 def test_six(self): 962 self.assert_compile( 963 or_(false(), true()), "1 = 1", dialect=self._dialect(False) 964 ) 965 966 def test_eight(self): 967 self.assert_compile( 968 and_(false(), true()), "false", dialect=self._dialect(True) 969 ) 970 971 def test_nine(self): 972 self.assert_compile( 973 and_(false(), true()), "0 = 1", dialect=self._dialect(False) 974 ) 975 976 def test_ten(self): 977 c = column("x", Boolean) 978 self.assert_compile(c == 1, "x = :x_1", dialect=self._dialect(False)) 979 980 def test_eleven(self): 981 c = column("x", Boolean) 982 self.assert_compile( 983 c.is_(true()), "x IS true", dialect=self._dialect(True) 984 ) 985 986 def test_twelve(self): 987 c = column("x", Boolean) 988 # I don't have a solution for this one yet, 989 # other than adding some heavy-handed conditionals 990 # into compiler 991 self.assert_compile( 992 c.is_(true()), "x IS 1", dialect=self._dialect(False) 993 ) 994 995 996class ConjunctionTest(fixtures.TestBase, testing.AssertsCompiledSQL): 997 998 """test interaction of and_()/or_() with boolean , null constants""" 999 1000 __dialect__ = default.DefaultDialect(supports_native_boolean=True) 1001 1002 def test_one(self): 1003 self.assert_compile(~and_(true()), "false") 1004 1005 def test_two(self): 1006 self.assert_compile(or_(~and_(true())), "false") 1007 1008 def test_three(self): 1009 self.assert_compile(or_(and_()), "") 1010 1011 def test_four(self): 1012 x = column("x") 1013 self.assert_compile( 1014 and_(or_(x == 5), or_(x == 7)), "x = :x_1 AND x = :x_2" 1015 ) 1016 1017 def test_five(self): 1018 x = column("x") 1019 self.assert_compile(and_(true()._ifnone(None), x == 7), "x = :x_1") 1020 1021 def test_six(self): 1022 x = column("x") 1023 self.assert_compile(or_(true(), x == 7), "true") 1024 self.assert_compile(or_(x == 7, true()), "true") 1025 self.assert_compile(~or_(x == 7, true()), "false") 1026 1027 def test_six_pt_five(self): 1028 x = column("x") 1029 self.assert_compile( 1030 select([x]).where(or_(x == 7, true())), "SELECT x WHERE true" 1031 ) 1032 1033 self.assert_compile( 1034 select([x]).where(or_(x == 7, true())), 1035 "SELECT x WHERE 1 = 1", 1036 dialect=default.DefaultDialect(supports_native_boolean=False), 1037 ) 1038 1039 def test_seven(self): 1040 x = column("x") 1041 self.assert_compile( 1042 and_(true(), x == 7, true(), x == 9), "x = :x_1 AND x = :x_2" 1043 ) 1044 1045 def test_eight(self): 1046 x = column("x") 1047 self.assert_compile( 1048 or_(false(), x == 7, false(), x == 9), "x = :x_1 OR x = :x_2" 1049 ) 1050 1051 def test_nine(self): 1052 x = column("x") 1053 self.assert_compile(and_(x == 7, x == 9, false(), x == 5), "false") 1054 self.assert_compile(~and_(x == 7, x == 9, false(), x == 5), "true") 1055 1056 def test_ten(self): 1057 self.assert_compile(and_(None, None), "NULL AND NULL") 1058 1059 def test_eleven(self): 1060 x = column("x") 1061 self.assert_compile( 1062 select([x]).where(None).where(None), "SELECT x WHERE NULL AND NULL" 1063 ) 1064 1065 def test_twelve(self): 1066 x = column("x") 1067 self.assert_compile( 1068 select([x]).where(and_(None, None)), "SELECT x WHERE NULL AND NULL" 1069 ) 1070 1071 def test_thirteen(self): 1072 x = column("x") 1073 self.assert_compile( 1074 select([x]).where(~and_(None, None)), 1075 "SELECT x WHERE NOT (NULL AND NULL)", 1076 ) 1077 1078 def test_fourteen(self): 1079 x = column("x") 1080 self.assert_compile( 1081 select([x]).where(~null()), "SELECT x WHERE NOT NULL" 1082 ) 1083 1084 def test_constant_non_singleton(self): 1085 is_not(null(), null()) 1086 is_not(false(), false()) 1087 is_not(true(), true()) 1088 1089 def test_constant_render_distinct(self): 1090 self.assert_compile( 1091 select([null(), null()]), "SELECT NULL AS anon_1, NULL AS anon_2" 1092 ) 1093 self.assert_compile( 1094 select([true(), true()]), "SELECT true AS anon_1, true AS anon_2" 1095 ) 1096 self.assert_compile( 1097 select([false(), false()]), 1098 "SELECT false AS anon_1, false AS anon_2", 1099 ) 1100 1101 def test_is_true_literal(self): 1102 c = column("x", Boolean) 1103 self.assert_compile(c.is_(True), "x IS true") 1104 1105 def test_is_false_literal(self): 1106 c = column("x", Boolean) 1107 self.assert_compile(c.is_(False), "x IS false") 1108 1109 def test_and_false_literal_leading(self): 1110 self.assert_compile(and_(False, True), "false") 1111 1112 self.assert_compile(and_(False, False), "false") 1113 1114 def test_and_true_literal_leading(self): 1115 self.assert_compile(and_(True, True), "true") 1116 1117 self.assert_compile(and_(True, False), "false") 1118 1119 def test_or_false_literal_leading(self): 1120 self.assert_compile(or_(False, True), "true") 1121 1122 self.assert_compile(or_(False, False), "false") 1123 1124 def test_or_true_literal_leading(self): 1125 self.assert_compile(or_(True, True), "true") 1126 1127 self.assert_compile(or_(True, False), "true") 1128 1129 1130class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): 1131 __dialect__ = "default" 1132 1133 table1 = table( 1134 "mytable", 1135 column("myid", Integer), 1136 column("name", String), 1137 column("description", String), 1138 ) 1139 1140 table2 = table("op", column("field")) 1141 1142 def test_operator_precedence_1(self): 1143 self.assert_compile( 1144 self.table2.select((self.table2.c.field == 5) == None), # noqa 1145 "SELECT op.field FROM op WHERE (op.field = :field_1) IS NULL", 1146 ) 1147 1148 def test_operator_precedence_2(self): 1149 self.assert_compile( 1150 self.table2.select( 1151 (self.table2.c.field + 5) == self.table2.c.field 1152 ), 1153 "SELECT op.field FROM op WHERE op.field + :field_1 = op.field", 1154 ) 1155 1156 def test_operator_precedence_3(self): 1157 self.assert_compile( 1158 self.table2.select((self.table2.c.field + 5) * 6), 1159 "SELECT op.field FROM op WHERE (op.field + :field_1) * :param_1", 1160 ) 1161 1162 def test_operator_precedence_4(self): 1163 self.assert_compile( 1164 self.table2.select((self.table2.c.field * 5) + 6), 1165 "SELECT op.field FROM op WHERE op.field * :field_1 + :param_1", 1166 ) 1167 1168 def test_operator_precedence_5(self): 1169 self.assert_compile( 1170 self.table2.select(5 + self.table2.c.field.in_([5, 6])), 1171 "SELECT op.field FROM op WHERE :param_1 + " 1172 "(op.field IN (:field_1, :field_2))", 1173 ) 1174 1175 def test_operator_precedence_6(self): 1176 self.assert_compile( 1177 self.table2.select((5 + self.table2.c.field).in_([5, 6])), 1178 "SELECT op.field FROM op WHERE :field_1 + op.field " 1179 "IN (:param_1, :param_2)", 1180 ) 1181 1182 def test_operator_precedence_7(self): 1183 self.assert_compile( 1184 self.table2.select( 1185 not_(and_(self.table2.c.field == 5, self.table2.c.field == 7)) 1186 ), 1187 "SELECT op.field FROM op WHERE NOT " 1188 "(op.field = :field_1 AND op.field = :field_2)", 1189 ) 1190 1191 def test_operator_precedence_8(self): 1192 self.assert_compile( 1193 self.table2.select(not_(self.table2.c.field == 5)), 1194 "SELECT op.field FROM op WHERE op.field != :field_1", 1195 ) 1196 1197 def test_operator_precedence_9(self): 1198 self.assert_compile( 1199 self.table2.select(not_(self.table2.c.field.between(5, 6))), 1200 "SELECT op.field FROM op WHERE " 1201 "op.field NOT BETWEEN :field_1 AND :field_2", 1202 ) 1203 1204 def test_operator_precedence_10(self): 1205 self.assert_compile( 1206 self.table2.select(not_(self.table2.c.field) == 5), 1207 "SELECT op.field FROM op WHERE (NOT op.field) = :param_1", 1208 ) 1209 1210 def test_operator_precedence_11(self): 1211 self.assert_compile( 1212 self.table2.select( 1213 (self.table2.c.field == self.table2.c.field).between( 1214 False, True 1215 ) 1216 ), 1217 "SELECT op.field FROM op WHERE (op.field = op.field) " 1218 "BETWEEN :param_1 AND :param_2", 1219 ) 1220 1221 def test_operator_precedence_12(self): 1222 self.assert_compile( 1223 self.table2.select( 1224 between( 1225 (self.table2.c.field == self.table2.c.field), False, True 1226 ) 1227 ), 1228 "SELECT op.field FROM op WHERE (op.field = op.field) " 1229 "BETWEEN :param_1 AND :param_2", 1230 ) 1231 1232 def test_operator_precedence_13(self): 1233 self.assert_compile( 1234 self.table2.select( 1235 self.table2.c.field.match(self.table2.c.field).is_(None) 1236 ), 1237 "SELECT op.field FROM op WHERE (op.field MATCH op.field) IS NULL", 1238 ) 1239 1240 def test_operator_precedence_collate_1(self): 1241 self.assert_compile( 1242 self.table1.c.name == literal("foo").collate("utf-8"), 1243 'mytable.name = (:param_1 COLLATE "utf-8")', 1244 ) 1245 1246 def test_operator_precedence_collate_2(self): 1247 self.assert_compile( 1248 (self.table1.c.name == literal("foo")).collate("utf-8"), 1249 'mytable.name = :param_1 COLLATE "utf-8"', 1250 ) 1251 1252 def test_operator_precedence_collate_3(self): 1253 self.assert_compile( 1254 self.table1.c.name.collate("utf-8") == "foo", 1255 '(mytable.name COLLATE "utf-8") = :param_1', 1256 ) 1257 1258 def test_operator_precedence_collate_4(self): 1259 self.assert_compile( 1260 and_( 1261 (self.table1.c.name == literal("foo")).collate("utf-8"), 1262 (self.table2.c.field == literal("bar")).collate("utf-8"), 1263 ), 1264 'mytable.name = :param_1 COLLATE "utf-8" ' 1265 'AND op.field = :param_2 COLLATE "utf-8"', 1266 ) 1267 1268 def test_operator_precedence_collate_5(self): 1269 self.assert_compile( 1270 select([self.table1.c.name]).order_by( 1271 self.table1.c.name.collate("utf-8").desc() 1272 ), 1273 "SELECT mytable.name FROM mytable " 1274 'ORDER BY mytable.name COLLATE "utf-8" DESC', 1275 ) 1276 1277 def test_operator_precedence_collate_6(self): 1278 self.assert_compile( 1279 select([self.table1.c.name]).order_by( 1280 self.table1.c.name.collate("utf-8").desc().nullslast() 1281 ), 1282 "SELECT mytable.name FROM mytable " 1283 'ORDER BY mytable.name COLLATE "utf-8" DESC NULLS LAST', 1284 ) 1285 1286 def test_operator_precedence_collate_7(self): 1287 self.assert_compile( 1288 select([self.table1.c.name]).order_by( 1289 self.table1.c.name.collate("utf-8").asc() 1290 ), 1291 "SELECT mytable.name FROM mytable " 1292 'ORDER BY mytable.name COLLATE "utf-8" ASC', 1293 ) 1294 1295 def test_commutative_operators(self): 1296 self.assert_compile( 1297 literal("a") + literal("b") * literal("c"), 1298 ":param_1 || :param_2 * :param_3", 1299 ) 1300 1301 def test_op_operators(self): 1302 self.assert_compile( 1303 self.table1.select(self.table1.c.myid.op("hoho")(12) == 14), 1304 "SELECT mytable.myid, mytable.name, mytable.description FROM " 1305 "mytable WHERE (mytable.myid hoho :myid_1) = :param_1", 1306 ) 1307 1308 def test_op_operators_comma_precedence(self): 1309 self.assert_compile( 1310 func.foo(self.table1.c.myid.op("hoho")(12)), 1311 "foo(mytable.myid hoho :myid_1)", 1312 ) 1313 1314 def test_op_operators_comparison_precedence(self): 1315 self.assert_compile( 1316 self.table1.c.myid.op("hoho")(12) == 5, 1317 "(mytable.myid hoho :myid_1) = :param_1", 1318 ) 1319 1320 def test_op_operators_custom_precedence(self): 1321 op1 = self.table1.c.myid.op("hoho", precedence=5) 1322 op2 = op1(5).op("lala", precedence=4)(4) 1323 op3 = op1(5).op("lala", precedence=6)(4) 1324 1325 self.assert_compile(op2, "mytable.myid hoho :myid_1 lala :param_1") 1326 self.assert_compile(op3, "(mytable.myid hoho :myid_1) lala :param_1") 1327 1328 def test_is_eq_precedence_flat(self): 1329 self.assert_compile( 1330 (self.table1.c.name == null()) 1331 != (self.table1.c.description == null()), 1332 "(mytable.name IS NULL) != (mytable.description IS NULL)", 1333 ) 1334 1335 1336class OperatorAssociativityTest(fixtures.TestBase, testing.AssertsCompiledSQL): 1337 __dialect__ = "default" 1338 1339 def test_associativity_1(self): 1340 f = column("f") 1341 self.assert_compile(f - f, "f - f") 1342 1343 def test_associativity_2(self): 1344 f = column("f") 1345 self.assert_compile(f - f - f, "(f - f) - f") 1346 1347 def test_associativity_3(self): 1348 f = column("f") 1349 self.assert_compile((f - f) - f, "(f - f) - f") 1350 1351 def test_associativity_4(self): 1352 f = column("f") 1353 self.assert_compile((f - f).label("foo") - f, "(f - f) - f") 1354 1355 def test_associativity_5(self): 1356 f = column("f") 1357 self.assert_compile(f - (f - f), "f - (f - f)") 1358 1359 def test_associativity_6(self): 1360 f = column("f") 1361 self.assert_compile(f - (f - f).label("foo"), "f - (f - f)") 1362 1363 def test_associativity_7(self): 1364 f = column("f") 1365 # because - less precedent than / 1366 self.assert_compile(f / (f - f), "f / (f - f)") 1367 1368 def test_associativity_8(self): 1369 f = column("f") 1370 self.assert_compile(f / (f - f).label("foo"), "f / (f - f)") 1371 1372 def test_associativity_9(self): 1373 f = column("f") 1374 self.assert_compile(f / f - f, "f / f - f") 1375 1376 def test_associativity_10(self): 1377 f = column("f") 1378 self.assert_compile((f / f) - f, "f / f - f") 1379 1380 def test_associativity_11(self): 1381 f = column("f") 1382 self.assert_compile((f / f).label("foo") - f, "f / f - f") 1383 1384 def test_associativity_12(self): 1385 f = column("f") 1386 # because / more precedent than - 1387 self.assert_compile(f - (f / f), "f - f / f") 1388 1389 def test_associativity_13(self): 1390 f = column("f") 1391 self.assert_compile(f - (f / f).label("foo"), "f - f / f") 1392 1393 def test_associativity_14(self): 1394 f = column("f") 1395 self.assert_compile(f - f / f, "f - f / f") 1396 1397 def test_associativity_15(self): 1398 f = column("f") 1399 self.assert_compile((f - f) / f, "(f - f) / f") 1400 1401 def test_associativity_16(self): 1402 f = column("f") 1403 self.assert_compile(((f - f) / f) - f, "(f - f) / f - f") 1404 1405 def test_associativity_17(self): 1406 f = column("f") 1407 # - lower precedence than / 1408 self.assert_compile((f - f) / (f - f), "(f - f) / (f - f)") 1409 1410 def test_associativity_18(self): 1411 f = column("f") 1412 # / higher precedence than - 1413 self.assert_compile((f / f) - (f / f), "f / f - f / f") 1414 1415 def test_associativity_19(self): 1416 f = column("f") 1417 self.assert_compile((f / f) - (f - f), "f / f - (f - f)") 1418 1419 def test_associativity_20(self): 1420 f = column("f") 1421 self.assert_compile((f / f) / (f - f), "(f / f) / (f - f)") 1422 1423 def test_associativity_21(self): 1424 f = column("f") 1425 self.assert_compile(f / (f / (f - f)), "f / (f / (f - f))") 1426 1427 def test_associativity_22(self): 1428 f = column("f") 1429 self.assert_compile((f == f) == f, "(f = f) = f") 1430 1431 def test_associativity_23(self): 1432 f = column("f") 1433 self.assert_compile((f != f) != f, "(f != f) != f") 1434 1435 1436class IsDistinctFromTest(fixtures.TestBase, testing.AssertsCompiledSQL): 1437 __dialect__ = "default" 1438 1439 table1 = table("mytable", column("myid", Integer)) 1440 1441 def test_is_distinct_from(self): 1442 self.assert_compile( 1443 self.table1.c.myid.is_distinct_from(1), 1444 "mytable.myid IS DISTINCT FROM :myid_1", 1445 ) 1446 1447 def test_is_distinct_from_sqlite(self): 1448 self.assert_compile( 1449 self.table1.c.myid.is_distinct_from(1), 1450 "mytable.myid IS NOT ?", 1451 dialect=sqlite.dialect(), 1452 ) 1453 1454 def test_is_distinct_from_postgresql(self): 1455 self.assert_compile( 1456 self.table1.c.myid.is_distinct_from(1), 1457 "mytable.myid IS DISTINCT FROM %(myid_1)s", 1458 dialect=postgresql.dialect(), 1459 ) 1460 1461 def test_not_is_distinct_from(self): 1462 self.assert_compile( 1463 ~self.table1.c.myid.is_distinct_from(1), 1464 "mytable.myid IS NOT DISTINCT FROM :myid_1", 1465 ) 1466 1467 def test_not_is_distinct_from_postgresql(self): 1468 self.assert_compile( 1469 ~self.table1.c.myid.is_distinct_from(1), 1470 "mytable.myid IS NOT DISTINCT FROM %(myid_1)s", 1471 dialect=postgresql.dialect(), 1472 ) 1473 1474 def test_isnot_distinct_from(self): 1475 self.assert_compile( 1476 self.table1.c.myid.isnot_distinct_from(1), 1477 "mytable.myid IS NOT DISTINCT FROM :myid_1", 1478 ) 1479 1480 def test_isnot_distinct_from_sqlite(self): 1481 self.assert_compile( 1482 self.table1.c.myid.isnot_distinct_from(1), 1483 "mytable.myid IS ?", 1484 dialect=sqlite.dialect(), 1485 ) 1486 1487 def test_isnot_distinct_from_postgresql(self): 1488 self.assert_compile( 1489 self.table1.c.myid.isnot_distinct_from(1), 1490 "mytable.myid IS NOT DISTINCT FROM %(myid_1)s", 1491 dialect=postgresql.dialect(), 1492 ) 1493 1494 def test_not_isnot_distinct_from(self): 1495 self.assert_compile( 1496 ~self.table1.c.myid.isnot_distinct_from(1), 1497 "mytable.myid IS DISTINCT FROM :myid_1", 1498 ) 1499 1500 def test_not_isnot_distinct_from_postgresql(self): 1501 self.assert_compile( 1502 ~self.table1.c.myid.isnot_distinct_from(1), 1503 "mytable.myid IS DISTINCT FROM %(myid_1)s", 1504 dialect=postgresql.dialect(), 1505 ) 1506 1507 1508class InTest(fixtures.TestBase, testing.AssertsCompiledSQL): 1509 __dialect__ = "default" 1510 1511 table1 = table("mytable", column("myid", Integer)) 1512 table2 = table( 1513 "myothertable", column("otherid", Integer), column("othername", String) 1514 ) 1515 1516 def _dialect(self, empty_in_strategy="static"): 1517 return default.DefaultDialect(empty_in_strategy=empty_in_strategy) 1518 1519 def test_in_1(self): 1520 self.assert_compile( 1521 self.table1.c.myid.in_(["a"]), "mytable.myid IN (:myid_1)" 1522 ) 1523 1524 def test_in_2(self): 1525 self.assert_compile( 1526 ~self.table1.c.myid.in_(["a"]), "mytable.myid NOT IN (:myid_1)" 1527 ) 1528 1529 def test_in_3(self): 1530 self.assert_compile( 1531 self.table1.c.myid.in_(["a", "b"]), 1532 "mytable.myid IN (:myid_1, :myid_2)", 1533 ) 1534 1535 def test_in_4(self): 1536 self.assert_compile( 1537 self.table1.c.myid.in_(iter(["a", "b"])), 1538 "mytable.myid IN (:myid_1, :myid_2)", 1539 ) 1540 1541 def test_in_5(self): 1542 self.assert_compile( 1543 self.table1.c.myid.in_([literal("a")]), 1544 "mytable.myid IN (:param_1)", 1545 ) 1546 1547 def test_in_6(self): 1548 self.assert_compile( 1549 self.table1.c.myid.in_([literal("a"), "b"]), 1550 "mytable.myid IN (:param_1, :myid_1)", 1551 ) 1552 1553 def test_in_7(self): 1554 self.assert_compile( 1555 self.table1.c.myid.in_([literal("a"), literal("b")]), 1556 "mytable.myid IN (:param_1, :param_2)", 1557 ) 1558 1559 def test_in_8(self): 1560 self.assert_compile( 1561 self.table1.c.myid.in_(["a", literal("b")]), 1562 "mytable.myid IN (:myid_1, :param_1)", 1563 ) 1564 1565 def test_in_9(self): 1566 self.assert_compile( 1567 self.table1.c.myid.in_([literal(1) + "a"]), 1568 "mytable.myid IN (:param_1 + :param_2)", 1569 ) 1570 1571 def test_in_10(self): 1572 self.assert_compile( 1573 self.table1.c.myid.in_([literal("a") + "a", "b"]), 1574 "mytable.myid IN (:param_1 || :param_2, :myid_1)", 1575 ) 1576 1577 def test_in_11(self): 1578 self.assert_compile( 1579 self.table1.c.myid.in_( 1580 [literal("a") + literal("a"), literal("b")] 1581 ), 1582 "mytable.myid IN (:param_1 || :param_2, :param_3)", 1583 ) 1584 1585 def test_in_12(self): 1586 self.assert_compile( 1587 self.table1.c.myid.in_([1, literal(3) + 4]), 1588 "mytable.myid IN (:myid_1, :param_1 + :param_2)", 1589 ) 1590 1591 def test_in_13(self): 1592 self.assert_compile( 1593 self.table1.c.myid.in_([literal("a") < "b"]), 1594 "mytable.myid IN (:param_1 < :param_2)", 1595 ) 1596 1597 def test_in_14(self): 1598 self.assert_compile( 1599 self.table1.c.myid.in_([self.table1.c.myid]), 1600 "mytable.myid IN (mytable.myid)", 1601 ) 1602 1603 def test_in_15(self): 1604 self.assert_compile( 1605 self.table1.c.myid.in_(["a", self.table1.c.myid]), 1606 "mytable.myid IN (:myid_1, mytable.myid)", 1607 ) 1608 1609 def test_in_16(self): 1610 self.assert_compile( 1611 self.table1.c.myid.in_([literal("a"), self.table1.c.myid]), 1612 "mytable.myid IN (:param_1, mytable.myid)", 1613 ) 1614 1615 def test_in_17(self): 1616 self.assert_compile( 1617 self.table1.c.myid.in_([literal("a"), self.table1.c.myid + "a"]), 1618 "mytable.myid IN (:param_1, mytable.myid + :myid_1)", 1619 ) 1620 1621 def test_in_18(self): 1622 self.assert_compile( 1623 self.table1.c.myid.in_([literal(1), "a" + self.table1.c.myid]), 1624 "mytable.myid IN (:param_1, :myid_1 + mytable.myid)", 1625 ) 1626 1627 def test_in_19(self): 1628 self.assert_compile( 1629 self.table1.c.myid.in_([1, 2, 3]), 1630 "mytable.myid IN (:myid_1, :myid_2, :myid_3)", 1631 ) 1632 1633 def test_in_20(self): 1634 self.assert_compile( 1635 self.table1.c.myid.in_(select([self.table2.c.otherid])), 1636 "mytable.myid IN (SELECT myothertable.otherid FROM myothertable)", 1637 ) 1638 1639 def test_in_21(self): 1640 self.assert_compile( 1641 ~self.table1.c.myid.in_(select([self.table2.c.otherid])), 1642 "mytable.myid NOT IN " 1643 "(SELECT myothertable.otherid FROM myothertable)", 1644 ) 1645 1646 def test_in_22(self): 1647 self.assert_compile( 1648 self.table1.c.myid.in_( 1649 text("SELECT myothertable.otherid FROM myothertable") 1650 ), 1651 "mytable.myid IN (SELECT myothertable.otherid " 1652 "FROM myothertable)", 1653 ) 1654 1655 def test_in_24(self): 1656 self.assert_compile( 1657 select([self.table1.c.myid.in_(select([self.table2.c.otherid]))]), 1658 "SELECT mytable.myid IN (SELECT myothertable.otherid " 1659 "FROM myothertable) AS anon_1 FROM mytable", 1660 ) 1661 1662 def test_in_25(self): 1663 self.assert_compile( 1664 select( 1665 [ 1666 self.table1.c.myid.in_( 1667 select([self.table2.c.otherid]).as_scalar() 1668 ) 1669 ] 1670 ), 1671 "SELECT mytable.myid IN (SELECT myothertable.otherid " 1672 "FROM myothertable) AS anon_1 FROM mytable", 1673 ) 1674 1675 def test_in_26(self): 1676 self.assert_compile( 1677 self.table1.c.myid.in_( 1678 union( 1679 select([self.table1.c.myid], self.table1.c.myid == 5), 1680 select([self.table1.c.myid], self.table1.c.myid == 12), 1681 ) 1682 ), 1683 "mytable.myid IN (" 1684 "SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_1 " 1685 "UNION SELECT mytable.myid FROM mytable " 1686 "WHERE mytable.myid = :myid_2)", 1687 ) 1688 1689 def test_in_27(self): 1690 # test that putting a select in an IN clause does not 1691 # blow away its ORDER BY clause 1692 self.assert_compile( 1693 select( 1694 [self.table1, self.table2], 1695 self.table2.c.otherid.in_( 1696 select( 1697 [self.table2.c.otherid], 1698 order_by=[self.table2.c.othername], 1699 limit=10, 1700 correlate=False, 1701 ) 1702 ), 1703 from_obj=[ 1704 self.table1.join( 1705 self.table2, 1706 self.table1.c.myid == self.table2.c.otherid, 1707 ) 1708 ], 1709 order_by=[self.table1.c.myid], 1710 ), 1711 "SELECT mytable.myid, " 1712 "myothertable.otherid, myothertable.othername FROM mytable " 1713 "JOIN myothertable ON mytable.myid = myothertable.otherid " 1714 "WHERE myothertable.otherid IN (SELECT myothertable.otherid " 1715 "FROM myothertable ORDER BY myothertable.othername " 1716 "LIMIT :param_1) ORDER BY mytable.myid", 1717 {"param_1": 10}, 1718 ) 1719 1720 def test_in_28(self): 1721 self.assert_compile( 1722 self.table1.c.myid.in_([None]), "mytable.myid IN (NULL)" 1723 ) 1724 1725 def test_empty_in_dynamic_1(self): 1726 self.assert_compile( 1727 self.table1.c.myid.in_([]), 1728 "mytable.myid != mytable.myid", 1729 dialect=self._dialect("dynamic"), 1730 ) 1731 1732 def test_empty_in_dynamic_2(self): 1733 self.assert_compile( 1734 self.table1.c.myid.notin_([]), 1735 "mytable.myid = mytable.myid", 1736 dialect=self._dialect("dynamic"), 1737 ) 1738 1739 def test_empty_in_dynamic_3(self): 1740 self.assert_compile( 1741 ~self.table1.c.myid.in_([]), 1742 "mytable.myid = mytable.myid", 1743 dialect=self._dialect("dynamic"), 1744 ) 1745 1746 def test_empty_in_dynamic_warn_1(self): 1747 with testing.expect_warnings( 1748 "The IN-predicate was invoked with an empty sequence." 1749 ): 1750 self.assert_compile( 1751 self.table1.c.myid.in_([]), 1752 "mytable.myid != mytable.myid", 1753 dialect=self._dialect("dynamic_warn"), 1754 ) 1755 1756 def test_empty_in_dynamic_warn_2(self): 1757 with testing.expect_warnings( 1758 "The IN-predicate was invoked with an empty sequence." 1759 ): 1760 self.assert_compile( 1761 self.table1.c.myid.notin_([]), 1762 "mytable.myid = mytable.myid", 1763 dialect=self._dialect("dynamic_warn"), 1764 ) 1765 1766 def test_empty_in_dynamic_warn_3(self): 1767 with testing.expect_warnings( 1768 "The IN-predicate was invoked with an empty sequence." 1769 ): 1770 self.assert_compile( 1771 ~self.table1.c.myid.in_([]), 1772 "mytable.myid = mytable.myid", 1773 dialect=self._dialect("dynamic_warn"), 1774 ) 1775 1776 def test_empty_in_static_1(self): 1777 self.assert_compile(self.table1.c.myid.in_([]), "1 != 1") 1778 1779 def test_empty_in_static_2(self): 1780 self.assert_compile(self.table1.c.myid.notin_([]), "1 = 1") 1781 1782 def test_empty_in_static_3(self): 1783 self.assert_compile(~self.table1.c.myid.in_([]), "1 = 1") 1784 1785 1786class MathOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL): 1787 __dialect__ = "default" 1788 1789 table1 = table("mytable", column("myid", Integer)) 1790 1791 @testing.combinations( 1792 ("add", operator.add, "+"), 1793 ("mul", operator.mul, "*"), 1794 ("sub", operator.sub, "-"), 1795 ("div", operator.truediv if util.py3k else operator.div, "/"), 1796 ("mod", operator.mod, "%"), 1797 id_="iaa", 1798 ) 1799 def test_math_op(self, py_op, sql_op): 1800 for (lhs, rhs, res) in ( 1801 (5, self.table1.c.myid, ":myid_1 %s mytable.myid"), 1802 (5, literal(5), ":param_1 %s :param_2"), 1803 (self.table1.c.myid, "b", "mytable.myid %s :myid_1"), 1804 (self.table1.c.myid, literal(2.7), "mytable.myid %s :param_1"), 1805 ( 1806 self.table1.c.myid, 1807 self.table1.c.myid, 1808 "mytable.myid %s mytable.myid", 1809 ), 1810 (literal(5), 8, ":param_1 %s :param_2"), 1811 (literal(6), self.table1.c.myid, ":param_1 %s mytable.myid"), 1812 (literal(7), literal(5.5), ":param_1 %s :param_2"), 1813 ): 1814 self.assert_compile(py_op(lhs, rhs), res % sql_op) 1815 1816 1817class ComparisonOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL): 1818 __dialect__ = "default" 1819 1820 table1 = table("mytable", column("myid", Integer)) 1821 1822 def test_pickle_operators_one(self): 1823 clause = ( 1824 (self.table1.c.myid == 12) 1825 & self.table1.c.myid.between(15, 20) 1826 & self.table1.c.myid.like("hoho") 1827 ) 1828 eq_(str(clause), str(util.pickle.loads(util.pickle.dumps(clause)))) 1829 1830 def test_pickle_operators_two(self): 1831 clause = tuple_(1, 2, 3) 1832 eq_(str(clause), str(util.pickle.loads(util.pickle.dumps(clause)))) 1833 1834 @testing.combinations( 1835 (operator.lt, "<", ">"), 1836 (operator.gt, ">", "<"), 1837 (operator.eq, "=", "="), 1838 (operator.ne, "!=", "!="), 1839 (operator.le, "<=", ">="), 1840 (operator.ge, ">=", "<="), 1841 id_="naa", 1842 ) 1843 def test_comparison_op(self, py_op, fwd_op, rev_op): 1844 dt = datetime.datetime(2012, 5, 10, 15, 27, 18) 1845 for (lhs, rhs, l_sql, r_sql) in ( 1846 ("a", self.table1.c.myid, ":myid_1", "mytable.myid"), 1847 ("a", literal("b"), ":param_2", ":param_1"), # note swap! 1848 (self.table1.c.myid, "b", "mytable.myid", ":myid_1"), 1849 (self.table1.c.myid, literal("b"), "mytable.myid", ":param_1"), 1850 ( 1851 self.table1.c.myid, 1852 self.table1.c.myid, 1853 "mytable.myid", 1854 "mytable.myid", 1855 ), 1856 (literal("a"), "b", ":param_1", ":param_2"), 1857 (literal("a"), self.table1.c.myid, ":param_1", "mytable.myid"), 1858 (literal("a"), literal("b"), ":param_1", ":param_2"), 1859 (dt, literal("b"), ":param_2", ":param_1"), 1860 (literal("b"), dt, ":param_1", ":param_2"), 1861 ): 1862 1863 # the compiled clause should match either (e.g.): 1864 # 'a' < 'b' -or- 'b' > 'a'. 1865 compiled = str(py_op(lhs, rhs)) 1866 fwd_sql = "%s %s %s" % (l_sql, fwd_op, r_sql) 1867 rev_sql = "%s %s %s" % (r_sql, rev_op, l_sql) 1868 1869 self.assert_( 1870 compiled == fwd_sql or compiled == rev_sql, 1871 "\n'" 1872 + compiled 1873 + "'\n does not match\n'" 1874 + fwd_sql 1875 + "'\n or\n'" 1876 + rev_sql 1877 + "'", 1878 ) 1879 1880 1881class NonZeroTest(fixtures.TestBase): 1882 def _raises(self, expr): 1883 assert_raises_message( 1884 TypeError, 1885 "Boolean value of this clause is not defined", 1886 bool, 1887 expr, 1888 ) 1889 1890 def _assert_true(self, expr): 1891 is_(bool(expr), True) 1892 1893 def _assert_false(self, expr): 1894 is_(bool(expr), False) 1895 1896 def test_column_identity_eq(self): 1897 c1 = column("c1") 1898 self._assert_true(c1 == c1) 1899 1900 def test_column_identity_gt(self): 1901 c1 = column("c1") 1902 self._raises(c1 > c1) 1903 1904 def test_column_compare_eq(self): 1905 c1, c2 = column("c1"), column("c2") 1906 self._assert_false(c1 == c2) 1907 1908 def test_column_compare_gt(self): 1909 c1, c2 = column("c1"), column("c2") 1910 self._raises(c1 > c2) 1911 1912 def test_binary_identity_eq(self): 1913 c1 = column("c1") 1914 expr = c1 > 5 1915 self._assert_true(expr == expr) 1916 1917 def test_labeled_binary_identity_eq(self): 1918 c1 = column("c1") 1919 expr = (c1 > 5).label(None) 1920 self._assert_true(expr == expr) 1921 1922 def test_annotated_binary_identity_eq(self): 1923 c1 = column("c1") 1924 expr1 = c1 > 5 1925 expr2 = expr1._annotate({"foo": "bar"}) 1926 self._assert_true(expr1 == expr2) 1927 1928 def test_labeled_binary_compare_gt(self): 1929 c1 = column("c1") 1930 expr1 = (c1 > 5).label(None) 1931 expr2 = (c1 > 5).label(None) 1932 self._assert_false(expr1 == expr2) 1933 1934 1935class NegationTest(fixtures.TestBase, testing.AssertsCompiledSQL): 1936 __dialect__ = "default" 1937 1938 table1 = table("mytable", column("myid", Integer), column("name", String)) 1939 1940 def test_negate_operators_1(self): 1941 for (py_op, op) in ((operator.neg, "-"), (operator.inv, "NOT ")): 1942 for expr, expected in ( 1943 (self.table1.c.myid, "mytable.myid"), 1944 (literal("foo"), ":param_1"), 1945 ): 1946 self.assert_compile(py_op(expr), "%s%s" % (op, expected)) 1947 1948 def test_negate_operators_2(self): 1949 self.assert_compile( 1950 self.table1.select( 1951 (self.table1.c.myid != 12) & ~(self.table1.c.name == "john") 1952 ), 1953 "SELECT mytable.myid, mytable.name FROM " 1954 "mytable WHERE mytable.myid != :myid_1 " 1955 "AND mytable.name != :name_1", 1956 ) 1957 1958 def test_negate_operators_3(self): 1959 self.assert_compile( 1960 self.table1.select( 1961 (self.table1.c.myid != 12) 1962 & ~(self.table1.c.name.between("jack", "john")) 1963 ), 1964 "SELECT mytable.myid, mytable.name FROM " 1965 "mytable WHERE mytable.myid != :myid_1 AND " 1966 "mytable.name NOT BETWEEN :name_1 AND :name_2", 1967 ) 1968 1969 def test_negate_operators_4(self): 1970 self.assert_compile( 1971 self.table1.select( 1972 (self.table1.c.myid != 12) 1973 & ~and_( 1974 self.table1.c.name == "john", 1975 self.table1.c.name == "ed", 1976 self.table1.c.name == "fred", 1977 ) 1978 ), 1979 "SELECT mytable.myid, mytable.name FROM " 1980 "mytable WHERE mytable.myid != :myid_1 AND " 1981 "NOT (mytable.name = :name_1 AND mytable.name = :name_2 " 1982 "AND mytable.name = :name_3)", 1983 ) 1984 1985 def test_negate_operators_5(self): 1986 self.assert_compile( 1987 self.table1.select( 1988 (self.table1.c.myid != 12) & ~self.table1.c.name 1989 ), 1990 "SELECT mytable.myid, mytable.name FROM " 1991 "mytable WHERE mytable.myid != :myid_1 AND NOT mytable.name", 1992 ) 1993 1994 def test_negate_operator_type(self): 1995 is_((-self.table1.c.myid).type, self.table1.c.myid.type) 1996 1997 def test_negate_operator_label(self): 1998 orig_expr = or_( 1999 self.table1.c.myid == 1, self.table1.c.myid == 2 2000 ).label("foo") 2001 expr = not_(orig_expr) 2002 isinstance(expr, Label) 2003 eq_(expr.name, "foo") 2004 is_not(expr, orig_expr) 2005 is_(expr._element.operator, operator.inv) # e.g. and not false_ 2006 2007 self.assert_compile( 2008 expr, 2009 "NOT (mytable.myid = :myid_1 OR mytable.myid = :myid_2)", 2010 dialect=default.DefaultDialect(supports_native_boolean=False), 2011 ) 2012 2013 def test_negate_operator_self_group(self): 2014 orig_expr = or_( 2015 self.table1.c.myid == 1, self.table1.c.myid == 2 2016 ).self_group() 2017 expr = not_(orig_expr) 2018 is_not(expr, orig_expr) 2019 2020 self.assert_compile( 2021 expr, 2022 "NOT (mytable.myid = :myid_1 OR mytable.myid = :myid_2)", 2023 dialect=default.DefaultDialect(supports_native_boolean=False), 2024 ) 2025 2026 def test_implicitly_boolean(self): 2027 # test for expressions that the database always considers as boolean 2028 # even if there is no boolean datatype. 2029 assert not self.table1.c.myid._is_implicitly_boolean 2030 assert (self.table1.c.myid == 5)._is_implicitly_boolean 2031 assert (self.table1.c.myid == 5).self_group()._is_implicitly_boolean 2032 assert (self.table1.c.myid == 5).label("x")._is_implicitly_boolean 2033 assert not_(self.table1.c.myid == 5)._is_implicitly_boolean 2034 assert or_( 2035 self.table1.c.myid == 5, self.table1.c.myid == 7 2036 )._is_implicitly_boolean 2037 assert not column("x", Boolean)._is_implicitly_boolean 2038 assert not (self.table1.c.myid + 5)._is_implicitly_boolean 2039 assert not not_(column("x", Boolean))._is_implicitly_boolean 2040 assert ( 2041 not select([self.table1.c.myid]).as_scalar()._is_implicitly_boolean 2042 ) 2043 assert not text("x = y")._is_implicitly_boolean 2044 assert not literal_column("x = y")._is_implicitly_boolean 2045 2046 2047class LikeTest(fixtures.TestBase, testing.AssertsCompiledSQL): 2048 __dialect__ = "default" 2049 2050 table1 = table("mytable", column("myid", Integer), column("name", String)) 2051 2052 def test_like_1(self): 2053 self.assert_compile( 2054 self.table1.c.myid.like("somstr"), "mytable.myid LIKE :myid_1" 2055 ) 2056 2057 def test_like_2(self): 2058 self.assert_compile( 2059 ~self.table1.c.myid.like("somstr"), "mytable.myid NOT LIKE :myid_1" 2060 ) 2061 2062 def test_like_3(self): 2063 self.assert_compile( 2064 self.table1.c.myid.like("somstr", escape="\\"), 2065 "mytable.myid LIKE :myid_1 ESCAPE '\\'", 2066 ) 2067 2068 def test_like_4(self): 2069 self.assert_compile( 2070 ~self.table1.c.myid.like("somstr", escape="\\"), 2071 "mytable.myid NOT LIKE :myid_1 ESCAPE '\\'", 2072 ) 2073 2074 def test_like_5(self): 2075 self.assert_compile( 2076 self.table1.c.myid.ilike("somstr", escape="\\"), 2077 "lower(mytable.myid) LIKE lower(:myid_1) ESCAPE '\\'", 2078 ) 2079 2080 def test_like_6(self): 2081 self.assert_compile( 2082 ~self.table1.c.myid.ilike("somstr", escape="\\"), 2083 "lower(mytable.myid) NOT LIKE lower(:myid_1) ESCAPE '\\'", 2084 ) 2085 2086 def test_like_7(self): 2087 self.assert_compile( 2088 self.table1.c.myid.ilike("somstr", escape="\\"), 2089 "mytable.myid ILIKE %(myid_1)s ESCAPE '\\\\'", 2090 dialect=postgresql.dialect(), 2091 ) 2092 2093 def test_like_8(self): 2094 self.assert_compile( 2095 ~self.table1.c.myid.ilike("somstr", escape="\\"), 2096 "mytable.myid NOT ILIKE %(myid_1)s ESCAPE '\\\\'", 2097 dialect=postgresql.dialect(), 2098 ) 2099 2100 def test_like_9(self): 2101 self.assert_compile( 2102 self.table1.c.name.ilike("%something%"), 2103 "lower(mytable.name) LIKE lower(:name_1)", 2104 ) 2105 2106 def test_like_10(self): 2107 self.assert_compile( 2108 self.table1.c.name.ilike("%something%"), 2109 "mytable.name ILIKE %(name_1)s", 2110 dialect=postgresql.dialect(), 2111 ) 2112 2113 def test_like_11(self): 2114 self.assert_compile( 2115 ~self.table1.c.name.ilike("%something%"), 2116 "lower(mytable.name) NOT LIKE lower(:name_1)", 2117 ) 2118 2119 def test_like_12(self): 2120 self.assert_compile( 2121 ~self.table1.c.name.ilike("%something%"), 2122 "mytable.name NOT ILIKE %(name_1)s", 2123 dialect=postgresql.dialect(), 2124 ) 2125 2126 2127class BetweenTest(fixtures.TestBase, testing.AssertsCompiledSQL): 2128 __dialect__ = "default" 2129 2130 table1 = table("mytable", column("myid", Integer), column("name", String)) 2131 2132 def test_between_1(self): 2133 self.assert_compile( 2134 self.table1.c.myid.between(1, 2), 2135 "mytable.myid BETWEEN :myid_1 AND :myid_2", 2136 ) 2137 2138 def test_between_2(self): 2139 self.assert_compile( 2140 ~self.table1.c.myid.between(1, 2), 2141 "mytable.myid NOT BETWEEN :myid_1 AND :myid_2", 2142 ) 2143 2144 def test_between_3(self): 2145 self.assert_compile( 2146 self.table1.c.myid.between(1, 2, symmetric=True), 2147 "mytable.myid BETWEEN SYMMETRIC :myid_1 AND :myid_2", 2148 ) 2149 2150 def test_between_4(self): 2151 self.assert_compile( 2152 ~self.table1.c.myid.between(1, 2, symmetric=True), 2153 "mytable.myid NOT BETWEEN SYMMETRIC :myid_1 AND :myid_2", 2154 ) 2155 2156 def test_between_5(self): 2157 self.assert_compile( 2158 between(self.table1.c.myid, 1, 2, symmetric=True), 2159 "mytable.myid BETWEEN SYMMETRIC :myid_1 AND :myid_2", 2160 ) 2161 2162 def test_between_6(self): 2163 self.assert_compile( 2164 ~between(self.table1.c.myid, 1, 2, symmetric=True), 2165 "mytable.myid NOT BETWEEN SYMMETRIC :myid_1 AND :myid_2", 2166 ) 2167 2168 2169class MatchTest(fixtures.TestBase, testing.AssertsCompiledSQL): 2170 __dialect__ = "default" 2171 2172 table1 = table("mytable", column("myid", Integer), column("name", String)) 2173 2174 def test_match_1(self): 2175 self.assert_compile( 2176 self.table1.c.myid.match("somstr"), 2177 "mytable.myid MATCH ?", 2178 dialect=sqlite.dialect(), 2179 ) 2180 2181 def test_match_2(self): 2182 self.assert_compile( 2183 self.table1.c.myid.match("somstr"), 2184 "MATCH (mytable.myid) AGAINST (%s IN BOOLEAN MODE)", 2185 dialect=mysql.dialect(), 2186 ) 2187 2188 def test_match_3(self): 2189 self.assert_compile( 2190 self.table1.c.myid.match("somstr"), 2191 "CONTAINS (mytable.myid, :myid_1)", 2192 dialect=mssql.dialect(), 2193 ) 2194 2195 def test_match_4(self): 2196 self.assert_compile( 2197 self.table1.c.myid.match("somstr"), 2198 "mytable.myid @@ to_tsquery(%(myid_1)s)", 2199 dialect=postgresql.dialect(), 2200 ) 2201 2202 def test_match_5(self): 2203 self.assert_compile( 2204 self.table1.c.myid.match("somstr"), 2205 "CONTAINS (mytable.myid, :myid_1)", 2206 dialect=oracle.dialect(), 2207 ) 2208 2209 def test_match_is_now_matchtype(self): 2210 expr = self.table1.c.myid.match("somstr") 2211 assert expr.type._type_affinity is MatchType()._type_affinity 2212 assert isinstance(expr.type, MatchType) 2213 2214 def test_boolean_inversion_postgresql(self): 2215 self.assert_compile( 2216 ~self.table1.c.myid.match("somstr"), 2217 "NOT mytable.myid @@ to_tsquery(%(myid_1)s)", 2218 dialect=postgresql.dialect(), 2219 ) 2220 2221 def test_boolean_inversion_mysql(self): 2222 # because mysql doesnt have native boolean 2223 self.assert_compile( 2224 ~self.table1.c.myid.match("somstr"), 2225 "NOT MATCH (mytable.myid) AGAINST (%s IN BOOLEAN MODE)", 2226 dialect=mysql.dialect(), 2227 ) 2228 2229 def test_boolean_inversion_mssql(self): 2230 # because mssql doesnt have native boolean 2231 self.assert_compile( 2232 ~self.table1.c.myid.match("somstr"), 2233 "NOT CONTAINS (mytable.myid, :myid_1)", 2234 dialect=mssql.dialect(), 2235 ) 2236 2237 2238class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): 2239 __dialect__ = "default" 2240 2241 def test_contains(self): 2242 self.assert_compile( 2243 column("x").contains("y"), 2244 "x LIKE '%' || :x_1 || '%'", 2245 checkparams={"x_1": "y"}, 2246 ) 2247 2248 def test_contains_escape(self): 2249 self.assert_compile( 2250 column("x").contains("a%b_c", escape="\\"), 2251 "x LIKE '%' || :x_1 || '%' ESCAPE '\\'", 2252 checkparams={"x_1": "a%b_c"}, 2253 ) 2254 2255 def test_contains_autoescape(self): 2256 self.assert_compile( 2257 column("x").contains("a%b_c/d", autoescape=True), 2258 "x LIKE '%' || :x_1 || '%' ESCAPE '/'", 2259 checkparams={"x_1": "a/%b/_c//d"}, 2260 ) 2261 2262 def test_contains_literal(self): 2263 self.assert_compile( 2264 column("x").contains(literal_column("y")), 2265 "x LIKE '%' || y || '%'", 2266 checkparams={}, 2267 ) 2268 2269 def test_contains_text(self): 2270 self.assert_compile( 2271 column("x").contains(text("y")), 2272 "x LIKE '%' || y || '%'", 2273 checkparams={}, 2274 ) 2275 2276 def test_not_contains(self): 2277 self.assert_compile( 2278 ~column("x").contains("y"), 2279 "x NOT LIKE '%' || :x_1 || '%'", 2280 checkparams={"x_1": "y"}, 2281 ) 2282 2283 def test_not_contains_escape(self): 2284 self.assert_compile( 2285 ~column("x").contains("a%b_c", escape="\\"), 2286 "x NOT LIKE '%' || :x_1 || '%' ESCAPE '\\'", 2287 checkparams={"x_1": "a%b_c"}, 2288 ) 2289 2290 def test_not_contains_autoescape(self): 2291 self.assert_compile( 2292 ~column("x").contains("a%b_c/d", autoescape=True), 2293 "x NOT LIKE '%' || :x_1 || '%' ESCAPE '/'", 2294 checkparams={"x_1": "a/%b/_c//d"}, 2295 ) 2296 2297 def test_contains_concat(self): 2298 self.assert_compile( 2299 column("x").contains("y"), 2300 "x LIKE concat(concat('%%', %s), '%%')", 2301 checkparams={"x_1": "y"}, 2302 dialect=mysql.dialect(), 2303 ) 2304 2305 def test_not_contains_concat(self): 2306 self.assert_compile( 2307 ~column("x").contains("y"), 2308 "x NOT LIKE concat(concat('%%', %s), '%%')", 2309 checkparams={"x_1": "y"}, 2310 dialect=mysql.dialect(), 2311 ) 2312 2313 def test_contains_literal_concat(self): 2314 self.assert_compile( 2315 column("x").contains(literal_column("y")), 2316 "x LIKE concat(concat('%%', y), '%%')", 2317 checkparams={}, 2318 dialect=mysql.dialect(), 2319 ) 2320 2321 def test_contains_text_concat(self): 2322 self.assert_compile( 2323 column("x").contains(text("y")), 2324 "x LIKE concat(concat('%%', y), '%%')", 2325 checkparams={}, 2326 dialect=mysql.dialect(), 2327 ) 2328 2329 def test_like(self): 2330 self.assert_compile( 2331 column("x").like("y"), "x LIKE :x_1", checkparams={"x_1": "y"} 2332 ) 2333 2334 def test_like_escape(self): 2335 self.assert_compile( 2336 column("x").like("a%b_c", escape="\\"), 2337 "x LIKE :x_1 ESCAPE '\\'", 2338 checkparams={"x_1": "a%b_c"}, 2339 ) 2340 2341 def test_ilike(self): 2342 self.assert_compile( 2343 column("x").ilike("y"), 2344 "lower(x) LIKE lower(:x_1)", 2345 checkparams={"x_1": "y"}, 2346 ) 2347 2348 def test_ilike_escape(self): 2349 self.assert_compile( 2350 column("x").ilike("a%b_c", escape="\\"), 2351 "lower(x) LIKE lower(:x_1) ESCAPE '\\'", 2352 checkparams={"x_1": "a%b_c"}, 2353 ) 2354 2355 def test_notlike(self): 2356 self.assert_compile( 2357 column("x").notlike("y"), 2358 "x NOT LIKE :x_1", 2359 checkparams={"x_1": "y"}, 2360 ) 2361 2362 def test_notlike_escape(self): 2363 self.assert_compile( 2364 column("x").notlike("a%b_c", escape="\\"), 2365 "x NOT LIKE :x_1 ESCAPE '\\'", 2366 checkparams={"x_1": "a%b_c"}, 2367 ) 2368 2369 def test_notilike(self): 2370 self.assert_compile( 2371 column("x").notilike("y"), 2372 "lower(x) NOT LIKE lower(:x_1)", 2373 checkparams={"x_1": "y"}, 2374 ) 2375 2376 def test_notilike_escape(self): 2377 self.assert_compile( 2378 column("x").notilike("a%b_c", escape="\\"), 2379 "lower(x) NOT LIKE lower(:x_1) ESCAPE '\\'", 2380 checkparams={"x_1": "a%b_c"}, 2381 ) 2382 2383 def test_startswith(self): 2384 self.assert_compile( 2385 column("x").startswith("y"), 2386 "x LIKE :x_1 || '%'", 2387 checkparams={"x_1": "y"}, 2388 ) 2389 2390 def test_startswith_escape(self): 2391 self.assert_compile( 2392 column("x").startswith("a%b_c", escape="\\"), 2393 "x LIKE :x_1 || '%' ESCAPE '\\'", 2394 checkparams={"x_1": "a%b_c"}, 2395 ) 2396 2397 def test_startswith_autoescape(self): 2398 self.assert_compile( 2399 column("x").startswith("a%b_c/d", autoescape=True), 2400 "x LIKE :x_1 || '%' ESCAPE '/'", 2401 checkparams={"x_1": "a/%b/_c//d"}, 2402 ) 2403 2404 def test_startswith_autoescape_custom_escape(self): 2405 self.assert_compile( 2406 column("x").startswith("a%b_c/d^e", autoescape=True, escape="^"), 2407 "x LIKE :x_1 || '%' ESCAPE '^'", 2408 checkparams={"x_1": "a^%b^_c/d^^e"}, 2409 ) 2410 2411 def test_not_startswith(self): 2412 self.assert_compile( 2413 ~column("x").startswith("y"), 2414 "x NOT LIKE :x_1 || '%'", 2415 checkparams={"x_1": "y"}, 2416 ) 2417 2418 def test_not_startswith_escape(self): 2419 self.assert_compile( 2420 ~column("x").startswith("a%b_c", escape="\\"), 2421 "x NOT LIKE :x_1 || '%' ESCAPE '\\'", 2422 checkparams={"x_1": "a%b_c"}, 2423 ) 2424 2425 def test_not_startswith_autoescape(self): 2426 self.assert_compile( 2427 ~column("x").startswith("a%b_c/d", autoescape=True), 2428 "x NOT LIKE :x_1 || '%' ESCAPE '/'", 2429 checkparams={"x_1": "a/%b/_c//d"}, 2430 ) 2431 2432 def test_startswith_literal(self): 2433 self.assert_compile( 2434 column("x").startswith(literal_column("y")), 2435 "x LIKE y || '%'", 2436 checkparams={}, 2437 ) 2438 2439 def test_startswith_text(self): 2440 self.assert_compile( 2441 column("x").startswith(text("y")), 2442 "x LIKE y || '%'", 2443 checkparams={}, 2444 ) 2445 2446 def test_startswith_concat(self): 2447 self.assert_compile( 2448 column("x").startswith("y"), 2449 "x LIKE concat(%s, '%%')", 2450 checkparams={"x_1": "y"}, 2451 dialect=mysql.dialect(), 2452 ) 2453 2454 def test_not_startswith_concat(self): 2455 self.assert_compile( 2456 ~column("x").startswith("y"), 2457 "x NOT LIKE concat(%s, '%%')", 2458 checkparams={"x_1": "y"}, 2459 dialect=mysql.dialect(), 2460 ) 2461 2462 def test_startswith_firebird(self): 2463 self.assert_compile( 2464 column("x").startswith("y"), 2465 "x STARTING WITH :x_1", 2466 checkparams={"x_1": "y"}, 2467 dialect=firebird.dialect(), 2468 ) 2469 2470 def test_not_startswith_firebird(self): 2471 self.assert_compile( 2472 ~column("x").startswith("y"), 2473 "x NOT STARTING WITH :x_1", 2474 checkparams={"x_1": "y"}, 2475 dialect=firebird.dialect(), 2476 ) 2477 2478 def test_startswith_literal_mysql(self): 2479 self.assert_compile( 2480 column("x").startswith(literal_column("y")), 2481 "x LIKE concat(y, '%%')", 2482 checkparams={}, 2483 dialect=mysql.dialect(), 2484 ) 2485 2486 def test_startswith_text_mysql(self): 2487 self.assert_compile( 2488 column("x").startswith(text("y")), 2489 "x LIKE concat(y, '%%')", 2490 checkparams={}, 2491 dialect=mysql.dialect(), 2492 ) 2493 2494 def test_endswith(self): 2495 self.assert_compile( 2496 column("x").endswith("y"), 2497 "x LIKE '%' || :x_1", 2498 checkparams={"x_1": "y"}, 2499 ) 2500 2501 def test_endswith_escape(self): 2502 self.assert_compile( 2503 column("x").endswith("a%b_c", escape="\\"), 2504 "x LIKE '%' || :x_1 ESCAPE '\\'", 2505 checkparams={"x_1": "a%b_c"}, 2506 ) 2507 2508 def test_endswith_autoescape(self): 2509 self.assert_compile( 2510 column("x").endswith("a%b_c/d", autoescape=True), 2511 "x LIKE '%' || :x_1 ESCAPE '/'", 2512 checkparams={"x_1": "a/%b/_c//d"}, 2513 ) 2514 2515 def test_endswith_autoescape_custom_escape(self): 2516 self.assert_compile( 2517 column("x").endswith("a%b_c/d^e", autoescape=True, escape="^"), 2518 "x LIKE '%' || :x_1 ESCAPE '^'", 2519 checkparams={"x_1": "a^%b^_c/d^^e"}, 2520 ) 2521 2522 def test_endswith_autoescape_warning(self): 2523 with expect_warnings("The autoescape parameter is now a simple"): 2524 self.assert_compile( 2525 column("x").endswith("a%b_c/d", autoescape="P"), 2526 "x LIKE '%' || :x_1 ESCAPE '/'", 2527 checkparams={"x_1": "a/%b/_c//d"}, 2528 ) 2529 2530 def test_endswith_autoescape_nosqlexpr(self): 2531 assert_raises_message( 2532 TypeError, 2533 "String value expected when autoescape=True", 2534 column("x").endswith, 2535 literal_column("'a%b_c/d'"), 2536 autoescape=True, 2537 ) 2538 2539 def test_not_endswith(self): 2540 self.assert_compile( 2541 ~column("x").endswith("y"), 2542 "x NOT LIKE '%' || :x_1", 2543 checkparams={"x_1": "y"}, 2544 ) 2545 2546 def test_not_endswith_escape(self): 2547 self.assert_compile( 2548 ~column("x").endswith("a%b_c", escape="\\"), 2549 "x NOT LIKE '%' || :x_1 ESCAPE '\\'", 2550 checkparams={"x_1": "a%b_c"}, 2551 ) 2552 2553 def test_not_endswith_autoescape(self): 2554 self.assert_compile( 2555 ~column("x").endswith("a%b_c/d", autoescape=True), 2556 "x NOT LIKE '%' || :x_1 ESCAPE '/'", 2557 checkparams={"x_1": "a/%b/_c//d"}, 2558 ) 2559 2560 def test_endswith_literal(self): 2561 self.assert_compile( 2562 column("x").endswith(literal_column("y")), 2563 "x LIKE '%' || y", 2564 checkparams={}, 2565 ) 2566 2567 def test_endswith_text(self): 2568 self.assert_compile( 2569 column("x").endswith(text("y")), "x LIKE '%' || y", checkparams={} 2570 ) 2571 2572 def test_endswith_mysql(self): 2573 self.assert_compile( 2574 column("x").endswith("y"), 2575 "x LIKE concat('%%', %s)", 2576 checkparams={"x_1": "y"}, 2577 dialect=mysql.dialect(), 2578 ) 2579 2580 def test_not_endswith_mysql(self): 2581 self.assert_compile( 2582 ~column("x").endswith("y"), 2583 "x NOT LIKE concat('%%', %s)", 2584 checkparams={"x_1": "y"}, 2585 dialect=mysql.dialect(), 2586 ) 2587 2588 def test_endswith_literal_mysql(self): 2589 self.assert_compile( 2590 column("x").endswith(literal_column("y")), 2591 "x LIKE concat('%%', y)", 2592 checkparams={}, 2593 dialect=mysql.dialect(), 2594 ) 2595 2596 def test_endswith_text_mysql(self): 2597 self.assert_compile( 2598 column("x").endswith(text("y")), 2599 "x LIKE concat('%%', y)", 2600 checkparams={}, 2601 dialect=mysql.dialect(), 2602 ) 2603 2604 2605class CustomOpTest(fixtures.TestBase): 2606 def test_is_comparison(self): 2607 c = column("x") 2608 c2 = column("y") 2609 op1 = c.op("$", is_comparison=True)(c2).operator 2610 op2 = c.op("$", is_comparison=False)(c2).operator 2611 2612 assert operators.is_comparison(op1) 2613 assert not operators.is_comparison(op2) 2614 2615 @testing.combinations( 2616 (sqltypes.NULLTYPE,), 2617 (Integer(),), 2618 (ARRAY(String),), 2619 (String(50),), 2620 (Boolean(),), 2621 (DateTime(),), 2622 (sqltypes.JSON(),), 2623 (postgresql.ARRAY(Integer),), 2624 (sqltypes.Numeric(5, 2),), 2625 id_="r", 2626 ) 2627 def test_return_types(self, typ): 2628 some_return_type = sqltypes.DECIMAL() 2629 2630 c = column("x", typ) 2631 expr = c.op("$", is_comparison=True)(None) 2632 is_(expr.type, sqltypes.BOOLEANTYPE) 2633 2634 c = column("x", typ) 2635 expr = c.bool_op("$")(None) 2636 is_(expr.type, sqltypes.BOOLEANTYPE) 2637 2638 expr = c.op("$")(None) 2639 is_(expr.type, typ) 2640 2641 expr = c.op("$", return_type=some_return_type)(None) 2642 is_(expr.type, some_return_type) 2643 2644 expr = c.op("$", is_comparison=True, return_type=some_return_type)( 2645 None 2646 ) 2647 is_(expr.type, some_return_type) 2648 2649 2650class TupleTypingTest(fixtures.TestBase): 2651 def _assert_types(self, expr): 2652 eq_(expr.clauses[0].type._type_affinity, Integer) 2653 eq_(expr.clauses[1].type._type_affinity, String) 2654 eq_(expr.clauses[2].type._type_affinity, LargeBinary()._type_affinity) 2655 2656 def test_type_coercion_on_eq(self): 2657 a, b, c = ( 2658 column("a", Integer), 2659 column("b", String), 2660 column("c", LargeBinary), 2661 ) 2662 t1 = tuple_(a, b, c) 2663 expr = t1 == (3, "hi", "there") 2664 self._assert_types(expr.right) 2665 2666 def test_type_coercion_on_in(self): 2667 a, b, c = ( 2668 column("a", Integer), 2669 column("b", String), 2670 column("c", LargeBinary), 2671 ) 2672 t1 = tuple_(a, b, c) 2673 expr = t1.in_([(3, "hi", "there"), (4, "Q", "P")]) 2674 eq_(len(expr.right.clauses), 2) 2675 for elem in expr.right.clauses: 2676 self._assert_types(elem) 2677 2678 2679class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL): 2680 __dialect__ = "default" 2681 2682 @testing.fixture 2683 def t_fixture(self): 2684 m = MetaData() 2685 2686 t = Table( 2687 "tab1", 2688 m, 2689 Column("arrval", ARRAY(Integer)), 2690 Column("data", Integer), 2691 ) 2692 return t 2693 2694 def test_any_array(self, t_fixture): 2695 t = t_fixture 2696 2697 self.assert_compile( 2698 5 == any_(t.c.arrval), 2699 ":param_1 = ANY (tab1.arrval)", 2700 checkparams={"param_1": 5}, 2701 ) 2702 2703 def test_any_array_method(self, t_fixture): 2704 t = t_fixture 2705 2706 self.assert_compile( 2707 5 == t.c.arrval.any_(), 2708 ":param_1 = ANY (tab1.arrval)", 2709 checkparams={"param_1": 5}, 2710 ) 2711 2712 def test_all_array(self, t_fixture): 2713 t = t_fixture 2714 2715 self.assert_compile( 2716 5 == all_(t.c.arrval), 2717 ":param_1 = ALL (tab1.arrval)", 2718 checkparams={"param_1": 5}, 2719 ) 2720 2721 def test_all_array_method(self, t_fixture): 2722 t = t_fixture 2723 2724 self.assert_compile( 2725 5 == t.c.arrval.all_(), 2726 ":param_1 = ALL (tab1.arrval)", 2727 checkparams={"param_1": 5}, 2728 ) 2729 2730 def test_any_comparator_array(self, t_fixture): 2731 t = t_fixture 2732 2733 self.assert_compile( 2734 5 > any_(t.c.arrval), 2735 ":param_1 > ANY (tab1.arrval)", 2736 checkparams={"param_1": 5}, 2737 ) 2738 2739 def test_all_comparator_array(self, t_fixture): 2740 t = t_fixture 2741 2742 self.assert_compile( 2743 5 > all_(t.c.arrval), 2744 ":param_1 > ALL (tab1.arrval)", 2745 checkparams={"param_1": 5}, 2746 ) 2747 2748 def test_any_comparator_array_wexpr(self, t_fixture): 2749 t = t_fixture 2750 2751 self.assert_compile( 2752 t.c.data > any_(t.c.arrval), 2753 "tab1.data > ANY (tab1.arrval)", 2754 checkparams={}, 2755 ) 2756 2757 def test_all_comparator_array_wexpr(self, t_fixture): 2758 t = t_fixture 2759 2760 self.assert_compile( 2761 t.c.data > all_(t.c.arrval), 2762 "tab1.data > ALL (tab1.arrval)", 2763 checkparams={}, 2764 ) 2765 2766 def test_illegal_ops(self, t_fixture): 2767 t = t_fixture 2768 2769 assert_raises_message( 2770 exc.ArgumentError, 2771 "Only comparison operators may be used with ANY/ALL", 2772 lambda: 5 + all_(t.c.arrval), 2773 ) 2774 2775 # TODO: 2776 # this is invalid but doesn't raise an error, 2777 # as the left-hand side just does its thing. Types 2778 # would need to reject their right-hand side. 2779 self.assert_compile( 2780 t.c.data + all_(t.c.arrval), "tab1.data + ALL (tab1.arrval)" 2781 ) 2782 2783 def test_any_array_comparator_accessor(self, t_fixture): 2784 t = t_fixture 2785 2786 self.assert_compile( 2787 t.c.arrval.any(5, operator.gt), 2788 ":param_1 > ANY (tab1.arrval)", 2789 checkparams={"param_1": 5}, 2790 ) 2791 2792 def test_any_array_comparator_negate_accessor(self, t_fixture): 2793 t = t_fixture 2794 2795 self.assert_compile( 2796 ~t.c.arrval.any(5, operator.gt), 2797 "NOT (:param_1 > ANY (tab1.arrval))", 2798 checkparams={"param_1": 5}, 2799 ) 2800 2801 def test_all_array_comparator_accessor(self, t_fixture): 2802 t = t_fixture 2803 2804 self.assert_compile( 2805 t.c.arrval.all(5, operator.gt), 2806 ":param_1 > ALL (tab1.arrval)", 2807 checkparams={"param_1": 5}, 2808 ) 2809 2810 def test_all_array_comparator_negate_accessor(self, t_fixture): 2811 t = t_fixture 2812 2813 self.assert_compile( 2814 ~t.c.arrval.all(5, operator.gt), 2815 "NOT (:param_1 > ALL (tab1.arrval))", 2816 checkparams={"param_1": 5}, 2817 ) 2818 2819 def test_any_array_expression(self, t_fixture): 2820 t = t_fixture 2821 2822 self.assert_compile( 2823 5 == any_(t.c.arrval[5:6] + postgresql.array([3, 4])), 2824 "%(param_1)s = ANY (tab1.arrval[%(arrval_1)s:%(arrval_2)s] || " 2825 "ARRAY[%(param_2)s, %(param_3)s])", 2826 checkparams={ 2827 "arrval_2": 6, 2828 "param_1": 5, 2829 "param_3": 4, 2830 "arrval_1": 5, 2831 "param_2": 3, 2832 }, 2833 dialect="postgresql", 2834 ) 2835 2836 def test_all_array_expression(self, t_fixture): 2837 t = t_fixture 2838 2839 self.assert_compile( 2840 5 == all_(t.c.arrval[5:6] + postgresql.array([3, 4])), 2841 "%(param_1)s = ALL (tab1.arrval[%(arrval_1)s:%(arrval_2)s] || " 2842 "ARRAY[%(param_2)s, %(param_3)s])", 2843 checkparams={ 2844 "arrval_2": 6, 2845 "param_1": 5, 2846 "param_3": 4, 2847 "arrval_1": 5, 2848 "param_2": 3, 2849 }, 2850 dialect="postgresql", 2851 ) 2852 2853 def test_any_subq(self, t_fixture): 2854 t = t_fixture 2855 2856 self.assert_compile( 2857 5 == any_(select([t.c.data]).where(t.c.data < 10)), 2858 ":param_1 = ANY (SELECT tab1.data " 2859 "FROM tab1 WHERE tab1.data < :data_1)", 2860 checkparams={"data_1": 10, "param_1": 5}, 2861 ) 2862 2863 def test_any_subq_method(self, t_fixture): 2864 t = t_fixture 2865 2866 self.assert_compile( 2867 5 == select([t.c.data]).where(t.c.data < 10).as_scalar().any_(), 2868 ":param_1 = ANY (SELECT tab1.data " 2869 "FROM tab1 WHERE tab1.data < :data_1)", 2870 checkparams={"data_1": 10, "param_1": 5}, 2871 ) 2872 2873 def test_all_subq(self, t_fixture): 2874 t = t_fixture 2875 2876 self.assert_compile( 2877 5 == all_(select([t.c.data]).where(t.c.data < 10)), 2878 ":param_1 = ALL (SELECT tab1.data " 2879 "FROM tab1 WHERE tab1.data < :data_1)", 2880 checkparams={"data_1": 10, "param_1": 5}, 2881 ) 2882 2883 def test_all_subq_method(self, t_fixture): 2884 t = t_fixture 2885 2886 self.assert_compile( 2887 5 == select([t.c.data]).where(t.c.data < 10).as_scalar().all_(), 2888 ":param_1 = ALL (SELECT tab1.data " 2889 "FROM tab1 WHERE tab1.data < :data_1)", 2890 checkparams={"data_1": 10, "param_1": 5}, 2891 ) 2892