the int() catch here on limit/offset is something we added due to user report which raised the issue that it's a security hazard, back when we used to render the given value directly in the SQL without using a bound parameter. We fixed both that it allowed non-int values as well as that it didn't use a bound parameter; technically only one or the other is needed at most. But it quickly got picked up by about a hundred "security advisory" bots blasting for months about it as a SQLAlchemy security advisory, so I haven't gone near liberalizing it. At this point the "int" check could probably switch on either int or a SQL expression (just not straight text). Looking at the way limit_clause() is written we'd have to change how _limit/_offset are interpreted in order to allow other expressions in there. There may be existing recipes that expect _limit/_offset to be plain integers though.
but without even getting into changing anything, looking at the source for the postgresql compiler it would appear ALL is already emitted if OFFSET is present and LIMIT is not: from sqlalchemy.sql import select from sqlalchemy.dialects import postgresql print select(['foo']).offset(5).compile(dialect=postgresql.dialect()) output: SELECT foo LIMIT ALL OFFSET %(param_1)s Seems like it's been this way for years, it already emits ALL. So there's no issue? On Mar 20, 2014, at 5:49 PM, Ryan Kelly <[email protected]> wrote: > Redshift needs LIMIT ALL to avoid attempting an optimization which > causes it to crash. > > Note that OFFSET 0 is the same as omitting OFFSET, but it acts as an > optimization fence. This, I suppose, is a similar thing. > > -Ryan Kelly > > On Thu, Mar 20, 2014 at 5:38 PM, Michael Bayer <[email protected]> > wrote: >> >> LIMIT NULL and LIMIT ALL per the PG docs at >> http://www.postgresql.org/docs/9.0/static/queries-limit.html are the same as >> omitting the number. These would appear to be syntactical helpers that >> you wouldn't really need when working with a select() construct (unless >> you're trying to get at some PG optimizer quirk). >> >> so why exactly do you need to emit these otherwise unnecessary keywords ? >> >> >> >> On Mar 20, 2014, at 5:29 PM, Ryan Kelly <[email protected]> wrote: >> >>> Hi: >>> >>> It seems that the argument to query.limit must be a number. However, >>> NULL (which I imagine could be passed by the null() construct or as a >>> string) and ALL (which, I suppose could be text("ALL") or >>> literal("ALL") or just the string "ALL") are perfectly acceptable >>> values on PostgreSQL. >>> >>> Is there some way to convince SQLAlchemy to render these values? >>> >>> -Ryan Kelly >>> >>> -- >>> 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 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 [email protected]. >> To post to this group, send email to [email protected]. >> 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
