the behavior is a result of the fact that Oracle has no LIMIT/OFFSET capability, and SQLAlchemy attempts to approximate this behavior, using the recipe described at http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html .
So the subquerying is taking effect as a necessity of that backend, unless you want to add limit/offset to this query in a different way - since every other element of the query is textual I would advise just using a straight text() construct instead of a SELECT. Tiago Becker wrote: > I forgot, in the Ctrl+c and ctrl+v, to add that the problem only happens > when using limit and offset with oracle dialect (sorry about that).. > > so > > > self.fields = ' case field1 > when 34 then field2 > when 94 then field3 > when 48 then field4 > end another_field' > > self.from = ' table ' > > self.where = ' 1 = ' //nevermind this > > sql = select(columns=[self.fields], from_obj=self.tables, > whereclause=self.where, bind=self.engine, order_by= ' 1 > ').limit(1).offset(30) > > result: > > select > case field1 > when 34 then field2 > when 94 then field3 > when 48 then field4 > end another_field > from > (select > case field1 > when 34 then field2 > when 94 then field3 > when 48 then field4 > end another_field, > ROW_NUMBER() OVER (ORDER BY 1 ) AS ora_rn > from > table > where > 1 = 1) > WHERE ora_rn>1 AND ora_rn<=30 > > > > > sorry about the mess, > > On Tue, May 19, 2009 at 2:21 PM, Michael Bayer > <mike...@zzzcomputing.com>wrote: > >> send along full reproducing test cases and that will reveal all. >> >> On May 19, 2009, at 11:36 AM, Tiago Becker wrote: >> >> "this indicates a None is being sent as a column somewhere." >> >> I'm just not passing the order_by clause... >> >> "I dont see the SQLAlchemy expression you're dealing with here. the >> symptom you describe is when placing a FROM object in the columns >> clause of a SELECT, the object is also added to the FROM clause >> automatically, unless additional steps are taken to prevent this. A >> CASE statement is not is not a FROM object, its a column object, so >> this should not occur. If you are placing a SELECT in the columns >> clause of another SELECT, call as_scalar() on it." >> >> hmm, something strange here, as i'm not passing a from object in the >> columns... >> >> im doing this: >> >> self.fields = ' case field1 >> when 34 then field2 >> when 94 then field3 >> when 48 then field4 >> end another_field' >> >> self.from = ' table ' >> >> self.where = ' 1 = ' //nevermind this >> >> then: >> >> sql = select(columns=[self.fields], from_obj=self.tables, >> whereclause=self.where, bind=self.engine, order_by= ' 1 ') >> >> results in >> >> select >> case field1 >> when 34 then field2 // this is the problem... in the inner >> select (the FROM select), field2 is not selected, so it does not exists >> when 94 then field3 >> when 48 then field4 >> end another_field >> from >> (select >> case field1 >> when 34 then field2 >> when 94 then field3 >> when 48 then field4 >> end another_field >> from >> table >> where >> 1 = 1) >> >> this is can be fixed by replacing the fields in the outer join by adding >> *, >> as it will select all the fields in the query... >> >> How do i solve this? >> >> On Tue, May 19, 2009 at 12:13 PM, Michael Bayer >> <mike...@zzzcomputing.com>wrote: >> >>> >>> >>> On May 19, 2009, at 10:39 AM, Tiago Becker wrote: >>> >>> > Hi. >>> > >>> > Im still trying to use the select object from sqlalchemy, but i >>> > found a strange (bug or not) behavior: >>> > >>> > sql = select(columns=[self.fields], from_obj=self.tables, >>> > whereclause=self.where, bind=self.req.cfg.engine, order_by= ' 1 ') >>> > >>> > 1) without order by, i get an error: AttributeError: 'NoneType' >>> > object has no attribute 'proxies'. is this intended? >>> >>> this indicates a None is being sent as a column somewhere. >>> >>> > 2) the select fails when theres a CASE instruction, like this: >>> > >>> > case field1 >>> > when 34 then field2 >>> > when 94 then field3 >>> > when 48 then field4 >>> > end another_field >>> > >>> > It fails because sqlalchemy does a subquery, but instead of using * >>> > (in the outer query, to bring all the fields) it just paste the same >>> > fields from the original query, failing to get all the fields in the >>> > case expression.... >>> >>> I dont see the SQLAlchemy expression you're dealing with here. the >>> symptom you describe is when placing a FROM object in the columns >>> clause of a SELECT, the object is also added to the FROM clause >>> automatically, unless additional steps are taken to prevent this. A >>> CASE statement is not is not a FROM object, its a column object, so >>> this should not occur. If you are placing a SELECT in the columns >>> clause of another SELECT, call as_scalar() on it. >>> >>> >>> >> >> >> >> >> >> > >> > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
