I noticed some strange behavior while using the SQLAlchemy expression
language. I'm using Python 3.5 and SQLAlchemy 1.1.19 against MySQL 5.7.13
via the mysqlclient v1.3.10 connector.
The problem happens when I build up a boolean expression using and_() /
or_(), then give that expression a name using .label(), and then negate the
expression using not_().
For example,
disjunction = or_(user.c.user_name == "jack", user.c.user_name == "james")
disjunction_with_label = disjunction.label("banned_users")
negated_disjunction = not_(disjunction_with_label)
This is causing two problems:
1) If I use this expression with .where to filter a select query, the SQL
that SQLAlchemy emits doesn't wrap the negated clause in parentheses,
meaning that only the first clause in the disjunction will be negated
(instead of the whole inner expression, as I'd expect). E.g.:
SELECT "user".user_id, "user".user_name, "user".email_address, "user".password
FROM "user"
WHERE NOT "user".user_name = :user_name_1 OR "user".user_name = :user_name_2
However, if I don't .label() the disjunction, SQLAlchemy emits a query that
*does* have parentheses:
SQL: SELECT "user".user_id, "user".user_name, "user".email_address,
"user".password
FROM "user"
WHERE NOT ("user".user_name = :user_name_1 OR "user".user_name = :user_name_2)
2) actually running the emitted query produces strange results (that are
different from the results I get by binding the literals and running the
textual query directly.)
Please see this gist
https://gist.github.com/rogueleaderr/e28cb1f707b6637421137c0c4bf3c282 for a
full working reproduction of the issue in a Jupyter notebook and in an
executable Python script.
So...is there a SQL reason I might not be aware of why negating a labelled
expression should behave in this way? If not, should I consider this a bug in
SQLAlchemy and report it?
Thank you!
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
