On Wed, Mar 7, 2018 at 8:20 AM, <nicolas.ro...@tiime.fr> wrote: > Hello everyone, > > While toying around with the cool new features of 1.2, I noticed that the > expanding of a bindparam in a "in" doesn't accept empty list. > > This is documented, and the error message is pretty clear, but the behavior > itself is pretty inconsistent with the other "in" syntax :
it is quite inconsistent. But that's where we are - SQL doesn't support empty "IN" and "expanding IN" only does a search and replace of the "()" part of the IN expression, which is what many DBAPI drivers do as well. the "empty IN" that you can do normally is itself something that only works as of 1.2 if you view the history of that change (http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#the-in-not-in-operator-s-empty-collection-behavior-is-now-configurable-default-expression-simplified) > > from sqlalchemy import * > metadata = MetaData() > engine = create_engine('sqlite://') > user = Table('user', metadata, > Column('user_id', Integer, primary_key=True), > ) > > id_list = [] > metadata.create_all(engine) > stmt_1 = select([user]).where( > user.c.user_id.in_(id_list) > ) > > stmt_2 = select([user]).where( > user.c.user_id.in_(bindparam('foo', expanding=True)) > ) > > engine.execute(stmt_2, {"foo": id_list}) > > Here > engine.execute(stmt_1) > returns the expected result (where the "in empty list" is replaced by false) > > And > engine.execute(stmt_2, {"foo": id_list}) > returns an error > > If I'm not mistaken the first statement already use a tricks to simulate an > empty list as print(stmt_1) returns > SELECT "user".user_id FROM "user" WHERE 1 != 1 > > If my understantding of sqlalchemy is correct in the second statement the > query > > SELECT user.user_id \nFROM user \nWHERE user.user_id IN ([EXPANDING_foo])' > > is aready "compiled" and there is a replacement of the string > "[EXPANDING_foo]" by the actual list put in the bindparam. that is correct. > > Would it be possible in the case where the list is empty replace it by a > query returning an empty list instead of throwing an error ? > (https://bitbucket.org/zzzeek/sqlalchemy/src/beab362b2e892fe11d0526064eabd136d3b1f8ee/lib/sqlalchemy/engine/default.py?at=master&fileviewer=file-view-default#default.py-735) it means the statement would have to be like: SELECT user.user_id FROM user WHERE [EXPANDING_EXPR]user.user_id IN ([EXPANDING_foo])[/EXPANDING_EXPR]' so that the whole thing can be replaced. Note EXPANDING_EXPR can be nested too. We'd be building a whole recursive descent parser into the most performance critical part of the statement invocation. I'm not in a hurry to do this but feel free to work on this or alternate ideas. The ratio of complexity to benefit seems quite poor. > For example "select 1 where false" returns and empty list and this works on > at least postgres > (https://www.postgresql.org/message-id/20060609130037.32155.qm...@web37915.mail.mud.yahoo.com) > and mysql. > > TLDR; query producing empty lists such as "select 1 where false" may be used > as tricks to resovle the non handling of empty IN expression > > -- > 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
