Flatten operator precedence for comparison operators

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

6 files changed, 64 insertions, 21 deletions

diff --git a/doc/build/changelog/changelog_12.rst b/doc/build/changelog/changelog_12.rst
index c855c10e5..e7d15d72d 100644
--- a/doc/build/changelog/changelog_12.rst
+++ b/doc/build/changelog/changelog_12.rst
@@ -34,6 +34,23 @@
         An exception is now raised if the version id is programmatic and
         was set to NULL for an UPDATE.  Pull request courtesy Diana Clarke.
 
+    .. change:: 3999
+        :tags: bug, sql
+        :tickets: 3999
+
+        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.
+
+        .. seealso::
+
+            :ref:`change_3999`
+
+
     .. change:: 3796
         :tags: bug, orm
         :tickets: 3796
diff --git a/doc/build/changelog/migration_12.rst b/doc/build/changelog/migration_12.rst
index 9371fe64c..c25bdaced 100644
--- a/doc/build/changelog/migration_12.rst
+++ b/doc/build/changelog/migration_12.rst
@@ -512,6 +512,24 @@ The feature should be regarded as **experimental** within the 1.2 series.
 
 :ticket:`3953`
 
+.. _change_3999:
+
+Flattened operator precedence for comparison operators
+-------------------------------------------------------
+
+The operator precedence for operators like IN, LIKE, equals, IS, MATCH, and
+other comparison operators has been flattened into one level.  This will
+have the effect of more parenthesization being generated when comparison
+operators are combined together, such as::
+
+    (column('q') == null()) != (column('y') == null())
+
+Will now generate ``(q IS NULL) != (y IS NULL)`` rather than
+``q IS NULL != y IS NULL``.
+
+
+:ticket:`3999`
+
 .. _change_1546:
 
 Support for SQL Comments on Table, Column, includes DDL, reflection
diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst
index 634abbfc5..9420441e3 100644
--- a/doc/build/core/tutorial.rst
+++ b/doc/build/core/tutorial.rst
@@ -1177,7 +1177,7 @@ username:
     ...                 addresses.c.email_address.like(users.c.name + '%')
     ...             )
     ...  )
-    users JOIN addresses ON addresses.email_address LIKE (users.name || :name_1)
+    users JOIN addresses ON addresses.email_address LIKE users.name || :name_1
 
 When we create a :func:`.select` construct, SQLAlchemy looks around at the
 tables we've mentioned and then places them in the FROM clause of the
@@ -1192,7 +1192,7 @@ here we make use of the :meth:`~.Select.select_from` method:
     ...    )
     {sql}>>> conn.execute(s).fetchall()
     SELECT users.fullname
-    FROM users JOIN addresses ON addresses.email_address LIKE (users.name || ?)
+    FROM users JOIN addresses ON addresses.email_address LIKE users.name || ?
     ('%',)
     {stop}[(u'Jack Jones',), (u'Jack Jones',), (u'Wendy Williams',)]
 
@@ -1273,7 +1273,7 @@ off to the database:
     {sql}>>> conn.execute(s, username='wendy').fetchall()
     SELECT users.id, users.name, users.fullname
     FROM users
-    WHERE users.name LIKE (? || '%')
+    WHERE users.name LIKE ? || '%'
     ('wendy',)
     {stop}[(2, u'wendy', u'Wendy Williams')]
 
@@ -1298,7 +1298,7 @@ single named value is needed in the execute parameters:
     SELECT users.id, users.name, users.fullname, addresses.id,
         addresses.user_id, addresses.email_address
     FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
-    WHERE users.name LIKE (? || '%') OR addresses.email_address LIKE (? || '@%')
+    WHERE users.name LIKE ? || '%' OR addresses.email_address LIKE ? || '@%'
     ORDER BY addresses.id
     ('jack', 'jack')
     {stop}[(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com'), (1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')]
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 58f32b3e6..f8731385b 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -1091,18 +1091,19 @@ _PRECEDENCE = {
     sub: 7,
 
     concat_op: 6,
-    match_op: 6,
-    notmatch_op: 6,
-
-    ilike_op: 6,
-    notilike_op: 6,
-    like_op: 6,
-    notlike_op: 6,
-    in_op: 6,
-    notin_op: 6,
-
-    is_: 6,
-    isnot: 6,
+
+    match_op: 5,
+    notmatch_op: 5,
+
+    ilike_op: 5,
+    notilike_op: 5,
+    like_op: 5,
+    notlike_op: 5,
+    in_op: 5,
+    notin_op: 5,
+
+    is_: 5,
+    isnot: 5,
 
     eq: 5,
     ne: 5,
diff --git a/test/orm/test_relationships.py b/test/orm/test_relationships.py
index 52debbe3a..42c554bc8 100644
--- a/test/orm/test_relationships.py
+++ b/test/orm/test_relationships.py
@@ -442,12 +442,12 @@ class DirectSelfRefFKTest(fixtures.MappedTest, AssertsCompiledSQL):
         Entity = self.classes.Entity
         self.assert_compile(
             Entity.descendants.property.strategy._lazywhere,
-            "entity.path LIKE (:param_1 || :path_1)"
+            "entity.path LIKE :param_1 || :path_1"
         )
 
         self.assert_compile(
             Entity.descendants.property.strategy._rev_lazywhere,
-            ":param_1 LIKE (entity.path || :path_1)"
+            ":param_1 LIKE entity.path || :path_1"
         )
 
     def test_ancestors_lazyload_clause(self):
@@ -456,12 +456,12 @@ class DirectSelfRefFKTest(fixtures.MappedTest, AssertsCompiledSQL):
         # :param_1 LIKE (:param_1 || :path_1)
         self.assert_compile(
             Entity.anscestors.property.strategy._lazywhere,
-            ":param_1 LIKE (entity.path || :path_1)"
+            ":param_1 LIKE entity.path || :path_1"
         )
 
         self.assert_compile(
             Entity.anscestors.property.strategy._rev_lazywhere,
-            "entity.path LIKE (:param_1 || :path_1)"
+            "entity.path LIKE :param_1 || :path_1"
         )
 
     def test_descendants_lazyload(self):
@@ -524,7 +524,7 @@ class DirectSelfRefFKTest(fixtures.MappedTest, AssertsCompiledSQL):
         self.assert_compile(
             sess.query(Entity).join(Entity.descendants, aliased=True),
             "SELECT entity.path AS entity_path FROM entity JOIN entity AS "
-            "entity_1 ON entity_1.path LIKE (entity.path || :path_1)"
+            "entity_1 ON entity_1.path LIKE entity.path || :path_1"
         )
 
 
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 3dd9af5e2..c18f9f9be 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -1478,6 +1478,13 @@ class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         self.assert_compile(op2, "mytable.myid hoho :myid_1 lala :param_1")
         self.assert_compile(op3, "(mytable.myid hoho :myid_1) lala :param_1")
 
+    def test_is_eq_precedence_flat(self):
+        self.assert_compile(
+            (self.table1.c.name == null()) !=
+            (self.table1.c.description == null()),
+            "(mytable.name IS NULL) != (mytable.description IS NULL)",
+        )
+
 
 class OperatorAssociativityTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = 'default'