[sqlalchemy] Is there a reason why there is no dialect specific fix for ORA-01795?
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 or split in_ to several or_) Why not incorporate this fix into sqlalchemy? -- 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.
Re: [sqlalchemy] Is there a reason why there is no dialect specific fix for ORA-01795?
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 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. 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.
Re: [sqlalchemy] Is there a reason why there is no dialect specific fix for ORA-01795?
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) 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 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. 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.
Re: [sqlalchemy] Is there a reason why there is no dialect specific fix for ORA-01795?
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.
