fixed a bug that exists if limit isn't specified.
things should be fine now...
On 15/07/07, lei you <[EMAIL PROTECTED]> wrote:
>
> the line
>
> orderby = wrapper.order_by_clause = sql.ClauseList(*map(
> wrapper.corresponding_column, select.order_by_clause.clauses))
>
> should most be
>
> orderby = sql.ClauseList(*map(wrapper.corresponding_column,
> select.order_by_clause.clauses))
>
> not exactly sure why i did that...
>
> new diff attached
>
> On 15/07/07, lei you <[EMAIL PROTECTED]> wrote:
> >
> > what is sql generated for the proper fix?
> >
> > attached is a patch that attempts the wrapper solution. (seems to work
> > for both union/non-union selects)
> >
> > it basically does the following.
> >
> > wrap the original select (be it union or not) in _msorg
> >
> > if the original select has an order_by_clause,
> > adapt the order_by_clause for use in _msorg
> > remove the order_by_clause from the original select,
> > then perform effectively
> > select _ms.[columns involved]
> > from (select _msorg.*, row_number() over ([adapted orderby_clause])
> > as _msrn
> > from _msorg) as _ms
> > where ...
> >
> > otherwise, the original select does not have an order_by_clause,
> > perform
> > select _ms.[columns involved]
> > from (select _msorg.*, row_number() over (_msnull) as _msrn
> > from _msorg, (select null as _msnull) as __msnull) as _ms
> > where ...
> >
> > what other potential pitfall may there be?
> >
> > (using 0.4.0 doesn't seem to be an option for me,
> > my current setup is tesla 0.2.4 on (elixir + sqlalchemy 0.39 + pylons
> > 0.9.6),
> > last time i tried sa 0.4, things didnt seem to want to work out,
> > can't exactly remember what's broken (most probably elixir)
> >
> >
> > On 15/07/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
> > >
> > >
> > >
> > >
> > > On Jul 14, 11:15 pm, "lei you" < [EMAIL PROTECTED] > wrote:
> > > > # TODO: put a real copy-container on Select and copy, or somehow
> > > make this
> > > > # not modify the Select statement
> > > >
> > > > In the above comments, are we referring to a solution where the
> > > original
> > > > select statement is wrap inside another select statement where the
> > > actual
> > > > row_number function is applied?
> > > >
> > >
> > > the proper fix for this is present in the 0.4 branch of SQLAlchemy.
> > > I'd favor deferring the MS-SQL solution to 0.4 where it will be easier
> > > to implement.
> > >
> > >
> > > > > >
> > >
> >
> >
>
>
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
796,799c796,799
< if select.limit:
< s += "TOP %s " % (select.limit,)
< if select.offset:
< raise exceptions.InvalidRequestError('MSSQL does not support
LIMIT with an offset')
---
> # if select.limit:
> # s += "TOP %s " % (select.limit,)
> # if select.offset:
> # raise exceptions.InvalidRequestError('MSSQL does not support
> LIMIT with an offset')
842,848c842,883
< def visit_select(self, select):
< # label function calls, so they return a name in cursor.description
< for i,c in enumerate(select._raw_columns):
< if isinstance(c, sql._Function):
< select._raw_columns[i] = c.label(c.name + "_" +
hex(random.randint(0, 65535))[2:])
<
< super(MSSQLCompiler, self).visit_select(select)
---
> def visit_select(self, select):
> # label function calls, so they return a name in cursor.description
>
> for i,c in enumerate(select._raw_columns):
> if isinstance(c, sql._Function):
> select._raw_columns[i] = c.label(c.name + "_" +
> hex(random.randint(0, 65535))[2:])
>
> super(MSSQLCompiler, self).visit_select(select)
> self._limit_offset_interpolate(select)
>
> def visit_compound_select(self, select):
> super(MSSQLCompiler, self).visit_compound_select(select)
> self._limit_offset_interpolate(select)
>
> def _limit_offset_interpolate(self, select):
>
> if not hasattr(select, '_mssql_visit') and (select.limit is not None
> or select.offset is not None):
> select._mssql_visit = True
>
> if hasattr(select, "order_by_clause") and select.order_by_clause
> and select.order_by_clause.clauses:
> wrapper = select.alias("_msorg").select()
> # taming the original orderby clause
> orderby = sql.ClauseList(*map(wrapper.corresponding_column,
> select.order_by_clause.clauses))
> # remove the order_by_clause from original select
> select.order_by_clause = sql.ClauseList()
> else:
> # in case where no orderby is specified, use a dummy table
> orderby = "_msnull"
> wrapper = sql.select([select.alias('_msorg'),
> sql.literal_column("NULL").label(orderby).select().alias('__msnull')])
>
> wrapper.append_column(sql.literal_column("ROW_NUMBER() OVER
> (ORDER BY %s)" % orderby).label("_msrn"))
> limited = sql.select([c for c in wrapper.alias('_ms').c if c.key
> != '_msrn' and c.key != '_msnull'])
> offset = select.offset or 0
>
> if offset:
> limited.append_whereclause("_msrn > %d" % offset)
>
> if select.limit:
> limited.append_whereclause("_msrn <= %d" % limit + offset)
>
> self.traverse(limited)
> self.strings[select] = self.strings[limited]
> self.froms[select] = self.froms[limited]
