On 04/20/2017 03:39 PM, George London wrote:
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
OK so, this could be improved, but also, you shouldn't be putting a
label() expression into the WHERE clause. That's the part here that the
API does not expect.
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)
right because, negation works in the absense of label(). a negation of
a label in SQL doesn't make sense. Label means this:
"some_sql_expression AS some_label"
how do you "negate" that in SQL? "not (some_sq_expression AS
some_label)"? that's invalid SQL. I know you expect it to "dig in"
to the element, e.g. modify the thing that is labeled, but this would be
an exception to the usual structural semantics of the SQL expression
language. We probably make exceptions like this in some places but they
are special cases that need to be added and tested.
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 when you do this stuff it is very important to turn the echo=True on
the engine. Because the SQLite compiler is returning a different
result than the default one:
compare:
str(query_with_negated_disjunction_with_label)
to:
str(query_with_negated_disjunction_with_label.compile(engine))
Why that is, I have no idea. Let's see.
from sqlalchemy import *
expr = or_(column('x') == 1, column('y') == 2)
bug = not_(expr.label('foo'))
from sqlalchemy.dialects import sqlite
print bug.compile()
print bug.compile(dialect=sqlite.dialect())
output:
NOT x = :x_1 OR y = :y_1
x = ? OR y = ? = 0
So, this seems very odd at first but this is in fact hitting another
element in the code that even has a TODO around it
(https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/elements.py#L656),
because it doesn't know what to do with your label() (again, unsupported
/ never anticipated use case) it assumes that you're giving it a true_()
/ false_() constant, so you see in the case of SQLite it is trying to
compare the whole thing to zero because SQLite doesn't have true/false
constants.
I've put this up at
https://bitbucket.org/zzzeek/sqlalchemy/issues/3969/negation-of-labeled-element
. SQL element changes like these are never trivial things because they
can have far reaching affects so I encourage you do just not use label()
until the moment you are putting your expression into the top-level
columns clause. thanks for the clear test case!
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
