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 <[email protected]>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
> <[email protected]>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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
