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