Negation with .self_group()

[sqlalchemy-bot]

Migrated issue, originally created by Alana Pradhan

I think this bug is an extension of https://bitbucket.org/zzzeek/sqlalchemy/issues/3969/negation-of-labeled-element

I hit this bug in 1.2.10 when I add .self_group() to the or_ expression:

from sqlalchemy import *
from sqlalchemy.dialects import sqlite
expr = or_(column('x') == 1, column('y') == 2).self_group()

bug = not_(expr.label('foo'))



print(bug.compile())
print(bug.compile(dialect=sqlite.dialect()))

NOT (x = :x_1 OR y = :y_1)
(x = ? OR y = ?) = 0

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

same question, how did you come across this? what are you trying to achieve (briefly) ?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

the "=0" is coming out for backends that don't have the concept of "native boolean", which means among other things they don't like specifying a column as a boolean, such as, "SELECT * FROM table WHERE x" or "SELECT * FROM table WHERE NOT x", hence SQLAlchemy adds "=1" or "=0" to express the integer comparison explicitly, as these databases use integer values 1/0 to represent boolean.

The original issue in #3969 was that the parenthesis were getting lost. However here, the parenthesis are maintained. On the two backends that are most prominently non-native boolean, I can't reproduce any problem:

SQLite:

sqlite> create table foo(x integer, y integer);
sqlite> insert into foo (x, y) values (5, 5);
sqlite> insert into foo (x, y) values (7, 7);
sqlite> select * from foo WHERE (x=5 or y=5) = 0;
7|7
sqlite> select * from foo WHERE not (x=5 or y=5);
7|7

MySQL / MariaDB:

MariaDB [test]> create table foo(x integer, y integer);
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> insert into foo (x, y) values (5, 5);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into foo (x, y) values (7, 7);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from foo WHERE (x=5 or y=5) = 0;
+------+------+
| x    | y    |
+------+------+
|    7 |    7 |
+------+------+
1 row in set (0.00 sec)

MariaDB [test]> select * from foo WHERE not (x=5 or y=5);
+------+------+
| x    | y    |
+------+------+
|    7 |    7 |
+------+------+
1 row in set (0.00 sec)

so while the expression is visually unappealing, it works fine. Can you provide more detail how this behavior is impacting what you are trying to do ?

[sqlalchemy-bot]

Alana Pradhan wrote:

Thank you for getting back to me. I think then the issue is with Oracle (v12c 12.1)? When I try to run a query using that syntax, I get a syntax error: ORA-00933: SQL command not properly ended

SELECT * FROM all_objects WHERE (owner='test' or object_name='test') = 0

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK...and why are you calling self_group() explicitly?

[sqlalchemy-bot]

Alana Pradhan wrote:

So I might be using the ORM API incorrectly, but I always call .self_group() on my nested OR statements just to be safe. Happy to take another approach.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK. I'd like to see if this can be fixed though a quick solution is eluding me. But you probably don't need to call self_group() as parenthesization is added automatically based on precedence rules.

[sqlalchemy-bot]

Alana Pradhan wrote:

Awesome. Thank you for letting me know about the parentheses being automatically added. I do see that in my code and was able to remove the .self_group() call. (Also, just wanted to say that I'm a huge fan of sqlalchemy and appreciate all the work you've done as well as the contribution to the community)

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

a proposal is up at https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/841. not sure if I want this backported to 1.2 or not but that is the plan at the moment.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

passed on the first run will put it in 1.2 also

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Add concept of "implicit boolean", treat as native

Fixed issue that is closely related to 🎫3639 where an expression
rendered in a boolean context on a non-native boolean backend would
be compared to 1/0 even though it is already an implcitly boolean
expression, when :meth:.ColumnElement.self_group were used. While this
does not affect the user-friendly backends (MySQL, SQLite) it was not
handled by Oracle (and possibly SQL Server). Whether or not the
expression is implicitly boolean on any database is now determined
up front as an additional check to not generate the integer comparison
within the compliation of the statement.

Fixes: #4320
Change-Id: Iae0a65e5c01bd576e64733c3651e1e1a1a1b240c

→ 462ccd9

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Add concept of "implicit boolean", treat as native

Fixed issue that is closely related to 🎫3639 where an expression
rendered in a boolean context on a non-native boolean backend would
be compared to 1/0 even though it is already an implcitly boolean
expression, when :meth:.ColumnElement.self_group were used. While this
does not affect the user-friendly backends (MySQL, SQLite) it was not
handled by Oracle (and possibly SQL Server). Whether or not the
expression is implicitly boolean on any database is now determined
up front as an additional check to not generate the integer comparison
within the compliation of the statement.

Fixes: #4320
Change-Id: Iae0a65e5c01bd576e64733c3651e1e1a1a1b240c
(cherry picked from commit 462ccd9)

→ 7b53d99