SQL expressions don't support x < col < y

[Former user]

If you use

session.Query(SomeTable).filter(1 < SomeTable.some_col < 100)

the generated SQL statement will have an incomplete WHERE clause:

SELECT ... WHERE SomeTable.some_col < :some_col_1

It should instead be

SELECT ... WHERE :some_col_1 < SomeTable.some_col AND SomeTable.some_col < :some_col_2

[Michael Trier]

You should be able to accomplish this with do:

session.Query(SomeTable).filter(_and(1 < SomeTable.some_col, SomeTable.some_col < 100))

or using SomeTable.some_col.between(1, 100) depending on how your database implements between you may need to modify the numbers in the clause.

[Mike Bayer]

unfortunately this is likely impossible from a python perspective. The mechanism of "x < y < z" relies upon the return value of the two individual expressions. a SQLA expression such as "column < 5" returns a BinaryExpression object, which evaluates as True - therefore the second expression is never called and we are never given a chance to detect the chain of expressions. Furthermore, the chain of expressions would need to be detected and converted to BETWEEN, since SQL doesn't support the chained comparison operators.

Not including the detection of chains->BETWEEN part, to make this work would require manipulation of the BinaryExpression object's __nonzero__() value based on the direction of the comparison operator, so as to force both comparisons. Adding a basic __nonzero__() to BinaryExpression that returns False illustrates that it's tolerated pretty poorly by the current codebase, and at the very least many dozens of "if x:" kinds of checks would need to be converted to "if x is None:", but there might be further issues that are more difficult to resolve. For the outside world it might wreak havoc.

Given that the appropriate SQL operator here is BETWEEN which is easily accessible from the between operator, I don't think the level of bending over backwards and confusing people is worth it so this a "wontfix".

[Mike Bayer]

Removing milestone: 0.5.xx (automated comment)