summaryrefslogtreecommitdiff
path: root/test/sql/test_operators.py
Commit message (Collapse)AuthorAgeFilesLines
* Ensure custom ops have consistent typing behavior, boolean supportMike Bayer2017-09-011-2/+31
| | | | | | | | | | | | | | Refined the behavior of :meth:`.Operators.op` such that in all cases, if the :paramref:`.Operators.op.is_comparison` flag is set to True, the return type of the resulting expression will be :class:`.Boolean`, and if the flag is False, the return type of the resulting expression will be the same type as that of the left-hand expression, which is the typical default behavior of other operators. Also added a new parameter :paramref:`.Operators.op.return_type` as well as a helper method :meth:`.Operators.bool_op`. Change-Id: Ifc8553cd4037d741b84b70a9702cbd530f1a9de0 Fixes: #4063
* Flatten operator precedence for comparison operatorsMichael Birtwell2017-05-251-0/+7
| | | | | | | | | | | | | | | The operator precedence for all comparison operators such as LIKE, IS, IN, MATCH, equals, greater than, less than, etc. has all been merged into one level, so that expressions which make use of these against each other will produce parentheses between them. This suits the stated operator precedence of databases like Oracle, MySQL and others which place all of these operators as equal precedence, as well as Postgresql as of 9.5 which has also flattened its operator precendence. Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com> Fixes: #3999 Change-Id: I3f3d5124a64af0d376361cdf15a97e2e703be56f Pull-request: https://github.com/zzzeek/sqlalchemy/pull/367
* Enforce boolean result type for all eq_, is_, isnot, comparisonMike Bayer2017-04-271-0/+10
| | | | | | | | | | | | Repaired issue where the type of an expression that used :meth:`.ColumnOperators.is_` or similar would not be a "boolean" type, instead the type would be "nulltype", as well as when using custom comparison operators against an untyped expression. This typing can impact how the expression behaves in larger contexts as well as in result-row-handling. Change-Id: Ib810ff686de500d8db26ae35a51005fab29603b6 Fixes: #3873
* Add _negate() to Label to negate inner elementMike Bayer2017-04-211-1/+15
| | | | | | | | | Fixed the negation of a :class:`.Label` construct so that the inner element is negated correctly, when the :func:`.not_` modifier is applied to the labeled expression. Change-Id: Ia99917b2959bdfbff28689c962b4203911c57b85 Fixes: #3969
* Add new "expanding" feature to bindparam()Mike Bayer2017-04-071-3/+3
| | | | | | | | | | | | | | | | Added a new kind of :func:`.bindparam` called "expanding". This is for use in ``IN`` expressions where the list of elements is rendered into individual bound parameters at statement execution time, rather than at statement compilation time. This allows both a single bound parameter name to be linked to an IN expression of multiple elements, as well as allows query caching to be used with IN expressions. The new feature allows the related features of "select in" loading and "polymorphic in" loading to make use of the baked query extension to reduce call overhead. This feature should be considered to be **experimental** for 1.2. Fixes: #3953 Change-Id: Ie708414a3ab9c0af29998a2c7f239ff7633b1f6e
* Double percent signs based on paramstyle, not dialectMike Bayer2017-04-051-24/+24
| | | | | | | | | | | | This patch moves the "doubling" of percent signs into the base compiler and makes it completely a product of whether or not the paramstyle is format/pyformat or not. Without this paramstyle, percent signs are not doubled across text(), literal_column(), and column(). Change-Id: Ie2f278ab1dbb94b5078f85c0096d74dbfa049197 Fixes: #3740
* Treat collation names as identifiersMike Bayer2017-03-231-8/+8
| | | | | | | | | | | The expression used for COLLATE as rendered by the column-level :func:`.expression.collate` and :meth:`.ColumnOperators.collate` is now quoted as an identifier when the name is case sensitive, e.g. has uppercase characters. Note that this does not impact type-level collation, which is already quoted. Change-Id: I83d5d9cd1e66a4f20b96303bb84c5f360d5d6a1a Fixes: #3785
* Add "empty in" strategies; default to "static"Mike Bayer2017-03-141-11/+49
| | | | | | | | | | | | | | | The longstanding behavior of the :meth:`.Operators.in_` and :meth:`.Operators.not_in_` operators emitting a warning when the right-hand condition is an empty sequence has been revised; a new flag :paramref:`.create_engine.empty_in_strategy` allows an empty "IN" expression to generate a simple boolean expression, or to invoke the previous behavior of dis-equating the expression to itself, with or without a warning. The default behavior is now to emit the simple boolean expression, allowing an empty IN to be evaulated without any performance penalty. Change-Id: I65cc37f2d7cf65a59bf217136c42fee446929352 Fixes: #3907
* add autoescape option to startswith, endswith, and containsDiana Clarke2017-03-141-13/+111
| | | | | | Fixes: #2694 Change-Id: I34c0bdcb01c2b76b9ab6cd315dae13e3dd8a502b Pull-request: https://github.com/zzzeek/sqlalchemy/pull/207
* Make all tests to be PEP8 compliantKhairi Hafsham2017-02-071-13/+15
| | | | | | | | tested using pycodestyle version 2.2.0 Fixes: #3885 Change-Id: I5df43adc3aefe318f9eeab72a078247a548ec566 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/343
* Add "eager_parenthesis" late-compilation rule, use w/ PG JSON/HSTOREMike Bayer2016-10-011-2/+27
| | | | | | | | | | | | Added compiler-level flags used by Postgresql to place additional parenthesis than would normally be generated by precedence rules around operations involving JSON, HSTORE indexing operators as well as within their operands since it has been observed that Postgresql's precedence rules for at least the HSTORE indexing operator is not consistent between 9.4 and 9.5. Fixes: #3806 Change-Id: I5899677b330595264543b055abd54f3c76bfabf2
* Exclude eq and ne from associative operatorsJohn Passaro2016-09-191-0/+8
| | | | | | | | | | | | The "eq" and "ne" operators are no longer part of the list of "associative" operators, while they remain considered to be "commutative". This allows an expression like ``(x == y) == z`` to be maintained at the SQL level with parenthesis. Pull request courtesy John Passaro. Fixes: #3799 Change-Id: I3759d8987b35649d7418b6524316c9e70c857e68 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/308
* Preserve type for math negationMike Bayer2016-07-011-0/+6
| | | | | | | | | | Fixed issue in SQL math negation operator where the type of the expression would no longer be the numeric type of the original. This would cause issues where the type determined result set behaviors. Change-Id: If0e339614a3686e251235fc94b6f59310c4630a5 Fixes: #3735
* Add IS (NOT) DISTINCT FROM operatorsSebastian Bank2016-06-061-0/+66
| | | | | | | | | | | None / True / False render as literals. For SQLite, "IS" is used as SQLite lacks "IS DISTINCT FROM" but its "IS" operator acts this way for NULL. Doctext-author: Mike Bayer <mike_mp@zzzcomputing.com> Change-Id: I9227b81f7207b42627a0349d14d40b46aa756cce Pull-request: https://github.com/zzzeek/sqlalchemy/pull/248
* - Fixed issue where inadvertent use of the Python ``__contains__``Mike Bayer2016-02-021-1/+23
| | | | | | | | | override with a column expression (e.g. by using ``'x' in col``) would cause an endless loop in the case of an ARRAY type, as Python defers this to ``__getitem__`` access which never raises for this type. Overall, all use of ``__contains__`` now raises NotImplementedError. fixes #3642
* - Added :class:`.mysql.JSON` for MySQL 5.7. The JSON type providesMike Bayer2016-01-061-24/+125
| | | | | | | | | | | | | | | | | | | | | | | | | | persistence of JSON values in MySQL as well as basic operator support of "getitem" and "getpath", making use of the ``JSON_EXTRACT`` function in order to refer to individual paths in a JSON structure. fixes #3547 - Added a new type to core :class:`.types.JSON`. This is the base of the PostgreSQL :class:`.postgresql.JSON` type as well as that of the new :class:`.mysql.JSON` type, so that a PG/MySQL-agnostic JSON column may be used. The type features basic index and path searching support. fixes #3619 - reorganization of migration docs etc. to try to refer both to the fixes to JSON that helps Postgresql while at the same time indicating these are new features of the new base JSON type. - a rework of the Array/Indexable system some more, moving things that are specific to Array out of Indexable. - new operators for JSON indexing added to core so that these can be compiled by the PG and MySQL dialects individually - rename sqltypes.Array to sqltypes.ARRAY - as there is no generic Array implementation, this is an uppercase type for now, consistent with the new sqltypes.JSON type that is also not a generic implementation. There may need to be some convention change to handle the case of datatypes that aren't generic, rely upon DB-native implementations, but aren't necessarily all named the same thing.
* - build out a new base type for Array, as well as new any/all operatorsMike Bayer2015-08-251-2/+154
| | | | | | - any/all work for Array as well as subqueries, accepted by MySQL - Postgresql ARRAY now subclasses Array - fixes #3516
* - as the Concatenable mixin was changed to support calling down toMike Bayer2015-08-181-1/+55
| | | | | | | | | | | | "super" instead of hardcoding to "self.type" for the default return value, the base Comparator was returning other_comparator.type. It's not clear what the rationale for this was, though in theory the base Comparator should possibly even throw an exception if the two types aren't the same (or of the same affinity?) . - mysql.SET was broken on this because the bitwise version adds "0" to the value to force an integer within column_expression, we are doing type_coerces here now in any case so that there is no type ambiguity for this operation
* - merge of ticket_3499 indexed access branchMike Bayer2015-08-171-2/+196
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - The "hashable" flag on special datatypes such as :class:`.postgresql.ARRAY`, :class:`.postgresql.JSON` and :class:`.postgresql.HSTORE` is now set to False, which allows these types to be fetchable in ORM queries that include entities within the row. fixes #3499 - The Postgresql :class:`.postgresql.ARRAY` type now supports multidimensional indexed access, e.g. expressions such as ``somecol[5][6]`` without any need for explicit casts or type coercions, provided that the :paramref:`.postgresql.ARRAY.dimensions` parameter is set to the desired number of dimensions. fixes #3487 - The return type for the :class:`.postgresql.JSON` and :class:`.postgresql.JSONB` when using indexed access has been fixed to work like Postgresql itself, and returns an expression that itself is of type :class:`.postgresql.JSON` or :class:`.postgresql.JSONB`. Previously, the accessor would return :class:`.NullType` which disallowed subsequent JSON-like operators to be used. part of fixes #3503 - The :class:`.postgresql.JSON`, :class:`.postgresql.JSONB` and :class:`.postgresql.HSTORE` datatypes now allow full control over the return type from an indexed textual access operation, either ``column[someindex].astext`` for a JSON type or ``column[someindex]`` for an HSTORE type, via the :paramref:`.postgresql.JSON.astext_type` and :paramref:`.postgresql.HSTORE.text_type` parameters. also part of fixes #3503 - The :attr:`.postgresql.JSON.Comparator.astext` modifier no longer calls upon :meth:`.ColumnElement.cast` implicitly, as PG's JSON/JSONB types allow cross-casting between each other as well. Code that makes use of :meth:`.ColumnElement.cast` on JSON indexed access, e.g. ``col[someindex].cast(Integer)``, will need to be changed to call :attr:`.postgresql.JSON.Comparator.astext` explicitly. This is part of the refactor in references #3503 for consistency in operator use.
* - Fixed bug where coersion of literal ``True`` or ``False`` constantMike Bayer2015-07-191-0/+58
| | | | | | in conjunction with :func:`.and_` or :func:`.or_` would fail with an AttributeError. fixes #3490
* Added test for modulo operator.pr/188Dan Gittik2015-07-171-0/+3
|
* - start trying to move things into __slots__. This seems to reduce theslotsMike Bayer2015-01-041-7/+9
| | | | | size of the many per-column objects we're hitting, but somehow the overall memory is hardly being reduced at all in initial testing
* - Fixed bug where using a :class:`.TypeDecorator` that implementedMike Bayer2014-12-191-0/+25
| | | | | | | a type that was also a :class:`.TypeDecorator` would fail with Python's "Cannot create a consistent method resolution order (MRO)" error, when any kind of SQL comparison expression were used against an object using this type.
* - The :meth:`.Operators.match` operator is now handled such that theMike Bayer2014-12-041-1/+27
| | | | | | | | | | | | | | | | return type is not strictly assumed to be boolean; it now returns a :class:`.Boolean` subclass called :class:`.MatchType`. The type will still produce boolean behavior when used in Python expressions, however the dialect can override its behavior at result time. In the case of MySQL, while the MATCH operator is typically used in a boolean context within an expression, if one actually queries for the value of a match expression, a floating point value is returned; this value is not compatible with SQLAlchemy's C-based boolean processor, so MySQL's result-set behavior now follows that of the :class:`.Float` type. A new operator object ``notmatch_op`` is also added to better allow dialects to define the negation of a match operation. fixes #3263
* - Reversing a change that was made in 0.9, the "singleton" natureMike Bayer2014-10-191-1/+20
| | | | | | | | | of the "constants" :func:`.null`, :func:`.true`, and :func:`.false` has been reverted. These functions returning a "singleton" object had the effect that different instances would be treated as the same regardless of lexical use, which in particular would impact the rendering of the columns clause of a SELECT statement. fixes #3170
* - update the flake8 rules againMike Bayer2014-07-181-242/+333
| | | | - apply autopep8 + manual fixes to most of test/sql/
* - Fixed a bug within the custom operator plus :meth:`.TypeEngine.with_variant`Mike Bayer2014-06-271-1/+30
| | | | | | system, whereby using a :class:`.TypeDecorator` in conjunction with variant would fail with an MRO error when a comparison operator was used. fixes #3102
* - Fixed bug where the :meth:`.Operators.__and__`,Mike Bayer2014-05-241-0/+49
| | | | | | | :meth:`.Operators.__or__` and :meth:`.Operators.__invert__` operator overload methods could not be overridden within a custom :class:`.TypeEngine.Comparator` implementation. fixes #3012
* - Fixes to the newly enhanced boolean coercion in :ticket:`2804` whereMike Bayer2014-04-011-3/+27
| | | | | | | the new rules for "where" and "having" woudn't take effect for the "whereclause" and "having" kw arguments of the :func:`.select` construct, which is also what :class:`.Query` uses so wasn't working in the ORM either. fixes #3013 re: #2804
* - Added new flag :paramref:`.expression.between.symmetric`, when set to TrueMike Bayer2014-03-301-3/+43
| | | | | | | renders "BETWEEN SYMMETRIC". Also added a new negation operator "notbetween_op", which now allows an expression like ``~col.between(x, y)`` to render as "col NOT BETWEEN x AND y", rather than a parentheiszed NOT string. fixes #2990
* - Fixed bug in :func:`.tuple_` construct where the "type" of essentiallyMike Bayer2014-02-271-1/+21
| | | | | | | | | | | the first SQL expression would be applied as the "comparison type" to a compared tuple value; this has the effect in some cases of an inappropriate "type coersion" occurring, such as when a tuple that has a mix of String and Binary values improperly coerces target values to Binary even though that's not what they are on the left side. :func:`.tuple_` now expects heterogeneous types within its list of values. fixes #2977
* - Fixed bug where :meth:`.in_()` would go into an endless loop ifMike Bayer2014-02-131-0/+33
| | | | | | erroneously passed a column expression whose comparator included the ``__getitem__()`` method, such as a column that uses the :class:`.postgresql.ARRAY` type. [ticket:2957]
* - Added a new parameter :paramref:`.Operators.op.is_comparison`. ThisMike Bayer2014-01-311-0/+10
| | | | | | flag allows a custom op from :meth:`.Operators.op` to be considered as a "comparison" operator, thus usable for custom :paramref:`.relationship.primaryjoin` conditions.
* - The precedence rules for the :meth:`.ColumnOperators.collate` operatorMike Bayer2013-12-051-0/+52
| | | | | | | | | | | have been modified, such that the COLLATE operator is now of lower precedence than the comparison operators. This has the effect that a COLLATE applied to a comparison will not render parenthesis around the comparison, which is not parsed by backends such as MSSQL. The change is backwards incompatible for those setups that were working around the issue by applying :meth:`.Operators.collate` to an individual element of the comparison expression, rather than the comparison expression as a whole. [ticket:2879]
* An overhaul of expression handling for special symbols particularlyMike Bayer2013-10-231-4/+206
| | | | | | | | | | with conjunctions, e.g. ``None`` :func:`.expression.null` :func:`.expression.true` :func:`.expression.false`, including consistency in rendering NULL in conjunctions, "short-circuiting" of :func:`.and_` and :func:`.or_` expressions which contain boolean constants, and rendering of boolean constants and expressions as compared to "1" or "0" for backends that don't feature ``true``/``false`` constants. [ticket:2804]
* - Removed some now unneeded version checks [ticket:2829] courtesy alex gaynorMike Bayer2013-09-221-1/+0
|
* - The :meth:`.Operators.notin_` operator added in 0.8 now properlyMike Bayer2013-08-071-0/+11
| | | | | produces the negation of the expression "IN" returns when used against an empty collection. Also in 0.8.3.
* - remove the ``__iter__()`` with notimplemented since it interferesMike Bayer2013-06-031-7/+6
| | | | with legitimate iterable detection, [ticket:2726]
* Fully implemented the IS and IS NOT operators withMike Bayer2013-04-221-1/+28
| | | | | | | | | | regards to the True/False constants. An expression like ``col.is_(True)`` will now render ``col IS true`` on the target platform, rather than converting the True/ False constant to an integer bound parameter. This allows the ``is_()`` operator to work on MySQL when given True/False constants. [ticket:2682]
* The :meth:`.ColumnOperators.in_` operator will now coerceMike Bayer2013-02-021-0/+6
| | | | | values of ``None`` to :func:`.null`. [ticket:2496]
* - BinaryExpression now keeps track of "left" and "right" as passed in,Mike Bayer2012-12-021-0/+52
| | | | | so that they can be compared in ``__nonzero__`` prior to their self_group() step. [ticket:2621]
* Fixed a gotcha where inadvertently calling list() on aMike Bayer2012-11-181-0/+12
| | | | | | :class:`.ColumnElement` would go into an endless loop, if :meth:`.ColumnOperators.__getitem__` were implemented. A new NotImplementedError is emitted via ``__iter__()``.
* Added :meth:`.ColumnOperators.notin_`,Mike Bayer2012-10-241-1/+41
| | | | | | :meth:`.ColumnOperators.notlike`, :meth:`.ColumnOperators.notilike` to :class:`.ColumnOperators`. [ticket:2580]
* - move most/all operator specific tests into test_operator, convert fully to TOTMike Bayer2012-10-241-32/+334
|
* - get 100% lint/pep8 happening for test_compiler; next we will beginMike Bayer2012-10-241-26/+278
| | | | | | cutting up tests and removing old ones - move test_in() to test_operators - slice up migrated operator tests into TOT
* trying different approaches to test layout. in this one, the testing modulesMike Bayer2012-09-271-2/+3
| | | | | | | become an externally usable package but still remains within the main sqlalchemy parent package. in this system, we use kind of an ugly hack to get the noseplugin imported outside of the "sqlalchemy" package, while still making it available within sqlalchemy for usage by third party libraries.
* - [bug] A tweak to column precedence which moves theMike Bayer2012-09-221-0/+4
| | | | | | | "concat" and "match" operators to be the same as that of "is", "like", and others; this helps with parenthesization rendering when used in conjunction with "IS". [ticket:2564]
* - aaand actually get is/isnot to be usable with None/NULLMike Bayer2012-09-221-2/+7
|
* - [bug] Added missing operators is_(), isnot()Mike Bayer2012-09-221-0/+10
| | | | | | to the ColumnOperators base, so that these long-available operators are present as methods like all the other operators. [ticket:2544]
* add a contains() override testMike Bayer2012-09-041-0/+11
|
View Lorry Controller Status