<p dir="auto"><strong>Migrated issue, originally created by Robert Scott (<a href="https://github.com/ris">@ris</a>)</strong></p>
<p dir="auto">Using sqlalchemy 1.1.4 with postgresql 9.6.</p>
<p dir="auto">The result of an <code class="notranslate">IS NULL</code> comparison is a boolean and in postgres it is usable as such - for instance the following works:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="db=&gt; SELECT (123 IS NULL) = ('blah' IS NULL);
 ?column? 
----------
 t
(1 row)"><pre class="notranslate"><code class="notranslate">db=&gt; SELECT (123 IS NULL) = ('blah' IS NULL);
 ?column? 
----------
 t
(1 row)
</code></pre></div>
<p dir="auto">However the following fails due to operator precedence:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="db=&gt; SELECT 1 IS NULL = 'blah' IS NULL;
ERROR:  invalid input syntax for type boolean: &quot;blah&quot;
LINE 1: SELECT 1 IS NULL = 'blah' IS NULL;"><pre class="notranslate"><code class="notranslate">db=&gt; SELECT 1 IS NULL = 'blah' IS NULL;
ERROR:  invalid input syntax for type boolean: "blah"
LINE 1: SELECT 1 IS NULL = 'blah' IS NULL;
</code></pre></div>
<p dir="auto">sqlalchemy 1.1.4 unfortunately doesn't include these parentheses:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&gt;&gt;&gt; str(db.session.query((User.id == None) == (User.name == None)))
'SELECT users.id IS NULL = users.name IS NULL AS anon_1 \nFROM users'"><pre class="notranslate"><code class="notranslate">&gt;&gt;&gt; str(db.session.query((User.id == None) == (User.name == None)))
'SELECT users.id IS NULL = users.name IS NULL AS anon_1 \nFROM users'
</code></pre></div>
<p dir="auto">which of course fails in a similar way. Luckily this wrapping <em>is</em> performed correctly for the result of equalities:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&gt;&gt;&gt; str(db.session.query((User.id == 123) == (User.name == &quot;blah&quot;)))
'SELECT (users.id = %(id_1)s) = (users.name = %(name_1)s) AS anon_1 \nFROM users'"><pre class="notranslate"><code class="notranslate">&gt;&gt;&gt; str(db.session.query((User.id == 123) == (User.name == "blah")))
'SELECT (users.id = %(id_1)s) = (users.name = %(name_1)s) AS anon_1 \nFROM users'
</code></pre></div>
<p dir="auto">and also the workaround of manually casting the result to a boolean works fine:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&gt;&gt;&gt; str(db.session.query(db.cast(User.id == None, db.Boolean) == db.cast(User.name == None, db.Boolean)))
'SELECT CAST(users.id IS NULL AS BOOLEAN) = CAST(users.name IS NULL AS BOOLEAN) AS anon_1 \nFROM users'"><pre class="notranslate"><code class="notranslate">&gt;&gt;&gt; str(db.session.query(db.cast(User.id == None, db.Boolean) == db.cast(User.name == None, db.Boolean)))
'SELECT CAST(users.id IS NULL AS BOOLEAN) = CAST(users.name IS NULL AS BOOLEAN) AS anon_1 \nFROM users'
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">e.g.:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="--- 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,"><pre class="notranslate"><code class="notranslate">--- 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,
</code></pre></div>
<p dir="auto">rationale:  MySQL, Oracle and now Postgresql all have relatively flat precedences for these operators:</p>
<p dir="auto">(mysql):<br>
<a href="https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/operator-precedence.html" rel="nofollow">https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/operator-precedence.html</a></p>
<p dir="auto">(oracle):</p>
<p dir="auto"><a href="https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions001.htm#i1034834" rel="nofollow">https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions001.htm#i1034834</a></p>
<p dir="auto">pg 9.4, less flat: <a href="https://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE" rel="nofollow">https://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE</a></p>
<p dir="auto">pg 9.5, now is more flat: <a href="https://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE" rel="nofollow">https://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE</a></p>
<p dir="auto">making these all have the same precedence means we'll just have more parenthesis.</p>