[sqlalchemy] Re: MSSQL and LIMIT/OFFSET - Michael Bayer - com.googlegroups.sqlalchemy

10 messages in com.googlegroups.sqlalchemy[sqlalchemy] Re: MSSQL and LIMIT/OFFSET

From	Sent On	Attachments
Paul Johnston	26 Nov 2007 11:25
Michael Bayer	26 Nov 2007 12:17	.patch
Esceo	09 Jan 2008 23:46
Esceo	10 Jan 2008 00:40
Paul Johnston	10 Jan 2008 01:02
Esceo	10 Jan 2008 01:33
Esceo	10 Jan 2008 02:41
lei you	10 Jan 2008 02:43	.diff
Esceo	10 Jan 2008 02:46
Esceo	10 Jan 2008 19:36

Subject:	[sqlalchemy] Re: MSSQL and LIMIT/OFFSET
From:	Michael Bayer (mike...@zzzcomputing.com)
Date:	11/26/2007 12:17:48 PM
List:	com.googlegroups.sqlalchemy
Attachments:	mssql.638.patch - 6k

On Nov 26, 2007, at 2:25 PM, Paul Johnston wrote:

Hi,

Can someone (Mike?) give me a hand with this, I've hit the limits of my understanding of the query compiler.

The patch I've done on #638 (mostly copied from Oracle) creates a subquery with row_number if there is an OFFSET. It aliases the query, because MSSQL is funny about that, and it also attempts to move ORDER BY from the inner to outer query (again, MSSQL is funny). Only problem is that it doesn't quite pick up the correct labelled name. Uncomment this line in the patch to see the problem: #limitselect._order_by_clause = select._order_by_clause

Any ideas would be a help (and a query compiler 101 document would be fab!)

attached is a modified version of that patch which includes the unit test. If the SQL isnt right, change the unit tests first to reflect what results you'd like to see, then we can try tweaking it.

the key feature used here is the "proxies" element on Column. this is also a new feature, it was previously some nasty methodology before 0.4.1.

if i have a table: create table sometable (a int, b int)

and then a select: select a, b from sometable

and an aliased version of it: (select a, b from sometable) as anon_1

you've got six columns in play. you have table.a, table.b; you have select.a and select.b, and anon1.a and anon_1.b. The "proxies" collection allows you to navigate from anon_1.a to select.a back to table.a; its a collection that in almost all cases has just one element (it only has multiple elements when a column is proxying a UNION or similar). So that way you can navigate to the column which an aliased column "represents".