However, when using it on the right side of a compound expression, it doesn’t
generate parenthesis as we expect:
We can also usually force parenthesization around a binary expression (e.g.
an expression that has left/right operands and an operator) using the
ColumnElement.self_group() method:
Why are the parentheses rules like this?
A lot of databases barf when there are excessive parenthesis or when
parenthesis are in unusual places they doesn’t expect, so SQLAlchemy does not
generate parenthesis based on groupings, it uses operator precedence and if the
operator is known to be associative, so that parenthesis are generated
minimally. Otherwise, an expression like:
column('a') & column('b') & column('c') & column('d')
would produce:
(((a AND b) AND c) AND d)
which is fine but would probably annoy people (and be reported as a bug). In
other cases, it leads to things that are more likely to confuse databases or at
the very least readability, such as:
column('q', ARRAY(Integer, dimensions=2))[5][6]
would produce:
There are also some edge cases where we get things like "(x) = 7" and databases
really don’t like that either. So parenthesization doesn’t naively
parenthesize, it uses operator precedence and associativity to determine
groupings.
For Operators.op(), the value of precedence defaults to zero.
What if we defaulted the value of Operators.op.precedence to 100,
e.g. the highest? Then this expression makes more parenthesis, but is
otherwise OK, that is, these two are equivalent:
>>> print (column('q') - column('y')).op('+', precedence=100)(column('z'))
(q - y) + z
>>> print (column('q') - column('y')).op('+')(column('z'))
q - y + z
but these two are not:
>>> print column('q') - column('y').op('+', precedence=100)(column('z'))
q - y + z
>>> print column('q') - column('y').op('+')(column('z'))
q - (y + z)
For now, it’s not clear that as long as we are doing parenthesization based on
operator precedence and associativity, if there is really a way to parenthesize
automatically for a generic operator with no precedence given that is going to
work in all cases, because sometimes you want a custom op to have a lower
precedence than the other operators and sometimes you want it to be higher.
It is possible that maybe if the “binary” expression above forced the use of
the self_group() method when op() is called, making the assumption that
a compound expression on the left side can always be parenthesized harmlessly.
Perhaps this change can be made at some point, however for the time being
keeping the parenthesization rules more internally consistent seems to be
the safer approach.