On 6/17/14, 9:30 AM, Ofir Herzas wrote: > This can be done only for Oracle and only if the number of expressions > is higher than 1000 (which would otherwise produce an exception) > Regarding complex queries, I guess the several or_'s fix should work: > or_(x.in_(1...1000), x.in_(1001...2000)) > > How can this be done locally? (override in_ operator only for Oracle) I've looked at the thread again and it's not 100% clear to me which solution you're referring to. The linked message refers to using a tuple_() format that somehow bypasses Oracle's normal limits; my workaround is that I actually execute the statement multiple times as needed. Here it seems you're introducing a third potential workaround which is to join them together with or_().
For the tuple_() format or the or_() format, operators are overridden at the type level, see http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#types-operators. There's not yet a direct hook to override how "in" compiles to a SQL string for Oracle only. but again this would never be a default behavior because Oracle's 1000 element limit is there for a reason and we'd like to encourage users to stick to a database's most idiomatic patterns, else be notified that they're not. > > Thanks, > Ofir > > > On Tuesday, June 17, 2014 4:18:11 PM UTC+3, Michael Bayer wrote: > > > On 6/17/14, 4:32 AM, Ofir Herzas wrote: > > ORA-01795: maximum number of expressions in a list is 1000 > > > > As I understand, there are several options to fix this issue > > (e.g. > > > > https://groups.google.com/forum/#!searchin/sqlalchemy/maximum$20number$20of$20expressions/sqlalchemy/Oa6YWNE0-IQ/6GVmy27B-FAJ > > <https://groups.google.com/forum/#%21searchin/sqlalchemy/maximum$20number$20of$20expressions/sqlalchemy/Oa6YWNE0-IQ/6GVmy27B-FAJ> > > > or split in_ to several or_) > > > > Why not incorporate this fix into sqlalchemy? > SQLAlchemy's core SQL model is one-to-one with SQL. Taking a single > statement and magically executing ten statements within the scope > of an > execute() due to a particular operator being present would be very > inappropriate and surprising, and also wouldn't work for any queries > that are more complex than a simple "WHERE x IN y". > > > > -- > 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 > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
