- Added new flag :paramref:`.expression.between.symmetric`, when set to True

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

1 files changed, 12 insertions, 1 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 5165ee78f..af40b2537 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -93,7 +93,6 @@ OPERATORS = {
     operators.ge: ' >= ',
     operators.eq: ' = ',
     operators.concat_op: ' || ',
-    operators.between_op: ' BETWEEN ',
     operators.match_op: ' MATCH ',
     operators.in_op: ' IN ',
     operators.notin_op: ' NOT IN ',
@@ -956,6 +955,18 @@ class SQLCompiler(Compiled):
                 if escape else ''
             )
 
+    def visit_between_op_binary(self, binary, operator, **kw):
+        symmetric = binary.modifiers.get("symmetric", False)
+        return self._generate_generic_binary(
+                        binary, " BETWEEN SYMMETRIC "
+                                if symmetric else " BETWEEN ", **kw)
+
+    def visit_notbetween_op_binary(self, binary, operator, **kw):
+        symmetric = binary.modifiers.get("symmetric", False)
+        return self._generate_generic_binary(
+                        binary, " NOT BETWEEN SYMMETRIC "
+                                if symmetric else " NOT BETWEEN ", **kw)
+
     def visit_bindparam(self, bindparam, within_columns_clause=False,
                                             literal_binds=False,
                                             skip_bind_expression=False,